Subversion Repositories programming

TO ACCESS YOUR DATABASE (cs435):
mysql -u root -p cs435


PROBLEM 1:
--------------------------------------------------------------------------------
SELECT name FROM members
WHERE  name NOT IN
    (SELECT name FROM orders
     WHERE  item='Lettuce')
;

PROBLEM 2:
--------------------------------------------------------------------------------
SELECT name,address FROM members
WHERE  name IN
    (SELECT name FROM orders
     WHERE  item IN
        (SELECT item FROM suppliers
         WHERE  sname='Sunshine Produce')
    )
;

PROBLEM 3:
--------------------------------------------------------------------------------
SELECT sname,item,price FROM suppliers
WHERE  item NOT IN
    (SELECT item FROM orders
     WHERE  name='Brooks, B.')
;

PROBLEM 4: 
--------------------------------------------------------------------------------
SELECT sname FROM suppliers
WHERE  item IN
    (SELECT item FROM orders
     WHERE  name='Brooks, B.')
GROUP BY sname
HAVING COUNT(sname) =
    (SELECT COUNT(DISTINCT item) FROM orders
     WHERE  name='Brooks, B.')
;

---------------
Using JOIN ----
---------------
SELECT s.sname FROM suppliers s, orders o
WHERE  o.item=s.item 
AND o.name='Brooks, B.' 
GROUP BY s.sname
HAVING COUNT(*)=(SELECT COUNT(*) FROM orders 
                 WHERE name='Brooks, B.');

PROBLEM 5:
--------------------------------------------------------------------------------
SELECT DISTINCT sname FROM suppliers
WHERE item IN
    (SELECT item FROM orders
     WHERE name='Brooks, B.')
;

PROBLEM 6:
--------------------------------------------------------------------------------
SELECT item,AVG(price) FROM suppliers
WHERE  item IN
    (SELECT DISTINCT item FROM suppliers)
GROUP BY item;

PROBLEM 7: CHECK AGAIN
--------------------------------------------------------------------------------
SELECT sname,item FROM suppliers;

PROBLEM 8: HERE IS ONE POSSIBLE SOLUTION, USING "AS"
--------------------------------------------------------------------------------
SELECT name FROM members 
WHERE  name IN 
    (SELECT name FROM 
        (SELECT name,COUNT(name) FROM orders 
         GROUP BY name HAVING COUNT(name)>=2) 
     AS temp_tbl1)
;

PROBLEM 9:
--------------------------------------------------------------------------------
SELECT sname,COUNT(sname) FROM suppliers
GROUP BY sname;

PROBLEM 10: HERE IS ONE POSSIBLE SOLUTION, USING "AS"
--------------------------------------------------------------------------------
SELECT sname FROM suppliers 
WHERE item IN 
    (SELECT item FROM 
        (SELECT item,COUNT(item) FROM orders 
         GROUP BY item 
         HAVING COUNT(item)>1) 
     AS temp_tbl1)
;

PROBLEM 11:
--------------------------------------------------------------------------------
SELECT DISTINCT sname FROM suppliers
WHERE  sname IN
    (SELECT sname FROM suppliers
     WHERE  item='Lettuce')
AND sname IN
    (SELECT sname FROM suppliers
     WHERE  item='Whey')
;

PROBLEM 12:
--------------------------------------------------------------------------------
SELECT DISTINCT name FROM members
WHERE  name IN
    (SELECT name FROM orders
     WHERE  item='Lettuce')
OR name IN
    (SELECT name FROM orders
     WHERE  item='Whey')
;

PROBLEM 13:
--------------------------------------------------------------------------------
SELECT name FROM members
WHERE  name NOT IN
    (SELECT name FROM orders
     WHERE  item IN
        (SELECT DISTINCT item FROM orders
         WHERE  item<>'Whey')
    )
AND name IN
    (SELECT DISTINCT name FROM orders)
;