Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --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 ;
Add Comment
Please, Sign In to add comment