Provide the SQL and a screen shot of each of the items below and submit them in a single .doc for grading.
(1) Create an SQL server database for the Kudler Fine Foods Virtual Organization. The name of the database should be KFF_UG.
(2) Create the following tables using fields listed below. In the Employee table, use the Employee ID as the primary key. In the Job_Title table, use the Job_Title_ID as the primary key. The primary key from the Job Title table appears as the foreign key in the Employee table. IMPORTANT NOTE: the Salary, Hire Date and Age may not be known when the data is first entered.
o Job_Title
• Job_Title_ID
• Employer Information Report (EEO-1) classification
• Job title
• Job description
• Exempt/non-exempt status
o Employee.
• Employee ID.
• Last name.
• First name.
• Street Address.
• City.
• State.
• Telephone number.
• Hire date.
• Salary.
• Age.
• Foreign key referencing the Job_Title table.
The data to be used in the tables is obtained from the Kudler Fine Foods Virtual Organization. This data is accessed as follows:
Materials → Virtual Organizations → Business → Kudler Fine Foods → Intranet (upper right)
Now, on the left you will find: Chart Of Accounts, Employee Files, Job Classifications.
(3) Use the information from the job classifications and descriptions to enter records into the Job Title table for the following titles:
o Accounting clerk.
o Assistant manager.
o Bagger.
o Cashier.
o Computer support specialist.
o Director of finance and accounting.
o Retail assistant bakery and pastry.
o Retail assistant butchers and seafood specialists.
o Stocker.
(4) Use the SQL INSERT statement to go to the human resources department in the Kudler Fine Foods Virtual Organization. Using information found in the employee files for the La Jolla and Encinitas stores, enter records into the Employee table for the following employees:
LaJolla:
o Glenn Edelman.
o Eric McMullen.
o Raj Slentz.
Encinitas:
o Erin Broun.
o Donald Carpenter.
o David Esquivez.
o Nancy Sharp.
o Ulysses Grant.
(5) Check results by providing a query that shows all of the data from the Job_Title table and a query that shows all of the data from the Employee table (two separate queries).
(6) Provide a query that gives the Employee_ID, First Name, Last Name, Salary and EEO_1 classification. HINT: Requires a join of the two tables.
Provide the SQL and a screen shot of each of the above items in a .doc. It is important that the screen shot show all of the output.