Computer Science

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;

Order now and get 10% discount on all orders above $50 now!!The professional are ready and willing handle your assignment.

ORDER NOW »»