SQL

Introduction ✅ Interview Talk (Quick Version)

“A database is a structured collection of data that can
be stored and retrieved efficiently. Databases can be
2. SQL Basics
1. What is a Database? relational (RDBMS) like MySQL or PostgreSQL, which
A database is a structured collection of data that can store data in tables with rows and columns, or non- 1. What is SQL?
be stored, managed, and retrieved efficiently. relational (NoSQL) like MongoDB, which are schema- SQL (Structured Query Language) → Language
Purpose: organize and manage data for applications less. In RDBMS, tables have primary keys for unique used to interact with relational databases.
. identification and foreign keys for relationships. Other Used to create, read, update, delete, and manage
2. Types of Databases important concepts include schema, view, index, database objects.
1. Relational Databases (RDBMS) trigger, and stored procedures for data management
Store data in tables (rows and columns). and automation.” 2. DDL (Data Definition Language)
Examples: MySQL, PostgreSQL, Oracle, SQL
Used to define or modify database structures.
Server.
Commands:
Supports SQL (Structured Query Language).
CREATE → Create database, table, or other
2. Non-Relational Databases (NoSQL)
objects.
Flexible, schema-less data storage.
ALTER → Modify existing database/table
Examples: MongoDB, Cassandra, Redis.
structure.
Types: Document, Key-Value, Column, Graph
DROP → Delete database, table, or other
databases.
objects.
TRUNCATE → Remove all rows from a table
3. RDBMS Basics
(fast, cannot rollback in some DBs).
Table → Collection of rows (records) and columns
(fields).
3. DML (Data Manipulation Language)
Row → Single record.
Used to manipulate data inside tables.
Column → Attribute of data.
Commands:
Primary Key → Unique identifier for each row.
INSERT → Add new rows.
Foreign Key → Links one table to another to maintain
UPDATE → Modify existing data.
referential integrity.
DELETE → Remove rows.
MERGE → Insert or update depending on
4. Important Database Concepts
whether record exists.
1. Schema → Blueprint of database structure (tables,
relationships, constraints).
4. DQL (Data Query Language)
2. Table → Stores actual data in rows and columns.
Used to query or fetch data from database.
3. View → Virtual table based on a query; does not store
Main command:
data physically.
SELECT → Retrieve data from one or more
4. Index → Speeds up data retrieval.
tables.
5. Trigger → Automated action executed on INSERT,
Can use clauses: WHERE, ORDER BY, GROUP
UPDATE, DELETE.
BY, HAVING.
6. Stored Procedure → Predefined SQL code stored in
the database, can be executed repeatedly.
 3. Querying Data 6.GROUP BY Clause
5. DCL (Data Control Language) 1. Purpose
Used to control access/permissions in the GROUP BY is used to group rows that have
database. 1. SELECT Statement the same values in specified columns.
Commands: Retrieve data from one or more tables. Commonly used with aggregate functions
GRANT → Give privileges to users (e.g., SELECT, Examples: (COUNT, SUM, AVG, MIN, MAX) to summarize
INSERT). Select specific columns: SELECT name, data.
REVOKE → Remove privileges from users. age FROM users
Select all columns: SELECT * FROM users HAVING Clause
6. TCL (Transaction Control Language) Aliases: SELECT name AS username FROM HAVING is used to filter groups, similar to
Used to manage transactions to ensure data users how WHERE filters rows.
consistency.
Commands: 2. WHERE Clause ✅ Interview Talk
COMMIT → Save all changes permanently. Filter data using conditions. “GROUP BY groups rows with the same values in
ROLLBACK → Undo changes since last commit. Operators: specified columns, usually with aggregate
SAVEPOINT → Set a point within a transaction Comparison: =, <>, >, <, >=, <= functions to summarize data. The HAVING clause
to rollback partially. Set: IN, NOT IN filters these groups. It’s different from WHERE,
Range: BETWEEN … AND … which filters individual rows before grouping.”
Pattern: LIKE (% for any chars, _ for single
✅ Interview Talk (Quick Version) char) 7. Joins
“SQL is a language to interact with relational Null check: IS NULL, IS NOT NULL Combine data from multiple tables:
databases. DDL commands like CREATE, ALTER, DROP 1. INNER JOIN → only matching rows from both
define database structures. DML commands like 3. ORDER BY Clause tables.
INSERT, UPDATE, DELETE manipulate data. DQL Sort results. 2. LEFT JOIN → all rows from left table +
SELECT retrieves data. DCL (GRANT/REVOKE) ASC → ascending (default) matching rows from right.
manages permissions, and TCL (COMMIT, ROLLBACK, DESC → descending 3. RIGHT JOIN → all rows from right table +
SAVEPOINT) controls transactions for data matching rows from left.
consistency.” 4. DISTINCT Keyword 4. FULL OUTER JOIN → all rows from both
Removes duplicate rows from the result. tables, NULL if no match.
Example: SELECT DISTINCT department 5. SELF JOIN → table joined with itself.
FROM employees
8. Subqueries
5. Aggregate Functions Query within another query.
Perform calculations on multiple rows: Types:
COUNT(column) → number of rows Single-row → returns one value.
SUM(column) → total of numeric values Multiple-row → returns multiple values
AVG(column) → average value (use IN/ANY/ALL).
MIN(column) → minimum value Correlated subquery → refers to outer
MAX(column) → maximum value query column.
✅ Interview Talk (Subquerires) 4. Advanced SQL Concepts CHECK → Validates values against a
condition (e.g., CHECK (age >= 18))
“A subquery is a query inside another query. The inner DEFAULT → Assigns a default value if none
query executes first and returns a result, which is then 1. Views is provided
used by the outer query for filtering, comparison, or Definition: A view is a virtual table created by a
calculation. Correlated subqueries depend on each row SELECT query. ✅ Interview Talk (Quick Version)
of the outer query. This flow allows complex filtering and Does not store data physically (except “Advanced SQL concepts include Views, which
aggregation in a single SQL statement.” materialized views). are virtual tables; Indexes, which speed up data
Types: retrieval; and Constraints, which enforce data
a. Simple View → Based on a single table; no integrity. Views can be simple or materialized.
9. Set Operations aggregate functions or joins required. Indexes can be single-column, composite, or
Combine results from multiple queries: b. Materialized View → Stores the query result unique. Constraints like primary key, foreign key,
UNION → combines results, removes duplicates physically; needs refreshing to stay updated. unique, not null, check, and default ensure the
UNION ALL → combines results, keeps duplicates Benefits: database remains consistent and reliable.”
INTERSECT → returns common rows Simplifies complex queries
MINUS / EXCEPT → returns rows in first query but Provides data abstraction and security (hide 1. Stored Procedures & Functions
not in second sensitive columns) Stored Procedure: Predefined SQL code
stored in the database, can accept
2. Indexes parameters, used for repetitive tasks.
Definition: Indexes speed up data retrieval in Function: Similar to a procedure but returns a
large tables. single value and can be used in SQL queries.
✅ Interview Talk (Quick Version) Types: Benefits:
“In SQL, the SELECT statement retrieves data with a. Single Column Index → Index on one column. Reusability
optional filtering using WHERE. We can sort using ORDER b. Composite Index → Index on multiple Reduced network traffic
BY and remove duplicates with DISTINCT. Aggregate columns. Centralized business logic
functions like COUNT, SUM, AVG, MIN, and MAX help c. Unique Index → Ensures column values are 2. Triggers
summarize data. GROUP BY groups rows, and HAVING unique. Definition: Automated actions executed
filters groups. Joins combine tables (INNER, LEFT, RIGHT, Benefits: before or after INSERT, UPDATE, DELETE on a
FULL, SELF). Subqueries allow queries inside queries, Faster SELECT queries table.
and set operations like UNION, INTERSECT, and MINUS Can slow down INSERT/UPDATE/DELETE Types:
combine results from multiple queries.” because indexes need updating BEFORE Trigger → Executes before the
operation
3. Constraints AFTER Trigger → Executes after the
Definition: Rules enforced on table columns to operation
maintain data integrity. Use Cases:
Types: Data validation
a. PRIMARY KEY → Unique identifier for each Audit logging
row; cannot be NULL Maintaining derived columns
b. FOREIGN KEY → Maintains referential integrity
between tables
c. UNIQUE → Ensures all values in a column are
unique
d. NOT NULL → Column cannot have NULL value
 PL SQL
3. Cursors
Definition: Database objects used to iterate over
rows returned by a query.
Types:
Implicit Cursor → Automatically created for PL/SQL Blocks
single-row queries 1. What is a PL/SQL Block?
Explicit Cursor → Manually created for multi-row Definition: A PL/SQL block is the basic unit of
queries execution in Oracle PL/SQL.
Use Cases: Processing rows one by one when set- Combines SQL statements and procedural
based operations are not sufficient constructs (like variables, loops, conditionals).
Executes as a single logical unit.
4. Transactions & ACID Properties
Transaction: A logical unit of work that must be
completed entirely or not at all.
ACID Properties:
Atomicity: All or nothing
Consistency: Data remains consistent before &
after transaction
Isolation: Transactions do not interfere with each
other
Durability: Changes are permanent once
committed
Transaction Control Commands: ✅ Interview Talk (Quick Version)
COMMIT → Save changes permanently “A PL/SQL block is the basic unit of execution in
ROLLBACK → Undo changes Oracle, combining SQL and procedural code. It has
SAVEPOINT → Partial rollback declaration, executable, and exception sections.
Blocks can be anonymous (unnamed, immediate
5. Error Handling execution) or named (stored in the database for
SQL Server: TRY…CATCH block to handle runtime reuse, e.g., procedures and functions).”
errors
PL/SQL (Oracle): EXCEPTION block to catch and
handle errors
Use Cases:
Avoid breaking application due to DB errors
Log errors for auditing or debugging

✅ Interview Talk (Quick Version)

“Advanced SQL features include Stored Procedures and
Functions for reusable logic, Triggers for automated
actions before or after data changes, and Cursors for
row-by-row processing. Transactions ensure ACID
properties (Atomicity, Consistency, Isolation, Durability),
and error handling with TRY-CATCH or EXCEPTION
blocks prevents runtime failures and allows graceful
1. Variables & Constants
Variables: Store temporary data during block
execution. 6. Performance & Optimization
Constants: Fixed value, cannot be changed.
Declaration Example: (Database)

1. Query Optimization
Aim: Improve SQL query performance and
reduce execution time.
Techniques:
Select only required columns (SELECT
6. Procedures, Functions, Packages, Triggers column_name instead of SELECT *)
Procedure: Named block, may accept Use WHERE clause filters to minimize rows
parameters, does not return a value. scanned
Function: Named block, returns a value, can be Avoid unnecessary subqueries if joins are
used in SQL queries. more efficient
Package: Group of procedures, functions, Use set-based operations instead of row-by-
2. Data Types variables, and cursors; promotes modularity. row processing
Scalar types: NUMBER, VARCHAR2, CHAR, DATE, Trigger: Automatic action before/after INSERT,
BOOLEAN UPDATE, DELETE on a table. 2. Using Indexes Efficiently
Composite types: RECORD, TABLE, VARRAY Indexes speed up data retrieval but slow down
Used to define the kind of data a variable can ✅ Interview Talk (Quick Version) DML operations (INSERT/UPDATE/DELETE).
hold. “PL/SQL extends SQL with procedural capabilities. It Best Practices:
supports variables, constants, and data types, with Index columns used in WHERE, JOIN, ORDER
3. Control Statements control structures like IF, CASE, and loops. Cursors BY, GROUP BY
Conditional Statements: allow row-by-row processing. Exceptions handle Avoid indexing columns with low cardinality
IF … THEN … ELSE runtime errors. Named blocks include procedures (few unique values)
CASE … WHEN … THEN … ELSE … END → multi- (no return), functions (return value), packages Use composite indexes for multiple columns
way decision (grouped code), and triggers (automatic actions on often queried together
Loops: table events). This allows writing modular, reusable, Regularly rebuild or reorganize indexes for
FOR loop → fixed number of iterations and robust database logic.” fragmented tables
WHILE loop → runs while a condition is true
LOOP … EXIT WHEN … → general-purpose loop 3. Avoiding Full Table Scans
4. Cursors & Cursor FOR Loops Full table scans are expensive for large tables.
Cursor: Pointer to query result set for row-by- Avoid by:
row processing. Using indexed columns in WHERE clauses
Types: Writing selective queries
a.Explicit Cursor → declared manually for multi- Using EXISTS instead of IN for subqueries
row queries Partitioning large tables
b.Cursor FOR Loop → simplifies processing,
automatically opens/fetches/closes cursor
5. Exception Handling
Used to handle runtime errors gracefully.
Common exceptions: NO_DATA_FOUND,
TOO_MANY_ROWS, ZERO_DIVIDE
4. Execution Plan Analysis
Execution Plan shows how the database executes a
Interview Talk (Quick Version)
“Database performance optimization includes query
7. Database Design
query. optimization (selective columns, efficient filters, set-
Helps identify: based operations) and using indexes wisely. Avoid 1. ER Diagram Design
Table scans full table scans by indexing and writing selective Definition: Entity-Relationship (ER) diagrams
Index usage queries. Execution plans help analyze and tune visually represent the database structure.
Join methods (nested loops, hash join, merge join) queries. Normalization reduces redundancy, while Components:
Tools: denormalization improves read performance when Entities: Tables or real-world objects
Oracle → EXPLAIN PLAN necessary. For very large tables, partitioning (range, Attributes: Columns of tables
SQL Server → Execution Plan GUI list, hash, composite) enhances performance and Relationships: Associations between
Use this to tune queries and indexes manageability.” entities (1:1, 1:N, N:M)
Helps in planning and understanding
5. Normalization database before implementation.
Purpose: Reduce redundancy and improve data
integrity. 2. Primary Key / Foreign Key Relationships
Normal Forms: Primary Key (PK):
1NF: No repeating groups; each column contains Unique identifier for each row in a table
atomic values Cannot be NULL
2NF: 1NF + all non-key columns fully depend on Foreign Key (FK):
primary key Column referencing a primary key in
3NF: 2NF + no transitive dependencies another table
BCNF (Boyce-Codd): Stronger version of 3NF for Enforces referential integrity
complex keys Maintains data consistency across related
tables
6. Denormalization
Purpose: Improve query performance by reducing joins 3. Types of Relationships
Trade-off: Introduces redundancy 1. One-to-One (1:1)
Used when: Each row in Table A relates to only one
Read-heavy systems row in Table B
Reporting/analytics queries need faster access Example: Person → Passport
2. One-to-Many (1:N)
7. Partitioning Tables Each row in Table A can relate to multiple
Split large tables into smaller, manageable pieces for rows in Table B
performance. Example: Department → Employees
Types: 3. Many-to-Many (N:M)
Range Partitioning: Based on a range of values (e.g., Rows in Table A relate to multiple rows in
date ranges) Table B and vice versa
List Partitioning: Based on discrete list of values Implemented via junction table
(e.g., region names) Example: Students ↔ Courses
Hash Partitioning: Based on hash of column value
for uniform distribution
Composite Partitioning: Combination of multiple
methods
4. Data Types Selection
Choose appropriate data types for efficiency and
8. Advanced SQL Features 3. Recursive Queries
Definition: Queries that reference themselves to
accuracy:
1. Window Functions process hierarchical or tree-structured data.
VARCHAR2 for variable-length strings
Definition: Perform calculations across a set of Common in: Organizational charts, bill of
NUMBER for numeric data
rows related to the current row without materials, file systems.
DATE / TIMESTAMP for date/time
collapsing rows. Example: Oracle or SQL Server recursive CTE to
BOOLEAN for true/false
Common Functions: get employee hierarchy.
Reduces storage and improves performance
ROW_NUMBER() → Assigns unique sequential
numbers 4. JSON / XML Support
5. Referential Integrity
RANK() → Assigns rank with gaps for ties Modern databases (Oracle, PostgreSQL, SQL
Ensures relationships between tables remain
DENSE_RANK() → Assigns rank without gaps Server) support JSON and XML data types.
consistent.
for ties Functions:
Rules:
NTILE(n) → Divides rows into n roughly equal JSON: JSON_VALUE(), JSON_QUERY(),
Cannot insert FK value not present in PK table
groups JSON_TABLE()
Cannot delete PK row if referenced by FK (unless
Use Case: Ranking employees by salary within XML: XMLTYPE(), EXTRACT()
cascade rules applied)
departments. Use Case: Storing and querying semi-structured
data efficiently.
6. Schema Design & Best Practices
Keep schema normalized to reduce redundancy 2. Common Table Expressions (CTEs)
Definition: Temporary named result sets defined 5. Dynamic SQL
Name tables and columns meaningfully
using WITH clause. Definition: SQL statements constructed and
Use indexes for frequently queried columns
Benefits: executed at runtime.
Avoid storing derived data unless necessary
Simplifies complex queries Use Case: When table names, column names, or
Plan for scalability and partitioning if table grows large
Can be referenced multiple times in the main filters are not known in advance.
query Example: Using EXECUTE IMMEDIATE in Oracle
Example: PL/SQL or sp_executesql in SQL Server.
Caution: Can lead to SQL injection, always use
✅ Interview Talk (Quick Version) bind variables or parameters.
“Database design starts with ER diagrams to map entities,
attributes, and relationships. Use primary and foreign keys
to maintain data integrity. Relationships can be one-to-one,
✅ Interview Talk (Quick Version)
“Advanced SQL features include window functions
one-to-many, or many-to-many. Choose appropriate data
(ROW_NUMBER, RANK, NTILE) for ranking and
types for efficiency, and ensure referential integrity rules
analytics, CTEs for simplifying complex queries, and
are followed. Good schema design includes normalization,
recursive queries for hierarchical data. Modern
meaningful naming, indexing, and planning for scalability.”
databases support JSON/XML for semi-structured
data, and dynamic SQL allows runtime query
generation. These features enhance flexibility,
analytics, and performance in complex scenarios.”
 10. Integration with Programming
9. Database Tools 5. Monitoring Queries & Sessions
Purpose: Identify long-running queries, locks, and 1. JDBC (Java Database Connectivity)
performance bottlenecks. Definition: Standard Java API for connecting to
1. SQL Developer / SSMS / DBeaver Oracle Tools: relational databases.
SQL Developer (Oracle): GUI tool to run queries, V$SESSION, V$SQL, AWR reports Steps:
manage database objects, and debug PL/SQL. SQL Server Tools: a.Load database driver (Class.forName)
SQL Server Management Studio (SSMS): Manage SQL Activity Monitor, Dynamic Management Views b.Establish connection
Server databases, execute queries, design tables, and (DMVs) (DriverManager.getConnection)
monitor performance. Use Case: Performance tuning, deadlock c.Create statement (Statement /
DBeaver: Cross-platform database management tool resolution, and query optimization. PreparedStatement)
supporting multiple DBMS (Oracle, MySQL, PostgreSQL, d.Execute query (executeQuery /
SQL Server).
Benefits: Easy query execution, object management,
✅ Interview Talk (Quick Version) executeUpdate)
“Database tools help manage, monitor, and optimize e.Process results (ResultSet)
data visualization, and debugging. databases. SQL Developer, SSMS, and DBeaver f.Close connection
provide GUI interfaces for queries and object Benefits:
2. Exporting & Importing Data management. Export/import utilities move data Platform-independent database connectivity
Purpose: Move data between databases or for backup. between databases. Job schedulers like Supports transactions and batch processing
Oracle: DBMS_SCHEDULER automate repetitive tasks.
Data Pump (expdp / impdp) Backup and restore ensure data safety, while 2. ORM (Hibernate / JPA Basics)
SQL*Loader for bulk inserts from flat files monitoring queries and sessions helps identify Definition: Object-Relational Mapping
SQL Server: performance bottlenecks.” frameworks map database tables to Java objects.
bcp utility or Import/Export Wizard Hibernate / JPA:
Use Case: Migrating tables, backups, or reporting. Use entities (@Entity) for tables
Repositories / DAOs to perform CRUD
3. Scheduling Jobs operations
Purpose: Automate repetitive database tasks. Annotations like @Id, @GeneratedValue,
Oracle: DBMS_SCHEDULER @Column
Schedule procedures, scripts, or reports Benefits:
Supports recurring jobs Avoids boilerplate JDBC code
SQL Server: SQL Server Agent Jobs Supports caching, lazy loading, and
Use Case: Nightly data loads, backups, or automated relationships
reports.
3. Calling Stored Procedures from Application
4. Backup & Restore Purpose: Execute database procedures directly
Purpose: Protect data against loss or corruption. from code.
Oracle: Example in JDBC:
RMAN for full/incremental backups Use CallableStatement
expdp / impdp for logical backups Pass input/output parameters
SQL Server: Execute procedure and retrieve results
BACKUP DATABASE and RESTORE DATABASE Use Case: Centralized business logic in the
commands database, reusable by multiple applications
Use Case: Disaster recovery, database migration, or
 10. Integration with Programming
4. Parameterized Queries 5. Monitoring Queries & Sessions
Definition: Queries with placeholders (?) replaced by Purpose: Identify long-running queries, locks, and 1. JDBC (Java Database Connectivity)
values at runtime. performance bottlenecks. Definition: Standard Java API for connecting to
Benefits: Oracle Tools: relational databases.
Prevents SQL Injection V$SESSION, V$SQL, AWR reports Steps:
Allows dynamic queries with safe input SQL Server Tools: a.Load database driver (Class.forName)
Example: Activity Monitor, Dynamic Management Views b.Establish connection
(DMVs) (DriverManager.getConnection)
Use Case: Performance tuning, deadlock c.Create statement (Statement /
resolution, and query optimization. PreparedStatement)
d.Execute query (executeQuery /
✅ Interview Talk (Quick Version) executeUpdate)
“Database tools help manage, monitor, and optimize e.Process results (ResultSet)
databases. SQL Developer, SSMS, and DBeaver f.Close connection
✅ Interview Talk (Quick Version) provide GUI interfaces for queries and object Benefits:
“Applications interact with databases using JDBC management. Export/import utilities move data Platform-independent database connectivity
for standard connectivity or ORM frameworks like between databases. Job schedulers like Supports transactions and batch processing
Hibernate/JPA for mapping tables to objects. Stored DBMS_SCHEDULER automate repetitive tasks.
procedures can be called from applications for Backup and restore ensure data safety, while 2. ORM (Hibernate / JPA Basics)
centralized business logic. Parameterized queries monitoring queries and sessions helps identify Definition: Object-Relational Mapping
ensure security against SQL injection and allow performance bottlenecks.” frameworks map database tables to Java objects.
dynamic, safe query execution.” Hibernate / JPA:
Use entities (@Entity) for tables
Repositories / DAOs to perform CRUD
operations
Annotations like @Id, @GeneratedValue,
@Column
Benefits:
Avoids boilerplate JDBC code
Supports caching, lazy loading, and
relationships

3. Calling Stored Procedures from Application

Purpose: Execute database procedures directly
from code.
Example in JDBC:
Use CallableStatement
Pass input/output parameters
Execute procedure and retrieve results
Use Case: Centralized business logic in the
database, reusable by multiple applications