Comp 305-001/405-001: Database Administration

Spring 2024: MWF

Week 6

Homework 2: sql2.text, due at the end of February


select p.pname, sum( e.salary* w.hours/40)
from project p join works_on w on p.pnumber = w.pno
join employee e on w.essn = e.ssn
group by p.pnumber;

Start with sql2.html, " Multi-value nested queries"

Wednesday

More nested queries (sql2.html#nested2)

Entity-Relationship diagrams, and where joins come from: ER.html

Friday

ER diagrams and Joins

The ssn becomes a stand-in for an employee. To get the rest of the employee information, join to the employee table on e.ssn. Likewise, dept number is a stand-in for the department record, and the project number is a stand-in for the project.

Two-attribute-key relations

More ALL queries (pld.cs.luc.edu/database/sql2.html#universal_quantification)

HAVING