Assignment:
Using the example databases and tables below, 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 the 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 the 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 greater than to restrict record selection. (Use date of birth to restrict data.)
- Write a SQL query that joins two tables and restricts the data based on a single job classification.
- Write a SQL query that joins two tables and restricts data based on a specific job title and hire_date that is before a specific date.
- Write a SQL query that uses UNION of the two tables to return appropriate results.
- Write a SQL query that finds a job title record which has no associated employee.
- Write a SQL query that finds all the job classifications(no duplicates).
- Write a SQL query that finds all employees whose last name begins with the letters p through z.
Write the following queries using the SQL GROUP statement.
- Group employees by job classification: Select the employees' last names and group them by EEO-1 Classification.
- Group employees by salary: Select the employees' last names and group them by salary.
- Group employees by salary within their job classification: Select the employees' last names and group them by salary within their EEO-1 Classification.
Write the following queries.
- Find the number of employees within each job classification.
- Find the number of exempt employees within each job title.
SQL 2008 code:
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 VARCHAR (100) 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-JAN-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','03-FEB-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','25-MAR-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','19-APR-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-MAY-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','12-JUN-1985','038');
INSERT INTO Employee VALUES
('Sharp','Nancy','10793 Monteciono Rd','Ramona','CA','858','555-0135',
'Cashier','556332157','12-JUL-2003',21000.00,'F','29-JULY-1986','053');
Select * from Employee
Select * from job_title