CREATE TABLE JOB_TITLE
(
eeo_1_classification VARCHAR (20) NOT NULL,
Job_title_id VARCHAR (5) PRIMARY KEY,
job_title VARCHAR (50) NOT NULL,	
job_description  VARCHAR (100) NOT NULL,
exempt VARCHAR (4) NOT NULL		
)

CREATE TABLE Employee
(
id_num INT IDENTITY (1, 1) PRIMARY KEY,
last_name VARCHAR (15) NOT NULL,
first_name VARCHAR(15) NOT NULL,        
address VARCHAR (50) NOT NULL,	
city VARCHAR (20) NOT NULL,
state  VARCHAR (2) NOT NULL,
telephone_area_code INT NOT NULL,
telephone_number VARCHAR (10) NOT  NULL,
eeo_1_classification VARCHAR (20) NOT NULL,
ssn VARCHAR (20) NOT NULL,
hire_date DATETIME	NOT NULL,
salary DECIMAL (10,2),    
gender CHAR(1) NOT NULL,
date_of_birth smalldatetime NOT NULL,
job_title_id VARCHAR (5) 
CONSTRAINT FK_Employee_Job_title 
FOREIGN KEY REFERENCES Job_title (Job_title_id)
)

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

INSERT INTO JOB_TITLE  VALUES
('Officials Managers','062', 
'Asst Manager', 
'Supervises and 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 customer purchases', 
'No');

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

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

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

INSERT INTO JOB_TITLE VALUES
('Operatives','038', 
'Retail Assistant 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','175 Bishops Lane','La_Jolla','CA','619','555-0199',
'Sales Workers','123456789','07-OCT-2003',21500.75,'M','01/01/1946','084');

INSERT INTO Employee VALUES
('McMullen','Eric','763 Church St','Lemon Grove','CA','619','555-0135',
'Sales Worker','234567890','1-NOV-2002',13500.00,'M','02/03/1990','084');

INSERT INTO Employee VALUES
('Slentz','Raj','123 Torrey Dr.','North Clairmont','CA','619','555-0123',
'Officials & Managers','157863498','1-JUN-2000',48000.00,'M','03/25/1976','016');  

INSERT INTO Employee VALUES
('Broun','Erin','2045 Parkway Apt.2B','Encinitas','CA','760','555-0100',
'Sales Workers','654983473','12-MAR-2003',10530.00,'F','04/19/1986','053');

INSERT INTO Employee VALUES
('Carpenter','Donald','927 Second ST.', 'Encinitas','CA','619','555-0154',
'Office/Clerical','468656425','1-NOV-2003', 15000.00,'M','05/05/1992','071');

INSERT INTO Employee VALUES
('Esquivez','David','10983 N. Coast Hwy Apt 902','Encinitas','CA','760','555-0108',
'Operatives','560348976','25-JUL-2003',18500.00,'M','06/12/1985','038'); 

INSERT INTO Employee VALUES
('Sharp','Nancy','10793 Monteciono Rd','Ramona','CA','858','555-0135',
'Cashier','556332157','12-JUL-2003',21000.00,'F','07/29/1986','053');   

Select Last_name,First_name,Salary,Job_title 
from employee JOIN Job_title 
ON Employee.Job_title_Id=Job_title.Job_title_id
WHERE Salary BETWEEN 20000 AND 45000

Select Last_name,First_name,Hire_date,Salary 
from employee JOIN Job_title 
ON Employee.Job_title_Id=Job_title.Job_title_id
WHERE Hire_date BETWEEN '2003-01-01' AND '2003-12-31'

Select Last_name,First_name,Telephone_area_code,Job_title 
from employee JOIN Job_title 
ON Employee.Job_title_Id=Job_title.Job_title_id
WHERE Telephone_area_code like '6%' ORDER BY Last_name asc

Select Last_name,First_name,Date_of_birth,Job_title 
from employee JOIN Job_title 
ON Employee.Job_title_Id=Job_title.Job_title_id
WHERE Date_of_birth > '1980-01-01' ORDER BY Date_of_birth desc

Select Last_name,First_name,eeo_1_classification=JOB_TITLE
from employee JOIN Job_title 
ON Employee.Job_title_Id=Job_title.Job_title_id
WHERE Job_title.eeo_1_classification = 'Clerk'

Select Last_name,First_name,Job_title,Hire_date 
from employee JOIN Job_title ON Employee.Job_title_Id=Job_title.Job_title_id
WHERE Job_title = 'Cashier' AND Hire_date < '2003-07-31'

select Job_title_id 
from job_title minus 
select job_title_id 
from employee;

select distinct eeo_1_classification 
from job_title;

select * 
from employee 
where last_name like '[P-Z]%'

select eeo_1_classification,
count(last_name) 
from employee 
group by eeo_1_classification;

Select Last_name, eeo_1_classification
from employee JOIN Job_title 
ON Employee.Job_title_Id=Job_title.Job_title_id
GROUP BY eeo_1_classification,Last_name

Select Last_name,Salary,Job_title,exempt
from employee JOIN Job_title 
ON Employee.Job_title_Id=Job_title.Job_title
GROUP BY exempt,Job_title,Salary,Last_name

Select eeo_1_classification, count(*)
from employee JOIN Job_title 
ON Employee.Job_title_Id=Job_title.Job_title_id
GROUP BY eeo_1_classification

Select Job_title, count(*)
from employee JOIN Job_title 
ON Employee.Job_title_Id=Job_title.Job_title
where exempt = 'EXEMPT'
GROUP BY Job_title


Select * from Employee
Select * from job_title