1

CSE 480: Database Systems

Lecture 11: SQL

2

SQL Query

SELECT <attribute list>FROM <table list>WHERE <condition that must be satisfied by a tuple>

– In MySQL, FROM and WHERE clauses are optional

– Example:

3

SELECT Clause

SELECT <attribute list>

Attribute list may include– * (wildcard)

– Keywords such as AS and DISTINCT

– Arithmetic expression

– String functions

– Boolean expression

4

Wildcard(*) in SELECT-Clause

Retrieve the values for all columns of the selected tuples

5

AS in SELECT-Clause

Rename the columns in query result

Example: Find the names of employees who earn more than their supervisors

SELECT E.FNAME AS EMP_FNAME, E.LNAME AS EMP_LNAMEFROM EMPLOYEE E, EMPLOYEE SWHERE E.SUPERSSN=S.SSN AND E.SALARY > S.SALARY;

EMP_FNAME EMP_LNAMEOutput:

6

DISTINCT in SELECT-Clause

Eliminate duplicate tuples in query result

7

Arithmetic Expression in SELECT-Clause

Query: Show the effect of giving employees a 10% raise

8

String functions in SELECT-Clause

9

String functions in SELECT-Clause

10

String functions in SELECT-Clause

For more string functions, go to http://dev.mysql.com/doc/refman/4.1/en/string-functions.html

11

Boolean Expression in SELECT-Clause

12

FROM Clause

SELECT <attribute list>

FROM <table list>

Table list may include– Names of 1 or more tables

– Subquery for joined tables

13

Joined Relations in FROM-Clause

Query 1: Retrieve the name and address of all employees who work for the 'Research' department

SELECT Fname, Lname, AddressFROM EMPLOYEE, DEPARTMENT WHERE Dname='Research' AND Dno=Dnumber

is equivalent to:

SELECT Fname, Lname, AddressFROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)WHERE Dname='Research'

14

Joined Relations in FROM-Clause

Many types of Join– regular JOIN

– NATURAL JOIN

– CROSS JOIN

– LEFT OUTER JOIN

– RIGHT OUTER JOIN,

– etc

15

JOIN

Id Name111 John222 Mary333 Bill444 Joe

Id Value 111 B 111 A 222 A

RRSS

SELECT *SELECT *FROM R JOIN S on R.Id = S. IdFROM R JOIN S on R.Id = S. Id

foreach row r in table R foreach row s in table S if r.Id = s.Id then

output the merged row of r and s

Id Name Id Value111 John 111 B111 John 111 A222 Mary 222 A

16

JOIN – Example

Id Name Addr Status111 John ….. …..222 Mary ….. …..333 Bill ….. …..444 Joe ….. …..

StudId CrsCode Sem Grade 111 CSE305 S00 B 111 CSE306 S99 A 222 CSE304 F99 A

StudentStudent TranscriptTranscript

Id Name Addr Status StudId CrsCode Sem Grade111 John ….. ….. 111 CSE305 S00 B111 John ….. ….. 111 CSE306 S99 A222 Mary ….. ….. 222 CSE305 F99 A

SELECT * FROM Student JOIN Transcript on Id = StudIdSELECT * FROM Student JOIN Transcript on Id = StudId

Produces columns (attributes) with identical values, which is redundant

17

Natural Join

Join condition equates all attributes with the same name Duplicate columns are automatically eliminated from result

Id Name111 John222 Mary333 Bill444 Joe

Id Value 111 B 111 A 222 A

RRSS

SELECT *SELECT *FROM R NATURAL JOIN SFROM R NATURAL JOIN S

foreach row r in table R foreach row s in table S Examine their common attributes If their values are the same, then merge the rows while removing their duplicate columns

Id Name Value111 John B111 John A222 Mary A

18

Natural Join

Id Name Addr Status111 John ….. …..222 Mary ….. …..333 Bill ….. …..444 Joe ….. …..

Id CrsCode Sem Grade111 CSE305 S00 B111 CSE306 S99 A222 CSE304 F99 A

StudentStudent Transcript2Transcript2

Id Name Addr Status CrsCode Sem Grade111 John ….. ….. CSE305 S00 B111 John ….. ….. CSE306 S99 A222 Mary ….. ….. CSE305 F99 A

SELECT * FROM Student NATURAL JOIN Transcript2SELECT * FROM Student NATURAL JOIN Transcript2

Duplicate attribute (Id) was removed

Join attribute must have the same name (Id)

19

Cross Join

Id Name111 John222 Mary333 Bill444 Joe

Id Value 111 B 111 A 222 A

RRSS

SELECT *SELECT *FROM R CROSS JOIN SFROM R CROSS JOIN S

foreach row r in table R foreach row s in table S output the merged row r and s

Id Name Id Value111 John 111 B111 John 111 A111 John 222 A… … … …444 Joe 222 A

Output has 12 rowsOutput has 12 rows

20

Left Outer Join

Id Name111 John222 Mary333 Bill444 Joe

Id Value 111 B 111 A 222 A

RRSS

SELECT *SELECT *FROM R LEFT OUTER JOIN SFROM R LEFT OUTER JOIN S ON R.Id = S.IdON R.Id = S.Id

foreach row r in table R foreach row s in table S. if r.Id = S.Id then output the merged row r and s if row r is not merged with any rows in S output row r with NULL values for s

Id Name Id Value111 John 111 B111 John 111 A222 Mary 222 A333 Bill NULL NULL444 Joe NULL NULL

Output has 5 rowsOutput has 5 rows

21

Left Outer Join - Example

Retrieve the name of all employees and their supervisors

SELECT E.Fname, E.Lname, S.Fname, S.LnameFROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S ON E.SUPERSSN=S.SSN)

22

Right Outer Join

Id Name111 John222 Mary333 Bill444 Joe

Id Value 111 B 111 A 555 A

RRSS

SELECT *SELECT *FROM R RIGHT OUTER JOIN SFROM R RIGHT OUTER JOIN S ON R.Id = S.IdON R.Id = S.Id

foreach row s in table S foreach row r in table R if r.Id = S.Id then output the merged row r and s if row s is not merged with any rows in R output row s with NULL values for r

Id Name Id Value111 John 111 B111 John 111 ANULL NULL 555 A

Output has 3 rowsOutput has 3 rows

23

WHERE <condition>

Selection condition is a Boolean expression– Simple selection condition:

<attribute> operator <constant> <attribute> operator <attribute> <attribute> operator <set> or <attribute> operator <relation>

– Complex conditions: <condition> AND <condition> <condition> OR <condition> NOT <condition>

WHERE-Clause

24

<attribute> operator <constant> <attribute> operator <attribute>

– Operator: =, >, <, >=, <=, <> (not equal to)

– Applicable to integers, floats, strings, dates, etc. (except for NULL)

SELECT *FROM EMPLOYEEWHERE SSN > SUPERSSN AND SALARY > 49999.99

AND MINIT='B' AND LNAME='SMITH' AND BDATE >= '1980-01-01';

Boolean Expression in WHERE-Clause

25

Substring Comparison in WHERE-Clause

Find employees who live in “Houston, TX”.

Use the LIKE operator to compare partial strings

Two reserved characters are used: – % matches an arbitrary number of characters

– _ matches a single arbitrary character

26

Substring Comparison

Query: Retrieve all employees whose address is in Houston, Texas.

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE ADDRESS LIKE '%Houston, TX

%‘;

27

Substring Comparison

Query: Retrieve all employees who were born during the 1950s.

Q26: SELECT FNAME, LNAMEFROM EMPLOYEEWHERE BDATE LIKE '195_ - _ _ - _

_‘;

28

Arithmetic Expression in WHERE-Clause

Query: Retrieve the names of employees who earn more than half the salary of their supervisors

SELECT E.FName, E.LnameFROM EMPLOYEE AS E, EMPLOYEE AS SWHERE E.Salary > S.Salary/2

AND E.SuperSSN = S.SSN;

29

Arithmetic Expression in WHERE-Clause

Between comparison operator

Query: Retrieve the first and last names of employees in department 5 whose salary is between $30,000 and $40,000.

SELECT Fname, LnameFROM EMPLOYEEWHERE (Salary BETWEEN 30000 AND 40000)

AND Dno = 5

30

UNSPECIFIED WHERE-clause

If there is only one relation in the FROM-clause and there is no join condition, this implies all tuples of the relation are selected

SELECT SSNFROM EMPLOYEE;

If more than one relation is specified in the FROM-clause and there is no join condition, then the CARTESIAN PRODUCT (Cross Join) of tuples is selected

SELECT SSN, DNAMEFROM EMPLOYEE, DEPARTMENT;

31

NULLS IN SQL QUERIES

Cannot use equality (=) comparison to check for null values

Query: Retrieve the names of employees who do not have supervisors

Query: Retrieve the names of employees who have supervisors

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE SUPERSSN IS NULL;

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE SUPERSSN IS NOT NULL;

32

SET OPERATIONS

SQL has directly incorporated some set operations– UNION

– INTERSECT

– MINUS or EXCEPT

The resulting relations of these set operations are sets of tuples; duplicate tuples are eliminated from the result

The set operations are applicable to union compatible relations– the two SQL relations must have the same attributes and the attributes

must appear in the same order

Note: MySQL supports only UNION; Oracle supports all 3 set operations

33

Example in Oracle

Duplicate rows are eliminated

Not available in MySQL

34

Example

Query: List the names of projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project

35

Example

Query: List the names of projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project

(SELECT PNAMEFROM PROJECT, DEPARTMENT, EMPLOYEEWHERE DNUM=DNUMBER AND MGRSSN=SSN

AND LNAME='Smith')UNION

(SELECT PNAMEFROM PROJECT, WORKS_ON, EMPLOYEEWHERE PNUMBER=PNO AND ESSN=SSN

AND NAME='Smith');

36

Example

Query: List the first name and last name of employees who do not work on any project

37

Example

Query: List the first name and last name of employees who do not work on any project

(SELECT Fname, LnameFROM EMPLOYEE)

MINUS

(SELECT Fname, LnameFROM WORKS_ON, EMPLOYEEWHERE ESSN=SSN);

Caution: Not applicable in MySQL (see slide 40 on how to write this query in MySQL)

38

IN Operator

v IN W

The comparison operator IN compares a value v with a set of values W, and evaluates to TRUE if v is one of the elements in W. This is SET membership test.

Examples:– 3 in {1, 2, 3} TRUE

– 0 in {1, 2, 3} FALSE

39

IN Operator

Query: Retrieve the social security numbers of all employees who work on project number 1, 2, or 3

SELECT DISTINCT ESSNFROM WORKS_ONWHERE (PNO = 1) OR (PNO = 2) OR (PNO =

3);

Using IN Operator:

SELECT DISTINCT ESSNFROM WORKS_ONWHERE PNO IN (1, 2, 3);

40

Example

Query: List the first name and last name of employees who do not work on any project

SELECT Fname, LnameFROM EMPLOYEEWHERE SSN NOT IN

(SELECT ESSN FROM WORKS_ON);

41

Exercise

Find the names of supervisors who are also managers Find the names of employees who are not supervisors Find the names of supervisors who supervise exactly one

employee

Try to write it in two ways:– Using IN operator

– Using SET operations (UNION, INTERSECT, MINUS)