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