SQL Theory

This is an SQL all topic document:-​

1.​ SQL Fundamentals

➢​ SQL syntax and rules
➢​ Comments in SQL
➢​ SQL data types
➢​ Operators (arithmetic, comparison, logical)
➢​ SQL Server architecture
➢​ Database objects
2.​ Data Definition Language (DDL)
➢​ CREATE
➢​ ALTER
➢​ DROP
➢​ TRUNCATE
➢​ RENAME
➢​ COMMENT
➢​ Schema management
➢​ Database creation/modification
➢​ Constraints (PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, DEFAULT, NOT
NULL)
➢​ Temporary tables
➢​ Sequences
➢​ Synonyms
3.​ Data Manipulation Language (DML)
➢​ SELECT
➢​ INSERT
➢​ UPDATE
➢​ DELETE
➢​ MERGE/UPSERT
➢​ Basic query structure
➢​ Filtering with WHERE
➢​ Sorting with ORDER BY
➢​ DISTINCT keyword
➢​ NULL handling
➢​ Pattern matching (LIKE, wildcards)
➢​ IN, BETWEEN, EXISTS
4. Data Control Language (DCL)
- GRANT
- REVOKE
- DENY
- Role management
- User permissions
- Object permissions
- Schema permissions
- System permissions

5. Transaction Control Language (TCL)

- BEGIN TRANSACTION
- COMMIT
- ROLLBACK
- SAVEPOINT
- SET TRANSACTION
- Transaction isolation levels
- Deadlock handling

6. Joins and Relationships

- INNER JOIN
- LEFT/RIGHT OUTER JOIN
- FULL OUTER JOIN
- CROSS JOIN
- Self joins
- Natural joins
- Multiple table joins
- Join optimization

7. Functions
- String functions
- Numeric functions
- Date/Time functions
- Conversion functions
- System functions
- User-defined functions
- Aggregate functions
- Window functions
- Analytical functions

8. Advanced Querying
- Subqueries (Single-row, Multi-row, Correlated)
- Common Table Expressions (CTEs)
- Recursive CTEs
- PIVOT and UNPIVOT
- Dynamic SQL
- Parameterized queries
- CASE statements
- IIF and COALESCE

9. Grouping and Aggregation

- GROUP BY
- HAVING
- ROLLUP
- CUBE
- GROUPING SETS
- Complex aggregations
- Windowing clauses
- OVER clause

10. Database Design

- Database normalization (1NF to 5NF)
- Denormalization
- Entity Relationship Diagrams (ERD)
- Cardinality
- Data modeling
- Schema design
- Best practices

11. Indexing and Performance

- Index types (Clustered, Non-clustered)
- Composite indexes
- Filtered indexes
- Index maintenance
- Statistics
- Query optimization
- Execution plans
- Performance tuning
- Query hints

12. Views
- Creating views
- Indexed views
- Materialized views
- Updatable views
- View limitations
- View performance
- View security

13. Stored Procedures and Functions

- Creating stored procedures
- Input/Output parameters
- Return values
- Error handling
- Dynamic SQL in procedures
- Function types
- Table-valued functions
- Scalar functions
- Aggregate functions

14. Triggers
- DML triggers
- DDL triggers
- INSTEAD OF triggers
- AFTER triggers
- Trigger execution order
- Nested triggers
- Trigger design patterns

15. Security
- Authentication
- Authorization
- Encryption
- Row-level security
- Column-level security
- Data masking
- Audit trails
- Compliance

16. Database Administration

- Backup and recovery
- Database maintenance
- Monitoring
- Performance tuning
- High availability
- Disaster recovery
- Replication
- Log shipping

17. Modern SQL Features

- JSON operations
- XML handling
- Full-text search
- Spatial data
- Graph database features
- Machine learning integration
- Temporal tables
- Memory-optimized tables

18. Data Integration and ETL

- BULK INSERT
- BCP utility
- SSIS concepts
- Data transformation
- Error handling
- Logging
- Scheduling
- Pipeline management

19. Advanced Concepts

- Partitioning
- Sharding
- Replication
- Always On
- In-memory OLTP
- Columnstore indexes
- Resource Governor
- Service Broker

20. Development Best Practices

- Code organization
- Naming conventions
- Error handling
- Transaction management
- Concurrency control
- Version control
- Testing strategies
- Documentation

21. Database Maintenance

- Index maintenance
- Statistics updates
- Integrity checks
- Log management
- Backup strategies
- Recovery models
- Performance monitoring
- Troubleshooting
SQL Fundamentals
1. SQL Fundamentals
A. Introduction to Databases
- What is a database
- Types of databases (RDBMS vs NoSQL)
- Popular database systems
- Client-server architecture
- Database instances

B. SQL Basics
- SQL standards
- SQL flavors (MySQL, PostgreSQL, SQL Server, Oracle)
- SQL statements structure
- SQL keywords
- Case sensitivity
- Statement terminators
- Comments (single line, multi-line)

C. Data Types
- Numeric types
* Integer types (TINYINT, SMALLINT, INT, BIGINT)
* Decimal types (DECIMAL, NUMERIC)
* Floating point types (FLOAT, REAL)
* Money types
- String types
* CHAR vs VARCHAR
* TEXT types
* NCHAR vs NVARCHAR
* Unicode support
- Date and Time types
* DATE
* TIME
* DATETIME
* TIMESTAMP
* INTERVAL
- Binary types
* BINARY
* VARBINARY
* BLOB
- Boolean type
- Special types
* XML
* JSON
* GUID/UUID
* ENUM
 * Spatial data types

D. Operators
- Arithmetic operators (+, -, *, /, %)
- Comparison operators (=, <>, <, >, <=, >=)
- Logical operators (AND, OR, NOT)
- Special operators
* BETWEEN
* IN
* LIKE
* IS NULL
* EXISTS
- Bitwise operators
- String operators
- Compound operators
Data Definition Language (DDL)
2. Data Definition Language (DDL)
A. Database Operations
- CREATE DATABASE
- ALTER DATABASE
- DROP DATABASE
- BACKUP DATABASE
- RESTORE DATABASE

B. Schema Operations
- CREATE SCHEMA
- ALTER SCHEMA
- DROP SCHEMA
- Schema permissions

C. Table Operations
- CREATE TABLE
* Column definitions
* Constraints
* Table options
- ALTER TABLE
* ADD column
* DROP column
* MODIFY column
* ALTER column
* ADD/DROP constraints
- DROP TABLE
- TRUNCATE TABLE
- RENAME TABLE

D. Constraints
- PRIMARY KEY
* Single column
* Composite
* Auto-increment
- FOREIGN KEY
* References
* ON DELETE actions
* ON UPDATE actions
- UNIQUE
- CHECK
- DEFAULT
- NOT NULL
- Table-level vs Column-level
E. Index Management
- CREATE INDEX
* Single-column
* Composite
* Unique
* Clustered
* Non-clustered
- DROP INDEX
- REBUILD INDEX
- REORGANIZE INDEX

F. View Operations
- CREATE VIEW
- ALTER VIEW
- DROP VIEW
- Indexed views
- Materialized views

G. Temporary Objects
- Local temp tables
- Global temp tables
- Table variables
- Temporary stored procedures
Data Manipulation Language (DML)
3. Data Manipulation Language (DML)
A. SELECT Statement
- Basic syntax
- Column selection
* All columns (*)
* Specific columns
* Column aliases
* Computed columns
- Filtering data (WHERE)
* Comparison conditions
* Logical conditions
* Pattern matching
* NULL handling
- Sorting data (ORDER BY)
* Single column
* Multiple columns
* ASC/DESC
* NULLS FIRST/LAST
- Limiting results
* TOP
* OFFSET-FETCH
* LIMIT

B. INSERT Operations
- INSERT INTO VALUES
- INSERT INTO SELECT
- Bulk insert
- Multiple row insert
- Identity insert
- Default values

C. UPDATE Operations
- Single table update
- Multi-table update
- Update with joins
- Update with subqueries
- Updating computed columns

D. DELETE Operations
- Single table delete
- Multi-table delete
- Delete with joins
- Delete with subqueries
- Truncate vs Delete
Control Language (DCL) 7
4. Data Control Language (DCL)
A. User Management
- CREATE USER
- ALTER USER
- DROP USER
- User authentication methods
- Password policies
- User profiles

B. Role Management
- CREATE ROLE
- ALTER ROLE
- DROP ROLE
- Predefined roles
- Custom roles
- Role hierarchies

C. Privileges
- Object privileges
* SELECT
* INSERT
* UPDATE
* DELETE
* EXECUTE
* REFERENCES
- System privileges
* CREATE SESSION
* CREATE TABLE
* CREATE VIEW
* CREATE PROCEDURE
- Schema privileges

D. GRANT Operations
- GRANT on tables
- GRANT on views
- GRANT on procedures
- GRANT on functions
- WITH GRANT OPTION
- Column-level grants

E. REVOKE Operations
- REVOKE from users
- REVOKE from roles
- Cascade revokes
- Revoking granted privileges

F. Security Contexts
- EXECUTE AS
- IMPERSONATION
- Ownership chaining
- Cross-database ownership
Transaction Control Language (TCL)
5. Transaction Control Language (TCL)
A. Transaction Basics
- Transaction properties (ACID)
* Atomicity
* Consistency
* Isolation
* Durability
- Transaction boundaries
- Implicit vs Explicit transactions

B. Transaction Commands
- BEGIN TRANSACTION
- COMMIT
- ROLLBACK
- SAVEPOINT
- RELEASE SAVEPOINT

C. Transaction Isolation Levels

- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
- SNAPSHOT

D. Concurrency Issues
- Dirty reads
- Non-repeatable reads
- Phantom reads
- Lost updates

E. Locking
- Shared locks
- Exclusive locks
- Update locks
- Intent locks
- Schema locks
- Lock escalation

F. Deadlock Handling
- Deadlock detection
- Deadlock prevention
- Deadlock resolution
- Priority setting
Joins and Relationships
6. Joins and Relationships
A. Types of Joins
- INNER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- CROSS JOIN
- Natural joins

B. Join Conditions
- Equi-joins
- Non-equi joins
- Multiple conditions
- Using vs On clause

C. Self Joins
- Hierarchical data
- Employee-manager
- Self-referencing

D. Multiple Table Joins

- Join order
- Join optimization
- Best practices

E. Advanced Join Concepts

- Anti-joins
- Semi-joins
- Lateral joins
- Cross apply
- Outer apply
Functions
7. Functions
A. String Functions
- SUBSTRING
- CHARINDEX
- PATINDEX
- LEFT/RIGHT
- LTRIM/RTRIM
- UPPER/LOWER
- REPLACE
- STRING_AGG
- CONCAT

B. Numeric Functions
- ROUND
- CEILING
- FLOOR
- ABS
- POWER
- SQRT
- RAND
- Mathematical functions

C. Date/Time Functions
- GETDATE
- DATEADD
- DATEDIFF
- DATEFORMAT
- EOMONTH
- Time zone handling

D. Conversion Functions
- CAST
- CONVERT
- PARSE
- TRY_CAST
- TRY_CONVERT
- TRY_PARSE

E. Aggregate Functions
- COUNT
- SUM
- AVG
- MIN/MAX
- STRING_AGG
- GROUPING

F. Window Functions
- ROW_NUMBER
- RANK
- DENSE_RANK
- NTILE
- LAG/LEAD
- FIRST_VALUE/LAST_VALUE

G. System Functions
- @@VERSION
- @@SPID
- SYSTEM_USER
- SESSION_USER
- CURRENT_USER
Advanced Querying
8. Advanced Querying
A. Subqueries
- Single-row subqueries
- Multi-row subqueries
- Correlated subqueries
- Nested subqueries
- EXISTS/NOT EXISTS
- ANY/ALL operators
- IN/NOT IN

B. Common Table Expressions (CTEs)

- Basic CTEs
- Recursive CTEs
* Tree structures
* Hierarchical data
* Graph traversal
- Multiple CTEs
- Materialized CTEs

C. PIVOT and UNPIVOT

- Static PIVOT
- Dynamic PIVOT
- UNPIVOT operations
- Cross tabulation

D. Dynamic SQL
- EXEC/EXECUTE
- sp_executesql
- Parameter handling
- SQL injection prevention
- Error handling

E. CASE Expressions
- Simple CASE
- Searched CASE
- Nested CASE
- CASE in calculations
- CASE in ordering

F. Advanced Conditions
- IIF function
- CHOOSE function
- COALESCE
- NULLIF
- Complex conditions
Grouping and Aggregation
9. Grouping and Aggregation
A. Basic Grouping
- GROUP BY clause
- Single column grouping
- Multiple column grouping
- Expression grouping

B. Advanced Grouping
- ROLLUP
- CUBE
- GROUPING SETS
- GROUPING_ID function

C. Having Clause
- Filtering groups
- Aggregate conditions
- Complex conditions
- Having vs Where

D. Window Aggregation
- OVER clause
- PARTITION BY
- ORDER BY in windows
- Frame specifications
- Running totals

E. Advanced Aggregation
- Conditional aggregation
- Distinct aggregation
- Nested aggregation
- Custom aggregates
Database Design
10. Database Design
A. Database Concepts
- Tables and relationships
- Entity types
- Attributes
- Relationships
- Cardinality
- Optionality

B. Normalization
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
- Fourth Normal Form (4NF)
- Fifth Normal Form (5NF)

C. Denormalization
- When to denormalize
- Performance considerations
- Redundancy management
- Trade-offs

D. Keys
- Primary keys
- Foreign keys
- Composite keys
- Surrogate keys
- Natural keys
- Candidate keys

E. Relationships
- One-to-One
- One-to-Many
- Many-to-Many
- Self-referencing
- Identifying vs Non-identifying

F. Design Patterns
- Star schema
- Snowflake schema
- Fact tables
- Dimension tables
- Bridge tables
- History tables
Indexing and Performance
11. Indexing and Performance
A. Index Types
- Clustered indexes
- Non-clustered indexes
- Unique indexes
- Filtered indexes
- Covering indexes
- Columnstore indexes
- Hash indexes

B. Index Design
- Key selection
- Fill factor
- Included columns
- Filtered indexes
- Index padding
- Page splits

C. Statistics
- Auto statistics
- Manual statistics
- Histogram
- Density vector
- Sampling

D. Query Optimization
- Execution plans
- Statistics IO
- Query cost
- Missing indexes
- Index usage
- Plan cache

E. Performance Tuning
- Query analysis
- Index analysis
- Wait statistics
- Resource monitoring
- Bottleneck identification
- Query store
Views
12. Views
A. Basic Views
- CREATE VIEW syntax
- ALTER VIEW
- DROP VIEW
- View permissions
- View limitations

B. Types of Views
- Simple views
- Complex views
- Indexed views
- Materialized views
- Partitioned views
- System views

C. View Properties
- ENCRYPTION
- SCHEMABINDING
- CHECK OPTION
- View metadata

D. Updatable Views
- INSERT through views
- UPDATE through views
- DELETE through views
- Instead of triggers
- View restrictions

E. View Performance
- View indexing
- View materialization
- Query optimization
- View statistics
Stored Procedures and Functions
13. Stored Procedures and Functions
A. Stored Procedures
- CREATE PROCEDURE
- ALTER PROCEDURE
- DROP PROCEDURE
- Parameter handling
* Input parameters
* Output parameters
* Default values
* Optional parameters

B. User-Defined Functions
- Scalar functions
- Table-valued functions
* Inline
* Multi-statement
- Aggregate functions
- RETURN values

C. Error Handling
- TRY...CATCH blocks
- ERROR functions
- RAISERROR
- THROW
- Custom error messages

D. Control Flow
- IF...ELSE
- WHILE loops
- BREAK
- CONTINUE
- GOTO
- RETURN

E. Advanced Concepts
- Dynamic SQL in procedures
- Temporary procedures
- Recursive procedures
- Nested procedures
- Procedure caching
Triggers
14. Triggers
A. DML Triggers
- AFTER triggers
* INSERT triggers
* UPDATE triggers
* DELETE triggers
- INSTEAD OF triggers
- Multiple event triggers

B. DDL Triggers
- Database level
- Server level
- Event groups
- Trigger precedence

C. Trigger Context
- INSERTED table
- DELETED table
- Trigger recursion
- Context functions

D. Advanced Triggers
- CLR triggers
- Encrypted triggers
- Nested triggers
- Trigger disabling

E. Best Practices
- Performance considerations
- Error handling
- Transaction management
- Trigger maintenance
Security
15. Security
A. Authentication
- Windows authentication
- SQL authentication
- Mixed mode
- Azure AD authentication
- Certificate-based

B. Authorization
- Permissions hierarchy
- Ownership chains
- Module signing
- Impersonation
- Proxy accounts

C. Data Protection
- Encryption
* TDE
* Column-level
* Always Encrypted
- Data masking
* Dynamic
* Static
- Row-level security
- Column-level security

D. Audit
- Server audit
- Database audit
- C2 audit mode
- Audit specifications
- Audit logs

E. Compliance
- GDPR compliance
- SOX compliance
- PCI compliance
- Audit trails
- Data retention
Database Administration
16. Database Administration
A. Backup and Recovery
- Backup types
* Full backup
* Differential backup
* Transaction log backup
* File/Filegroup backup
- Recovery models
* Simple
* Full
* Bulk-logged
- Point-in-time recovery
- Page restore

B. Maintenance
- Index maintenance
* Reorganize
* Rebuild
- Statistics maintenance
- DBCC commands
- File management
- Space management

C. Monitoring
- Performance Monitor
- Activity Monitor
- Dynamic Management Views
- Extended Events
- SQL Profiler
- Query Store

D. High Availability
- Mirroring
- Always On
* Availability Groups
* Failover Cluster Instances
- Log shipping
- Replication
* Snapshot
* Transactional
* Merge

E. Resource Management
- Resource Governor
- Max memory
- Max CPU
- I/O management
- Workload groups

F. Troubleshooting
- Deadlock analysis
- Blocking analysis
- Performance bottlenecks
- Memory pressure
- I/O bottlenecks
- Query problems
Miscellaneous
SQL Imp Practice Question
SQL Interview Questions