In this assignment, you’ll be wirting some SQL by-itself and some python that uses the sqlite3 module.
You should only use SQL and python features that we have discussed up to this point.
If you have questions about whether or not a particular feature can/should be used, ask the instructors!
When grading your assignments, each SQL query will be tested with SQLite version 3, so make sure to test on this!
All of your SQL code should be well-formatted and easy for the graders to read.
In this problem, you will be writing a set of SQL statements that create, populate, and query tables.
In particular, the database will be modeling NBA players and their association with teams.
All of your SQL should be put into a single file named sportsdb.sql.
Put a comment of the form -- Problem N above the SQL for each problem.
Problem 3 has several sub-problems, so make sure to label those clearly with comments too.
Note that each of these problems builds off of the previous ones, so an incorrect solution to one part could effect your grade on another! Also, once you have written all of your solutions, make sure they can all be run in sequence without any errors.
In this problem, you are to write a sequence of CREATE SQL statements to build the structure of the database.
You will create two tables, and one will have a column that is a FOREIGN KEY referencing a column from another table.
Also make sure that player_id is the PRIMARY KEY of the player table, and team_id is the PRIMARY KEY of the team table.
The structure should be as follows:
In all, your solution just be two CREATE SQL statements.
In this problem, you will write a sequence of INSERT statements to populate the tables you just created.
You should insert a total of 10 rows (7 into player and 3 into team).
The data should be as follows:
Notice that the team_id in the player table maps to a valid team_id in the team table.
In all, you should write 10 INSERT statements.
Reminder: You may need to run PRAGMA foreign_keys = ON; in sqlite3 to make sure the FOREIGN KEY works correctly.
You should not just load these from a CSV file.
Write INSERT commands.
In this problem, you will write a sequence of SELECT statements that return various results.
In all, you will write 4 individual SELECT queries, each of which will be worth 10 points.
For each, the correct results are shown below.
Your query for each part should match the results.
Get the last name and first name (last name first) of all rows from player.
The final result should look exactly like:
James|Lebron
Curry|Steph
Irving|Kyrie
Durant|Kevin
Bledsoe|Eric
Thompson|Klay
Chandler|Tyson
Ulis|Tyler
Get the last name and player id (in that order) from player where the player_id is greater than 3.
The final result should look exactly like:
Durant|4
Bledsoe|5
Thompson|6
Chandler|7
Ulis|8
Get the first name, last name, and position (in that order) from player where position is guard.
The final result should look exactly like:
Steph|Curry|guard
Eric|Bledsoe|guard
Klay|Thompson|guard
Tyler|Ulis|guard
Kyrie|Irving|guard
Get all columns from team where the coach’s name contains the letter n.
The final result should look exactly like:
Cleveland Cavaliers|Tyronn Lue|2
Phoenix Suns|Earl Watson|3
Get the first name, last name, and team of each player in the database.
You will probably want to use a JOIN for this!
The final result should be:
Lebron|James|Cleveland Cavaliers
Steph|Curry|Golden State Warriors
Kyrie|Irving|Cleveland Cavaliers
Kevin|Durant|Golden State Warriors
Eric|Bledsoe|Phoenix Suns
Klay|Thompson|Golden State Warriors
Tyson|Chandler|Phoenix Suns
Tyler|Ulis|Phoenix Suns
Write a sequence of DELETE statements that deletes the following rows from the tables:
teamplayerplayerBefore these items are deleted, selecting all of the rows from each table should looks like:
sqlite> SELECT * FROM player;
Lebron|James|forward|2|1
Steph|Curry|guard|1|2
Kyrie|Irving|guard|2|3
Kevin|Durant|forward|1|4
Eric|Bledsoe|guard|3|5
Klay|Thompson|guard|1|6
Tyson|Chandler|center|3|7
sqlite> SELECT * FROM team;
Golden State Warriors|Steve Kerr|1
Cleveland Cavaliers|Tyronn Lue|2
Phoenix Suns|Earl Watson|3
After, it should look like:
sqlite> SELECT * FROM player;
Steph|Curry|guard|1|2
Kevin|Durant|forward|1|4
Eric|Bledsoe|guard|3|5
Klay|Thompson|guard|1|6
Tyson|Chandler|center|3|7
sqlite> SELECT * FROM team;
Golden State Warriors|Steve Kerr|1
Phoenix Suns|Earl Watson|3
You must also write a python program named sportsdb.py.
This program should do all of the same tasks that your solutions to problems 1-3 do.
Your python program should create the tables, insert the data, and run the queries specified in problem 3.
However, when you load the data, you should read in the content from the CSV files provided below, rather than hard-coding a bunch of insert statements.
The output of running your python program should match what is shown below:
Results for (A):
('James', 'Lebron')
('Curry', 'Steph')
('Irving', 'Kyrie')
('Durant', 'Kevin')
('Bledsoe', 'Eric')
('Thompson', 'Klay')
('Chandler', 'Tyson')
('Ulis', 'Tyler')
Results for (B):
('Durant', 4)
('Bledsoe', 5)
('Thompson', 6)
('Chandler', 7)
('Ulis', 8)
Results for (C):
('Steph', 'Curry', 'guard')
('Kyrie', 'Irving', 'guard')
('Eric', 'Bledsoe', 'guard')
('Klay', 'Thompson', 'guard')
('Tyler', 'Ulis', 'guard')
Results for (D):
('Cleveland Cavaliers', 'Tyronn Lue', 2)
('Phoenix Suns', 'Earl Watson', 3)
Results for (E):
('Lebron', 'James', 'Cleveland Cavaliers')
('Steph', 'Curry', 'Golden State Warriors')
('Kyrie', 'Irving', 'Cleveland Cavaliers')
('Kevin', 'Durant', 'Golden State Warriors')
('Eric', 'Bledsoe', 'Phoenix Suns')
('Klay', 'Thompson', 'Golden State Warriors')
('Tyson', 'Chandler', 'Phoenix Suns')
('Tyler', 'Ulis', 'Phoenix Suns')
Some of the problems in this assignment requires printing out precise text. We will only take minimal points off for very minor differences (for example, minor character spacing issues). However, we will take points off for any non-trivial differences, so try to be as precise as you can!
Your SQL file should have a header comment and a comment above the command(s) for each problem. The comments should start with double-dashes, for example:
--
-- Author: Student Name
-- Description:
-- A short description of what this program / script / set of functions does!
--
You python code should also include a header comment and other documentation commenting, and should follow python style guidelines..
It is due Monday, April 2, at 5:00pm.
Turn in sportsdb.sql and sportsdb.py to the D2L dropbox before the due-date.