CS457 Syllabus & Progress

### SQL: SELECT...

• The SQL SELECT Command.

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

• The SELECT command is based on Relational Algebra and you will see the join/section/projection operations reflected in the 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 ```

• attribute_list: a list of attributes from the relations in the relation_list

• relation_list: a list of relations. The CARTESIAN PRODUCT ( click here ) of these relations is formed.

• boolean_expression: you should know what a Boolean expression is, if not, you need to re-take CS170.

• Meaning of the SQL SELECT command:

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

1. First, form the Cartesian product of the relations R1 × R2 × R3 × ... × RN

2. Next, select all the tuples that satisfy the boolean_expression

3. Finally, project the attributes attr1, attr2, ...., attrM   from the qualifying tuples

• 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 ```

• Illustrative Example 3: See what happens when 2 relations "employee" and "department" are specified 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" and "department"

• Example 4: demonstrates how to perform a join operation on "employee" and "department"

 ``` 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:

• For each tuple (row):

 ``` dno = dnumber (as according to the where condition) ```

• In fact, this is what we have learned before in Relational Algebra:

A join operation is a Cartesian product followed by a selection operation

The FROM clause in the SQL command specifies the Cartesian product operation

The WHERE clause in the SQL command specifies the condition of the σ operation