Docstoc

Using Relational Databases and SQ

Document Sample
Using Relational Databases and SQ Powered By Docstoc
					Using Relational Databases and SQL



           Lecture 7:
  Subqueries and Set Operations


                                 Steven Emory
             Department of Computer Science
      California State University, Los Angeles
Topics for Today
Set Operations
  UNION
  UNION ALL
Subqueries
  WHERE clause
  HAVING clause
  FROM clause
  SELECT clause (next week)
  Correlated and Nested Subqueries (next week)
Set Operations

UNION (supported by MySQL)‫‏‬
UNION ALL (supported by MySQL)‫‏‬
INTERSECT (not supported by MySQL)‫‏‬
EXCEPT/MINUS (not supported by MySQL)‫‏‬
UNION
Combines the results from multiple SELECT
statements into a single result set
UNION Syntax
Two tables:
  SELECT ...
  UNION [DISTINCT | ALL]
  SELECT ...
Three or more tables:
  SELECT ...
  UNION [DISTINCT | ALL]
  SELECT ...
  UNION [DISTINCT | ALL]
  SELECT ...
  ...
UNION Restrictions

Each SELECT clause must contain the same
number of columns
  -- This is an error!!!!
  SELECT FirstName, LastName
  FROM People
  UNION DISTINCT
  SELECT BirthFirstName
  FROM People;
UNION Example

UNION can also be used to append aggregate
information.
  For each gender, list the number of accounts held by
  members of that gender and append a total member
  count to the bottom of the list.
UNION Example

Solution:
  SELECT Gender, COUNT(Gender)
  FROM Accounts
  GROUP BY Gender
  UNION ALL
  SELECT 'Total', COUNT(Gender)
  FROM Members;
UNION and Duplicates
By default, the UNION keyword alone removes
duplicates (UNION DISTINCT is the default)
  To remove duplicates explicitly, use:
  UNION DISTINCT
  To keep duplicates use:
  UNION ALL
Example:
  SELECT FirstName FROM People
  UNION ALL
  SELECT BirthFirstName FROM People;
UNION and Sorting
To order all results in a UNION query, use a
single ORDER BY clause that orders on one or
more attributes from the first UNION
You can also order all results using a single
ORDER BY clause that orders on one or more
column aliases defined in the first UNION
UNION and Sorting
Examples:
  SELECT FirstName FROM People
  UNION DISTINCT
  SELECT BirthFirstName FROM People
  ORDER BY FirstName;
  SELECT FirstName AS Names FROM People
  UNION DISTINCT
  SELECT BirthFirstName FROM People
  ORDER BY Names;
Subqueries

Subqueries are queries within queries
Also called inner queries
A query that contains a subquery is called an
outer query
A subquery must be surrounded by parentheses
Subquery Example

Example (subquery in red):
  List all the name of all people that are not actors.
  SELECT FirstName, LastName
  FROM People
  WHERE PersonID NOT IN
  (SELECT ActorID FROM XRefActorsMovies);
When to Use Subqueries

Use a subquery when:
  When it is impossible to solve the problem using a
  single query
  When a subquery solution to the problem runs faster
  than an equivalent non-subquery solution to the
  problem (rare with the current version of MySQL)
Subqueries are UGLY
Example:
  SELECT Z.Type1, Z.Type2, CONCAT('$',
  TRUNCATE(Z.AvgPriceDifference, 2)) AS MaxAvgPriceDifference
  FROM (SELECT X.type AS Type1, Y.type AS Type2,
  ABS(X.AveragePrice - Y.AveragePrice) AS AvgPriceDifference FROM
  (SELECT type, AVG(price) AS AveragePrice FROM titles GROUP BY
  type) X JOIN (SELECT type, AVG(price) AS AveragePrice FROM titles
  GROUP BY type) Y WHERE X.type <> Y.type AND STRCMP(X.type,
  Y.type) < 0 HAVING AvgPriceDifference = (SELECT
  MAX(U.AvgPriceDifference) FROM (SELECT X.type AS Type1, Y.type
  AS Type2, ABS(X.AveragePrice - Y.AveragePrice) AS
  AvgPriceDifference FROM (SELECT type, AVG(price) AS AveragePrice
  FROM titles GROUP BY type) X JOIN (SELECT type, AVG(price) AS
  AveragePrice FROM titles GROUP BY type) Y WHERE X.type <>
  Y.type AND STRCMP(X.type, Y.type) < 0) U)) Z;
How to Solve Subquery Problems
To solve subquery problems:
  Always think substitution
  Analyze the question, looking for subqueries within
  the question
  Replace subqueries in the original question with
  substitution variables such as X, Y, and Z
  Write queries for your substitution variables
  Write a query to that solves the original question
  using your substitution variables
  Replace substitution variables with your subqueries
WHERE Clause Subqueries
Use a subquery in the WHERE clause when you
want to filter records from the outer query using a
single value or list of values returned from one or
more subqueries
When filtering with a list of values (a single
column of data), use the keywords IN, ALL, and
ANY.
WHERE Clause Subquery Example
Example:
  List all movie titles produced by Paramount Pictures
  or Twentieth Century-Fox. Do not use a join.
WHERE Clause Subquery Example
Outer and Inner Queries:
  The outer query...
  SELECT Title FROM Movies
  WHERE CompanyID = (X) OR CompanyID = (Y);
  Inner query X...
  SELECT CompanyID FROM Companies
  WHERE Name = ‘Paramount Pictures’;
  Inner query Y...
  SELECT CompanyID FROM Companies
  WHERE Name = ‘Twentieth Century-Fox’;
WHERE Clause Subquery Example

Solution:
  SELECT Title FROM Movies
  WHERE CompanyID = (SELECT CompanyID
  FROM Companies
  WHERE Name = ‘Paramount Pictures’) OR
  CompanyID = (SELECT CompanyID
  FROM Companies
  WHERE Name = ‘Twentieth Century-Fox’);
WHERE Clause Subquery Example
Did I say you can't use aggregates in the WHERE
clause? Not totally true.
  You can, but you need to put the aggregate
  calculation in a subquery so that it is computed first!
  List all movie titles with a runtime greater than the
  average runtime of all movies.
WHERE Clause Subquery Example
Solution:
  SELECT Title
  FROM Movies
  WHERE Runtime > (SELECT AVG(Runtime)
  FROM Movies);
IN and NOT IN
Use the IN keyword to test if an expression
matches any items in a list (typically returned by
a subquery)‫‏‬
Syntax:
  expression IN (list subquery)‫‏‬
  expression NOT IN (list subquery)‫‏‬
IN Example
Example: List the names of all actors.
  The outer query...
  SELECT FirstName, LastName FROM People
  WHERE PersonID IN (X);
  The inner query...
  SELECT ActorID FROM XRefActorsMovies;
  Substitute to get the solution...
  SELECT FirstName, LastName FROM People
  WHERE PersonID IN (SELECT ActorID FROM
  XRefActorsMovies);
ALL and ANY

ALL
 The condition must hold true for all elements in the
 list.
 Syntax: expression operator ALL (list subquery)‫‏‬
ANY
 The condition may hold true for at least one element
 in the list.
 Syntax: expression operator ANY (list subquery)‫‏‬
ALL and ANY Examples

Example:
  List the usernames of all accounts whose join dates
  are earlier than all of the accounts from Germany and
  Australia.
ALL and ANY Examples
Outer and Inner Queries:
  Outer query...
  SELECT Username
  FROM Accounts WHERE JoinDate < ALL (X)
  AND JoinDate < ALL (Y);
  Inner query X...
  SELECT JoinDate
  FROM Accounts WHERE Country = ‘DEU’;
  Inner query Y...
  SELECT JoinDate
  FROM Accounts WHERE Country = ‘AUS’;
ALL and ANY Examples
Substitute to get final solution:
  SELECT Username
  FROM Accounts WHERE JoinDate < ALL
  (SELECT JoinDate
  FROM Accounts WHERE Country = ‘DEU’)
  AND JoinDate < ALL (SELECT JoinDate
  FROM Accounts WHERE Country = ‘AUS’);
HAVING Clause Subqueries
Like the WHERE clause, you can have
subqueries in the HAVING clause as well
  Think substitution as well
  List only those countries for which the number of
  accounts in each country outnumber the total number
  of accounts from Australia.
HAVING Clause Subqueries
Outer and Inner Queries:
  Outer Query:
  SELECT Country FROM Accounts
  GROUP BY Country
  HAVING COUNT(*) > (X)
  Inner Query:
  SELECT COUNT(*)
  FROM Accounts
  WHERE Country = ‘AUS’;
HAVING Clause Subqueries
Substitute to get final solution:
  SELECT Country FROM Accounts
  GROUP BY Country
  HAVING COUNT(*) > (SELECT COUNT(*)
  FROM Accounts
  WHERE Country = ‘AUS’);
FROM Clause Subqueries
Queries oftentimes return tables
FROM clause arguments are tables
You can have subqueries in the FROM clause
Always wrap your subqueries in parentheses
Always define a table alias for any table returned
by a subquery in the FROM clause
FROM Clause Subquery Usage
Use a subquery in the FROM clause when you
you need a complex table in the FROM clause
that can only be computed using a separate query
(i.e. joining tables involving aggregate
calculations and unions).
FROM Clause Subquery Example
Example:
  For each movie, list the movie title and the difference
  between the number of males who rated the movie
  better than 7 and the number of females who rated the
  movie better than 7. For example, if 5 males rated
  Star Trek: Generations better than 7 and only 2
  females rated Star Trek: Generations better than 7,
  the displayed difference should be 3.
  Remember to think substitution!
FROM Clause Subquery Solution
The Outer Query:
  SELECT X.Title, X.MaleCount – Y.FemaleCount
  FROM (X) X INNER JOIN (Y) Y USING(MovieID);
FROM Clause Subquery Solution
The Inner Queries:
  Inner query X...
  SELECT MovieID, Title, SUM(Rating > 7) AS
  MaleCount FROM Movies LEFT JOIN Ratings
  USING(MovieID) LEFT JOIN Accounts
  USING(AccountID) WHERE Gender = 'M’ GROUP
  BY MovieID;
  Inner query Y...
  SELECT MovieID, Title, SUM(Rating > 7) AS
  FemaleCount FROM Movies LEFT JOIN Ratings
  USING(MovieID) LEFT JOIN Accounts
  USING(AccountID) WHERE Gender = 'F’ GROUP
  BY MovieID;
FROM Clause Subquery Solution
Now substitute to get the final solution:
  SELECT X.Title, X.MaleCount – Y.FemaleCount
  FROM (SELECT MovieID, Title, SUM(Rating > 7)
  AS MaleCount FROM Movies LEFT JOIN Ratings
  USING(MovieID) LEFT JOIN Accounts
  USING(AccountID) WHERE Gender = 'M’
  GROUP BY MovieID) X INNER JOIN (SELECT
  MovieID, Title, SUM(Rating > 7) AS FemaleCount
  FROM Movies LEFT JOIN Ratings
  USING(MovieID) LEFT JOIN Accounts
  USING(AccountID) WHERE Gender = 'F’ GROUP
  BY MovieID) Y USING(MovieID);
Sample Problems
Problems:
  List the names of all actors in the movie archive
  database that are older than all of the actors from the
  movie ‘The X Files.’

				
DOCUMENT INFO