ACS 3902 002 PostgreSQL Exercise

PART A

Using PostgreSQL, create the Company database from our textbook/notes and insert the data.

1. Add a new attribute to the EMPLOYEE table:
a) "Office" of type VARCHAR(4) and nulls allowed, and set default values to X-0
b) Perform an update on the table that sets the following office numbers based on department:

2. Modify the Company database to accommodate:
a) When an employee is deleted
i) set FKs in Employee and Department to null (note: requires that Mgr_ssn can be NULL)
ii) delete referenced rows in Works_On and Dependent

b) When an employee's SSN is updated
- then propagate that change to all pertinent FKs

3. Using the database that results from question 1, modify the contents of the employee table:

a) Delete James Borg.
b) Change Franklin Wong's SSN to 999999999
c) Insert the following records (fill in other attribute values with your own sample data)
i. Insert Hermione Granger who is supervised by John Smith(ssn 111111111, Salary 25000, Department 5)
ii. Insert Harry Potter who is supervised by Hermione Granger (ssn 777777777, Salary 20000, Department 5)
iii. Insert Ron Weasley who is supervised by Alecia Zalaya (ssn 101010101, Salary 20000, Department 4)

PART B

1. Continuing with the database resulting from Part A:

a) For all departments, list the department name, its manager's name and their office. The list must be in order by department name.

b) For those employees who have a supervisor, list the last name of the employee and his/her supervisor's last name. The list must in order by the supervisor last name and then by the employees.

c) Retrieve the last name of each employee who has a daughter. List the employees in sequence by last name. Do this in 3 different ways:

i) Use the IN operator.
ii) Use the EXISTS operator.
iii) Use a single-block query (i.e. do not use nested)

d) Create a view named Project_hours that lists the name of each project and total hours worked on each project

e) Use the view created in d) to retrieve a list of employees (fname, lname, ssn) who are working on the project with the least hours.

2. With the same Company database, use separate CTEs to answer each of:

a) For each employee working on projects ProductX, ProductY or ProductZ, list their name and number of dependents. Include employees with zero dependents.

b) For employee Franklin Wong (ssn 999999999), list the last names of all employees supervised by him directly or indirectly (i.e. from Wong down to the leaves). The list must in order by last name.

c) List the combined salary of all employees that Franklin Wong supervises directly or indirectly (i.e. add up the salaries of employees beneath Wong in the tree.

d) For employee Jennifer Wallace (ssn 987654321), retrieve the name of the youngest employee who reports directly or indirectly to her.

e) List the last name of the employee who is the top-most supervisor of Ron Weasley (ssn 101010101).

3. Print the following output using CTE:

*
**
***
****
*****
******
*******
********
... (total of 21 rows)

Use the PostgreSQL functions:

• concat() used to concatenate two or more strings
• length() is used to find the length of a string