To display the frequency of employees department wise. To list the names of those employees

Q. Consider the following tables Employee and Salary. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (i) to (vii)

Table: Employee

  
 
 
	 
Eid
 
	 
Name
 
	 
Depid
 
	 
Qualification
 
	 
Sec
 
 
 
	 
1
 
	 
Deepali Gupta
 
	 
101
 
	 
MCA
 
	 
F
 
 
 
	 
2
 
	 
Rajat Tyagi
 
	 
101
 
	 
BCA
 
	 
M
 
 
 
	 
3
 
	 
Hari Mohan
 
	 
102
 
	 
B.A.
 
	 
M
 
 
 
	 
4
 
	 
Harry
 
	 
102
 
	 
М.А.
 
	 
M
 
 
 
	 
5
 
	 
Sumit Mittal
 
	 
103
 
	 
B.Tech.
 
	 
M
 
 
 
	 
6
 
	 
Jyoti
 
	 
101
 
	 
M.Tech.
 
	 
F
 
 
 

Table: Salary

 
 
 
	 
Eid
 
	 
Basic
 
	 
D.A.
 
	 
HRA
 
	 
Bonus
 
 
 
	 
1
 
	 
6000
 
	 
2000
 
	 
2300
 
	 
200
 
 
 
	 
2
 
	 
2000
 
	 
300
 
	 
300
 
	 
30
 
 
 
	 
3
 
	 
1000
 
	 
300
 
	 
300
 
	 
40
 
 
 
	 
4
 
	 
1500
 
	 
390
 
	 
490
 
	 
30
 
 
 
	 
5
 
	 
8000
 
	 
900
 
	 
900
 
	 
80
 
 
 
	 
6
 
	 
10000
 
	 
300
 
	 
490
 
	 
89
 
 
 
 

(i) To display the frequency of employees department wise.

(ii) To list the names of those employees only whose name starts with 'H'

(iii) To add a new column in salary table. The column name is Total_Sal.

(iv) To store the corresponding values in the Total_Sal column.

(v) Select max(Basic) from Salary where Bonus > 40 ;

(vi) Select count(*) from Employee group by Sex;

(vii) Select Distinct Depid from Employee;

Answer =

(i)
Select Depid , count( Eid ) from Employee group by Depid ;

(ii)
Select Name from Employee where Name like “H%”;

(iii)
Alter table Salary add column Total_Sal integer;

(iv)
Update Employee
Set Total_sal = Basic + D.A. + HRA + Bonus;

(v)

max(Basic)

10000

 

(vi)

count(*)

2

4

(vii)

Distinct Depid

101

102

103