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 name…
Looking for solution of this Assignment?
WHY CHOOSE US?
We deliver quality original papers |
Our experts write quality original papers using academic databases.We dont use AI in our work. We refund your money if AI is detected |
Free revisions |
We offer our clients multiple free revisions just to ensure you get what you want. |
Discounted prices |
All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount |
100% originality |
We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity.We have ZERO TOLERANCE TO USE OF AI |
On-time delivery |
We will deliver your paper on time even on short notice or short deadline, overnight essay or even an urgent essay |