SQL Interview Question - 2

Given a table named employees with the following schema:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2),
    hire_date DATE
);
    

Write a SQL query to find the names of employees who have been with the company for more than 5 years and have a salary greater than $50,000.

 

Sample Data:

INSERT INTO employees (id, name, department, salary, hire_date) VALUES
(1, 'John Doe', 'Engineering', 60000.00, '2016-03-25'),
(2, 'Jane Smith', 'Marketing', 55000.00, '2017-07-10'),
(3, 'Alice Johnson', 'Engineering', 70000.00, '2018-01-15'),
(4, 'Bob Brown', 'Sales', 48000.00, '2019-05-20'),
(5, 'Emily Davis', 'HR', 52000.00, '2015-10-30'); 

Solution:

SELECT name
FROM employees
WHERE hire_date < DATE_SUB(CURDATE(), INTERVAL 5 YEAR) AND salary > 50000; 

Explanation:

• DATE_SUB(CURDATE(), INTERVAL 5 YEAR) calculates the date 5 years ago from the current date.
• hire_date < DATE_SUB(CURDATE(), INTERVAL 5 YEAR) checks if the employee's hire date is earlier than 5 years ago.
• salary > 50000 ensures that the employee's salary is greater than $50,000.
• The SELECT statement retrieves the names of employees who satisfy both conditions.

 

Share your alternate solution in comments.