Assignment:
Use the database and tables to write SQL queries using Between, Like, and Union:
- Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. Use salary to restrict data.
- Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. Use hire dates to restrict data.
- Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. Use telephone area codes to restrict data.
- Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. Use age to restrict data.
- Write a SQL query that uses the UNION of the two tables to produce a third table.
Use the updated database to write the following queries, using the SQL GROUP statement: MAKE SURE TO USE THE GROUP METHOD
- Select employees' last names and group them by EEO-1 classification.
- Select employees' last names and group them by salary.
- Select employees' last names and group them by salary in their EEO-1 classification.
- Select employees' last names and group them by salary in job titles, grouped into exempt and non-exempt.
Note. Select all data from both tables before you perform the following:
1. Increase all employees' salaries with the selected EEO-1 classification by 10%.
2. Increase all employees' salaries by 5%.
3. Choose an employee from the Employee table and delete that employee.
Use the database and tables from last week's assignment to write SQL statements that do the following:
1. Calculate the average salary for all employees.
2. Calculate the maximum salaries for exempt and non-exempt employees.
3. Calculate the maximum salary for all employees.
4. Calculate the minimum salaries for exempt and non-exempt employees.
5. Calculate the minimum salary for all employees.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employee]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Job_title]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Job_title]
GO
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
--Created a table labeled job title
CREATE TABLE employee
(
Employee_ID 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,
Hire_date DATETIME NOT NULL,
Salary DECIMAL (10,2),
Gender CHAR(1) NOT NULL,
Age INT NOT NULL,
Job_Title_ID VARCHAR (5) CONSTRAINT FK_Employee_Job_title FOREIGN KEY REFERENCES Job_title (Job_title_id)
)
GO
--created a table labeled employee
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 customer?s 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
(
'Avery','Ledonna','198 Governon Dr.','Del Mar','CA','619','555-0135','Craft Workers',
'28-MAR-2003',21000.00,'F',23,'027');
INSERT INTO EMPLOYEE VALUES
(
'Drohos','Craig','','Selano Beach','CA','619','555-0202','Officials & Managers',
'15-JUN-2000',51000.00,'M',32,'062');
INSERT INTO EMPLOYEE VALUES
('Meier','Elaine','Orchid Lane','Del Mar','CA','858','555-0112','Sales Workers',
'10-SEP-2000',20500.00,'F',51,'084');
INSERT INTO EMPLOYEE VALUES
('Quillian','Stanley','98542 Wandering Road Apt 2-B','Del Mar','CA','760','555-0198','Operatives',
'16-DEC-1999',23000.00,'M',29,'038');
INSERT INTO EMPLOYEE VALUES
('Tyink','Thomas','87592 Pacific Heights Blvd.', 'Del Mar','CA','858','555-0159','Craft Workers',
'1-MAY-2001', 19000.00,'M',32,'027');
INSERT INTO EMPLOYEE VALUES
('Vance','Brent','927 Cynthia Lane','Loway','CA','858','555-0147','Sales Workers',
'29-MAR-2001',10530.00,'M',22,'053');
INSERT INTO EMPLOYEE VALUES
('McNamara','Juanita','923 Parkway','Loway','CA','619','555-0200','Office Clerical',
'29-OCT-1999',25500.00,'F',32,'071');
INSERT INTO EMPLOYEE VALUES
('Nguyen','Meredith','10583 Arenas St.','La Jolla','CA','619','555-0102','Technician',
'27-SEP-1998',43000.00,'M',25,'095');
INSERT INTO EMPLOYEE VALUES
('Harvey','Stephens','7863 High Bluff Dr.','La Jolla','CA','619','555-0123','Officials & Managers',
'01-MAR-1998',75000.00,'M',51,'016');
INSERT INTO EMPLOYEE VALUES
('Vu','Matthew','981 Torrey Pines Rd.','La Jolla','CA','619','555-0138','Technician',
'16-AUG-2000',37000.00,'M',26,'095');
Select * from employee
Select * from job_title
--This statement selects values from both tables(Job Title and Employee)
select * from employee join job_title on employee.job_title_id = job_title.job_title_id where salary between 15000 and 25000
select * from employee join job_title on employee.job_title_id = job_title.job_title_id where hire_date between '2003/01/31' and '2003/12/31'
select * from employee join job_title on employee.job_title_id = job_title.job_title_id where telephone_area_code like '6%'
select * from employee join job_title on employee.job_title_id = job_title.job_title_id where age like '2%'
--This statement selects vaules from the Job Title ID based on salary range, hire date and age.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[New_table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[New_table]
GO
select * into New_table from
(select eeo_1_classification,job_title_id from employee
union
select eeo_1_classification,job_title_id from job_title) A
--This statement selects values eeo classfication from employee union and job title
select Last_name from employee where eeo_1_classification = 'sales workers'
select Last_name from employee where salary > 20000
select Last_name from employee where salary > 20000 and eeo_1_classification = 'sales workers'
select Last_name from employee join job_title on employee.job_title_id = job_title.job_title_id where salary > 20000 and exempt='yes'
--This statement selects values last name based on eeo classfication, salary and salary exempt.