Using Microsoft SQL Server Management Studio 2012 (Or Later) Or DataGrip
it’s only 10 questions …
Sample Code:
USE TV
SELECT *
FROM SHOW
/* All high-def channels: */
SELECT *
FROM CHANNEL
WHERE DisplayName LIKE ‘%HD’;
/* Limit results to just the channel #: */
SELECT ChannelID
FROM CHANNEL
WHERE DisplayName LIKE ‘%HD’;
/* Use this as a subquery to identify shows on these channels: */
SELECT *
FROM SCHEDULE
WHERE FK_ChannelID IN
(SELECT ChannelID
FROM CHANNEL
WHERE DisplayName LIKE ‘%HD’
)
ORDER BY ScheduleID;
/* Note that available columns are now limited to only those from SCHEDULE. */
/* An additional condition: limit to Spanish genre shows: */
SELECT *
FROM SCHEDULE
WHERE FK_ChannelID IN
(SELECT ChannelID
FROM CHANNEL
WHERE DisplayName LIKE ‘%HD’
)
AND FK_ShowID IN
(SELECT ShowID
FROM SHOW
WHERE Genre = ‘Spanish’
)
ORDER BY ScheduleID;
/* Reverse logic by using NOT IN: */
SELECT *
FROM SCHEDULE
WHERE FK_ChannelID NOT IN
(SELECT ChannelID
FROM CHANNEL
WHERE DisplayName LIKE ‘%HD’
)
AND FK_ShowID NOT IN
(SELECT ShowID
FROM SHOW
WHERE Genre = ‘Spanish’
)
ORDER BY ScheduleID;
/* Show channels in which the most popular Children’s show is scheuled: */
SELECT *
FROM CHANNEL
WHERE ChannelID IN
(SELECT FK_ChannelID
FROM SCHEDULE
WHERE FK_ShowID =
(SELECT TOP 1 ShowID
FROM SHOW
WHERE Genre = ‘Children’
ORDER BY ISNULL(StarRating,0) DESC
)
);
/* Same query, but using correlated subquery with EXISTS: */
SELECT *
FROM CHANNEL
WHERE EXISTS
(SELECT FK_ChannelID
FROM SCHEDULE
WHERE SCHEDULE.FK_ChannelID = CHANNEL.ChannelID
AND FK_ShowID =
(SELECT TOP 1 ShowID
FROM SHOW
WHERE Genre = ‘Children’
ORDER BY ISNULL(StarRating,0) DESC
)
);
/* Genre breakdown; plus Title of most popular show for each: */
SELECT OUTERSHOW.Genre,
COUNT(*) AS TOTALSHOWS,
(SELECT TOP 1 INNERSHOW.Title
FROM SHOW AS INNERSHOW
WHERE INNERSHOW.Genre = OUTERSHOW.Genre
ORDER BY ISNULL(INNERSHOW.StarRating,0) DESC
) AS MOSTPOPULARSHOW
FROM SHOW AS OUTERSHOW
GROUP BY OUTERSHOW.Genre
ORDER BY OUTERSHOW.Genre;
/* Why does this version fail? */
SELECT OUTERSHOW.Genre,
COUNT(*) AS TOTALSHOWS,
(SELECT INNERSHOW.Title
FROM SHOW AS INNERSHOW
WHERE INNERSHOW.Genre = OUTERSHOW.Genre
AND INNERSHOW.StarRating =
(SELECT MAX(StarRating)
FROM SHOW )
) AS MOSTPOPULARSHOW
FROM SHOW AS OUTERSHOW
GROUP BY OUTERSHOW.Genre
ORDER BY OUTERSHOW.Genre;
/* Add least popular Title: */
SELECT OUTERSHOW.Genre,
COUNT(*) AS TOTALSHOWS,
(SELECT TOP 1 INNERSHOW.Title
FROM SHOW AS INNERSHOW
WHERE INNERSHOW.Genre = OUTERSHOW.Genre
ORDER BY ISNULL(INNERSHOW.StarRating,0) DESC
) AS MOSTPOPULARSHOW,
(SELECT TOP 1 INNERSHOW.Title
FROM SHOW AS INNERSHOW
WHERE INNERSHOW.Genre = OUTERSHOW.Genre
ORDER BY ISNULL(INNERSHOW.StarRating,999) ASC
) AS LEASTPOPULARSHOW
FROM SHOW AS OUTERSHOW
GROUP BY OUTERSHOW.Genre
ORDER BY OUTERSHOW.Genre;
/* Subquery in ORDER BY clause; sort by earliest StartTime: */
SELECT Title,
Genre
FROM SHOW
ORDER BY ISNULL(
(SELECT MIN( CONVERT(TIME, StartTime, 14) )
FROM SCHEDULE
WHERE SCHEDULE.FK_ShowID = SHOW.ShowID),
’00:00:00′) ASC;
/* Same subquery in the SELECT to show value. Inefficient! */
SELECT Title,
Genre,
ISNULL(
(SELECT MIN( CONVERT(TIME, StartTime, 14) )
FROM SCHEDULE
WHERE SCHEDULE.FK_ShowID = SHOW.ShowID), ’00:00:00′) AS EarliestTime
FROM SHOW
ORDER BY ISNULL(
(SELECT MIN( CONVERT(TIME, StartTime, 14) )
FROM SCHEDULE
WHERE SCHEDULE.FK_ShowID = SHOW.ShowID),
’00:00:00′) ASC;
/* Switching databases */
USE NAMES
/* Metaphone breakdown: */
SELECT Metaphone,
COUNT(*)
FROM names
GROUP BY Metaphone
ORDER BY Metaphone;
/* Show Metaphone breakdown of names containing ‘nat’. Correlate with Metaphones
of names containing ‘han’: */
SELECT *
FROM
(SELECT Metaphone,
COUNT(*) AS NATS
FROM names
WHERE LOWER(Name) LIKE ‘%nat%’
GROUP BY Metaphone) AS NAT_TABLE
LEFT JOIN
(SELECT Metaphone,
COUNT(*) AS HANS
FROM names
WHERE LOWER(Name) LIKE ‘%han%’
GROUP BY Metaphone) AS HAN_TABLE
ON NAT_TABLE.Metaphone = HAN_TABLE.Metaphone
ORDER BY 1;
/* Some of the names from matching rows above: */
SELECT *
FROM names
WHERE LOWER(Name) LIKE ‘%nat%’
AND LOWER(Name) LIKE ‘%han%’
ORDER BY Metaphone, Name;
/* Calculate breakdown of all names with genders, and totals: */
SELECT N.Name,
YGT.Gender,
YGT.Year,
NC.NameCount
FROM names AS N JOIN name_counts AS NC ON N.NameID = NC.FK_NameID
JOIN year_gender_totals AS YGT ON NC.FK_YearGenderTotalID = YGT.YearGenderTotalID
ORDER BY N.NameID, YGT.Year, YGT.Gender;
/* Use this query in a Common Table Expresion (CTE). Omit ORDER BY clause: */
WITH MyNameQuery AS
(
SELECT N.Name,
YGT.Gender,
YGT.Year,
NC.NameCount
FROM names AS N JOIN name_counts AS NC ON N.NameID = NC.FK_NameID
JOIN year_gender_totals AS YGT ON NC.FK_YearGenderTotalID = YGT.YearGenderTotalID
)
SELECT *
FROM MyNameQuery
WHERE MyNameQuery.Year = 1967
ORDER BY Name, Year, Gender;
/* Join two versions of this CTE together */
WITH MyNameQuery AS
(
SELECT N.Name,
YGT.Gender,
YGT.Year,
NC.NameCount
FROM names AS N JOIN name_counts AS NC ON N.NameID = NC.FK_NameID
JOIN year_gender_totals AS YGT ON NC.FK_YearGenderTotalID = YGT.YearGenderTotalID
)
SELECT A.Name,
A.Year,
A.Gender AS MALE,
A.NameCount,
B.Gender AS FEMALE,
B.NameCount
FROM MyNameQuery AS A JOIN MyNameQuery AS B ON A.Name = B.Name
AND A.Year = B.Year
WHERE A.Gender = ‘M’
AND B.Gender = ‘F’
ORDER BY A.Year, A.Name;
Needs help with similar assignment?
We are available 24x7 to deliver the best services and assignment ready within 3-4 hours? Order a custom-written, plagiarism-free paper
Get Answer Over WhatsApp
Order Paper Now
