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

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

*******************************************************************************************

SQL SERVER 2012 and various databases

*******************************************************************************************

CERTIFICATION:

By typing my name below I certify that the enclosed is original coding written by myself

without unauthorized assistance.  I agree to abide by class restrictions and understand that

if I have violated them, I may receive reduced credit (or none) for this assignment.

CONSENT:   [your name here]

DATE:      [date]

*******************************************************************************************

*/

GO

PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’

PRINT ‘Read the questions below and insert your queries where prompted.  When  you are finished,

you should be able to run the file as a script to execute all answers sequentially (without errors!)’ + CHAR(10)

PRINT ‘Queries should be well-formatted.  SQL is not case-sensitive, but it is good form to

capitalize keywords and to capitalize table names as they appear in the database; you should also put

each projected column on its own line and use indentation for neatness.  Example:

SELECT Name,

CustomerID

FROM   CUSTOMER

WHERE  CustomerID < 106;

All SQL statements should end in a semicolon.  Whatever format you choose for your queries, make

sure that it is readable and consistent.’ + CHAR(10)

PRINT ‘Be sure to remove the double-dash comment indicator when you insert your code!’;

PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’ + CHAR(10) + CHAR(10)

GO

GO

PRINT ‘CIS 275, Lab Week 3, Question 1  [3pts possible]:

What is on TV?

—————

This is an exact repeat of Question 7 from Lab Week 2. However, instead of using the all_data

view, instead JOIN the CHANNEL, SHOW, and SCHEDULE tables together. Consult the ERD (if needed)

to determine which columns to match in the ON parts of the JOINs.

Show the first 100 TV shows that start on 9/10/2017 at 8:00PM (StartTime is 20:00:00).

Display results in order by ChannelNumber.

Show ONLY the DisplayName, ChannelNumber, StartTime, EndTime, and Title.

Use CONVERT to format the StartTime and EndTime hh:mi:ss without the day, month, or year.

Use CAST or CONVERT make DisplayName 10 characters wide, and Title 30 characters wide.

Make sure all columns have appropriate names (using AS where needed).

Hint: A DATETIME column can be matched against a string like 8/30/1962 13:00:00.

Hint 2: Correct results will have 100 rows, and look like this:

Channel Name Channel Number Start Time End Time Title

———— ————– ———- ——– ——————————

KATU         2              20:00:00   21:00:00 Celebrity Family Feud

KRCW         3              20:00:00   20:30:00 Two and a Half Men

KPXG         5              20:00:00   21:00:00 Law & Order: Criminal Intent

KOIN         6              20:00:00   21:00:00 Big Brother

DSCP         7              20:00:00   21:00:00 Alaska: The Last Frontier

WGNAP        9              20:00:00   21:00:00 Blue Bloods

KOPB         10             20:00:00   21:30:00 The Carpenters: Close to You (

KPTV         12             20:00:00   21:00:00 The Orville

KPDX         13             20:00:00   21:00:00 Rookie Blue

TELEP        15             20:00:00   00:00:00 Ad Channel

QVC          16             20:00:00   21:00:00 Today”s Top Tech

…

MEXCAN       625            20:00:00   20:30:00 Liga Mexicana de Jaripeo Profe

MULTV        626            20:00:00   22:00:00 Poncho en Domingo

TEFEI        629            20:00:00   22:30:00 La Peña de Morfi

‘ + CHAR(10)

GO

USE TV

—

— [Insert your code here]