Slide 15.3: SQL SELECT statement (cont.)

Slide 15.2: SQL SELECT statement
Slide 15.4: SQL SELECT examples
Home Print version

SQL SELECT Statement (Cont.)

SELECT Examples (Cont.)
Some of SELECT examples are given next (cont.):

The difference between 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 );

Let R be a relational schema with attributes A₁, ..., A_n, B₁, ..., B_m and S a relational schema with attributes B₁, ..., B_m, C₁, ..., C_l.

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) 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-notation R.B is used to specify which occurrence of the respective attribute is meant. With respect to the natural join, of course, we could have used S.B as well. The WHERE clause 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:

Tablename	Record
Customers	91
Categories	8
Employees	10
OrderDetails	518
Orders	196
Products	77
Shippers	3
Suppliers	29

W3C WebSQL

WebSQL Browser Support