[Solved] College Tutor: Project Phase 4

In this Phase, you will implement SQL query statements for the relational algebras created in Part 1 of Phase 3 and your queries will be tested using the Oracle database you populated in Part 2 of Phase 3.

Your Task: Based on the relational algebra provided by the instructor, implement SQL query statements for the following tasks. You could use your own solutions on relational algebra, but you should make sure your solutions are correct.

1. Find the tutors who are responsible for tutoring the same courses as ‘Jim Champ’ (Suppose there is only one User called Jim Champ)
2. Count the Number of Academic Units per university
3. Count the Number of Academic Units per university. If there are no units, display null.
4. Find faculty who are also students
5. List names of tutors who are hired by College CLAS (ID)
6. Calculate the total number of working hours (OnCall, Appointment and Tutoring Sessions) of Tutor 9003(ID) hired by Unit CS (ID) of Campus IUSB (ID) in May 2015. Only status in attended (char value ‘3’) will be counted.

Note:

1. You will need to enter data into the database to test your queries;
2. Do not change the design of the database, because the instructor will use the same database to test your queries;
3. If your query works, you will receive 3/3 (full) credit for that question;
4. If your query does not retrieve correct result but has no syntax errors, you will receive at most 2/3 credit for that question;
5. If your query has syntax errors and could not run, you will receive at most 1/3 credit for that question.