Slide 5.19: SQL SELECT statement (cont.)

Slide 5.18: SQL SELECT statement
Slide 5.20: Populating a database
Home

SQL SELECT Statement (Cont.)

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

Let 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;

The selection with respect to condition A = B is expressed by

SELECT DISTINCT * FROM  R  WHERE  A = b

SQL> SELECT DISTINCT  first_name  FROM  student  WHERE  class = student_id;

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),
  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;

Slide 5.18: SQL SELECT statement
Slide 5.20: Populating a database
Home

You don’t need a parachute to go skydiving.
You need a parachute to go skydiving twice.