12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849 |
- UPDATE manager
- SET mresbuilding = 'B4'
- WHERE managerid = 'M34';
-
- -- dropping constraints is a good way to keep data but remove the foreign keys,
- -- typically constraints are done if the database is expected to be modified in the future.
-
-
- SELECT a.buildingid, a.aptno, c.ccname
- FROM apartment a
- LEFT OUTER JOIN corpclient c
- ON a.ccid = c.ccid;
-
-
- SELECT a.buildingid, a.aptno, c.ccname
- FROM apartment a
- RIGHT OUTER JOIN corpclient c
- ON a.ccid = c.ccid;
-
- -- a good diagram.
- -- https://i.stack.imgur.com/1UKp7.png
- -- BTW, FULL_OUTERMOST_JOIN is not in MySQL. Use a union of left and right outer joins.
-
- -- UNION eliminates duplicates
-
- -- JOIN doesn't actually require foreign keys. It just requires matched types
- -- FK's exist rather for you to keep your data organized and enforce integrity
-
- -- see q44 (we've modified it to have s.smembername, below)
-
- SELECT m.managerid, m.fname, m.mlname, s.smemberid, s.smembername
- FROM manager m, staffmember s
- WHERE m.mfname = s.smembername;
-
- -- q45. IS NULL
-
- SELECT *
- FROM manager
- WHERE mbonus IS NULL;
-
- -- Good for optional queries
-
- -- q46 EXISTS
- -- meh according to prof. Doesn't like it, always a better way
-
- -- IS NOT NULL (replaced q47)
- SELECT *
- FROM manager
- WHERE mbonus IS NOT NULL;
|