COMPUTER SCIENCE 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).

 

EMPID FIRSTNAME LASTNAME ADDRESS CITY
010
GEORGE Smith 83 First Street Howard
105 MARY Jones 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 121 Harrison New York
441
PETER Thompson 11 Red 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”

 

CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( [email protected] )