Assignment:
Using Microsoft SQL Server CREATE DATABASE, CREATE TABLE, and SQL INSERT statements.
Use the CREATE DATABASE statement to create a SQL Server database for Kudler Fine Foods.
In the Kudler Fine Foods Database, use the CREATE TABLE statement to create the following two tables with the fields identified below:
Note: Specify appropriate SQL Server data types for the fields in these tables. The primary key from the Job_title table will appear as a foreign key in the Employee table.
Employee
Emp_ID (Primary Key, Identity)
Last_name
First_name
Emp_address
Emp_city
State_code
Telephone_area_code
Telephone_number
EEO_1_classification
Hire_date
Emp_salary
Emp_gender
Emp_age
Job_ID (Foreign key to Job_title table)
Job_title
Job_ID (Primary key)
EEO-1 Classification
Job_title
Job-description
Exempt_status (Yes or No)
Check your results by examining the tables and columns in the SQL. You also should run the SP_HELP stored procedure to list the tables and their columns. The commands are SP_HELP Employee and SP_HELP Job_title.
Using the SQL INSERT statement:
Go to the Human Resources department in the Kudler Fine Foods intranet. Using information found in the Employee Files for the La Jolla and Encinitas stores, enter records into the employee table for the following employees:
Glenn Edelman
Eric McMullen
Raj Slentz
Erin Broun
Donald Carpenter
David Esquivez
Nancy Sharp
Using the Kudler Fine Foods Job Classifications and Job Descriptions information, enter records into the job_title table for the following job titles:
Accounting Clerk
Asst. Manager
Bagger
Cashier
Computer Support Specialist
Director of Finance & Accounting
Retail Asst. Bakery & Pastry
Retail Asst. Butchers and Seafood Specialists
Stocker
Non-Exempt employees at Kudler Fine Foods are paid an hourly wage and are required to track their working hours. You need to enter an estimated yearly salary for these employees.
Check your results by using the SQL SELECT statement to list all of the columns and rows from both of your tables.
Employee Files
|
Position
|
EEO-1 Classification
|
Incumbent
|
Hire Date
|
Hourly Rate
|
Annualized Salary
|
Gender
|
Race
|
Age
|
|
Cashier
|
Sales Worker
|
Glenn Edelman
|
10/7/2003
|
$10.75
|
$21,500.00
|
M
|
Caucasian
|
64
|
|
Bagger
|
Sales Worker
|
Eric McMullen
|
11/1/2002
|
$6.75
|
$13,500.00
|
M
|
Caucasian
|
20
|
|
Assistant Manager
|
Officials & Managers
|
Raj Slentz
|
6/1/2000
|
N/A
|
$48,000.00
|
M
|
Asian
|
34
|
|
Bagger - 30 hours/wk
|
Sales Worker
|
Erin Broun
|
3/12/2003
|
$6.75
|
$10,530.00
|
F
|
Caucasian
|
24
|
|
Stocker
|
Office/Clerical
|
Donald Carpenter
|
11/1/2003
|
$7.50
|
$15,000.00
|
M
|
African American
|
18
|
|
Asst. -Butchers & Seafood Specialists
|
Operatives (Semi Skilled)
|
David Esquivez
|
7/25/2003
|
$9.25
|
$18,500.00
|
M
|
Hispanic
|
25
|
|
Cashier
|
Sales Workers
|
Nancy Sharp
|
7/12/2003
|
$10.50
|
$21,000.00
|
F
|
Caucasian
|
24
|