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] )