SQL_Oct16_Class

--INNER JOIN:
SELECT FACULTY.FACULTYID, FACULTY.FNAME, FACULTY.LNAME, DEPENDENTS.DEPFNAME
FROM FACULTY INNER JOIN DEPENDENTS ON
FACULTY.FACULTYID = DEPENDENTS.FACULTYID

--FULL OUTER JOIN
CREATE VIEW FULLOUTERJOIN AS
SELECT FAC.FNAME, FAC.LNAME, DEP.DEPFNAME
FROM FACULTY FAC FULL OUTER JOIN DEPENDENTS DEP ON
FAC.FACULTYID = DEP.FACULTYID

--RIGHT OUTER JOIN
SELECT FAC.FNAME, FAC.LNAME, DEP.DEPFNAME
FROM FACULTY FAC RIGHT OUTER JOIN DEPENDENTS DEP ON
FAC.FACULTYID = DEP.FACULTYID


--UNION
SELECT FNAME FROM FACULTY
UNION
SELECT DEPFNAME FROM DEPENDENTS

--UNION RESULTS = DISTINCT
SELECT STATE FROM FACULTY
UNION
SELECT STATE FROM STUDENT

--UNION RESULTS = ALL, not just distinct.
SELECT STATE FROM FACULTY
UNION ALL
SELECT STATE FROM STUDENT

--VIEWING AND THEN DROPPING VIEW TABLE
SELECT * FROM FULLOUTERJOIN
DROP VIEW FULLOUTERJOIN

CREATE TABLE TAX
(
TAXID					INT		IDENTITY(1000,1) PRIMARY KEY,
SOCIALSECURITYID		VARCHAR(9) NOT NULL,
FACULTYID				INT NOT NULL,
FOREIGN KEY(FACULTYID) REFERENCES FACULTY(FACULTYID) ON DELETE CASCADE
)