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 ( mrkdata@yahoo.com )
