SELECT
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 1
Basic Retrieval Queries in SQL
SELECT statement
One basic statement for retrieving information from
a database
SQL allows a table to have two or more tuples
that are identical in all their attribute values
Unlike relational model (relational model is strictly
set-theory based)
Multiset or bag behavior
Tuple-id may be used as a key
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 2
EXPANDED Block Structure of SQL
Queries
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 40
The SELECT-FROM-WHERE
Structure of Basic SQL Queries
Basic form of the SELECT statement:
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 4
The SELECT-FROM-WHERE Structure
of Basic SQL Queries (cont’d.)
Logical comparison operators
=, <, <=, >, >=, and <>
Projection attributes
Attributes whose values are to be retrieved
Selection condition
Boolean condition that must be true for any
retrieved tuple. Selection conditions include join
conditions (see Ch.8) when multiple relations are
involved.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 30
Basic Retrieval Queries
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 31
Basic Retrieval Queries (Contd.)
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 32
Ambiguous Attribute Names
Same name can be used for two (or more)
attributes in different relations
As long as the attributes are in different relations
Must qualify the attribute name with the relation
name to prevent ambiguity
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 33
Aliasing, and Renaming
Aliases or tuple variables
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.
SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;
Recommended practice to abbreviate names and to
prefix same or similar attribute from multiple tables.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 34
Aliasing,Renaming and Tuple
Variables (contd.)
The attribute names can also be renamed
EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd,
Addr, Sex, Sal, Sssn, Dno)
Note that the relation EMPLOYEE now has a
variable name E which corresponds to a tuple
variable
The “AS” may be dropped in most SQL
implementations
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 10
Unspecified WHERE Clause
and Use of the Asterisk
Missing WHERE clause
Indicates no condition on tuple selection
Effect is a CROSS PRODUCT
Result is all possible tuple combinations (or the
Algebra operation of Cartesian Product– see Ch.8)
result
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 36
Unspecified WHERE Clause
and Use of the Asterisk (cont’d.)
Specify an asterisk (*)
Retrieve all the attribute values of the selected
tuples
The * can be prefixed by the relation name; e.g.,
EMPLOYEE *
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 37
Tables as Sets in SQL
SQL does not automatically eliminate duplicate tuples in
query results
For aggregate operations (See sec 7.1.7) duplicates must
be accounted for
Use the keyword DISTINCT in the SELECT clause
Only distinct tuples should remain in the result
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 38
Tables as Sets in SQL (cont’d.)
Set operations
UNION, EXCEPT (difference), INTERSECT
Corresponding multiset operations: UNION ALL,
EXCEPT ALL, INTERSECT ALL)
Type compatibility is needed for these operations
to be valid
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 39
Substring Pattern Matching and
Arithmetic Operators
LIKE comparison operator
Used for string pattern matching
% replaces an arbitrary number of zero or more characters
underscore (_) replaces a single character
Examples: WHERE Address LIKE ‘%Houston,TX%’;
WHERE Ssn LIKE ‘_ _ 1_ _ 8901’;
BETWEEN comparison operator
E.g., in Q14 :
WHERE(Salary BETWEEN 30000 AND 40000)
AND Dno = 5;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 40
Arithmetic Operations
Standard arithmetic operators:
Addition (+), subtraction (–), multiplication (*), and
division (/) may be included as a part of SELECT
Query 13. Show the resulting salaries if every employee working on
the ‘ProductX’ project is given a 10 percent raise.
SELECT E.Fname, E.Lname, 1.1 * E.Salary AS Increased_sal
FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE E.Ssn=W.Essn AND W.Pno=P.Pnumber AND
P.Pname=‘ProductX’;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 16
Ordering of Query Results
Use ORDER BY clause
Keyword DESC to see result in a descending order
of values
Keyword ASC to specify ascending order explicitly
Typically placed at the end of the query
ORDER BY D.Dname DESC, E.Lname ASC,
E.Fname ASC
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 17
Basic SQL Retrieval Query Block
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 43
More Complex SQL Retrieval
Queries
Additional features allow users to specify more
complex retrievals from database:
Nested queries, joined tables, and outer joins (in
the FROM clause), aggregate functions, and
grouping
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 4
Comparisons Involving NULL
and Three-Valued Logic
Meanings of NULL
Unknown value
Unavailable or withheld value
Not applicable attribute
Each individual NULL value considered to be
different from every other NULL value
SQL uses a three-valued logic:
TRUE, FALSE, and UNKNOWN (like Maybe)
NULL = NULL comparison is avoided
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 5
Comparisons Involving NULL
and Three-Valued Logic (cont’d.)
SQL allows queries that check whether an
attribute value is NULL
IS or IS NOT NULL
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 7
Nested Queries, Tuples,
and Set/Multiset Comparisons
Nested queries
Complete select-from-where blocks within WHERE
clause of another query
Outer query and nested subqueries
Comparison operator IN
Compares value v with a set (or multiset) of values
V
Evaluates to TRUE if v is one of the elements in V
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 8
Nested Queries (cont’d.)
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 9
Nested Queries (cont’d.)
Use tuples of values in comparisons
Place them within parentheses
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 10
Nested Queries (cont’d.)
Use other comparison operators to compare a
single value v
= ANY (or = SOME) operator
Returns TRUE if the value v is equal to some value in
the set V and is hence equivalent to IN
Other operators that can be combined with ANY (or
SOME): >, >=, <, <=, and <>
ALL: value must exceed all values from nested
query
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 11
Nested Queries (cont’d.)
Avoid potential errors and ambiguities
Create tuple variables (aliases) for all tables
referenced in SQL query
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 26
Correlated Nested Queries
Queries that are nested using the = or IN
comparison operator can be collapsed into one
single block: E.g., Q16 can be written as:
Q16A: SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E, DEPENDENT AS D
WHERE E.Ssn=D.Essn AND E.Sex=D.Sex
AND
E.Fname=D.Dependent_name;
Correlated nested query
Evaluated once for each tuple in the outer query
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 13
Explicit Sets and Renaming of
Attributes in SQL
Can use explicit set of values in WHERE clause
Q17: SELECT DISTINCT Essn
FROM WORKS_ON
WHERE Pno IN (1, 2, 3);
Use qualifier AS followed by desired new name
Rename any attribute that appears in the result of
a query
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 18
Specifying Joined Tables in the
FROM Clause of SQL
Joined table
Permits users to specify a table resulting from a
join operation in the FROM clause of a query
The FROM clause in Q1A
Contains a single joined table. JOIN may also be
called INNER JOIN
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 19
Different Types of JOINed Tables in
SQL
Specify different types of join
NATURAL JOIN
Various types of OUTER JOIN (LEFT, RIGHT,
FULL )
NATURAL JOIN on two relations R and S
No join condition specified
Is equivalent to an implicit EQUIJOIN condition for
each pair of attributes with same name from R and
S
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 20
NATURAL JOIN
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 EMPLOYEE.Dno = DEPT.Dno as an
implicit join condition
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 31
INNER and OUTER Joins
INNER JOIN (versus OUTER 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 result
If no matching tuple
Padded with NULL values for attributes of right table
RIGHT OUTER JOIN
Every tuple in right table must appear in result
If no matching tuple
Padded with NULL values for attributes of left table
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 22
Example: LEFT OUTER JOIN
SELECT E.Lname AS Employee_Name
S.Lname AS Supervisor_Name
FROM Employee AS E LEFT OUTER JOIN EMPLOYEE AS S
ON E.Super_ssn = S.Ssn)
ALTERNATE SYNTAX:
SELECT E.Lname , S.Lname
FROM EMPLOYEE E, EMPLOYEE S
WHERE E.Super_ssn + = S.Ssn
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 33
Multiway JOIN in the FROM clause
FULL OUTER JOIN – combines result if LEFT
and RIGHT OUTER JOIN
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’;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 34
Aggregate Functions in SQL
Used to summarize information from multiple
tuples into a single-tuple summary
Built-in aggregate functions
COUNT, SUM, MAX, MIN, and AVG
Grouping
Create subgroups of tuples before summarizing
To select entire groups, HAVING clause is used
Aggregate functions can be used in the SELECT
clause or in a HAVING clause
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 35
Renaming Results of Aggregation
Following query returns a single row of computed values
from EMPLOYEE table:
Q19: SELECT SUM (Salary), MAX (Salary), MIN (Salary),
AVG (Salary)
FROM EMPLOYEE;
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;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 36
Aggregate Functions in SQL (cont’d.)
NULL values are discarded when aggregate
functions are applied to a particular column
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 37
Aggregate Functions on Booleans
SOME and ALL may be applied as functions on
Boolean Values.
SOME returns true if at least one element in the
collection is TRUE (similar to OR)
ALL returns true if all of the elements in the
collection are TRUE (similar to AND)
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 38
Grouping: The GROUP BY Clause
Partition relation into subsets of tuples
Based on grouping attribute(s)
Apply function to each such group independently
GROUP BY clause
Specifies grouping attributes
COUNT (*) counts the number of rows in the
group
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 39
Examples of GROUP BY
The grouping attribute must appear in the SELECT clause:
Q24: SELECT Dno, COUNT (*), AVG (Salary)
FROM EMPLOYEE
GROUP BY Dno;
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)
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;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 40
Grouping: The GROUP BY and
HAVING Clauses (cont’d.)
HAVING clause
Provides a condition to select or reject an entire
group:
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;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 31
Combining the WHERE and the
HAVING Clause
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.
INCORRECT QUERY:
SELECT Dno, COUNT (*)
FROM EMPLOYEE
WHERE Salary>40000
GROUP BY Dno
HAVING COUNT (*) > 5;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 42
Combining the WHERE and the
HAVING Clause (continued)
Correct Specification of the Query:
Note: the WHERE clause applies tuple by tuple
whereas HAVING applies to entire group of tuples
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 43
