#storedprocedures

Structured Query Language, or SQL, is the foundation of database management. Whether you're a database developer crafting robust schemas and queries or a database administrator ensuring performance, security, and data integrity, proficiency in SQL is essential. This blog will walk you through some of the most commonly asked SQL interview questions tailored for both developers and DBAs (Database Administrators), with clear examples and explanations.

Why SQL Interview Questions Matter

SQL is not just about writing SELECT queries. It's about understanding how to model data, query efficiently, prevent anomalies, and secure sensitive information. Interviewers test both practical SQL skills and conceptual understanding. Whether you're a fresher or experienced professional, preparing well for SQL questions is crucial for landing a job in roles like:

Database Developer

Data Analyst

Data Engineer

DBA (Database Administrator)

Backend Developer

Basic SQL Interview Questions

1. What is SQL?

SQL stands for Structured Query Language. It is used to store, retrieve, manipulate, and manage data in relational databases such as MySQL, SQL Server, PostgreSQL, and Oracle.

2. What is the difference between DELETE, TRUNCATE, and DROP?

Command Description Can Rollback DELETE Deletes specific rows using a WHERE clause Yes TRUNCATE Removes all rows from a table without logging No DROP Deletes the entire table (structure + data) No

3. What is a Primary Key?

A Primary Key is a column (or combination of columns) that uniquely identifies each record in a table. It cannot contain NULL values and must be unique.CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100) );

4. What are the different types of joins in SQL?

INNER JOIN: Returns records with matching values in both tables

LEFT JOIN: Returns all records from the left table, and matched ones from the right

RIGHT JOIN: Returns all records from the right table, and matched ones from the left

FULL OUTER JOIN: Returns all records when there is a match in one of the tables

Intermediate SQL Interview Questions

5. What is normalization? Explain its types.

Normalization is the process of organizing data to reduce redundancy and improve integrity. Common normal forms:

1NF: Atomic columns

2NF: Remove partial dependencies

3NF: Remove transitive dependencies

6. What is an index in SQL?

An index improves the speed of data retrieval. It is similar to the index in a book.CREATE INDEX idx_lastname ON employees(last_name);

Tip: Overusing indexes can slow down write operations.

7. What is a subquery?

A subquery is a query nested inside another query.SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

8. What are aggregate functions in SQL?

Functions that operate on sets of values and return a single value:

SUM()

AVG()

COUNT()

MAX()

MIN()

Advanced SQL Interview Questions (For Developers & DBAs)

9. What is a stored procedure?

A stored procedure is a precompiled set of SQL statements stored in the database. CREATE PROCEDURE GetEmployeeCount AS BEGIN SELECT COUNT(*) FROM employees; END;

Used for code reusability and performance optimization.

10. How do transactions work in SQL?

A transaction is a unit of work performed against a database. It follows ACID properties:

Atomicity

Consistency

Isolation

Durability

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

11. What is deadlock and how do you resolve it?

A deadlock occurs when two or more transactions block each other by holding locks on resources the other transactions need. To resolve:

Set proper lock timeouts

Use consistent locking order

Implement deadlock detection

12. What are triggers in SQL?

A trigger is a special stored procedure that runs automatically in response to certain events (INSERT, UPDATE, DELETE).CREATE TRIGGER before_insert_trigger BEFORE INSERT ON employees FOR EACH ROW BEGIN SET NEW.created_at = NOW(); END;

13. How do you optimize a slow-running query?

Use EXPLAIN to analyze the query plan

Create indexes on frequently searched columns

Avoid SELECT *

Use joins instead of subqueries where appropriate

Limit results using LIMIT or TOP clauses

SQL Questions for DBA-Specific Roles

14. What are the responsibilities of a DBA related to SQL?

Creating and managing databases and users

Backup and recovery

Performance tuning

Monitoring database health

Ensuring data security and access control

15. How do you implement backup and recovery in SQL Server?

Backup BACKUP DATABASE myDB TO DISK = 'D:\Backup\myDB.bak'; -- Restore RESTORE DATABASE myDB FROM DISK = 'D:\Backup\myDB.bak';

Conclusion