Lab 6 CIS 275

/*
*******************************************************************************************
CIS275 at PCC
CIS275 Lab Week 6: using SQL SERVER 2016-IMBD and Examples databases
*******************************************************************************************

CERTIFICATION:

By typing my name below I certify that the enclosed is original coding written by myself
without unauthorized assistance. I agree to abide by class restrictions and understand that
if I have violated them, I may receive reduced credit (or none) for this assignment.

CONSENT: [your name here]
DATE: [date]

*******************************************************************************************
*/

GO
PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’
PRINT ‘Read the questions below and insert your queries where prompted. When you are finished,
you should be able to run the file as a script to execute all answers sequentially (without errors!)’ + CHAR(10)
PRINT ‘Queries should be well-formatted. SQL is not case-sensitive, but it is good form to
capitalize keywords and table names; you should also put each projected column on its own line
and use indentation for neatness. Example:

SELECT Name,
CustomerID
FROM CUSTOMER
WHERE CustomerID < 106; All SQL statements should end in a semicolon. Whatever format you choose for your queries, make sure that it is readable and consistent.' + CHAR(10) PRINT 'Be sure to remove the double-dash comment indicator when you insert your code!'; PRINT '|---' + REPLICATE('+----',15) + '|' + CHAR(10) + CHAR(10) GO PRINT 'CIS 275, Lab 6, Question 1 [3pts Possible]-USE Examples database: Write a SELECT statement that returns the same result set as this SELECT statement, but does noy use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword.' USE Examples; SELECT DISTINCT LastName, FirstName FROM Employees ID JOIN Customers Cust ON ID.EmployeeID = Cust.CustID ORDER BY LastName, FirstName; PRINT 'Correct results will have 9 rows and will look like this: LastName FirstName Aaronsen Robert Hardy Thomas Hernandez Olivia Jones Elmer Locario Paulo O Leary Rhea Simonian Ralph Smith Cindy Watson Denise' --Insert your code here: GO PRINT ' CIS275. Lab 6, Question 2 [3pts Possible]-USE Examples Database Write a SELECT statement that answers this question: Which vendors have an YTDPurchases that’s greater than the average YTDPurchases for all vendors? Return the VendorsName, VendorCity, and YTDPurchases columns for each vendor. Sort the result set by the YTDPurchase column in descending sequence. Correct results will have 21 rows and will look like this: VendorName VendorCity YTDPurchases Malloy Lithographing Inc Ann Arbor 213039.65 United Parcel Service Reno 93601.99 Yesmed, Inc Fresno 48690.51 Valprint Fresno 44995.75 Bertelsmann Industry Svcs. Inc Valencia 39420.56 Zylka Design Fresno 30486.44 Federal Express Corporation Memphis 29742.98 Dean Witter Reynolds Fresno 29522.50 American Express Los Angeles 28740.10 Courier Companies, Inc Boston 27462.56 State of California Sacramento 22300.46 Pollstar Fresno 17500.00 AT&T Phoenix 15730.97 Data Reproductions Corp Auburn Hills 14624.88 Franchise Tax Board Sacramento 12632.50 Dataforms/West Fresno 12108.44 Custom Printing Company St Louis 12102.01 Computerworld San Francisco 11664.50 Champion Printing Company Cincinnati 10729.14 Dristas Groom & Mccormick Fresno 9082.00 Simon Direct Inc East Brunswick 8662.50' --Inser your code here GO PRINT 'CIS2275, Lab Week 6, Question 3 [3pts possible]-USE IMDB Database: Write the query to display the name and year of birth for all people born in 1990, who have directed at least one show (i.e. those who appear at least once in the title_directors table). ---------------------------------------------------------------------------------------------- Columns to display: name_basics.primaryName, name_basics.birthYear Sort in descending order by birth year. The result will have 541 rows as follows: primaryName birthYear Aaron George 1990 Aaron Granillo 1990 Aaron Miner 1990 Abdullah Helwani 1990 Abid Virani 1990 Abiola Ogunbiyi 1990 Adam Christie 1990 Adam Stephen Kelly 1990 Addison Sandoval 1990 Adrian Dent 1990 Adrian Spencer 1990 Adriana Li Mandri 1990 Adrien Le Falher 1990 ----- ----- Zainal Palmans 1990 Zeb Halsell 1990 Zgjim Terziqi 1990 Zosya Rodkevich 1990 Zurab Match 1990 ' + CHAR(10) -- -- [Insert your code here] GO -- PRINT 'CIS275, Lab 6, Question 4 [3pts Possible]-USE Example database Write a SELECT statement that returns the LastName and FirstName columns from the Employee table. Return one row for each employee that doesn’t have any project in the Projects table. To do that, use a subquery introduced with the NOT EXISTS operator. Sort the result set by LastName and then by FirstName. The result should include 2 rows: LastName FirstName Hardy Thomas Jones Elmer' -- [Insert your code here] GO PRINT 'CIS2275, Lab Week 6, Question 5 [3pts possible] USE IMDB Database: Show top 50 genre of television show which has had at least one title with 100 episodes. i.e. limit results to the titleType ''tvEpisode'' in the title_basics table, and to titles containing a row in the title_episode table with episodeNumber 100. ---------------------------------------------------------------------------------------------- Columns to display: title_genre.genre Display genre name only, and eliminate duplicate values. The result should include the following 50 rows: genre Crime Drama Drama Game-Show Crime Drama Crime Drama Crime Drama Crime Drama Crime Drama Crime Drama Drama Game-Show Game-Show Game-Show Game-Show Drama Drama Comedy Talk-Show Comedy News Talk-Show Comedy News Talk-Show Comedy News Talk-Show Comedy News Talk-Show Game-Show Talk-Show Comedy Music Drama Drama Comedy Talk-Show Comedy Talk-Show Talk-Show Game-Show Comedy ' + CHAR(10) GO -- -- [Insert your code here] -- GO PRINT 'CIS2275, Lab Week 6, Question 6 [3pts possible] USE IMDB: Write a common table expression to identify the BEST shows: join title_basics against title_ratings and limit your results to those with an averageRating value equal equal to 10. Project the title, type, and startYear from title_basics; and label your CTE as BESTSHOWS. In the main query, show a breakdown of BESTSHOWS grouped by type, along with the total number of rows for each (i.e. GROUP BY titleType) ---------------------------------------------------------------------------------------------- Columns to display: titleType, COUNT(*) Sort results in descending order by COUNT(*). The result should have 9 rows as follows: titleType TOTAL_BEST_SHOWS tvEpisode 2665 short 556 video 133 movie 114 tvMovie 87 tvSeries 14 tvMiniSeries 14 tvSpecial 12 tvShort 6 ' + CHAR(10) -- [Insert your code here] -- GO PRINT 'CIS2275, Lab Week 6, Question 7 [3pts possible] USE IMDB : Identify the least popular professions. Show each profession value from the name_profession table, along with the total number of matching rows (GROUP BY profession). Use the HAVING clause to limit your results to professions with less than 1,000 rows. ---------------------------------------------------------------------------------------------- Columns to display: name_profession.profession, COUNT(*) The result includes 3 rows as follows: profession TOTAL_PEOPLE electrical_department 1 production_department 1 script_department 1' + CHAR(10) -- -- [Insert your code here] -- GO PRINT 'CIS2275, Lab Week 6, Question 8 [3pts possible] USE IMDB Database: Show the name of every writer, along with the total number of titles they''ve written (i.e. rows in the title_writers table). Limit results to those who have written more than 5000 titles (inclusive) . ---------------------------------------------------------------------------------------------- Columns to display: name_basics.primaryName, COUNT(*) Sort results in descending order by primaryName The result should have 12 rows as follows:. primaryName TITLES_WRITTEN William J. Bell 10703 Tony Warren 9373 Reg Watson 10559 Peter Ling 5151 Lee Phillip Bell 11168 Kevin Laffan 5018 Hazel Adair 5066 Delia Fiallo 5558 Bradley Bell 6234 Armand Jammot 5329 Agnes Nixon 5594 Adrián Suar 5968 ' + CHAR(10) -- -- [Insert your code here] -- GO PRINT 'CIS2275, Lab Week 6, Question 9 [3pts possible] USE IMDB: Identify the names of people who have directed only 2 lowest-rated shows (i.e. title_ratings.averageRating = 1). For each of these people, display their names and the total number of shows they have written. ---------------------------------------------------------------------------------------------- Columns to display: name_basics.primaryName, COUNT(*) Sort results in ascending order by primaryName. The result should 15 rows as follows: primaryName TOTAL_WRITTEN Aliakbar Campwala 7 Brett Kelly 25 Carl Goldstein 5 Darcy Ann Turner 5 Joseph Christiana 8 Justin Chambers 8 Kayoko Asakura 7 Kimberlee McCalla 5 Mishovy Silenosty 7 Naoyoshi Kawamatsu 3 Paul Young 2 Raffi Atamian 4 Satoru Hirohara 4 Stuart Simpson 3 Yasutake Torii 2 ' + CHAR(10) -- -- [Insert your code here] -- GO PRINT 'CIS2275, Lab Week 6, Question 10 [3pts possible] USE IMDB: Identify every movie from 1913 (startYear = 1913, titleType = ''movie''); limit your results to those with a non-NULL value in the runtimeMinutescolumn. For each movie, display the primaryTitle and the averageRating value from the title_ratings table. Use DENSE_RANK() to display the rank based on averageRating (label this RATINGRANK), and also the rank based on runtimeMinutes (label this LENGTHRANK). Both of these should be based on an asecending sort order. ---------------------------------------------------------------------------------------------- Columns to display: title_basics.primaryTitle, title_ratings.averageRating, RATINGRANK, LENGTHRANK Sort results in ascending order by primaryTitle.The result should have 40 rows: primaryTitle averageRating RATINGRANK LENGTHRANK A Message from Mars 5.2 3 15 Atlantis 6.8 16 27 Brother Against Brother 6.0 8 5 Das Recht auf Dasein 6.3 11 1 David Copperfield 6.3 11 13 De levende ladder 6.2 10 4 Dick Whittington and his Cat 6.9 17 1 -- -- Traffic in Souls 6.2 10 21 Twilight of a Womans Soul 6.8 16 2 What 80 Million Women Want 4.5 1 8 Where Is Coletti? 6.7 15 20 Zigomar - the Black Scourge - Episode 1 6.6 14 7 ' + CHAR(10) -- -- [Insert your code here] -- GO ------------------------------------------------------------------------------------- -- This is an anonymous program block. DO NOT CHANGE OR DELETE. ------------------------------------------------------------------------------------- BEGIN PRINT '|---' + REPLICATE('+----',15) + '|'; PRINT ' End of CIS275 Lab Week 6' + REPLICATE(' ',50) + CONVERT(CHAR(12),GETDATE(),101); PRINT '|---' + REPLICATE('+----',15) + '|'; END;