CS448 Company Database Queries

In this project you will use the file: db.sql. Copy this file into your working directory. After entering the sqlplus environment, create and populate tables by the following command:

SQL>@db

ProjectsInfo is a database used by a software development company to keep track of its projects. ProjectsInfo keeps track of the projects, the employees/managers working on the projects including the universities they were graduated from. The back-end database of the ProjectsInfo consists of the relations defined in the following schema:

University(UnivId, UnivName)
Department(DeptId, DeptName)
Employee(EmpId, EmpName, DeptId, ZipCode)
Project(ProjId, ProjName)
Graduate(EmpId, UnivId, GradYear)
EmpProject(EmpId, ProjId, StartDate, EndDate)
ProjectManager(ProjId, MgrId, StartDate, EndDate)

• Relation University contains information about the universities where the employees graduated from. Attribute UnivId is the primary key.
• Relation Department contains information about the different departments in the company. Attribute DeptId is the primary key. The name of a department is unique.
• Relation Employee contains information about the employees in the company (including managers). Attribute EmpId is the primary key.
• Relation Project contains information about the projects that are running or that have been completed by the company. Attribute ProjId is the primary key. The name of a project is unique.
• Relation Graduate contains information about the university as well as the graduation year of each employee. It is assumed that each employee is graduated by exactly one degree from one university. Thus, the primary key is defined to be EmpId.
• Relation EmpProject contains information about all the projects an employee is/was working on. The primary key is composed of the three attributes: EmpId, ProjId, StartDate as an employee can rejoin a project s/he was released from. A NULL value in the EndDate attribute indicates "Current", i.e., the employee is currently working on that project.
• Relation ProjectManager contains information about all the managers of each project. A project has only one manager at a time, but the project can have different managers at different non-intersecting time frames. A manager is identified by Attribute MgrId and references the EmpId attribute of the Employee relation. The primary key is composed of the three attributes: ProjId, MgrId, StartDate. A NULL value in the EndDate attribute indicates "Current", i.e., the manager is currently managing that project.

Tasks

Write SQL queries that answer the questions below (one SQL query per question but you are allowed to use nested queries and/or the "WITH" clause of Oracle) and run them on the Oracle system.

Simple SELECT FROM WHERE queries should be sufficient for most queries, but some will require basic aggregation operators (e.g. COUNT(), MAX(), etc.), GROUP BY statements, and ORDER BY statements. If you are unfamiliar with Oracle SQL or any of these concepts please feel free to attend PSOs or TA office hours, or ask TAs if you have any questions.

1.Find the names of the employees who are living in West Lafayette (Zip code 47906 or 47907).

2.Find the names of the projects that are currently managed by any manager.

3.Display the names of all projects in descending order.

4.For each university, display the universitys name and the number of employees who have graduated from that university.

5.Display the name, department name, and graduation year of each Employee.

6.Find the names of all employees who work in Department 2. Print the names in ascending order.

7.Find the names of all Employees that graduated from Purdue after year 2000 (HINT: Dont assume that the university ID of Purdue will be the same in the grading test cases as they are in the provided data. Use Joins to solve instead of hard-coding the university ID).

8.For each zip code in the Employee table, print the number of employees that live in that zip code. Order the results by zip code in descending order.

9.Print the name(s) of the employee(s) who graduated most recently.

10.For each entry in EmpProject, print the name of the project and the name of the employee that worked on that project during that time period. Order the results in ascending order, first by the project name and second by the employee name.

Drop

Drop all tables. Use statement select * from user_catalog; to make sure that all the objects are dropped. You can use the droptables.sql script to drop all tables.