Identify employees who have never received a performance review.

This question was asked in Interview at black rock. Read more

Company: BlackRock

CTC: 26LPA

Source: LinkedIn

SQL Interview Question

Q. Identify employees who have never received a performance review.

-- Create the Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE,
    Department VARCHAR(50)
);

-- Create the PerformanceReviews table
CREATE TABLE PerformanceReviews (
    ReviewID INT PRIMARY KEY,
    EmployeeID INT,
    ReviewDate DATE,
    Comments TEXT,
    Rating INT CHECK (Rating BETWEEN 1 AND 5),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

-- Insert sample data into Employees table
INSERT INTO Employees VALUES
(1, 'Alice', 'Johnson', '2020-03-15', 'HR'),
(2, 'Bob', 'Smith', '2019-07-01', 'Finance'),
(3, 'Charlie', 'Brown', '2021-05-20', 'IT'),
(4, 'Diana', 'White', '2022-01-10', 'Marketing'),
(5, 'Evan', 'Taylor', '2018-11-25', 'Operations');

-- Insert sample data into PerformanceReviews table
INSERT INTO PerformanceReviews VALUES
(101, 1, '2021-06-01', 'Exceeded expectations', 5),
(102, 2, '2022-03-10', 'Met expectations', 4),
(103, 1, '2022-12-15', 'Outstanding performance', 5),
(104, 5, '2019-12-01', 'Needs improvement', 3);

See this code on db-fiddle

Solution

SELECT * 
 FROM Employees 
    WHERE EmployeeID NOT IN (SELECT EmployeeID FROM PerformanceReviews);

Explanation

It is a very Streight forward solution where a subquery is utilized to filter the employee records.

Subquery contains the EmployeeID from PerformanceReviews which is used to filter the Employees table to find the employees who have not received a performance review.

I hope this would have been helpful for you, consider sharing it with your friends. thank you.

Vishal

Hi, I am Vishal Jaiswal, I have about a decade of experience of working in MNCs like Genpact, Savista, Ingenious. Currently i am working in EXL as a senior quality analyst. Using my writing skills i want to share the experience i have gained and help as many as i can.

Spread the love