CSC 242 DPU Databases and Data Processing Questions

CSC 242-604Professor Adam Hecktman
Programming Homework Assignment 8
Due Tuesday, May 30th, 2023
Programming Problem
This week, we turned our attention to databases and data processing. We used SQLite
as our built-in Python database to learn the basics of the Structured Query Language
for querying and modifying databases. We learned how to use Python to access data in
databases. And we learned about parameter substitution and how it can be used to run
SQL queries with Python variables.
On Tuesday, you learned how to execute SQL statements to query a database as well
as to create tables, insert rows, update rows, etc. On Thursday, you will learn to
execute SQL statements in Python.
The Chinook Database is a sample sqlite3 database that models a digital media store. .
The tables include the following:
Tables in Chinook
Albums
Artists
Customers
Employees
Genres
Invoice_items
Invoices
Media_Types
Playlist_track
Playlists
The schema diagram below shows you what fields appear in each table:
CSC 242-604
Professor Adam Hecktman
Programming Homework Assignment 8
Due Tuesday, May 30th, 2023
1) SQL Statements (1 pt each): The following may be done using either sqlite’s
command line interface or Python
a. There are 8 employees, and they are tracked in the employees table. Write a
SQL command to display the employee ID, last name, first name, and hire
date for each employee, sorted by hire date.
b. There were 2 employees hired in 2004. Write a SQL command to display the
employee ID, last name, first name, and hire date for each employee hired in
2004, sorted by hire date. HINT: the dates are in the format ‘yyyy-mm-dd’. So
January 1st of 2005 in a SQL query is ‘2005-01-01’.
c. How many customers are based in India. HINT: Use the customers table.
d. A manager wants to know what countries their customers come from. Write a
query that shows by country how many customers come from each country
(in ascending order by country). HINT: You will use Group By.
e. List the album titles and artist names, for all albums, in ascending order by
name (in the artist table).
f. This one will require you to keep careful track of which table contains which
field. In this query, your manager wants to see information about customers
(first name, last name, city) who live in London, and their invoices (invoice ID
and invoice date).
CSC 242-604
Professor Adam Hecktman
Programming Homework Assignment 8
Due Tuesday, May 30th, 2023
2) SQL Statements (1 pt each): The following should be done in Python using
parameter substitution:
a. Insert a record into the artists table with the following:
i. artistID = 300
ii. artistName = ‘Steely Dan’
Remember to use parameter substitution!
b. The manager with Employee ID 6 has been promoted. Now all of her direct
reports report to the employee with ID 2. Write the code to update all
employee records where they were reporting to employee 6 (ReportTo = 6) to
now report to employee 2 (ReportTo = 2).
c. Write a query that selects track names, track composers, and genre names
where the genre is equal to a variable name (genreVal) that you set. So if
you set genreVal to ‘Classical’, it will return a cursor object with the track
names and composers and genre name for all classical tracks.
For example, this query would return:
Symphony No. 3 in E-flat major, Op. 55, “Eroica” – Scherzo: Allegro Vivace|Ludwig van Beethoven|Classical
Intoitus: Adorate Deum|Anonymous|Classical
Etc.
d. Now iterate over that cursor object using a Python for loop.
Grading
The assignment is worth 10 points. It will be graded thus:
•
1 pt for each problem.