Chapter – 8 . STRUCTURED QUERY LANGUAGE

WORKERS

W_ID	FIRSTNAME	LASTNAME	ADDRESS	CITY
102	Sam	Tones	33 Elm St.	Paris
105	Sarah	Ackerman	44	U.S.
144	Manila	Sengupta	24 FriendsStreet	New Delhi
210	George	Smith	83 First Street	Howard
255	Mary	Jones	842 Vine	Losantiville
300	Robert	Samuel	9 Fifth Cross	Washington
335	Henry	Williams	12 Moore Street	Boston

DESIG
W_ID	SALARY	BENEFITS	DESIGINATION
102	75000	15000	Manager
105	85000	25000	Director
144	70000	15000	Manager
210	75000	12500	Manager
255	50000	12000	Clerk
300	45000	10000	Clerk
335	40000	10000	Clerk
400	32000	7500	Salesman
451	28000	7500	Salesman

(i) To display W_ID Firstname, address and City of all employees living in New York from the Table WORKERs

Ans. select W_ID ,firstname,address,city from workers where city=”New York”

(ii) To display the content of workers table in ascending order of LASTNAME.

Ans. Select * from Worker Order By lastname Asc

(iii) To display the FIRSTNAME, LASTNAME and Total Salary of all Clerks from the tables WORKERS And DESIG, where Total salary is calculated as Salary + benifts.

Ans. Select firstname, lastname, salary+benefits where worker.w_id=desg.w_id and Designation=”Clerk”

(iv) To display the minimum salary among managers and Clerks from the tables DESIG

Ans . (Try This Answer)

(v) SELECT FIRSTNAME, SALARY
FROM WORKERS, DESIG
WHERE DESIGINATION = “MANAGER” AND
WORKERS.W_ID = DESIGN.W_ID

Ans .

FIRSTNAME	SALARY
Sam	75000
Manila	70000
George	75000

(vi) SELECT COUNT(DISTINCT DESIGNATION) FROM DESIGN ;

Ans. 4