SQL

Question 1

Given the employees table (department_id), write a query to select all columns from the employees table where the department_id is 5.

Answer 1

SELECT *
FROM employees
WHERE department_id = 5;

Question 2

Using the employees table (first_name, last_name), write a query to retrieve the first_name and last_name of employees whose last_name starts with ‘S’.

Answer 2

SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'S%';

Question 3

Given the orders table, write a query to find the total number of orders.

Answer 3

SELECT COUNT(*) AS total_orders
FROM orders;

Question 4

Using the employees table (salary, department_id), write a query to calculate the average salary of employees in each department_id.

Answer 4

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

Question 5

Given the employees table (salary), write a query to list the top 3 highest-paid employees.

Answer 5

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 3;

Question 6

Using the employees table (first_name, last_name) and departments table (department_name), write a query to join these tables on department_id and retrieve first_name, last_name, and department_name.

Answer 6

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Question 7

Given the employees table (manager_id), write a query to find employees who do not have a manager.

Answer 7

SELECT *
FROM employees
WHERE manager_id IS NULL;

Question 8

Using the employees table (department_id, salary), write a query to update the salary of employees in department_id 3 by increasing it by 10%.

Answer 8

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 3;

Question 9

Given the temp_data table (created_at), write a query to delete all records where created_at is older than ‘2023-01-01’.

Answer 9

DELETE FROM temp_data
WHERE created_at < '2023-01-01';

Question 10

Using the users table (email), write a query to create an index on the email column.

Answer 10

CREATE INDEX idx_users_email
ON users(email);

Question 11

Given the employees table (job_title), write a query to select the distinct job_title values.

Answer 11

SELECT DISTINCT job_title
FROM employees;

Question 12

Using the employees table (salary), write a query to find the maximum and minimum salary.

Answer 12

SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary
FROM employees;

Question 13

Given the employees table (first_name, last_name, manager_id), write a query to list all employees and their corresponding manager’s first_name and last_name.

Answer 13

SELECT e.first_name AS employee_first, e.last_name AS employee_last,
       m.first_name AS manager_first, m.last_name AS manager_last
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Question 14

Using the employees table (first_name, last_name) and departments table (department_id, department_name), write a query to retrieve the first_name and last_name of employees who work in the ‘Sales’ department using a subquery.

Answer 14

SELECT first_name, last_name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Sales'
);

Question 15

Given the employees table, write a query to add a new column birthdate of type DATE.

Answer 15

ALTER TABLE employees
ADD birthdate DATE;

Question 16

Given the employees table (salary), write a query to retrieve the second highest salary.

Answer 16

SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Question 17

Using the employees table (department_id), write a query to find all department_id that have more than 10 employees.

Answer 17

SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;

Question 18

Given the employees table (employee_id, manager_id, first_name, last_name, salary), write a query to list employees who have the same salary as their manager.

Answer 18

SELECT e.first_name, e.last_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary = m.salary;

Question 19

Using the employees table (department_id, employee_id, salary), write a query to calculate the cumulative salary for each department_id ordered by employee_id.

Answer 19

SELECT department_id, SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS cumulative_salary
FROM employees
ORDER BY department_id;

Question 20

Given the employees table (employee_id, first_name, last_name) and sales table (employee_id, amount), write a query to create a view named v_employee_sales that shows first_name, last_name, and the total_sales amount for each employee.

Answer 20

CREATE VIEW v_employee_sales AS
SELECT e.first_name, e.last_name, SUM(s.amount) AS total_sales
FROM employees e
JOIN sales s ON e.employee_id = s.employee_id
GROUP BY e.first_name, e.last_name;