CREATE TABLE JOB_TITLE
(
eeo_1_classification VARCHAR (20) NOT NULL,
job_title_id VARCHAR (5)  PRIMARY KEY,
job_title VARCHAR (50) NOT NULL,	
j_description  VARCHAR (100) NOT NULL,
exempt VARCHAR (4) NOT NULL		
 )
GO

CREATE TABLE employee
(
id_num INT IDENTITY (1, 1) PRIMARY KEY,
l_name VARCHAR (15)      NOT NULL,
f_name VARCHAR(15)     NOT NULL,
SocialSec	NCHAR(9)	NOT NULL,   
address VARCHAR (50)	 NOT NULL,	
city VARCHAR (20)           NOT NULL,
state  VARCHAR  (2)		NOT NULL,
tele_area_code INT    NOT NULL,
tele_number VARCHAR (10)	NOT  NULL,
eeo_1_classification	VARCHAR (20) NOT NULL,
hire_date DATETIME	NOT NULL,
salary DECIMAL  (10,2),    
gender 	CHAR(1)	NOT NULL,
race CHAR(16)  NOT NULL,
Birthdate   smalldatetime   NOT NULL,
job_title_id VARCHAR (5) CONSTRAINT FK_Employee_Job_title FOREIGN KEY REFERENCES Job_title (Job_title_id)
)
GO


INSERT INTO JOB_TITLE VALUES
('Office Clerical', '071', 'Accounting Clerk', 'maintains computes records','No');

INSERT INTO JOB_TITLE VALUES
('Officials Managers', '062', 'Asst Manager', 'supervises coordinates workers', 'Yes');

INSERT INTO JOB_TITLE VALUES
('Sales Worker', '053', 'Bagger', 'places customer items in bags',  'No');

INSERT INTO JOB_TITLE VALUES
('Sales Workers', '084', 'Cashier', 'itemize and total customer’s purchases', 'No');

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

INSERT INTO JOB_TITLE VALUES
('Officials  Managers', '016','Director of Finance  Accounting', 'plans and directs the finance and accounting activities','Yes');

INSERT INTO JOB_TITLE VALUES
('Craft Workers', '027', 'Retail Asst. Bakery & Pastry','monitors workers','No'); 

INSERT INTO JOB_TITLE VALUES
('Operatives', '038', 'Retail Asst. Butchers and Seafood Specialist', 'monitors workers', 'No');  

INSERT INTO JOB_TITLE  VALUES
('Stocker', '049', 'Office clerical','Stores, prices and restocks merchandise displays in store', 'No');


INSERT INTO EMPLOYEE  VALUES
(    
'Edelman','Glenn','455345234','175 Bishops Lane','La_Jolla','CA','619','555-0199','Sales Workers',
'07-OCT-2003',21500.75,'M','Caucasian','1944/03/14','084');

INSERT INTO EMPLOYEE  VALUES
(
'McMullen','Eric','269986237','763 Church St','Lemon Grove','CA','619','555-0135','Sales Workers',
 '1-NOV-2002',13500.00,'M','Caucasian','1987/12/12','084');

INSERT INTO EMPLOYEE  VALUES
('Slentz','Raj','589086237','123 Torrey Dr.','North Clairmont','CA','619','555-0123','Officials & Managers',
'1-JUN-2000',48000.00,'M','Asian','1975/06/15','016');  

INSERT INTO EMPLOYEE VALUES
('Broun','Erin','513336237','2045 Parkway Apt.2B','Encinitas','CA','760','555-0100','Sales Workers',
'12-MAR-2003',10530.00,'F','Caucasian','1983/07/22','053');

INSERT INTO EMPLOYEE VALUES
('Carpenter','Donald','526786237','927 Second ST.', 'Encinitas','CA','619','555-0154','Office Clerical',
'1-NOV-2003', 15000.00,'M','African American','1988/11/14','071');

INSERT INTO EMPLOYEE VALUES
('Esquivez','David','510971937','10983 N. Coast Hwy Apt 902','Encinitas','CA','760','555-0108','Operatives',
'25-JUL-2003',18500.00,'M','Hispanic','1987/01/11','038'); 

INSERT INTO EMPLOYEE VALUES
('Sharp','Nancy','526886237','10793 Monteciono Rd','Ramona','CA','858','555-0135','Sales Workers',
'12-JUL-2003',21000.00,'F','Caucasian','1986/05/28','053');  


INSERT INTO EMPLOYEE VALUES
('Ramirez','Marco','526692437','10793 2045 Ward Parkway, Lot#27', 'La Jolla', 'CA', '735', '555-0606','Sales Workers',
'12-JUL-2001',21550.00,'F','Hispanic','1976/05/28','053');  



Using the database, tables, and data, write SQL statements to:
•	Calculate the average salary for employees for each state. 
•	Calculate the maximum salary for exempt employees and the maximum salary for non-exempt employees. 
•	Calculate the maximum salary for all employees whose hire_date is between two dates. 
•	Calculate the maximum salary for employees within each job class
•	Calculate the minimum salary for exempt employees and the maximum salary for non-exempt employees. 
•	Calculate the total salary for employees separated by gender within each job class 
•	Determine the oldest and youngest employees and provide the age for each category.
•	Determine what percent of the largest salary is used for the total payroll in the company.