They may give a small table and ask:
• Write query to get second highest salary
Sol => select * from employee order by salary desc limit 1 offset 1
Or
Select * from employee where salary<(select max(salary) from employee group by salary)
limit 1;
Or
Select distinct salary from employee order by salary desc limit 1,1
• Count students grouped by department
Sol => select department ,count(*) from student group by department
• Find employees who do not have manager (LEFT JOIN + IS NULL)
Sol => select * from employee where manager is null;
• Count students per department, order by count desc
Sol => select department,count(student_id) from students group by department order by
count(department) desc
• Write query to delete duplicate rows
Sol => delete from employee group by id having count(*)>1;
• Find max marks for each subject using GROUP BY
Sol => select subject, max(marks) from students group by subject ;
• Display employees earning more than department average (HAVING)
Sol => select earning from money where earning > (select avg(earning) from money)
• Write query using CASE WHEN (grade calculation)
sol=> SELECT
name,
marks,
CASE
WHEN marks >= 90 THEN 'A'
WHEN marks >= 75 THEN 'B'
WHEN marks >= 60 THEN 'C'
WHEN marks >= 40 THEN 'D'
ELSE 'F'
END AS grade
FROM students;
• Use window function (ROW_NUMBER/PARTITION BY)
WITH ranked AS (
SELECT
student_id,
name,
subject,
marks,
ROW_NUMBER() OVER (PARTITION BY subject ORDER BY marks DESC) AS rank
FROM students
SELECT student_id, name, subject, marks
FROM ranked
WHERE rank = 1;