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

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

Table: Employees

 
 
 
 
	 
Empid
 
	 
Firstnam
 
	 
Lastname
 
	 
Address
 
	 
City
 
 
 
	 
010
 
	 
Ravi
 
	 
Kumar
 
	 
Raj nagar
 
	 
GZB
 
 
 
	 
105
 
	 
Harry
 
	 
Waltor
 
	 
Gandhi nagar
 
	 
GZB
 
 
 
	 
152
 
	 
Sam
 
	 
Tones
 
	 
33 Elm St.
 
	 
Paris
 
 
 
	 
215
 
	 
Sarah
 
	 
Ackerman
 
	 
440 U.S. 110
 
	 
Upton
 
 
 
	 
244
 
	 
Manila
 
	 
Sengupta
 
	 
24 Friends street
 
	 
New Delhi
 
 
 
	 
300
 
	 
Robert
 
	 
Samuel
 
	 
9 Fifth Cross
 
	 
Washington
 
 
 
	 
335
 
	 
Ritu
 
	 
Tondon
 
	 
Shastri Nagar
 
	 
GZB
 
 
 
	 
400
 
	 
Rachel
 
	 
Lee
 
	 
121 Harrison St.
 
	 
New York
 
 
 
	 
441
 
	 
Peter
 
	 
Thompson
 
	 
11 Red Road
 
	 
Paris
 
 
 
 

 Table : EmpSalary

 
 
 
	 
Empid
 
	 
Salary
 
	 
Benefits
 
	 
Designation
 
 
 
	 
010
 
	 
75000
 
	 
15000
 
	 
Manager
 
 
 
	 
105
 
	 
65000
 
	 
15000
 
	 
Manager
 
 
 
	 
152
 
	 
80000
 
	 
25000
 
	 
Director
 
 
 
	 
215
 
	 
75000
 
	 
12500
 
	 
Manager
 
 
 
	 
244
 
	 
50000
 
	 
12000
 
	 
Clerk
 
 
 
	 
300
 
	 
45000
 
	 
10000
 
	 
Clerk
 
 
 
	 
335
 
	 
40000
 
	 
10000
 
	 
Clerk
 
 
 
	 
400
 
	 
32000
 
	 
7500
 
	 
Salesman
 
 
 
 

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 Emp Salary, where total salary is calculated as Salary + Benefits.
(iv) To display the maximum salary among managers and clerks from the table EmpSalary.

Give the Output of following SQL commands:

(i) Select firstname, Salary from Employees, Empsalary where Designation = 'Salesman' and Employees. Empid = Empsalary.Empid;
(ii) Select count(distinct designation) from EmpSalary:
(iii) Select designation, sum(salary) from EmpSalary group by designation having count(*) > 2;
(iv) Select sum(Benefits) from EmpSalary where Designation = 'Clerk';

Answer =

(i)

Select firstname, lastname, address, city from Employee where city = “Paris”;

(ii)

Select * from Employee order by Firstname desc;

(iii)

Select firstname, lastname , (Salary + Benefits) “total salary” from Employee natural join EmpSalary  ;

 

(iv)

Select max(Salary) from EmpSalary where Designation in (“Manager”,  “Clerk”) ;

Output:

(i)

Fristname	salary
Rachel	32000
Peter	28000

(ii)

count(distinct designation)

4

(iii)

designation	Sum(salary)
Manager	215000
Clerk	135000

(iv)

Sum(Benefits)

32000