Assignment:
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 customers 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.