Group By statement using multiple tables

  • 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';

  • 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