JOIN NOW

Computer Science

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.

SQL Joins & Grouping

8 Likes

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 |
+-------------+-------------+

Answered By

3 Likes

Related Questions