SQL script:

USE [MASTER] Go CREATE DATABASE [Faculty] Go USE [Faculty] CREATE TABLE ProfRank ( ProfRankID INT CONSTRAINT ProfRank_PK PRIMARY KEY, ProfRankName NVARCHAR(30), Minimumm DECIMAL(8,2), Maximum DECIMAL(8,2) ); CREATE TABLE Dept ( DeptID INT CONSTRAINT Dept_PK PRIMARY KEY, DeptName NVARCHAR(50) ); CREATE TABLE Major ( MajorID INT CONSTRAINT Major_PK PRIMARY KEY, MajorName NVARCHAR(40) ); CREATE TABLE Faculty ( FacultyID INT CONSTRAINT Faculty_PK PRIMARY KEY, FirstName NVARCHAR(15), LastName NVARCHAR(15), Phone NVARCHAR(10), Salary DECIMAL(8, 2), Stipend DECIMAL(7, 2), HireDate DATE, SupervisorID INT CONSTRAINT Faculty_Faculty_FK REFERENCES Faculty(FacultyID), DeptID INT CONSTRAINT Faculty_Dept_FK REFERENCES Dept(DeptID) ); CREATE TABLE Student ( StudentID INT CONSTRAINT Student_PK PRIMARY KEY, FirstName NVARCHAR(15), LastName NVARCHAR(15), Phone NVARCHAR(10), AdvisorID INT CONSTRAINT Student_Faculty_FK REFERENCES Faculty(FacultyID) ); CREATE TABLE StudentMajor ( MajorID INT CONSTRAINT StudentMajor_Major_FK REFERENCES Major(MajorID), StudentID INT CONSTRAINT StudentMajor_Student_FK REFERENCES Student(StudentID), CONSTRAINT StudentMajorPK PRIMARY KEY (MajorID, StudentID)

); INSERT INTO Major VALUES(1, 'Software Engineering Technology'); INSERT INTO Major VALUES(2, 'Computer Engineering Technology'); INSERT INTO Major VALUES(3, 'Applied Psychology'); INSERT INTO Major VALUES(4, 'Communication Studies'); INSERT INTO Dept VALUES(1, 'Computer Systems Engineering Technology') INSERT INTO Dept VALUES(2, 'Humanities and Social Science'); INSERT INTO Dept VALUES(3, 'Communication'); INSERT INTO Dept VALUES(4, 'Electronics Engineering Technology'); INSERT INTO Faculty VALUES (1, 'Lucas', 'Cordova', '885-1230', 25000.00, 3000.00, '15-Sep-2016', Null, 1); INSERT INTO Faculty VALUES (2, 'Calvin', 'Caldwell', '885-1598', 33000.00, 3000.00, '15-Sep-1986', Null, 1); INSERT INTO Faculty VALUES (3, 'Randy', 'Albert', '885-1596', 35000.00, 1500.00, '15-Sep-1984', 1, 1); INSERT INTO Faculty VALUES (4, 'Ralph', 'Carestia', '885-1453', 37000.00, 1500.00,'5-Jan-1990', 1, 1); INSERT INTO Faculty VALUES (5, 'Todd', 'Breedlove', '885-1577', 23000.00, NULL,'15-Sep-1999', 2, 1); INSERT INTO Faculty VALUES (6, 'Jamie', 'Zipay', '885-1543', 26000.00, NULL,'7-Jan- 2001', 3, 1); INSERT INTO Faculty VALUES (7, 'Phong', 'Nguyen', '885-1599', 25000.00, NULL,'15- Sep-1999', 3, 1); INSERT INTO Faculty VALUES (8, 'Sherry', 'Yang', '885-1594', 27000.00, NULL,'15- Sep-1997', 2, 1); INSERT INTO Faculty VALUES (9, 'Lynda', 'Baker', '885-1672', 38000.00, 3000.00, '15-Sep-1989', Null, 2); INSERT INTO Faculty VALUES (10, 'Maria Lynn', 'Kessler', '885-1674', 26000.00, NULL, '15-Sep-2003', 8, 2); INSERT INTO Faculty VALUES (11, 'John', 'Puckett', '885-1678', 39000.00, 3000.00, '15-Sep-1989', Null, 3); INSERT INTO Faculty VALUES (12, 'Robin', 'Schwartz', '885-1398', 9000.00, NULL, '15-Sep-1999', 10, 3); INSERT INTO Faculty VALUES (13, 'Jim', 'Long', '885-1580', 19500.00, NULL, '15-Sep- 2000', 2, 1); INSERT INTO Faculty VALUES (14, 'Tim', 'Stewart', '851-5160', 19000.00, NULL, '15- Sep-2000', 2, 1); INSERT INTO Faculty VALUES (15, 'Leo', 'Dubray', '885-1492', 17000.00, NULL, '15- Sep-2001', 8, 2); INSERT INTO Faculty VALUES (16, 'Michele', 'Malott', '885-1395', 6000.00, NULL, '15-Sep-2005', 8, 2); INSERT INTO ProfRank VALUES (1, 'Instructor', 00000.00, 10000.00); INSERT INTO ProfRank VALUES (2, 'Assistant Professor', 10000.00, 20000.00); INSERT INTO ProfRank VALUES (3, 'Associate Professor', 20000.00, 30000.00); INSERT INTO ProfRank VALUES (4, 'Professor', 30000.00, 40000.00); INSERT INTO Student VALUES (1, 'Paul', 'Scott', '882-1002', 1); INSERT INTO Student VALUES (2, 'Chris', 'Ambiel', '883-1312', 13); INSERT INTO Student VALUES (3, 'Jake', 'Brownson', '882-3424', 5); INSERT INTO Student VALUES (4, 'Farhad', 'Shakiba', '884-1231', 5); INSERT INTO Student VALUES (5, 'Allan', 'Snippen', '882-2342', 5); INSERT INTO Student VALUES (6, 'Michael', 'Hart', '882-5464', 12); INSERT INTO Student VALUES (7, 'Jonathan', 'Thibeau', '883-2342', 12); INSERT INTO Student VALUES (8, 'Alberto', 'Martinez', '882-8796', 2); INSERT INTO Student VALUES (9, 'Jeanie', 'King', '891-1234', 3);

INSERT INTO Student VALUES (10, 'Jason', 'Richards', '882-3456', 3); INSERT INTO Student VALUES (11, 'Justin', 'Royse', '885-1111', 3); INSERT INTO Student VALUES (12, 'Xinger', 'Yu', '883-2322', 2); INSERT INTO Student VALUES (13, 'Storm', 'Dain', '885-3212', 13); INSERT INTO Student VALUES (14, 'TJ', 'Atterberry', '883-1231', 12); INSERT INTO Student VALUES (15, 'Roscoe', 'Casita', '883-1213', 4); INSERT INTO Student VALUES (16, 'Shad', 'Cole', '882-3232', 4); INSERT INTO Student VALUES (17, 'Luke', 'Goodale', '885-1002', 4); INSERT INTO Student VALUES (18, 'Kyle', 'Spencer', '885-1012', 4); INSERT INTO Student VALUES (19, 'Ed', 'Hudson', '882-1878', 7); INSERT INTO Student VALUES (20, 'Scott', 'Ore', '883-9303', 7); INSERT INTO Student VALUES (21, 'Ryan', 'McCarty', '884-1922', 6); INSERT INTO Student VALUES (22, 'Devan', 'Stormont', '883-1999', 4); INSERT INTO Student VALUES (23, 'Jeffrey', 'Bernt', '882-9999', 8); INSERT INTO Student VALUES (24, 'Chris', 'Gheen', '883-3434', 8); INSERT INTO Student VALUES (25, 'Cody', 'Zuschlag', '885-9654', 9); INSERT INTO Student VALUES (26, 'Kevin', 'Wong', '883-1233', 9); INSERT INTO Student VALUES (27, 'Andrew', 'Wilson', '885-2322', 10); INSERT INTO Student VALUES (28, 'Jesse', 'Stafford', '882-2328', 10); INSERT INTO Student VALUES (29, 'Kevin', 'Roberts', '882-1765', 14); INSERT INTO Student VALUES (30, 'Tim', 'Clark', '882-8888', 2); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (1, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (2, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (3, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (3, 2); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (4, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (4, 2); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (5, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (5, 2); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (6, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (7, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (8, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (9, 2); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (10, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (10, 2); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (11, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (11, 2); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (12, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (13, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (14, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (15, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (16, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (17, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (18, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (19, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (20, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (21, 2); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (22, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (23, 3); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (24, 3); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (25, 3); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (26, 3); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (27, 4); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (28, 4); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (29, 4); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (30, 1);

Instructions Load the following SQL script into SQL Server Management Studio. Complete the following exercises in a single document. For each exercise, provide any SQL code and a screenshot of the output where applicable. Exercises 1. Show all the names of the faculty that are making more than $20000. 2. Show all the faculty names who make less than $10000 or more than $30000. 3. Display the faculty name, phone number, and start date for all employees hired between September 1, 1999, and September 1, 2003. Order the output in ascending order by the start date. 4. Display all faculty members, in alphabetical order, for those people who work in departments 1, 2, and 4. 5. Display the name and hire date of every faculty member who was hired in 1999. 6. Display the name and phone number for all faculty members who don't have a manager. 7. Display the name, salary, and stipend for all faculty who earn stipends. Sort the data by an ascending order on salary and stipend. 8. Display the names of the faculty whose third letter in their first name is an I. 9. Display the names of all faculty who have two t's in their last name and are in department 1 or their manager is 1. 10. Display the name, salary for all faculty whose salary is not equal to $10000, $20000, or $30000. 11. Display the current date and label the column Date. 12. Display the faculty number, name, salary, and their salary increased by 15%. The raised salary should be displayed as a whole number. 13. Modify question 12 so that we add a new column to show the delta between the old salary and the new salary. Label the column Increase. 14. Show the number of months worked for all employees. The number of months should be rounded to the nearest whole number. Sort, in ascending order, by the number of months worked. Label the column MONTHS_WORKED. 15. Display all faculty members in the following format: Caldwell earns $3,000.00 monthly but wants $15,000.00.(3 times their current monthly salary) Give the column the name Pipe Dream. 16. Display the name and salary of all faculty members. Format the salary to be 15 characters long, left-padded with $. Label the column salary. 17. Display the names, with only the first letter capitalized, and the length of their name for all faculty members whose last name starts with a C, A, or D. 18. Display the faculty name, hire date, and the day of the week they started. Order the results by the day of the week starting with Monday. 19. Display the name and stipend for all faculty members. If they don't have a stipend, display "Not a Director or Chair". 20. Display all faculty members' last names followed by a row of asterisks which represent every $1000 of their salary. Order the data in descending order by their salaries. Submit Single document with your solutions in Word or PDF format submitted to Canvas by the time/date, as shown on Canvas for the challenge. USE (MASTER) Go CREATE DATABASE [Faculty] Go USE [Faculty] CREATE TABLE ProfRank ( ProfRankID INT CONSTRAINT ProfRank_PK PRIMARY KEY, ProfRank Name NVARCHAR(30), Minimumm DECIMAL (8,2), Maximum DECIMAL (8,2) ); CREATE TABLE Dept ( DeptID INT CONSTRAINT Dept_PK PRIMARY KEY, Dept Name NVARCHAR(50) ); CREATE TABLE Major ( MajorID INT CONSTRAINT Major_PK PRIMARY KEY, Major Name NVARCHAR(40) ); CREATE TABLE Faculty ( FacultyID FirstName Last Name Phone Salary Stipend HireDate SupervisorID Faculty (FacultyID), DeptID Dept (DeptID) ); INT CONSTRAINT Faculty_PK PRIMARY KEY, NVARCHAR(15), NVARCHAR(15), NVARCHAR(10), DECIMAL (8, 2), DECIMAL (7, 2), DATE, INT CONSTRAINT Faculty Faculty_FK REFERENCES INT CONSTRAINT Faculty_Dept_FK REFERENCES CREATE TABLE Student ( StudentID INT CONSTRAINT Student_PK PRIMARY KEY, First Name NVARCHAR(15), Last Name NVARCHAR(15), Phone NVARCHAR(10), AdvisorID INT CONSTRAINT Student_Faculty_FK REFERENCES Faculty (FacultyID) ); CREATE TABLE Student Major ( MajorID INT CONSTRAINT StudentMajor_Major_FK REFERENCES Major(MajorID), Student ID INT CONSTRAINT Student Major_Student_FK REFERENCES Student (StudentID), CONSTRAINT Student Major PK PRIMARY KEY (Major ID, StudentID) ); INSERT INTO Major VALUES(1, 'Software Engineering Technology'); INSERT INTO Major VALUES(2, 'Computer Engineering Technology'); INSERT INTO Major VALUES(3, 'Applied Psychology'); INSERT INTO Major VALUES (4, 'Communication Studies'); INSERT INTO Dept VALUES(1, "Computer Systems Engineering Technology') INSERT INTO Dept VALUES (2, 'Humanities and Social Science'); INSERT INTO Dept VALUES (3, 'Communication'); INSERT INTO Dept VALUES (4, 'Electronics Engineering Technology'); INSERT INTO Faculty VALUES (1, 'Lucas', 'Cordova', '885-1230', 25000.00, 3000.00, '15-Sep-2016', Null, 1); INSERT INTO Faculty VALUES (2, 'Calvin', 'Caldwell', '885-1598', 33000.00, 3000.00, '15-Sep-1986', Null, 1); INSERT INTO Faculty VALUES (3, 'Randy', 'Albert', '885-1596', 35000.00, 1500.00, '15-Sep-1984', 1, 1); INSERT INTO Faculty VALUES (4, 'Ralph', 'Carestia', '885-1453', 37000.00, 1500.00, '5-Jan-1990', 1, 1); INSERT INTO Faculty VALUES (5, 'Todd', 'Breedlove', '885-1577', 23000.00, NULL, '15-Sep-1999', 2, 1); INSERT INTO Faculty VALUES (6, 'Jamie', 'Zipay', '885-1543', 26000.00, NULL, ' 7-Jan- 2001', 3, 1); INSERT INTO Faculty VALUES (7, 'Phong', 'Nguyen', '885-1599', 25000.00, NULL, '15- Sep-1999', 3, 1); INSERT INTO Faculty VALUES (8, 'Sherry', 'Yang', '885-1594', 27000.00, NULL, '15- Sep-1997', 2, 1); INSERT INTO Faculty VALUES (9, 'Lynda', 'Baker', '885-1672', 38000.00, 3000.00, '15-Sep-1989', Null, 2); INSERT INTO Faculty VALUES (10, 'Maria Lynn', 'Kessler', '885-1674', 26000.00, NULL, '15-Sep-2003', 8, 2); INSERT INTO Faculty VALUES (11, 'John', 'Puckett', '885-1678', 39000.00, 3000.00, '15-Sep-1989', Null, 3); INSERT INTO Faculty VALUES (12, 'Robin', 'Schwartz', '885-1398', 9000.00, NULL, '15-Sep-1999', 10, 3); INSERT INTO Faculty VALUES (13, 'Jim', 'Long', '885-1580', 19500.00, NULL, '15-Sep- 2000', 2, 1); INSERT INTO Faculty VALUES (14, 'Tim', 'Stewart', '851-5160', 19000.00, NULL, '15- Sep-2000', 2, 1); INSERT INTO Faculty VALUES (15, "Leo', 'Dubray', '885-1492', 17000.00, NULL, '15- Sep-2001', 8, 2); INSERT INTO Faculty VALUES (16, 'Michele', 'Malott', '885-1395', 6000.00, NULL, '15-Sep-2005', 8, 2); INSERT INTO ProfRank VALUES (1, 'Instructor', 00000.00, 10000.00); INSERT INTO ProfRank VALUES (2, 'Assistant Professor', 10000.00, 20000.00); INSERT INTO ProfRank VALUES (3, 'Associate Professor', 20000.00, 30000.00); INSERT INTO ProfRank VALUES (4, Professor', 30000.00, 40000.00); INSERT INTO Student VALUES (1, 'Paul', 'Scott', '882-1002', 1); INSERT INTO Student VALUES (2, 'Chris', 'Ambiel', '883-1312', 13); INSERT INTO Student VALUES (3, 'Jake', 'Brownson '882-3424', 5); INSERT INTO Student VALUES (4, 'Farhad', 'Shakiba' 1884-1231', 5); INSERT INTO Student VALUES (5, 'Allan' Snippen', '882-2342', 5); INSERT INTO Student VALUES (6, 'Michael' Hart', '882-5464', 12); INSERT INTO Student VALUES (7, 'Jonathan', 'Thibeau', '883-2342', 12); INSERT INTO Student VALUES (8, 'Alberto' Martinez' '882-8796', 2); INSERT INTO Student VALUES (9, Jeanie', 'King', '891-1234', 3); , INSERT INTO Student VALUES (10, Jason', 'Richards '882-3456', 3); INSERT INTO Student VALUES (11, 'Justin', 'Royse', '885-1111', 3); INSERT INTO Student VALUES (12, 'Xinger' 'Yu', '883-2322', 2); INSERT INTO Student VALUES (13, Storm',''Dain', '885-3212', 13); INSERT INTO Student VALUES (14, 'TJ', 'Atterberry', '883-1231', 12); INSERT INTO Student VALUES (15, 'Roscoe', Casita', '883-1213', 4); INSERT INTO Student VALUES (16, 'Shad' Cole', '882-3232', 4); INSERT INTO Student VALUES (17, 'Luke', 'Goodale', '885-1002', 4); INSERT INTO Student VALUES (18, 'Kyle', 'Spencer', '885-1012', 4); INSERT INTO Student VALUES (19, 'Ed', 'Hudson' '882-1878', 7); INSERT INTO Student VALUES (20, 'Scott', Ore' 883-9303', 7); INSERT INTO Student VALUES (21, 'Ryan', 'McCarty', '884-1922', 6); INSERT INTO Student VALUES (22, 'Devan', 'Stormont', '883-1999', 4); INSERT INTO Student VALUES (23, Jeffrey', 'Bernt' '882-9999', 8); INSERT INTO Student VALUES (24, Chris", "Gheen', 1883-3434', 8); INSERT INTO Student VALUES (25, 'Cody', 'Zuschlag', '885-9654', 9); INSERT INTO Student VALUES (26, Kevin' ', 'Wong', 1883-1233', 9); INSERT INTO Student VALUES (27, 'Andrew', 'Wilson', '885-2322', 10); INSERT INTO Student VALUES (28, Jesse', 'Stafford', '882-2328', 10); INSERT INTO Student VALUES (29, Kevin', 'Roberts', '882-1765', 14); INSERT INTO Student VALUES (30, 'Tim', 'Clark', '882-8888', 2); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (1, 1); INSERT INTO Student Major (Student ID, MajorID) VALUES (2, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (3, 1); INSERT INTO Student Major (Student ID, MajorID) VALUES (3, 2); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (4, 1); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (4, 2); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (5, 1); INSERT INTO Student Major (Student ID, MajorID) VALUES (5, 2); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (6, 1); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (7, 1); INSERT INTO Student Major (Student ID, MajorID) VALUES (8, 1); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (9, 2); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (10, 1); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (10, 2); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (11, 1); INSERT INTO Student Major (Student ID, MajorID) VALUES (11, 2); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (12, 1); INSERT INTO Student Major (Student ID, MajorID) VALUES (13, 1); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (14, 1); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (15, 1); INSERT INTO Student Major (Student ID, MajorID) VALUES (16, 1); INSERT INTO Student Major (Student ID, MajorID) VALUES (17, 1); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (18, 1); INSERT INTO Student Major (Student ID, MajorID) VALUES (19, 1); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (20, 1); INSERT INTO Student Major (Student ID, MajorID) VALUES (21, 2); INSERT INTO StudentMajor (StudentID, MajorID) VALUES (22, 1); INSERT INTO Student Major (Student ID, MajorID) VALUES (23, 3); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (24, 3); INSERT INTO Student Major (Student ID, MajorID) VALUES (25, 3); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (26, 3); INSERT INTO Student Major (Student ID, MajorID) VALUES (27, 4); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (28, 4); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (29, 4); INSERT INTO StudentMajor (Student ID, MajorID) VALUES (30, 1)