1. What is the difference between WHERE and HAVING?
Solution:
WHERE filters rows before aggregation.
HAVING filters rows after aggregation.
SELECT department, AVG(salary)
FROM employees
WHERE salary > 3000
GROUP BY department
HAVING AVG(salary) > 5000;
2. Write a query to find the second-highest salary.
Solution:
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
3. How do you fetch the first 5 rows of a table?
Solution:
SELECT * FROM employees
LIMIT 5; -- (MySQL/PostgreSQL)
For SQL Server:
SELECT TOP 5 * FROM employees;
4. Write a query to find duplicate records in a table.
Solution:
SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
5. How do you find employees who don’t belong to any department?
Solution:
SELECT *
FROM employees
WHERE department_id IS NULL;
6. What is a JOIN, and write a query to fetch data using INNER JOIN.
Solution:
A JOIN combines rows from two or more tables based on a related column.
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
7. Write a query to find the total number of employees in each department.
Solution:
SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id;
8. How do you fetch the current date in SQL?
Solution:
SELECT CURRENT_DATE; -- MySQL/PostgreSQL
SELECT GETDATE(); -- SQL Server
9. Write a query to delete duplicate rows but keep one.
Solution:
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS rn
FROM table_name
)
DELETE FROM CTE WHERE rn > 1;
10. What is a Common Table Expression (CTE), and how do you use it?
Solution:
A CTE is a temporary result set defined within a query.
WITH EmployeeCTE AS (
SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id
)
SELECT * FROM EmployeeCTE WHERE total_employees > 10;
Hope it helps :)
#sql #dataanalysts