MySQL Exercise No:5
Consider the following tables WORKERS and DESIGNATION. Write SQL commands for
the statements (i) to (iii) and give output for SQL queries (iv) and (v).
Table: WORKERS
W_Id Firstname Lastname Address City
102 Sam Tones 33Elm St. Paris
105 Sarah Ackerman 440 U.S. 110 New York
144 Manila Sengupta 24Friends St. New Delhi
210 George Smith 83First St. Howard
255 Mary Jones 842 Vine Ave. Losantiville
Table : DESIGNATION
W_Id Salary Benefits Designation
102 75000 15000 Manager
105 85000 25000 Director
144 70000 15000 Manager
210 75000 12500 Manager
255 50000 12000 Clerk
i. To display the content of WORKERS table in ascending order of Lastname.
ii. To display the Firstname, Lastname and Total Salary of a Clerk from the table Workers and
Designation, where Total salary is calculated as Salary + Benefits
iii. To display the Maximum Salary among Managers and Clerks from the table Designation.
iv. Select W_ID, Firstname, Address, City from Workers where City = ‘ New York’;
v. Select W_ID, Firstname, Designation from Workers W, Designation D where W.W_Id
=D.W_Id and Salary > 80000;
Answers
i. Select * From Workers ORDER BY Lastname;
ii. Select Firstname, Lastname, Salary + Benefits “Total Salary” From Workers,
Designation WHERE Workers.W_ID = Designation.W_ID AND Designation = ‘Clerk’;
iii. Select Designation, Max( Salary) FROM Designation GROUP BY Designation HAVING
Designation IN ( ‘Manager’, ‘Clerk’);
W_Id Firstname Address City
iv.
105 Sarah 440 U.S 110 New York
v.
W_Id Firstname Designation
105 Sarah Director