1 / 11

SQL Examples for Relational Algebra Queries

Explore relational algebra and SQL examples based on Dr. Shiyong Lu's notes featuring database systems concepts. Learn query solutions such as finding professors teaching specific courses.

hjamie
Télécharger la présentation

SQL Examples for Relational Algebra Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Relational Algebra and SQL Examples Revised based on the notes from Dr. Shiyong Lu of Wayne State University Database Systems: An Application Oriented Approach, Edition 2 by Michael Kifer, Arthur Bernstein, Philip M. Lewis ISBN:0321268458 ISBN13:9780321268457 Release Date:March 2005

  2. Relational Algebra and SQL Exercises • Professor(ssn, profname, status) • Course(crscode, crsname, credits) • Taught(crsname, semester, ssn) Please refer to the SQLite commands that create the test database.

  3. Query 1 • Professor(ssn, profname, status) • Course(crscode, crsname, credits) • Taught(crsname, semester, ssn) Return those professors who have taught ‘CSCI203’ but never ‘CSCI240’.

  4. Relational Algebra Solution • Professor(ssn, profname, status) • Course(crscode, crsname, credits) • Taught(crsname, semester, ssn) ssn(crsname=‘CSCI203’(Taught)) -ssn(crsname=‘CSCI240’(Taught))

  5. SQL Solution • Professor(ssn, profname, status) • Course(crscode, crsname, credits) • Taught(crsname, semester, ssn) SELECT ssn From Taught Where crsname = ‘CSCI203’ EXCEPT SELECT ssn From Taught Where crsname = ‘CSCI240’;

  6. Query 2 • Professor(ssn, profname, status) • Course(crscode, crsname, credits) • Taught(crsname, semester, ssn) Return those professors who have taught both ‘CSCI203’ and ‘CSCI204’.

  7. Relational Algebra Solution • Professor(ssn, profname, status) • Course(crscode, crsname, credits) • Taught(crsname, semester, ssn) ssn(crsname=‘CSCI203’  crsname=‘CSCI204’ (Taught), wrong! ssn(crsname=‘CSCI203’(Taught))  ssn(crsname=‘CSCI204’(Taught)), correct!

  8. SQL Solution • Professor(ssn, profname, status) • Course(crscode, crsname, credits) • Taught(crsname, semester, ssn) SELECT T1.ssn From Taught T1, Taught T2 Where T1.crsname = ‘CSCI203’ AND T2.crsname=‘CSCI204’ AND T1.ssn=T2.ssn;

  9. Query 3 • Professor(ssn, profname, status) • Course(crscode, crsname, credits) • Taught(crsname, semester, ssn) Return those professors who have never taught ‘CSCI204’.

  10. Relational Algebra Solution • Professor(ssn, profname, status) • Course(crscode, crsname, credits) • Taught(crsname, semester, ssn) ssn(crsname<>‘CSCI204’(Taught)), wrong answer! ssn(Professor) -ssn(crsname=‘CSCI204’(Taught)), correct answer!

  11. SQL Solution • Professor(ssn, profname, status) • Course(crscode, crsname, credits) • Taught(crsname, semester, ssn) SELECT ssn From Professor EXCEPT SELECT ssn From Taught T Where T.crsname = ‘CSCI204’;

More Related