/* Employee table creation */

DROP TABLE Employee 

CREATE TABLE Employee
(
Emp_id						INT IDENTITY (1, 1) NOT NULL	PRIMARY KEY,	/* Chosen as Primary Key because it is Auto Generated and is unique to a single employee */
Last_name					VARCHAR (25)		NOT NULL,
First_name					VARCHAR (25)		NOT NULL,        
Address						VARCHAR (40)		NOT NULL,	
City						VARCHAR (15)		NOT NULL,
State						VARCHAR	(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,
)


/* Job Title table creation */

DROP TABLE Job_title

CREATE TABLE Job_title
(
Job_title					VARCHAR (50)		NOT NULL	PRIMARY KEY,	/* Chosen as Primay Key because it assumes the main reason for the entire table to be in existence */
eeo_1_classification		VARCHAR (50)		NOT NULL,
Job_description				VARCHAR (250)		NOT NULL,
Exempt_Non_Exempt_Status	BIT					NOT NULL,
)



/* Begin insertion of Employee Table data */

INSERT INTO Employee  
VALUES
('Edelman', 'Glenn', '175 Bishops Lane', 'La_Jolla', 'CA', '619', '555-0199', 'Sales Workers', '2003-10-07', 21500.00, 'M', 'Caucasion', 64); 

INSERT INTO Employee  
VALUES
('McMullen', 'Eric', '763 Church St', 'Lemon Grove', 'CA' ,'619' ,'555-0135' ,'Sales Workers', '2002-11-01', 13500.00, 'M', 'Caucasion', 20);

INSERT INTO Employee  
VALUES
('Slentz', 'Raj', '123 Torrey Dr.', 'North Clairmont', 'CA','619', '555-0123', 'Officials & Managers', '2000-06-01', 48000.00, 'M', 'Asian', 34);  

INSERT INTO Employee 
VALUES
('Broun', 'Erin', '2045 Parkway Apt.2B', 'Encinitas', 'CA', '760', '555-0100', 'Sales Workers', '2003-03-12', 10530.00, 'F', 'Caucasion', 24);

INSERT INTO Employee 
VALUES
('Carpenter', 'Donald','927 Second ST.', 'Encinitas', 'CA', '619', '555-0154', 'Office Clerical', '2003-11-01', 15000.00, 'M', 'African American', 18);

INSERT INTO Employee 
VALUES
('Esquivez', 'David', '10983 N. Coast Hwy Apt 902', 'Encinitas', 'CA', '760', '555-0108', 'Operatives', '2003-07-25', 18500.00, 'M', 'Hispanic', 25); 

INSERT INTO Employee 
VALUES
('Sharp', 'Nancy', '10793 Monteciono Rd', 'Ramona', 'CA', '858', '555-0135', 'Sales Workers', '2003-07-12', 21000.00, 'F', 'Caucasian', 24);  


/* Begin insertion of data into the Job_title Table */

INSERT INTO Job_title
VALUES
('Accounting Clerk', 'Office Clerical', 'Maintains computes records', 1);

INSERT INTO Job_title  
VALUES
('Asst Manager', 'Officials Managers', 'Supervises coordinates workers', 1);

INSERT INTO Job_title 
VALUES
('Bagger', 'Sales Worker', 'Places customer items in bags',  0);

INSERT INTO Job_title 
VALUES
('Cashier', 'Sales Worker', 'Itemize and total customer’s purchases', 0);

INSERT INTO Job_title 
VALUES
('Computer Support Specialist', 'Technician', 'Updates software maintain hardware provides training technical assistance', 1);

INSERT INTO Job_title 
VALUES
('Director of Finance and Accounting', 'Officials  Managers', 'Plans and directs the finance and accounting activities',1);

INSERT INTO Job_title 
VALUES
('Retail Asst. Bakery & Pastry','Craft Workers', 'Monitors workers',0); 

INSERT INTO Job_title 
VALUES
('Retail Asst. Butchers and Seafood Specialist', 'Operatives', 'Monitors workers', 0);  

INSERT INTO Job_title  
VALUES
('Stocker', 'Office Clerical', 'Stores, Prices and restocks merchandise displays in store', 0);