421-sql1

In from clause relation dot attribute disambiguates

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: abase Systems - SQL Queries I 22 31 debby debby 7 8 16 10 sid 22 31 skater debby debby 4 List of relations in FROM clause Relation-dot-attribute disambiguates attributes from several relations. Cross-Product: Skaters X Participates SELECT * FROM Skaters, Participates Constants as attribute values SELECT rating AS reality, FROM Skaters sid sname rating age Multirelational Queries upgrade debby debby lilly 16 10 10 COMP-421: Database Systems - SQL Queries I Attribute Lists (contd)   7 8 10 Renaming columns heading for all with rating < 9 COMP-421: Database Systems - SQL Queries I 22 debby 31 debby 58 lilly 10 AS dream reality dream 7 8 10   10 10 10 sid sname rating age 31 debby 8 58  lilly 10 22 debby 7 Join: equals cross-product and selection     Have to indicate comparison even with natural join Example: give me the names of all skaters that participate in a comp. SELECT sname FROM Skaters, Participates WHERE Skaters.sid = Participates.sid SELECT sname FROM Skaters JOIN Participates ON Skaters.sid = Participates.sid 10 10 16 5 πsname (Participates COMP-421: Database Systems - SQL Queries I Skaters) 6 1 Semantics (identical to relational algebra)   Range Variables Conversion to Relational Algebra     Same as for single relation, but start with the product (X)   Parallel assignment of tuple variables FROM Skaters S, Participates P WHERE S.sid = P.sid AND P.cid = 101   Consider a tuple variable for each relation in the FROM   Consider all possible assignments   For each such assignment of tuple variables to tuples that   makes the WHERE true, output the attributes of the SELECT 22 debby 31 debby 58 lilly 7 8 10 rank sid cid sid sname rating age 31 16 10 10 101   debby lilly 58  103 5 58 101 7 58 104 Use of range variable required when the same relation appears twice in the FROM clause Example: find pairs of skaters that have participated in the same competition SELECT p1.sid, p2.sid sname 2 Optional use of range variables SELECT S.sname of...
View Full Document

This document was uploaded on 02/11/2014.

Ask a homework question - tutors are online