SQL Server Performance

Group By statement using multiple tables

Discussion in 'SQL Server 2005 General Developer Questions' started by michaelu30, Mar 24, 2009.

  1. michaelu30 New Member

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

Share This Page