SHARE
TWEET

Untitled

a guest Dec 10th, 2018 62 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --1/a
  2. 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;
  3. --1/b
  4. SELECT f.* FROM firms f LEFT JOIN phones p ON p.firm_id=f.id WHERE p.phone IS NULL GROUP BY f.id;
  5. --1/c
  6. 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;
  7. --1/d
  8. 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;
  9. --1/e
  10. 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;
  11. --2/a
  12. 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 ;
  13. --2/b
  14. 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 ;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top