SELECT Examples (Cont.)
Some of SELECT examples are given next (cont.):
- The difference between
RandSis 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 );
- Let
Rbe a relational schema with attributesA1, ...,An,B1, ...,BmandSa relational schema with attributesB1, ...,Bm,C1, ...,Cl.
Then the natural join of RandS, which joins those tuples fromRandSwhich have equalB-values, is expressed bySELECT 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) PRIMARY KEY, 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;
Since attributes from different relational schemata may have identical names, the dot-notationR.Bis used to specify which occurrence of the respective attribute is meant. With respect to the natural join, of course, we could have usedS.Bas well. TheWHEREclause then explicitly states that tuples from the different relations must have identical values with respect to the shared attributes.
A simpler formulation of the natural join is on the right: SELECT * FROM R NATURAL JOIN S;
Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.
|
Result:
|
The Database includes:
|
|
The Database includes:
|