1+ -- 1076. Project Employees II
2+ --
3+ -- Table: Project
4+ --
5+ -- +-------------+---------+
6+ -- | Column Name | Type |
7+ -- +-------------+---------+
8+ -- | project_id | int |
9+ -- | employee_id | int |
10+ -- +-------------+---------+
11+ -- (project_id, employee_id) is the primary key of this table.
12+ -- employee_id is a foreign key to Employee table.
13+ -- Table: Employee
14+ --
15+ -- +------------------+---------+
16+ -- | Column Name | Type |
17+ -- +------------------+---------+
18+ -- | employee_id | int |
19+ -- | name | varchar |
20+ -- | experience_years | int |
21+ -- +------------------+---------+
22+ -- employee_id is the primary key of this table.
23+ --
24+ --
25+ -- Write an SQL query that reports all the projects that have the most employees.
26+ --
27+ -- The query result format is in the following example:
28+ --
29+ -- Project table:
30+ -- +-------------+-------------+
31+ -- | project_id | employee_id |
32+ -- +-------------+-------------+
33+ -- | 1 | 1 |
34+ -- | 1 | 2 |
35+ -- | 1 | 3 |
36+ -- | 2 | 1 |
37+ -- | 2 | 4 |
38+ -- +-------------+-------------+
39+ --
40+ -- Employee table:
41+ -- +-------------+--------+------------------+
42+ -- | employee_id | name | experience_years |
43+ -- +-------------+--------+------------------+
44+ -- | 1 | Khaled | 3 |
45+ -- | 2 | Ali | 2 |
46+ -- | 3 | John | 1 |
47+ -- | 4 | Doe | 2 |
48+ -- +-------------+--------+------------------+
49+ --
50+ -- Result table:
51+ -- +-------------+
52+ -- | project_id |
53+ -- +-------------+
54+ -- | 1 |
55+ -- +-------------+
56+ -- The first project has 3 employees while the second one has 2.
57+
58+ -- # Write your MySQL query statement below
59+ select project_id from Project
60+ group by project_id
61+ having count (employee_id) =
62+ (
63+ select count (employee_id)
64+ from Project
65+ group by project_id
66+ order by count (employee_id)
67+ desc
68+ limit 1
69+ )
0 commit comments