all.sql

/* q1 */
SELECT DISTINCT iid
FROM purchase
where pdate='2017-11-24'
ORDER BY iid ASC;
/* q2 */

SELECT DISTINCT cname
FROM customer NATURAL JOIN purchase NATURAL JOIN item
where quantity > 1 and
      itype='smartphone'
ORDER BY cname ASC;

/* q3 */

SELECT DISTINCT cname
FROM customer NATURAL JOIN purchase NATURAL JOIN item
where itype='smartphone'

INTERSECT

SELECT DISTINCT cname
FROM customer NATURAL JOIN purchase NATURAL JOIN item
where itype='laptop'
ORDER BY cname ASC;

/* q4 */

SELECT DISTINCT cid, iid
FROM customer, item

EXCEPT

SELECT DISTINCT cid, iid
FROM purchase
ORDER BY cid, iid ASC;

/* q5 */

SELECT DISTINCT cname
FROM customer C
where budget >= 7000 and
    NOT EXISTS ((SELECT I.iid
           FROM item I
        WHERE I.price <= 300 )
    EXCEPT
    (SELECT P.iid
        FROM purchase P
        WHERE P.cid = C.cid))
ORDER BY cname ASC;

/* q6 */
/* SIMILAR TO RA
SELECT DISTINCT iid
FROM purchase A, purchase B, purchase C
where A.iid = B.iid and B.iid = C.iid and
     A.cid <> B.cid and B.cid <> C.cid and A.cid <> C.cid

EXCEPT
SELECT DISTINCT iid
FROM purchase A, purchase B, purchase C, purchase D
where A.iid = B.iid and B.iid = C.iid and C.iid = D.iid
     A.cid <> B.cid and B.cid <> C.cid and A.cid <> C.cid and
     A.cid <> D.cid and B.cid <> D.cid and C.cid <> D.cid
ORDER BY iid ASC;
*/

SELECT distinct iid
FROM item NATURAL JOIN purchase
WHERE price > 400
GROUP BY iid
HAVING count(distinct cid) = 3
ORDER BY iid ASC;

/* q7 */

SELECT DISTINCT itype, avg(price), min(price), max(price)
FROM item
GROUP BY itype
ORDER BY itype ASC;

/* q8 */

WITH T(iid, avgBudget) as (
    SELECT DISTINCT iid, avg(budget) as avgBudget
    FROM (
        select distinct cid, iid from purchase
    ) as Y NATURAL JOIN customer
    group by iid
)
SELECT iid
FROM T
where T.avgBudget = (SELECT max(avgBudget) from T)
/* q9 */

UPDATE item as I
SET price = price / 2
WHERE (SELECT count(DISTINCT cid) FROM purchase as P where I.iid = P.iid) < 3;


/* q10 */

DELETE FROM customer as C
WHERE NOT EXISTS (
    SELECT iid
    FROM (purchase NATURAL JOIN item) as T
    WHERE T.cid = C.cid and T.price > 500
);

/* testing db */
INSERT INTO customer (cname, rating, budget) VALUES ('Liron', 10, 7000);
INSERT INTO customer (cname, rating, budget) VALUES ('Nadav', 5, 7500);
INSERT INTO customer (cname, rating, budget) VALUES ('Arik', 6, 6700);
INSERT INTO customer (cname, rating, budget) VALUES ('Avichai', 6, 6700);
INSERT INTO customer (cname, rating, budget) VALUES ('Itay', 6, 7800);
INSERT INTO item (iname, itype, price) VALUES ('nexus', 'smartphone', 200);
INSERT INTO item (iname, itype, price) VALUES ('thinkpad', 'laptop', 401);
INSERT INTO item (iname, itype, price) VALUES ('macbook', 'laptop', 900);
INSERT INTO item (iname, itype, price) VALUES ('iphone', 'smartphone', 900);
INSERT INTO purchase(cid, iid, pdate, quantity) VALUES (1, 1, '2017-11-24', 2);
INSERT INTO purchase(cid, iid, pdate, quantity) VALUES (2, 1, '2017-11-25', 1);
INSERT INTO purchase(cid, iid, pdate, quantity) VALUES (2, 2, '2017-11-25', 1);
INSERT INTO purchase(cid, iid, pdate, quantity) VALUES (1, 3, '2017-12-24', 2);
INSERT INTO purchase(cid, iid, pdate, quantity) VALUES (2, 3, '2017-12-24', 2);
INSERT INTO purchase(cid, iid, pdate, quantity) VALUES (3, 3, '2017-12-24', 2);
INSERT INTO purchase(cid, iid, pdate, quantity) VALUES (4, 3, '2017-12-24', 2);
INSERT INTO purchase(cid, iid, pdate, quantity) VALUES (3, 1, '2017-12-24', 2);
INSERT INTO purchase(cid, iid, pdate, quantity) VALUES (3, 2, '2017-12-22', 2);
INSERT INTO purchase(cid, iid, pdate, quantity) VALUES (4, 2, '2017-12-22', 2);
INSERT INTO purchase(cid, iid, pdate, quantity) VALUES (5, 4, '2017-10-22', 2);