--------------------------------------------------------------------------------
-- CSE 344 -- Spring 2011
-- Lecture 06: SUBQUERIES IN SQL
--------------------------------------------------------------------------------
-- In this lecture we will continue with  the  schema and data from lecture 04-05

--------------------------------------------------
-- | Purchase(pid, product, price, quantity)  | --
-- | Product(pid, pname, manufacturer)        | --
--------------------------------------------------

-- RECAP: queries with GROUP BY
-- *** IN CLASS: for each manufacturer, compute the total number of products they sold
-- *** IN CLASS: for each manufacturer, compute the total number of products they sold each month

-- At the whiteboard: discuss the need to group by the key
-- WHAT'S WRONG ?  select x.manufacturer, sum(...)        from ... group by x.manufacturer
-- WHAT'S WRONG ?  select x.manufacturer, sum(...)        from ... group by x.pid
-- WHAT'S WRONG ?  select x.pid, x.manufacturer, sum(...) from ... group by x.pid
-- HOW DO WE FIX THIS ?


-------------------------------------------------------------------------------
-- SUBQUERIES IN SQL:
--  can occur in three different places
--
-- 1. Subqueries in the SELECT clause

-- Query: for each month, compute the nuber of purchases over $10.00:

select distinct x.month, (select count(*) from purchase y where x.month=y.month and price > 10.0)
from   purchase x
where  price > 10.0;

-- same as

select month, count(*)
from purchase
where price > 10.0
group by month;

-- Query: retriev all products purchased, their prices, and their manufacturers:

select x.product, x.price, (select y.manufacturer from product y where x.product = y.pname)
from purchase x;

-- *** can this query be unnested ? ("Unnesting a query" means writing it without a nested query)
-- (in class...)

-- Not all subqueries make sense in a SELECT clause.
-- *** does this query make sense ?

select y.manufacturer, (select x.product, x.price from purchase x where x.product=y.pname)
from  product y;

-------------------------------------------------------------------------------
-- 2. Subqueries in the FROM clause

-- find all months that had at least one purchase < $120.00;
-- of these retain only those months that also had at least one purchase > $12.00

select distinct x.month
from (select * from purchase y where y.price < 120.0) x
where x.price > 12.0;

-- *** in class: unnest this query !

-- sometimes we MUST use a subquery in the FROM clause !

-- Query: for each month, find the product that sold at the highest price.
--
-- Step 1: for each month find the highest price

select x.month, max(x.price) from purchase x group by x.month;

-- Step 2: using this as a subquery, find the prodcut that sold in that month at that price
-- (draw on the white board...)

select y.month, z.price, z.product
from (select x.month, max(x.price) as maxprice from purchase x group by x.month) y, 
     purchase z
where y.month = z.month and y.maxprice = z.price;

-------------------------------------------------------------------------------
-- 3. Subqueries in the WHERE clause
--
-- We need these especially in order to express univeral quantifiers
--
-------------------------------------------
-- Let's start with EXISTENTIAL QUANTIFIERS:

-- Query : find all products that sold at least once in a quantity >= 5

-- easy...
select distinct x.product
from purchase x
where x.quantity >= 5;

-----------
-- Contrieved way #1, using the quantifier EXISTS

select distinct x.pname
from product x
where exists (select * from purchase y where x.pname=y.product and y.quantity >= 5);


-- In general, exists(...some subquery...) does this: 
-- it evaluates the subquery, and if the answer is non-empty, then returns TRUE, otherwise FALSE

-----------
-- Contrieved way #2, using the operator IN:

select distinct pname
from product
where pname in (select product from purchase where quantity >= 5);

-- In general, "value in (subquery)" checks if the value is among the answers returned by subquery

-----------
-- Contrieved way #3, using the operator ANY:

select distinct x.manfuacturer
from product x
where 5 <= any (select y.quantity from purchase y where x.pname = y.product);

-- SQLITE DOES NOT SUPPORT "ANY" and "ALL" !

-- in general, "value op any (subquery)" checks if there exists a value returned by the subquery
-- that is in the relationship "op" with the value

-------------------------------------------
-- Next, let's do UNIVERSAL QUANTIFIERS:
--
-- Query: find all products that sold only in quantities >= 5
-- 
-- Equivalently, more verbose formulat:
--     find all products with the following property:
--        for all their purchases, quantity >= 5
--
-- *** question in class: suppose a product did not sell at all
--     but it is still in "product"
--     Should we return it ?
-- *** similarly: suppose quantity=NULL for all purchases a product
--     Should we return it ?

-- Writing this query is difficult; we do it in two steps.
--
-- Step 1: find THE WRONG products
--    i.e. who sold in some quantity < 5
--    we use one of the contrieved queries above
-- Using alternative #1:

select distinct x.pname
from product x
where exists (select * from purchase y where x.pname = y.product and y.quantity < 5);

-- Step 2: negate !
--    modify the query to retrieve THE RIGHT manufacturers
--    i.e. who did not sell any product >= 5.00

select distinct x.pname
from product x
where not exists (select * from purchase y where x.pname = y.product and y.quantity < 5);

-- Using alternative #2:

select distinct x.pname
from product x
where x.pname in (select y.product from purchase y where y.quantity < 5);

-- Step 2: negate !

select distinct x.pname
from product x
where x.pname not in (select y.product from purchase y where y.quantity < 5);

-- Using alternative #3 (doesn't work on sqlite)

select distinct x.pname
from product x
where 5 < any (select y.quantity from purchase y where x.pname = y.product);

-- Step 2: negate !

select distinct x.pname
from product x
where 5 >= all (select y.quantity from purchase y where x.pname = y.product);


-------------------------------------------------------------------------------
-- Final remarks:
--    1. expressing quantifiers in SQL seems  difficult at first
--       we will learn how to do this in a systematic way, 
--       by studying all relational queries, in two formalisms:
--           relational calculus
--           datalog
--    2. We still express only WHAT we want to get from the data
--       The query optimizer needs to figure out HOW to get the answer from the data
--       For that, it translates the query into a third formalism:
--           relational algebra
-- On Friday we discuss some final, advanced SQL queries
-- Then we discuss these relational query formalisms over the next 3-4 lectures
