--1/a
SELECT f.name, IF (p.phone IS NULL, "-", p.phone) as phone   FROM firms f LEFT JOIN phones p ON p.firm_id=f.id GROUP BY f.id;
--1/b
SELECT f.* FROM firms f LEFT JOIN phones p ON p.firm_id=f.id WHERE p.phone IS NULL GROUP BY f.id;
--1/c
SELECT f.*, count(phone) as phone_count FROM firms f LEFT JOIN phones p ON p.firm_id=f.id GROUP BY f.id HAVING phone_count>=2;
--1/d
SELECT f.*, count(phone) as phone_count FROM firms f LEFT JOIN phones p ON p.firm_id=f.id GROUP BY f.id HAVING phone_count<2;
--1/e
SELECT f.*, count(phone) as phone_count FROM firms f LEFT JOIN phones p ON p.firm_id=f.id GROUP BY f.id ORDER BY phone_count DESC LIMIT 1;
--2/a
SELECT cname, gname, SUM(q), shipdate FROM (SELECT c.compid, c.name as cname, g.goodid, g.name as gname, s.shipdate, IF(s.quantity IS NULL, 0, s.quantity) as q FROM company c JOIN goods g ON TRUE LEFT JOIN shipment s ON c.compid=s.compid AND g.goodid=s.goodid ORDER BY shipdate DESC) as result GROUP BY compid, goodid ;
--2/b
SELECT cname, gname, IF(SUM(q)=0, 'NO DATA', SUM(q)), shipdate FROM (SELECT c.compid, c.name as cname, g.goodid, g.name as gname, s.shipdate, IF(s.quantity IS NULL, 0, s.quantity) as q FROM company c JOIN goods g ON TRUE LEFT JOIN shipment s ON c.compid=s.compid AND g.goodid=s.goodid AND s.shipdate >= DATE_SUB(NOW(), INTERVAL 150 DAY) ORDER BY shipdate DESC) as result GROUP BY compid, goodid ;