0
See current output starting at page 

DROP TABLE EMPLOYEE;
DROP TABLE JOB_TITLE;

Create Table EMPLOYEE 
(
EMP_ID	        int	    NOT NULL,
LAST_NAME	varchar(20)	NOT NULL,
FIRST_NAME	varchar(20)	NOT NULL,
EMP_ADDRESS	varchar(25)	NOT NULL,
EMP_CITY    varchar(25) NOT NULL,
EMP_STATE   char(2)     NOT NULL,
AREA_CODE	    int	    NOT NULL,
PHONE_NUM	    int	    NOT NULL,
HIRE_DATE  datetime	    NOT NULL,
SALARY        money	    NOT NULL,
GENDER	       char(5)	NOT NULL,
AGE             int	    NOT NULL
JOB TITLE   varchar(50) NOT NULL
CONSTRAINT EMP_ID_PK PRIMARY KEY (EMP_ID)
);

Insert into Employee values(1,'Edelman'  ,'Glenn' ,'175 N.Bishops LN'   ,'La Jolla'       ,'CA','619','5550199','10/07/2003','21500','M','64','Cashier');
Insert into Employee values(2,'McMullen' ,'Eric'  ,'763 Church ST'      ,'Lemon Grove'    ,'CA','619','5550133','11/01/2002','13500','M','20','Cashier');
Insert into Employee values(3,'Slentz'   ,'Raj'   ,'123 Torrey Dr'      ,'North Clairmont','CA','619','5550123','06/01/2000','48000','M','34','Cashier');
Insert into Employee values(4,'Broun'    ,'Eric'  ,'2045 Park Way'      ,'Encinatas'      ,'CA','760','5550100','03/12/2003','10530','M','24','Cashier');
Insert into Employee values(5,'Carpenter','Donald','927 2ND ST'         ,'Encinatas'      ,'CA','619','5550154','11/01/2003','15000','M','18','Cashier');
Insert into Employee values(6,'Esquivez' ,'David' ,'10983 N. Coast Hwy.','Encinatas'      ,'CA','619','5550158','07/25/2003','18500','M','25','Cashier');
Insert into Employee values(7,'Sharp'    ,'Nancy' ,'10793 Montecito LN' ,'Ramona'         ,'CA','858','5550135','07/12/2003','21000','F','24','Cashier');

Create Table JOB_TITLE
(
Job_title	        varchar(50)	NOT NULL,
Job_description	    varchar(50)	NOT NULL,
Emp_exempt_status	   char (2)	NOT NULL,
EEO1_classification	varchar(25)	NOT NULL,
CONSTRAINT JOB_TITLE_PK PRIMARY KEY (JOB_TITLE)
);

Insert into JOB_TITLE values('Accounting Clerk'                    ,'maintain accounting records'          ,'NE','Office Clerical');
Insert into JOB_TITLE values('Assistant Manager'                   ,'assist store managers'                ,'E' ,'Officials and managers');
Insert into JOB_TITLE values('Bagger'                              ,'Bag Purchases-carryout when needed'   ,'NE','Sales Worker');
Insert into JOB_TITLE values('Cashier'                             ,'Cash handling-itemize customer orders','NE','Sales Worker');
Insert into JOB_TITLE values('Computer Support Specialist'         ,'maintain PC systems'                  ,'NE','Technician');
Insert into JOB_TITLE Values('Director of Finance and Accounting'  ,'Directs financial activities'         ,'E' ,'Officials & Managers');
Insert into JOB_TITLE values('Retail Assistant-bakery and Pastry'  ,'Handle Customer Requests'             ,'NE','Operatives(semi-skilled)');
Insert into JOB_TITLE Values('Retail Assistant-butcher and seafood','Handle Custoemr Requests'             ,'NE','Operatives(semi-skilled)');
Insert into JOB_TITLE Values('Stocker'                             ,'Store, price, restock Inventory'      ,'NE','Office-Clerical');

select * from EMPLOYEE;

select * from JOB_TITLE;

select *
  from EMPLOYEE,JOB_TITLE
 where EMPLOYEE.JOB_TITLE=JOB_TITLE.JOB_TITLE;
 
  --DROP TABLE EMPTEST;
CREATE TABLE EMPTEST(EMP_ID INT IDENTITY(1,1),NAME VARCHAR(10));

INSERT INTO EMPTEST VALUES('FRED'  );
INSERT INTO EMPTEST VALUES('WILMA' );
INSERT INTO EMPTEST VALUES('BARNEY');
INSERT INTO EMPTEST VALUES('BETTY' );
INSERT INTO EMPTEST VALUES('DINO'  );

SELECT * FROM EMPTEST;



/* WEEK THREE ASSIGNMENT  */
/* NUMBERS 1 - 4          */
/* SQL QUERIES            */

SELECT LAST_NAME, FIRST_NAME,EMP_City,EMP_State,JOB_DESCRIPTION, 
FROM EMPLOYEE,JOB_TITLE 
WHERE Salary BETWEEN 10000 and 20000;

SELECT LAST_NAME, FIRST_NAME, Hire_Date, Age,
FROM EMPLOYEE,JOB_TITLE
WHERE  Emp_exempt_status="E";

SELECT  LAST_NAME, FIRST_NAME,EMP_CITY, 
FROM EMPLOYEE,JOB_TITLE
WHERE AGE > 25, EMP_STATE=C;


SELECT  LAST_NAME, FIRST_NAME,AGE, JOB_DESCRIPTION
from EMPLOYEE,JOB_TITLE,
WHERE  EMP_CITY LIKE "E",GENDER; 



/* WEEK THREE ASSIGNMENT  */
/* NUMBERS 5-7            */
/*GROUP BY STATEMENTS     */

/* JOB DESCRIPTION        */

SELECT LAST_NAME, FIRST_NAME, COUNT (*)
FROM (EMPLOYEE,JOB_TITLE),
GROUP BY JOB_DESCRIPTION));


SELECT LAST_NAME, FIRST_NAME,
FROM (EMPLOYEE,JOB_TITLE)
GROUP BY STATE HAVING "C"));


SELECT LAST_NAME, FIRST_NAME,
FROM (EMPLOYEE,JOB_TITLE)
GROUP BY EMP_EXEMPT_STATUS));


OUTPUT

Msg 102, Level 15, State 1, Line 18
Incorrect syntax near 'JOB'.
Msg 156, Level 15, State 1, Line 75
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 79
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 83
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 89
Incorrect syntax near the keyword 'WHERE'.
Msg 102, Level 15, State 1, Line 100
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 105
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 110
Incorrect syntax near the keyword 'FROM'.
4
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by ChrisPadgham
2

There are so many errors with these, that I don't know how to correct all of it. There are 9 instances, I got tired after 7 I have pointed for some help. Looks like this is your homework. I wonder if it is even Ok to help you. anyway here it is.

1. In line 86, your where condition has comma instead of And or OR
for ex. where AGE > 25, EMP_STATE=C Check your SQL book.

2. In line 91, what are you trying? WHERE EMP_CITY LIKE "E", GENDER In line 106, there is code like this

SELECT LAST_NAME, FIRST_NAME, FROM (EMPLOYEE,JOB_TITLE)GROUP BY STATE HAVING "C"));  
SELECT LAST_NAME, FIRST_NAME, FROM (EMPLOYEE,JOB_TITLE)GROUP BY EMP_EXEMPT_STATUS));

3. One error I see is , (comma) just before from. Unless you copy pasted code wrongly. Remove the comma.

4. In from part of statement, remove the brackets. (starting ine 102)
for ex. ... from (a,b) is wrong. use ... from a,b 5. Your select and group by do not match. You cannot select columns that are not grouped on.

6. state having "C" needs to have a boolean condition for ex. having count(*) > 1 7. line 20. column name Job title cannot have blank.

All the best.

Edited by padtes: n/a

0

You have bigger problems than the syntax errors. I suggest you revisit the data model. Also your joins between the Employee and job_title tables need defining.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.