Assignment2

Question 1
In SQL, specify the following queries on the database specified in Figure 1 below using the concept of nested queries and the concepts described in chapter 5.
a. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees.
SELECT fname, minit, lname FROM employee WHERE dno =(SELECT dno FROM employee WHERE salary= (SELECT MAX(Salary) FROM employee)); b. Retrieve the names of all employees whose supervisor’s supervisor has '888665555' for SSN.
SELECT fname, minit, lname FROM employee WHERE Super_ssn IN (SELECT ssn FROM employee WHERE Super_ssn= '888665555');
c. Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company.
SELECT fname, minit, lname FROM employee WHERE Salary >= 10000 + (SELECT MIN(Salary) FROM employee');

Question 2
In SQL, specify the following queries on the database specified in Figure 1 above. Show the query results if applied to the database of Figure 2 below.
a. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department.
SELECT Dname, count(dno) FROM employee INNER JOIN department ON employee.dno = department.dnumber GROUP BY Dno HAVING (AVG(Salary) > 30000);
b. For part a above, suppose we want the number of male employees in each department rather than all employees. Hint: consider using a nested query.Figure 2
SELECT Dname, count(dno) FROM employee INNER JOIN department ON employee.dno = department.dnumber WHERE sex = 'M' GROUP BY Dno HAVING (AVG(Salary) > 30000);

Question 3
Create an ER diagram for the following case. Write your assumptions about any missing information:
You are working for a consulting company and want to create a very simple database. This database will be used to track problems clients are having and the consultants who are working on them. Each client can have many