?
Solved

SQL Query

Posted on 2006-06-05
4
Medium Priority
?
1,120 Views
Last Modified: 2010-05-18
Dear experts.
I try to calculate the maximum salary for exempt employees and the maximum salary for non-exempt employees. Can you tell me what did I wrong??
I got the error that Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.

Here is my code

SELECT MAX(SALARY),
FROM EMPLOYEES,JOB_TITLE,
WHERE EXEMPT_STAUTS=NON_EXEMPT_STAUTS;




CREATE TABLE EMPLOYEES

(EMPLOYEES_NUM CHAR(2) PRIMARY KEY,

LAST_NAME CHAR(15),

FIRST_NAME CHAR(15),

ADDRESS CHAR(50),

CITY CHAR(15),

STATE CHAR(2),

TELEPHONE_AREA_CODE CHAR(3),

TELEPHONE_NUMBER CHAR(7),

EEO_1CLASSIFICATION CHAR(50),

HIRE_DATE CHAR(8),

SALARY DECIMAL(8,2),

GENDER CHAR(1),
RACE CHAR(30),

AGE CHAR(2));

The command(s)completed successfully.

Data insertion



INSERT INTO EMPLOYEES

VALUES

('01','EDELMAN','GLENN','175 BISHOPS LANE','LA
JOLLA','CA','619','5550199',
'SALES WORKERS','20061007','21500.00','F','CAUCASIAN','64');

INSERT INTO EMPLOYEES

VALUES
(
'02','MCMULLEN','ERIC','763 CHURCH ST','LEMON
GROVE','CA','619','5550133',
'SALES WORKERS','20021101','13500.00','M','CAUCASIAN','20');

INSERT INTO EMPLOYEES

VALUES
(
'03','SLENTZ','RAY','123 TORREY DR','NORTH CLAIRMONT','CA','619',
'5550123','OPERATIVES','19980715','23500.00','F','HISPANIC','28');

INSERT INTO EMPLOYEES

VALUES
(
'04','BROUN','ERIN','2045 PARKWAY APT213','ENCINITAS',
'CA','760','5550100','SALES
WORKERS','20030312','10530.00','F','CAUCASIAN','24');

INSERT INTO EMPLOYEES
VALUES
(
'05','CARPENTER','DONALD','927 SECOND
ST','ENCINITAS','CA','619','5550154',
'OFFICE/CLERICAL','20030218','15000.00','M','AFRICAN-AMERICAN','18');

INSERT INTO EMPLOYEES

VALUES
(
'06','ESQUIVEZ','DAVID','10983 N.COAST HWY
APT902','ENCINITAS','CA','760','5550108',
'OPERATIVES(SEMI-SKILLED)','20030725','18500','M','HISPANIC','25');
INSERT INTO EMPLOYEES
VALUES
(
'07','SHARP','NANCY','10793 MONTCANO RD','RAMONA','CA','858','5550135',
'SALES WORKERS','20030712','21000.00','F','CAUCASIAN','24');

INSERT INTO EMPLOYEES
VALUES
(
'08',
'MCNAMARA',
'JUANITA',
'923 PARKWAY',
'POWAY',
'CA',
'619',
'5550206',
'OFFICE/CLERIACAL',
'19991029',
'25500.00',
'F',
'HISPANIC',
'32');

INSERT INTO EMPLOYEES
VALUES
(
'09',
'NGUYEN',
'MEREDITY',
'10583 ARENAS ST',
'LA JOLLA',
'CA',
'619',
'5550102',
'TECHNIICAN',
'19980927',
'43000.00',
'M',
'CAUCASIAN',
'25');

INSERT INTO EMPLOYEES
VALUES
(
'10',
'STEPHENS',
'HARVERY',
'7863 HIGH BLUFF DRIVE',
'LA JOLLA',
'CA',
'619',
'5550123',
'OFFICE&MANAGERS',
'19980301',
'75000.00',
'M',
'CAUCASIAN',
'51');

INSERT INTO EMPLOYEES
VALUES
(
'11',
'VU',
'MATTHEW',
'981 JOVEY PINES ROAD',
'LA JOLLA',
'CA',
'619',
'5550138',
'TECHNICIAN',
'20000816',
'37000.00',
'M',
'ASIAN',
'26');

INSERT INTO EMPLOYEES
VALUES
(
'12',
'AVERY',
'LEDONNA',
'198 HAVERNA DR',
'DELMAR',
'CA',
'619',
'5550135',
'CRAFT WORKERS(SKILLED)',
'20030328',
'21000.00',
'F',
'AFRICAN-AMERICAN',
'23');

INSERT INTO EMPLOYEES
VALUES
(
'13',
'DROHOS',
'CRAIG',
'X',
'SOLANO BEACH',
'CA',
'619',
'5550202',
'OFFICALS & MANAGERS',
'20000615',
'51000.00',
'M',
'CAUCASIAN',
'28');


INSERT INTO EMPLOYEES
VALUES
(
'14',
'MEIER',
'ELAINE',
'9703 DRAVIE LANE',
'DEMAR',
'CA',
'858',
'5550112',
'SALES WORKERS',
'20000910',
'20500.00',
'F',
'ASIAN',
'51');

INSERT INTO EMPLOYEES
VALUES
(
'15',
'QUILLIAN',
'STANLEY',
'98542 WANDERING ROAD APT2-B',
'DELMAR',
'CA',
'760',
'5550198',
'OPERATIVES(SEMI-SKILLED)',
'19991216',
'23000.00',
'M',
'AMERIAN-INDIAN',
'29');

INSERT INTO EMPLOYEES
VALUES
(
'16',
'TYINK',
'THOMAS',
'87592 VACIFIC HEIGHTS BLVD',
'DELMAR',
'CA',
'858',
'5550159',

'CRAFT WORKERS(SKILLED)',
'20010501',
'19000.00',
'M',
'AFRICAN-AMERICAN',
'32');

INSERT INTO EMPLOYEES
VALUES
(
'17',
'VANCE',
'BRENT',
'927 CYUTHIA LANE',
'POWAY',
'CA',
'858',
'5550147',
'SALES WORKERS',
'20010329',
'10530.00',
'M',
'CAUCASIAN',
'22');

0
Comment
Question by:drmopco
  • 2
  • 2
4 Comments
 

Author Comment

by:drmopco
ID: 16838960
Can anyone help???
I got the maximum salary for exempt employees but I don't know how to get the salary for non_exempt employees.

Here is my update one.


SELECT MAX(SALARY),
FROM EMPLOYEES,JOB_TITLE,
WHERE EXEMPT_NON_EXEMPT_=EXEMPT_NON_EXEMPT_;
0
 
LVL 2

Accepted Solution

by:
bdsmithback earned 2000 total points
ID: 16838973
Hi drmopco,

Here's a few things.

First we'll look at the query:

SELECT MAX(SALARY),
FROM EMPLOYEES,JOB_TITLE,
WHERE EXEMPT_STAUTS=NON_EXEMPT_STAUTS;

You want to remove the commas before from and where to make the query

SELECT MAX(SALARY)
FROM EMPLOYEES,JOB_TITLE
WHERE EXEMPT_STAUTS=NON_EXEMPT_STAUTS;

But - this will still not get you the desired results.  I'm guessing that the JOB_TITLE query is where you store the field EXEMPT_STATUS.  But, there is nothing that links employees to job_title in your query.  It should look something like select max(salary) from employees join job_title on employees.job_titleid = job_title.job_titleid where exempt_status='non_exempt_status';  By joining the tables, you link the employees to the jobs they have and then take the maximum salary given a certain exempt_status.  Also, you'll probably have to add the quotes to 'NON_EXEMPT_STATUS' because I'm guessing that EXEMPT_STATUS is some sort of text or varchar field.  

I hope this helps!
0
 

Author Comment

by:drmopco
ID: 16839058
THANKS. IT HELPS A LOT!!!!
0
 
LVL 2

Expert Comment

by:bdsmithback
ID: 16839062
Thanks - glad it helped.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

840 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