Question

Problem 1 (25 points) Consider the following database schema: Employee (fname, Iname, ssn, address, salary, mgrssn, dnumber) Department (dname, dnumber, mgrssn, mngrstartdate) Project (pname, pnumber, plocation, dnumber) Works_On (ssn, pnumber, hours_per_week) Dependent (ssn, dependent name, bdate, relationship) The above relations store information about a company. The meaning of most of the relations and attributes is straightforward. For example, the first relation stores information about employees. The mgrssn is the SSN of the manager (supervisor) of the given employee. A manager is also an employee. The department relation stores information about departments. Also, each project is associated with a given department and is located in a single location. Finally, the Works_On and the Dependent relations are store information about which project each employee works on and the dependents of each employee. An employee can work on many projects. Using the above schema write the following queries in SQL: 1. Find the first and last names of the employees that work in the “Toy” department. 2. Find the first and the last name of the employees that have more than 2 dependents. 3. Find the ssn of the employee(s) that work the most (largest) total number of project hours per week. (An employee can work on multiple projects and the sum of the hours on these projects is the total hours per week for the employee).