CBSE eBooks

CBSE Guess > eBooks > Class XII > Computer Science By . Mr. MRK

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

EMPSALRAY

(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).