relational algebra soln

relational algebra soln - WHERE l.cid=c.cid AND...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Study Problems on Relational Algebra Understand each of the following SQL query, and then translate it into relational algebra. Database schema: Courses (cid, name) Staff (sid, firstname, lastname) Lecturing (sid, cid, year) (1). SELECT c.name FROM courses c, staff s, lecturing l WHERE l.sid=s.sid AND l.cid=c.cid AND (l.year=1999 OR l.year=2000) AND s.lastname='Jung'; Answer: Find the names of courses that are taught by staff with last name “Jung” in year 1999 or 2000. π name ( σ (lecturing.sid=staff.sid) (lecturing.cid = course.cid) (year = 1999 year = 2000) (lastname = Jung) (lecturing staff course)) or π name ( σ (year = 1999 year = 2000) lecturing   σ lastname = Jung staff   course) The second form is much more efficient! (2). SELECT c.name FROM lecturing l, courses c WHERE l.cid=c.cid and l.year=2001 EXCEPT SELECT c.name FROM lecturing l, courses c
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: WHERE l.cid=c.cid AND (l.year=2000 OR l.year=1999); Answer: Find the names of courses that are taught in 2001 but not taught in 2000 or 1999. name ( year = 2001 (lecturing courses)) - name ( (year = 1999 year = 2000) (lecturing course)) (3). SELECT c.name FROM lecturing l1, lecturing l2, courses c WHERE l1.cid=l2.cid AND l1.cid=c.cid AND l1.sid=l2.sid AND l1.year=1999 AND l2.year=2000; Answer: Find the names of the courses that are taught by the same staff in both 1999 and 2000 name ( year = 1999 year2 = 2000 (lecturing courses year -> year2 lecturing) (4). SELECT s.lastname FROM staff s WHERE s.sid NOT IN (SELECT l.sid FROM lecturing l); Answer: Find the last names of the staff that do not teach lastname (( sid staff - sid lecturing) staff)...
View Full Document

Page1 / 2

relational algebra soln - WHERE l.cid=c.cid AND...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online