Project 3 — SQL Expressions Solutions

Project 3 — SQL Expressions

SOLUTIONS

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.

SELECT *

FROM instructor

WHERE dept_name = 'Comp. Sci.'

ORDER BY name

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

SELECT dept_name, AVG(salary)

FROM instructor

GROUP BY dept_name

ORDER BY dept_name

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.

SELECT name, dept_name, course_id, sec_id, semester, year

FROM instructor NATURAL LEFT OUTER JOIN teaches

WHERE year = 2009

ORDER BY dept_name, name

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

SELECT name, course_id, sec_id, semester, year

FROM student

JOIN takes USING (id)

WHERE year = 2010 and semester = 'Spring'

ORDER BY course_id, sec_id, name

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

SELECT COUNT( DISTINCT i1.name)

FROM instructor i1, instructor i2

WHERE i1.dept_name = i2.dept_name

AND i1.name <> i2.name

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

SELECT *

FROM instructor i1

WHERE salary >=

(

SELECT AVG(salary)

FROM instructor i2

WHERE i1.dept_name = i2.dept_name

)

Alternatively:

WITH avg_salaries AS

(

SELECT dept_name, AVG(salary) AS avg_sal

FROM instructor i2

GROUP BY dept_name

)

SELECT *

FROM instructor NATURAL JOIN avg_salaries

WHERE salary >= avg_sal

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.

SELECT name, count(*)

FROM instructor NATURAL JOIN teaches

GROUP BY name

HAVING count(*) >= ALL

(

SELECT count(*)

FROM instructor NATURAL JOIN teaches

GROUP BY name

)

Alternatively:

WITH

max_courses AS

(

SELECT name, count(*) AS course_cnt

FROM instructor NATURAL JOIN teaches

GROUP BY name

max_teaches AS

(

SELECT MAX(course_cnt) AS max_course_cnt

FROM max_courses

)

SELECT name, course_cnt

FROM max_courses, max_teaches

WHERE course_cnt >= max_course_cnt

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).

SELECT name, course_id, building, room_number, day, start_hr

FROM instructor

JOIN teaches USING (id)

JOIN section USING (course_id, sec_id, semester, year)

JOIN time_slot USING (time_slot_id)

WHERE name = 'Katz' AND year = 2010 and semester = 'Spring'

ORDER BY (CASE day

WHEN 'M' THEN '1'

WHEN 'T' THEN '2'

WHEN 'W' THEN '3'

WHEN 'R' THEN '4'

WHEN 'F' THEN '5'

END)

, start_hr

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

SELECT *

FROM course c1

NATURAL LEFT OUTER JOIN prereq

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

UPDATE department d

SET budget =

(

SELECT SUM(salary)*1.1 FROM instructor i WHERE i.dept_name = d.dept_name

)

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

SELECT id,

ROUND (

SUM (

CASE SUBSTR(grade,1,1)

WHEN 'A' THEN 4 * credits

WHEN 'B' THEN 3 * credits

WHEN 'C' THEN 2 * credits

WHEN 'D' THEN 1 * credits

WHEN 'F' THEN 0 * credits

END )

(SELECT tot_cred FROM student WHERE takes.id = student.id)

, 2) AS gpa

FROM takes NATURAL JOIN course

GROUP BY id

ORDER BY gpa

Alternatively:

WITH

qual_points AS

(

SELECT id, SUM ( CASE SUBSTR(grade,1,1)

WHEN 'A' THEN 4 * credits

WHEN 'B' THEN 3 * credits

WHEN 'C' THEN 2 * credits

WHEN 'D' THEN 1 * credits

WHEN 'F' THEN 0 * credits

END ) AS qual

FROM takes NATURAL JOIN course

GROUP BY id

)

SELECT id, name, ROUND (qual / tot_cred, 2) AS gpa

FROM student NATURAL JOIN qual_points

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

SELECT * FROM course

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

SELECT name, tot_cred FROM student

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

SELECT * FROM course WHERE credits >= 3

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

SELECT * FROM student, instructor

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

SELECT * FROM depositor

UNION

SELECT * FROM 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.

CREATE TABLE hall (

name VARCHAR(20) PRIMARY KEY

)

INSERT INTO hall VALUES ('d''Artagnan')

INSERT INTO hall VALUES ('Athos')

INSERT INTO hall VALUES ('Porthos')

INSERT INTO hall VALUES ('Aramis')

CREATE TABLE resides (

name VARCHAR(20) REFERENCES hall(name),

id VARCHAR(5) REFERENCES student(id),

PRIMARY KEY (name, id)

)

INSERT INTO resides (id, name)

SELECT id,

CASE

( SELECT TRUNC( SUM(

CASE SUBSTR(grade,1,1)

WHEN 'A' THEN 4 * credits

WHEN 'B' THEN 3 * credits

WHEN 'C' THEN 2 * credits

WHEN 'D' THEN 1 * credits

WHEN 'F' THEN 0 * credits

END )) / student.tot_cred AS gpa

FROM takes NATURAL JOIN course

WHERE student.id = takes.id

)

WHEN 1 THEN 'd''Artagnan'

WHEN 2 THEN 'Athos'

WHEN 3 THEN 'Porthos'

WHEN 4 THEN 'Aramis'

ELSE 'Aramis'

END AS name

FROM student