1. High Performing Employees (Easy)

Grokking SQL for Tech Interviews

Ask Author

Back to course home

0% completed

Vote For New Content

1. High Performing Employees (Easy)

Table of Contents

Contents are not accessible

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

MYSQL

. . . .

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 the Employee and Performance tables on the empId 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 |
+-------+

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible