/*
*******************************************************************************************
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;
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.
Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.
Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.
Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.
Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.
Read more