-- ##################################
-- section 1
-- ##################################

-- pc models with speed at least 2.50
SELECT model
FROM pc
where speed >= 2.50;


-- Printers but no PCs
(SELECT manufacturer
FROM product
WHERE type = 'printer')

EXCEPT

(SELECT manufacturer
FROM product
WHERE type = 'pc');


--PCs but no laptops
( SELECT manufacturer
  FROM product
  WHERE type = 'pc'
)

EXCEPT

( SELECT manufacturer
  FROM product
  WHERE type = 'laptop'
);

-- Manufacturers of laptops with a hard disk of at least 500 GB
SELECT DISTINCT p.manufacturer
FROM product p inner join laptop l
ON p.model = l.model
WHERE hd >= 500;


-- manufacturer, speed for laptops with hd >= 500 GB
SELECT DISTINCT p.manufacturer, l.speed
FROM product p, laptop l
WHERE p.model = l.model AND l.hd >= 500;


-- The model number and price of all products made by manufacturer C
(SELECT q.model, q.price
FROM product p INNER JOIN pc q
ON p.model = q.model
WHERE p.manufacturer = 'C')

UNION ALL

(SELECT l.model, l.price
FROM product p INNER JOIN laptop l
ON p.model = l.model
WHERE p.manufacturer = 'C')

UNION ALL

(SELECT r.model, r.price
FROM product p INNER JOIN printer r
ON p.model = r.model
WHERE p.manufacturer = 'C');


-- The manufacturers of at least two differenc computers (PC or laptop) with speeds of at least 2.0

WITH pc_and_laptop AS (
    ( SELECT model
    FROM pc
    WHERE speed >= 2.0)

    UNION

    (SELECT model
    FROM laptop
    WHERE speed >= 2.0)
)
SELECT DISTINCT p.manufacturer
FROM pc_and_laptop m INNER JOIN product p
ON m.model = p.model
GROUP by p.manufacturer
HAVING count(p.model) >=2 ;


-- The manufacturerers with number of color printers and baw printers
SELECT p.manufacturer,
       sum(CASE WHEN r.color THEN 1 ELSE 0 END) AS num_color,
       sum(CASE WHEN NOT r.color THEN 1 ELSE 0 END) AS num_baw
FROM product p
INNER JOIN printer r ON p.model = r.model
GROUP BY p.manufacturer
ORDER BY num_color DESC;




-- ##################################
-- with > two different approaches:
-- ##################################

-- The manufacturer of laptops with a speed of at least 2.0
SELECT DISTINCT p.manufacturer
FROM product p
WHERE p.model IN
    (SELECT l.model
        FROM laptop l
        WHERE l.speed >= 2.0);

SELECT DISTINCT p.manufacturer
FROM product p
WHERE EXISTS
    (SELECT *
        FROM laptop l
        WHERE l.model = p.model AND speed >= 2.0);

-- The printers with the highest price
SELECT p.model
FROM printer p
WHERE p.price >= ALL (SELECT q.price
                      FROM printer q);

SELECT p.model
FROM printer p
WHERE p.price = (SELECT MAX(q.price)
                 FROM printer q);


-- The laptops whose speed is slower than that of the fastest PC.
SELECT l.model
FROM laptop l
WHERE l.speed < (SELECT MAX(speed)
                 FROM pc p);


SELECT l.model
FROM laptop l
WHERE l.speed < ANY (SELECT p.speed
                     FROM pc p);




-- model with lowest price
SELECT u.model
FROM ((SELECT model, price FROM pc)
       UNION ALL
      (SELECT model, price FROM laptop)
       UNION ALL
      (SELECT model, price FROM printer)) as u
WHERE u.price >= ALL ((SELECT price FROM PC)
                      UNION ALL
                      (SELECT price FROM laptop)
                      UNION ALL
                      (SELECT price FROM printer));


(SELECT model
FROM pc
WHERE price <= ALL((SELECT MIN(price) FROM pc)
    UNION ALL
    (SELECT MIN(price) FROM laptop)
    UNION ALL
    (SELECT MIN(price) FROM printer)))

UNION ALL

(SELECT model
FROM laptop
WHERE price <= ALL((SELECT MIN(price) FROM pc)
    UNION ALL
    (SELECT MIN(price) FROM laptop)
    UNION ALL
    (SELECT MIN(price) FROM printer)))

UNION ALL

(SELECT model
FROM printer
WHERE price <= ALL((SELECT MIN(price) FROM pc)
    UNION ALL
    (SELECT MIN(price) FROM laptop)
    UNION ALL
    (SELECT MIN(price) FROM printer)));



-- manufacturer of the color printer with the highest price
SELECT DISTINCT p.manufacturer
FROM product p
WHERE p.model IN (SELECT q.model
                  FROM printer q
                  WHERE q.price >= (SELECT MAX(r.price)
                                    FROM printer r
                                    WHERE r.color));


SELECT DISTINCT p.manufacturer
FROM product p, printer q
WHERE p.model = q.model AND q.color AND
NOT EXISTS (SELECT *
            FROM printer r
            WHERE r.color AND r.price > q.price);



-- ##################################
-- Recursive exercise
-- ##################################

WITH RECURSIVE transfer(origin, desitnation, arrival_time, num_transits) AS (
    (SELECT origin, destination, arrival_time, 0 FROM flights)

    UNION

    (SELECT t.origin, f.destination, f.arrival_time, t.num_transits + 1
    FROM transfer t, flights f
    WHERE t.destination = f.origin
        AND f.departure_time >= t.arrival_time + TIME '01:00:00'
        AND t.num_transits < 5)
) SELECT origin, destination FROM transfer ;



WITH RECURSIVE person_path AS (
    (SELECT s.person AS first, o.person AS last , ARRAY[s.person, o.person] AS person_array
    FROM student_organization s, student_organization o
    WHERE s.person = 'Alan Miller'
    AND s.organization = o.organization
    AND s.person <> o.person)

    UNION ALL

    (SELECT pp.person, o2.person, pp.person_array || o2.person
    FROM person_path pp, organization o1, organization o2
    WHERE CARDINALITY(pp.array < 5) -- add no more
    AND pp.last <> 'Ida Duncan' -- already ends on Ida Duncan
    AND o1.person = pp.second
    AND o1.organization = o2.organization
    AND o1.person <> o2.person
    AND o2.person <> ALL(pp.person_array) -- no cycles
)
SELECT person_array from person_path;