SQL Assignment

The schema of these relations is: Courses (course, credits, days, time, room, title, prof, dept) Profs (pname, given, office, ext, email) Students (name, given, linux, major, email) Enroll (linux, course, grade) Coreq (lect, lab) Dayx (days, dayo, day, jour) -- ex: MNF 1 Monlundi All attributes are CHARACTER, except for credits, grade, dayo and ext, which are integer. Primary keys are underlined SQL statements. here is an outline of the various clauses of SELECT. You can type any sql statement in psql, they can cover several lines, and end with a semicolon (:) Up-arrow recalls previous lines. In this outline, keywords are bold, optional things are in [square brackets] and one or more things in curly brackets) o select (list of attributes and aggregate functionsh o from tablenames o [where [conditions) ] o [group by o [having (conditions) ]] o order by (column (s) [descending] JOIN constructs. You can't use the first form for "outer" joins) for the others, inner join is the default o from people, phone where people.id-phone.id [and ...] o from people [left I right full] join phone on (people.id phone.id) o from people [...] join phone using (id) from people natural [...] join phone o from (students natural join enroll) natural join courses o courses left join labs using(course) - to get also courses without labs Natural join won't work Why? SUBQUERIES can be placed in the where clause, for example o where linux in (select linux from students where name- 'Mouse) o where not exists (select from enroll where enroll.course courses.course) this is a co-related subquery, which must be reevaluated for each row of the outer query, involving courses