LIKE

1
LIKE

• LIKE allows to select character strings which
  have some element in common by using wild cards
• Wild cards
• means zero, one or more characters
• _ means one single character

2
SELECT Queries Over Several Tables

• Which films name ends in 100?

SELECT Title FROM Film WHERE Title LIKE
100

• Which films name contains the character a, b and
  c in the first, third and fifth positions?

SELECT Title FROM Film WHERE Title LIKE
a_b_c
3
Correlated Subqueries

• Subquery must be re-evaluated for each tuple in
  outer SELECT
• Table variable is used
• Find the customers who live at more than one
  address (assume the key of Customer is
  (CustomerID, Address)).

SELECT Name FROM Customer C WHERE CustomerID
IN (SELECT D.CustomerID FROM
Customer D WHERE C.Address ltgt
D.Address)
4
Multiple Attribute Producing Subquery

• The subquery produces a table with several
  columns
• EXISTS
• true if subquery produced table has a tuple
• NOT EXISTS
• true if subquery produced table is empty

5
EXISTS

• List the customers who live in Dublin and have a
  film reserved.

SELECT NameFROM CustomerWHERE City
Dublin AND EXISTS ( SELECT FROM
Reserved WHERE Reserved.CustomerID
Customer.CustomerID)
6
EXISTS, cont.

• Often, EXISTS can be replaced with another
  correlation name.
• List the customers who live in Dublin and have a
  film reserved.

SELECT NameFROM Customer, ReservedWHERE City
Dublin AND Customer.CustomerID
Reserved.CustomerID
7
NOT EXISTS

• List the customers who live in Dublin but have no
  films reserved.

SELECT NameFROM CustomerWHERE City
Dublin AND NOT EXISTS(SELECT
FROM Reserved WHERE Reserved.CustomerID
Customer.CustomerID)
8
Outline - The SELECT statement

• Single table
• Projection
• Selection
• Multiple tables
• Cartesian product and join
• Set operations
• Subqueries
• Optional clauses
• Ordering results
• Computing aggregates on groups
• Additional joins

9
ORDER BY

• Can sort the result of a select, using ORDER BY.
• Who has reserved a film?

SELECT NameFROM Customer, ReservedWHERE
Customer.CustomerID Reserved.CustomerIDORDER
BY Name

• Can also sort in descending order, via DESC (ASC
  is the default).

SELECT NameFROM Customer, ReservedWHERE
Customer.CustomerID Reserved.CustomerIDORDER
BY Name DESC

• Only columns in the select list can be used for
  the ordering.

10
Select in the From Clause

• The table in a from clause can itself be a select
  statement.
• List the customers who have reserved an expensive
  film.

SELECT CustomerID FROM Reserved,(SELECT
FROM Film WHERE RentalPrice gt 4) AS
Expensive WHERE Reserved.FilmID
Expensive.FilmID

• A correlation name is required in such cases.

11
Aggregates

• Aggregates operate on the set of values of a
  column of a table, and return a single value.
• SUM sum of values
• AVG average value
• MAX maximum value
• MIN minimum value
• COUNT number of values
• How many reservations are there?

SELECT COUNT () FROM Reserved
12
Aggregates, cont.

• What is the total rental price of all films?

SELECT SUM(RentalPrice) FROM Film

• Eliminate duplicates before computing aggregate
  with DISTINCT
• In how many cities do customers reside?

SELECT COUNT(DISTINCT City) FROM Customer
13
Aggregates, cont.

• What is the average rental price of reserved
  films?

SELECT AVG(RentalPrice)FROM Reserved,
FilmWHERE Reserved.FilmID Film.FilmID

• Find the film(s) with the highest rental price.

SELECT Title FROM Film WHERE RentalPrice
IN (SELECT MAX(RentalPrice) FROM Film)
14
GROUP BY

• The table can be partitioned into several groups
  specifying
• GROUP BY ltlist of columnsgt
• Each group has the same attribute values for the
  indicated columns.

Groups
GROUP BY Name
15
GROUP BY, cont.

• Aggregate is applied to each group.
• What is the oldest age for each name?

SELECT Name, MAX(Age) FROM Person GROUP BY
Name
16
GROUP BY, cont.

• What is the average rental price of reserved
  films, by kind?

SELECT Kind, AVG(RentalPrice) FROM Reserved,
Film WHERE Reserved.FilmID Film.FilmID GROUP
BY Kind
17
HAVING

• Individual groups can be eliminated by using
  HAVING.
• List the names and average age per name, as long
  as the average is more than 2.

SELECT Name, AVG(Age) FROM Person GROUP BY Name
HAVING AVG(Age) gt 2
18
HAVING, cont.

• Columns in HAVING clause must appear in the GROUP
  BY (or be contained within an aggregate).
• List the customers whose average rental price for
  reserved films is greater than 3.

SELECT Name FROM Customer, Reserved,
Film WHERE Customer.CustomerID
Reserved.CustomerID AND Reserved.FilmID
Film.FilmID GROUP BY Name HAVING
AVG(RentalPrice) gt 3

• Note, RentalPrice appears within an aggregate

19
Summary

• A query in SQL can consist of up to six clauses,
  but only the first two, SELECT and FROM, are
  mandatory.
• The clauses are specified in the following order.
• SELECT column(s)
• FROM table list
• WHERE condition
• GROUP BY grouping column(s)
• HAVING group condition
• ORDER BY sort list

20
Summary, cont.

• The query is evaluated in a different order.
• The tables in the from clause are combined using
  Cartesian products.
• The where predicate is then applied.
• The resulting tuples are grouped according to the
  group by clause.
• The having predicate is applied to each group,
  possibly eliminating some groups.
• The aggregate(s) are applied to each remaining
  group.
• The select clause is performed last (followed by
  order clause).

21
Order of Clause Evaluation

• FROM
• WHERE optional
• GROUP BY - optional, defaults to 1 group
• HAVING optional
• SELECT - (generalized) projection
• ORDER BY - optional