East Carolina Bank needs a Personnel database. Use this data model to create the Employee and Department tables. The supervisor relationships are shown on page 2. Include the Foreign Key constraint for only the "Head" relationship. The Foreign Key format for MS-SQL is:
Constraint nameofconstraint Foreign Key (nameofFK) References nameofPKtable (nameofPK)
Use the Insert statements on page 2 to determine the data types. Make up date of birth and salary for the employees, giving at least one worker a higher salary than his/her boss. (45 pts.) See image.
Insert the SQL query following each question. All lists should be sorted. Always use meaningful column headings.
Using the Delete command, try to delete Jason Meeks from the Employee table. Since Jason is the head of a department, the Foreign Key constraint should prevent you from doing this. If the delete is successful, the Foreign Key constraint is not correct and will have to be fixed. You will also have to add Jason back to the Employee table. Include this Delete command in your assignment submission. (10 pts.)
List each Department name and the name (first and last combined) and DOB of the Employee that heads that Department. (8 pts.) This question uses the ____________________ relationship. (2 pts.)
List the employee's name and salary and the supervisor's name and salary of any employee who makes more than their boss (combine their last and first names and use CAST to restrict their salary to two decimal places) e.g. Cast(EmpSalary as Decimal(10,2)). (8 pts.) This question uses the ____________________ relationship. (2 pts.)
For each department, list the department name, the number of employees, and the total salary budget. (8 pts.) This question uses the ____________________ relationship. (2 pts.)
List the Supervisor’s Department Name, and Department phone number, Supervisor's name, and Employee's name (combine their last and first names). (8 pts.) This question uses the _______________ and ________________ relationships. (2 pts.) Your output should look exactly like this. (notice the sort order) See image.
With the results of this SQL query on the screen, press the Print Screen key, put the cursor at the bottom of this document and press ctrl-V to paste it. (5 pts.)
submit all Create, Insert, Delete, and Select statements and any Update or Alter you may have used. Name this Word file as instructed in the Submission link.. Bank heirarchy.
When creating a hierarchy you must start at the top and work your way down. Notice everyone has a supervisor except the CEO. Insert statements.