• CHAR stores fixed-length data and pads extra spaces.
• VARCHAR2 stores variable-length data, saving storage space.

A view is a virtual table created by a SELECT query. It does not store data itself, but presents data from one or more tables in a structured way. Views simplify complex queries, improve readability, and enhance security by restricting access to specific rows or columns.

The UNIQUE constraint ensures that all values in a column (or combination of columns) are distinct. This prevents duplicate values and helps maintain data integrity.

A composite primary key uses two or more columns together to uniquely identify each row when one column alone isn’t sufficient.

WHERE

HAVING

WHERE filters individual rows before grouping or aggregation, so it can’t use aggregate functions like SUM or COUNT; it’s best for narrowing raw data early (e.g., a date range or status).

WHERE

SUM

COUNT

HAVING filters the resulting groups after GROUP BY, so it’s meant for conditions on aggregates (e.g., groups with totals above a threshold).

HAVING

GROUP BY

Example

SELECT customer_id, COUNT(*) AS orders_2025FROM ordersWHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'      GROUP BY customer_idHAVING COUNT(*) > 5;                                            

SQL joins combine rows from two tables based on a matching condition (typically keys) to answer questions that span both tables.

• An INNER JOIN returns only matches that exist in both tables (the intersection).
• A LEFT JOIN returns all rows from the left table and the matching rows from the right; when there’s no match, right-side columns are NULL.
• A RIGHT JOIN is the mirror image: all rows from the right table plus matches from the left, NULL when absent.
• A FULL (OUTER) JOIN returns all rows from either table, filling in NULL where a counterpart is missing.

NULL.

NULL

NULL

PRIMARY KEY

UNIQUE

A PRIMARY KEY uniquely identifies each row in a table: it combines UNIQUE + NOT NULL, there can be only one per table (though it can be composite across multiple columns), and it’s the default target for foreign keys.

A UNIQUE key also enforces uniqueness, but doesn’t require NOT NULL and you can have many UNIQUE constraints per table.

A CTE (Common Table Expression) is a temporary, named result set defined with WITH that exists only for the duration of a single statement. You use CTEs to break complex logic into steps, avoid repeating the same subquery, improve readability/maintenance, enable recursion (e.g., org charts, folder trees), and make debugging easier.

WITH

Normalization organizes relational data to minimize redundancy and prevent update/insert/delete anomalies by splitting tables based on dependencies while preserving meaning.

• 1NF requires each column to hold atomic values and no repeating groups.
• 2NF is 1NF plus no partial dependence on a composite key—every non-key must depend on the whole key.
• 3NF is 2NF plus no transitive dependencies—non-keys depend only on the key, not on other non-keys.
• BCNF strengthens 3NF: for every functional dependency X→Y, X must be a candidate key (fixes edge cases like overlapping keys)
• 4NF removes multi-valued dependencies .
• 5NF (PJNF) eliminates join dependencies, decomposing so tables can be losslessly recombined without spurious tuple.

UNION

UNION ALL

UNION combines results from two (or more) SELECTs and removes duplicates (it performs a DISTINCT across all columns), which adds sorting/hash work and can be slower.

UNION

UNION ALL keeps duplicates and usually runs faster because it simply appends result sets.

UNION ALL

Example:

SELECT Name FROM Customers  UNION  SELECT Name FROM Employees

Example:

SELECT Name FROM Customers  UNION ALL  SELECT Name FROM Employees;

A clustered index stores table rows in the physical order of the index key (the data pages are the index), so you can have only one; it’s ideal for range scans and primary-key lookups.

• Key is narrow, stable, and increasing (e.g., order_id, order_date).
• Queries often do range scans or ORDER BY on that key.

order_id

order_date

A non-clustered index is a separate structure (key → row locator) and you can have many; it accelerates specific filters, joins, and sorts without changing the table’s row order.

• You need fast point lookups or high-selectivity filters (WHERE email=?).
• Columns are common JOIN/GROUP BY/ORDER BY targets.
• You can cover queries via INCLUDEd columns.

WHERE email=?

INCLUDE

SQL supports pattern matching mainly with LIKE (and NOT LIKE) using wildcards—% for any-length string and _ for a single character.

LIKE

NOT LIKE

%

_

PostgreSQL has case-insensitive ILIKE, SIMILAR TO, and regex operators (~, ~*), MySQL/SQLite offer REGEXP/REGEXP_LIKE, and all allow an optional ESCAPE clause to treat % or _ as literals.

ILIKE

SIMILAR TO

~

~*

REGEXP

REGEXP_LIKE

ESCAPE

%

_

Use a window (analytic) function: compute SUM(amount) over rows of the same product, ordered by time, accumulating from the start up to the current row. This keeps row detail while adding a running total.

SUM(amount)

SELECT  product_id,  sale_date,  amount,  SUM(amount) OVER (    PARTITION BY product_id    ORDER BY sale_date, sale_id            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  ) AS running_totalFROM sales;

A correlated subquery is a subquery that depends on the current row of the outer query—i.e., it references columns from the outer query and is re-evaluated for each outer row.

Example use case employees paid above their own department’s average:

SELECT e.employee_id, e.name, e.salary, e.department_idFROM employees eWHERE e.salary >      (SELECT AVG(e2.salary)       FROM employees e2       WHERE e2.department_id = e.department_id);

EXISTS checks whether a correlated subquery returns at least one row; NOT EXISTS checks that it returns none. They return boolean and stop at the first match, ignoring what the subquery selects. IN compares a value against a list/set (literal list or subquery output).

EXISTS

NOT EXISTS

IN

• EXISTS/NOT EXISTS use a correlated subquery and are robust with NULLs
• NOT IN is NULL-sensitive if the subquery can yield NULL, NOT IN may return no rows (unknown comparison), so prefer NOT EXISTS
• Performance varies EXISTS often wins for large subqueries with indexes; IN is fine for small, deduplicated lists.

EXISTS/NOT EXISTS

NULL

NOT IN

NULL

NOT IN

NOT EXISTS

EXISTS

IN

An anti-join returns rows from one table (the “left”) that have no matching rows in another table (the “right”) based on a join condition—i.e., “in A but not in B.” It’s commonly expressed as a LEFT JOIN followed by WHERE right.pk IS NULL, or with a semi-join style predicate like NOT EXISTS.

LEFT JOIN

WHERE right.pk IS NULL

NOT EXISTS

ROW_NUMBER() assigns a unique sequential number to each row within a partition based on the order—no ties share a number (ties are broken arbitrarily by the ORDER BY). RANK() assigns the same rank to tied rows but leaves gaps after ties (1,1,3…). DENSE_RANK() also assigns the same rank to ties but doesn’t leave gaps (1,1,2…).

ROW_NUMBER()

RANK()

DENSE_RANK()

LAG and LEAD are window functions that let you look at values from previous (LAG) or next (LEAD) rows in the same result set without self-joins. They’re used for comparisons across rows e.g., changes from yesterday to today, detecting trends, or filling forward/backward values

LAG

LEAD

LAG

LEAD

A CROSS JOIN returns the cartesian product of two tables—every row from A paired with every row from B so the result size is rows(A) × rows(B), and it doesn’t use a join condition

rows(A) × rows(B)

An INNER JOIN returns only the rows where the specified join condition matches between the two tables (e.g., matching keys), so its result is a filtered subset, not every combination

• Use a cross join for generating combinations (dates × products, sizes × colors) .
• When you intentionally need all pairings; use an inner join to combine related records.

A foreign key (FK) is a column (or set of columns) in a child table that references a primary/unique key in a parent table to ensure the child’s values actually exist in the parent. This enforces referential integrity by preventing actions that would create “orphan” rows.

UNION, INTERSECT, and EXCEPT are SQL set operations that combine results from two queries with the same number of columns and compatible data types. UNION returns the distinct union of both result sets (removes duplicates).

UNION

INTERSECT

EXCEPT

• Use it to merge similar data from multiple sources
• INTERSECT returns only rows common to both queries—ideal for finding overlap.
• EXCEPT (called MINUS in Oracle) returns rows in the first query that aren’t in the second.

MINUS

To optimize a slow query,

• First measure: reproduce the issue, capture timings, and run EXPLAIN/EXPLAIN ANALYZE to see the plan, row estimates, and bottlenecks
• Next, fix fundamentals: ensure current statistics, right indexes
• sargable predicates (avoid functions on columns, leading % wildcards, or expressions that prevent
• Reduce data early with selective WHERE filters, fetch only needed columns (no SELECT *), and prefer EXISTS over IN for semi-joins
• Tame row explosion by checking JOIN selectivity, de-duplicating before joins, and pre-aggregating where helpful.
• Rewrite problematic patterns (split wide ORs into UNION ALL, replace correlated subqueries with joins, consider window functions carefully)
• For large sets, use keyset pagination (seek method) instead of OFFSET, and consider materialized views, caching, or partitioning for heavy, recurring analytics.

EXPLAIN/EXPLAIN ANALYZE

%

WHERE

SELECT *

EXISTS

IN

OR

UNION ALL

OFFSET

A query is a SQL statement used to retrieve, update, or manipulate data in a database. The most common type of query is a SELECT statement, which fetches data from one or more tables based on specified conditions.

A subquery is a query nested within another query. It is often used in the WHERE clause to filter data based on the results of another query, making it easier to handle complex conditions.

Database partitioning is the practice of splitting a large table (and its indexes) into smaller, more manageable pieces called partitions while keeping it logically a single table.This improves query performance (partition pruning scans only relevant partitions), eases maintenance (backup/reindex/archiving per partition), and enhances availability.

The primary defense against SQL injection is to use parameterized queries (prepared statements) everywhere never build SQL with string concatenation. Combine this with allow-list input validation (e.g., only digits for IDs), least-privilege DB accounts (no DROP, limited schema access), and safe stored procedures that don’t assemble dynamic SQL.

DROP

SQL commands are broadly classified into:

• 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.

The DEFAULT constraint assigns a default value to a column when no value is provided during an INSERT operation. This helps maintain consistent data and simplifies data entry.

Denormalization is the process of combining normalized tables into larger tables for performance reasons. It is used when complex queries and joins slow down data retrieval, and the performance benefits outweigh the drawbacks of redundancy.

• Arithmetic Operators: +, -, *, /, %
• Comparison Operators: =, !=, <>, >, <, >=, ⇐
• Logical Operators: AND, OR, NOT
• Set Operators: UNION, INTERSECT, EXCEPT
• Special Operators: BETWEEN, IN, LIKE, IS NULL
• Concatenation Operators:|| (Oracle, PostgreSQL) or + (SQL Server) to combine strings.

||

+

• INNER JOIN: Returns rows that have matching values in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table, and matching rows from the right table.
• RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table, and matching rows from the left table.
• FULL JOIN (FULL OUTER JOIN): Returns all rows when there is a match in either table.
• CROSS JOIN: Produces the Cartesian product of two tables.
• SELF JOIN: A table joined with itself, often used for hierarchical data.

The GROUP BY clause is used to arrange identical data into groups. It is typically used with aggregate functions (such as COUNT, SUM, AVG) to perform calculations on each group rather than on the entire dataset.

Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include:

• COUNT(): Returns the number of rows.
• SUM(): Returns the total sum of values.
• AVG(): Returns the average of values.
• MIN(): Returns the smallest value.
• MAX(): Returns the largest value.

Indexes are database objects that improve query performance by allowing faster retrieval of rows. They function like a book’s index, making it quicker to find specific data without scanning the entire table. However, indexes require additional storage and can slightly slow down data modification operations. Types of Indexes:

• Clustered Index: Sorts and stores data rows in order of the key (only one per table).
• Non-Clustered Index: Separate structure with pointers to data rows (can be many per table).
• Unique Index: Ensures no duplicate values.
• Composite Index: Index on multiple columns.

TRUNCATE is a DDL command, while DELETE is a DML command, which is why they differ in speed and logging behavior.

• DELETE: Removes rows one at a time and records each deletion in the transaction log, allowing rollback. It can have a WHERE clause.
• TRUNCATE: Removes all rows at once without logging individual row deletions. It cannot have a WHERE clause and is faster than DELETE for large data sets.

SQL is best for structured, reliable transactions, while NoSQL shines in handling massive, fast-changing, and unstructured data.

SQL Databases:

• Use structured tables with rows and columns.
• Rely on a fixed schema.
• Offer ACID properties.

NoSQL Databases:

• Use flexible, schema-less structures (e.g., key-value pairs, document stores).
• Are designed for horizontal scaling.
• Often focus on performance and scalability over strict consistency.

Common constraints include:

• NOT NULL: Ensures a column cannot have NULL values.
• UNIQUE: Ensures all values in a column are distinct.
• PRIMARY KEY: Uniquely identifies each row in a table.
• FOREIGN KEY: Ensures referential integrity by linking to a primary key in another table.
• CHECK: Ensures that all values in a column satisfy a specific condition.
• DEFAULT: Sets a default value for a column when no value is specified.

A cursor is a database object used to retrieve, manipulate, and traverse through rows in a result set one row at a time. Cursors are helpful when performing operations that must be processed sequentially rather than in a set-based manner.

Types of Cursors (SQL Server):

• STATIC: Snapshot of result set, does not reflect changes.
• DYNAMIC: Reflects all changes made while the cursor is open.
• FORWARD_ONLY: Can only move forward through the result set.
• KEYSET: Uses a key to fetch rows; changes to non-key columns are visible.

A trigger is a set of SQL statements that automatically execute in response to certain events on a table, such as INSERT, UPDATE, or DELETE. Triggers help maintain data consistency, enforce business rules, and implement complex integrity constraints.

• Types: BEFORE or AFTER triggers (depending on DB).
• Uses: Enforce business rules, maintain audit logs, check data consistency.

The SELECT statement retrieves data from one or more tables. It is the most commonly used command in SQL, allowing users to filter, sort, and display data based on specific criteria.

ORDER BY sorts the result set of a query in ascending or descending order based on one or more columns.

A table is a structured collection of related data organized into rows and columns. Columns define the type of data stored, while rows contain individual records.

NULL represents a missing or unknown value. It is different from zero or an empty string. NULL values indicate that the data is not available or applicable.

A stored procedure is a precompiled set of SQL statements stored in the database. It can take input parameters, perform logic and queries, and return output values or result sets. Stored procedures improve performance and maintainability by centralizing business logic.

1. DDL (Data Definition Language):

These commands are used to define and modify the structure of database objects such as tables, indexes, and views. For example, the CREATE command creates a new table, the ALTER command modifies an existing table, and the DROP command removes a table entirely. DDL commands primarily focus on the schema or structure of the database.

Example:

CREATE TABLE Employees (    ID INT PRIMARY KEY,    Name VARCHAR(50));

2. DML (Data Manipulation Language):

These commands deal with the actual data stored within database objects. For instance, the INSERT command adds rows of data to a table, the UPDATE command modifies existing data, and the DELETE command removes rows from a table. In short, DML commands allow you to query and manipulate the data itself rather than the structure.

Example:

INSERT INTO Employees (ID, Name) VALUES (1, 'Alice');

The ALTER command is used to modify the structure of an existing database object. This command is essential for adapting our database schema as requirements evolve.

• Add or drop a column in a table.
• Change a column’s data type.
• Add or remove constraints.
• Rename columns or tables.
• Adjust indexing or storage settings.

Data integrity refers to the accuracy, consistency, and reliability of the data stored in the database. SQL databases maintain data integrity through several mechanisms:

• Constraints: Ensuring that certain conditions are always met. For example, NOT NULL ensures a column cannot have missing values, FOREIGN KEY ensures a valid relationship between tables, and UNIQUE ensures no duplicate values.
• Transactions: Ensuring that a series of operations either all succeed or all fail, preserving data consistency.
• Triggers: Automatically enforcing rules or validations before or after changes to data.
• Normalization: Organizing data into multiple related tables to minimize redundancy and prevent anomalies.
• Cascading actions: Foreign keys can enforce integrity with ON DELETE CASCADE and ON UPDATE CASCADE, automatically updating or removing related rows.
• These measures collectively ensure that the data remains reliable and meaningful over time.

NOT NULL

FOREIGN KEY

UNIQUE

ON DELETE CASCADE

ON UPDATE CASCADE

The CASE statement is SQL’s way of implementing conditional logic in queries. It evaluates conditions and returns a value based on the first condition that evaluates to true. If no condition is met, it can return a default value using the ELSE clause.

Example:

SELECT ID,         CASE             WHEN Salary > 100000 THEN 'High'             WHEN Salary BETWEEN 50000 AND 100000 THEN 'Medium'             ELSE 'Low'         END AS SalaryLevel  FROM Employees;

The COALESCE function returns the first non-NULL value from a list of expressions. It’s commonly used to provide default values or handle missing data gracefully.

Example:

SELECT COALESCE(NULL, NULL, 'Default Value') AS Result;

1. COUNT(): Counts the number of rows or non-NULL values in a column.

Example:

SELECT COUNT(*) FROM Orders;

2. SUM(): Adds up all numeric values in a column.

Example:

SELECT SUM(TotalAmount) FROM Orders;

• NVL() replaces a NULL value with a specified replacement value (e.g., NVL(Salary, 0)).
• NVL2() returns one value if the expression is NOT NULL and another value if it is NULL.

NULL

NVL(Salary, 0)

Example:

SELECT NVL(Salary, 0) AS AdjustedSalary FROM Employees;  -- Replaces NULL with 0SELECT NVL2(Salary, Salary, 0) AS AdjustedSalary FROM Employees;  -- If Salary is NULL, returns 0; otherwise, returns Salary.

If two employees have the same salary, they get the same rank, but RANK() will skip a number for the next rank, while DENSE_RANK() will not.

RANK()

DENSE_RANK()

Scalar functions operate on individual values and return a single value as a result. They are often used for formatting or converting data. Common examples include:

• LEN(): Returns the length of a string.
• ROUND(): Rounds a numeric value.
• CONVERT(): Converts a value from one data type to another.

• COUNT(column) ignores NULL values and only counts non-NULL entries.
• COUNT(*) counts all rows, including those with NULL values in columns.

COUNT(column)

NULL

COUNT(*)

NULL

Example:

SELECT Name, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum  FROM Employees;

Window functions allow you to perform calculations across a set of table rows that are related to the current row within a result set, without collapsing the result set into a single row. These functions can be used to compute running totals, moving averages, rank rows, etc.

SELECT Name, Salary,     SUM(Salary) OVER (ORDER BY Salary) AS RunningTotal  FROM Employees;

1. Index

• An index is a database object created to speed up data retrieval. It stores a sorted reference to table data, which helps the database engine find rows more quickly than scanning the entire table.
• Example: A non-unique index on a column like LastName allows quick lookups of rows where the last name matches a specific value.

2. Key

• A key is a logical concept that enforces rules for uniqueness or relationships in the data.
• For instance, a PRIMARY KEY uniquely identifies each row in a table and ensures that no duplicate or NULL values exist in the key column(s).
• A FOREIGN KEY maintains referential integrity by linking rows in one table to rows in another.

Indexing allows the database to locate and access the rows corresponding to a query condition much faster than scanning the entire table. Instead of reading each row sequentially, the database uses the index to jump directly to the relevant data pages. This reduces the number of disk I/O operations and speeds up query execution, especially for large tables.

Example:

CREATE [[index|INDEX]] idx_lastname ON Employees(LastName);SELECT * FROM Employees WHERE LastName = 'Smith';

The index on LastName lets the database quickly find all rows matching ‘Smith’ without scanning every record.

LastName

Advantages

• Faster query performance, especially for SELECT queries with WHERE clauses, JOIN conditions, or ORDER BY clauses.
• Improved sorting and filtering efficiency.

Disadvantages:

• Increased storage space for the index structures.
• Additional overhead for write operations (INSERT, UPDATE, DELETE), as indexes must be updated whenever the underlying data changes.
• Potentially slower bulk data loads or batch inserts due to the need to maintain index integrity.In short, indexes make read operations faster but can slow down write operations and increase storage requirements.

Temporary tables are tables that exist only for the duration of a session or a transaction. They are useful for storing intermediate results, simplifying complex queries, or performing operations on subsets of data without modifying the main tables.

1. Local Temporary Tables:

• Prefixed with # (e.g., TempTable).
• Only visible to the session that created them.
• Automatically dropped when the session ends.

#

#TempTable

2. Global Temporary Tables:

• Prefixed with (e.g., GlobalTempTable).
• Visible to all sessions.
• Dropped when all sessions that reference them are closed.

##

##GlobalTempTable

Example:

CREATE TABLE #TempResults (ID INT, Value VARCHAR(50));INSERT INTO #TempResults VALUES (1, '[[test|Test]]');SELECT * FROM #TempResults;

Standard View:

• A virtual table defined by a query.
• Does not store data; the underlying query is executed each time the view is referenced.
• A standard view shows real-time data.

Materialized View:

• A physical table that stores the result of the query.
• Data is precomputed and stored, making reads faster.
• Requires periodic refreshes to keep data up to date.
• materialized view is used to store aggregated sales data, updated nightly, for fast reporting.

In Oracle/Postgres, you use:

REFRESH MATERIALIZED VIEW my_view;

• They can also be set up with ON DEMAND or ON COMMIT refresh policies depending on DBMS.

A sequence is a database object that generates a series of unique numeric values. It’s often used to produce unique identifiers for primary keys or other columns requiring sequential values.

Example:

CREATE SEQUENCE seq_emp_id START WITH 1 INCREMENT BY 1;SELECT NEXT VALUE FOR seq_emp_id; -- Returns 1SELECT NEXT VALUE FOR seq_emp_id; -- Returns 2

1. Greater Flexibility:

• Can specify start values, increments, and maximum values.
• Can be easily reused for multiple tables.
• Sequences are independent objects that generate numbers.

2. Dynamic Adjustment: Can alter the sequence without modifying the table structure.

3. Cross-Table Consistency: Use a single sequence for multiple related tables to ensure unique identifiers across them.In short, sequences offer more control and reusability than identity columns.

Constraints enforce rules that the data must follow, preventing invalid or inconsistent data from being entered:

• NOT NULL: Ensures that a column cannot contain NULL values.
• UNIQUE: Ensures that all values in a column are distinct.
• PRIMARY KEY: Combines NOT NULL and UNIQUE, guaranteeing that each row is uniquely identifiable.
• FOREIGN KEY: Ensures referential integrity by requiring values in one table to match primary key values in another.
• CHECK: Validates that values meet specific criteria (e.g., CHECK (Salary > 0)).By automatically enforcing these rules, constraints maintain data reliability and consistency.

CHECK (Salary > 0)

Local Temporary Table:

• Prefixed with # (e.g., TempTable).
• Exists only within the session that created it.
• Automatically dropped when the session ends.

#

#TempTable

Global Temporary Table:

• Prefixed with (e.g., GlobalTempTable).
• Visible to all sessions.
• Dropped only when all sessions referencing it are closed.

Example:

CREATE TABLE #LocalTemp (ID INT);CREATE TABLE ##GlobalTemp (ID INT);

The MERGE statement combines multiple operations INSERT, UPDATE, and DELETE into one. It is used to synchronize two tables by:

• Inserting rows that don’t exist in the target table.
• Updating rows that already exist.
• Deleting rows from the target table based on conditions
• In SQL Server, MERGE can cause concurrency issues (race conditions or deadlocks).

Example:

MERGE INTO TargetTable T  USING SourceTable S  ON T.ID = S.ID  WHEN MATCHED THEN      UPDATE SET T.Value = S.Value  WHEN NOT MATCHED THEN      INSERT (ID, Value) VALUES (S.ID, S.Value);

1. GROUP BY: Aggregate rows to eliminate duplicates

SELECT Column1, MAX(Column2)  FROM TableName  GROUP BY Column1;

2. ROW_NUMBER(): Assign a unique number to each row and filter by that

WITH CTE AS (      SELECT Column1, Column2, ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column2) AS RowNum      FROM TableName  )  SELECT * FROM CTE WHERE RowNum = 1;

ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability, four key properties that ensure database transactions are processed reliably.

1. Atomicity:

• A transaction is treated as a single unit of work, meaning all operations must succeed or fail as a whole.
• If any part of the transaction fails, the entire transaction is rolled back.
• Example: In banking, transferring money must debit one account and credit another; if one fails, both roll back.

2. Consistency:

• A transaction must take the database from one valid state to another, maintaining all defined rules and constraints.
• This ensures data integrity is preserved throughout the transaction process.
• Example: Inventory count cannot drop below zero.

3. Isolation:

• Transactions should not interfere with each other.
• Even if multiple transactions occur simultaneously, each must operate as if it were the only one in the system until it is complete.
• Example: Two people booking the same seat won’t overwrite each other’s data.

4. Durability:

• Once a transaction is committed, its changes must persist, even in the event of a system failure.
• This ensures the data remains stable after the transaction is successfully completed.
• Example: A confirmed order stays in the system despite a server restart.

Isolation levels define the extent to which the operations in one transaction are isolated from those in other transactions. They are critical for managing concurrency and ensuring data integrity. Common isolation levels include:

1. Read Uncommitted:

• Allows reading uncommitted changes from other transactions.
• Can result in dirty reads, where a transaction reads data that might later be rolled back.

2. Read Committed:

• Ensures a transaction can only read committed data.
• Prevents dirty reads but does not protect against non-repeatable reads or phantom reads.

3. Repeatable Read:

• Ensures that if a transaction reads a row, that row cannot change until the transaction is complete.
• Prevents dirty reads and non-repeatable reads but not phantom reads.

4. Serializable:

• The highest level of isolation.
• Ensures full isolation by effectively serializing transactions, meaning no other transaction can read or modify data that another transaction is using.
• Prevents dirty reads, non-repeatable reads, and phantom reads, but may introduce performance overhead due to locking and reduced concurrency.

• The WITH (NOLOCK) hint allows a query to read data without acquiring shared locks, effectively reading uncommitted data.
• It can improve performance by reducing contention for locks, especially on large tables that are frequently updated.
• Results may be inconsistent or unreliable, as the data read might change or be rolled back.

Example:

SELECT *  FROM Orders WITH (NOLOCK);This query fetches data from the Orders table without waiting for other transactions to release their locks.

Deadlocks occur when two or more transactions hold resources that the other transactions need, resulting in a cycle of dependency that prevents progress. Strategies to handle deadlocks include:

1. Deadlock detection and retry:

• Many database systems have mechanisms to detect deadlocks and terminate one of the transactions to break the cycle.
• The terminated transaction can be retried after the other transactions complete.

2. Reducing lock contention:

• Use indexes and optimized queries to minimize the duration and scope of locks.
• Break transactions into smaller steps to reduce the likelihood of conflicts.

3. Using proper isolation levels:

• In some cases, lower isolation levels can help reduce locking.
• Conversely, higher isolation levels (like Serializable) may ensure a predictable order of operations, reducing deadlock risk.

4. Consistent ordering of resource access:

• Ensure that transactions acquire resources in the same order to prevent cyclical dependencies.

A database snapshot is a read-only, static view of a database at a specific point in time.

• Reporting: Allowing users to query a consistent dataset without affecting live operations.
• Backup and recovery: Snapshots can serve as a point-in-time recovery source if changes need to be reversed.
• Testing: Providing a stable dataset for testing purposes without the risk of modifying the original data.

Example:

CREATE DATABASE MySnapshot ON  (      NAME = MyDatabase_Data,      FILENAME = 'C:\Snapshots\MyDatabase_Snapshot.ss'  )  AS SNAPSHOT OF MyDatabase;

1. OLTP (Online Transaction Processing)

• Handles large volumes of simple transactions (e.g., order entry, inventory updates).
• Optimized for fast, frequent reads and writes.
• Normalized schema to ensure data integrity and consistency.
• Examples: e-commerce sites, banking systems.

2. OLAP (Online Analytical Processing)

• Handles complex queries and analysis on large datasets.
• Optimized for read-heavy workloads and data aggregation.
• Denormalized schema (e.g., star or snowflake schemas) to support faster querying.
• Examples: Business intelligence reporting, data warehousing.

1. Live Lock

• Occurs when two or more transactions keep responding to each other’s changes, but no progress is made.
• Unlike a deadlock, the transactions are not blocked; they are actively running, but they cannot complete.

2. Deadlock

• A deadlock occurs when two or more transactions are waiting on each other’s resources indefinitely, blocking all progress.
• No progress can be made unless one of the transactions is terminated

The EXCEPT operator is used to return rows from one query’s result set that are not present in another query’s result set. It effectively performs a set difference, showing only the data that is unique to the first query.

Example:

SELECT ProductID FROM ProductsSoldEXCEPTSELECT ProductID FROM ProductsReturned;

Use Case:

• To find discrepancies between datasets.
• To verify that certain data exists in one dataset but not in another.

Performance Considerations:

• EXCEPT works best when the datasets involved have appropriate indexing and when the result sets are relatively small.
• Large datasets without indexes may cause slower performance because the database has to compare each row.

Dynamic SQL is SQL code that is constructed and executed at runtime rather than being fully defined and static. In SQL Server: Use sp_executesql or EXEC. In other databases: Concatenate query strings and execute them using the respective command for the database platform.

Syntax:

DECLARE @sql NVARCHAR(MAX)SET @sql = 'SELECT * FROM ' + @TableNameEXEC sp_executesql @sql;

Advantages:

• Flexibility: Dynamic SQL can adapt to different conditions, tables, or columns that are only known at runtime.
• Simplifies Complex Logic: Instead of writing multiple queries, a single dynamically constructed query can handle multiple scenarios.

Risks:

• SQL Injection Vulnerabilities: If user input is not sanitized, attackers can inject malicious SQL code.
• Performance Overhead: Because dynamic SQL is constructed at runtime, it may not benefit from cached execution plans, leading to slower performance.
• Complexity in Debugging: Dynamic queries can be harder to read and troubleshoot.

Partitioning is a database technique used to divide data into smaller, more manageable pieces.

Horizontal Partitioning:

• Divides the rows of a table into multiple partitions based on values in a specific column.
• Example: Splitting a customer table into separate partitions by geographic region or by year.
• Use Case: When dealing with large datasets, horizontal partitioning can improve performance by limiting the number of rows scanned for a query.

Vertical Partitioning:

• Divides the columns of a table into multiple partitions.
• Example: Storing infrequently accessed columns (e.g., large text or binary fields) in a separate table or partition.
• Use Case: Helps in optimizing storage and query performance by separating commonly used columns from less frequently accessed data.

Key Difference:

• Horizontal partitioning is row-based, focusing on distributing the dataset’s rows across partitions.
• Vertical partitioning is column-based, aiming to separate less-used columns into different partitions or tables.

1. Indexing Strategy:

• Focus on the most frequently queried columns or those involved in JOIN and WHERE conditions.
• Avoid indexing every column, as it increases storage and maintenance costs.

2. Index Types:

• Use clustered indexes for primary key lookups and range queries.
• Use non-clustered indexes for filtering, ordering, and covering specific queries.

3. Partitioned Indexes:

• If the table is partitioned, consider creating local indexes for each partition. This improves manageability and can speed up queries targeting specific partitions.

4. Maintenance Overhead:

• Index rebuilding and updating can be resource-intensive. Plan for regular index maintenance during off-peak hours.
• Monitor index fragmentation and rebuild indexes as necessary to maintain performance.

5. Monitoring and Tuning:

• Continuously evaluate query performance using execution plans and statistics.
• Remove unused or rarely accessed indexes to reduce maintenance costs.

1. Sharding

• Sharding involves splitting a database into multiple smaller, independent databases (shards). Each shard operates on a subset of the overall data and can be hosted on separate servers.
• Sharding is a horizontal scaling strategy that distributes data across multiple databases, typically to handle massive data volumes and high traffic.
• Purpose: Horizontal scaling to handle large volumes of data and high query loads.
• Example: A global user database might be divided into shards by region, such as a shard for North America, Europe, and Asia.
• Key Benefit: Each shard can be queried independently, reducing the load on any single server.

2. Partitioning

• Partitioning splits a single table into smaller, logical pieces, usually within the same database.
• Partitioning is a logical organization of data within a single database to optimize performance and manageability.
• Purpose: Improve query performance by reducing the amount of data scanned, and simplify maintenance tasks such as archiving or purging old data.
• Example: A sales table could be partitioned by year so that queries targeting recent sales do not need to scan historical data.

WITH RecursiveCTE (ID, ParentID, Depth) AS (      SELECT ID, ParentID, 1 AS Depth      FROM Categories      WHERE ParentID IS NULL      UNION ALL      SELECT c.ID, c.ParentID, r.Depth + 1      FROM Categories c      INNER JOIN RecursiveCTE r      ON c.ParentID = r.ID  )  SELECT * FROM RecursiveCTE;

SELECT ProductID, [2021], [2022]  FROM (      SELECT ProductID, YEAR(SaleDate) AS SaleYear, Amount      FROM Sales  ) AS Source  PIVOT (      SUM(Amount)      FOR SaleYear IN ([2021], [2022])  ) AS PivotTable;

86: Delete duplicate data from table only first data remains constant.

Managers -

Query:

DELETE M1 From managers M1, managers M2 Where M2.Name = M1.Name AND M1.Id>M2.Id; 

86 : Find the employee name using COALESCE() when First_Name, Second_Name, or Last_Name may contain NULL values.

Employee Table:

Query:

SELECT ID, COALESCE(FName, SName, LName) as Name FROM employees; 

87: Find employees hired in the last n months using the TIMESTAMPDIFF() function.

TIMESTAMPDIFF()

Query -

Select *, TIMESTAMPDIFF (month, Hiredate, current_date()) as DiffMonth From employeesWhere TIMESTAMPDIFF (month, Hiredate, current_date()) Between 1 and 5 Order by Hiredate desc; 

To do well in interviews, you need to understand core concepts, Database Languages, DDL, DML, DQL, Joins, etc.

1. Core Concepts: Data Types, Operators, Commands, CREATE Database, SELECT Database, CREATE TABLE, WHERE Clause, ORDER By Clause, Aggregate Function, Constraints.

2. Advanced Topics: TRUNCATE TABLE, Group By Clause, FETCH, INTERSECT Operator, EXISTS Operator, CASE OperatorJOIN, JSON Functions, Indexes, Subquery, Wildcards Operators, Stored Procedures, Trigger, Regular Expressions, Database Tuning.