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;