Company ABC - Background
Your client, The ABC Company, asks you to create a small database to keep the information of employees, departments, projects and dependants.
Refer the data tables in the page 2 and write answers or perform actions for following questions.
1. Identify primary keys, foreign keys, NULL and other constraints exist with the tables. Document the identified constraints.
Table 1. Identified Primary Key, Foreign key, Not null Constraints
|
Table Name
|
Primary Key
|
Foreign Key ( references table A)
|
Nott Null columns
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2. Create all the tables with identified constraints on Oracle. (e.g. primary keys, foreign keys, NULL and other)
NOTE: When you create tables you should decide the appropriate types for the attributes. And also you should add the screenshots of CREATE TABLE statements in the project Report
3. Insert all the given values. You are feel free to insert new values to the tables.
NOTE: You should add all INSERT INTO statements and screenshots in the project report.
4. Write following queries on the tables.
a. Print employee's first name, last name, address and department number.
b. Print employee full name and department and employee's supervisors full name.
c. Print employee information who works for project 3. (i.e. ProductZ).
d. Print the employee and the number of dependant for an employee.
e. Print information about employees working more than one project.
f. Find the manager who earns the highest salary.
g. Print for all the employees number of hours in total they have worked in the projects.
h. Print project information of the projects which are managed by department 5 (i.e. research).
i. Print the total number of projects controlled by the different departments and the manager of the project. (department manager is the project manager)
j. Identify and print information about employees who have children less than 15 years old.
k. Print details of mangers who have worked more than 20 years of the company
l. Create your own query. It must include a nested query. Submit the following:
i. question your query is answering
ii. the SQL query
iii. the mark for this question will depend on the complexity of the query.
iv. higher marks will be given for queries that are more complex and/or innovative.
v. if you do not provide a description of what question the query is answering, you will get zero for this query.