Please execute each of following query in MS SQL server management studio. Once
ID: 3825017 • Letter: P
Question
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;
Explanation / Answer
1. select * from faculty;
FacFirstname FacLastname FacultyID
Bags P 1
Alex Paul 2
2. select * from Dependents
DependentId FacultyID DependentName
1 1 Parthee
2 2 John
3. After creating and inserting the rows into tax table.
SELECT * from Tax;
TaxID SocialSecurityID FacultyID
1000 256987412 1000
1001 245896541 1001
1002 253564878 1002
1003 456891265 1003
1004 563871954 1004
1005 364573144 1005
1006 325489642 1006
1007 253569715 1007
1008 254158794 1008
1009 287468912 1009
1010 254867946 1010
1011 256789467 1011
1012 256789415 1012
1013 254469731 1013
1014 256974586 1014
4. After creating Payment table and inserted the rows
select * from Payment;
PaymentID FacultyID ClassName Section Payment
1000 1001 Java 1301 1 5010.00
1001 1001 Java 1301 2 5010.00
1002 1002 Salary 999 50563.00
1003 1003 Java 1301 3 5010.00
1004 1003 Java 1301 4 5010.00
1005 1003 Java 1302 1 5010.00
1006 1003 Java 1302 2 5010.00
1007 1003 Java 1302 6 5010.00
1008 1007 Java 1302 3 5010.00
1009 1007 Java 1302 4 5010.00
1010 1008 Database 4 3300.00
1011 1008 Java 1302 5 5010.00
1012 1005 Java 1302 7 5010.00
1013 1003 Database 4 3300.00
1014 1005 Database 5 3300.00
1015 1005 Database 6 3300.00
1016 1006 Salary 999 80000.00
1017 1007 Database 7 3300.00
1018 1007 Database 7 3300.00
1019 1009 Proj Mgt 1 3300.00
1020 1009 Proj Mgt 2 3300.00
1021 1009 Proj Mgt 3 3300.00
1022 1009 Adv DB 1 3300.00
1023 1009 Adv Db 2 3300.00
1024 1010 Data Str 1 3300.00
1025 1010 Data Str 2 3300.00
1026 1010 Data Str 3 3300.00
1027 1010 Web dsn 3 3300.00
1028 1010 Web dsn 3 3300.00
1029 1010 Web dsn 3 3300.00
1030 1011 Salary 999 80000.00
1031 1012 Salary 999 12000.00
1032 1013 Salary 999 15065.00
1033 1014 Salary 999 70005.00
5. The Tax_Info view is created.
select * from Tax_Info;