COMPUTER SCIENCE STRUCTURED QUERY LANGUAGE

(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 ( [email protected] )