Preparing for an SQL interview? Our guide, 20 Most Popular SQL Interview Questions & Answers,covers the most frequently asked questions to help you ace your next interview with confidence. From basic queries to advanced concepts like joins, normalization, and stored procedures, we provide clear, concise explanations and sample answers to boost your understanding. Whether you’re a beginner or looking to refresh your skills, this resource is ideal for you. Enroll in our SQL Online Training Course to deepen your knowledge and practice real-world scenarios. Master SQL fundamentals and stand out in your job search with this comprehensive guide today!
Here SQL Interview Q & A
1. What is SQL and why is it important?
Answer:
SQL (Structured Query Language) is a standard language used to communicate with relational databases. It helps users store, manipulate, retrieve, and manage data efficiently.
Real-time example: An e-commerce website stores customer details, product information, and transaction history in a relational database. SQL is used to fetch a customerтАЩs order history instantly when they log in.
2. What are the different types of SQL statements?
Answer:
SQL statements can be categorized into:
- DDL (Data Definition Language) тАУ CREATE, ALTER, DROP
- DML (Data Manipulation Language) тАУ INSERT, UPDATE, DELETE
- DCL (Data Control Language) тАУ GRANT, REVOKE
- TCL (Transaction Control Language) тАУ COMMIT, ROLLBACK, SAVEPOINT
- DQL (Data Query Language) тАУ SELECT
Real-time example:
A banking application uses DML to insert new transactions and DCL to control access to sensitive customer information.
3. What is the difference between WHERE and HAVING clauses?
Answer:
- WHERE filters rows before grouping.
- HAVING filters groups after aggregation.
Real-time example:
To get departments with an average salary > $50,000:
sql
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;4. What is a Primary Key? Can a table have multiple primary keys?
Answer:
A Primary Key uniquely identifies each row in a table and ensures that values are non-null and unique.
A table cannot have multiple primary keys but can have a composite primary key (a combination of columns).
Real-time example:
In a university database, (student_id, course_id) together can be a composite primary key in the enrollments table.
5. What is a Foreign Key?
Answer:
A Foreign Key enforces a link between two tables by referencing the primary key of another table.
Real-time example:
In an order management system, customer_id in the orders table is a foreign key referring to customers(customer_id).
6. What is the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN?
Answer:
- INNER JOIN returns matching rows from both tables.
- LEFT JOIN returns all rows from the left table and matching rows from the right.
- FULL OUTER JOIN returns all rows when there is a match in either table.
Real-time example:
Displaying customers and their orders (even if they havenтАЩt ordered yet) uses LEFT JOIN.
7. What is normalization? Why is it needed?
Answer:
Normalization is the process of organizing data to reduce redundancy and improve data integrity.
Real-time example:
In a hospital database, storing patient details and doctor details in separate tables reduces duplication and eases updates.
8. Explain the difference between UNION and UNION ALL.
Answer:
- UNION combines results and removes duplicates.
- UNION ALL combines all results including duplicates.
Real-time example:
Fetching customer emails from two regions (removing duplicates) uses UNION.
9. What is an Index? How does it improve performance?
Answer:
An index is a database object that improves the speed of data retrieval.
Real-time example:
A retail application uses an index on product_id to quickly fetch product details when scanning at checkout.
10. What is a view? How is it different from a table?
Answer:
A view is a virtual table based on a SQL query. Unlike tables, views do not store data permanently.
Real-time example:
A finance app creates a view to show only customers with active loans, hiding unnecessary columns.
11. What is ACID in SQL transactions?
Answer:
ACID stands for:
- Atomicity: All steps succeed or fail together
- Consistency: Data remains valid
- Isolation: Transactions don’t interfere
- Durability: Changes persist after commit
Real-time example:
When transferring money between bank accounts, ACID ensures the debit and credit either both happen or neither.
12. How can you find duplicate rows in a table?
Answer:
sql
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;Real-time example:
Finding customers with duplicate phone numbers in a CRM system.
13. What is a stored procedure? Why is it useful?
Answer:
A stored procedure is a reusable SQL code block stored in the database.
Real-time example:
An e-commerce site has a stored procedure to calculate discounts and apply them during checkout automatically.
14. What are triggers?
Answer:
A trigger is an automatic action performed by the database in response to certain events like INSERT, UPDATE, or DELETE.
Real-time example:
When a new employee is inserted into the HR system, a trigger sends a welcome email automatically.
15. What is the difference between CHAR and VARCHAR?
Answer:
- CHAR(n) stores fixed-length strings (always uses n bytes).
- VARCHAR(n) stores variable-length strings (uses actual length + 1 byte).
Real-time example:
For storing country codes (US, IN), CHAR(2) is suitable.
16. How can you optimize SQL queries?
Answer:
Techniques include:
- Use indexes
- Select only necessary columns
- Avoid SELECT *
- Use joins effectively
- Write WHERE clauses smartly
Real-time example:
Optimizing a report query fetching last monthтАЩs sales data by using date range and indexing sales_date.
17. What is a subquery? Can you give an example?
Answer:
A subquery is a query within another SQL query.
Real-time example:
Fetching employees earning more than the average salary:
sql
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);18. What is a correlated subquery?
Answer:
A correlated subquery references columns from the outer query and is executed once for each row.
Real-time example:
Finding employees who earn more than their department average:
sql
SELECT name
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department);19. What is a composite key?
Answer:
A composite key uses multiple columns to uniquely identify a row.
Real-time example:
In a library system, (book_id, member_id) in the borrowed_books table forms a composite key.
20. What is the difference between DELETE, TRUNCATE, and DROP?
Answer:
- DELETE: Removes rows (can use WHERE), can be rolled back
- TRUNCATE: Removes all rows, cannot be rolled back
- DROP: Deletes entire table structure and data
Real-time example:
Clearing temporary session data uses TRUNCATE. Removing an obsolete table uses DROP.
