Follow detailed instructions that I will provide via attachments. Use Microsoft SQL Server Management Studio. Answer all questions and meet all requirements. I will provide each database required to complete this assignment. You will need to send back the .sql file that contains the scripts you use to answer each question, along with a microsoft word document that contains a screenshot of the result of each question. Label each screenshot according to each question.
USE Master; GO CREATE DATABASE Capstone; GO USE Capstone; GO Create Table WorkLocs( TeamWorkLoc varchar(10) not null primary key, Building varchar(20), NumWorkStations int ) Create Table Clients( ClientID INT PRIMARY KEY, ClientName varchar (100) not null, ) Create Table FacultySponsors( FacultyID INT PRIMARY KEY, FirstName varchar(50) not null, LastName varchar (50) not null, Title varchar (20), EmailAddress varchar(70) not null ) Create Table ProjectTeams( TeamID varchar(25) PRIMARY KEY, AcademicYear int not null, AcademicTerm varchar (10) not null, TeamWorkLoc varchar (10), Foreign Key (TeamWorkLoc) references WorkLocs(TeamWorkLoc) ) Create Table ClientProjects( ClientID INT, ClientProjectID varchar(20), ClientProjectName varchar(120) not null, FacultySponsorID INT, TeamID varchar(25), primary key (ClientID,ClientProjectID), foreign key (ClientID) references Clients(ClientID), foreign key (FacultySponsorID) references FacultySponsors(FacultyID), foreign key (TeamID) references ProjectTeams(TeamID) ) Create Table Courses( CourseID int not null primary key, CourseTitle varchar (80) not null ) Create Table Students( ID INT Not null IDENTITY Primary Key, StudentID varchar(20) not null, LastName varchar (50) not null, FirstName varchar (50) not null, Email varchar (70) not null, CapstoneLevel varchar(5) Not Null, TeamID varchar(25), PMIDvarchar(20), foreign key (TeamID) references ProjectTeams(TeamID) ) Create Table StudentCourses( StudentID varchar(20) not null, CourseID int not null, AcademicTermvarchar(10) NOT NULL, AcademicYearINT NOT NULL, primary key (StudentID,CourseID), foreign key (CourseID) references Courses(CourseID) ) GO -- LOAD WORK LOCATION DATA INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('1103-A','Bevill','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('1103-B','Bevill','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('112-A','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('112-B','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('117-A','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('117-B','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('117-C','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('117-D','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('118-A','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('118-B','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('118-C','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('118-D','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('121-A','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('124-A','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('124-B','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('124-C','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('124-D','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('125-A','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('125-B','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('125-C','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('125-D','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('138-A','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('138-B','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('138-C','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('138-D','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('138-E','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('138-F','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('138-G','AIME','6'); INSERT INTO WorkLocs (TeamWorkLoc,Building,NumWorkstations) VALUES ('138-H','AIME','6'); -- LOAD CLIENT DATA INSERT INTO Clients VALUES('1','Carters'); INSERT INTO Clients VALUES('2','City of Tuscaloosa'); INSERT INTO Clients VALUES('3','UA MIS'); INSERT INTO Clients VALUES('4','Georgia Little League'); INSERT INTO Clients VALUES('5','Haiti eCommerce'); INSERT INTO Clients VALUES('6','Hunt Refining'); INSERT INTO Clients VALUES('7','Neptune Technology'); INSERT INTO Clients VALUES('8','Create Honduras'); INSERT INTO Clients VALUES('9','Stanford'); INSERT INTO Clients VALUES('10','The Edge'); INSERT INTO Clients VALUES('11','UA CCS'); INSERT INTO Clients VALUES('12','UA Financial Affairs'); INSERT INTO Clients VALUES('13','UA Grad School'); INSERT INTO Clients VALUES('14','UA Athletics'); INSERT INTO Clients VALUES('15','UNUM'); INSERT INTO Clients VALUES('16','Youth Umpire Scheduling and Training'); INSERT INTO Clients VALUES('17','Regions'); INSERT INTO Clients VALUES('18','SPOC'); INSERT INTO Clients VALUES('19','EBSCO'); INSERT INTO Clients VALUES('20','ServiceMaster'); INSERT INTO Clients VALUES('21','Afflink'); INSERT INTO Clients VALUES('22','AutoZone'); INSERT INTO Clients VALUES('23','Books-A-Million'); INSERT INTO Clients VALUES('24','Alabama Department of Senior Services'); INSERT INTO Clients VALUES('25','ISM'); -- LOAD FacultySponsors DATA INSERT INTO FacultySponsors VALUES('1','Greg','Cottrell', '', '
[email protected] '); INSERT INTO FacultySponsors VALUES('2','Rhadika','DeLaire', '', '
[email protected] '); INSERT INTO FacultySponsors VALUES('3','Richard','Doherty', '', '
[email protected]'); INSERT INTO FacultySponsors VALUES('4','Stefan','Kim', '', '
[email protected] '); INSERT INTO FacultySponsors VALUES('5','Jeff','Lucas', '', '
[email protected] '); INSERT INTO FacultySponsors VALUES('6','Ron','Norman', 'Dr', '
[email protected] '); INSERT INTO FacultySponsors VALUES('7','Josh','Perdue', '', '
[email protected] '); INSERT INTO FacultySponsors VALUES('8','Kiron','Shastry', 'Dr', '
[email protected] '); INSERT INTO FacultySponsors VALUES('9','Andrew','Watson', '', '
[email protected] '); INSERT INTO FacultySponsors VALUES('10','Brett','Coburn', '', '
[email protected]'); INSERT INTO FacultySponsors VALUES('11','Hassler','Ed', 'Dr', '
[email protected]'); INSERT INTO FacultySponsors VALUES('12','Davis','Heather', '', '
[email protected]'); --Load ProjectTeams DATA INSERT INTO ProjectTeams VALUES('FALL15-01','2015','FALL', '117-C'); INSERT INTO ProjectTeams VALUES('FALL15-02','2015','FALL', '118-A'); INSERT INTO ProjectTeams VALUES('FALL15-03','2015','FALL', '117-A'); INSERT INTO ProjectTeams VALUES('FALL15-04','2015','FALL', '1103-B'); INSERT INTO ProjectTeams VALUES('FALL15-05','2015','FALL', '138-H'); INSERT INTO ProjectTeams VALUES('FALL15-06','2015','FALL', '118-C'); INSERT INTO ProjectTeams VALUES('FALL15-07','2015','FALL', '125-D'); INSERT INTO ProjectTeams VALUES('FALL15-08','2015','FALL', '112-B'); INSERT INTO ProjectTeams VALUES('FALL15-09','2015','FALL', '138-F'); INSERT INTO ProjectTeams VALUES('FALL15-10','2015','FALL', '1103-A'); INSERT INTO ProjectTeams VALUES('FALL15-11','2015','FALL', '121-A'); INSERT INTO ProjectTeams VALUES('FALL15-12','2015','FALL', '138-G'); INSERT INTO ProjectTeams VALUES('FALL15-13','2015','FALL', '125-B'); INSERT INTO ProjectTeams VALUES('FALL15-14','2015','FALL', '124-A'); INSERT INTO ProjectTeams VALUES('FALL15-15','2015','FALL', '124-B'); INSERT INTO ProjectTeams VALUES('FALL15-16','2015','FALL', '118-D'); INSERT INTO ProjectTeams VALUES('FALL15-17','2015','FALL', '124-D'); INSERT INTO ProjectTeams VALUES('FALL15-18','2015','FALL', '125-A'); INSERT INTO ProjectTeams VALUES('FALL15-19','2015','FALL', '125-C'); INSERT INTO ProjectTeams VALUES('FALL15-20','2015','FALL', '138-B'); INSERT INTO ProjectTeams VALUES('FALL15-21','2015','FALL', '118-B'); INSERT INTO ProjectTeams VALUES('FALL15-22','2015','FALL', '138-A'); INSERT INTO ProjectTeams VALUES('FALL15-23','2015','FALL', '138-D'); INSERT INTO ProjectTeams VALUES('FALL15-24','2015','FALL', '112-A'); INSERT INTO ProjectTeams VALUES('FALL15-25','2015','FALL', '138-C'); INSERT INTO ProjectTeams VALUES('FALL15-26','2015','FALL', '117-D'); INSERT INTO ProjectTeams VALUES('FALL15-27','2015','FALL', '138-E'); INSERT INTO ProjectTeams VALUES('FALL15-28','2015','FALL', '117-B'); INSERT INTO ProjectTeams VALUES('SPRING16-01','2016','SPRING', '1103-B'); INSERT INTO ProjectTeams VALUES('SPRING16-02','2016','SPRING', '117-A'); INSERT INTO ProjectTeams VALUES('SPRING16-03','2016','SPRING', '124-C'); INSERT INTO ProjectTeams VALUES('SPRING16-04','2016','SPRING', '118-C'); INSERT INTO ProjectTeams VALUES('SPRING16-05','2016','SPRING', '138-B'); INSERT INTO ProjectTeams VALUES('SPRING16-06','2016','SPRING', '125-D'); INSERT INTO ProjectTeams VALUES('SPRING16-07','2016','SPRING', '118-D'); INSERT INTO ProjectTeams VALUES('SPRING16-08','2016','SPRING', '117-B'); INSERT INTO ProjectTeams VALUES('SPRING16-09','2016','SPRING', '138-H'); INSERT INTO ProjectTeams VALUES('SPRING16-10','2016','SPRING', '124-B'); INSERT INTO ProjectTeams VALUES('SPRING16-11','2016','SPRING', '124-A'); INSERT INTO ProjectTeams VALUES('SPRING16-12','2016','SPRING', '112-A'); INSERT INTO ProjectTeams VALUES('SPRING16-13','2016','SPRING', '138-G'); INSERT INTO ProjectTeams VALUES('SPRING16-14','2016','SPRING', '138-E'); INSERT INTO ProjectTeams VALUES('SPRING16-15','2016','SPRING', '138-A'); INSERT INTO ProjectTeams VALUES('SPRING16-16','2016','SPRING', '138-F'); INSERT INTO ProjectTeams VALUES('SPRING16-17','2016','SPRING', '121-A'); INSERT INTO ProjectTeams VALUES('SPRING16-18','2016','SPRING', '124-D'); INSERT INTO ProjectTeams VALUES('SPRING16-19','2016','SPRING', '117-D'); INSERT INTO ProjectTeams VALUES('SPRING16-20','2016','SPRING', '117-C'); INSERT INTO ProjectTeams VALUES('SPRING16-21','2016','SPRING', '118-A'); INSERT INTO ProjectTeams VALUES('SPRING16-22','2016','SPRING', '125-B'); INSERT INTO ProjectTeams VALUES('SPRING16-23','2016','SPRING', '138-D'); INSERT INTO ProjectTeams VALUES('SPRING16-24','2016','SPRING', '1103-A'); INSERT INTO ProjectTeams VALUES('SPRING16-25','2016','SPRING', '125-C'); INSERT INTO ProjectTeams VALUES('SPRING16-26','2016','SPRING', '125-A'); INSERT INTO ProjectTeams VALUES('SPRING16-27','2016','SPRING', '118-B'); INSERT INTO ProjectTeams VALUES('SPRING16-28','2016','SPRING', '138-C'); INSERT INTO ProjectTeams VALUES('FALL16-01','2016','FALL', '138-B'); INSERT INTO ProjectTeams VALUES('FALL16-02','2016','FALL', '138-F'); INSERT INTO ProjectTeams VALUES('FALL16-03','2016','FALL', '124-A'); INSERT INTO ProjectTeams VALUES('FALL16-04','2016','FALL', '112-B'); INSERT INTO ProjectTeams VALUES('FALL16-05','2016','FALL', '124-B'); INSERT INTO ProjectTeams VALUES('FALL16-06','2016','FALL', '138-H'); INSERT INTO ProjectTeams VALUES('FALL16-07','2016','FALL', '121-A'); INSERT INTO ProjectTeams VALUES('FALL16-08','2016','FALL', '117-C'); INSERT INTO ProjectTeams VALUES('FALL16-09','2016','FALL', '117-D'); INSERT INTO ProjectTeams VALUES('FALL16-10','2016','FALL', '124-C'); INSERT INTO ProjectTeams VALUES('FALL16-11','2016','FALL', '138-G'); INSERT INTO ProjectTeams VALUES('FALL16-12','2016','FALL', '117-B'); INSERT INTO ProjectTeams VALUES('FALL16-13','2016','FALL', '118-A'); INSERT INTO ProjectTeams VALUES('FALL16-14','2016','FALL', '125-A'); INSERT INTO ProjectTeams VALUES('FALL16-15','2016','FALL', '125-D'); INSERT INTO ProjectTeams VALUES('FALL16-16','2016','FALL', '118-B'); INSERT INTO ProjectTeams VALUES('FALL16-17','2016','FALL', '118-C'); INSERT INTO ProjectTeams VALUES('FALL16-18','2016','FALL', '112-A'); INSERT INTO ProjectTeams VALUES('FALL16-19','2016','FALL', '138-A'); INSERT INTO ProjectTeams VALUES('FALL16-20','2016','FALL', '138-E'); INSERT INTO ProjectTeams VALUES('FALL16-21','2016','FALL', '125-B'); INSERT INTO ProjectTeams VALUES('FALL16-22','2016','FALL', '124-D'); INSERT INTO ProjectTeams VALUES('FALL16-23','2016','FALL', '118-D'); INSERT INTO ProjectTeams VALUES('FALL16-24','2016','FALL', '125-C'); INSERT INTO ProjectTeams VALUES('SPRING17-01','2017','SPRING', '117-D'); INSERT INTO ProjectTeams VALUES('SPRING17-02','2017','SPRING', '138-H'); INSERT INTO ProjectTeams VALUES('SPRING17-03','2017','SPRING', '125-B'); INSERT INTO ProjectTeams VALUES('SPRING17-04','2017','SPRING', '118-A'); INSERT INTO ProjectTeams VALUES('SPRING17-05','2017','SPRING', '117-C'); INSERT INTO ProjectTeams VALUES('SPRING17-06','2017','SPRING', '118-B'); INSERT INTO ProjectTeams VALUES('SPRING17-07','2017','SPRING', '124-A'); INSERT INTO ProjectTeams VALUES('SPRING17-08','2017','SPRING', '118-C'); INSERT INTO ProjectTeams VALUES('SPRING17-09','2017','SPRING', '125-C'); INSERT INTO ProjectTeams VALUES('SPRING17-10','2017','SPRING', '118-D'); INSERT INTO ProjectTeams VALUES('SPRING17-11','2017','SPRING', '117-B'); INSERT INTO ProjectTeams VALUES('SPRING17-12','2017','SPRING', '138-B'); INSERT INTO ProjectTeams VALUES('SPRING17-13','2017','SPRING', '138-E'); INSERT INTO ProjectTeams VALUES('SPRING17-14','2017','SPRING', '124-B'); INSERT INTO ProjectTeams VALUES('SPRING17-15','2017','SPRING', '112-B'); INSERT INTO ProjectTeams VALUES('SPRING17-16','2017','SPRING', '138-F'); INSERT INTO ProjectTeams VALUES('SPRING17-17','2017','SPRING', '112-A'); INSERT INTO ProjectTeams VALUES('SPRING17-18','2017','SPRING', '121-A'); INSERT INTO ProjectTeams VALUES('SPRING17-19','2017','SPRING', '125-A'); INSERT INTO ProjectTeams VALUES('SPRING17-20','2017','SPRING', '125-D'); INSERT INTO ProjectTeams VALUES('SPRING17-21','2017','SPRING', '138-G'); INSERT INTO ProjectTeams VALUES('SPRING17-22','2017','SPRING', '124-C'); INSERT INTO ProjectTeams VALUES('SPRING17-23','2017','SPRING', '124-D'); INSERT INTO ProjectTeams VALUES('SPRING17-24','2017','SPRING', '138-A'); --LOAD CLIENTPROJECTS DATA INSERT INTO ClientProjects VALUES('1','F16-01','Distribution Center Self-Guided Tour Application', '2', 'FALL16-01'); INSERT INTO ClientProjects VALUES('1','F16-02','Distribution Center TV Web Application', '4', 'FALL16-02'); INSERT INTO ClientProjects VALUES('1','S17-01','PCI iOS App', '1', 'SPRING17-01'); INSERT INTO ClientProjects VALUES('1','F15-01','Insurance Admin and Reporting', '1', 'FALL15-01'); INSERT INTO ClientProjects VALUES('1','F15-02','Registration System', '2', 'FALL15-02'); INSERT INTO ClientProjects VALUES('1','S16-01','Website Expansion', '4', 'SPRING16-01'); INSERT INTO ClientProjects VALUES('2','F16-01','Data Visualization Tool', '1', 'FALL16-03'); INSERT INTO ClientProjects VALUES('2','F15-01','App Dev Project', '6', 'FALL15-03'); INSERT INTO ClientProjects VALUES('2','S16-01','Development Research Technology', '7', 'SPRING16-02'); INSERT INTO ClientProjects VALUES('3','F16-01','Coding Challenge Phase 3', '8', 'FALL16-04'); INSERT INTO ClientProjects VALUES('3','F16-02','Salesforce Phase 3', '5', 'FALL16-05'); INSERT INTO ClientProjects VALUES('3','S17-01','Coding Challenge Phase 4', '2', 'SPRING17-02'); INSERT INTO ClientProjects VALUES('3','S17-02','Salesforce Phase 4', '6', 'SPRING17-03'); INSERT INTO ClientProjects VALUES('3','S16-01','Salesforce Phase 2', '3', 'SPRING16-03'); INSERT INTO ClientProjects VALUES('3','F15-01','Salesforce Phase 1', '8', 'FALL15-04'); INSERT INTO ClientProjects VALUES('3','F15-02','Coding Challenge Phase 1', '11', 'FALL15-05'); INSERT INTO ClientProjects VALUES('3','S16-02','Coding Challenge Phase 2', '5', 'SPRING16-04'); INSERT INTO ClientProjects VALUES('4','F16-01','Phase 3', '6', 'FALL16-06'); INSERT INTO ClientProjects VALUES('4','S17-01','Administration Portal ', '4', 'SPRING17-04'); INSERT INTO ClientProjects VALUES('4','S17-02','Player Registration System', '2', 'SPRING17-05'); INSERT INTO ClientProjects VALUES('4','S16-01','CRM Selection and Implementation', '7', 'SPRING16-05'); INSERT INTO ClientProjects VALUES('5','F16-01','Haiti eCommerce', '3', 'FALL16-07'); INSERT INTO ClientProjects VALUES('5','F15-01','Website Expansion', '10', 'FALL15-06'); INSERT INTO ClientProjects VALUES('5','S16-01','NVIEW IOS to Android', '12', 'SPRING16-06'); INSERT INTO ClientProjects VALUES('6','F16-01','Software Selection', '3', 'FALL16-08'); INSERT INTO ClientProjects VALUES('6','F15-01','CMIU iOS to Android', '11', 'FALL15-07'); INSERT INTO ClientProjects VALUES('6'