You will write SQL queries against a DB whose schema and instances are described here in SQL. Assume all relations are in BCNF. This is the same database used in HW2 for the relational algebra queries. You must run your queries on a real system, e.g. SQL Server. You can use the commands in the web page linked above to create and populate your database. SQL Server's Query Analyzer has an option allowing you to open a file in the query window and execute all the commands in the file. Instructions on using the SQL Server installation in the Sieg Hall labs can be found here. You can, of course, use any relational product you like, but you may have to make minor adjustments to the domain names, etc. Finally, beware of one thing: It is possible to write an SQL query that gives the correct answer (for these relation instances) but is not, in general, the right query.

You do not need to remove duplicates in the final result unless explicitly requested to do so. What you should turn in for each question is the SQL query and the result it produced. The query and the result for each question should be together (i.e., don’t give us all the queries then, separately, all the results). All these queries can be posed in SQL, and all return non-empty results from the sample instance provided.

IMPORTANT: This homework must be submitted via email. Your email message must contain your name (near the top of the message body) and your SQL queries with their responses, in the proper order. The email must be received by bart@cs (don't send them to Prof. Vandenberg) before the beginning of class on the due date. The SQL and results must be included as text in your message; do not use any attachments. Each query is worth 9 points except the last one, which is worth 10.

1. Retrieve all department locations (just the dlocation field), with duplicates removed.
   
   
2. Retrieve the last name and social security number of all female employees.
   
   
3. For each department with at least one employee, retrieve the department number and the average salary of all employees working in that department. Order the results by increasing average salary.
   
   
4. Retrieve the last names of all employees who work more than 30 hours per week on some project.
   
   
5. Retrieve the first name of every employee in department 5 who works more than 20 hours per week on a project named "ProductX".
   
   
6. Find the social security number, last name, and salary of all employees who are directly supervised by an employee whose first name is "Franklin" and last name is "Wong".
   
   
7. For each project, list the project name and the total hours per week (by all employees) spent on that project. Your results should contain, for each project, its name and one number, the total hours spent by all employees of that project combined.
   
   
8. Retrieve all first names in the database (note that two relations contain first names). Duplicates should be preserved.
   
   
9. List the first and last names of all employees who have no supervisor and work on no projects.
   
   
10. Retrieve the social security numbers of all employees having the largest number of dependents.
    
    
11. Retrieve the last name, salary, address, and sex of all employees who work on every project located in Houston. Do not use "group by".