SQL

Fundamentals of Database Systems

7th Edition - mostly Chapter 6, a little Chapter 7
6th Edition - mostly Chapter 4, a little Chapter 5

Chapters 6 and 7 Outline

n DDL general overview

n Retrieval Queries in SQL

n Additional Features of SQL

n More Complex SQL Retrieval Queries

n Views (Virtual Tables) in SQL

Slide 2

2
 Basic SQL
n SQL language
n Considered one of the major reasons for the commercial success
of relational databases

n SQL
n SQL Actually comes from the word “SEQUEL” which was the original
term used in the paper: “SEQUEL TO SQUARE” by Chamberlin and
Boyce. IBM could not copyright that term, so they abbreviated to SQL and
copyrighted the term SQL.

n Now popularly known as “Structured Query language”.

n SQL is an informal or practical rendering of the relational data

model with syntax

Slide 3

SQL Standards
§ SQL has gone through many standards: starting with
§ SQL-86 or SQL 1.A.
§ SQL-92 is referred to as SQL-2.

§ Later standards (from SQL-1999) are divided into core specification and
specialized extensions. The extensions are implemented for different
applications – such as data mining, data warehousing, multimedia etc.

§ SQL-2006 added XML features;

§ In 2008 Object-oriented features were added.

§ SQL-3 is the current standard which started with SQL-1999.

§ It is not fully implemented in any RDBMS.

Slide 4

4
 SQL CREATE TABLE Data Definition Statements
(Company Schema)

(Dept_create_date Date)

CHECK(Dept_create_date <= Mgr_start_date);

CHECK(Dlocation in (Calgary’, Edmonton’, Toronto'))

Basic Retrieval Queries in SQL

n SELECT statement
n One basic statement for retrieving information from a database

n SQL allows a table to have two or more tuples that are identical in all
their attribute values

n Unlike relational model (relational model is strictly set-theory based)

n Multiset or bag behavior

n Tuple-id may be used as a key

Slide 6

6
 The SELECT-FROM-WHERE Structure of Basic
SQL Queries

n Basic form of the SELECT statement:

Slide 7

The SELECT-FROM-WHERE Structure of Basic

SQL Queries (cont’d.)

n Logical comparison operators

n =, <, <=, >, >=, and <>

n Projection attributes
n Attributes whose values are to be retrieved

n Selection condition
n Boolean condition that must be true for any retrieved tuple.

n Selection conditions include join conditions (as in relational

algebra) when multiple relations are involved.

Slide 8

8
 Simple SQL Query

Product PName Price Category Manufacturer

Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT PName, Price, Manufacturer

FROM Product
WHERE Price > 100

PName Price Manufacturer

“selection” and SingleTouch $149.99 Canon
“projection” MultiTouch $203.99 Hitachi

Slide 9

Notation

Input Schema

Product(PName, Price, Category, Manfacturer)

SELECT PName, Price, Manufacturer

FROM Product
WHERE Price > 100

Answer(PName, Price, Manfacturer)

Output Schema
Slide 10

10
 Basic Retrieval Queries

Slide 11

11

Basic Retrieval Queries (Contd.)

Slide 12

12
 Ambiguous Attribute Names

n Same name can be used for two (or more) attributes in different
relations

n As long as the attributes are in different relations

n Must qualify the attribute name with the relation name to prevent
ambiguity

Slide 13

13

Aliasing, and Renaming

n Aliases or tuple variables
n Declare alternative relation names E and S to refer to the EMPLOYEE
relation twice in a query:

Query 8. For each employee, retrieve the employee’s first and last name and the first and last name of
his or her immediate supervisor.

n SELECT [Link], [Link], [Link], [Link]

FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=[Link];

n Recommended practice to abbreviate names and

to prefix same or similar attribute from multiple tables.

Slide 14

14
 Aliasing, Renaming and Tuple Variables (contd.)

n The attribute names can also be renamed

EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd, Addr, Sex, Sal,

Sssn, Dno)

n Note that the relation EMPLOYEE now has a variable name E

which corresponds to a tuple variable

n The “AS” may be dropped in most SQL implementations

Slide 15

15

Unspecified WHERE Clause

and Use of the Asterisk
n Missing WHERE clause
n Indicates no condition on tuple selection

n Effect is a CROSS PRODUCT

n Result is all possible tuple combinations (or the Algebra operation of
Cartesian Product) result

Slide 16

16
 Unspecified WHERE Clause
and Use of the Asterisk (cont’d.)

n Specify an asterisk (*)

n Retrieve all the attribute values of the selected tuples

n The * can be prefixed by the relation name; e.g., EMPLOYEE *

Slide 17

17

Tables as Sets in SQL

n SQL does not automatically eliminate duplicate tuples in query results

n For aggregate operations (See sec 7.1.7) duplicates must be

accounted for

n Use the keyword DISTINCT in the SELECT clause

n Only distinct tuples should remain in the result

Slide 18

18
 Tables as Sets in SQL (cont’d.)
n Set operations
n UNION, EXCEPT (difference), INTERSECT

n Corresponding multiset operations: UNION ALL, EXCEPT ALL, INTERSECT ALL)

n Type compatibility is needed for these operations to be valid

Slide 19

19

Set Operations

n Find departments located in Calgary or Edmonton

(select Dnumber from Dept_Location where Dlocation = ‘Calgary')
union
(select Dnumber from Dept_Location where Dlocation = ‘Edmonton’)

n Find departments located in Calgary and Edmonton

(select Dnumber from Dept_Location where Dlocation = ‘Calgary')
intersect
(select Dnumber from Dept_Location where Dlocation = ‘Edmonton’)

n Find departments located in Calgary but not in Edmonton

(select Dnumber from Dept_Location where Dlocation = ‘Calgary')
except
(select Dnumber from Dept_Location where Dlocation = ‘Edmonton’)
Slide 20

20
 Substring Pattern Matching and Arithmetic
Operators

n LIKE comparison operator

n Used for string pattern matching

n % replaces an arbitrary number of zero or more characters
n Examples: WHERE Address LIKE ‘%Houston,TX%’;

n underscore (_) replaces a single character

n Examples: WHERE Ssn LIKE ‘_ _ 1_ _ 8901’;

n BETWEEN comparison operator

E.g., in Q14 :
WHERE(Salary BETWEEN 30000 AND 40000)
AND Dno = 5;

Slide 21

21

Arithmetic Operations

n Standard arithmetic operators:

n Addition (+), subtraction (–), multiplication (*), and division (/)
may be included as a part of SELECT

n Query 13. Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10
percent raise.

SELECT [Link], [Link], 1.1 * [Link] AS Increased_sal

FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE [Link]=[Link] AND [Link]=[Link] AND [Link]=‘ProductX’;

Slide 22

22
 The INSERT Command

§ Specify the table name and a list of values for the tuple

§ Order of values in the list MUST BE the same as the order in the table definition

§ The variation below inserts multiple tuples where a new table is loaded with
values from the result of a query.

Slide 23

23

The DELETE Command

§ Removes tuples from a relation

§ Includes a WHERE clause to select the tuples to be deleted.
§ The number of tuples deleted will vary.

Slide 24

24
 The UPDATE Command

§ Example: Change the location and controlling department number of project number
10 to Calgary' and 5, respectively

UPDATE PROJECT
SET Plocation=‘Calgary’, Dnum=4
WHERE Pnumber=10;

§ Example: Give all employees in the 'Research' department a 10% raise in salary.

UPDATE EMPLOYEE
SET SALARY = SALARY *1.1
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research')

Slide 25

25

Ordering of Query Results

n Use ORDER BY clause

n Keyword DESC to see result in a descending order of values

n Keyword ASC to specify ascending order explicitly

n Typically placed at the end of the query

ORDER BY [Link] DESC, [Link] ASC, [Link] ASC

Slide 26

26
Basic SQL Retrieval Query Block

Slide 27

27

Comparisons Involving NULL

and Three-Valued Logic

n SQL allows queries that check whether an attribute value is NULL

n IS or IS NOT NULL

Slide 28

28
 Comparisons Involving NULL
and Three-Valued Logic (cont’d.)

n SQL treats as unknown the result of any comparison involving a null value
(other than predicates is null and is not null).
n Example: 20 > null or null <> null or null = null

n The predicate in a where clause can involve Boolean operations (and, or, not);
thus the definitions of the Boolean operations need to be extended to deal with
the value unknown.
n and : (true and unknown) = unknown,
(false and unknown) = false,
(unknown and unknown) = unknown

n or: (unknown or true) = true,

(unknown or false) = unknown
(unknown or unknown) = unknown

n Result of where clause predicate is treated as false if it evaluates to unknown

Slide 29

29

Nested Queries (cont’d.)

Slide 30

30
 Nested Queries (cont’d.)

n Use tuples of values in comparisons

n Place them within parentheses

Slide 31

31

Nested Queries (cont’d.)

n Use other comparison operators to compare a single value v

n = ANY (or = SOME) operator

n Returns TRUE if the value v is equal to some value in the set V and is
hence equivalent to IN

n Other operators that can be combined with ANY (or SOME): >, >=, <,
<=, and <>
n ALL: value must exceed all values from nested query

Slide 32

32
 Nested Queries (cont’d.)

n Avoid potential errors and ambiguities

n Create tuple variables (aliases) for all tables referenced in SQL query

Slide 33

33

The EXISTS and UNIQUE Functions in SQL for

correlating queries

n EXISTS function
n Check whether the result of a correlated nested query is empty or
not.
n They are Boolean functions that return a TRUE or FALSE result.

n EXISTS and NOT EXISTS

n Typically used in conjunction with a correlated nested query

n SQL function UNIQUE(Q)

n Returns TRUE if there are no duplicate tuples in the result of query Q

Slide 34

34
 USE of EXISTS

Q7:

SELECT Fname, Lname

FROM Employee
WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE Ssn= Essn)

AND EXISTS (SELECT *

FROM Department
WHERE Ssn= Mgr_Ssn)

Slide 35

35

USE OF NOT EXISTS

To achieve the “for all” (universal quantifier- see Ch.8) effect, we use
double negation this way in SQL:

Query: List first and last name of employees who work on ALL projects
controlled by Dno=5.

SELECT Fname, Lname

FROM Employee
WHERE NOT EXISTS ( (SELECT Pnumber FROM PROJECT WHERE Dno=5)
EXCEPT (SELECT Pno FROM WORKS_ON WHERE Ssn= ESsn)
);

The above is equivalent to double negation:

List names of those employees for whom there does NOT exist a project managed
by department no. 5 that they do NOT work on.

Slide 36

36
 Double Negation to accomplish “for all” in SQL

n Q3B: SELECT Lname, Fname

FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM WORKS_ON B
WHERE ( [Link] IN ( SELECT Pnumber
FROM PROJECT
WHERE Dnum=5)
AND

NOT EXISTS (SELECT *

FROM WORKS_ON C
WHERE [Link]=Ssn
AND [Link]=[Link] )));

The above is a direct rendering of:

List names of those employees for whom there does NOT exist a project
managed by department no. 5 that they do NOT work on.

Slide 37

37

Explicit Sets and Renaming of Attributes in SQL

n Can use explicit set of values in WHERE clause

Q17: SELECT DISTINCT Essn

FROM WORKS_ON
WHERE Pno IN (1, 2, 3);

n Use qualifier AS followed by desired new name

n Rename any attribute that appears in the result of a query

Slide 38

38
 Specifying Joined Tables in the FROM Clause
of SQL

n Joined table
n Permits users to specify a table resulting from a join operation in

the FROM clause of a query

n The FROM clause in Q1A

n Contains a single joined table. JOIN may also be called INNER
JOIN

Slide 39

39

Different Types of JOINed Tables in SQL

n Specify different types of join

n NATURAL JOIN

n NATURAL JOIN on two relations R and S

n No join condition specified

n Is equivalent to an implicit EQUIJOIN condition for each pair of

attributes with same name from R and S

Slide 40

40
 NATURAL JOIN

n Rename attributes of one relation so it can be joined with another using

NATURAL JOIN:

Q1B: SELECT Fname, Lname, Address

FROM (EMPLOYEE NATURAL JOIN
(DEPARTMENT AS DEPT (Dname, Dno, Mssn, Msdate)))
WHERE Dname=‘Research’;

The above works with [Link] = [Link] as an implicit join condition

Slide 41

41

Multiway JOIN in the FROM clause

n Can nest JOIN specifications for a multiway join:

Q2A: SELECT Pnumber, Dnum, Lname, Address, Bdate

FROM ((PROJECT JOIN DEPARTMENT ON Dnum=Dnumber)
JOIN EMPLOYEE ON Mgr_ssn=Ssn)
WHERE Plocation=‘Stafford’;

Slide 42

42
 INNER and OUTER Joins
§ INNER JOIN
» Default type of join in a joined table
» Tuple is included in the result only if a matching tuple exists in the
other relation
§ LEFT OUTER JOIN
» Every tuple in left table must appear in the result
» If no matching tuple
• Tuples are added with NULL values for attributes of right table
§ RIGHT OUTER JOIN
» Every tuple in right table must appear in the result
» If no matching tuple
• Tuples are with NULL values for attributes of left table
§ FULL OUTER JOIN – combines the results of LEFT and RIGHT
OUTER JOIN

Slide 43

43

Example: OUTER JOINS

SELECT [Link] AS Employee_Name, [Link] AS Supervisor_Name

FROM Employee AS E LEFT OUTER JOIN EMPLOYEE AS S
ON E.Super_ssn = [Link])

SELECT [Link] AS Employee_Name, [Link] AS Supervisor_Name

FROM Employee AS E RIGHT OUTER JOIN EMPLOYEE AS S
ON E.Super_ssn = [Link])

SELECT [Link] AS Employee_Name, [Link] AS Supervisor_Name

FROM Employee AS E OUTER JOIN EMPLOYEE AS S
ON E.Super_ssn = [Link])

Slide 44

44
 Aggregate Functions in SQL

n Used to summarize information from multiple tuples into a single-tuple

summary
n Built-in aggregate functions
n COUNT, SUM, MAX, MIN, and AVG

n Grouping
n Create subgroups of tuples before summarizing

n To select entire groups, HAVING clause is used

n Aggregate functions can be used in the SELECT clause or in a

HAVING clause

Slide 45

45

Renaming Results of Aggregation

n Following query returns a single row of computed values from

EMPLOYEE table:

Q19: SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)
FROM EMPLOYEE;

n The result can be presented with new names:

Q19A: SELECT SUM (Salary) AS Total_Sal, MAX (Salary) AS Highest_Sal,

MIN (Salary) AS Lowest_Sal, AVG (Salary) AS Average_Sal
FROM EMPLOYEE;

Slide 46

46
 Aggregate Functions in SQL (cont’d.)

n NULL values are discarded when aggregate functions are

applied to a particular column

Slide 47

47

Aggregate Functions on Booleans

n SOME and ALL may be applied as functions on Boolean Values.

n SOME returns true if at least one element in the collection is TRUE

(similar to OR)

n ALL returns true if all of the elements in the collection are TRUE
(similar to AND)

Slide 48

48
 Grouping: The GROUP BY Clause

n Partition relation into subsets of tuples

n Based on grouping attribute(s)

n Apply function to each such group independently

n GROUP BY clause
n Specifies grouping attributes

n COUNT (*) counts the number of rows in the group

Slide 49

49

Examples of GROUP BY

n The grouping attribute must appear in the SELECT clause:

Q24:
SELECT Dno, COUNT (*), AVG (distinct Salary)
FROM EMPLOYEE
GROUP BY Dno;

n If the grouping attribute has NULL as a possible value, then a

separate group is created for the null value (e.g., null Dno in the
above query)

n GROUP BY may be applied to the result of a JOIN:

Q25: SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname;
Slide 50

50
 Grouping: The GROUP BY and HAVING
Clauses (cont’d.)

n HAVING clause
n Provides a condition to select or reject an entire group:

n Query 26. For each project on which more than two employees work, retrieve
the project number, the project name, and the number of employees who
work on the project.

Q26:
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname
HAVING COUNT (*) > 2;

Slide 51

51

Combining the WHERE and the HAVING Clause

n Consider the query: we want to count the total number of employees

whose salaries exceed $40,000 in each department, but only for
departments where more than five employees work.

n INCORRECT QUERY:
SELECT Dno, COUNT (*)
FROM EMPLOYEE
WHERE Salary>40000
GROUP BY Dno
HAVING COUNT (*) > 5;

Slide 52

52
 Combining the WHERE and the HAVING Clause
(continued)

Correct Specification of the Query:

n Note: the WHERE clause applies tuple by tuple whereas HAVING
applies to entire group of tuples

Slide 53

53

Subqueries in the From Clause

n SQL allows a subquery expression to be used in the from clause

n Find the average employees’ salaries of those departments where the average
salary is greater than $42,000.”
select dept_number, avg_salary
from ( select dno as dept_number, avg (salary) as avg_salary
from employee
group by dno)
where avg_salary > 42000;

n Note that we do not need to use the having clause

n Another way to write the above query
select dept_number, avg_salary
from ( select dno, avg (salary)
from employee
group by dno)
as dept_avg (dept_name, avg_salary)
where avg_salary > 42000;
Slide 54

54
 EXPANDED Block Structure of SQL Queries

Slide 55

55

Views (Virtual Tables) in SQL

n Concept of a view in SQL

n Single table derived from other tables called the defining tables

n Considered to be a virtual table that is not necessarily populated

Slide 56

56
 Specification of Views in SQL

n CREATE VIEW command

n Give table name, list of attribute names, and a query to specify the
contents of the view

n In V1, attributes retain the names from base tables. In V2, attributes are
assigned names

Slide 57

57

Views as authorization mechanism

n SQL query authorization statements (GRANT and REVOKE) are

described in detail in Chapter 30

n Views can be used to hide certain attributes or tuples from

unauthorized users

n E.g., For a user who is only allowed to see employee information for
those who work for department 5, he may only access the view
n DEPT5EMP:
CREATE VIEW DEPT5EMP AS
SELECT *
FROM EMPLOYEE
WHERE Dno = 5;

Slide 58

58