Supplemental reading
SQL reference book Oracle database 12c SQL by Price, ISBN 9780071799362 (available in Books 24x7 DePaul online library as eBook). pp 42-43: Using the IN Operator, Using the BETWEEN Operator pp. 45-57: Sorting Rows Using the ORDER BY Clause, Performing SELECT Statements That Use Two Tables, Using Table Aliases, Cartesian Products, Performing SELECT Statements that Use More than Two Tables, Join Conditions and Join Types, Non-equijoins, Outer Joins, Self-joins, Performing Joins Using the SQL/92 Syntax.
Part 1
In this and the next part we will use an extended version of the schema from Assignment 2. You can find it in a file ZooDatabase.sql posted with this assignment on D2L.
Once again, it is up to you to write the SQL queries to answer the following questions:
List the animals (animal names) and the ID of the zoo keeper assigned to them.
Now repeat the previous query and make sure that the animals without a handler also appear in the answer.
Report, for every zoo keeper name, the total number of hours they spend feeding all animals in their care.
Report every handling assignment (as a list of assignment date, zoo keeper name and animal name). Sort the result of the query by the assignment date in an ascending order.
Find the names of animals that have at least 1 zoo keeper assigned to them.
Find the names of animals that have 0 or 1 (i.e., less than 2) zoo keepers assigned to them.
Optional query
List all combination of animals where the difference between feeding time requirement is 0.25 hours or less (e.g., Grizzly bear, 3, Bengal tiger, 2.75). Hint: this will require a self-join. Avoid listing identical pairs such as (Grizzly bear, 3, Grizzly bear, 3)
Part 2
A. Write a python script that is going to read the queries that you have created in Part-1 from a SQL file, execute each SQL query against SQLite database and print the output of that query. You must read your SQL queries from a file, please do not copy SQL directly into python code. The code that would run commands from the ZooDatabase.sql file is provided (runSQL.py in Python 3, I also included an optional runSQL_Python2.py which works in Python 2), so all you have to do is to change it so that it reads your queries from a SQL file in the same way and also prints the output of your queries. You must print every row individually using a for-loop.
B. Create the table and use python to automate loading of the following file into SQLite. It contains comma-separated data, with two changes: NULL may now be represented by NULL string or an empty string (e.g., either ,NULL, or ,,) and some of the names have the following form “Last, First” instead of “First Last”, which is problematic because when you split the string on a comma, you end up with too many values to insert.
Part 3
Using the company.sql database (posted in with this assignment on D2L), write the following SQL queries.
Find the names of all employees who are directly supervised by ‘Franklin T Wong’ (your SQL query must use the name, not the SSN value).
For each project, list the project name, project number, and the total hours per week (by all employees) spent on that project.
For each department, retrieve the department name and the average salary of all employees working in that department. Order the output by department number in ascending order.
Retrieve the average salary of all female employees.
For each department whose average salary is greater than $42,000, retrieve the department name and the number of employees in that department.
Retrieve the names of employees whose salary is within $25,000 of the salary of the employee who is paid the most in the company (e.g., if the highest salary in the company is $85,000, retrieve the names of all employees that make at least $60,000.).
Be sure that your name and “Assignment 3” appear at the top of your submitted file.