Using Microsoft SQL Server Management Studio 2012 (Or Later) Or DataGrip

Hello, this question is here because we've worked on this and other similar assignments. If you don't know the answer, you can ask us for help. We guarantee an original paper free from Plagiarism.

Order a Similar Paper Order Different Paper

We got you covered for the whole semester.

Write my research paper
Sample Code:USE TVSELECT *FROM SHOW/* All high-def channels: */SELECT *FROM CHANNELWHERE DisplayName LIKE ‘%HD’;/* Limit results to just the channel #: */SELECT ChannelIDFROM CHANNELWHERE DisplayName LIKE ‘%HD’;/* Use this as a subquery to identify shows on these channels: */SELECT *FROM SCHEDULEWHERE 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 SCHEDULEWHERE 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 SCHEDULEWHERE 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 CHANNELWHERE 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 CHANNELWHERE 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 MOSTPOPULARSHOWFROM SHOW AS OUTERSHOWGROUP BY OUTERSHOW.GenreORDER 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 MOSTPOPULARSHOWFROM SHOW AS OUTERSHOWGROUP BY OUTERSHOW.GenreORDER 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 LEASTPOPULARSHOWFROM SHOW AS OUTERSHOWGROUP BY OUTERSHOW.GenreORDER BY OUTERSHOW.Genre;/* Subquery in ORDER BY clause; sort by earliest StartTime: */SELECT Title, GenreFROM 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 EarliestTimeFROM 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 namesGROUP BY MetaphoneORDER 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_TABLELEFT JOIN (SELECT Metaphone, COUNT(*) AS HANS FROM names WHERE LOWER(Name) LIKE ‘%han%’ GROUP BY Metaphone) AS HAN_TABLEON NAT_TABLE.Metaphone = HAN_TABLE.MetaphoneORDER BY 1;/* Some of the names from matching rows above: */SELECT *FROM namesWHERE 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.NameCountFROM 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.YearGenderTotalIDORDER 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 MyNameQueryWHERE MyNameQuery.Year = 1967ORDER 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.NameCountFROM MyNameQuery AS A JOIN MyNameQuery AS B ON A.Name = B.Name AND A.Year = B.YearWHERE A.Gender = ‘M’AND B.Gender = ‘F’ORDER BY A.Year, A.Name;Purchase the answer to view itPurchase the answer to view itPurchase the answer to view itPurchase the answer to view itPurchase the answer to view itPurchase the answer to view itPurchase the answer to view itPurchase the answer to view itPurchase the answer to view itPurchase the answer to view it
This is property of essayprince.net. We are the first stop for all students Buying research papers online. Our paper writing service is second to none. Come and Buy research papers online here, TODAY

If you don't know the answer to this question, you can ask us for help. We guarantee an original paper free from Plagiarism.

Order a Similar Paper Order Different Paper

You can trust us with any of your assignments. We got you covered for the whole semester. We dedicate one writer to do all your assignments

Open chat
1
Hello Good Friend
40% OFF on your Assignments.
How can we help you today?

Order your essay today and save 20% with the discount code TWENTYTWENTY50