SQL interview questions
Query writing: joins, GROUP BY with HAVING, second-highest salary, DELETE versus TRUNCATE versus DROP, and query writing.
1. What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes chosen rows (with WHERE), is logged row by row and can be rolled back. TRUNCATE removes all rows quickly, keeps the table structure and usually resets auto-increment. DROP removes the whole table, structure included.
Asked in: Cognizant GenC
2. What are the types of joins?
INNER JOIN returns rows with matches in both tables. LEFT JOIN returns all rows from the left table plus matches (NULL where none). RIGHT JOIN is the mirror. FULL OUTER JOIN returns all rows from both. CROSS JOIN returns every combination. A self join joins a table to itself, for example employees to their managers.
Asked in: LTIMindtree
3. Write a query to show each employee with their department name.
SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.id; Use LEFT JOIN instead if employees without a department must also appear.
Asked in: TCS NQT, Tech Mahindra
4. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates: SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id HAVING COUNT(*) > 5;
Asked in: Deloitte
5. Find the employee with the highest salary.
SELECT name, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees); This returns everyone who shares the top salary. ORDER BY salary DESC LIMIT 1 returns just one row.
Asked in: Cognizant GenC
6. Find the second-highest salary.
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); For the Nth highest: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET N-1; or use DENSE_RANK() in a subquery.
Asked in: Cognizant GenC
7. How do you find duplicate rows?
Group by the columns that should be unique and keep groups with more than one row: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
8. What is the difference between UNION and UNION ALL?
Both combine the results of two SELECTs with the same number and types of columns. UNION removes duplicate rows (slower); UNION ALL keeps every row (faster).
Asked in: Capgemini
9. What does GROUP BY do?
It groups rows that share a value so aggregate functions (COUNT, SUM, AVG, MIN, MAX) work per group: SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id; Every selected column must be grouped or aggregated.
Asked in: HCLTech
10. What are constraints in SQL?
Rules on columns: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK (a condition such as age >= 18) and DEFAULT (a value used when none is given).
Asked in: LTIMindtree
11. What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts all rows; COUNT(column) counts rows where that column is not NULL; COUNT(DISTINCT column) counts unique non-NULL values.
12. What is a subquery? When would you use a join instead?
A query inside another query, used in WHERE, FROM or SELECT (as in the second-highest salary query). A correlated subquery runs once per outer row. Joins are often faster for combining columns from several tables.
13. What is the difference between SQL and MySQL?
SQL is the language for querying relational databases. MySQL is one database system (an RDBMS) that uses SQL, like PostgreSQL or Oracle.
14. What are window functions?
Functions that calculate across a set of related rows without collapsing them, using OVER(): ROW_NUMBER(), RANK(), DENSE_RANK(), and running totals with SUM(...) OVER (ORDER BY ...). Handy for top-N per group.