/*
	Individual Assignment #1:  Kudler Fine Foods SQL Server Database
	Author:     Jim Gear
	Date:       October 26, 2008
	Filename:   2-Gear-IA.sql
	Version:    1.0
	Purpose:    This project is to create a SQL Server database for Kudler fine foods. The database and
	            tables will be created using the SQL CREATE statements as required in specification 2.1
	            of the assignment instructions. Additionally, primary keys will be applied to the tables.
	            Per specification 2.2 the SQL INSERT statement will be used to enter records into the 
	            tables. Specification 2.3 requires that the results be checked with the SELECT statement.
*/ 


/*Commented out the CREATE DATABASE statement after it was created. Otherwise an error would occur upon
  execution that Kudler already exists. Choose another name. I am assuming that you already have a database
  created named Kudler for testing purposes. If not, simply uncomment the statement*/

--CREATE DATABASE Kudler              
                                       
USE Kudler                             --Ensuring that the proper database is being used.

CREATE TABLE Employee                  /* Spec. 2.1 requires the use of the CREATE statement to create
                                          the tables. */ 
(
Emp_id                   int           NOT NULL IDENTITY(1,1)
  PRIMARY KEY,                          /*Spec 2.1 Emp_id has been selected as the Primary Key because
                                          it uniquely names a row in the table. */
Last_name                varchar(25)   NOT NULL,
First_name               varchar(25)   NOT NULL,
Address                  varchar(40)   NOT NULL,
City                     varchar(15)   NOT NULL,
State                    char(2)       NOT NULL,
Telephone_area_code      varchar(3)    NOT NULL,
Telephone_number         varchar(8)    NOT NULL,
Job_title                varchar(50)   NOT NULL,
Hire_date                smalldatetime NOT NULL,
Wage                     money         NOT NULL,
Gender                   char(1)       NOT NULL,
Race                     varchar(25)   NOT NULL,
Age                      int           NOT NULL,
)
CREATE TABLE Job_title                 --Spec. 2.1 Used to create the Job_title table
(
Job_title                varchar(50)   NOT NULL
  PRIMARY KEY,                         /*Spec 2.1 Job_title is the Primary because it  
                                          uniquely names a row in the table*/
EEO_1_Classification     varchar(30)   NOT NULL,
Job_description          varchar(250)  NOT NULL,
Exempt_Non_Exempt_Status bit           NOT NULL,
)
INSERT INTO Employee                   /*Spec 2.2 Here the INSERT statement is used to insert 
                                         data into the tables*/
VALUES('Edleman','Glenn','175 Bishops Lane','LaJolla','CA',
        '619','555-0199','Cashier','10/7/2003','$10.75','M',
        'Caucasian','64')
INSERT INTO Employee
VALUES('McMullen','Eric','763 Church St.','Lemon Grove', 'CA',
       '619','555-0133','Bagger','11/1/2002','$6.75','M','Caucasian',
       '20')
INSERT INTO Employee
VALUES('Slentz', 'Raj','123 Torrey Dr.','Clairmont','CA','619','555-0123',
       'Assistant Manager','6/1/2000','$48,000','M','Asian','34')
INSERT INTO Employee
VALUES('Broun','Erin','2045 Parkway Apt. 2B','Encinitas','CA','760',
       '555-0100','Bagger-30 hours/wk','3/12/2003','$6.75','F',
       'Caucasian','24')
INSERT INTO Employee
VALUES('Carpenter','Donald','927 Second St.','Encinitas','CA','619',
       '555-0154','Stocker','11/1/2003','$7.50','M','African American',
       '18')
INSERT INTO Employee
VALUES('Esquivez','David','10983 N. Coast Hwy Apt. 902','Encinitas',
       'CA','760','555-0108','Asst.-Butchers & Seafood Specialists',
       '7/25/2003','$9.25','M','Hispanic','25')
INSERT INTO Employee
VALUES('Sharp','Nancy','10793 Montecino Rd.','Ramona','CA',
       '858','555-0135','Cashier','7/12/2003','$10.50','F',
       'Caucasian','24')

INSERT INTO Job_title
VALUES('Accounting Clerk','Office/Clerical',
       'Accounting Clerk Computes, classifies,records, and
        verifies numerical data for use in maintaining 
        accounting records.','0')
INSERT INTO Job_title
VALUES('Assistant  Manager','Officials & Managers','Supervises and coordinates
        activities of workers in department of food store. Assists store manager
         in daily operations of store.','1')
INSERT INTO Job_title
VALUES('Bagger','Sales Workers','Places customer orders in bags. Performs carryout
        duties for customers.','0')
INSERT INTO Job_title
VALUES('Cashier','Sales Workers','Operates cash register to itemize and
        total customer’s purchases in grocery store.','0')
INSERT INTO Job_title
VALUES('Computer Support Specialist','Technician','Installs, modifies, 
        and makes minor repairs to personal computer hardware and
        software systems, and provides technical assistance and training to system
        users.','0')
INSERT INTO Job_title
VALUES('Dir. of Fin. & Acct.','Officials & Managers','Plans and directs the finance 
        and accounting activities for Kudler Fine Foods.','1')
INSERT INTO Job_title
VALUES('Asst.-Bakery & Pastry','Craft Workers (Skilled)','Obtains or prepares 
        food items requested by customers in retail food store.','0')
INSERT INTO Job_title
VALUES('Asst.-Butchers & Seafood Specialists','Operatives (Semi skilled)','Obtains 
        or prepares food items requested by customers in retail food store.','0')
INSERT INTO Job_title
VALUES('Stocker','Office/Clerical','Stores, prices and restocks merchandise
        displays in store.','0')
SELECT * FROM Employee                     /* Spec. 2.3 Using the select statement to view 
                                              the data from the specified tables.*/
SELECT * FROM Job_title