Project 3 — SQL Expressions

 

Academic Integrity: I completed this project without the assistance of others. I understand that cheating, helping others to cheat, or failing to report such actions is dishonest and wrong. Such acts could result in disciplinary action against me.

 

· Email your solution in an attached PDF file to both the instructor and the TA.

· The subject should be

“Project3 - " + Your First Name + Last Name e.g. Project3 - JohnDoe

· Your file name should be

“Project3 - " + Your First Name + Last Name + ".PDF” e.g. Project3 - JohnDoe.pdf

· Include your name inside the PDF file.

· For full credit, your assignment must be received by 11:59 pm on the due date.

 

 

Use the university database to write SQL expressions for the questions below.

 

 

1. Show all the instructor information for the instructors in the computer science department. Order them alphabetically by name.

2. Show the name and average salary for each department. Order by department.

3. For each instructor, show the name, department, and each of the classes that he teaches in 2009 .  Organize the output by department and then by name.  Do not show their salary or their id.

4. Create a class roster of all students in each section offered in Spring 2010.

5. Use a join to show how many instructors belong to the same department as another instructor.

6. Show all instructors that make more than the average salary for the department.

7. Show the instructors that teach the most classes (compared to other instructors) over all terms.  Include their name and the number of classes they teach.

8. Show Katz’s teaching schedule for Fall 2009.  Include name, course_id,  building, room number, days, start times and ending times. Order by day of the week and start time (Monday followed by Tuesday, etc).

9. List each course and all it’s prerequisites.

10. Update the budget of each department to be 10% more than the total of the instructors’ salaries.

11. Compute each student’s GPA. Ignore +’s or -’s.  List id and GPA.  Order by GPA.

12. Convert the following relational algebra expression to SQL.     s (course)

13. Convert the following relational algebra expression to SQL.     p _{name, tot_cred} (student)

14. Convert the following relational algebra expression to SQL.     s _{credits  >= 3}  (course)

15. Convert the following relational algebra expression to SQL.     student × instructor

16. Convert the following relational algebra expression to SQL.     depositor È borrower

17. Add tables to the database to track which of the four residence halls each student lives in.  Names of the halls are: d’Artagnon, Athos, Porthos, and Aramis.  Don’t forget to define primary and foreign keys.  Populate the tables.  Assign students to a hall based on GPA.  Use the INSERT … SELECT form of the insert to assign all students in one statement.