0% completed
Problem Statement
Table: Employee
Each row in this table represents an individual employee, detailing their unique ID, name, department they belong to, and their salary.
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| empId | int |
| name | varchar |
| department | varchar |
| salary | int |
+-------------+---------+
empId is the primary key for this table.
Each row of this table contains information about an employee, including their name, department, and salary.
Table: Performance
+-------------+------+
| Column Name | Type |
+-------------+------+
| empId | int |
| rating | int |
+-------------+------+
empId is the primary key for this table.
empId is a foreign key referencing the empId column of the Employee table.
Each row of this table contains the performance rating (an integer value) of an employee.
Problem Definition
Write an SQL query to find the names of employees with a performance rating above 8. Return the result table in any order.
The query result format is in the following example:
Example
Input:
Employee table:
+-------+---------+-------------+--------+
| empId | name | department | salary |
+-------+---------+-------------+--------+
| 1 | Alice | HR | 70000 |
| 2 | Bob | Engineering | 80000 |
| 3 | Charlie | HR | 60000 |
| 4 | David | Engineering | 90000 |
+-------+---------+-------------+--------+
Performance table:
+-------+--------+
| empId | rating |
+-------+--------+
| 1 | 9 |
| 2 | 8 |
| 3 | 7 |
| 4 | 9 |
+-------+--------+
Output:
+---------+
| name |
+---------+
| Alice |
| David |
+---------+
Only Alice and David have performance rating above 8.
Try It Yourself
Solution
To solve this problem, we need to identify employees in the Employee
table who have a performance rating higher than 8. We will use SQL query to join the Employee
table with the Performance
table and filter the employees based on their performance rating.
- Join Employee and Performance tables: Begin by executing a
SELECT
statement to join theEmployee
andPerformance
tables on theempId
column. - Filter by Performance Rating: Introduce a
WHERE
clause to filter out employees with a performance rating higher than 8.
SQL Query
The SQL query to achieve this is as follows:
SELECT e.name FROM Employee e JOIN Performance p ON e.empId = p.empId WHERE p.rating > 8;
Step by Step Approach
Step 1: Join Employee and Performance Tables
Initiate the process by joining the Employee
and Performance
tables on the empId
column. Select the name
column only after joining both tables.
SELECT e.name FROM Employee e JOIN Performance p ON e.empId = p.empId
Output After Step 1:
+---------+
| name |
+---------+
| Alice |
| Bob |
| Charlie |
| David |
+---------+
Step 2: Filter by Performance Rating
Refine the selection by applying a condition to filter out employees with a performance rating higher than 8.
SELECT e.name FROM Employee e JOIN Performance p ON e.empId = p.empId WHERE p.rating > 8;
Final Output:
+-------+
| name |
+-------+
| Alice |
| David |
+-------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible