sql playground
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

11-26-18.sql 1.2KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849
  1. UPDATE manager
  2. SET mresbuilding = 'B4'
  3. WHERE managerid = 'M34';
  4. -- dropping constraints is a good way to keep data but remove the foreign keys,
  5. -- typically constraints are done if the database is expected to be modified in the future.
  6. SELECT a.buildingid, a.aptno, c.ccname
  7. FROM apartment a
  8. LEFT OUTER JOIN corpclient c
  9. ON a.ccid = c.ccid;
  10. SELECT a.buildingid, a.aptno, c.ccname
  11. FROM apartment a
  12. RIGHT OUTER JOIN corpclient c
  13. ON a.ccid = c.ccid;
  14. -- a good diagram.
  15. -- https://i.stack.imgur.com/1UKp7.png
  16. -- BTW, FULL_OUTERMOST_JOIN is not in MySQL. Use a union of left and right outer joins.
  17. -- UNION eliminates duplicates
  18. -- JOIN doesn't actually require foreign keys. It just requires matched types
  19. -- FK's exist rather for you to keep your data organized and enforce integrity
  20. -- see q44 (we've modified it to have s.smembername, below)
  21. SELECT m.managerid, m.fname, m.mlname, s.smemberid, s.smembername
  22. FROM manager m, staffmember s
  23. WHERE m.mfname = s.smembername;
  24. -- q45. IS NULL
  25. SELECT *
  26. FROM manager
  27. WHERE mbonus IS NULL;
  28. -- Good for optional queries
  29. -- q46 EXISTS
  30. -- meh according to prof. Doesn't like it, always a better way
  31. -- IS NOT NULL (replaced q47)
  32. SELECT *
  33. FROM manager
  34. WHERE mbonus IS NOT NULL;