CS457 Syllabus & Progress

SQL: SELECT...

The SQL SELECT Command.
- Select command:

The BASIC form of the SELECT Command.

The basic form of the SELECT command is the SELECT-FROM-WHERE block:


   SELECT attribute_list        
   FROM   relation_list
   WHERE  boolean_expression

where:

attribute_list = a list of attributes taken from the relations in the relation_list
relation_list = a list of relations.
boolean_expression = a condition that each tuple in the cartesian product must satisfy

The meaning of the SQL SELECT command:

The SQL SELECT command:


   SELECT attr1, attr2, ...., attrM      
   FROM   R1, R2, R3, ..., RN
   WHERE  boolean_expression

is processed as follows:

Compute the Cartesian product of the relations R1 × R2 × R3 × ... × RN
Select all the tuples in R1 × R2 × R3 × ... × RN that satisfy the condition boolean_expression
Project out the attributes attr1, attr2, ...., attrM from the qualifying tuples in step 2

Examples.

Example 1: List SSN, Lname and DNO of all employees


  select ssn, lname, dno 
  from   employee


  Output:

     SSN       LNAME             DNO
     --------- ---------- ----------
     123456789 Smith               5     
     333445555 Wong                5
     999887777 Zelaya              4
     987654321 Wallace             4
     666884444 Narayan             5
     453453453 English             5
     987987987 Jabbar              4
     888665555 Borg                1

Example 2: List dnumber and dname of all departments


   select dnumber, dname
   from   department


  Output:

        DNUMBER DNAME
     ---------- ---------------     
              5 Research
	      4 Administration
	      1 Headquarters

Example 3: specifying 2 relations (employee and department) in the relation list


 Query:

    select ssn, lname, dno, dnumber, dname
    from employee, department


 Output:

    SSN       LNAME             DNO    DNUMBER DNAME
    --------- ---------- ---------- ---------- ---------------     
    123456789 Smith               5          5 Research
    333445555 Wong                5          5 Research
    999887777 Zelaya              4          5 Research
    987654321 Wallace             4          5 Research
    666884444 Narayan             5          5 Research
    453453453 English             5          5 Research
    987987987 Jabbar              4          5 Research
    888665555 Borg                1          5 Research
    123456789 Smith               5          4 Administration
    333445555 Wong                5          4 Administration
    999887777 Zelaya              4          4 Administration
    987654321 Wallace             4          4 Administration
    666884444 Narayan             5          4 Administration
    453453453 English             5          4 Administration
    987987987 Jabbar              4          4 Administration
    888665555 Borg                1          4 Administration
    123456789 Smith               5          1 Headquarters
    333445555 Wong                5          1 Headquarters
    999887777 Zelaya              4          1 Headquarters
    987654321 Wallace             4          1 Headquarters
    666884444 Narayan             5          1 Headquarters
    453453453 English             5          1 Headquarters
    987987987 Jabbar              4          1 Headquarters
    888665555 Borg                1          1 Headquarters

Observation:

The output is the cartesian product of the two relations employee × department

Example 4: applying a condition on the tuples in the cartesian product


 Query:

    select ssn, lname, dno, dnumber, dname
    from employee, department
    where dno = dnumber


 Output:

     SSN       LNAME             DNO    DNUMBER DNAME
     --------- ---------- ---------- ---------- ---------------    
     888665555 Borg                1          1 Headquarters
     999887777 Zelaya              4          4 Administration
     987654321 Wallace             4          4 Administration
     987987987 Jabbar              4          4 Administration
     123456789 Smith               5          5 Research
     453453453 English             5          5 Research
     666884444 Narayan             5          5 Research
     333445555 Wong                5          5 Research

Notice that:

Each tuple (row) in the output satisfies the following condition:

dno = dnumber (as according to the where condition)

So the SQL query:

select .... from employee, department where dno = dnumber

performs this operation in Relational Algebra:

σ_dno=dnumber ( employee × department ) = employee ⋈_dno=dnumber department