Chapter – 8 . STRUCTURED QUERY LANGUAGE

DELHI 2005:

(5) Consider the following tables EMPLOYEES and EMPSALARY. write SQL commands for the Statements (i) to (iv) and give outputs for SQL quires (v) to (viii).

EMPLOYEES

EMPID	FIRSTNAME	LASTNAME	ADDRESS	CITY
010	GEORGE	Smith	83 First	Howard
105	MARY	Jones	Street 842VineAve	Losantiville
152	SAM	Tones	33 Elm st	Paris
215	SARAH	Ackerman	440 U.S.110	Upton
244	MANILA	Sengupta	24 FriendsStreet	New Delhi
300	ROBERT	Samuel	9 Fifth Cross	Washington
335	HENRY	Williams	12 Moore Street	Boston
400	RACHEL	Lee	121Harrison	New York
441	PETER	Thompson	road	Paris

EMPSALRAY

EMPID	SALARY	BENEFITS	DESIGNATION
010	75000	15000	Manager
105	65000	15000	Manager
152	80000	25000	Director
215	75000	12500	Manager
244	50000	12000	Clerk
300	45000	10000	Clerk
335	40000	10000	Clerk
400	32000	7500	Salesman
441	28000	7500	Salesman

(i) To display Firstname, Lastname, Address and City of all employees living in Paris from the table EMPLOYEES.

Ans. Select Firstname,Lastname,Address,City from Employees where City=”Paris”

(ii) To display the content of EMPLOYEES table in descending order of FIRSTNAME.

Ans. Select * from Employees Order By Firstname Desc

(iii) To display the Firstname, Lastname, and Total Salary of all managers from the tables, where Total Salary is calculated as Salary+Benifts.

Ans. Select Firstname,Lastname,Salary+Benefits from Employees, Empsalary where Designation=”Manager” and Employees.EmpId=EmpSalary.EmpId

(iv) To display the Maximum salary among Managers and Clerks from the table EMPSALARY.

Ans. Select Designation,max(Salary) from EmpSalary where Designation=”Manager” or Designation=”Clerk”

(v) SELECT FIRSTNAME,SALARY FROM EMPLOYEES,EMPSALARY WHERE DESTINATION =’Salesman’AND EMPOLYEES.EMPID=EMPSALARY.EMPID;

Ans. Firstname Salary Rachel 32000 Peter 28000

(vi) SELECT COUNT (DISTINT DESIGNATION ) FROM EMPSALARY

Ans. 4

(vii) SELECT DESIGNATION , SUM(SALARY) FROM EMPSALARY GROUP BY DESIGNATION HAVING COUNT(*)>2;

Ans. Designation Sum(Salary) Manager 215000 Clerk 135000

(viii) SELECT SUM (BENEFITS) FROM EMPSALARY WHERE DESIGNATION=’Clerk’;

Ans. 32000 OUTSIDE DELHI 2005

5) Consider the following tables WORKERS and DESIG. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii).