SQL Basic Questions for Interviews with Answers: A Comprehensive Guide

SQL (Structured Query Language) is a critical skill for any developer, database administrator, or data analyst. Interviewers frequently assess SQL proficiency through both theoretical questions and practical problem-solving tasks. This blog covers the most commonly asked SQL interview questions with answers and examples, ensuring you’re fully prepared for your interview. By reading this post, you’ll gain the knowledge and confidence needed to excel in your SQL interviews.

1. What is SQL? Explain its uses.

SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It allows users to:

• Retrieve data from databases (using queries).
• Insert, update, and delete records.
• Create and modify database structures.
• Control access to the database.

In modern applications, SQL plays a vital role in handling vast amounts of structured data efficiently.

2. What are the different types of SQL commands?

SQL commands are grouped into five categories:

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT
• DQL (Data Query Language): SELECT

3. What is a primary key? Can a table have multiple primary keys?

A primary key is a column (or set of columns) that uniquely identifies each row in a table. It must contain unique, non-null values.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50)
);

A table cannot have multiple primary keys, but it can have a composite primary key using multiple columns.

4. What is the difference between WHERE and HAVING?

• WHERE: Filters rows before grouping.
• HAVING: Filters groups after grouping.

-- WHERE example
SELECT department, COUNT(*) AS total_employees
FROM employees
WHERE department != 'HR'
GROUP BY department;

-- HAVING example
SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

5. What is the difference between INNER JOIN and OUTER JOIN?

• INNER JOIN: Returns only matching rows from both tables.
• OUTER JOIN (e.g., LEFT JOIN): Returns all rows from one table and matched (or NULL) from the other.

-- INNER JOIN
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- LEFT OUTER JOIN
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

6. What is a foreign key?

A foreign key is a column in one table that references the primary key of another table, ensuring referential integrity.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

7. What is the difference between DELETE and TRUNCATE?

• DELETE: Removes specific rows (can use WHERE), is logged, and slower.
• TRUNCATE: Removes all rows quickly, not logged per row, cannot use WHERE.

-- DELETE with condition
DELETE FROM employees WHERE department = 'HR';

-- TRUNCATE entire table
TRUNCATE TABLE employees;

8. What is indexing, and why is it important?

Indexing creates a data structure (like a book index) to speed up data retrieval. It significantly improves query performance on large tables.

CREATE INDEX idx_employee_name ON employees (name);

9. What is normalization? Explain its types.

Normalization reduces redundancy and improves data integrity:

• 1NF: Atomic values, no repeating groups.
• 2NF: No partial dependencies (all non-key columns depend on the full primary key).
• 3NF: No transitive dependencies (non-key columns depend only on the primary key).
• BCNF: Stronger version of 3NF for complex dependencies.

10. What are aggregate functions? List some examples.

Aggregate functions return a single value from a set of rows:

• COUNT() – number of rows
• SUM() – total sum
• AVG() – average
• MIN() / MAX() – smallest/largest value

SELECT 
  COUNT(*) AS total_employees,
  AVG(salary) AS average_salary
FROM employees;

11. How do you find duplicate rows in a table?

SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

12. What is a subquery? Explain with an example.

A subquery is a query nested inside another query.

-- Employees earning more than average salary
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

13. What is the difference between UNION and UNION ALL?

• UNION: Combines results and removes duplicates.
• UNION ALL: Combines results and keeps duplicates.

-- UNION (distinct)
SELECT name FROM employees
UNION
SELECT name FROM managers;

-- UNION ALL (with duplicates)
SELECT name FROM employees
UNION ALL
SELECT name FROM managers;

14. How do you optimize SQL queries?

• Use indexes on frequently queried columns.
• Avoid SELECT *; fetch only needed columns.
• Prefer JOIN over subqueries when possible.
• Optimize WHERE clauses (e.g., avoid functions on indexed columns).
• Analyze execution plans using EXPLAIN.

Conclusion

This comprehensive guide covers all the important SQL interview questions, ensuring you don’t need to look elsewhere. With clear explanations and examples, you can confidently tackle SQL interviews. SQL proficiency is an indispensable skill in the tech industry, and thorough preparation can set you apart from other candidates. Bookmark this page, practice the examples, and elevate your SQL expertise to excel in your interviews!

Frequently Asked Questions