CS457 Syllabus & Progress

## SQL: SELECT...

SELECT is SQL's query command...

• 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

• Examples - More Interesting Queries.

1. Find the birthday and address of Alicia

 ``` SELECT bdate, address FROM employee WHERE fname = 'Alicia'; ```

2. Find the name and address of employees working in the 'Research' department

 ``` SELECT fname, lname, address FROM employee, department WHERE dno = dnumber AND dname = 'Research'; ```

• The following is the equivalence query in Relational Algebra:

 πfname, lname, address ( σ(dname='Research' and dnumber = dno) ( employee × department )   )

You can clearly see the correspondence between SQL and Relational Algebra....

3. Find the name of employees in the 'Research' department who earn over \$30,000

 ``` SELECT fname, lname FROM employee, department WHERE dno = dnumber AND dname = 'Research' AND salary > 30000; ```

4. Find the ssn of employees who work on the project 'ProductX'

 ``` SELECT essn FROM works_on, project WHERE pno = pnumber AND pname = 'ProductX' ```

5. Find the name of employees who work on the project 'ProductX'

 ``` SELECT fname, lname FROM employee, works_on, project WHERE ssn = essn AND pno = pnumber AND pname = 'ProductX' ```

6. For the projects located in 'Stafford', find the name of the project, the name of the controlling department, the last name of the department's manager, his address and birthdate

 ``` Projects in 'Stafford' can be found as follows: SELECT pnumber, pname, plocation FROM project WHERE plocation = 'Stafford'; The controlling department of these projects is added as follows: SELECT pnumber, pname, dname, mgrssn FROM project, department WHERE dnum = dnumber AND plocation = 'Stafford'; Finally, the manager information is added as follows: SELECT pname, dname, lname, address bdate FROM project, department, employee WHERE dnum = dnumber AND mgrssn = ssn AND plocation = 'Stafford'; ```

• DISTINCT.

• SQL outputs by default duplicate values, for example:

 ``` SELECT name NAME FROM dependent ---------- Alice Abner Alice Elizabeth Joy Micheal Theodore ```

• Use the DISTINCT keyword in the SELECT clause to remove duplicate values:

 ``` SELECT DISTINCT name NAME FROM dependent ---------- Alice Theodore Joy Abner Micheal Elizabeth ```

• NOTE: use DISTINCT with caution - it requires SQL to sort the tuples (heavy duty processing)

• The * SELECTOR.

• The SELECT * selects all attributes in all relations in the FROM clause

• For example:

 ``` SELECT * FROM department WHERE dname = 'Research'; Output: DNAME DNUMBER MGRSSN MGRSTARTDA --------------- ---------- --------- ---------- Research 5 333445555 22-MAY-78 ```