SQL-Assignment 4: The following questions are based on all tables of the SQL-Example 03. Please execute each of following query in MS SQL server management studio. Once you get the successful result, take a screenshot of each query and respective successful message. Copy screenshots into a word document and submit it in D2L. PLEASE COPY THE ENTIRE SCREEN IN YOUR SCREEN SHOT. I want to see your entire environment in your screen shot that includes the query and result. /* Code selects employee name where it finds a value for them in the dependents table */ /* this code has a sub clause */ SELECT FacFirstname, FacLastname FROM Faculty WHERE FacultyID in (SELECT DISTINCT FacultyID FROM Dependents); /* the sub clause is SELECT DISTINCT FacultyID FROM Dependents */ SELECT * FROM Faculty; SELECT * FROM Dependants; /* time to create a tax table to hold TAX info which is the social security number. */ CREATE TABLE Tax ( TaxID INT IDENTITY(1000,1) PRIMARY KEY, SocialSecurityID Varchar (9) NOT NULL, FacultyID INT NOT NULL ); /* add the foreign Key Constrain between Tax and Faculty Tables */ ALTER TABLE Tax ADD CONSTRAINT FK2_FacultyID FOREIGN KEY (FacultyID) REFERENCES Faculty(FacultyID) ON DELETE CASCADE; SELECT * from Tax; /* Test the table to see it is there. */ /* Insert Data */ INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1000, 256987412); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1001, 245896541); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1002, 253564878); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1003, 456891265); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1004, 563871954); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1005, 364573144); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1006, 325489642); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1007, 253569715); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1008, 254158794); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1009, 287468912); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1010, 254867946); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1011, 256789467); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1012, 256789415); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1013, 254469731); INSERT INTO Tax (FacultyID, SocialSecurityID) VALUES (1014, 256974586); /* create a Salary payment table */ CREATE TABLE Payment ( PaymentID INT IDENTITY(1000,1) PRIMARY KEY, FacultyID INT NOT NULL, ClassName Varchar(20) NOT NULL, Section INT NOT NULL, Payment money NOT NULL ) /* add the foreign Key */ ALTER TABLE Payment ADD CONSTRAINT FK5_FacultyID FOREIGN KEY (FacultyID) REFERENCES Faculty(FacultyID) ON DELETE NO ACTION; SELECT * FROM Payment; /* Test the table */ /* add the data to payment table */ INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1001, 'Java 1301', 001, 5010.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1001, 'Java 1301', 002, 5010.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1002, 'Salary ', 999, 50563.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1003, 'Java 1301', 003, 5010.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1003, 'Java 1301', 004, 5010.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1003, 'Java 1302', 001, 5010.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1003, 'Java 1302', 002, 5010.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1003, 'Java 1302', 006, 5010.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1007, 'Java 1302', 003, 5010.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1007, 'Java 1302', 004, 5010.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1008, 'Database ', 004, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1008, 'Java 1302', 005, 5010.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1005, 'Java 1302', 007, 5010.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1003, 'Database', 004, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1005, 'Database', 005, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1005, 'Database', 006, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1006, 'Salary ', 999, 80000.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1007, 'Database', 007, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1007, 'Database', 007, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1009, 'Proj Mgt', 001, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1009, 'Proj Mgt', 002, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1009, 'Proj Mgt', 003, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1009, 'Adv DB , 001, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1009, 'Adv Db , 002, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1010, 'Data Str', 001, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1010, 'Data Str', 002, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1010, 'Data Str', 003, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1010, 'Web dsn ', 003, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1010, 'Web dsn ', 003, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1010, 'Web dsn ', 003, 3300.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1011, Salary , 999, 80000.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1012, Salary , 999, 12000.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1013, Salary , 999, 15065.00); INSERT INTO Payment (FacultyID, ClassName, Section, Payment) VALUES (1014, Salary , 999, 70005.00); /* Test the table for the data */ Select * from Payment; Select * from Tax; /* create a statement by adding a join */ SELECT Faculty.FacultyID,Faculty.FacFirstname, Faculty.FacLastname, Tax.SocialSecurityID as Faculty_SSN from Faculty, Tax where Faculty.FacultyID=Tax.FacultyID; /* We are going to create a view to join the Social security number to the Faculty member */ CREATE VIEW Tax_Info AS SELECT Faculty.FacultyID ,Faculty.FacFirstname , Faculty.FacLastname, Tax.SocialSecurityID from Faculty, Tax where Faculty.FacultyID=Tax.FacultyID; /* Test Your view by calling it with a select statement*/ SELECT * from TAX_INFO; SELECT FacFirstname, FacLastname from Tax_Info; DROP VIEW Tax_Info; /* change the table columns using "AS" to create a column alias */ CREATE VIEW Tax_Info AS SELECT Faculty.FacultyID ,Faculty.FacFirstname AS Firstname, Faculty.FacLastname AS Laastname, Tax.SocialSecurityID AS SSN from Faculty, Tax where Faculty.FacultyID=Tax.FacultyID;

Figure 5-5 Referential integrity constraints (Pine Valley Furniture) CUSTOMER Customer ID Customer Name Customer Address City State Postal Code ORDER Referential Onder D Order Date Customer ID integrity constraints are drawn Vla arrows ORDER LINE from dependent to Order ID ProducLD ordered ourity parent table PRODUCT Product ID Product Description product Finish Standard Price Product Line ID Figure 5-5 Referential integrity constraints (Pine Valley Furniture) CUSTOMER Customer ID Customer Name Customer Address City State Postal Code ORDER Referential Onder D Order Date Customer ID integrity constraints are drawn Vla arrows ORDER LINE from dependent to Order ID ProducLD ordered ourity parent table PRODUCT Product ID Product Description product Finish Standard Price Product Line ID