Here, we will discuss SQL Technical Interview Questions, which interviewers ask in most company interviews.
Table of Contents
1. What is MySQL?
MySQL is an open-source relational database management system (RDBMS) that uses SQL (Structured Query Language) to manage data. It’s known for its speed, reliability, and ease of use, powering applications.
2. Top MySQL Interview Topics
Storage engines (InnoDB vs MyISAM), indexes (B-Tree, covering), ACID properties, normalization (1NF-3NF), JOIN types, transactions/isolation levels, SQL optimization (EXPLAIN, indexing), replication, partitioning, stored procedures, triggers, deadlocks, constraints (PK, FK), SQL injection prevention, TRUNCATE vs DELETE, CHAR vs VARCHAR, GROUP BY/HAVING, query tuning, NULL handling, and data types.
SQL Technical Interview Questions
1. What are triggers in SQL, and how are they used?
Triggers are special stored procedures that automatically execute in response to certain events on a table, such as INSERT, UPDATE, or DELETE.
They are used for enforcing business rules, auditing changes, and maintaining data integrity.
2. What are the differences between WHERE and HAVING clauses in SQL?
The WHERE clause filters records before any groupings are made, while the HAVING clause filters records after groupings have been applied.
HAVING is typically used with aggregate functions.
3. How can you write a query to find the second-highest salary of an employee?
SELECT MAX(salary)
FROM employees
WHERE
salary < (
SELECT MAX(salary) FROM employees
)
4. What is the purpose of the GROUP BY clause in SQL?
The GROUP BY clause is used to arrange identical data into groups. It is often used with aggregate functions to perform calculations on each group.
5. Explain the concept of SQL JOINS and list all types of joins with examples.
SQL JOINS combine rows from two or more tables based on a related column.
Join types include INNER JOIN (only matching rows), LEFT JOIN (all rows from the left table), RIGHT JOIN (all rows from the right table), and FULL OUTER JOIN (all rows from both tables).
6. How does indexing improve SQL query performance? Explain the different types of indexes.
Indexing creates a data structure that improves the speed of data retrieval operations.
Indexing types include primary indexes (unique identifiers) and secondary indexes (non-unique columns).
7. What is a stored procedure in SQL, and how is it beneficial?
Stored procedures are precompiled SQL statements that can be executed on demand.
They enhance performance by reducing the need to send multiple queries and help in maintaining business logic.
8. Differentiate between TRUNCATE and DELETE statements in SQL.
TRUNCATE is a DDL command that removes all rows from a table without logging individual row deletions.
DELETE is a DML command that removes rows based on a condition and logs each deletion.
9. Explain the difference between CHAR and VARCHAR data types.
CHAR is a fixed-length data type, while VARCHAR is variable-length.
CHAR pads the data with spaces, whereas VARCHAR only uses as much space as needed.
10. What are constraints in SQL, and what are the different types of constraints?
Constraints are rules enforced on data columns to ensure data integrity.
It includes NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints.
11. How do RANK, ROW_NUMBER, DENSE_RANK, LEAD, and LAG window functions work?
RANK, ROW_NUMBER, DENSE_RANK, LEAD, and LAG are window functions that provide access to a set of rows related to the current row. They are used for ranking, retrieving previous or next row values, and more.
12. What is the difference between a Primary Key and a Secondary Key in SQL?
A Primary Key uniquely identifies each record in a table, while a Secondary Key is an alternate key used for searching records but does not have to be unique.
13. How would you optimize a slow SQL query?
Optimization techniques include analyzing execution plans, indexing relevant columns, avoiding SELECT *, and using efficient joins and filters.
14. What are the differences between Common Table Expressions (CTEs) and subqueries? Which one is faster?
Common Table Expressions (CTEs) are temporary result sets defined within the execution scope of a single SQL statement, while Subqueries are nested queries.
CTEs can improve readability and organization of complex queries.
15. Explain the differences between UNION and UNION ALL in SQL.
UNION combines the results of two queries and removes duplicates, while UNION ALL includes all duplicates.
UNION is typically slower due to the deduplication process.
16. What is the difference between DDL, DML, and DCL commands in SQL?
DDL (Data Definition Language) commands define database structures (e.g., CREATE, ALTER).
DML (Data Manipulation Language) commands manipulate data (e.g., INSERT, UPDATE).
DCL (Data Control Language) commands control access (e.g., GRANT, REVOKE).
17. What are the various types of operators in SQL? Provide examples.
Operators in SQL include arithmetic operators (e.g., +, -, *), comparison operators (e.g., =, <, >), logical operators (e.g., AND, OR), and set operators (e.g., UNION, INTERSECT).
18. How do you use stored procedures to enhance the performance of SQL queries?
Stored procedures can enhance performance by minimizing network traffic, allowing batch processing of multiple SQL statements, and reducing execution time through pre-compilation.
19. Write a query to find employees who have worked for more than 5 years.
SELECT * FROM employees WHERE DATEDIFF(CURRENT_DATE, hire_date) > 1825;
20. What are views in SQL, and how are they used?
Views are virtual tables based on the result of a SELECT query.
They simplify complex queries and enhance security by restricting access to specific data.
21. Write a SQL query to find departments with no employees.
SELECT d.department_id FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id IS NULL;
22. What are the various types of relationships in SQL? Explain with examples.
Relationships include one-to-one (e.g., each employee has one ID), one-to-many (e.g., one department has many employees), and many-to-many (e.g., students enrolled in multiple courses).
23. What is the order of execution of an SQL query?
The order of execution is: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.
This sequence defines how SQL processes the query.
24. Write a SQL query to find employees with the same name working in the same department.
SELECT name, department_id FROM employees GROUP BY name, department_id HAVING COUNT(*) > 1;
25. What is an index in SQL, and what are its different types?
An index is a database object that improves the speed of data retrieval.
It includes unique indexes (no duplicate values) and composite indexes (multiple columns).
26. Write a query to identify the top 3 departments with the highest average salaries.
SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ORDER BY avg_salary DESC LIMIT 3;
27. How can duplicate rows in an SQL query result be handled?
Duplicate rows can be handled using the DISTINCT keyword in SELECT statements or by using GROUP BY to aggregate results.
28. Explain the difference between a subquery and a join. When would you use each?
A subquery is a nested query used to return data for a parent query, while a join combines rows from two or more tables.
Subqueries are often used when filtering based on aggregates, while joins are used for combining data.
29. How does the GROUP BY clause differ from the WHERE clause?
The GROUP BY clause groups rows sharing a property so aggregate functions can be applied, whereas the WHERE clause filters rows before grouping.
30. Write a query to find the top 2 products with the highest sales.
SELECT product_id, SUM(sales) AS total_sales FROM sales GROUP BY product_id ORDER BY total_sales DESC LIMIT 2;
31. Explain the differences between INNER JOIN and OUTER JOIN in SQL.
INNER JOIN returns only matching rows between tables, while OUTER JOIN returns all rows from one table and matched rows from the other, filling with NULLs where there are no matches.
32. Write a query to identify customers who placed an order but haven’t made a payment.
SELECT c.customer_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN payments p ON o.order_id = p.order_id WHERE p.payment_id IS NULL;
33. What is the difference between a function and a stored procedure in SQL?
A function returns a single value and can be used in SQL expressions, while a stored procedure can perform multiple operations and does not return a value directly.
34. Write a query to find employees who have the same name and work in the same department.
SELECT name, department_id FROM employees GROUP BY name, department_id HAVING COUNT(*) > 1;
