Database Administrator Top 20+ Basic SQL Interview Questions and Answers

Prepare for your next data or database-related job interview with these essential Basic SQL interview questions and answers. Covers queries, joins, subqueries, indexing, and more. Curated by Interview Chamber.

Interview Chamber Interview Chamber
Jun 25, 2025 - 12:21 Updated: Jun 25, 2025 - 12:37
3
Database Administrator Top 20+ Basic SQL Interview Questions and Answers

Basic DBA SQL Interview Questions and Answers 

Whether you're a beginner or preparing for an entry-level data role, mastering SQL is essential for database management and analysis. Below are the most common SQL interview questions and answers categorized by topic, perfect for revision and practice.

1. What is SQL, and Why is It Important in Data Analysis?

Answer:
SQL (Structured Query Language) is used to interact with and manipulate relational databases. It is vital for data analysis because it allows efficient data retrieval, filtering, aggregation, and transformation. SQL helps analysts extract insights, prepare reports, and make informed decisions.

2. Difference Between SQL and MySQL

Answer:

  • SQL is a standard language used to manage relational databases.

  • MySQL is a specific database management system (DBMS) that implements SQL.

  • SQL is the language; MySQL is a software tool that uses it.

3. Types of SQL Commands

Answer:

  • DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE

  • DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE

  • DCL (Data Control Language): GRANT, REVOKE

  • TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

4. Difference Between WHERE and HAVING Clauses

Answer:

  • WHERE filters rows before aggregation.

  • HAVING filters aggregated data.

Example:

sql
SELECT department, COUNT(*) FROM employees WHERE salary > 50000 GROUP BY department HAVING COUNT(*) > 5;

5. Retrieve All Records from a Table

Answer:

sql
SELECT * FROM table_name;

6. Top 5 Highest Revenue Taxpayers

Answer:

sql
SELECT taxpayer_id, name, revenue FROM taxpayer_records ORDER BY revenue DESC LIMIT 5;

7. SQL JOINS: Types and Differences

Answer:

  • INNER JOIN: Returns rows with matching values in both tables.

  • LEFT JOIN: Returns all rows from the left table and matching ones from the right.

  • RIGHT JOIN: Returns all rows from the right table and matching ones from the left.

  • FULL JOIN: Returns all rows when there’s a match in either table.

Example:

sql
SELECT a.name, b.amount_paid FROM taxpayer_details a INNER JOIN tax_payments b ON a.taxpayer_id = b.taxpayer_id;

8. Join Two Tables Using SQL

Tables:

  • taxpayer_details (taxpayer_id, name, address)

  • tax_payments (taxpayer_id, amount_paid, payment_date)

Query:

sql
SELECT td.name, tp.amount_paid, tp.payment_date FROM taxpayer_details td INNER JOIN tax_payments tp ON td.taxpayer_id = tp.taxpayer_id;

9. SELF JOIN vs CROSS JOIN

Answer:

  • SELF JOIN: Joins a table to itself.

sql
SELECT a.name, b.name AS colleague FROM employees a JOIN employees b ON a.manager_id = b.employee_id;

  • CROSS JOIN: Produces the Cartesian product.

sql
SELECT a.name, b.department FROM employees a CROSS JOIN departments b;

10. Difference Between UNION and UNION ALL

Answer:

  • UNION: Eliminates duplicates.

  • UNION ALL: Includes all results, including duplicates.

Example:

sql
SELECT name FROM customers1 UNION SELECT name FROM customers2;

🔹 Data Aggregation & Filtering

11. Total Tax Collected in 2024

sql
SELECT SUM(amount_paid) AS total_tax FROM tax_payments WHERE YEAR(payment_date) = 2024;

12. Find Second Highest Tax Payment

sql
SELECT MAX(amount_paid) FROM tax_payments WHERE amount_paid < (SELECT MAX(amount_paid) FROM tax_payments);

13. SQL Aggregate Functions

  • COUNT(): Counts rows

  • SUM(): Adds values

  • AVG(): Calculates average

  • MIN(): Minimum value

  • MAX(): Maximum value

14. Count Unique Taxpayers in a City

sql
SELECT COUNT(DISTINCT taxpayer_id) FROM taxpayer_details WHERE city = 'Dar es Salaam';

15. GROUP BY with HAVING Clause

sql
SELECT city, COUNT(*) FROM taxpayers GROUP BY city HAVING COUNT(*) > 5;

🔹 Advanced SQL (Subqueries, CTEs, Indexing)

16. What Is a Subquery?

A query embedded inside another query.

sql
SELECT name FROM taxpayers WHERE revenue > (SELECT AVG(revenue) FROM taxpayers);

17. What Is a Common Table Expression (CTE)?

Temporary named result set.

sql
WITH top_taxpayers AS ( SELECT taxpayer_id, revenue FROM taxpayers ORDER BY revenue DESC LIMIT 10 ) SELECT * FROM top_taxpayers;

18. What Are Window Functions?

Allow row-wise calculations over a window of rows.

sql
SELECT taxpayer_id, revenue, RANK() OVER (ORDER BY revenue DESC) AS rank FROM taxpayers;

19. DELETE vs TRUNCATE vs DROP

  • DELETE: Removes specific rows.

  • TRUNCATE: Removes all rows, keeps structure.

  • DROP: Deletes the table structure entirely.

20. What Is an Index in SQL?

An index boosts performance by speeding up queries.
Types:

  • Clustered Index: Orders actual data rows.

  • Non-Clustered Index: Stores pointers to data.

Example:

sql
CREATE INDEX idx_taxpayer_name ON taxpayers(name);

21. ACID Properties in Transactions

  • Atomicity: All-or-nothing execution.

  • Consistency: Preserves valid data state.

  • Isolation: Transactions are independent.

  • Durability: Changes persist after commit.

Example:

sql
BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; COMMIT;

22. What Are Stored Procedures?

Precompiled reusable SQL blocks.

sql
CREATE PROCEDURE GetTaxpayerPayments(@taxpayer_id INT) AS BEGIN SELECT * FROM tax_payments WHERE taxpayer_id = @taxpayer_id; END;

Execute:

sql
EXEC GetTaxpayerPayments 101;

23. Primary Key vs Unique Key

Feature Primary Key Unique Key
Uniqueness Enforced Enforced
NULL Allowed No Yes (one NULL)
Number Allowed One per table Multiple
Purpose Identify each row Enforce uniqueness

Example:

sql
CREATE TABLE taxpayers ( taxpayer_id INT PRIMARY KEY, email VARCHAR(255) UNIQUE );

24. How to Optimize Slow SQL Queries

  • Use indexes:

sql
CREATE INDEX idx_taxpayer_revenue ON taxpayers(revenue);

  • Avoid SELECT *:

sql
SELECT name, revenue FROM taxpayers WHERE revenue > 100000;

  • Prefer joins over nested subqueries.

  • Use WHERE instead of HAVING when possible.

  • Analyze with EXPLAIN PLAN:

sql
EXPLAIN ANALYZE SELECT * FROM taxpayers WHERE revenue > 100000;