Using microsoft sql server management studio 2012 (or later) or

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;