Requirements (Please see attached file)
You will be creating a database for a theme park. You must create a SQL Script to perform all the following functions:
Save your time - order a paper!
Get your paper written from scratch within the tight deadline. Our service is a reliable solution to all your troubles. Place an order on any task and we will take care of it. You won’t have to worry about the quality and deadlinesOrder Paper Now
Step 1: Create the tables
Table # 1 Name: Employee
Columns and Data Types:
state (char(2)), zip (char(5)), jobCode (char(4)) , dateOfBirth (date), certification(bit), salary(money) )
Table # 2 Name: Job:
Columns and Data Types:
Next You will write the script to create constraints on these two tables. The following constraints must be created:
integrity to the Job table’s primary key.
only be in either CA or FL.
the values ‘CAST’, ‘ENGI’, ‘INSP’ or ‘PMGR’
Step 2: Insert the data
Write the Insert statements to populate 3 sample employees. Make up any sample data for your employees. Make sure
your data doesn’t violate any constraints.
Write the Insert statements to populate the following available jobs codes and job descriptions:
CAST Cast Member
PMGR Project Manager
Step 3: Create Views
Write the statements to create the following views:
– vw_CertifiedEngineers: This View will show the empNumber, firstName, lastName and jobDesc of the
employees who are engineers and have a certification value of 1.
o Column Names: empNumber, firstName, lastName, jobDesc
– vw_ReadyToRetire: This View will show the empNumber, firstName and lastName of those employees who are
over 62 ( Hint: use the birthdate year to calculate their age based on the current date year)
o Column Names: empNumber, firstName, lastName
– vw_EmployeeAvgSalary: This view will show the average salary and the employee jobcode grouped per the
different job codes.
o Column Names: AvgSalary, jobCode
Step 4: Add indexes
Create the SQL Scripts for indexes on the following columns:
Index name: IDX_LastName Table: Employee Column: lastName
Index name: IDX_ssn Table: Employee Column: ssn
Step 5: Verify Objects
Verify Object names
Once you have completed your work and created the required objects in your database, you will need to run the
Project1Verifier to assure that:
– Your objects have been properly created
– Object naming is correct.
Run the Project1Verifier.sql script on the database where you created your objects. If any errors appear, you need to
review them before proceeding.