Complete SQL Database Guide with Visual Diagrams
SQL Command Types
DDL - Data Definition Language
Key DDL Commands:
CREATE: Creates new database objects (tables, views, indexes)
ALTER: Modifies existing database structure
DROP: Deletes database objects completely
TRUNCATE: Removes all rows from a table quickly
RENAME: Changes object names
TRUNCATE vs DELETE vs DROP Comparison
Comparison Summary:
Use DELETE when you need to remove specific rows with conditions
Use TRUNCATE when you need to clear an entire table quickly
Use DROP when you want to remove the table completely
SQL Operators and Clauses
UNION vs UNION ALL
When to Use:
UNION: When you need unique results from combined queries
UNION ALL: When you need all results including duplicates (faster)
WHERE vs HAVING Clause
Key Differences:
WHERE: Filters rows before grouping
HAVING: Filters groups after aggregation
WHERE: Cannot use COUNT, SUM, AVG, etc.
HAVING: Can use aggregate functions
Database Indexes
Clustered vs Non-Clustered Indexes
Important Notes:
A table can have only one clustered index
A table can have multiple non-clustered indexes (up to 999 in SQL Server)
Clustered index determines physical storage order
Non-clustered indexes have separate structures
Index Types and Performance
Performance Tips:
Always aim for Index Seek operations
Avoid Table Scan in production queries
Monitor execution plans regularly
Advanced Queries
Finding Second Highest Salary
Method Recommendations:
Subquery: Simple and readable
Window Function: Most efficient for large datasets
Self-Join: Flexible for nth highest value
Finding Duplicate Rows
Best Practice:
Use GROUP BY for simple duplicate counts
Use Window Functions to identify and remove duplicates
Use Self-Join when you need all duplicate record details
Correlated vs Non-Correlated Subqueries
Performance Considerations:
Non-correlated subqueries are generally faster
Correlated subqueries run once per outer row
Consider using JOINs instead of correlated subqueries when possible
Data Types and Constraints
PRIMARY KEY vs UNIQUE Constraint
Key Differences:
PRIMARY KEY: Mandatory, one per table, no NULLs
UNIQUE: Optional, multiple allowed, one NULL permitted
CHAR vs VARCHAR Data Types
Storage Comparison:
CHAR(10): "Hello" = 10 bytes (padded)
VARCHAR(10): "Hello" = 5 bytes
NVARCHAR(10): "Hello" = 10 bytes (2 per char)
ISNULL() vs COALESCE()
When to Use:
ISNULL: SQL Server, two-value replacement
COALESCE: Cross-platform, multiple fallback values
Joins and Relationships
JOIN Types Comparison
Join Type Usage:
INNER JOIN: Most common, returns only matches
LEFT JOIN: Keep all left table records
RIGHT JOIN: Keep all right table records
FULL OUTER JOIN: Keep all records from both
SELF JOIN: Compare rows within same table
Referential Integrity
Cascade Options Explained:
CASCADE DELETE: Delete child records when parent is deleted
CASCADE UPDATE: Update child records when parent key changes
SET NULL: Set foreign key to NULL when parent is deleted
SET DEFAULT: Set foreign key to default value
NO ACTION: Prevent deletion/update if child records exist
Database Functions
Function Types in SQL
Function Categories:
Scalar: Return single value
Aggregate: Work on sets of values
Table-Valued: Return table results
System: Provide system information
Inline vs Multi-Statement Table-Valued Functions
Performance Note: Inline TVFs are significantly faster because they can be optimized by the query optimizer like views.
Window and Ranking Functions
RANK vs DENSE_RANK vs ROW_NUMBER
When to Use:
ROW_NUMBER: Need unique sequential numbers
RANK: Standard competition ranking (skip numbers)
DENSE_RANK: Dense ranking (no gaps)
LAG vs LEAD Functions
Practical Example:
-- Compare current month sales to previous month
SELECT
Month,
Sales,
LAG(Sales, 1, 0) OVER (ORDER BY Month) as PrevMonthSales,
Sales - LAG(Sales, 1, 0) OVER (ORDER BY Month) as Difference
FROM MonthlySales
FIRST_VALUE vs LAST_VALUE
Important: LAST_VALUE requires explicit frame specification to work correctly!
Database Storage and Performance
How Data is Stored in Database
Storage Hierarchy:
Pages: 8KB blocks (smallest unit)
Extents: 8 pages (64KB)
Files: Collections of extents
Query Execution Steps
Query Lifecycle:
Parsing: Check syntax and security
Binding: Resolve names and types
Optimization: Create best execution plan
Execution: Run query and return results
