Bright Tutorials Bright Tutorials
CBSE Class 12 Computer Science Question 79 of 91

Grouping Records, Joins in SQL — Question 14

Back to all questions
14
Question

Question 14a

With reference to the table below, answer the questions that follow :

Table : Employees

EmpidFirstnameLastnameAddressCity
010RaviKumarRaj nagarGZB
105HarryWaltorGandhi nagarGZB
152SamTones33 Elm St.Paris
215SarahAckerman440 U.S. 110Upton
244ManilaSengupta24 Friends streetNew Delhi
300RobertSamuel9 Fifth CrossWashington
335RituTondonShastri NagarGZB
400RachelLee121 Harrison St.New York
441PeterThompson11 Red RoadParis

Table : EmpSalary

EmpidSalaryBenefitsDesignation
0107500015000Manager
1056500015000Manager
1528000025000Director
2157500012500Manager
2445000012000Clerk
3004500010000Clerk
3354000010000Clerk
400320007500Salesman
441280007500Salesman

Write the SQL commands for the following using above tables :

(i) To show firstname, lastname, address and city of all employees living in Pairs.

(ii) To display the content of Employees table in descending order of Firstname.

(iii) To display the firstname, lastname and total salary of all managers from the tables Employes and EmpSalary, where total salary is calculated as Salary + Benefits.

(iv) To display the maximum salary among managers and clerks from the table EmpSalary.

Answer

(i)

SELECT Firstname, Lastname, Address, City
FROM Employees
WHERE City = 'Paris';
Output
+-----------+----------+-------------+-------+
| Firstname | Lastname | Address     | City  |
+-----------+----------+-------------+-------+
| SAM       | TONES    | 33 ELM ST.  | PARIS |
| PETER     | THOMPSON | 11 RED ROAD | PARIS |
+-----------+----------+-------------+-------+

(ii)

SELECT *
FROM Employees
ORDER BY Firstname DESC;
Output
+-------+-----------+----------+-------------------+------------+
| empid | FIRSTNAME | LASTNAME | ADDRESS           | CITY       |
+-------+-----------+----------+-------------------+------------+
|   215 | SARAH     | ACKERMAN | 440 U.S. 110      | UPTON      |
|   152 | SAM       | TONES    | 33 ELM ST.        | PARIS      |
|   300 | ROBERT    | SAMUEL   | 9 FIFTH CROSS     | WASHINGTON |
|   335 | RITU      | TONDON   | SHASTRI NAGAR     | GZB        |
|    10 | RAVI      | KUMAR    | RAJ NAGAR         | GZB        |
|   400 | RACHEL    | LEE      | 121 HARRISON ST.  | NEW YORK   |
|   441 | PETER     | THOMPSON | 11 RED ROAD       | PARIS      |
|   244 | MANILA    | SENGUPTA | 24 FRIENDS STREET | NEW DELHI  |
|   105 | HARRY     | WALTOR   | GANDHI NAGAR      | GZB        |
+-------+-----------+----------+-------------------+------------+

(iii)

SELECT e.Firstname, e.Lastname, 
(s.Salary + s.Benefits) AS TotalSalary
FROM Employees e, EmpSalary s  
WHERE e.Empid = s.Empid AND s.Designation = 'Manager';
Output
+-----------+----------+-------------+
| Firstname | Lastname | TotalSalary |
+-----------+----------+-------------+
| RAVI      | KUMAR    |       90000 |
| HARRY     | WALTOR   |       80000 |
| SARAH     | ACKERMAN |       87500 |
+-----------+----------+-------------+

(iv)

SELECT Designation, MAX(Salary) 
FROM EmpSalary 
WHERE Designation IN ('Manager', 'Clerk') 
group by designation;
Output
+-------------+-------------+
| Designation | MAX(Salary) |
+-------------+-------------+
| MANAGER     |       75000 |
| CLERK       |       50000 |
+-------------+-------------+