代写SQL语句，实现数据库查询分析。
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.
