SQL-Assignment 4:

The following questions are based on all tables of the SQL-Assignment 2. this is the link to assignment 2http://imgur.com/a/GVN3Z ...... Please read the attachment , create schemas table and write necessary queries.

Write SQL commands for the following:

Modify the Product table by adding an attribute QtyOnHand as an integer field.

Enter at least 5 sample data of your own choosing into all tables (Customer, Order, Order_Line, and Product).

Retrieve data of all tables (each table separately).

For each customer, list the Customer _ID and the total number of orders placed.

List Product_ID, Product_ Description, Product_Finish and Standard_Price greater than $400.

List the average number of customers per state.

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;