-
Notifications
You must be signed in to change notification settings - Fork 8
/
Copy pathsqlquery11-Empnum.sql
43 lines (39 loc) · 2.56 KB
/
sqlquery11-Empnum.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Information about the table:
Table Employee_data:
+---------+----------+----------+-------------------+---------+------------+--------+----------+
| EmpCode | EmpFName | EmpLName | Job | Manager | HireDate | Salary | DeptCode |
+---------+----------+----------+-------------------+---------+------------+--------+----------+
| 9369 | TONY | STARK | SOFTWARE ENGINEER | 7902 | 1980-12-17 | 2800 | 20 |
| 9499 | TIM | ADOLF | SALESMAN | 7698 | 1981-02-20 | 1600 | 30 |
| 9566 | KIM | JARVIS | MANAGER | 7839 | 1981-04-02 | 3570 | 20 |
| 9654 | SAM | MILES | SALESMAN | 7698 | 1981-09-28 | 1250 | 30 |
| 9782 | KEVIN | HILL | MANAGER | 7839 | 1981-06-09 | 2940 | 10 |
| 9788 | CONNIE | SMITH | ANALYST | 7566 | 1982-12-09 | 3000 | 20 |
| 9839 | ALFRED | KINSLEY | PRESIDENT | 7566 | 1981-11-17 | 5000 | 10 |
| 9844 | PAUL | TIMOTHY | SALESMAN | 7698 | 1981-09-08 | 1500 | 30 |
| 9876 | JOHN | ASGHAR | SOFTWARE ENGINEER | 7788 | 1983-01-12 | 3100 | 20 |
| 9900 | ROSE | SUMMERS | TECHNICAL LEAD | 7698 | 1981-12-03 | 2950 | 20 |
| 9902 | ANDREW | FAULKNER | ANALYST | 7566 | 1981-12-03 | 3000 | 10 |
| 9934 | KAREN | MATTHEWS | SOFTWARE ENGINEER | 7782 | 1982-01-23 | 3300 | 20 |
| 9591 | WENDY | SHAWN | SALESMAN | 7698 | 1981-02-22 | 500 | 30 |
| 9698 | BELLA | SWAN | MANAGER | 7839 | 1981-05-01 | 3420 | 30 |
| 9777 | MADII | HIMBURY | ANALYST | 7839 | 1981-05-01 | 2000 | NULL |
| 9860 | ATHENA | WILSON | ANALYST | 7839 | 1992-06-21 | 7000 | 50 |
| 9861 | JENNIFER | HUETTE | ANALYST | 7839 | 1996-07-01 | 5000 | 50 |
+---------+----------+----------+-------------------+---------+------------+--------+----------+
Problem Statement
Fetch the number of employees for each role/Job.
Note: Name the number of employees as "empnum" using Alias Keyword.
Solution:
SELECT job, COUNT(empfname) AS empnum FROM employee_data GROUP BY job;
Output:
+-------------------+--------+
| job | empnum |
+-------------------+--------+
| SOFTWARE ENGINEER | 3 |
| SALESMAN | 4 |
| MANAGER | 3 |
| ANALYST | 5 |
| PRESIDENT | 1 |
| TECHNICAL LEAD | 1 |
+-------------------+--------+