Here is a structured list of SQL questions with answers based on your uploaded PPT (SQL
Basics Recap) along with tips to remember under each answer. These are suitable for viva or
quick revision.
---
💡 Question 1
Q: Write a query to find all employee names who are earning more than 2500 but less than
5000.
Answer:
SELECT ename FROM emp WHERE sal > 2500 AND sal < 5000;
Tip to Remember: Use AND to combine multiple conditions when all must be true. Always check
boundary values in the condition.
---
💡 Question 2
Q: Find all employees working in Dept#20 as 'Analyst', earning less than 2000, and hired at
least 30 years ago.
Answer:
SELECT * FROM emp
WHERE deptno = 20 AND job = 'Analyst' AND sal < 2000 AND hiredate < SYSDATE - 10000;
Tip to Remember: SYSDATE - 10000 roughly equals 30 years ago. Use AND for chaining
multiple conditions.
---
💡 Question 3
Q: Find employees with 'E' in their name or ending with 'G', from Dept#30, earning at least 1500.
Answer:
SELECT * FROM emp
WHERE (ename LIKE '%E%' OR ename LIKE '__%G') AND deptno = 30 AND sal >= 1500;
Tip to Remember: Use % for any number of characters and _ for exactly one character in LIKE.
Group conditions using parentheses.
---
💡 Question 4
Q: Find employees with 'ER' in their job, job length ≥ 3, salary between 2500 and 5000, and with
the company ≤ 15 years.
Answer:
SELECT * FROM emp
WHERE job LIKE '%ER%' AND sal > 2500 AND sal < 5000 AND hiredate >= SYSDATE - 5600;
Tip to Remember: Use LIKE for partial matching and calculate time with SYSDATE - days.
---
💡 Question 5
Q: Display sum, min, max, and average salaries paid to employees.
Answer:
SELECT COUNT(*), SUM(sal), MIN(sal), MAX(sal), AVG(sal) FROM emp;
Tip to Remember: Group functions like SUM, MIN, MAX, AVG always work on multiple rows.
---
💡 Question 6
Q: Show total salary paid by each department.
Answer:
SELECT SUM(sal), deptno FROM emp GROUP BY deptno;
Tip to Remember: Use GROUP BY to aggregate data per column value.
---
💡 Question 7
Q: Show average and max salary per job for employees reporting to MGR 7839 and not having
'K' in ename.
Answer:
SELECT AVG(sal), MAX(sal), job FROM emp
WHERE mgr = 7839 AND ename NOT LIKE '%K%'
GROUP BY job;
Tip to Remember: Only columns in GROUP BY or aggregate functions can appear in SELECT.
---
💡 Question 8
Q: Average salary per department if it has at least 2 employees and min salary > average salary
+ 100.
Answer:
SELECT AVG(sal)
FROM emp
GROUP BY deptno
HAVING COUNT(*) >= 2 AND MIN(sal) > AVG(sal) + 100;
Tip to Remember: Use HAVING to apply conditions on grouped data (after aggregation).
---
💡 Question 9
Q: Max and min salary per department if average salary is between 1500 and 3000, no ‘A’ in
name or null commission, and hired 6+ months ago.
Answer:
SELECT MAX(sal), MIN(sal)
FROM emp
WHERE ename NOT LIKE '%A%' OR comm IS NULL AND MONTHS_BETWEEN(SYSDATE,
hiredate) > 6
GROUP BY deptno
HAVING MAX(sal) > 4500 AND AVG(sal) < 1500;
Tip to Remember: Be careful with OR/AND priorities — always use brackets for clarity.
---
💡 Question 10
Q: Sort employee salary in ascending order.
Answer:
SELECT deptno, sal FROM emp ORDER BY sal;
Tip to Remember: Use ORDER BY for sorting. Default is ascending (ASC), use DESC for
descending.
---
💡 Question 11
Q: List employee name and department name of those hired ≥10 years ago and are Analysts.
Answer:
SELECT empno, ename, d.deptno, dname, ROUND(MONTHS_BETWEEN(SYSDATE,
hiredate),0), hiredate
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND MONTHS_BETWEEN(SYSDATE, hiredate) > 120
AND job = 'Analyst';
Tip to Remember: For joins, list both tables and link using table1.col = table2.col.
---
💡 Question 12
Q: Show employee and manager name for those in Accounting department.
Answer:
SELECT e.ename, e.empno, b.ename, b.empno
FROM emp e, emp b, dept d
WHERE e.empno = b.mgr AND d.deptno = b.deptno AND dname = 'ACCOUNTING';
Tip to Remember: Self joins use the same table twice with different aliases.
---
💡 Question 13
Q: Show employees earning minimum salary but not Manager/Clerk and must earn commission.
Answer:
SELECT ename, sal, deptno
FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp)
AND job <> 'Manager'
AND job != 'CLERK'
AND comm IS NOT NULL;
Tip to Remember: Subqueries are queries inside () used in WHERE, FROM, or SELECT.
---
💡 Question 14
Q: Show deptno where min salary < avg salary across all employees and dept location ends
with 'K'.
Answer:
SELECT e.deptno, MIN(sal)
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND loc LIKE '____K'
GROUP BY e.deptno
HAVING MIN(sal) < (SELECT AVG(sal) FROM emp);
Tip to Remember: Use LIKE '____K' for 5-character location ending in ‘K’. Use HAVING with
GROUP BY.
---
Would you like a downloadable PDF or Word version of this compiled list?
