Q. Consider the following EMP and DEPT tables:
Table: EMP
| EmpNo | EmpName | City | Designation | DOJ | Sal | Comm | DeptID |
|---|---|---|---|---|---|---|---|
| 8369 | SMITH | Mumbai | CLERK | 1990-12-18 | 800.00 | NULL | 20 |
| 8499 | ANYA | Varanasi | SALESMAN | 1991-02-20 | 1600.00 | 300,00 | 30 |
| 8521 | SETH | Jaipur | SALESMAN | 1991-02-22 | 1250.00 | 500.00 | 30 |
| 8566 | MAHADEVAN | Delhi | MANAGER | 1991-04-02 | 2985.00 | NULL | 20 |
Table: DEPT
| DeptID | DeptName | MgrID | Location |
|---|---|---|---|
| 10 | SALES | 8566 | Mumbai |
| 20 | PERSONNEL | 9698 | Delhi |
| 30 | ACCOUNTS | 4578 | Delhi |
| 40 | RESEARCH | 8839 | Bengaluru |
Write the SQL command to get the following:
(a) Show the minimum, maximum and average salary of managers.
(b) Count the number of clerks in the organization.
(c) Display the designation-wise list of employees with name, salary and date of joining.
(d) Count the number of employees who are not getting commission.
(e) Show the average salary for all departments with more than 5 working people.
(f) List the count of employees grouped by DeptID.
(g) Display the maximum salary of employees in each department.
(h) Display the name of employees along with their designation and department name.
(i) Count the number of employees working in ACCOUNTS department.
Answer :-
(a) Select Min( Sal ) , Max( Sal), Avg ( Sal ) from EMP where Designation = “MANAGER” ;
(b) Select Count ( * ) from EMP where Designation = “CLERK” ;
(c) Select EmpName , DOJ , Sal from EMP group by Designation ;
(d) Select Count( * ) from EMP Comm is Null ;
(e) Select Avg( Sal ) from EMP group by DeptID having Count(*) > 5 ;
(f) Select count(*) from EMP group by DeptID ;
(g) Select max( Sal ) from EMP group by DeptID ;
(h) Select EmpName , Designation , DeptName from EMP natural join DeptID ;
(i) Select count(*) from EMP where DeptID = 30 ;
Count the number of employees who are not getting commission.
ReplyDeleteSelect Count( * ) from EMP Comm is not Null ;
i think this should be this as comm should be null according to condition
Select Count( * ) from EMP Comm is Null ;
Yes you, I will correct it.😊
Deleteselect count(*) from EMP where comm is Null;
ReplyDelete???
DeleteIt's correct
DeleteHow to create refrence table in this..?
ReplyDeleteBy using foreign key.
DeleteIt will be good if you can also show how to create table before giving answer to question.. thankyou
ReplyDeleteOk, we will try.
DeleteSelect count(*) from EMP group by DeptID = 30 ; its it from dept
ReplyDeleteisnt it ffrom dept
DeleteI have corrected it.
Deleteit would be great if you could attach outputs as well...it would help in better understanding
ReplyDeletecould you please explain (c) because acc to the answer it would display name, sal , doj of smith anya and mahadevan only but question asked for designation wise list.
ReplyDeletePost a Comment
You can help us by Clicking on ads. ^_^
Please do not send spam comment : )