Creating a databse using Microsoft SQL Server

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.

In Microsoft SQL Server Management Studio, save your CREATE DATABASE, CREATE TABLE, and SELECT SQL statements as .SQL files. Save your SQL output as .RPT files. You will need to turn in both types of files (.SQL and .RPT)

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