📌 Hi, From Ribhu and Susmita 👋
Why Should You Trust Us?
We’re not just sharing theory — we’ve lived this journey ourselves.
Here’s why you should believe in what we’re offering:
● We’ve cracked 40+ LPA software jobs in top product-based companies.
● We’ve cracked IITs and know what it takes to stand out in highly competitive
environments.
ita
● Susmita has built over 10 apps — published and live on both the App Store
and Play Store.
m
● She’s also cracked 7+ startup companies and is deeply active in the freelance
and startup community — helping, collaborating, and growing alongside
founders and tech innovators.
us
● We’ve mentored students, professionals, and aspiring developers who’ve
gone on to land jobs at FAANG-level companies, successful startups, and
fast-growing product firms.
_s
hu
Since we’ve established credibility and walked the path ourselves, we
know exactly what works and what’s just noise.
🚀
Now, let’s get started — and help you build the same kind of success
rib
story.
✅ Top 100 SQL Interview Questions and
Answers (2025)
📌 Basic SQL Questions
1. What is SQL?
○ SQL (Structured Query Language) is used to manage and manipulate
relational databases.
ita
2. What is a primary key?
○ A column (or a set of columns) that uniquely identifies each row in a table.
m
3. What is a foreign key?
○ A column that creates a relationship between two tables by referencing the
us
primary key of another table.
4. What is a unique key?
_s
○ A constraint that ensures all values in a column are unique.
5. What is the difference between WHERE and HAVING?
○ WHERE filters rows before grouping, HAVING filters groups after GROUP BY.
hu
6. What is a JOIN?
○ A JOIN combines rows from two or more tables based on related columns.
rib
7. Types of JOINs:
○ INNER JOIN
○ LEFT JOIN
○ RIGHT JOIN
○ FULL OUTER JOIN
○ CROSS JOIN
○ SELF JOIN
8. What is normalization?
○ Organizing data to reduce redundancy and improve integrity.
9. What is denormalization?
○ Adding redundancy for faster reads by combining tables.
10.What is the difference between CHAR and VARCHAR?
● CHAR has a fixed length, VARCHAR has a variable length.
ita
11.What is a view?
● A virtual table created by a query.
m
12.What is an index?
● A performance tuning method to speed up data retrieval.
us
13.What is a clustered index?
● Sorts and stores data rows in the table based on key values.
_s
14.What is a non-clustered index?
● A separate structure that points to the actual data.
hu
15.What is a subquery?
● A query within another query.
rib
16.What is a correlated subquery?
● A subquery that references columns from the outer query.
17.Difference between DELETE, TRUNCATE, and DROP
● DELETE: Deletes rows with a WHERE clause.
● TRUNCATE: Deletes all rows, cannot be rolled back.
● DROP: Removes the table completely.
18.What is a constraint?
● Rules applied to columns (e.g., NOT NULL, UNIQUE, CHECK, FOREIGN KEY).
19.What is a transaction?
● A sequence of SQL statements treated as a single unit.
20.ACID properties
● Atomicity
● Consistency
● Isolation
ita
● Durability
m
📌 Intermediate SQL Questions
us
21.What is a stored procedure?
● Precompiled SQL code saved and executed on demand.
_s
22.What is a trigger?
● SQL code automatically executed in response to certain events.
hu
23.What is the difference between UNION and UNION ALL?
● UNION removes duplicates, UNION ALL includes all.
24.What is the difference between RANK(), DENSE_RANK(), and
rib
ROW_NUMBER()?
● RANK() skips rankings if there's a tie.
● DENSE_RANK() continues ranking sequentially.
● ROW_NUMBER() assigns a unique row number.
25.What is a CTE (Common Table Expression)?
● A temporary named result set that you can reference within a SELECT, INSERT,
UPDATE, or DELETE.
26.Explain window functions
● Functions like ROW_NUMBER(), RANK(), LEAD(), LAG() used with OVER() to
perform calculations across a set of rows.
27.What is a composite key?
● A primary key made up of two or more columns.
28.What is a pivot table?
● Used to transform rows into columns.
ita
29.What is the difference between INNER JOIN and OUTER JOIN?
● INNER JOIN returns matching rows, OUTER JOIN returns matching + non-matching
rows.
m
30.What is CASE in SQL?
● Similar to if-else, used to perform conditional logic.
us
31.What is the difference between NVL(), ISNULL(), COALESCE()?
● All replace null values but differ by database system.
_s
32.What is a schema?
● A collection of database objects (tables, views, procedures).
hu
33.What is data integrity?
● Ensuring accuracy and consistency of data.
rib
34.What are aggregate functions?
● SUM(), COUNT(), MAX(), MIN(), AVG()
35.What is a temporary table?
● A table created temporarily for storing data temporarily.
36.What is an alias?
● Temporary names for tables or columns.
37.How do you find duplicate records in a table?
SELECT col1, COUNT(*)
FROM table
GROUP BY col1
HAVING COUNT(*) > 1;
38.How to get the second highest salary?
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
ita
39.How to fetch nth highest salary?
● Using ROW_NUMBER(), LIMIT, or subquery.
m
40.How to swap values of two columns?
us
UPDATE table
SET col1 = col2, col2 = col1;
_s
hu
rib
📌 Advanced SQL Questions
41. Explain normalization forms (1NF, 2NF, 3NF, BCNF)
Normalization is a database design technique that aims to reduce redundancy and improve
data integrity by organizing the tables and their relationships. There are several forms of
normalization:
1.First Normal Form (1NF):
○ A table is in 1NF if it only contains atomic (indivisible) values and each
column contains only one value per row. No repeating groups or arrays are
allowed.
○ Example: A column shouldn't contain a list like "John, Mike, Sarah" in one
cell.
ita
2.Second Normal Form (2NF):
○ A table is in 2NF if it is in 1NF and all non-key attributes are fully functionally
dependent on the primary key (i.e., no partial dependency).
m
○ Example: If a table has a composite primary key, each non-key attribute must
depend on the whole primary key.
us
3.Third Normal Form (3NF):
○ A table is in 3NF if it is in 2NF and no transitive dependencies exist. This
means non-key attributes must not depend on other non-key attributes.
_s
○ Example: If "Employee_ID" is the primary key, and "Employee_Name"
depends on "Employee_ID" but "Employee_Department" depends on
"Employee_Name," it violates 3NF.
hu
4.Boyce-Codd Normal Form (BCNF):
○ A table is in BCNF if it is in 3NF, and for every non-trivial functional
dependency, the left side of the dependency is a superkey.
rib
○ Example: If there are dependencies where a non-superkey determines
another non-key attribute, it violates BCNF.
42. Difference between OLTP and OLAP
● OLTP (Online Transaction Processing):
○ Transactional: OLTP systems are designed to handle a large number of
short online transaction queries. They support business processes like sales,
banking, and customer orders.
○ Characteristics:
■ Fast query processing.
■ Frequent inserts, updates, and deletes.
■ Highly normalized schemas (often 3NF).
● OLAP (Online Analytical Processing):
○ Analytical: OLAP systems are designed for complex queries and data
ita
analysis. They support decision-making processes like business intelligence,
reporting, and data mining.
○ Characteristics:
m
■ Complex queries on large data sets.
■ Historical data, multidimensional analysis (e.g., cube analysis).
us
■ Schema often uses denormalized structures like star and snowflake
schemas.
_s
43. Explain materialized view
hu
A materialized view is a database object that contains the results of a query and stores the
data physically. Unlike a regular view, which is a virtual table, a materialized view stores the
results, thus improving query performance for complex or frequently queried operations.
● Benefits:
rib
○ Faster query performance as it avoids recalculating the data.
○ Can be periodically refreshed to keep the data up-to-date.
● Drawback:
○ Requires additional storage and overhead for refreshing the data.
44. Explain indexing strategies
Indexing is used to speed up the retrieval of data from a database. There are several
strategies and types of indexes:
1.Single-column Index: Index on one column, commonly used in WHERE, JOIN, and
ORDER BY clauses.
2.Composite Index: Index on multiple columns, useful when queries involve
conditions on multiple columns.
3.Unique Index: Ensures the uniqueness of values in a column (like primary keys).
4.Full-text Index: Used for text searching in large text fields (like TEXT or VARCHAR).
ita
5.Bitmap Index: Suitable for columns with a limited number of distinct values (e.g.,
gender).
6.Clustered Index: The data is physically stored in the index order. Each table can
m
have only one clustered index.
● Strategy:
us
○ Index columns that are frequently used in queries (e.g., WHERE, JOIN,
ORDER BY).
○ Avoid indexing columns that are rarely queried or frequently updated, as
_s
indexes add overhead.
hu
45. When would you use MERGE?
The MERGE statement is used to perform INSERT, UPDATE, and DELETE operations in
one statement, depending on whether a match is found between the target and source data.
rib
It is often used for synchronization tasks or to maintain data consistency.
● Example use cases:
○ Upsert operations: Insert new records or update existing ones.
○ Data synchronization: Synchronize data between two tables, like syncing
customer data from staging to production.
46. What is data warehousing?
Data Warehousing is the process of collecting and storing data from multiple sources into a
central repository, which is optimized for query and reporting rather than transactional
processing.
● Key characteristics:
○ Integrates data from multiple heterogeneous sources.
○ Stores historical data for analysis.
○ Supports ETL (Extract, Transform, Load) processes for data cleansing and
transformation.
ita
○ Used for decision support, business intelligence, and analytics.
m
47. Explain WITH RECURSIVE queries
WITH RECURSIVE is used to define a Common Table Expression (CTE) that references
us
itself, making it useful for querying hierarchical or recursive data (e.g., organizational charts,
category trees).
● Syntax:
_s
WITH RECURSIVE cte_name AS (
-- Base case
SELECT ...
hu
FROM ...
WHERE ...
UNION ALL
-- Recursive case
SELECT ...
rib
FROM cte_name
JOIN ...
WHERE ...
)
SELECT * FROM cte_name;
48. What are surrogate keys?
A surrogate key is an artificial, unique identifier for a record in a table, typically used instead
of a natural key (e.g., email address, social security number).
● Benefits:
○ Avoids using business data for keys, reducing risk of changes.
○ Simplifies relationships between tables.
● Examples:
○ Auto-incrementing integers (e.g., id column).
○ Globally Unique Identifiers (GUIDs).
ita
49. What is a fact table and dimension table?
● Fact Table:
m
○ Contains measurable, quantitative data (e.g., sales, profits) and foreign keys
to dimension tables.
us
○ Example: A sales fact table could contain columns like sales_amount,
quantity, and foreign keys like product_id and date_id.
_s
● Dimension Table:
○ Contains descriptive attributes related to the fact data (e.g., product names,
categories, customer demographics).
hu
○ Example: A product dimension table could have columns like product_name,
product_category, and price.
rib
50. Explain star and snowflake schema
● Star Schema:
○ A type of data warehouse schema where a central fact table is connected to
dimension tables directly.
○ Simple and efficient for querying but may lead to some redundancy in the
dimension tables.
● Snowflake Schema:
○ A more normalized version of the star schema, where dimension tables are
further normalized into multiple related tables.
○ Reduces data redundancy but can be more complex to query.
51. How to optimize slow queries?
● Indexing: Create indexes on columns used in WHERE, JOIN, ORDER BY, and
GROUP BY clauses.
ita
● **Avoiding SELECT ***: Always specify the required columns to reduce unnecessary
data retrieval.
● Query Refactoring: Simplify complex queries by breaking them down into smaller
subqueries or using appropriate joins.
m
● Analyze Query Plan: Use EXPLAIN to analyze how the query is executed and
identify performance bottlenecks.
us
● Caching: Use query caching to speed up repeated queries.
● Limit the Result Set: Use LIMIT to return only the necessary data.
_s
52. What is query execution plan?
hu
A query execution plan outlines the steps the database will take to execute a SQL query,
including the chosen indexes, join types, and order of operations. It helps identify
performance bottlenecks.
rib
● Key components:
○ Scan types (e.g., index scan, table scan).
○ Join methods (e.g., nested loop, hash join).
○ Cost estimation (estimated CPU and I/O usage).
53. What are covering indexes?
A covering index is an index that contains all the columns required for a query, allowing the
database to satisfy the query entirely from the index without needing to access the
underlying table.
● Benefits:
○ Improves performance by reducing table lookups.
○ Useful for frequently executed queries that need specific columns.
ita
54. What is sharding?
Sharding is the process of splitting large datasets into smaller, more manageable pieces
called shards. Each shard is stored on a different server to distribute the load and improve
performance.
m
● Benefits:
us
○ Improved performance and scalability.
○ Reduced risk of a single point of failure.
● Challenges:
_s
○ Increased complexity in managing distributed data.
○ Requires good partitioning strategy.
hu
55. What is partitioning?
rib
Partitioning involves dividing a large table into smaller, more manageable segments called
partitions. This can be done based on range, list, hash, or other criteria.
● Benefits:
○ Speeds up queries that can work on specific partitions.
○ Simplifies maintenance by allowing operations (like archiving) on specific
partitions.
56. How to handle deadlocks?
Deadlocks occur when two transactions wait for each other to release locks, causing a cycle.
To handle deadlocks:
1.Deadlock Detection: Use the DBMS’s deadlock detection mechanism to identify and
resolve deadlocks.
2.Transaction Timeout: Set transaction timeouts to automatically abort long-running
transactions.
3.Locking Strategy: Ensure that locks are acquired in a consistent order to avoid
cycles.
ita
m
57. Explain isolation levels
SQL isolation levels define the visibility of transactions to each other:
us
1.Read Uncommitted: Allows dirty reads (uncommitted changes from other
transactions).
2.Read Committed: Prevents dirty reads but allows non-repeatable reads.
_s
3.Repeatable Read: Prevents dirty reads and non-repeatable reads but still allows
phantom reads.
hu
4.Serializable: Highest isolation level, preventing all anomalies (dirty reads,
non-repeatable reads, and phantom reads).
rib
58. Difference between logical and physical data independence
● Logical Data Independence: The ability to change the logical schema (structure)
without affecting the external schema or application programs.
● Physical Data Independence: The ability to change the physical storage of data
without affecting the logical schema or application programs.
59. Explain parallel query processing
Parallel query processing involves executing a query using multiple processors or threads
in parallel, which can significantly reduce query execution time for large datasets.
● Uses:
○ Complex queries on large datasets (e.g., aggregation, joins).
○ Databases that support parallelism (e.g., Oracle, SQL Server).
60. Explain query hints
ita
Query hints are directives given by the user to influence the execution plan chosen by the
query optimizer. They can be used to force specific join methods, indexing strategies, or
even disable optimizations.
m
● Example: In SQL Server, the OPTION (FORCESEEK) hint forces the query
optimizer to use an index seek operation.
us
_s
hu
rib
📌 Scenario / Real-World Problems
61. Find customers who placed orders in the last 30 days
SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';
62. Find employees with higher salary than their manager
SELECT e.employee_id, e.name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;
63. Find top 3 selling products
SELECT product_id, SUM(quantity) AS total_sold
FROM order_items
ita
GROUP BY product_id
ORDER BY total_sold DESC
LIMIT 3;
m
64. Count total orders placed by each customer
us
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;
_s
65. List customers who haven’t placed any order
SELECT c.customer_id, c.name
hu
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;
rib
66. Find total revenue by month
SELECT DATE_TRUNC('month', order_date) AS month, SUM(total_amount)
AS revenue
FROM orders
GROUP BY month
ORDER BY month;
67. Retrieve continuous absent days for an employee
SELECT employee_id, MIN(absent_date) AS start_date, MAX(absent_date)
AS end_date
FROM (
SELECT employee_id, absent_date,
absent_date - INTERVAL '1 day' * ROW_NUMBER() OVER
(PARTITION BY employee_id ORDER BY absent_date) AS grp
FROM attendance
WHERE status = 'Absent'
) t
GROUP BY employee_id, grp;
ita
68. Show consecutive dates from a table
SELECT MIN(date) AS start_date, MAX(date) AS end_date
m
FROM (
SELECT date,
date - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY date)
us
AS grp
FROM dates_table
) t
GROUP BY grp;
_s
69. Detect and remove duplicates
hu
DELETE FROM products a
USING products b
WHERE a.product_id > b.product_id
AND a.product_name = b.product_name;
rib
70. Split comma-separated values into rows
(PostgreSQL syntax — other databases have similar)
SELECT customer_id, unnest(string_to_array(tags, ',')) AS tag
FROM customers;
71. Get cumulative salary
SELECT employee_id, salary,
SUM(salary) OVER (ORDER BY employee_id) AS cumulative_salary
FROM employees;
72. Find gaps in a sequence
SELECT (id + 1) AS missing_from
FROM numbers n
WHERE NOT EXISTS (SELECT 1 FROM numbers WHERE id = n.id + 1);
ita
73. Transposing rows to columns
(E.g. Pivoting sales data by region)
m
SELECT
product_id,
us
SUM(CASE WHEN region = 'North' THEN sales ELSE 0 END) AS
north_sales,
SUM(CASE WHEN region = 'South' THEN sales ELSE 0 END) AS
south_sales
_s
FROM sales
GROUP BY product_id;
hu
74. Group data in ranges (0-10, 11-20)
SELECT
FLOOR(score / 10) * 10 AS range_start,
rib
FLOOR(score / 10) * 10 + 9 AS range_end,
COUNT(*) AS total
FROM test_scores
GROUP BY range_start
ORDER BY range_start;
75. Fetch latest record per group
SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, order_date DESC;
(PostgreSQL — for other DBs, use ROW_NUMBER() approach)
76. Detect circular references in hierarchical data
WITH RECURSIVE hierarchy AS (
SELECT employee_id, manager_id, ARRAY[employee_id] AS path
FROM employees
WHERE manager_id IS NOT NULL
UNION ALL
ita
SELECT e.employee_id, e.manager_id, h.path || e.employee_id
FROM employees e
JOIN hierarchy h ON e.manager_id = h.employee_id
WHERE NOT e.employee_id = ANY(h.path)
m
)
SELECT * FROM hierarchy WHERE employee_id = ANY(path);
us
77. Update multiple rows conditionally
UPDATE employees
_s
SET salary = CASE
WHEN department = 'Sales' THEN salary * 1.1
WHEN department = 'IT' THEN salary * 1.2
ELSE salary
hu
END;
rib
78. Delete duplicates keeping only the latest
DELETE FROM orders a
USING orders b
WHERE a.customer_id = b.customer_id
AND a.order_date < b.order_date;
79. Search by multiple keywords
SELECT *
FROM products
WHERE product_name ILIKE '%keyword1%'
OR product_name ILIKE '%keyword2%';
80. Check if a string contains only numbers
SELECT column_name,
CASE WHEN column_name ~ '^\d+$' THEN 'Numeric' ELSE
'Non-numeric' END AS check_result
FROM your_table;
(PostgreSQL regex syntax — use ISNUMERIC() or REGEXP_LIKE() in other DBs)
ita
m
us
_s
hu
rib
📌 Theoretical Concepts
📌 81. Entity-Relationship (ER) Model
An ER model is a diagrammatic technique for designing relational databases, showing:
● Entities (objects — e.g., Employee, Department)
● Attributes (properties — e.g., Name, Salary)
● Relationships (associations — e.g., Employee works in Department)
Example:
● Employee (employee_id, name, salary)
● Department (dept_id, dept_name)
ita
● Relationship: Employee belongs to Department
📌 82. Difference Between Physical and Logical Database Models
m
Aspect Logical Model Physical Model
us
Focus What data is stored and How data is stored physically
relationships
Example Tables, Columns, Indexes, Partitions, File Groups
Relationships
_s
Technology-Depend No Yes
ent
Optimizations No (pure structure) Yes (performance tuning aspects
hu
Included included)
📌 83. What is a Database Cursor?
rib
A cursor is a database object used to retrieve, manipulate, and navigate through a result set
row by row.
Use cases:
● When row-by-row processing is necessary
● Complex iterative operations
Types:
● Implicit: Automatically handled
● Explicit: User-defined, manually opened/closed
📌 84. When to Use Stored Procedures Over Queries
Use stored procedures when:
● You need reusable, precompiled, parameterized blocks of SQL logic
ita
● Complex business logic with multiple statements
● Improved performance through precompilation
● Enhanced security (restrict direct table access)
m
📌 85. What is Indexing Overhead?
us
Indexing overhead refers to:
_s
● Additional disk space to store indexes
● Extra time during insert, update, delete operations because the index must be
maintained
hu
● Impact on write performance for heavily transactional systems
Trade-off: Faster reads vs. slower writes and extra storage
rib
📌 86. Pros and Cons of Denormalization
Pros Cons
Faster read queries (fewer Redundant data, possible
joins) inconsistency
Simplified query logic Higher storage usage
Better performance for OLAP Harder to maintain and update
📌 87. Benefits of ACID Properties
● Atomicity — All or none of a transaction executes
● Consistency — Data remains valid before and after transactions
● Isolation — Concurrent transactions don’t interfere
● Durability — Committed changes persist after system failures
Why important?
Guarantees reliability, data integrity, and fault tolerance in relational databases.
ita
📌 88. Differences Between Procedural and Non-Procedural SQL
m
Procedural SQL Non-Procedural SQL
Tells how to do it (step-by-step logic) Tells what to do (result oriented)
us
Example: PL/SQL, T-SQL Example: SQL SELECT statements
Uses loops, conditions, variables Uses declarative set-based operations
More control, but verbose Simpler, but less control over execution
_s
📌 89. SQL vs NoSQL
hu
SQL (Relational) NoSQL (Non-relational)
Structured data, fixed schema Unstructured, flexible schema
Tables, Rows, Columns Key-Value, Document, Graph, Column-family
rib
ACID transactions supported BASE (Basically Available, Soft state, Eventual
consistency)
Examples: MySQL, Examples: MongoDB, Cassandra, Redis
PostgreSQL
Vertical scaling (scale-up) Horizontal scaling (scale-out)
📌 90. Importance of Data Types
● Ensure data integrity and prevent invalid entries
● Help optimize storage and memory usage
● Allow correct data processing (e.g., math on numbers, sorting strings)
● Improves query performance by avoiding implicit conversions
Example: Use DATE for dates instead of VARCHAR — allows date functions, sorting, and
indexing.
ita
m
us
_s
hu
rib
📌 Tools / Features / Best Practices
📌 91. Using EXPLAIN or QUERY PLAN
● EXPLAIN or QUERY PLAN shows how the database engine will execute a SQL
query.
● It reveals:
○ Join order
○ Index usage
○ Full table scans
○ Cost estimates
● Purpose: Diagnose slow queries, identify bottlenecks, optimize performance.
Example (PostgreSQL):
ita
EXPLAIN SELECT * FROM orders WHERE customer_id = 101;
📌 92. Using Transactions in Batch Operations
m
● Transactions group multiple operations into a single unit.
us
● Ensures Atomicity — either all operations succeed, or none.
● Important for batch updates/inserts to maintain data consistency.
_s
Example:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
hu
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
📌 93. Importance of Foreign Key Constraints
rib
● Maintains referential integrity between related tables.
● Prevents insertion of invalid data or deletion of referenced records.
● Automates cascading actions like ON DELETE CASCADE or ON UPDATE
CASCADE.
Example:
FOREIGN KEY (dept_id) REFERENCES department(dept_id)
📌 94. When to Create Indexes
● Create indexes on:
○ Columns frequently used in WHERE, JOIN, ORDER BY, GROUP BY
○ Foreign key columns
○ Large tables queried often
ita
Caution:
● Avoid over-indexing (affects write performance)
m
● Index selectivity matters — high uniqueness → better index performance
us
📌 95. Avoiding Full Table Scans
● Use indexes on filtering columns.
_s
● Avoid using functions or operations on indexed columns (e.g., WHERE UPPER(name)
= 'JOHN')
hu
● Use proper WHERE clauses.
● Avoid SELECT * if not needed.
rib
● Keep statistics updated so the query planner can optimize better.
📌 96. Difference Between Inner Queries and CTEs
Aspect Inner Queries (Subqueries) CTEs (Common Table Expressions)
Scope Local to the outer query Can be referenced multiple times
Readabilit Can become nested/complex Cleaner, modular, easy to debug
y
Reusabilit Not reusable Reusable within the same query
y
Example (SELECT MAX(salary) WITH max_salary AS (SELECT
FROM employees) MAX(salary) FROM employees)
📌 97. When to Use Temporary Tables
● Storing intermediate results in multi-step operations.
● When working with large, complex queries or batch processing.
ita
● Isolate intermediate calculations or data manipulations.
Example:
m
CREATE TEMPORARY TABLE temp_orders AS
SELECT * FROM orders WHERE order_date > '2024-01-01';
us
📌 98. Best Practices for Schema Design
_s
● Use proper data types.
● Normalize data to avoid redundancy.
hu
● Define primary and foreign keys.
● Name tables and columns clearly and consistently.
● Use indexes wisely.
rib
● Keep security and access control in mind.
● Plan for scalability (partitioning, sharding if needed).
📌 99. Parameterizing SQL Queries (to Prevent SQL Injection)
● Use prepared statements or parameterized queries to safely insert user input.
● Avoid concatenating user input directly into SQL strings.
Example (PostgreSQL, Python):
cur.execute("SELECT * FROM users WHERE username = %s", (username,))
✅ Prevents malicious input like ' OR '1'='1
📌 100. Backup and Recovery Strategies
ita
● Regularly schedule full, differential, and transaction log backups.
● Store backups offsite and securely.
m
● Periodically test restore operations.
● Use replication or clustering for high availability.
us
● Maintain disaster recovery plans for emergencies.
_s
hu
rib
