learnSQL/11-26-18.sql

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;