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

Grouping Records, Joins in SQL — Question 12

Back to all questions
12
Question

Question 12

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

Table : Employee

EidNameDepidQualificationSec
1Deepali Gupta101MCAF
2Rajat Tyagi101BCAM
3Hari Mohan102B.A.M
4Harry102M.A.M
5Sumit Mittal103B.Tech.M
6Jyoti101M.Tech.F

Table : Salary

EidBasicD.AHRABonus
1600020002300200
2200030030030
3100030030040
4150039049030
5800090090080
61000030049089
  1. To display the frequency of employees department wise.
  2. To list the names of those employees only whose name starts with 'H'
  3. To add a new column in salary table. The column name is Total_Sal.
  4. To store the corresponding values in the Total_Sal column.
  5. Select max(Basic) from Salary where Bonus > 40 ;
  6. Select count(*) from Employee group by Sex ;
  7. Select Distinct Depid from Employee ;
Answer

1.

SELECT Depid, COUNT(*) AS Frequency 
FROM Employee 
GROUP BY Depid;

2.

SELECT Name
FROM Employee
WHERE Name LIKE 'H%';

3.

ALTER TABLE Salary
ADD COLUMN Total_Sal FLOAT;

4.

UPDATE Salary
SET Total_Sal = Basic + `D.A.` + HRA + Bonus;

5.

SELECT MAX(Basic)
FROM Salary
WHERE Bonus > 40;
Output
+------------+
| MAX(Basic) |
+------------+
|      10000 |
+------------+

6.

SELECT Sec as sex, COUNT(*) AS Count 
FROM Employee 
GROUP BY Sec ;
Output
+-----+-------+
| sex | Count |
+-----+-------+
| F   |     2 |
| M   |     4 |
+-----+-------+

7.

SELECT DISTINCT Depid FROM Employee ;
Output
+-------+
| Depid |
+-------+
|   101 |
|   102 |
|   103 |
+-------+