Advanced SQL

Run the query with an empty S:

Set Operators & Nested Queries

  • Let λ(X) is the count of tuple in X.
  • Multiset X could be represented in the following two ways.
    • In a set, all counts are {0, 1}.

Tuple

(1, a) (1, a) (1, b) (2, c) (2, c) (2, c) (1, d) (1, d)

Tuple λ(X)
(1, a) 2
(1, b) 1
(2, c) 3
(1, d) 2

INTERSECTION

  • λ(Z)=min(λ(X),λ(Y))

http://i1.fuimg.com/610050/970e8fa776703ce7.png

  • Operation finishes SELECT first, and then carry out INTERSECT command.
  • Now the SQL mentioned first could be changed to:

UNION

  • λ(Z)=λ(X)+λ(Y)

http://i1.fuimg.com/610050/732f1beed092d948.png

  • In default UNION operation, there aren’t duplicates. (SQL uses “set”)
  • If we want duplicates, we use UNION ALL. (indicates “multiset”)

EXCEPT

  • Multiset version(with duplicates): EXCEPT ALL

Nested Queries

  • With such a problem: “Headquarters of companies which make gizmos in US AND China
    • Company(name, hq_city)
    • Product(pname, maker, factory_loc)
  • We will be wrong if we use:

  • Use nested queries to solve:

  • IN operator

  • ALL / ANY operator (not supported in SQLite)

  • EXISTS operator
    • <> means !=

  • With two SQL block to do the same thing, use DISTINCT to avoid duplicates
  • Nested queries as alternatives to INTERSECT and EXCEPT
    • INTERSECT and EXCEPT not in some DBMSs!

  • Represent external relation to be vars and use it in internal subquery.

Aggregation & GROUP BY

Aggregation Operators

  • Aggregation operations: SUM, COUNT, MIN, MAX, AVG
    • Except COUNT, all others apply to a single attribute
  • COUNT applies to duplicates, unless use DISTINCT

GROUP BY

  1. Compute the FROM and WHERE clauses
  2. Group by the attributes in the GROUP BY
  3. Compute the SELECT clause: grouped attributes and aggregates

http://i1.fuimg.com/610050/e37f346107aecb0a.png

GROUP BY with HAVING

  • HAVING clauses contains conditions on aggregates
  • Whereas WHERE clauses condition on individual tuples

  • Evaluation steps:
    1. Evaluate FROMWHERE: apply condition C1 on the attributes in R1, …, Rn
    2. GROUP BY the attributes a1, …, ak
    3. Apply HAVING condition to each group
    4. Compute aggregates in SELECT part and return the result

http://i1.fuimg.com/610050/cde323c738446a54.png

  • TIPS: use LIMIT <num> to limit the number of rows to display.

Advanced SQL-izing

Quantifiers

  • An existential quantifier is a logical quantifier if the form “there exists”
  • eg: Find all companies that make some product with price < 100

  • A universal quantifier is of the form “for all”
  • eg: Find all companies with products all having price < 100
    • equivalent to: Find all companies that make only products with price < 100

NULLs

  • The schema specifies for each attribute if can be null (nullable attribute) or not
  • For numerical operations, NULL -> NULL
    • If x = NULL then 4 * (3 − x)/7 is still NULL
  • For boolean operations, in SQL there are three values
    • FALSE = 0, UNKNOWN = 0.5, TRUE = 1
    • If x = NULL, then x=”Joe” is UNKOWN
  • C1 AND C2 = min(C1, C2)
  • C1 OR C2 = max(C1, C2)
  • NOT C1 = 1 – C1
  • If one of conditions is =NULL in AND, there is no return

Outer Joins

  • By default, joins in SQL are “inner joins”
  • An outer join return tuples from the joined relations that don’ have a corresponding tuple in the other relations (i.e., NULL value is allowed)
  • Left outer join:
    • Include the left tuple even if there’s no match
  • Right outer join:
    • Include the right tuple even if there’s no match
  • Full outer join:
    • Include the both left and right tuples even if there’s no match

Leave a Reply

Your email address will not be published.