USE KUDLER
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 * from employee job_title where job_title_id=Job_title_id and salary Between 15000 and 20000;
select * from employee job_title where job_title_id=Job_title_id and hire_date Between '2000-01-01' and '2003-01-01';
select * from employee job_title where job_title_id=Job_title_id and telephone_number like  '555-013%';
select * from employee job_title where job_title_id=Job_title_id and date_of_birth > '1990/01/01';
select * from employee job_title where job_title_id=Job_title_id and job_title_id = 'Director of Finance Accounting' and hire_date < '2001/01/01';
select eeo_1_classification,job_title_id from employee union select eeo_1_classification,Job_title_id from job_title;
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, salary from employee group by salary;
select salary,eeo_1_classification,last_name from employee group by salary,eeo_1_classification;
select eeo_1_classification,count(*) from employee group by eeo_1_classification; 
select job_title from job_title where exempt = 'Yes'; 

Select * from Employee
Select * from job_title

select last_name, salary from employee group by salary;

select salary,eeo_1_classification,last_name from employee group by salary,eeo_1_classification;

select last_name, salary from employee group by salary, last_name;
select salary,eeo_1_classification,last_name from employee group by salary,eeo_1_classification, last_name;

select last_name, salary from employee ORDER BY salary, last_name;
select salary,eeo_1_classification,last_name from employee ORDER BY salary,eeo_1_classification, last_name;