Assignment:
Using the database and tables , 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 LIKE to restrict record selection. (Use age to find all people in their 20's.)
Write a SQL query that uses UNION of the two tables. Be creative.
Using the updated database, write the following queries using the SQL GROUP BY statement and appropriate aggregate statements (when needed):
Write a SQL query that displays the average salary / hourly wage grouped by job_title.
Write a SQL query that displays the number of people in each EE0-1 Classification and the average salary / hourly wage for each classification.
Group employees by EEO classification: Select the employees' last names and group them by EEO-1 Classification
Group employees by salary within their EEO classification: Select the employees' last names and group them by salary within their EEO-1 Classification.
Select the employees' last names and group them by salary within job titles that are grouped into exempt and non-exempt/* Employee table creation */
DROP TABLE Employee
CREATE TABLE Employee
(
Emp_id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY, /* Chosen as Primary Key because it is Auto Generated and is unique to a single employee */
Last_name VARCHAR (25) NOT NULL,
First_name VARCHAR (25) NOT NULL,
Address VARCHAR (40) NOT NULL,
City VARCHAR (15) NOT NULL,
State VARCHAR (2) NOT NULL,
Telephone_area_code VARCHAR (3) NOT NULL,
Telephone_number VARCHAR (8) NOT NULL,
Job_title VARCHAR (50) NOT NULL,
Hire_date SMALLDATETIME NOT NULL,
Wage MONEY NOT NULL,
Gender CHAR(1) NOT NULL,
Race VARCHAR (25) NOT NULL,
Age INT NOT NULL,
)
/* Job Title table creation */
DROP TABLE Job_title
CREATE TABLE Job_title
(
Job_title VARCHAR (50) NOT NULL PRIMARY KEY, /* Chosen as Primay Key because it assumes the main reason for the entire table to be in existence */
eeo_1_classification VARCHAR (50) NOT NULL,
Job_description VARCHAR (250) NOT NULL,
Exempt_Non_Exempt_Status BIT NOT NULL,
)
/* Begin insertion of Employee Table data */
INSERT INTO Employee
VALUES
('Edelman', 'Glenn', '175 Bishops Lane', 'La_Jolla', 'CA', '619', '555-0199', 'Sales Workers', '2003-10-07', 21500.00, 'M', 'Caucasion', 64);
INSERT INTO Employee
VALUES
('McMullen', 'Eric', '763 Church St', 'Lemon Grove', 'CA' ,'619' ,'555-0135' ,'Sales Workers', '2002-11-01', 13500.00, 'M', 'Caucasion', 20);
INSERT INTO Employee
VALUES
('Slentz', 'Raj', '123 Torrey Dr.', 'North Clairmont', 'CA','619', '555-0123', 'Officials & Managers', '2000-06-01', 48000.00, 'M', 'Asian', 34);
INSERT INTO Employee
VALUES
('Broun', 'Erin', '2045 Parkway Apt.2B', 'Encinitas', 'CA', '760', '555-0100', 'Sales Workers', '2003-03-12', 10530.00, 'F', 'Caucasion', 24);
INSERT INTO Employee
VALUES
('Carpenter', 'Donald','927 Second ST.', 'Encinitas', 'CA', '619', '555-0154', 'Office Clerical', '2003-11-01', 15000.00, 'M', 'African American', 18);
INSERT INTO Employee
VALUES
('Esquivez', 'David', '10983 N. Coast Hwy Apt 902', 'Encinitas', 'CA', '760', '555-0108', 'Operatives', '2003-07-25', 18500.00, 'M', 'Hispanic', 25);
INSERT INTO Employee
VALUES
('Sharp', 'Nancy', '10793 Monteciono Rd', 'Ramona', 'CA', '858', '555-0135', 'Sales Workers', '2003-07-12', 21000.00, 'F', 'Caucasian', 24);
/* Begin insertion of data into the Job_title Table */
INSERT INTO Job_title
VALUES
('Accounting Clerk', 'Office Clerical', 'Maintains computes records', 1);
INSERT INTO Job_title
VALUES
('Asst Manager', 'Officials Managers', 'Supervises coordinates workers', 1);
INSERT INTO Job_title
VALUES
('Bagger', 'Sales Worker', 'Places customer items in bags', 0);
INSERT INTO Job_title
VALUES
('Cashier', 'Sales Worker', 'Itemize and total customers purchases', 0);
INSERT INTO Job_title
VALUES
('Computer Support Specialist', 'Technician', 'Updates software maintain hardware provides training technical assistance', 1);
INSERT INTO Job_title
VALUES
('Director of Finance and Accounting', 'Officials Managers', 'Plans and directs the finance and accounting activities',1);
INSERT INTO Job_title
VALUES
('Retail Asst. Bakery & Pastry','Craft Workers', 'Monitors workers',0);
INSERT INTO Job_title
VALUES
('Retail Asst. Butchers and Seafood Specialist', 'Operatives', 'Monitors workers', 0);
INSERT INTO Job_title
VALUES
('Stocker', 'Office Clerical', 'Stores, Prices and restocks merchandise displays in store', 0);