Enter fullscreen mode

Welcome to the most comprehensive SQL guide ever crafted for freshers and intermediate learners! This isn’t just a guide—it’s your one-stop solution to mastering SQL for interviews, real-world projects, and beyond. Whether you're aspiring to become a Data Engineer, Analyst, or Software Developer, this document will make you SQL-ready like never before.

✅ 1. Introduction to SQL

🔍 Explanation:

SQL (Structured Query Language) is used to manage and manipulate relational databases. It helps perform operations like data insertion, retrieval, modification, and deletion. It's also used to define schema, manage access, and control transactions.

🔠 Syntax:

sql
CopyEdit
SELECT column1, column2 FROM table_name WHERE condition;

📌 Use Case:

sql
CopyEdit
SELECT Name, Age FROM Employees WHERE Age > 30;

🔥 Interview Questions & Answers:

Q1. Your login system is vulnerable to SQL injection. How would you detect and fix it?

Answer: You can detect injection risks by testing inputs like ' OR 1=1--. Prevent it by using prepared statements (parameterized queries) and ORM frameworks that avoid string concatenation in SQL.

Q2. Without using TOP, LIMIT, or window functions, how would you fetch the second highest salary?

sql
CopyEdit
SELECT MAX(Salary) FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

Explanation: Filters out the highest and returns the next one.

Q3. What happens if you run a SELECT * on a table with a BLOB column?

Answer: It may return unreadable binary data or crash the client. Always specify column names and avoid fetching heavy data types like BLOB unless needed.

✅ 2. SQL Data Types

🔍 Explanation:

Each column in SQL must have a defined data type, such as INTEGER, VARCHAR, DATE, BOOLEAN, etc. Data types ensure that only valid values are stored.

🔠 Syntax:

sql
CopyEdit
CREATE TABLE Employees (
    ID INT,
    Name VARCHAR(100),
    Salary DECIMAL(10,2),
    JoinDate DATE
);

📌 Use Case:

sql
CopyEdit
INSERT INTO Employees (ID, Name, Salary, JoinDate) VALUES (1, 'Anu', 50000.00, '2023-01-01');

🔥 Interview Questions & Answers:

Q1. Can you store numbers with high precision like ₹10 crore up to 4 decimal places?

Answer: Yes, use DECIMAL(14,4) — 14 total digits with 4 after the decimal. For money, avoid FLOAT due to rounding errors.

Q2. What happens if you try to insert a string into an INT column?

Answer: It either throws an error or silently converts based on DBMS. In MySQL, '123abc' becomes 123.

Q3. When would you prefer CHAR over VARCHAR?

Answer: Use CHAR for fixed-length fields like gender codes or country codes ('IN', 'US'), as it improves lookup speed.

✅ 3. DDL – Data Definition Language

🔍 Explanation:

DDL defines and modifies the structure of database objects like tables, schemas, and constraints.

🔠 Syntax:

sql
CopyEdit
CREATE TABLE Departments (ID INT PRIMARY KEY, Name VARCHAR(50));
ALTER TABLE Departments ADD Location VARCHAR(100);
DROP TABLE Departments;

📌 Use Case:

sql
CopyEdit
ALTER TABLE Employees ADD CONSTRAINT chk_salary CHECK (Salary > 0);

🔥 Interview Questions & Answers:

Q1. Difference between DROP, TRUNCATE, and DELETE?

Answer:

DROP: Deletes table + data + structure. Irreversible.
TRUNCATE: Deletes all rows, resets identity but keeps structure.
DELETE: Deletes selected rows using WHERE. Transactional.

Q2. How do you modify a column’s data type without losing data?

Answer:

Use ALTER TABLE MODIFY COLUMN, but first ensure all existing data is compatible with the new type.

Q3. Can you rename a table in SQL without losing data?

Answer:

Yes:

sql
CopyEdit
ALTER TABLE old_name RENAME TO new_name;

✅ 4. DML – Data Manipulation Language

🔍 Explanation:

DML is used to manipulate data in existing tables: insert, update, and delete.

🔠 Syntax:

sql
CopyEdit
INSERT INTO Employees VALUES (...);
UPDATE Employees SET Salary = 60000 WHERE ID = 2;
DELETE FROM Employees WHERE ID = 3;

📌 Use Case:

sql
CopyEdit
INSERT INTO Employees (ID, Name, Salary) VALUES (5, 'Amit', 70000);

🔥 Interview Questions & Answers:

Q1. How would you insert data into one table from another table with a condition?

sql
CopyEdit
INSERT INTO HighEarners (ID, Name, Salary)
SELECT ID, Name, Salary FROM Employees WHERE Salary > 100000;

Q2. Can you rollback a DELETE operation? When and how?

Answer: Yes, if it's inside a transaction and hasn’t been committed. Use ROLLBACK.

Q3. What is MERGE in SQL and when is it used?

Answer:

MERGE combines INSERT, UPDATE, DELETE into one. Useful for upserts.

✅ 5. DQL – Data Query Language

🔍 Explanation:

DQL is primarily for reading data using SELECT.

🔠 Syntax:

sql
CopyEdit
SELECT Name FROM Employees WHERE Department = 'Finance';

📌 Use Case:

sql
CopyEdit
SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 5;

🔥 Interview Questions & Answers:

Q1. How does SQL process this query?

sql
CopyEdit
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY;

Answer: The logical order is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

Q2. Can you use GROUP BY without aggregates?

Answer: Technically yes, but it's useful only when applying aggregates.

Q3. Difference between DISTINCT and GROUP BY?

Answer: DISTINCT removes duplicates. GROUP BY groups data and allows aggregation.

✅ 6. Aggregate Functions

🔍 Explanation:

These return a single result for a group of rows. Examples: SUM, AVG, MIN, MAX, COUNT.

🔠 Syntax:

sql
CopyEdit
SELECT AVG(Salary) FROM Employees;

📌 Use Case:

sql
CopyEdit
SELECT Department, SUM(Salary) FROM Employees GROUP BY Department;

🔥 Interview Questions & Answers:

Q1. Why does AVG(Salary) sometimes return null?

Answer: If all values in the column are NULL.

Q2. Difference between COUNT(*) and COUNT(column)?

Answer: COUNT(*) counts all rows; COUNT(column) ignores NULLs.

Q3. Can you use aggregate functions in WHERE clause?

Answer: No. Use HAVING for aggregates.

✅ 7. Joins

🔍 Explanation:

Joins combine rows from multiple tables based on related columns.

🔠 Syntax:

sql
CopyEdit
SELECT e.Name, d.DeptName
FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.ID;

📌 Use Case:

sql
CopyEdit
SELECT e.Name, d.Name FROM Employees e LEFT JOIN Departments d ON e.DeptID = d.ID;

🔥 Interview Questions & Answers:

Q1. Explain SELF JOIN with example.

Answer:

sql
CopyEdit
SELECT A.Name AS Manager, B.Name AS Employee
FROM Employees A JOIN Employees B ON A.ID = B.ManagerID;

Q2. What is the result of a LEFT JOIN if no match is found?

Answer: NULLs will be returned for the columns of the right table.

Q3. Can a JOIN cause a Cartesian product?

Answer: Yes, if there's no valid ON condition or if CROSS JOIN is used.

✅ 8. Subqueries

🔍 Explanation:

A subquery is a query inside another query. Can be used in SELECT, FROM, or WHERE clauses.

🔠 Syntax:

sql
CopyEdit
SELECT Name FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

🔥 Interview Questions & Answers:

Q1. Difference between correlated and non-correlated subquery?

Answer: A correlated subquery depends on each row of the outer query. A non-correlated one executes independently.

Q2. Can subqueries return multiple values?

Answer: Yes, with operators like IN, ANY, ALL.

✅ 9. Window Functions

🔍 Explanation:

They perform calculations across a sliding window of rows.

🔠 Syntax:

sql
CopyEdit
SELECT Name, Salary,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM Employees;

🔥 Interview Questions & Answers:

Q1. Difference between RANK, DENSE_RANK, and ROW_NUMBER?

Answer:

RANK: Skips numbers on tie
DENSE_RANK: Doesn’t skip
ROW_NUMBER: Always unique

Q2. Use of LAG and LEAD?

Answer: They return the previous and next row values within the partition.

✅ 10. CTEs (Common Table Expressions)

🔍 Explanation:

Temporary named result set used to simplify complex queries.

🔠 Syntax:

sql
CopyEdit
WITH HighSalary AS (
  SELECT * FROM Employees WHERE Salary > 100000
)
SELECT * FROM HighSalary;

🔥 Interview Questions & Answers:

Q1. What is a recursive CTE and where is it used?

Answer: Used for hierarchical data like org charts. Includes base case + recursive part.

Q2. Are CTEs better than subqueries?

Answer: Yes, for readability and reuse, especially in complex nested queries.

✅ The rest 10 concepts (Transactions → JSON) with similar tricky Q&A are continued in the next reply... 👇

Continuing the complete SQL guide with tricky interview-level Q&A — here are the remaining concepts:

✅ 11. Transactions

🔍 Explanation:

A transaction is a sequence of SQL statements executed as a single unit. It should follow ACID properties: Atomicity, Consistency, Isolation, Durability.

🔠 Syntax:

sql
CopyEdit
BEGIN TRANSACTION;
UPDATE Employees SET Salary = 80000 WHERE ID = 1;
COMMIT;

📌 Use Case:

Rollback changes on failure:

sql
CopyEdit
BEGIN;
UPDATE Accounts SET Balance = Balance - 1000 WHERE ID = 1;
-- Error occurs
ROLLBACK;

🔥 Interview Questions & Answers:

Q1. How would you ensure a money transfer between accounts doesn’t result in partial updates?

Answer: Wrap both UPDATEs in a transaction and COMMIT only if both succeed. Use ROLLBACK on failure.

Q2. Explain isolation levels and their impact on performance.

Answer:

READ UNCOMMITTED → Allows dirty reads
READ COMMITTED → No dirty reads
REPEATABLE READ → No dirty or non-repeatable reads
SERIALIZABLE → Highest isolation; prevents phantom reads but slower

Use based on consistency vs performance trade-off.

Q3. When would you use SAVEPOINT?

Answer: When you want partial rollbacks within a large transaction.

✅ 12. Indexing

🔍 Explanation:

Indexes improve read performance by reducing data scanned, especially in WHERE and JOIN operations.

🔠 Syntax:

sql
CopyEdit
CREATE INDEX idx_emp_name ON Employees(Name);

📌 Use Case:

sql
CopyEdit
CREATE INDEX idx_multi ON Employees(DeptID, Salary);

🔥 Interview Questions & Answers:

Q1. Can indexes hurt performance? When?

Answer: Yes. They slow down INSERT, UPDATE, DELETE because the index also needs updating. Use only on read-heavy columns.

Q2. What is a covering index?

Answer: An index that includes all the columns needed by a query — no need to access the table.

Q3. Clustered vs Non-clustered Index?

Answer:

Clustered: Alters row order physically (only one per table).
Non-clustered: Logical order with pointers.

✅ 13. Views & Materialized Views

🔍 Explanation:

View = Virtual table based on a query.
Materialized View = Query result physically stored and periodically refreshed.

🔠 Syntax:

sql
CopyEdit
CREATE VIEW ActiveEmployees AS SELECT * FROM Employees WHERE IsActive = 1;

🔥 Interview Questions & Answers:

Q1. Can a view be updated? Under what conditions?

Answer: Yes, only if:

Based on one table
No aggregate or GROUP BY
All columns in SELECT are updatable

Q2. How are materialized views useful in reporting dashboards?

Answer: Avoids re-running expensive queries every time. Refresh manually or periodically.

✅ 14. Query Optimization

🔍 Explanation:

Process of tuning queries to improve speed and reduce resource usage.

💡 Tips:

Use EXPLAIN to analyze performance.
Use selective indexes.
Avoid SELECT *.

🔥 Interview Questions & Answers:

Q1. What do you look at in an EXPLAIN plan?

Answer: Scan type (full scan vs index), filter cost, sort operations, table order in joins.

Q2. How do you optimize queries on large tables?

Answer:

Index WHERE columns
Use partitions
Break into smaller temp tables
Use caching if data is static

✅ 15. SQL Constraints

🔍 Explanation:

Constraints enforce rules on data integrity — like NOT NULL, UNIQUE, PRIMARY KEY, CHECK.

🔠 Syntax:

sql
CopyEdit
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Salary DECIMAL(10,2) CHECK (Salary > 0)
);

🔥 Interview Questions & Answers:

Q1. UNIQUE vs PRIMARY KEY?

Answer: PRIMARY KEY = UNIQUE + NOT NULL. One per table. UNIQUE allows multiple NULLs.

Q2. Can a CHECK constraint refer to another table?

Answer: No. For cross-table validation, use triggers.

✅ 16. Triggers

🔍 Explanation:

Triggers are blocks that execute automatically in response to INSERT, UPDATE, or DELETE events.

🔠 Syntax:

sql
CopyEdit
CREATE TRIGGER afterInsert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
  INSERT INTO AuditLog(...) VALUES (...);
END;

🔥 Interview Questions & Answers:

Q1. Can triggers cause performance issues?

Answer: Yes. They run behind-the-scenes, and multiple nested triggers can slow down inserts/updates.

Q2. Difference between BEFORE and AFTER triggers?

Answer:

BEFORE: Validate/change data before insert/update
AFTER: Log or sync data after commit

✅ 17. Stored Procedures

🔍 Explanation:

Stored Procedures are precompiled SQL blocks that encapsulate logic for reuse.

🔠 Syntax:

sql
CopyEdit
CREATE PROCEDURE GetEmployeesByDept(IN deptID INT)
BEGIN
  SELECT * FROM Employees WHERE DeptID = deptID;
END;

🔥 Interview Questions & Answers:

Q1. Why use stored procedures over application code?

Answer:

Better performance (compiled once)
Centralized logic
Less network roundtrips
Secured access via execution

Q2. Can stored procedures return result sets and output parameters?

Answer: Yes. Use OUT params and SELECT to return sets.

✅ 18. Recursive Queries

🔍 Explanation:

Used to query hierarchical data like employee-manager trees.

🔠 Syntax:

sql
CopyEdit
WITH RECURSIVE OrgChart AS (
  SELECT ID, Name, ManagerID FROM Employees WHERE ManagerID IS NULL
  UNION ALL
  SELECT e.ID, e.Name, e.ManagerID FROM Employees e JOIN OrgChart o ON e.ManagerID = o.ID
)
SELECT * FROM OrgChart;

🔥 Interview Questions & Answers:

Q1. How do you avoid infinite recursion?

Answer: Add a level counter and stop at depth OR set a MAX RECURSION level.

Q2. Real-world use cases of recursive queries?

File directory structures
Org hierarchy
Bill of materials
Comments with replies

✅ 19. Partitioning

🔍 Explanation:

Partitioning divides a table logically (and sometimes physically) to improve performance on large datasets.

🔠 Syntax:

sql
CopyEdit
CREATE TABLE Sales (
  ID INT,
  SaleDate DATE
)
PARTITION BY RANGE (YEAR(SaleDate)) (
  PARTITION p1 VALUES LESS THAN (2023),
  PARTITION p2 VALUES LESS THAN (2024)
);

🔥 Interview Questions & Answers:

Q1. When should you partition a table?

Answer: When the table grows large and queries mostly hit specific ranges (e.g., logs by year or month).

Q2. What types of partitioning exist?

RANGE
LIST
HASH
COMPOSITE

✅ 20. JSON in SQL

🔍 Explanation:

Modern SQL engines (MySQL, Postgres, SQL Server) support JSON for semi-structured data.

🔠 Syntax:

sql
CopyEdit
SELECT JSON_VALUE(Details, '$.price') FROM Products;

🔥 Interview Questions & Answers:

Q1. When should you store data as JSON in SQL?

Answer: When schema is flexible, and new attributes are added frequently (e.g., product metadata, custom fields).

Q2. Can you index JSON fields?

Comprehensive SQL Guide for Freshers and Intermediate Learners

Subscribe to my newsletter

Dnyaneshwar Shekade