Lab # 8~ Multi Table Queries

Study the ERD for the Football database so that you are familiar with how that database is structured.

Additionally log on to SQL Server and examine the data in each table by issuing a SQL * statement for

each table.

Read each question below and follow your steps to create the query. Once you have perfected your

query, copy the SQL statement and your output to a notepad file. Put your name on the notepad file and

upload it to the Lab#8 Assignment.

1. Create a query that will show the name of the location for each game. Include in your query the game

date and time and the location name. Only include those games on 9/13/2012. Format the time so

that it does not appear as military time and you do not see the seconds and milliseconds. HINT:

explore the datapart function. Your output should look like the following:

Game Date Game Time Location

---------- --------- --------------------

2012-09-13 6:0 Field A

2012-09-13 6:0 Field B

2012-09-13 6:0 Field C

2. Create a query that will show the name of each coach (first and last) with the team that they are on.

Order your output by team name. Your output should look like the following:

first_name last_name teamName

--------------- ------------------------- ---------------

Martin Sam Blue Jackets

Allen Timothy Blue Jackets

Warren Tanner Gold Dragons

Tim Ford Gold Dragons

Theodore Turner Green Hornets

James Barney Green Hornets

Alfred Dillinger Purple Pilots

Carlos Velasquez Purple Pilots

Larry Ngao Red Devils

Midori Nagayama Red Devils

Mark Quigley Silver Lions

Audrey Ropeburn Silver Lions

3. Create a query that will show those teams that have won 2 or more games. Order your output by the

team that has won the most games. Your output should look like the following:

Team Name Num Wins

--------------- -----------

Green Hornets 3

Red Devils 2

Silver Lions 2

Blue Jackets 2

Gold Dragons 2

4. Create a query that shows the first and last name of the ref and the number of games they have

refereed for. Include in your output only those refs with a last name of Johnson and Jones. Your

output should look like the following:

first_name last_name Games Ref'd

--------------- ------------------------- -----------

Jim Johnson 5

Tom Jones 3