The SELECT command

  • Select command:

    • The SQL SELECT command is used to retrieve the set of tuples that satisfy a given condition.

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

       SELECT attribute_list        
   FROM   relation_list
 [ WHERE  boolean_expression ]          // Optional

  • The general form of the SELECT command contains the GROUP-BY and HAVING clauses: 

       SELECT   attribute_list        
   FROM     relation_list
 [ WHERE    boolean_expression ] 
 [ GROUP BY grouping_attrs ] 
 [ HAVING   group_condition ]

The basic 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.

      • The cartesian product ( click here ) of the relations is computed.

    • boolean_expression = a condition that each tuple in the cartesian product must satisfy (a.k.a.: tuple condition)

Meaning of the basic SELECT command

  • The SQL SELECT command: 

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

    is processed as follows:

    1. Compute the Cartesian product of the relations R1 × R2 × R3 × ... × RN

    2. Select all the tuples in R1 × R2 × R3 × ... × RN that satisfy the condition boolean_expression

    3. Project out the attributes attr1, attr2, ...., attrM   from the qualifying tuples in step 2

Example of the SELECT command with 1 input relation

  • Example 1: retrieve 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 of the SELECT command with 2 input relation (cartesian product) 

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



    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

Example of the SELECT command with 2 input relation with a condition (join) 

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



     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