(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). WORKERS
| W_ID |
FIRSTNAME | LASTNAME | ADDRESS | CITY |
|---|---|---|---|---|
| 102 |
Sam | Tones | 33 Elm St. | Paris |
| 105 |
Sarah | Ackerman | 44 U.S.110 | NewYork |
| 144 |
Manila | Sengup ta | 24 Friends Street | New Delhi |
| 210 |
George | Smith | 83 First Street | Howard |
| 255 |
Mary | Jones | 842 Vine Ave. | Losantiville |
| 300 |
Robert | Samuel | 9 Fifth Cross | Washington |
| 335 |
Henry | Williams | 12Moore Street | Boston |
| 403 |
Ronny | Lee | 121 Harrison St. | New York |
| 451 |
Pat | Thomps on | 11 Red Road | Paris |
DESIG
| W_ID |
SALARY | BENEFITS | DESIGINA TION |
|---|---|---|---|
| 102 | 75000 | 15000 | Manager |
| 105 | 85000 | 25000 | Director |
| 144 | 70000 | 15000 | Manager |
| 210 | 75000 | 12500 | Manager |
| 255 | 50000 | 12000 | Clerk |
| 300 | 45000 | 10000 | Clerk |
| 335 | 40000 | 10000 | Clerk |
| 400 | 32000 | 7500 | Salesman |
| 451 | 28000 | 7500 | Salesman |
(i) To display W_ID Firstname, address andCity of all employees living in New York fromthe Table WORKERs
Ans: select W_ID ,firstname,address,city from workers where city=”New York”
(ii) To display the content of workers table in ascending order of LASTNAME.
Ans:Select * from Worker Order By lastname Asc
(iii) To display the FIRSTNAME, LASTNAME and Total Salary of all Clerks from the tables WORKERS And DESIG, where Total salary is calculated as Salary + benifts.
Ans: Select firstname, lastname, salary+benefits where worker.w_id=desg.w_id and Designation=”Clerk”
(iv) To display the minimum salary among managers and Clerks from the tables DESIG.
Ans: (Try This Answer)
(v) SELECT FIRSTNAME, SALARY FROM WORKERS, DESIG WHERE DESIGINATION = “MANAGER” AND WORKERS.W_ID = DESIGN.W_ID
Ans: FIRSTNAME SALARY
Sam 75000
Manila 70000
George 75000
(vi)SELECT COUNT(DISTINCT DESIGNATION) FROM DESIGN ;
Ans: 4
(vii) SELECT DESIGNATION, SUM(SALARY) FROM DESIG GROUP BY DESIGNATION HAVING COUNT (*) < 3;
Ans: Designation Sum(Salary)
Director 85000
Salesman 60000
(viii) SELECT SUM(BENIFTS) FROM DESIG WHERE DESIGINATION =”salesman”;
Ans: 15000
CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( mrkdata@yahoo.com )
