Nested Structures

1
Nested Structures

• SELECT Name
• FROM Items
• WHERE Iid IN (SELECT DISTINCT Iid FROM Bids)

2

• Let Temp SELECT Iid FROM Bids

SELECT Name FROM Items WHERE Iid IN (SELECT Iid
FROM Bids)
Is Iid IN Temp? Is I1 IN Temp?
YES! Put this tuple in the output
3
Nested Structures correlation

• SELECT I.Name FROM Items I
• WHERE I.Iid IN (SELECT DISTINCT B.Iid FROM Bids B
• WHERE B.Buyid I.Oid)

Is I1 in Temp? No, remove this tuple!
Is I2 in Temp? No, remove this tuple!
4
Nested Structures correlation

• SELECT I.Name FROM Items I
• WHERE I.Iid IN (SELECT DISTINCT B.Iid FROM Bids B
• WHERE B.Buyid I.Oid)

Is I3 in Temp? No, remove this tuple!
Is I4 in Temp? YES, keep this tuple!
5
SQL - Nested Statements

• A nested sub-expression may refer to the tuples
  from the outer relation
• SELECT Buy.Name
• FROM Buyers Buy
• WHERE Buy.BuyId IN (
• SELECT B.BuyID
• FROM Bids B
• WHERE B.Amount gt 20 AND
• 
  B.BuyID NOT IN (
• 
  SELECT B2.BuyID
• 
  FROM Bids B2
• 
  WHERE B2.Amount lt 20 AND B2.Iid
  B.Iid))

Names of buyers with a bid greater than 20 on an
item for which the buyer has no other bids less
than 20.
6
Nested Structures - Exists

• SELECT
• FROM
• WHERE (NOT) EXISTS
• (SELECT
• FROM R1, ..., Rm
• WHERE P)
• Semantics For each tuple of the outer query,
  execute the inner query if there is at least one
  (no) tuple in the result of the inner query, then
  retrieve that tuple of the outer query.
• This accounts for the there exists type of
  queries

7
SQL Nested Statements

• Two more nested conditions
• EXISTS ( SELECT FROM WHERE ) is true if
  the SFW clause returns a non-empty relation
• NOT EXISTS ( SELECT FROM WHERE ) is true
  if the SFW clause returns an empty relation
• Find the name of all buyers who have no bids with
  an amount less than 50
• SELECT Buy.Name
• FROM Buyers Buy
• WHERE NOT EXISTS (SELECT B.BuyId
• FROM Bids B
• WHERE B.Amount lt50 AND
• 
  B.BuyIDBuy.BuyId)

8
SQL - EXISTS

• Find the names of buyers who placed a bid on all
  items with one or more bids
• SELECT Buy.Name
• FROM Buyers Buy
• WHERE EXISTS (
• SELECT B.Iid
• FROM Bids B
• WHERE Buy.BuyId B.BuyId)
• Is this correct?

Names of buyers who have bid on an item
Find buyers such that there does not exist an
item with bids on which these buyers did not bid
9
SQL - EXISTS

• Find the names of buyers who placed a bid on all
  items with one or more bids
• SELECT Buy.Name
• FROM Buyers Buy
• WHERE NOT EXISTS (
• SELECT B.Iid
• FROM Bids B
• WHERE NOT EXISTS (
• SELECT
• FROM Bids B2
• WHERE B.Iid B2.Iid
  AND
• 
  B2.Buyid Buy.BuyId) )

either there are no bids or this buyer has a bid
on everything
items with bids, but not by this buyer
bids for this item by this buyer
10
Things to Think About

• Dont use nested queries if they are not needed.
• they make the query unnecessarily complicated
• they make optimization harder
• If a query requires only IN and EXISTS, then
  it probably can be done without nesting.

11
Aggregate functions

• Count, Max, Min, Sum, and Avg are aggregate
  functions that apply to the values of a set of
  tuples
• Find the total number of bids in the bids table
• SELECT Count()
• FROM Bids
• Find the average bid for the Dipping Bird.
• SELECT Avg(B.Amount) AS AvgAmt
• FROM Bids B, Items I
• WHERE B.Iid I.Iid AND I.Name Dipping Bird

12
Aggregate Functions

• Find the buyer(s) who placed the maximum bid on
  the Dipping Bird
• SELECT DISTINCT B2.Buyid
• FROM Bids B2, Items I2
• WHERE B2.IidI2.Iid AND
• I2.Name Dipping Bird AND
• B2.Amount (SELECT Max(B.Amount)
• FROM
  Bids B, Items I
• 
  WHERE B.Iid I.Iid AND
• 
  I.Name Dipping Bird)

13
Grouping

• SELECT a
• FROM b
• WHERE c
• GROUP BY d
• Create the Cartesian product of the relations in
  b
• Find all tuples from the Cartesian product that
  satisfy the condition in c (including any
  subexpressions)
• For all distinct values of the columns listed in
  d, create a group that contains the set of tuples
  with that value for d
• Create the columns in a, which should start with
  columns in d and end with aggregates that will
  apply to each group separately.

14
Group by

• Find the average bid amount for each item, list
  the item name, the average bid and the number of
  bids
• SELECT I.name,
• avg(B.Amount) AS AvgBid,
• count() AS Cnt
• FROM Items I, Bids B
• WHERE I.Iid B.Iid
• GROUP BY I.name

15
First Step

• After the FROM/WHERE clause before grouping

16
Second Step

• After the group by

17
Third Step

• The SELECT statement creates a single tuple for
  each group.
• The aggregates functions are applied to each
  group individually.

18
GROUP BY HAVING

• Find the items with 2 or more bids and with the
  average bid greater than 20, for each item list
  the item name
• SELECT I.name
• FROM Items I, Bids B
• WHERE I.Iid B.Iid
• GROUP BY I.name
• HAVING avg(B.Amount) gt 20 AND
• count(DISTINCT B.Bid) gt 2
• Find all buyer and item pairs where the buyer
  placed more than one bid on the same item
• SELECT B.buyid, B.iid
• FROM Bids B
• GROUP BY B.buyid, B.iid
• HAVING count(B.bid) gt 1

19

• Find owners who have at least two items with
  average bids greater than 20 for each item. List
  the owner identifiers and the names of the items.
• SELECT I2.Oid, I2.Name
• FROM Items I2
• WHERE I2.Iid IN (
• SELECT B.Iid
• FROM Bids B
• GROUP BY B.Iid
• HAVING Avg(B.Amount) gt20)
• GROUP BY I2.Oid
• HAVING count(I2.Iid) gt 2

20

• Find owners who have at least two items with
  average bids greater than 20 for each item. List
  the owner identifiers and the names of the items.
• SELECT I2.Oid, I2.Name
• FROM Items I2
• WHERE I2.Oid IN (
• SELECT I.Oid
• FROM Items I
• WHERE I.Iid IN (
• SELECT B.Iid
• FROM Bids B
• GROUP BY B.Iid
• HAVING Avg(B.Amount)
  gt20)
• GROUP BY I.Oid HAVING
  Count() gt 2)
• AND I2.Iid IN (
• SELECT B.Iid
• FROM Bids B
• GROUP BY B.Iid
• HAVING Avg(B.Amount) gt20)

21
ORDER BY

• Follows group by and orders the results in
  ascending or descending order
• Example Find buyer and item pairs in which the
  buyer has more than one bid for the item, order
  the result in descending order of buyer ids
• SELECT B.buyid, B.iid
• FROM Bids B
• GROUP BY B.buyid, B.iid HAVING count(B.bid) gt 1
• ORDER BY B.buyid DESC

22
SELECT complete

• First the Cartesian product of all tables in the
  from clause is formed
• From this, rows not satisfying the where
  condition are eliminated
• The remaining rows are grouped in accordance with
  the group by clause
• Groups not satisfying the having clause then
  eliminated
• The expressions of the select clause target list
  are evaluated
• If the key word distinct is present, duplicate
  rows are now eliminated
• The union is taken after each Subselect is
  evaluated
• Finally, the set of all result rows is sorted if
  an order by clause is present.

23
Join types

• It is possible to define a join in the from
  clause
• INNER JOIN regular join from relational algebra
  where only tuples from the two tables satisfying
  the join condition are chosen
• LEFT OUTER JOIN (regular join) union (tuples
  from the table to the left that did not join with
  any tuples, padded with null values for the new
  attributes)
• RIGHT OUTER JOIN (regular join) union (tuples
  from the table to the right that did not join
  with any tuples, padded with null values for the
  new attributes)
• FULL JOIN LEFT RIGHT OUTER JOIN

24
Join Examples

• Find names of all items with bids
• SELECT DISTINCT Items.name
• FROM Items INNER JOIN Bids
• ON Items.Iid Bids.Iid
• Find all items and the total number of bids on
  them
• SELECT Items.Iid, Count(Bids.Bid)
• FROM Items LEFT OUTER JOIN Bids
• ON Items.Iid Bids.Iid
• GROUP BY Items.Iid

25
Left Outer Join Result

• The result of the left outer join in the
  preceding query is

Result of items inner join bids
The additional tuples resulting from the left
outer join
26
Auction Database
27
Example 1

• Find the name and email of owners of items
  located in Boston

SELECT O.Name, O.Email FROM Owners O, Items
I WHERE I.Location Boston AND I.Oid
O.Oid
28
Example 2

• Find the identifiers and amount of bids placed by
  buyer Roberts

SELECT B.Bid, B.Amount FROM Bids B, Buyers
Buy WHERE Buy.Name Roberts AND Buy.Buyid
B.Buyid
29
Example 3

• Find the names of buyers who placed a bid on an
  item owned by Brown

SELECT DISTINCT Buy.Name FROM Bids B,
Buyers Buy, Owners O, Items I WHERE O.Name
Brown AND O.Oid I.Oid AND
I.Iid B.Iid AND B.Buyid Buy.Buyid
30
Example 4

• Find the identifier of items with more than one
  bid

SELECT B.Iid FROM Bids B GROUP BY B.Iid
HAVING count(B.Bid) gt 1 or SELECT DISTINCT
B.Iid FROM Bids B WHERE EXISTS ( SELECT
FROM Bids B2 WHERE B2.Iid B.Iid AND B2.Bid
ltgt B.Bid)
31
Example 5

• Find the names of items all buyers placed a bid on

Find the names of items for which there does not
exist a buyer who did not place a bid on this
item SELECT I.name FROM Items I
WHERE NOT EXISTS ( SELECT FROM Buyers
Buy WHERE NOT
EXISTS (
SELECT FROM Bids B
WHERE
Buy.Buyid B.Buyid AND
B.Iid
I.Iid ))
32
Expressions

• Each predicate in the WHERE clause can evaluate
  to
• TRUE
• FALSE
• UNKNOWN
• Example
• SELECT B.
• FROM Bids B
• WHERE B.amount gt (SELECT max(B1.amount)
• FROM Bids
  B1, Items I
• WHERE B1.iidI.iid AND
• I.location Boston)

What if there are no tuples in this expression?
33
Unknown expressions

• Expressions that compare a value against a
  possibly null value return the truth value
  Unknown (U).
• Logically Unknown is different than True or
  False.
• Not U U
• U and True U
• U and U U
• U and False False
• U or True True
• U or U U
• U or False U

34
Unknown Expressions

• Unknown valued tuples do not contribute to a
  query result
• SELECT B.
• FROM Bids B
• WHERE B.amount gtALL (SELECT B1.amount
• FROM
  Bids B1, Items I
• WHERE B1.iidI.iid AND
• I.location Boston)
• This query will not return any tuples if the
  amount for some bids in Boston is null.

35
Data Management

• Creating tables, inserting and deleting tuples,
  and updating column values can be done by SQL
  statement.
• CREATE TABLE tablename
• ( colname1 TYPE NOT NULL,
• ,
• PRIMARY KEY(colname1,)
• )
• INSERT INTO tablename(colname1,colname2,)
• VALUES ()

36
More Data Management

• Insert can also involve a SELECT condition
• INSERT INTO BUYERS(buyid,name, email)
• SELECT next(buyid), O.name, O.email
• FROM Owners O
• WHERE NOT EXISTS (SELECT FROM Buyers B
• WHERE B.nameO.name
• AND B.emailO.email)

37
Delete

• The Delete statement involves a WHERE condition
  that specifies the range of tuples that should be
  deleted from the relation.
• DELETE FROM Bids
• WHERE Bids.Iid IN (SELECT I.iid FROM Items I
• WHERE I.oid O1)

38
Update

• The Update statement includes a range condition
  (WHERE) and a set condition for the new values.
• For each tuple t that satisfies the where
  condition, run the expression in the set
  statement.
• UPDATE items
• SET avgbid (SELECT avg(B.amount)
• FROM Bids B
• WHERE B.iid items.iid)
• WHERE items.location Boston