30 Days Of LeetCode Database Day 27 - 1075. Project Employees I

1Table: Project
2 
3+-------------+---------+
4| Column Name | Type    |
5+-------------+---------+
6| project_id  | int     |
7| employee_id | int     |
8+-------------+---------+
9(project_id, employee_id) is the primary key of this table.
10employee_id is a foreign key to Employee table.
11Each row of this table indicates that the employee with employee_id is working on the project with project_id.
12 
13 
14Table: Employee
15 
16+------------------+---------+
17| Column Name      | Type    |
18+------------------+---------+
19| employee_id      | int     |
20| name             | varchar |
21| experience_years | int     |
22+------------------+---------+
23employee_id is the primary key of this table. It's guaranteed that experience_years is not NULL.
24Each row of this table contains information about one employee.
25 
26 
27Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.
28 
29Return the result table in any order.
30 
31The query result format is in the following example.
32 
33 

1Example 1:
2 
3Input: 
4Project table:
5+-------------+-------------+
6| project_id  | employee_id |
7+-------------+-------------+
8| 1           | 1           |
9| 1           | 2           |
10| 1           | 3           |
11| 2           | 1           |
12| 2           | 4           |
13+-------------+-------------+
14Employee table:
15+-------------+--------+------------------+
16| employee_id | name   | experience_years |
17+-------------+--------+------------------+
18| 1           | Khaled | 3                |
19| 2           | Ali    | 2                |
20| 3           | John   | 1                |
21| 4           | Doe    | 2                |
22+-------------+--------+------------------+
23Output: 
24+-------------+---------------+
25| project_id  | average_years |
26+-------------+---------------+
27| 1           | 2.00          |
28| 2           | 2.50          |
29+-------------+---------------+
30Explanation: The average experience years for the first project is (3 + 2 + 1) / 3 = 2.00 and for the second project is (3 + 2) / 2 = 2.50
31 

1**SQL**
2 
3SELECT p.project_id,
4ROUND(SUM(e.experience_years) / COUNT(e.employee_id),  2) AS average_years
5FROM Project p JOIN Employee e ON e.employee_id = p.employee_id
6GROUP BY p.project_id;