Using ORACLE server 12c SQL*Developer (client ver 18.2)

Lab 3 Marked

Use your Oracle USER ID stuxx on BTACS database.

1. Use the SQL Developer to login into your account
2. Now create the following tables : (xx is your login number)

staffxx (staffNo, lname, fname, dept, desig, joiningDate, salary, rating, branchCode)

also DOB, commission and GossSalary

branchxx (branchCode, address, city, phone, yearOfOpening)

Please note the code for rating as

1 = Full time employee

2 = Contract employee

3 = Visiting employee

3. Write the queries to:
   2. List the non managers getting salary more than any of the manager
   3. List the non managers getting salary more than all the managers
   4. List the details of the manager of the oldest branch

4. Add two columns Manager (same as staffID) and managerSince (date) in branch table
5. Modify three tuples of the branch table to add manager code and managerSince date in branch table
6. Now run the queries:
   1. List the details of all the managers who are listed in branch table
   2. List all the managers who are working as manager for more than 2 years
   3. List a manager who is working as a manager at the same branch ever since he has joined.

This is a database system question

Assignment due at 4 p.m.