Project Employees I

Task: Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.

Table: Project

+-------------+------+
| Column Name | Type |
+-------------+------+
| project_id  | INT  |
| employee_id | INT  |
+-------------+------+

Table: Employee

+------------------+-------------+
| Column Name      | Type        |
+------------------+-------------+
| employee_id      | INT         |
| name             | varchar(50) |
| experience_years | INT         |
+------------------+-------------+

Input: Project table:

+------------+-------------+
| project_id | employee_id |
+------------+-------------+
| 1          | 1           |
| 1          | 2           |
| 1          | 3           |
| 2          | 1           |
| 2          | 4           |
+------------+-------------+

Employee table:

+-------------+--------+------------------+
| employee_id | name   | experience_years |
+-------------+--------+------------------+
| 1           | Khaled | 3                |
| 2           | Ali    | 2                |
| 3           | John   | 1                |
| 4           | Doe    | 2                |
+-------------+--------+------------------+

Output:

+------------+---------------+
| project_id | average_years |
+------------+---------------+
| 1          | 2             |
| 2          | 2.5           |
+------------+---------------+

View Reference Solution & Explanation ▼

This solution uses a `JOIN` to combine data from multiple tables, the `GROUP BY` clause to aggregate results, and the `ROUND()` function to format numerical output.

SELECT p.project_id, ROUND(AVG(e.experience_years), 2) AS average_years FROM Project p JOIN Employee e ON p.employee_id = e.employee_id GROUP BY p.project_id;