Discuss the below:
Create a SQL queries to accomplish following.
1. Choose a Job Description and increase by 10% all employees' salaries that have the selected Job Description (not Job Title).
2. Decrease all Exempt employees' salaries from a certain State by 15%.
3. Increase all Non-Exempt employees' salaries by 5%, if employee was hired more than 3 years ago and is less than 30 years of Age.
4. Delete Exempt employees earning a Salary greater than a certain amount.
5. Calculate the maximum salary of all Exempt employees.
6. Calculate the minimum, maximum, and average salary for each Job_Description.
7. Insert 3 additional Job_Title records that no Employee holds.
Write a Select query that displays ALL Job_Descriptions and the first and last name of anyone holding that description. The query MUST show all Job_descriptions, including the 3 additional just added.
-- Create Table Job_title-------------------------
CREATE TABLE Job_title (
Job_Title_ID char(4)PRIMARY KEY,
Job_title varchar(50) NOT NULL,
Job_description varchar(50) NOT NULL,
EEO1_classification varchar(50) NOT NULL,
FLSA_Status varchar(20) NOT NULL,
);
---Insert data into table for Weeek 2 ---
INSERT INTO Job_title VALUES
(11, 'Accounting Clerk', 'Computes, classifies, records, and verifies numeri', 'Office/Clerical', 'Non-Exempt'),
(12, 'Assistant Manager', 'Supervises and coordinates activities of workers i', 'Officials & Managers', 'Exempt'),
(13, 'Bagger', 'Places customer orders in bags. Performs carryout ', 'Sales Workers_B', 'Non-Exempt'),
(14, 'Cashier', 'Operates cash register to itemize and total custom', 'Sales Workers_A', 'Non-Exempt'),
(15, 'Computer Support Specialist', 'Installs, modifies, and makes minor repairs to per', 'Technician', 'Non-Exempt'),
(16, 'Director of finance and accounting', 'Plans and directs the finance and accounting activ', 'Officials & Managers', 'Exempt'),
(17, 'Retail assistant bakery and pastry', 'Obtains or prepares food items requested by custom', 'Operatives (Semi skilled)_A', 'Non-Exempt'),
(18, 'Retail assistant butchers and seafood specialists', 'Obtains or prepares food items requested by custom', 'Operatives (Semi skilled)_B', 'Non-Exempt'),
(19, 'Stocker', 'Stores, prices and restocks merchandise displays i', 'Office/Clerical', 'Non-Exempt');
---Employee Table build because FK Job Title created first -------------
create Table Employee (
Employee_ID INT IDENTITY (200, 1) PRIMARY KEY,
Job_Title_ID CHAR (4) CONSTRAINT FK_Employee_Job_Title_ID FOREIGN KEY REFERENCES Job_title (Job_Title_ID),
EEO1_classification varchar(50),
Last_name varchar (15) NOT NULL,
First_name varchar(15) NOT NULL,
Address varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State varchar(5) NOT NULL,
area_code varchar(8) NOT NULL,
Telephone varchar(17) NOT NULL,
Hire_date date NOT NULL,
Salary SMALLMONEY Not Null,
Gender varchar(3) NOT NULL,
Age varchar(2) NOT NULL,
);
-- Employee data Insert-----
INSERT INTO Employee (Job_Title_ID, EEO1_classification, Last_name, First_name, Address, City, State,area_code, Telephone, Hire_date, Salary, Gender, Age) VALUES
('14', 'Sales Workers_A', 'Glenn', 'Edelman', '175 Bishop Lane, La ', 'La Jolla', 'CA', '619', '555-0199', '2010-08-24', '21500', 'M', '64'),
('14', 'Sales Workers_A', 'McMulen', 'Erick', '763 Church St. CA', '', 'CA', '619', '555-0135', '10-10-2002', '13500', 'M', '20'),
('13', 'Sales Workers_B', 'Erin', 'Broun', '2045 Park Way, Apt 2', 'Encinitas', 'CA', '760', '555-0100', '03-01-2003', '10530', 'F', '34'),
('12', 'Officials & Managers', 'Slentz', 'Raj', '123 Torrey Dr, North', 'North Clairmont', 'CA', '619', '555-0123', '5-15-2000', '48000', 'M', '34'),
('18', 'Operatives (Semi skilled)_B', 'David', 'Esquivez', '10983 N.Coast Hwy Ap', 'Encinitas', 'CA', '760', '550-1108', '07-01-2003', '18500', 'M', '25'),
('19', 'Office/Clerical', 'Carpenter', 'Donald', '927 Second St, Encin', 'Encinitas', 'CA', '619', '555-0154', '10-15-2003', '15000', 'M', '18'),
('14', 'Sales Workers_A', 'Sharp', 'Nancy', '10793 Montecino Rd, ', 'Ramona', 'CA', '858', '555-0135', '06-01-2003', '21000', 'F', '24');
('11', 'Office/Clerical_A ','McNamara', 'Juanita', '923','Parkway', 'CA', '619','555-0206', '1999-10-29', '25000', 'F', '32'),
('16', 'Officials & Managers','Stephens', 'Harvey', '7863 High Bluff Drive','Parkway', 'CA', '619', '555-0123', '1998-03-01', '75000', 'M', '51'),
('15', 'Technician', 'Nguyen', 'Meredith', '10583 Arenas St.', 'LA Jolla', 'CA', '619', '555-0102', '1998-09-27','43000', 'M', '25'),
('17', 'Operatives (Semi skilled)_A', 'Avery', 'Ledonna', '198 Governor Dr.', 'Del Mar', 'CA', '619', '555-0135', '2003-03-28', '21000', 'F', '23'),
('18', 'Operatives (Semi skilled)_B ', 'Quillian', 'Stanley', '98542 Wandering Rd.', 'Del Mar', 'CA', '760', '555-0198', '1999-12-16', '23000','M ', '29'),
('16', 'Officials & Managers','Drohos', 'Craig', 'unknown research', 'Selano Beach', 'CA', '619', '555-0202', '2000-06-15', '51000', 'M', '32'),
('17', 'Operatives (Semi skilled)_A','Tyink', 'Thomas', '87592 Pacific Heights Blvd.', 'DelMar', 'CA', '858', '555-0159', '2001-05-01', '19000', 'M', '32'),
('14', 'Sales Workers_A','Meier', 'Elaine', '9703 Orchid Lane', 'Del Mar', 'CA', '858', '555-0112', '2000-09-09','20500','F', '51'),
('13', 'Sales Workers_B','Vance', 'Brent', '927 Cynthia Lane', 'Parway', 'CA', '858', '555-0147', '2001-03-29', '10530', 'M', '22'),
('15', 'Technician', 'Vu', 'Matthew' , '10583 Arenas St.', 'LA Jolla', 'CA', '619', '555-0112', '2000-08-16', '37000', 'M', '26');