Solved

SQL Syntax help

Posted on 2008-10-23
14
428 Views
Last Modified: 2012-05-05
Hi Everyone-

I am trying to create a query for the following Spec:

Select the employees last names and group them by salary within job titles that are grouped into
exempt and non exempt.  The nesting is creating some confusion for my.    I could use some help in trying to get the syntax in order.

This is for a class project, I have created the other queries for the class and am stuck on this one.   I am unsure how to nest the code so it works correctly.

Currently I am getting this error.

olumn 'Job_title.Title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Looking for Syntax help for a Query, the following

SELECT Last_name, Salary From Employee
GROUP BY Last_name, Salary
Union
Select Title From Job_title
Group By Exempt_non_exempt_status


Thanks in advance.


USE [master]
GO


CREATE DATABASE NewDataBase
ON
(NAME = 'NewDataBase',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewDataBase.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
(NAME = 'NewDataBaseLog',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewDataBase.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )

GO


Use NewDataBase
CREATE TABLE Job_title
 
(
Title                              int PRIMARY KEY CLUSTERED IDENTITY (1,1),
Job_title                              varchar(128)            Not Null,
EE0_1_classification            varchar(128)            Not Null,
Job_Description                        varchar(512)            Not Null,
Exempt_non_exempt_status      char(40)                  Not Null,
)
 
use NewDataBase
CREATE TABLE Employee
(      Emp_id                              int                              NOT NULL      PRIMARY KEY IDENTITY,
      Last_name                        varchar(40)                  NOT NULL,
      First_name                        varchar(40)                  NOT NULL,
      Address                              varchar(40)                  NOT NULL,
      City                              varchar(20)                  NOT NULL,
      State                              char(2)                        NOT NULL,      
      Telephone_area_code            char(3)                        Not Null,
      Telephone_number            char(7)                        Not Null,
      EE0_1_classification      varchar(128)            Not Null,
      Hire_date                        smalldatetime            Not Null,
      Salary                              money                        Not Null,
      Gender                              char(1)                        Not Null,
      Age                                    char(3)                        Not NulL,
      Title      int NULL REFERENCES [dbo].[Job_Title](Title) NOT FOR REPLICATION
)

INSERT INTO Job_Title
(Job_Title, EE0_1_Classification, Job_Description, Exempt_Non_Exempt_Status)
VALUES
('Accounting Clerk', 'Office/Clerical','Computes, classifies, records, and verifies numerical data for use in maintaining
accounting records.','NON_EXEMPT')

INSERT INTO Job_Title
(Job_Title, EE0_1_Classification, Job_Description, Exempt_Non_Exempt_Status)
VALUES
('Asst. Manager', 'Officials & Managers','Supervises and coordinates activities of workers in department of food store.
Assists store manager in daily operations of store.','EXEMPT')

INSERT INTO Job_Title
(Job_Title, EE0_1_Classification, Job_Description, Exempt_Non_Exempt_Status)
VALUES
('Bagger', 'Sales Workers','Places customer orders in bags. Performs carryout duties for customers.
Essential Duties','NON_EXEMPT')

INSERT INTO Job_Title
(Job_Title, EE0_1_Classification, Job_Description, Exempt_Non_Exempt_Status)
VALUES
('Cashier', 'Sales Workers','Operates cash register to itemize and total customers purchases in grocery
store.','NON_EXEMPT')

INSERT INTO Job_Title
(Job_Title, EE0_1_Classification, Job_Description, Exempt_Non_Exempt_Status)
VALUES
('Computer Support Specialist', 'Technician','Installs, modifies, and makes minor repairs to personal computer hardware and
software systems, and provides technical assistance and training to system
users.','NON_EXEMPT')

INSERT INTO Job_Title
(Job_Title, EE0_1_Classification, Job_Description, Exempt_Non_Exempt_Status)
VALUES
('Director of Finance And Accounting', 'Officials & Managers','Plans and directs the finance and accounting activities for Kudler Fine Foods.
Essential Duties','EXEMPT')

INSERT INTO Job_Title
(Job_Title, EE0_1_Classification, Job_Description, Exempt_Non_Exempt_Status)
VALUES
('Retail Asst. Bakery And Pastry', 'Craft Workers (Skilled)','Obtains or prepares food items requested by customers in retail food stores','NON_EXEMPT')

INSERT INTO Job_Title
(Job_Title, EE0_1_Classification, Job_Description, Exempt_Non_Exempt_Status)
VALUES
('Retail Asst. Butchers and Seafood Specialists', 'Operatives (Semi skilled)','Obtains or prepares food items requested by customers in retail food stores','NON_EXEMPT')

INSERT INTO Job_Title
(Job_Title, EE0_1_Classification, Job_Description, Exempt_Non_Exempt_Status)
VALUES
('Stocker', 'Office/Clerical','Stores, prices and restocks merchandise displays in store.
Essential Duties','NON_EXEMPT')

INSERT INTO Employee
(Last_name,First_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Edelman', 'Glen','175 Bishop Lane','La Jolla','CA','619','5550199','Sales Workers','10/7/2003','21500','M','64')


INSERT INTO Employee
(Last_name,First_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('McMullen', 'Eric','763 Church Street','LybbGrove','CA','619','5550133','Sales Workers','11/1/2002','13500','M','20')

INSERT INTO Employee
(Last_name,First_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Slentz', 'Raj','5123 Tommy Drive','North Clarimon','CA','619','5550123','Officials & Managers','6/1/2000','48000','M','34')

INSERT INTO Employee
(Last_name,First_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Broun', 'Erin','2045 Parkway Apt 2B','Encinitas','CA','766','5550100','Sales Workers','3/12/2003','10530','F','24')

INSERT INTO Employee
(Last_name,First_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Carpenter', 'Donald','927 Second Street','Encinitas','CA','619','5550154','Office/Clerical','10/15/2003','15000','M','18')

INSERT INTO Employee
(Last_name,First_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Esquivez','David', '10983 N. Coast Hwy Apt 902','Encinitas','CA','858','5550180','Operatives (Semi skilled)','7/1/2001','18500','M','25')


INSERT INTO Employee
(Last_name,First_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Sharp', 'Nancy','10793 Montecino RD','Romona','CA','858','5550135','Cashier','6/1/2003','21000','F','24')


INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('laurie', 'priest', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Officials & Managers','6/1/1998','67,000.00','F','34')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Craig ', 'Drohos', 'Salamo Beach', 'Salamo Beach',  'CA','619', '5550202', 'Officials & Managers','6/15/2000','51,000.00','M','32')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Thompson ', 'Erin ', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Officials & Managers','5/1/1999','47367.00','F','28')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Vasquez', 'Ernestina', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Dept. Mgr. - Produce & Foodstuffs','7/22/2000','38,000.00','F','27')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Cantu ', 'Maria', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Officials & Managers','3/2/2002','37,000.00','F','45')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Fouay', 'Chumakov', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Officials & Managersr','8/1/2002','40,000.00','F','26')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Randy', 'Depree', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Assistant Manager','12/7/1999','51,000.00','M','40')


INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Connie', 'Garcia', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','10/7/2001','51,000.00','M','36')


INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Gregory',' Freeman', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','8/30/2002','19,500.00','M','31')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Cyndi',' Grewell', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','5/14/2003','21,000.00','F','32')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Clay',' Lomax', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Operatives (Semi skilled)','11/27/2001','26,000.00','M','28')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Fran',' Lopez-Palma', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Operatives (Semi skilled)','3/28/2002','20,500.00','F','27')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Aaron',' Nitkowski', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Operatives (Semi skilled)','1/20/2002','21,500.00','M','32')


INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Patrick',' Rayder', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Operatives (Semi skilled)','3/11/2002','22,500.00','M','28')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Stan',' Quillian', '98542 Wandering Pond Apt 20b ', 'Del Mar',  'CA','760', '5550198', 'Operatives (Semi skilled)','12/16/1999','23,000.00','M','29')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Martha',' Quintero', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Operatives (Semi skilled)','6/16/2003','19,500.00','F','26')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Perry',' VanSlooten', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Craft Workers (Skilled)','12/25/2002','19,500.00','M','24')


INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Larissa',' Urtado', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Craft Workers (Skilled)','2/5/2003','19,000.00','F','35')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Thomas',' Tyink', '87592 Pacivic Hieghts BLVD', 'Del Mar',  'CA','858', '5550159', 'Craft Workers (Skilled)','5/1/2001','19,500.00','M','32')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Ledonna',' Avery', '198 Governer Drive', 'Del Mar',  'CA','619', '5550135', 'Craft Workers (Skilled)','3/28/2003','21,000.00','F','23')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('David',' Ball', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Craft Workers (Skilled)','8/22/2000','20,500.00','M','26')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Chris',' Bareman', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','12/25/2002','19,000.00','M','24')



INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('George',' Bakker', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','2/10/2003','18,000.00','M','19')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Israel',' Bruss', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','5/8/2001','19,000.00','M','22')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Mark ','Grayson', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','4/16/2003','18,000.00','M','21')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Phanthousit ','Soukhone', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','11/29/2002','20,500.00','F','42')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Angie ','Merz', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','11/29/2002','18,000.00','F','38')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Margarita ','Lopez', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','8/26/2000','20,000.00','F','52')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Elaine ','Meier', '9703 Dranid Lane ', 'DelMar',  'CA','None Given', 'None Given', 'Sales Workers','12/27/2001','20,500.00','M','51')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Derea ','Spencer', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','4/11/2003','19,500.00','M','44')


INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Linzee ','Sobota', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','6/9/2002','14,000.00','M','44')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Ken ','Soper', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','4/16/2003','14,500.00','M','44')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Gregg ','Sobkowiak', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Sales Workers','12/17/2001','10,920.00','M','44')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Brent ','Vance', '927 Cynthia Lane Parkway', 'Del Mar',  'CA','858', '5551456', 'Sales Workers','3/29/2001','10,530.00','M','44')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Art ','Miller', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Office/Clerical','9/29/2003','14,500.00','M','44')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Luis ','Munoz', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Office/Clerical','3/31/2001','17,500.00','M','44')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Eden ','Chapman', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Office/Clerical','9/13/2001','17,500.00','M','44')

INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Jose ','Chavez', 'None Given', 'None Given',  'CA','None Given', 'None Given', 'Office/Clerical','11/19/2003','16,500.00','M','44')


INSERT INTO Employee
(First_name,Last_name,Address,City,State,Telephone_area_code,Telephone_number,EE0_1_Classification,Hire_date,Salary,Gender,Age)
VALUES
('Glen', 'Edelman','175 Bishop Lane','La Jolla','CA','619','5550199','Sales Workers','10/7/2003','21500','M','64')





select * from employee

select * from Job_title


0
Comment
Question by:salberta
  • 7
  • 6
14 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22786693
Hello salberta,

Thank you for being candid about this being a school project.  As this academic, I am going to limit this to
general observations:

1) Any column in the SELECT clause of a query with an ORDER BY clause must either be in an aggregate
function (e.g., COUNT, SUM, AVG etc) or also included in the GROUP BY clause

2) When you combine queries using the UNION operator, they should have the same number of columns
in the result set

Regards,

Patrick
0
 

Author Comment

by:salberta
ID: 22787247
Thanks-
So If you can help break down the reqs I would appreciate it..
Select the employees last names and group them by salary -- Am I ok here?
SELECT Last_name, Salary From Employee
GROUP BY Last_name, Salary
within job titles  --- are they asking for a Union here?
grouped into exempt and non exempt
Group By Exempt_non_exempt_status
 
Is there a source I can use that would ouline a similar req for my study.  I am not sure how to group SQL statements correctly.
Thanks.
 
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 22787500
Hi salberta,

I suggest you start by considering your own question "are they asking for a Union here?"  You may also find it helpful to write out a few sample rows of the result set you're after - it can help you to see where you're going.
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 22787525
In fact, forget about the grouping for the moment - just see if you can create a query that outputs all the relevant fields for each employee.
0
 

Author Comment

by:salberta
ID: 22787574
Thanks Calpurnia-
I have this much figued out....

SELECT salary, Last_name From Employee
GROUP BY salary, Last_name
0
 

Author Comment

by:salberta
ID: 22788291
Hi Guys-
Maybe I should word the question this way
Could someone please rewrite explicitly, the question to allow for myself to disect the requirements correclty.
Select the employees last names and group them by salary within job titles that are grouped into
exempt and non exempt.  The nesting is creating some confusion for my.    I could use some help in trying to get the syntax in order.
 
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 22788738
salberta, as I said above, forget about the grouping for the moment, just think about how you would write to a query to output the following details for each employee:

Last_name, salary, Job_title, Exempt_non_exempt_status
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:salberta
ID: 22788781
Thanks calpurnia
without grouping
SELECT salary, Last_name From Employee
SELECT Exempt_non_exempt_status, Job_title From Job_title
with grouping
SELECT salary, Last_name From Employee
GROUP BY salary, Last_name
ORDER BY salary;
SELECT Exempt_non_exempt_status, Job_title From Job_title
GROUP BY Exempt_non_exempt_status, Job_title
ORDER BY Job_title
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 22789086
"SELECT salary, Last_name From Employee
SELECT Exempt_non_exempt_status, Job_title From Job_title"

That's 2 queries! You need to create a SINGLE query that will output all 4 fields. And to give you a little pointer, you DON'T want to use a UNION query...
0
 
LVL 10

Accepted Solution

by:
calpurnia earned 125 total points
ID: 22791081
I'd also like to ask why does the field EE0_1_classification appear in both your tables?
0
 

Author Comment

by:salberta
ID: 22792670
Hi Calpurnia-
 
I will ask the instructor to clarify the colums for referencing.  this is what I fianlly came up with.  The query retruns values and is what I needed, the Exempt_non_exempt_status is the only common field and was really throwing me on why I was not returing data.   I finally used this colum and I am getting data.

 I would rather be using the Northwind data base like the book uses for reference......But after a couple of hours of study I got it.
SELECT e.salary, e.Last_name , j.Job_Title, j.Exempt_non_exempt_status
FROM Employee e
INNER JOIN Job_Title j
ON e.EEO_1_Classification = j.EEO_1_Classification
GROUP BY  j.Exempt_non_exempt_status, e.salary, e.Last_name, j.Job_Title
 
Thank YOU
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 22795379
salberta, you're getting closer, but think about the effect of joining the tables on EEO_1_Classification. Several records in both tables have the same values in this field, so it's not going to get you the right results.

To demonstrate the problem, try running the following query and tell me what output you get:

SELECT e.salary, e.Last_name , j.Job_Title, j.Exempt_non_exempt_status
FROM Employee e
INNER JOIN Job_Title j
ON e.EEO_1_Classification = j.EEO_1_Classification
WHERE e.Last_name='Cantu';
0
 

Author Comment

by:salberta
ID: 22797118
Hi Calpurnia-
The result set returns empty.  
 
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 22797471
Hmmm, it should have given you:

Salary                          Last_name                         Job_Title                                                         Exempt_non_exempt_status
37,000.00                   Cantu                                    Asst. Manager                                                EXEMPT
37,000.00                   Cantu                                    Director of Finance And Accounting           EXEMPT          

What do you get if you run the following?

SELECT e.salary, e.Last_name , j.Job_Title, j.Exempt_non_exempt_status
FROM Employee e
INNER JOIN Job_Title j
ON e.EEO_1_Classification = j.EEO_1_Classification;              
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now