R be as in the previous slide, where A and B are in X. The selection of R with respect to condition A = a is expressed by
SELECT DISTINCT * FROM R WHERE A = a SQL> SELECT DISTINCT last_name FROM student WHERE class = 2; |
A = B is expressed by
SELECT DISTINCT * FROM R WHERE A = b SQL> SELECT DISTINCT first_name FROM student WHERE class = student_id; |
R and S is expressed by
SELECT DISTINCT * FROM R EXCEPT SELECT DISTINCT * FROM S SQL> SELECT DISTINCT * FROM ( SELECT last_name FROM student ) 2 MINUS 3 SELECT DISTINCT * FROM ( SELECT first_name FROM student ); |
R be a relational schema with attributes A1, ..., An, B1, ..., Bm and S a relational schema with attributes B1, ..., Bm, C1, ..., Cl.
Then the natural join of R and S, which joins those tuples from R and S which have equal B-values, is expressed by
SELECT DISTINCT A1, ..., Am, R.B1, ..., R.Bm, C1, ..., Cl FROM R, S WHERE R.B1 = S.B1 AND ... AND R.Bm = S.Bm SQL> DROP TABLE enrollment; SQL> CREATE TABLE enrollment ( 2 student_id CHAR(4), 3 course_no CHAR(4), 4 major CHAR(6) ); SQL> INSERT INTO enrollment VALUES ( 2 '1', '101', 'No' ); SQL> SELECT DISTINCT s.student_id, class, major 2 FROM student s, enrollment e 3 WHERE s.student_id = e.student_id; |
|
You don’t need a parachute to go skydiving. You need a parachute to go skydiving twice. |