Consider the following relational schema. An employee can work in more than one department; also, the percentTime field of the Works relations shows the percentage of time that a given employee works in a given department.

Employees(eID integer, fName string, fName string, age integer, salary real)

Locations(lID integer, address string, city string, state string, zip string) Departments(dID integer, dName string, budget real, managerEID integer, locationID integer)

WorksIn(eID integer, dID integer, percentTime integer, startDate date, endDate date )

The primary key fields are underlined, and the domain of each field is listed after the field name. Thus eID is the key for Employees, lID is the key for Locations, dID is the key for Departments, and eID,and dID together form the key for WorksIn. These tables have been created and the data have been populated using MySQL server. Download HW1.sql to your computer and run it on MySQL. Execute your SQL statements based on these tables. Turn in your SQL queries statements and the results of these queries to the assignment link.

Some notes to keep in mind as you go:

While we have tried to limit the possible number of correct solutions to each question, keep in mind that there still may be multiple correct solutions. For each of the question, there is a relatively short solution, which is preferred.

Unless specified in the question, your queries should produce the correct answer for any instance of the relations. We have provided a script that will contain DDL for creating a sample database with some test data, but your queries should work over any legitimate instance of the relation.

Unless specified in the question, return the entire tuple. E.g. retrieve students would mean return the entire students tuples.

Return the columns in the order we specify. E.g. question 6 would require you to output the fields in name, salary order.

Note that the dataset provided is minimal. We will be testing your queries on a much larger dataset, so be sure to add to the one provided to make sure your queries are in fact correct

Write the following queries in SQL statements. The query answers must not contain duplicates, but you should use the SQL keyword DISTINCT only when necessary. When your SQL query statement is not accepted by MySQL, it will display error messages. You need to read that message and fix all problems accordingly. Note that MySQL does not support INTERSECT and EXCEPT currently. When you need to find the intersection and difference of two result sets, you can use IN and NOT IN instead. In addition, for this assignment, creation of temporary tables is not allowed, i.e., for each question you must write exactly one SQL statement.

(20 Points) Find the minimum, maximum, and average salary in each department.

Answer:

(20 Points) Retrieve the name and salary of the two highest salaried employees for the department Research.

Answer:

(20 Points) Retrieve the name of employees, whose salary is higher than the average of all employees, and their department name.

Answer:

(20 Points) Find the department name and the number of its employees who are making higher than average salary of all employees.

Answer:

(20 Points) Find the department name and the number of its employees who are making higher than the departments average salary.

Answer:

Extra Credit Question

(20 Points) Retrieve the name and salary of the first two highest salaried employees in each department.

Answer: