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 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 'T. 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. 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)