Help please-queries using =, between and Like

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help please-queries using =, between and Like

Author

Topic

Troy382438
Starting Member

1 Post

Posted - 2010-01-23 : 13:21:09

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 EMPLOYEE.EMP_ID,EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME,EMPLOYEE.EMP_City,EMPLOYEE.EMP_State,JOB_TITLE.JOB_DESCRIPTION,
FROM EMPLOYEE.JOB_TITLE=JOB_TITLE.JOB_TITLE,
WHERE JOB_TITLE.Salary BETWEEN 10000 and 20000;

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

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

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

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

/* JOB DESCRIPTION */

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

SELECT EMPLOYEE.EMP_ID, EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME,
FROM EMPLOYEE.JOB_TITLE=JOB_TITLE.JOB_TITLE
GROUP BY EMPLOYEE.STATE HAVING 'C';

SELECT EMPLOYEE_EMP.ID, EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME,
FROM EMPLOYEE.JOB_TITLE=JOB_TITLE.JOB_TITLE
GROUP BY EMPLOYEE.EMP_EXEMPT_STATUS;

Msg 102, Level 15, State 1, Line 18
Incorrect syntax near 'JOB_TITLE'.
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 102, Level 15, State 1, Line 88
Incorrect syntax near '='.
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'.

thank you. Help is most appreciated

Troy Reinhart

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 05:05:43

I trust you have written this attempting to do your Assignment. If the assignment is to fix the errors in this code then you need to show us what you have done. We won't do your homework for you (if we do that you won;t learn anything, will you?!) but we're happy to help if you tell us what you have done, explain where you are stuck, and ask specific questions for help.

Here are some pointers:

You have a series of syntax errors in your code:

Extra commas on the end of lines where there are no more elements to list.

FROM should be a table, and a join, and a condition:

so FROM EMPLOYEE.JOB_TITLE=JOB_TITLE.JOB_TITLE, (note the redundant comma on the end) should be


FROM EMPLOYEE
    JOIN JOB
        ON EMPLOYEE.JOB_TITLE=JOB_TITLE.JOB_TITLE

You can't have a comma here "WHERE EMPLOYEE.AGE > 25, EMPLOYEE.EMP_STATE=C;" you presumably meant to use either AND (or just possibly OR).

You have another "extra" comma here "WHERE EMPLOYEE.GENDER EMPLOYEE.EMP_CITY LIKE "E",; " and again you need AND or OR, you also need a logical expression for EMPLOYEE.GENDER - presumably to check specifically for Male or Female. LIKE needs a regular expression. EMPLOYEE.EMP_CITY LIKE "E" will only match Cities that are EQUAL to "E".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources