Project Employees I - Problem

You are given two tables: Project and Employee.

The Project table contains information about which employees are working on each project, where (project_id, employee_id) is the primary key.

The Employee table contains information about each employee, including their experience_years.

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

Return the result table in any order.

Table Schema

Project
Column Name Type Description
project_id PK int Project identifier
employee_id PK int Employee identifier, foreign key to Employee table
Primary Key: (project_id, employee_id)
Note: Each row indicates that an employee is working on a project
Employee
Column Name Type Description
employee_id PK int Employee identifier, primary key
name varchar Employee name
experience_years int Years of experience (guaranteed not NULL)
Primary Key: employee_id
Note: Contains information about each employee including their experience

Input & Output

Example 1 — Basic Project Experience Calculation
Input Tables:
Project
project_id employee_id
1 1
1 2
1 3
2 1
2 4
Employee
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
💡 Note:

For project 1: employees 1, 2, 3 have experience years (3, 2, 1), so average = (3+2+1)/3 = 2.00. For project 2: employees 1, 4 have experience years (3, 2), so average = (3+2)/2 = 2.50.
Example 2 — Single Employee Project
Input Tables:
Project
project_id employee_id
1 1
2 2
Employee
employee_id name experience_years
1 Alice 5
2 Bob 8
Output:
project_id average_years
1 5
2 8
💡 Note:

Each project has only one employee, so the average equals the individual employee's experience years.

Constraints

  • 1 ≤ project_id ≤ 100
  • 1 ≤ employee_id ≤ 1000
  • 0 ≤ experience_years ≤ 50
  • experience_years is guaranteed to be not NULL

Visualization

Tap to expand
Project Employees I - SQL Solution INPUT TABLES Project Table: project_id employee_id 1 1 1 2 1 3 2 1 Employee Table: emp_id name exp_yrs 1 Alice 3 2 Bob 2 3 Carol 1 Join on employee_id p.employee_id = e.employee_id ALGORITHM STEPS 1 JOIN Tables Connect Project with Employee using employee_id 2 GROUP BY project_id Group all employees per project together 3 AVG(experience_years) Calculate average experience for each project group 4 ROUND(..., 2) Round result to 2 decimal places as required SELECT project_id, ROUND(AVG( experience_years),2) FROM Project p JOIN Employee e GROUP BY 1 FINAL RESULT Project 1 Calculation: Employees: 1, 2, 3 Experience: 3 + 2 + 1 = 6 Average: 6 / 3 = 2.00 ROUND(2.0, 2) = 2.00 Project 2 Calculation: Employees: 1 Experience: 3 ROUND(3.0, 2) = 3.00 Output: project_id avg_exp 1 2.00 2 3.00 OK - Query Complete Key Insight: The JOIN operation connects each project row with its corresponding employee data. GROUP BY aggregates employees per project, and AVG() calculates mean experience. ROUND(value, 2) ensures exactly 2 decimal places in the output as specified. TutorialsPoint - Project Employees I | Optimal Solution

Related Problems

Asked in
Amazon 12 Facebook 8 Microsoft 6
25.6K Views
Medium Frequency
~8 min Avg. Time
895 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen