March 24, 2009 at 7:01 am
Drop table Employees;
Drop table Job;
Select the employees’ last names and group them by salary within job titles that are grouped into exempt and non-exempt.
My attempt at this is:
SELECT e.Last_name,e.Salary,e.Job_Title,j.Exemption_Status
FROM Employees e,Job j
INNER JOIN (SELECT Salary
FROM Employees
GROUP BY Job_Title,Salary) e
ON e.Job_Title=j.Job_Title;
I know that since the data has to be pulled from 2 tables that a JOIN must be used and that since Job_Title is found in both tables as the primary key that an alias has to exist to distinguish the 2 fields.....I am drawing a blank on how to pull this specific query though. Any help is greatly appreciated!!! I included the tables and data insertions. I don't expect someone to do it for me....just put me on the right track!! This is my first SQL class and I am a little "overwhelmed".
CREATE TABLE Job (
EEO_Classification VARCHAR(60) NOT NULL,
Job_Title VARCHAR(50) NOT NULL PRIMARY KEY,
Job_description VARCHAR(1000) NULL,
Exemption_Status VARCHAR(20) NOT NULL );
CREATE TABLE Employees (
Emp_ID CHAR(6) NOT NULL PRIMARY KEY,
Last_name VARCHAR(20) NOT NULL,
First_name VARCHAR(20) NULL,
Address VARCHAR(40) NULL,
City VARCHAR(32) NULL,
State CHAR(2) NULL,
Telephone_area_code VARCHAR(3) NULL,
Telephone_number VARCHAR(16) NULL,
EE01_Classification VARCHAR(60) NULL,
Hire_date DATETIME NULL,
Salary MONEY NULL,
Gender NCHAR(1) NOT NULL,
Age VARCHAR(3) NULL,
Job_Title VARCHAR(50) NOT NULL references Job(Job_Title));
INSERT INTO Job (EEO_Classification, Job_Title, Job_description, Exemption_Status)
SELECT 'Office/Clerical','Accounting Clerk','Computes, classifies, records, and
verifies numerical data for use in maintaining accounting records','Non-exempt'
UNION ALL
SELECT 'Officials & Managers','Asst. Manager','Supervises and coordinates activities
of workers in department of food store. Assists store manager in daily operations of
store','Exempt'
UNION ALL
SELECT 'Sales Workers','Bagger','Places customer orders in bags. Performs carryout
duties for customers','Non-exempt'
UNION ALL
SELECT 'Sales Workers','Cashier','Operates cash register to itemize and total
customers purchases in grocery store.','Non-exempt'
UNION ALL
SELECT 'Technician','Computer Support Specialist','Installs, modifies, and makes minor
repairs to personal computer hardware and software systems and provides technical
assistance and training to system users','Non-exempt'
UNION ALL
SELECT 'Officials & Managers','Director of Finance & Accounting','Plans and directs the
finance and accounting activities for Kudler Fine Foods.','Exempt'
UNION ALL
SELECT 'Craft Workers(skilled)','Retail Asst. Bakery & Pastry','Obtains or prepares
food items requested by customers in retail food store.','Non-exempt'
UNION ALL
SELECT 'Operatives(semi-skilled)','Retail Asst. Butchers and Seafood Specialists','Obtains
or prepares food items requested by customers in retail food store.','Non-exempt'
UNION ALL
SELECT 'Office/Clerical','Stocker','Stores, prices, and restocks merchandise displays
in store','Non-exempt'
UNION ALL
SELECT 'Operatives(skilled)','Butcher','Cuts meat','Non-exempt';
INSERT INTO Employees (Emp_ID, Last_name, First_name, Address,
City, State, Telephone_area_code, Telephone_number, EE01_Classification, Hire_date,
Salary, Gender, Age, Job_Title)
SELECT '800','Edelman','Glenn','175 Bishops Lane','La Jolla','CA',619,'555-0199',
'Sales Workers','10-07-2003',21500,'M',64,'Cashier'
UNION ALL
SELECT '801','McMullen','Eric','763 Church Street','Lemongrove','CA',619,'555-0133',
'Sales Workers','11-01-2002',13500,'M',20,'Bagger'
UNION ALL
SELECT '802','Slentz','Raj','123 Torrey Drive','North Clairmont','CA',619,'555-1023',
'Officials & Managers','06-01-2000',48000,'M',34,'Asst. Manager'
UNION ALL
SELECT '803','Broun','Erin','2045 Parkway Apt2B','Encinitas','CA',760,'555-0100',
'Sales Workers','03-12-2003',10530,'F',24,'Bagger'
UNION ALL
SELECT '804','Carpenter','Donald','927 Second St','Encinitas','CA',619,'555-0154',
'Office/Clerical','11-01-2003',15000,'M',18,'Stocker'
UNION ALL
SELECT '805','Esquivez','David','10983 North Coast Hwy Apt 902','Encinitas','CA',760,
'555-0108','Operatives(semi skilled)','07-25-2003',18500,'M',25,'Butcher'
UNION ALL
SELECT '806','Sharp','Nancy','10793 Montecino Road','Ramona','CA',858,'555-0135',
'Sales Workers','07-12-2003',21000,'F',24,'Cashier'
UNION ALL
SELECT '807','McNamara','Juanita','923 Parkway Highway',NULL,'CA',619,'555-0206',
'Office/Clerical','10-29-1999',25500,'F',32,'Accounting Clerk'
UNION ALL
SELECT '808','Nguyen','Meredith','10583 Arenas Street','La Jolla','CA',619,'555-0102',
'Technician','09-27-1998',43000,'F',25,'Computer Support Specialist'
UNION ALL
SELECT '809','Stephens','Harvey','7863 High Bluff Drive','La Jolla','CA',619,'555-0123',
'Officials & Managers','03-01-1998',75000,'M',51,'Director of Finance & Accounting'
UNION ALL
SELECT '810','Vu','Matthew','981 Torrey Pines Road','La Jolla','CA',619,'555-0138',
'Technician','08-16-2000',37000,'M',26,'Computer Support Specialist'
UNION ALL
SELECT '811','Avery','Ledonna','198 Governor Drive','Del Mar','CA',619,'555-0135',
'Craft Workers(skilled)','03-28-2003',21000,'F',23,'Retail Asst. Bakery & Pastry'
UNION ALL
SELECT '812','Drohos','Craig',NULL,'Selano Beach','CA',619,'555-0202',
'Officials & Managers','06-15-2000',51000,'M',32,'Asst. Manager'
UNION ALL
SELECT '813','Meier','Elaine','9703 Dronid Lane','Del Mar','CA',858,'555-0112',
'Sales Workers','09-10-2000',20500,'F',51,'Cashier'
UNION ALL
SELECT '814','Quillian','Stanley','98542 Wandering Road Apt2B','Del Mar','CA',760,'555-0198',
'Operatives(semi skilled)','12-16-1999',23000,'M',29,'Retail Asst. Butchers and Seafood Specialists'
UNION ALL
SELECT '815','Tyink','Thomas','87592 Pacific Heights Blvd.','Del Mar','CA',858,'555-0159',
'Craft Workers(skilled)','05-01-2001',19000,'M',32,'Retail Asst. Bakery & Pastry'
UNION ALL
SELECT '816','Vance','Brent','927 Cynthia Lane','Poway','CA',858,'555-0157',
'Sales Workers','03-29-2001',10530,'M',22,'Bagger';
March 24, 2009 at 5:42 pm
Well, I would say you should look at your table alias's. You are using e as an alias twice. Also, the only thing you are returning in your derived table e is salary; it doesn't seem to be doing anything. Also, and I am self taught here, so I'm not to keen on some terminology, but you are using commas to separate the first two objects in your from clause, which means if you don't specify a where clause, you will be doing a cross join. Then, for the third object, you are using INNER JOIN. I'm not sure if you can use both methods in the same query with proper syntax, but for confusions' sake, I would probably use all comma's, or all JOINS.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply