This question was asked in Interview at black rock. Read more
Company: BlackRock
CTC: 26LPA
Source: LinkedIn
-- 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
SELECT *
FROM Employees
WHERE EmployeeID NOT IN (SELECT EmployeeID FROM PerformanceReviews);
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.
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.
Welcome to Day 13 of Learning Python for Data Science! Today, we’re focusing on three…
Test your understanding of Python Data Structure, which we learned in our previous lesson of…
Welcome to Day 12 of Learning Python for Data Science. Today, we’ll dive into Pandas,…
NumPy Array in Python is a powerful library for numerical computing in Python. It provides…
Welcome to Day 9 of Learning Python for Data Science. Today we will explore comprehensions,…
Test your understanding of Python Data Structure, which we learned in our previous lesson of…