Lab # 6~ Single Table Queries This is a group assignment. Only one submission is
ID: 3592268 • Letter: L
Question
Lab # 6~ Single Table Queries
This is a group assignment. Only one submission is needed.
Download the script Football and execute it once you log in to SQL Server. Study the ERD so that you are familiar with how that database is structured. Additionally 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 #6 Assignment.
1.Create a list of all games. Include in your output the game id, game date and game time. Whendone your output should look like the following:
gameID gameDate gameTime
----------- ---------- ----------------
10000 2012-08-30 18:00:00.0000000
10001 2012-08-30 18:00:00.0000000
10002 2012-09-06 18:00:00.0000000
10003 2012-09-06 18:00:00.0000000
10004 2012-09-06 18:00:00.0000000
10005 2012-09-13 18:00:00.0000000
10006 2012-09-13 18:00:00.0000000
10007 2012-09-13 18:00:00.0000000
10012 2012-08-23 18:00:00.0000000
10013 2012-08-23 18:00:00.0000000
10014 2012-08-23 18:00:00.0000000
10015 2012-08-30 18:00:00.0000000
2.Create a list of all location names and city. Using Concatenation and aliases, get your output tolook like the following:
Field Name and City
---------------------------------------------------------
Field A || Warren
Field B || Clinton Twp.
Field C || Macomb
Field D || Sterling Heights
3.Create a list of all players and the fees they pay. The football league is looking to increase fees by15%. Show that column in your list. When complete your output should look like the following.Note: your spacing may be a little different. This output is formatted to be smaller.
PersonID Fee Increase New Fee
-------- ------------ ------------
117 0.0000 0.0000
118 3.7500 28.7500
119 3.7500 28.7500
120 3.7500 28.7500
121 3.7500 28.7500
122 0.0000 0.0000
123 3.7500 28.7500
124 3.7500 28.7500
125 3.7500 28.7500
126 3.7500 28.7500
127 3.7500 28.7500
128 0.0000 0.0000
129 3.7500 28.7500
130 3.7500 28.7500
131 3.7500 28.7500
132 3.7500 28.7500
133 3.7500 28.7500
134 0.0000 0.0000
135 3.7500 28.7500
136 3.7500 28.7500
137 3.7500 28.7500
138 3.7500 28.7500
139 0.0000 0.0000
140 3.7500 28.7500
141 3.7500 28.7500
142 3.7500 28.7500
143 3.7500 28.7500
145 0.0000 0.0000
4. Create a list of players, their fees and their physical. Sort your list so that the higher value fees are at the top, followed by those that have had a physical. Using column aliases, duplicate your output to look like the following:
Person ID Fee Player Physical
----------- --------------------------------------- ---------------
119 25.00 Y
120 25.00 Y
121 25.00 Y
123 25.00 Y
125 25.00 Y
127 25.00 Y
129 25.00 Y
130 25.00 Y
131 25.00 Y
132 25.00 Y
136 25.00 Y
138 25.00 Y
141 25.00 Y
142 25.00 Y
143 25.00 Y
137 25.00 N
135 25.00 N
140 25.00 N
133 25.00 N
126 25.00 N
124 25.00 N
118 25.00 N
117 0.00 Y
128 0.00 Y
134 0.00 Y
122 0.00 N
139 0.00 N
145 0.00 N
5. Create a query that will list home teams, the location and the score. Sort your output by team, location and score. When completed your output should look like:
home_team locationid score
----------- ----------- ----------
101 1 27 - 7
101 2 21 - 10
102 1 14 - 0
102 2 27 - 14
103 2 21 - 7
103 3 10 - 7
104 3 24 - 3
104 3 14 - 7
105 1 17 - 14
105 3 21 - 20
106 1 14 - 7
106 2 14 - 3
Footbal.sql: https://drive.google.com/file/d/0BwasM-ovulfxOU5JcUVTMnJyTy1Dd09GRmtKWkYzcHhnOVJr/view?usp=sharing
Football ERD: https://drive.google.com/file/d/0BwasM-ovulfxRGRKeG9MSUpadGh2aUQ4UWRidWtVZHl5ZGhn/view?usp=sharing
Explanation / Answer
Below are SQL queries of Football database.
--This query creates a list of all games available in relation ‘game’.
SELECT gameID, gameDate, gameTime
FROM game;
--This query list all the location names and city. CONCAT function is used to merge --location name and city ------0----attribute.
SELECT CONCAT(locationName, ‘ || ’, city) AS ‘Field Name and City’
FROM location;
--This query list ‘playerID’, increment in their fee and total fee after increment of 15%.
SELECT p.PersonID, (pr.fee *15)/100) AS Fee, (pr.fee + (pr.fee *15)/100) AS ‘Increase new fee’
FROM player AS p INNER JOIN playerrec AS pr
ON p.PersonID = pr.PersonID
--This query creates list of Player ID, fee and physical attribute. The list is order by Fee and physical
SELECT p.PlayerID, pr.Fee, Physical AS ‘Player Physical’
FROM player AS p INNER JOIN playerrec AS pr
ON p.PersonID = pr.PersonID
ORDER BY pr.Fee DESC, ‘Player Physical’ ASC;
--This query result in home_teams, their location and score. List is sorted by home_team and locationid.
SELECT g.home_team, g.locationID, g.score
FROM game AS g
ORDER BY home_team, locationID
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.