[Solved] CS6360.001 – Quiz 3

Database Design

Specify following queries in SQL. Use COMPANY database.

1. Create following views.
2. A view that has the department name, manager name, and address for every department.
3. A view that has the employee name, supervisor name, and employee salary for each employee who works in the ‘Research’ department.

2. For each employee, list his/her ssn, last name and the number of dependents he/she has, even if it is zero (Output: Ssn, LName, Num Deps). Output should be ordered alphabetically, by last name of employee.

3. Find the last names of employees earning above average salary in their respective departments. (Output: Lname, DNo, Salary).

4. List the last names of employees who do not work on any project controlled by their respective departments (Output: LName).

5. List the female employees, each of whom works on 2 or more projects. (Output: SSN, Number-of-projects)

Each question is 20 points.

If you are not using Oracle Cloud account to test your sql queries, use following schema for COMPANY database.

Employee (FName, LName, SSN, BDate, Address, Gender, Salary, SuperSSN, DNo)

Department (DName, DNo, MgrSSN, MgrStartDate)

Project (PName, PNo, PLocation, DNo)

Works_On (SSN, PNo, Hours)

Dept_Location (Dno, DLocation)

Dependent (Essn, Name, Gender, Relationship)