I need to do the following with my code that is attached.
4.4 how many people are salaried and how many are regular
4.5 Calculate the minimum salary for all salaried employees
4.6 Calculate the minimum wage rate for all regular employees
4.7 Calculate the average wage for each eeo-1 classifications
4.8 Calculate the maximum salary for exempt employees and the maximum salary for non-exempt employees
4.9 Calculate the minimum salary for exempt employees and the maximum salary for non-exempt employees
CREATE TABLE Employee
(Emp_id int primary key identity(1,1) NOT NULL,
Last_name varchar(25),
First_name varchar(25),
Address varchar(40),
City varchar(15),
State char(2),
Telephone_area_code varchar(3),
Telephone_number varchar(8),
Job_title varchar(50),
Hire_date DATE,
Wage money,
Gender char(1) ,
Race varchar(25),
Age int );
CREATE TABLE Job_title
(Job_title varchar(50) primary key NOT NULL,
EEO_1_Classification varchar(30),
Job_description varchar(250),
Exempt_Non_Exempt_Status bit);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Edelman’, ‘Glenn’, ’175 Bishops Lane’, ‘La Jolla’, ‘CA’, ’629′, ’555-0199′, ‘Cashier’, ’10-07-2003′, 10.75, ‘M’, ‘Caucasian’, 64);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘McMullen’, ‘Eric’, ’763 Church St’, ‘Lemon Grove’, ‘CA’, ’619′, ’555-0135′, ‘Bagger’, ’11-01-2002′, 6.75, ‘M’, ‘Caucasian’, 20);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Slentz’, ‘Raj’, ’123 Torrey Dr’, ‘North Clairmont’, ‘CA’, ’619′, ’555-0123′, ‘Assistant Manager’, ’06-01-2000′, 48000, ‘M’, ‘Asian’, 34);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Broun’, ‘Erin’, ’2045 Parkway Apt 2B’, ‘Encinitas’, ‘CA’, ’760′, ’555-0100′, ‘Bagger – 30 hours/wk’, ’03-12-2003′, 6.75, ‘F’, ‘Caucasian’, 24);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Carpenter’, ‘Donald’, ’927 Second St’, ‘Encinitas’, ‘CA’, ’619′, ’555-0154′, ‘Stocker’, ’11-01-2003′, 7.50, ‘M’, ‘African American’, 18);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Esquivez’, ‘David’, ’10983 N Coast Hwy Apt 902′, ‘Encinitas’, ‘CA’, ’760′, ’555-0108′, ‘Asst. – Butchers & Seafood Specialists’, ’07-25-2003′, 9.25, ‘M’, ‘Hispanic’, 25);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Sharp’, ‘Nancy’, ’10793 Montecino Rd’, ‘Ramona’, ‘CA’, ’858′, ’555-0135′, ‘Cashier’, ’07-12-2003′, 10.50, ‘F’, ‘Caucasian’, 24);
INSERT INTO Job_title (Job_title, EEO_1_Classification, Job_description, Exempt_Non_Exempt_Status)
VALUES (‘Accounting Clerk’, ‘Office Clerical’, ‘COmputes, classifies, records, and verifies numerical data for use in maintaining accounting records’, 0);
INSERT INTO Job_title (Job_title, EEO_1_Classification, Job_description, Exempt_Non_Exempt_Status)
VALUES (‘Assistant Manager’, ‘Officials & Managers’, ‘Supervises and coordinates activities of workers in department of food store. Assists store managerin daily operations of store’, 1);
INSERT INTO Job_title (Job_title, EEO_1_Classification, Job_description, Exempt_Non_Exempt_Status)
VALUES (‘Bagger’, ‘Sales Workers’, ‘Places customers orders in bags. Performs carryout duties for customers’, 0);
INSERT INTO Job_title (Job_title, EEO_1_Classification, Job_description, Exempt_Non_Exempt_Status)
VALUES (‘Cashier’, ‘Sales Workers’, ‘Operates cash register to itemize and total customers purchases in grocery store’, 0);
INSERT INTO Job_title (Job_title, EEO_1_Classification, Job_description, Exempt_Non_Exempt_Status)
VALUES (‘Computer Support Specialist’, ‘Technician’, ‘Installs, modifies, and makes minor repairs to personal computer hardware and software systems, and provides technical assistance and training to system users’, 0);
INSERT INTO Job_title (Job_title, EEO_1_Classification, Job_description, Exempt_Non_Exempt_Status)
VALUES (‘Director of Finance & Accounting’, ‘Officials & Managers’, ‘Plans and directs the finance and accounting activities for Kudler Fine Foods’, 1);
INSERT INTO Job_title (Job_title, EEO_1_Classification, Job_description, Exempt_Non_Exempt_Status)
VALUES (‘Retail Assistant’, ‘Craft Workers’, ‘Obtains or prepares food items requested by customers in retail food store’, 0);
INSERT INTO Job_title (Job_title, EEO_1_Classification, Job_description, Exempt_Non_Exempt_Status)
VALUES (‘Stocker’, ‘Office/Clerical’, ‘Stores, prices and restocks merchandise displays in store’, 0);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Avery’, ‘Ledonna’, ’198 Governor Dr’, ‘Del Mar’, ‘CA’, ’619′, ’555-0135′, ‘Asst Bakery & Pastry’, ’3-28-2003′, 10.50, ‘F’, ‘African American’, 23);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Drohos’, ‘Craig’, ‘Null’, ‘Selena Beach’, ‘CA’, ’619′, ’555-0202′, ‘Assistant Manager’, ’6-15-2000′, 51000, ‘M’, ‘Caucasian’, 32);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Meier’, ‘Elaine’, ’9703 Orchid Lane’, ‘Del Mar’, ‘CA’, ’858′, ’555-0112′, ‘Cashier’, ’9-10-2000′, 10.25, ‘F’, ‘Asian’, 51);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Quillian’, ‘Stanley’, ’98542 Wandering Road Apt 2-B’, ‘Del Mar’, ‘CA’, ’760′, ’555-0198′, ‘Asst Butchers & Seafood Specialist’, ’12-6-1999′, 11.50, ‘M’, ‘American Indian’, 29);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Tyink’, ‘Thomas’, ’87592 Pacific Heights Blvd’, ‘Del Mar’, ‘CA’, ’858′, ’555-0159′, ‘Asst Bakery & Pastry’, ’5-1-2001′, 9.50, ‘F’, ‘Asian’, 35);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Vance’, ‘Brent’, ’927 Cynthia Lane’, ‘Spontney’, ‘CA’, ’858′, ’555-0147′, ‘Bagger’, ’3-29-2001′, 6.75, ‘M’, ‘Caucasian’, 22);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘McNamara’, ‘Juanita’, ’923 Parkway’, ‘Roway’, ‘CA’, ’619′, ’555-0500′, ‘Accounting Clerk’, ’10-29-1999′, 12.75, ‘F’, ‘Hispanic’, 32);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Nguyen’, ‘Meredith’, ’10583 Arenas St’, ‘La Jolla’, ‘CA’, ’619′, ’555-0102′, ‘Computer Support Specialist’, ’9-27-1998′, 21.50, ‘M’, ‘Caucasian’, 25);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Stephens’, ‘Harvey’, ’7863 High Bluff Drive’, ‘La Jolla’, ‘CA’, ’619′, ’555-0123′, ‘Dir of Finance and Acct’, ’3-9-1999′, 75000, ‘M’, ‘Caucasian’, 51);
INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date, Wage, Gender, Race, Age)
VALUES (‘Vu’, ‘Matthew’, ’981 Torrey Pines Road’, ‘La Jolla’, ‘CA’, ’619′, ’555-0138′, ‘Computer Support Specialists’, ’8-16-2000′, 18.50, ‘M’, ‘Asian’, 26);
– 3.2 verifies data in tables
SELECT * FROM Employee
SELECT * FROM Job_title
–3.3 joins two tables and restricts data by wage
SELECT *
FROM Employee
JOIN Job_title
ON Employee.Job_title=Job_title.Job_title
WHERE Wage
BETWEEN 9.00 and 13.00
–3.4 Joins two tables and uses hire_date to restrict data
SELECT *
FROM Employee
JOIN Job_title
ON Employee.Job_title=Job_title.Job_title
WHERE Hire_date
BETWEEN ’1-1-1999′ AND ’12-31-2009′
–3.5 Sorts data by telephone area code
SELECT *
FROM Employee
JOIN Job_Title
ON Employee.Job_Title = Job_Title.Job_Title
WHERE Telephone_Area_Code
LIKE ’619′