Consider the following tables Employee and Salary. Write SQL commands for the statements

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( * ) 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 Salary
Set Total_sal = Basic + D.A. + HRA + Bonus;

(v)

max(Basic)

10000

(vi)

count(*)

2

4

(vii)

Distinct Depid

101

102

103