SQL Tutorial

Beginners To Experts

The site is under development.

SQL Tutorial

1.1 What is SQL?
SQL stands for Structured Query Language, used to manage databases.
-- Example: Simple SELECT query
SELECT * FROM users;
1.2 Database Concepts
Understand databases, tables, rows, and columns.
-- Creating a table
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);
1.3 SQL History and Standards
Brief history and SQL standards overview.
-- SQL is standardized but varies slightly by system
-- Example: MySQL, PostgreSQL, SQL Server syntax differences
1.4 Setting Up a Database
Install and configure a SQL database.
-- MySQL: login and create database
mysql -u root -p
CREATE DATABASE sample_db;
1.5 Basic SQL Syntax
Basic syntax rules for SQL statements.
-- SQL statements end with a semicolon
SELECT column1, column2 FROM table_name;
1.6 SQL Data Types
Common SQL data types overview.
CREATE TABLE products (
  id INT,
  name VARCHAR(100),
  price DECIMAL(10,2),
  created_at DATE
);
1.7 Introduction to SQL Clients
Tools to interact with SQL databases.
-- Example: Use MySQL Workbench or psql command line
mysql -u user -p
1.8 Running Your First Query
How to execute a simple query.
SELECT NOW();
1.9 Understanding Query Results
Learn how to interpret result sets.
-- Example output:
-- +---------------------+
-- | NOW()               |
-- +---------------------+
-- | 2025-07-06 10:00:00 |
-- +---------------------+
1.10 SQL Best Practices
Basic tips to write clean SQL.
-- Use uppercase for SQL keywords
SELECT name FROM users WHERE id = 1;

2.1 SELECT Statement Basics
Retrieve data from a table.
SELECT * FROM customers;
2.2 Selecting Specific Columns
Choose which columns to display.
SELECT name, email FROM customers;
2.3 Filtering Rows with WHERE
Select rows based on conditions.
SELECT * FROM orders WHERE amount > 100;
2.4 Using Logical Operators
Combine conditions with AND, OR.
SELECT * FROM users WHERE age > 18 AND country = 'USA';
2.5 Sorting Results with ORDER BY
Sort query results.
SELECT * FROM products ORDER BY price DESC;
2.6 Limiting Results with LIMIT
Retrieve a subset of rows.
SELECT * FROM logs ORDER BY created_at DESC LIMIT 10;
2.7 Using DISTINCT
Get unique rows.
SELECT DISTINCT country FROM customers;
2.8 Using Aliases
Rename columns or tables.
SELECT name AS customer_name FROM customers;
2.9 Combining Filters (IN, BETWEEN)
Filter with ranges or sets.
SELECT * FROM orders WHERE status IN ('pending', 'shipped');
SELECT * FROM sales WHERE date BETWEEN '2025-01-01' AND '2025-01-31';
2.10 Handling NULL Values
Query NULL and NOT NULL.
SELECT * FROM users WHERE phone IS NOT NULL;

3.1 INSERT INTO Basics
Insert new rows into tables.
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
3.2 Inserting Multiple Rows
Insert many rows in one query.
INSERT INTO products (name, price) VALUES
('Product A', 10.99),
('Product B', 15.49);
3.3 INSERT with SELECT
Insert data from another table.
INSERT INTO archive_orders SELECT * FROM orders WHERE status = 'completed';
3.4 UPDATE Statement Basics
Modify existing data.
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;
3.5 Updating Multiple Columns
Change several fields.
UPDATE products SET price = price * 1.1, updated_at = NOW() WHERE category = 'books';
3.6 DELETE Statement Basics
Remove rows from a table.
DELETE FROM sessions WHERE last_active < NOW() - INTERVAL 30 DAY;
3.7 DELETE with JOIN (Some SQL flavors)
Delete rows using joins.
DELETE orders FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.status = 'inactive';
3.8 Using Transactions for Safety
Commit or rollback changes.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
3.9 Auto-Increment Columns
Use auto-increment IDs.
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);
3.10 RETURNING Clause (Postgres)
Get updated/deleted rows.
UPDATE users SET active = FALSE WHERE last_login < '2024-01-01' RETURNING id, name;

4.1 Primary Keys
Unique row identifiers.
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);
4.2 Foreign Keys
Enforce relationships between tables.
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);
4.3 Unique Constraints
Ensure uniqueness in columns.
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE(email);
4.4 NOT NULL Constraint
Columns must have values.
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);
4.5 Default Values
Set default values for columns.
CREATE TABLE users (
  id INT PRIMARY KEY,
  status VARCHAR(10) DEFAULT 'active'
);
4.6 CHECK Constraints
Validate data on insert/update.
ALTER TABLE products ADD CONSTRAINT check_price CHECK (price >= 0);
4.7 Indexes
Speed up query performance.
CREATE INDEX idx_name ON users(name);
4.8 Composite Keys
Use multiple columns as keys.
CREATE TABLE enrollment (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);
4.9 Auto-Increment & Sequences
Generate unique IDs automatically.
CREATE SEQUENCE user_seq START 1;
4.10 Dropping Constraints
Remove constraints.
ALTER TABLE users DROP CONSTRAINT unique_email;

5.1 INNER JOIN
Return matching rows between tables.
SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;
5.2 LEFT JOIN
Return all from left table, matched or NULL.
SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
5.3 RIGHT JOIN
Return all from right table, matched or NULL.
SELECT users.name, orders.amount
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
5.4 FULL OUTER JOIN
Return all rows from both tables.
SELECT users.name, orders.amount
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;
5.5 CROSS JOIN
Cartesian product of two tables.
SELECT users.name, products.name
FROM users
CROSS JOIN products;
5.6 SELF JOIN
Join table to itself.
SELECT a.name AS employee, b.name AS manager
FROM employees a
LEFT JOIN employees b ON a.manager_id = b.id;
5.7 Using Aliases in Joins
Simplify table names.
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;
5.8 Joining Multiple Tables
Join more than two tables.
SELECT u.name, o.amount, p.name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;
5.9 NATURAL JOIN
Join tables based on same column names.
SELECT * FROM orders NATURAL JOIN customers;
5.10 USING Clause
Specify join column explicitly.
SELECT * FROM orders JOIN customers USING (customer_id);

6.1 Aggregate Functions Overview
Functions like COUNT, SUM, AVG, MIN, MAX.
SELECT COUNT(*) FROM orders;
6.2 COUNT Function
Count rows or distinct values.
SELECT COUNT(DISTINCT user_id) FROM orders;
6.3 SUM Function
Sum numeric columns.
SELECT SUM(amount) FROM payments;
6.4 AVG Function
Calculate average.
SELECT AVG(price) FROM products;
6.5 MIN and MAX Functions
Find smallest and largest values.
SELECT MIN(age), MAX(age) FROM users;
6.6 GROUP BY Clause
Aggregate data by groups.
SELECT country, COUNT(*) FROM users GROUP BY country;
6.7 HAVING Clause
Filter groups after aggregation.
SELECT country, COUNT(*) FROM users GROUP BY country HAVING COUNT(*) > 10;
6.8 Combining GROUP BY with ORDER BY
Sort grouped results.
SELECT category, SUM(sales) FROM products GROUP BY category ORDER BY SUM(sales) DESC;
6.9 Using NULLs in Aggregations
How NULL values affect results.
SELECT COUNT(column) FROM table; -- counts non-null only
6.10 Scalar Functions
Use string, date, and numeric functions.
SELECT UPPER(name), LENGTH(name) FROM users;

7.1 Subqueries Basics
Queries within queries.
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);
7.2 Correlated Subqueries
Subquery depends on outer query.
SELECT name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
7.3 EXISTS Clause
Check existence of rows.
SELECT * FROM products p WHERE EXISTS (SELECT 1 FROM sales s WHERE s.product_id = p.id);
7.4 ANY and ALL Operators
Compare values with subquery.
SELECT * FROM orders WHERE amount > ANY (SELECT amount FROM refunds);
7.5 UNION and UNION ALL
Combine result sets.
SELECT name FROM customers
UNION
SELECT name FROM suppliers;
7.6 INTERSECT and EXCEPT
Find common or different rows.
SELECT id FROM users
INTERSECT
SELECT user_id FROM orders;
7.7 CASE Statements
Conditional logic in queries.
SELECT name,
  CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END AS age_group
FROM users;
7.8 COALESCE Function
Return first non-null value.
SELECT name, COALESCE(phone, 'No phone') FROM users;
7.9 CAST and CONVERT
Change data types.
SELECT CAST(price AS DECIMAL(10,2)) FROM products;
7.10 Window Functions Intro
Aggregate over partitions.
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;

8.1 What are Views?
Virtual tables based on queries.
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = TRUE;
8.2 Creating Views
Define reusable queries.
CREATE VIEW recent_orders AS
SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '7 days';
8.3 Updating Views
Modify view definitions.
CREATE OR REPLACE VIEW recent_orders AS
SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days';
8.4 Dropping Views
Remove views.
DROP VIEW IF EXISTS old_users;
8.5 What are Stored Procedures?
Predefined SQL code blocks.
CREATE PROCEDURE IncreasePrice(IN percent DECIMAL)
BEGIN
  UPDATE products SET price = price * (1 + percent);
END;
8.6 Creating Stored Procedures
Write reusable procedures.
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN userId INT)
BEGIN
  SELECT * FROM orders WHERE user_id = userId;
END //
DELIMITER ;
8.7 Calling Stored Procedures
Execute procedures.
CALL GetUserOrders(1);
8.8 Parameters in Procedures
Use IN, OUT, INOUT parameters.
CREATE PROCEDURE GetCount(OUT total INT)
BEGIN
  SELECT COUNT(*) INTO total FROM users;
END;
8.9 Stored Functions
Functions returning a value.
CREATE FUNCTION GetDiscount(price DECIMAL) RETURNS DECIMAL
BEGIN
  RETURN price * 0.1;
END;
8.10 Dropping Procedures & Functions
Remove stored code.
DROP PROCEDURE IF EXISTS IncreasePrice;
DROP FUNCTION IF EXISTS GetDiscount;

9.1 What is a Transaction?
A unit of work that is atomic.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
9.2 ACID Properties
Atomicity, Consistency, Isolation, Durability.
-- Ensured by DBMS internally
9.3 COMMIT and ROLLBACK
Save or undo changes.
ROLLBACK; -- Undo changes if error occurs
9.4 Isolation Levels
Read uncommitted, committed, repeatable read, serializable.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
9.5 Locking Mechanisms
Pessimistic and optimistic locking.
SELECT * FROM accounts FOR UPDATE;
9.6 Deadlocks
When transactions wait on each other.
-- Detect and resolve via timeout or retry
9.7 Savepoints
Partial rollbacks.
SAVEPOINT sp1;
ROLLBACK TO sp1;
9.8 Transaction Best Practices
Keep transactions short and consistent.
-- Avoid user interaction inside transactions
9.9 Concurrent Access
Handle multiple users accessing data.
-- Use proper isolation and locking
9.10 Monitoring Transactions
Track open and slow transactions.
SELECT * FROM pg_stat_activity WHERE state = 'active';

10.1 What is an Index?
Data structure to speed up queries.
CREATE INDEX idx_name ON users(name);
10.2 Types of Indexes
B-tree, Hash, Full-text, etc.
-- CREATE INDEX USING BTREE or HASH
10.3 Creating Indexes
Syntax and options.
CREATE UNIQUE INDEX idx_email ON users(email);
10.4 Dropping Indexes
Remove indexes.
DROP INDEX idx_name ON users;
10.5 EXPLAIN Query Plan
Analyze how queries run.
EXPLAIN SELECT * FROM orders WHERE amount > 100;
10.6 Optimizing SELECT Queries
Use indexes and avoid full scans.
SELECT * FROM users WHERE email = 'user@example.com';
10.7 Composite Indexes
Indexes on multiple columns.
CREATE INDEX idx_name_email ON users(name, email);
10.8 Indexing and JOINs
Improve join performance.
CREATE INDEX idx_user_id ON orders(user_id);
10.9 Query Caching
Cache results to speed up.
-- Depends on DBMS settings
10.10 Maintaining Indexes
Rebuild and analyze indexes.
ANALYZE TABLE users;

11.1 Backup Strategies
Full, incremental, differential backups.
pg_dump mydb > backup.sql
11.2 Restoring Databases
Restore from backups.
psql mydb < backup.sql
11.3 User Management
Create and manage users.
CREATE USER readonly WITH PASSWORD 'secret';
11.4 Granting Permissions
Control access rights.
GRANT SELECT ON users TO readonly;
11.5 Revoking Permissions
Remove rights.
REVOKE INSERT ON orders FROM readonly;
11.6 Roles and Groups
Organize users.
CREATE ROLE admin;
GRANT admin TO alice;
11.7 Encryption Basics
Encrypt data and connections.
-- Use SSL and data encryption functions
11.8 Auditing
Track changes and access.
-- Enable audit extensions or plugins
11.9 Security Best Practices
Strong passwords, least privilege.
ALTER USER admin WITH PASSWORD 'newstrongpassword';
11.10 Monitoring Security
Check logs and alerts.
tail -f /var/log/postgresql/postgresql.log

12.1 Analyzing Slow Queries
Use EXPLAIN and logs.
EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 1000;
12.2 Index Optimization
Create and drop indexes smartly.
CREATE INDEX idx_amount ON orders(amount);
12.3 Query Refactoring
Simplify queries for speed.
SELECT id, name FROM users WHERE active = TRUE;
12.4 Avoiding SELECT *
Select only needed columns.
SELECT name, email FROM users;
12.5 Use of LIMIT and OFFSET
Paginate results.
SELECT * FROM products LIMIT 10 OFFSET 20;
12.6 Optimize Joins
Index join columns.
CREATE INDEX idx_user_id ON orders(user_id);
12.7 Use Query Caching
Cache frequent queries.
-- Depends on DBMS caching
12.8 Partitioning Tables
Split large tables.
CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
12.9 Optimize Data Types
Use appropriate types.
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;
12.10 Monitoring Tools
Use DBMS monitoring tools.
-- Use tools like pgAdmin or MySQL Workbench

13.1 Common Data Types
Integer, varchar, date, etc.
CREATE TABLE users (id INT, name VARCHAR(100), birthdate DATE);
13.2 Numeric Types
INT, FLOAT, DECIMAL.
CREATE TABLE products (price DECIMAL(10,2));
13.3 Character Types
CHAR, VARCHAR, TEXT.
CREATE TABLE articles (content TEXT);
13.4 Date & Time Types
DATE, TIME, TIMESTAMP.
CREATE TABLE events (event_date TIMESTAMP);
13.5 NULL vs NOT NULL
Allow or disallow NULLs.
CREATE TABLE users (email VARCHAR(255) NOT NULL);
13.6 UNIQUE Constraint
Ensure unique values.
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE(email);
13.7 PRIMARY KEY
Uniquely identify rows.
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT
);
13.8 FOREIGN KEY
Enforce referential integrity.
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
13.9 CHECK Constraints
Validate data rules.
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 18);
13.10 Default Values
Set default data.
ALTER TABLE users ALTER COLUMN active SET DEFAULT TRUE;

14.1 Types of Backups
Full, incremental, differential.
pg_dump mydb > full_backup.sql
14.2 Backup Scheduling
Automate backups.
cron job to run pg_dump daily
14.3 Restoring from Backups
Use psql or mysql command.
psql mydb < full_backup.sql
14.4 Point-in-Time Recovery
Restore to specific time.
-- configure WAL archiving
14.5 Backup Compression
Save space.
pg_dump mydb | gzip > backup.sql.gz
14.6 Verifying Backups
Check backup integrity.
gunzip -t backup.sql.gz
14.7 Backup Security
Protect backup files.
chmod 600 backup.sql.gz
14.8 Disaster Recovery Planning
Prepare for failures.
-- Document recovery steps
14.9 Automated Recovery
Scripts for quick restore.
bash restore.sh
14.10 Backup Best Practices
Test restores regularly.
-- schedule regular test restores

15.1 Recursive Queries
Queries that reference themselves.
WITH RECURSIVE cte AS (
  SELECT id, parent_id FROM categories WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id FROM categories c JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;
15.2 JSON Data Types
Store JSON documents.
CREATE TABLE data (info JSON);
15.3 JSON Functions
Query JSON data.
SELECT info->'name' FROM data;
15.4 Full-Text Search
Search text efficiently.
CREATE INDEX idx_fts ON documents USING gin(to_tsvector('english', content));
15.5 Common Table Expressions (CTE)
Temporary named result sets.
WITH cte AS (SELECT * FROM users WHERE active = TRUE) SELECT * FROM cte;
15.6 Window Functions Advanced
ROW_NUMBER, RANK, LEAD, LAG.
SELECT name, ROW_NUMBER() OVER (ORDER BY score DESC) FROM players;
15.7 Pivot Tables
Transform rows into columns.
-- Use CASE with GROUP BY for pivot
15.8 Dynamic SQL
Build queries dynamically.
PREPARE stmt FROM 'SELECT * FROM users WHERE age > ?';
EXECUTE stmt USING @age;
15.9 Recursive Triggers
Triggers that call themselves.
-- Be careful to avoid infinite loops
15.10 SQL Extensions
Vendor-specific features.
-- PostgreSQL, MySQL, Oracle extensions

16.1 User Authentication
Control access by user credentials.
CREATE USER john WITH PASSWORD 'strongPass123';
16.2 Roles and Privileges
Assign permissions via roles.
CREATE ROLE manager;
GRANT SELECT, UPDATE ON employees TO manager;
GRANT manager TO john;
16.3 Granting Permissions
Allow users specific operations.
GRANT INSERT ON orders TO john;
16.4 Revoking Permissions
Remove previously granted rights.
REVOKE UPDATE ON employees FROM john;
16.5 Encryption Techniques
Encrypt data in transit and at rest.
-- Enable SSL connections for your DB
16.6 Auditing Database Activity
Track user actions and changes.
-- Use audit extensions or triggers
16.7 Data Masking
Hide sensitive info in queries.
SELECT name, '****' AS ssn FROM employees;
16.8 Compliance Standards
Follow GDPR, HIPAA, PCI DSS.
-- Enforce access policies per regulation
16.9 Secure Backup Practices
Encrypt and restrict backup files.
gpg --encrypt --recipient admin backup.sql
16.10 Incident Response
Procedures for security breaches.
-- Document and follow breach protocols

17.1 Enabling Query Logs
Log all queries for analysis.
SET log_statement = 'all';
17.2 Using EXPLAIN
Analyze query plans.
EXPLAIN SELECT * FROM orders WHERE status = 'shipped';
17.3 Using EXPLAIN ANALYZE
Get actual execution stats.
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';
17.4 Slow Query Logging
Log queries exceeding threshold.
SET log_min_duration_statement = 1000; -- ms
17.5 Monitoring Active Sessions
View currently running queries.
SELECT * FROM pg_stat_activity WHERE state = 'active';
17.6 Analyzing Locks
Find blocking transactions.
SELECT * FROM pg_locks WHERE granted = false;
17.7 Using Performance Views
Query DBMS performance stats.
SELECT * FROM pg_stat_user_tables;
17.8 Log Rotation & Management
Keep logs manageable.
-- Configure log rotation in postgresql.conf
17.9 Alerting on Errors
Set up notifications for issues.
-- Use monitoring tools like Nagios
17.10 Query Performance Tuning
Optimize based on log data.
-- Adjust indexes and rewrite queries

18.1 What is Data Warehousing?
Centralized data storage for analysis.
-- Collect data from multiple sources
18.2 Star Schema Design
Fact and dimension tables.
CREATE TABLE sales_fact (...);
CREATE TABLE product_dim (...);
18.3 Snowflake Schema
Normalized dimension tables.
-- Dimensions linked to sub-dimensions
18.4 ETL Basics
Extract, transform, load process.
-- Use tools like Talend or custom scripts
18.5 Extract Phase
Retrieve data from sources.
SELECT * FROM source_db.table;
18.6 Transform Phase
Clean and format data.
UPDATE staging SET col = TRIM(col);
18.7 Load Phase
Insert data into warehouse.
INSERT INTO warehouse.table SELECT * FROM staging;
18.8 Incremental Loading
Load only new data.
WHERE last_modified > last_load_date
18.9 Data Warehouse Optimization
Partitioning and indexing.
CREATE INDEX idx_date ON sales_fact(sale_date);
18.10 Data Warehouse Maintenance
Update and archive data.
DELETE FROM sales_fact WHERE sale_date < '2020-01-01';

19.1 Introduction to NoSQL
Non-relational data stores.
-- Examples: MongoDB, Cassandra
19.2 Differences Between SQL & NoSQL
Schema, scalability, transactions.
-- SQL: structured, ACID; NoSQL: flexible, BASE
19.3 NewSQL Overview
Relational DBs with NoSQL features.
-- Examples: Google Spanner, CockroachDB
19.4 Hybrid Architectures
Combining SQL and NoSQL.
-- Polyglot persistence
19.5 Integrating NoSQL Data
Querying NoSQL with SQL tools.
-- Use connectors or foreign data wrappers
19.6 JSON Support in SQL
Store and query JSON columns.
SELECT info->'key' FROM table WHERE info->>'status' = 'active';
19.7 Using Foreign Data Wrappers
Access external data sources.
CREATE FOREIGN TABLE nosql_table (...) SERVER nosql_server;
19.8 Data Sync Between SQL & NoSQL
Keep data consistent.
-- Use CDC or messaging queues
19.9 Transactions in NewSQL
ACID with scalability.
BEGIN TRANSACTION;
-- Operations
COMMIT;
19.10 Choosing the Right DB
Based on use case and data.
-- Analyze workload and requirements

20.1 Introduction to Cloud Databases
Hosted DB services.
-- Examples: AWS RDS, Azure SQL Database
20.2 Setting Up Cloud Instances
Provisioning databases.
-- Use cloud portals or CLI
20.3 Connecting to Cloud DBs
Configure clients.
psql -h cloud-db.example.com -U user -d dbname
20.4 Scaling Cloud Databases
Vertical and horizontal scaling.
-- Increase instance size or add read replicas
20.5 Cloud Backup & Recovery
Automated backups.
-- Use cloud snapshot features
20.6 Security in Cloud Databases
Network, encryption, IAM.
-- Use VPC, SSL, and IAM roles
20.7 Monitoring Cloud DB Performance
Cloud-native tools.
-- Use CloudWatch, Azure Monitor
20.8 Serverless SQL Databases
Pay-per-use, no infrastructure.
-- Examples: Amazon Aurora Serverless
20.9 Multi-region Deployments
Replicate data across regions.
-- Configure cross-region replicas
20.10 Migrating to Cloud
Lift and shift strategies.
-- Use migration services/tools

21.1 Using SQL Scripts
Automate queries.
-- save queries in .sql files and run
psql -f script.sql
21.2 Shell Scripting with SQL
Run SQL commands in shell scripts.
#!/bin/bash
psql -c "SELECT COUNT(*) FROM users;"
21.3 Scheduled Jobs
Automate tasks with cron or DB schedulers.
-- Use pgAgent or cron jobs
21.4 Dynamic SQL
Generate SQL code on the fly.
EXECUTE 'SELECT * FROM ' || table_name;
21.5 Error Handling in Scripts
Detect and react to errors.
BEGIN
  -- statements
EXCEPTION WHEN OTHERS THEN
  -- error handling
END;
21.6 Using Variables in Scripts
Store values for reuse.
DECLARE v_count INT;
SELECT COUNT(*) INTO v_count FROM users;
21.7 Logging in Scripts
Record script activity.
RAISE NOTICE 'Processed % rows', v_count;
21.8 Automation Best Practices
Test and document scripts.
-- Use version control for scripts
21.9 Integrating with Other Tools
Use SQL with Python, etc.
import psycopg2
21.10 Scheduling with Cloud Services
Use cloud task schedulers.
-- AWS Lambda + CloudWatch Events

22.1 Introduction to SQL Analytics
Use SQL for data analysis.
SELECT category, SUM(sales) FROM sales GROUP BY category;
22.2 Aggregate Functions
COUNT, SUM, AVG, MIN, MAX.
SELECT AVG(price) FROM products;
22.3 Window Functions
Running totals, ranks.
SELECT name, SUM(sales) OVER (PARTITION BY region) FROM sales_data;
22.4 CUBE and ROLLUP
Multi-level aggregation.
SELECT region, product, SUM(sales)
FROM sales
GROUP BY CUBE(region, product);
22.5 Pivot Tables
Convert rows to columns.
-- Use CASE WHEN inside aggregate
22.6 Common Table Expressions
Simplify complex queries.
WITH recent_sales AS (
  SELECT * FROM sales WHERE sale_date > CURRENT_DATE - INTERVAL '30 days'
) SELECT * FROM recent_sales;
22.7 Advanced Grouping Sets
Custom aggregations.
GROUP BY GROUPING SETS ((region), (product), ());
22.8 Generating Reports
Exporting query results.
COPY (SELECT * FROM sales) TO '/tmp/sales.csv' CSV HEADER;
22.9 Data Visualization Integration
Connect SQL results to tools.
-- Use BI tools like Tableau or PowerBI
22.10 Performance Tips for Analytics
Index and partition for large datasets.
CREATE INDEX idx_sale_date ON sales(sale_date);

23.1 Inner Join Recap
Rows matching in both tables.
SELECT * FROM orders o INNER JOIN customers c ON o.customer_id = c.id;
23.2 Left Join
All from left table, matched from right.
SELECT * FROM customers c LEFT JOIN orders o ON c.id = o.customer_id;
23.3 Right Join
All from right table, matched from left.
SELECT * FROM orders o RIGHT JOIN customers c ON o.customer_id = c.id;
23.4 Full Outer Join
All rows from both tables.
SELECT * FROM customers c FULL OUTER JOIN orders o ON c.id = o.customer_id;
23.5 Cross Join
Cartesian product.
SELECT * FROM colors CROSS JOIN sizes;
23.6 Self Join
Join table to itself.
SELECT e1.name, e2.name AS manager FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id;
23.7 Using UNION
Combine results, remove duplicates.
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
23.8 UNION ALL
Combine results, keep duplicates.
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;
23.9 INTERSECT
Common rows in both queries.
SELECT city FROM customers
INTERSECT
SELECT city FROM suppliers;
23.10 EXCEPT
Rows in first query not in second.
SELECT city FROM customers
EXCEPT
SELECT city FROM suppliers;

24.1 What are Triggers?
Auto execute on data changes.
CREATE TRIGGER update_timestamp BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_modified_column();
24.2 BEFORE & AFTER Triggers
Timing of execution.
-- BEFORE or AFTER INSERT/UPDATE/DELETE
24.3 Row-level vs Statement-level
Trigger on each row or per statement.
FOR EACH ROW or FOR EACH STATEMENT
24.4 Creating Trigger Functions
Code executed by triggers.
CREATE FUNCTION update_modified_column() RETURNS trigger AS $$
BEGIN
  NEW.modified = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
24.5 Dropping Triggers
Remove triggers.
DROP TRIGGER update_timestamp ON users;
24.6 Event Scheduling
Scheduled jobs in DB.
-- MySQL EVENT syntax
CREATE EVENT cleanup
ON SCHEDULE EVERY 1 DAY
DO DELETE FROM logs WHERE created < NOW() - INTERVAL 30 DAY;
24.7 Use Cases for Triggers
Auditing, validation, cascading.
-- Log changes or enforce business rules
24.8 Recursive Triggers
Triggers calling themselves.
-- Be careful to avoid infinite loops
24.9 Performance Impact
Triggers may slow DML.
-- Use sparingly and optimize logic
24.10 Best Practices
Keep triggers simple and documented.
-- Document trigger behavior clearly

25.1 Logical vs Physical Backups
Dump data vs copy files.
pg_dump vs filesystem backup
25.2 Point-in-Time Recovery (PITR)
Restore DB to a specific time.
-- Use WAL archiving and recovery.conf
25.3 Backup Compression & Encryption
Save space and secure data.
pg_dump db | gzip | gpg -c > backup.gz.gpg
25.4 Incremental Backups
Backup only changes.
-- Supported by some DBMS
25.5 Backup Verification
Ensure backups are valid.
pg_restore --list backup.dump
25.6 Automating Backups
Schedule regular backups.
cron job or scheduler
25.7 Restoring to Different Servers
Move backups between hosts.
pg_restore -h otherhost -d newdb backup.dump
25.8 Disaster Recovery Planning
Prepare for emergencies.
-- Document and test recovery procedures
25.9 Backup Best Practices
Keep multiple copies, offsite.
-- Rotate backups and test restores
25.10 Troubleshooting Backups
Fix common backup errors.
-- Check disk space, permissions

26.1 Planning a Migration
Assess environment and risks.
-- Document schema and dependencies
26.2 Schema Migration
Update structures.
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
26.3 Data Migration
Move and transform data.
INSERT INTO new_table SELECT * FROM old_table;
26.4 Application Migration
Update app DB connections.
-- Change connection strings
26.5 Rolling Upgrades
Upgrade with minimal downtime.
-- Use replicas and failover
26.6 Downtime Planning
Schedule and communicate downtime.
-- Notify users and backup DB
26.7 Testing Migration
Validate data and performance.
-- Use staging environment
26.8 Rollback Strategies
Return to previous state.
-- Backup before migration
26.9 Upgrade Tools
Use DBMS-specific utilities.
pg_upgrade or similar
26.10 Post-Migration Tasks
Rebuild indexes, update stats.
VACUUM ANALYZE;

27.1 What Are Extensions?
Add extra features to DB.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
27.2 Popular Extensions
UUID, PostGIS, pg_stat_statements.
CREATE EXTENSION postgis;
27.3 Installing Extensions
Use DBMS commands.
CREATE EXTENSION hstore;
27.4 Using Extensions
Functions and types.
SELECT uuid_generate_v4();
27.5 Writing Custom Extensions
For advanced users.
-- Write in C or PL languages
27.6 Managing Extensions
Update or remove.
DROP EXTENSION hstore;
27.7 Extension Compatibility
Check with DB version.
-- Verify before upgrading DB
27.8 Security Considerations
Trust and permissions.
-- Only trusted extensions
27.9 Extension Performance Impact
Monitor overhead.
-- Test before production use
27.10 Extension Best Practices
Document and version control.
-- Track installed extensions

28.1 Storing JSON Data
Use JSON or JSONB types.
CREATE TABLE config (data JSONB);
28.2 Querying JSON
Extract data from JSON.
SELECT data->'name' FROM config WHERE id = 1;
28.3 Updating JSON Fields
Modify parts of JSON.
UPDATE config SET data = jsonb_set(data, '{age}', '30') WHERE id = 1;
28.4 Storing XML Data
Use XML data type.
CREATE TABLE xml_data (content XML);
28.5 Querying XML
XPath and extraction.
SELECT xpath('/person/name/text()', content) FROM xml_data;
28.6 Validating JSON/XML
Check data format.
-- Use constraints or external validation
28.7 Indexing JSON/XML
Improve query speed.
CREATE INDEX idx_json ON config USING gin(data);
28.8 Converting JSON & XML
Transform between formats.
SELECT jsonb_to_xml(data) FROM config;
28.9 Performance Considerations
Size and complexity impact.
-- Use JSONB for better performance
28.10 Use Cases
Flexible schemas, APIs.
-- Store semi-structured data efficiently

29.1 Monitoring Tools Overview
Available DB monitoring solutions.
-- Use pgAdmin, Zabbix, Prometheus
29.2 Setting Threshold Alerts
Notify on key metrics.
-- Alert if CPU > 80% or disk < 10%
29.3 Monitoring Query Performance
Track slow queries.
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
29.4 Monitoring Locks & Deadlocks
Detect blocking.
SELECT * FROM pg_locks WHERE NOT granted;
29.5 Alerting on Failures
Setup notifications.
-- Use email or Slack alerts
29.6 Monitoring Replication Lag
Check replication delays.
SELECT pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn();
29.7 Monitoring Disk Usage
Check space consumed.
SELECT pg_size_pretty(pg_database_size('mydb'));
29.8 Log Analysis Automation
Parse logs for issues.
-- Use ELK stack or similar tools
29.9 Custom Metrics
Track application-specific data.
-- Use extensions or custom queries
29.10 Continuous Improvement
Use monitoring feedback.
-- Adjust queries and configs regularly

30.1 Cloud-native Databases
Designed for the cloud environment.
-- Examples: Google Spanner, Azure Cosmos DB
30.2 AI & Machine Learning Integration
Embed AI workflows with SQL.
-- Use ML models inside DBMS or via extensions
30.3 Blockchain & SQL
Immutable ledgers with SQL querying.
-- Hybrid blockchain-SQL solutions
30.4 Graph Databases
Query graph data alongside SQL.
-- Use extensions like pgGraph
30.5 Multi-Model Databases
Support for relational, document, graph.
-- Examples: ArangoDB, Cosmos DB
30.6 Edge Computing & Databases
Databases running closer to users.
-- Lightweight DBs on edge devices
30.7 Automation & Self-tuning
DB tuning with AI.
-- Auto indexing and query optimization
30.8 Serverless Databases
On-demand DB resources.
-- Examples: AWS Aurora Serverless
30.9 Quantum Computing & Databases
Future possibilities.
-- Research ongoing
30.10 Preparing for Change
Keep skills and systems updated.
-- Continuous learning and adaptation

31.1 Understanding SQL Injection
Malicious injection attacks.
-- Avoid by using prepared statements
PREPARE stmt AS SELECT * FROM users WHERE username = $1;
EXECUTE stmt('admin');
31.2 User Authentication
Managing DB user access.
CREATE USER app_user WITH PASSWORD 'securePass123';
31.3 Role-Based Access Control
Assign permissions via roles.
CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
31.4 Granting & Revoking Permissions
Control user actions.
GRANT INSERT, UPDATE ON orders TO app_user;
REVOKE DELETE ON orders FROM app_user;
31.5 Using Views for Security
Limit access via views.
CREATE VIEW public_order_view AS SELECT id, total FROM orders;
GRANT SELECT ON public_order_view TO read_only;
31.6 Encrypting Data at Rest
Secure storage.
-- Use Transparent Data Encryption (TDE) features in DBMS
31.7 Encrypting Data in Transit
Secure communication.
-- Enable SSL connections in PostgreSQL config
31.8 Auditing & Logging
Track DB activities.
-- Enable logging in postgresql.conf:
log_statement = 'all'
31.9 SQL Security Best Practices
Secure coding and configs.
-- Use least privilege principle
31.10 Incident Response
Handling breaches.
-- Review logs and revoke compromised credentials

32.1 Understanding Query Execution Plans
Visualize how queries run.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1;
32.2 Using Indexes Effectively
Speed up searches.
CREATE INDEX idx_customer ON orders(customer_id);
32.3 Avoiding Full Table Scans
Optimize filters.
-- Use indexed columns in WHERE clauses
SELECT * FROM orders WHERE customer_id = 5;
32.4 Analyzing Slow Queries
Identify bottlenecks.
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
32.5 Optimizing JOIN Operations
Use appropriate joins and indexes.
EXPLAIN SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
32.6 Using LIMIT & OFFSET
Reduce result size.
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10 OFFSET 20;
32.7 Query Caching
Cache frequent queries.
-- Use materialized views or cache layers
CREATE MATERIALIZED VIEW recent_orders AS SELECT * FROM orders WHERE order_date > NOW() - INTERVAL '1 day';
32.8 Database Statistics & VACUUM
Keep DB stats updated.
VACUUM ANALYZE;
32.9 Connection Pooling
Manage DB connections.
-- Use PgBouncer or similar tools
32.10 Monitoring Performance
Track DB health.
SELECT * FROM pg_stat_activity;

33.1 What is an Index?
Data structure to speed queries.
CREATE INDEX idx_name ON customers(name);
33.2 Types of Indexes
B-tree, Hash, GIN, GiST.
CREATE INDEX idx_gin ON documents USING gin(to_tsvector('english', content));
33.3 When to Use Indexes
Columns used in WHERE, JOIN, ORDER BY.
CREATE INDEX idx_order_date ON orders(order_date);
33.4 Composite Indexes
Index multiple columns.
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);
33.5 Unique Indexes
Enforce uniqueness.
CREATE UNIQUE INDEX idx_email ON users(email);
33.6 Partial Indexes
Index part of table.
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
33.7 Index Maintenance
Rebuild and analyze indexes.
REINDEX INDEX idx_customer_date;
33.8 Downsides of Indexes
Slower writes, storage cost.
-- Avoid excessive indexes on write-heavy tables
33.9 Using EXPLAIN with Indexes
Verify index usage.
EXPLAIN SELECT * FROM orders WHERE customer_id = 10;
33.10 Indexing Best Practices
Balance read/write needs.
-- Monitor and tune regularly

34.1 What Are Stored Procedures?
Precompiled SQL routines.
CREATE PROCEDURE add_order(customer_id INT, amount DECIMAL) AS $$
BEGIN
  INSERT INTO orders(customer_id, total) VALUES (customer_id, amount);
END;
$$ LANGUAGE plpgsql;
34.2 Creating Functions
Return values from SQL.
CREATE FUNCTION get_total_orders(customer_id INT) RETURNS INT AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM orders WHERE customer_id = customer_id);
END;
$$ LANGUAGE plpgsql;
34.3 Procedure vs Function
Procedures do not return values; functions do.
-- CALL procedure; SELECT function();
34.4 Parameters & Variables
Use input/output parameters.
DECLARE total_orders INT;
SELECT COUNT(*) INTO total_orders FROM orders WHERE customer_id = 1;
34.5 Control Structures
IF, CASE, LOOP, WHILE.
IF total_orders > 10 THEN
  RAISE NOTICE 'High order count';
END IF;
34.6 Error Handling
Use EXCEPTION blocks.
BEGIN
  -- statements
EXCEPTION WHEN others THEN
  RAISE WARNING 'Error occurred';
END;
34.7 Returning Result Sets
Use RETURNS TABLE or SETOF.
CREATE FUNCTION get_orders() RETURNS TABLE(id INT, total DECIMAL) AS $$
BEGIN
  RETURN QUERY SELECT id, total FROM orders;
END;
$$ LANGUAGE plpgsql;
34.8 Debugging Procedures
Use RAISE NOTICE for output.
RAISE NOTICE 'Value: %', variable;
34.9 Performance Considerations
Avoid long transactions.
-- Keep procedures efficient
34.10 Best Practices
Modular, documented code.
-- Comment and version control

35.1 What is a Transaction?
A unit of work.
BEGIN;
-- multiple statements
COMMIT;
35.2 ACID Properties
Atomicity, Consistency, Isolation, Durability.
-- Guarantees of transactions
35.3 Isolation Levels
Read Uncommitted, Read Committed, Repeatable Read, Serializable.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
35.4 Locking Mechanisms
Row-level and table-level locks.
SELECT * FROM orders FOR UPDATE;
35.5 Deadlocks
Avoid circular waits.
-- Detect and resolve deadlocks
35.6 Handling Rollbacks
Undo changes.
ROLLBACK;
35.7 Savepoints
Partial rollback points.
SAVEPOINT sp1;
-- statements
ROLLBACK TO sp1;
35.8 Optimistic vs Pessimistic Concurrency
Different locking strategies.
-- Choose strategy based on workload
35.9 Monitoring Locks
View active locks.
SELECT * FROM pg_locks WHERE NOT granted;
35.10 Best Practices
Keep transactions short.
-- Avoid user interaction inside transactions

36.1 What is a Data Warehouse?
Centralized data storage for analysis.
-- Large scale DB optimized for reads
36.2 Star Schema
Fact and dimension tables.
CREATE TABLE sales_fact (...);
CREATE TABLE dim_date (...);
36.3 Snowflake Schema
Normalized dimension tables.
-- More complex schema normalization
36.4 ETL Processes
Extract, Transform, Load.
-- Use tools like Apache NiFi or custom scripts
36.5 OLAP vs OLTP
Analytical vs transactional systems.
-- OLAP for complex queries, OLTP for transactions
36.6 Partitioning Tables
Split large tables.
CREATE TABLE sales PARTITION BY RANGE(order_date);
36.7 Materialized Views in Warehousing
Cache aggregates.
CREATE MATERIALIZED VIEW monthly_sales AS SELECT date_trunc('month', order_date), SUM(total) FROM sales GROUP BY 1;
36.8 Data Warehouse Performance
Indexing and compression.
-- Use columnar storage for analytics
36.9 Data Governance
Quality and compliance.
-- Audit trails and data validation
36.10 Future of Data Warehousing
Cloud and real-time warehousing.
-- Use Snowflake, BigQuery, Redshift

37.1 Introduction to Big Data
Handling huge datasets.
-- Use Hadoop, Spark alongside SQL
37.2 SQL on Hadoop
Tools like Hive, Impala.
SELECT * FROM hive_table WHERE date = '2024-01-01';
37.3 Using Spark SQL
Query distributed data.
spark.sql("SELECT * FROM sales WHERE region = 'NA'");
37.4 Data Lakes & SQL
Store raw data.
-- Query Parquet or ORC files with SQL engines
37.5 Streaming Data & SQL
Real-time analysis.
-- Use Apache Kafka + ksqlDB
37.6 Integration Challenges
Schema evolution, latency.
-- Handle schema changes carefully
37.7 Cloud Big Data Services
Managed services.
-- AWS Athena, Google BigQuery
37.8 Security in Big Data
Access controls.
-- Use encryption and IAM roles
37.9 Query Optimization on Big Data
Cost-based optimization.
-- Use partition pruning and predicate pushdown
37.10 Future Trends
AI and automation.
-- Automated query tuning and data cataloging

38.1 Types of Backups
Full, incremental, differential.
pg_dump for full backup;
pg_dump -F c for compressed backups
38.2 Backup Scheduling
Automate with cron.
0 2 * * * pg_dump mydb > /backups/mydb_$(date +\%F).sql
38.3 Backup Verification
Check backup integrity.
pg_restore --list backup_file
38.4 Restoring Databases
Recover from backups.
pg_restore -d newdb backup_file
38.5 Point-in-Time Recovery
Restore DB state at a time.
-- Use WAL logs and recovery.conf
38.6 Backup Encryption
Secure stored backups.
pg_dump mydb | gzip | gpg -c > backup.gz.gpg
38.7 Cloud Backup Solutions
Use cloud storage.
-- AWS S3, Azure Blob Storage
38.8 Disaster Recovery Planning
Prepare for failures.
-- Document recovery steps and test
38.9 Backup Automation Tools
Use scripts and utilities.
-- pgBackRest, Barman
38.10 Backup Best Practices
Multiple copies, offsite.
-- Rotate backups regularly

39.1 What is Replication?
Copying data to multiple servers.
-- Master-slave replication example
39.2 Synchronous vs Asynchronous
Different replication modes.
-- Synchronous waits for confirmation, async does not
39.3 Setting up Streaming Replication
Continuous data streaming.
-- Configure primary and standby servers
39.4 Logical Replication
Replicate specific tables.
CREATE PUBLICATION my_pub FOR TABLE orders;
CREATE SUBSCRIPTION my_sub CONNECTION 'conninfo' PUBLICATION my_pub;
39.5 Replication Monitoring
Track lag and status.
SELECT * FROM pg_stat_replication;
39.6 Failover & Switchover
Promote standby to primary.
-- Use pg_ctl promote
39.7 Replication Lag Causes
Network, load issues.
-- Monitor with pg_stat_replication
39.8 Conflict Resolution
Handling data conflicts.
-- Use application logic or conflict handlers
39.9 Replication Security
Secure connections.
-- Use SSL and firewall rules
39.10 Replication Best Practices
Test regularly.
-- Monitor and document setup

40.1 What is High Availability?
Minimize downtime.
-- Use clustering and failover solutions
40.2 Clustering Basics
Grouping DB servers.
-- Use Patroni, Pacemaker
40.3 Load Balancing
Distribute client connections.
-- Use Pgpool-II or HAProxy
40.4 Automatic Failover
Switch to standby on failure.
-- Configure tools like repmgr or Patroni
40.5 Data Consistency
Ensure sync between nodes.
-- Use synchronous replication
40.6 Monitoring HA Systems
Track cluster health.
-- Use Prometheus, Grafana
40.7 Backup in HA Environments
Coordinate backups.
-- Backup from standby to reduce load
40.8 Scaling Clusters
Add nodes as needed.
-- Use horizontal scaling methods
40.9 Security in HA Clusters
Secure cluster communication.
-- Use encrypted connections and authentication
40.10 HA Best Practices
Test failover scenarios.
-- Document and rehearse disaster recovery

41.1 Data Masking
Hide sensitive info.
SELECT phone, '****-****' AS masked_ssn FROM users;
41.2 Row Level Security
Control access by row.
CREATE POLICY user_policy ON orders FOR SELECT USING (user_id = current_user_id());
41.3 Transparent Data Encryption
Encrypt data at rest.
-- Enable TDE feature in DBMS
41.4 Auditing with Triggers
Log changes.
CREATE TRIGGER audit_order AFTER INSERT OR UPDATE ON orders FOR EACH ROW EXECUTE PROCEDURE audit_function();
41.5 SQL Injection Prevention Techniques
Parameterized queries.
PREPARE stmt AS SELECT * FROM users WHERE email = $1;
EXECUTE stmt('user@example.com');
41.6 Secure Coding Practices
Validate input.
-- Use input sanitization functions
41.7 Encryption Key Management
Secure keys.
-- Use KMS or hardware security modules
41.8 Compliance Standards
GDPR, HIPAA, PCI DSS.
-- Document compliance efforts
41.9 Incident Detection & Response
Monitor suspicious activity.
-- Use alerts on unusual queries
41.10 Security Automation
Automate audits.
-- Use scripts and tools for regular checks

42.1 Window Functions
Perform calculations over partitions.
SELECT customer_id, order_date, SUM(total) OVER (PARTITION BY customer_id) FROM orders;
42.2 Common Table Expressions (CTEs)
Temporary named result sets.
WITH recent_orders AS (SELECT * FROM orders WHERE order_date > NOW() - INTERVAL '30 days')
SELECT * FROM recent_orders;
42.3 Recursive Queries
Query hierarchical data.
WITH RECURSIVE subordinates AS (
  SELECT id, manager_id FROM employees WHERE id = 1
  UNION
  SELECT e.id, e.manager_id FROM employees e JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;
42.4 Pivoting Data
Transform rows to columns.
-- Use CASE statements for pivot
SELECT
  product,
  SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan,
  SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb
FROM sales_data GROUP BY product;
42.5 Lateral Joins
Join with subqueries.
SELECT c.name, o.total FROM customers c CROSS JOIN LATERAL (
  SELECT total FROM orders WHERE customer_id = c.id ORDER BY order_date DESC LIMIT 1
) o;
42.6 JSON & XML Querying
Query semi-structured data.
SELECT data->'name' FROM json_table WHERE id = 1;
42.7 Full Text Search
Text search capabilities.
SELECT * FROM documents WHERE to_tsvector(content) @@ to_tsquery('database');
42.8 Dynamic SQL
Construct SQL dynamically.
EXECUTE 'SELECT * FROM ' || tablename;
42.9 Error Handling in Queries
Use TRY/CATCH in PL/pgSQL.
BEGIN
  -- statements
EXCEPTION WHEN others THEN
  -- handle error
END;
42.10 Query Optimization Tips
Use indexes and avoid costly operations.
-- Avoid SELECT * and functions in WHERE clauses

43.1 Temporal Data Types
DATE, TIME, TIMESTAMP.
CREATE TABLE events (event_time TIMESTAMP);
43.2 Time Zones in SQL
Store and convert timezones.
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
43.3 Interval Data Types
Represent durations.
SELECT NOW() + INTERVAL '1 day';
43.4 Temporal Queries
Query by time ranges.
SELECT * FROM orders WHERE order_date > NOW() - INTERVAL '7 days';
43.5 Valid Time vs Transaction Time
Bi-temporal data.
-- Track data validity and changes
43.6 Temporal Tables
Store history.
-- Use triggers or system-versioned tables
43.7 Working with Timestamps
Formatting and functions.
SELECT TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI:SS') FROM orders;
43.8 Temporal Aggregations
Summarize by time.
SELECT DATE_TRUNC('month', order_date), COUNT(*) FROM orders GROUP BY 1;
43.9 Performance Considerations
Index on time columns.
CREATE INDEX idx_order_date ON orders(order_date);
43.10 Use Cases
Auditing, financial systems.
-- Use temporal data for compliance

44.1 Geospatial Data Types
POINT, LINESTRING, POLYGON.
CREATE TABLE locations (geom geometry(Point, 4326));
44.2 Using PostGIS
Spatial extension for PostgreSQL.
CREATE EXTENSION postgis;
44.3 Spatial Queries
Distance, intersection.
SELECT ST_Distance(geom1, geom2) FROM locations;
44.4 Indexing Geospatial Data
Use GiST or SP-GiST.
CREATE INDEX idx_locations_geom ON locations USING gist(geom);
44.5 Importing Spatial Data
Use shp2pgsql or ogr2ogr.
shp2pgsql -I myshape.shp locations | psql mydb
44.6 Visualizing Data
Use GIS tools.
-- Use QGIS or similar
44.7 Geospatial Functions
Buffer, intersects, contains.
SELECT ST_Buffer(geom, 100) FROM locations;
44.8 Geospatial Data Applications
Mapping, navigation.
-- Analyze spatial relationships
44.9 Performance Tips
Use indexes and optimize queries.
EXPLAIN ANALYZE SELECT * FROM locations WHERE ST_Intersects(geom, ST_MakeEnvelope(...));
44.10 Future of Geospatial SQL
Real-time location analytics.
-- Integration with IoT and AI

45.1 SQL vs NoSQL Overview
Differences and use cases.
-- Relational vs document/key-value stores
45.2 Using JSON in SQL
Semi-structured data support.
CREATE TABLE users (profile JSONB);
45.3 Foreign Data Wrappers (FDW)
Access external NoSQL from SQL.
CREATE EXTENSION mongo_fdw;
45.4 Querying NoSQL via SQL
Use FDWs or connectors.
SELECT * FROM mongo_collection;
45.5 Syncing SQL & NoSQL
Data synchronization strategies.
-- Use CDC tools or custom scripts
45.6 Data Modeling Differences
Relational vs document models.
-- Normalize SQL; denormalize NoSQL
45.7 Use Cases for Integration
Hybrid applications.
-- Use SQL for transactions, NoSQL for flexibility
45.8 Challenges in Integration
Schema mismatch, latency.
-- Plan schema mapping carefully
45.9 Tools & Connectors
Available integrations.
-- Debezium, Kafka Connect
45.10 Future Trends
Unified multi-model DBs.
-- Emerging multi-model systems like ArangoDB

46.1 Importance of Data Modeling
Foundation of good DB design.
-- Plan entities and relationships
46.2 Normalization Forms
1NF, 2NF, 3NF and beyond.
-- Eliminate redundancy and dependencies
46.3 Denormalization
Improve read performance.
-- Duplicate data for faster queries
46.4 Entity-Relationship Diagrams (ERDs)
Visualize data models.
-- Use tools like draw.io or ERDPlus
46.5 Handling Many-to-Many Relationships
Use join tables.
CREATE TABLE order_products (
  order_id INT,
  product_id INT,
  PRIMARY KEY(order_id, product_id)
);
46.6 Naming Conventions
Consistency is key.
-- Use singular table names, snake_case columns
46.7 Using Surrogate Keys
Auto-increment IDs.
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT
);
46.8 Handling Nulls
Design with nullable fields.
-- Use NOT NULL where possible
46.9 Documentation & Versioning
Keep schema docs updated.
-- Use tools like Liquibase or Flyway
46.10 Data Modeling Tools
Software for modeling.
-- ER/Studio, MySQL Workbench

47.1 EXPLAIN & EXPLAIN ANALYZE
View query plans.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 10;
47.2 pg_stat_statements
Query performance stats.
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
47.3 Auto_explain Module
Log slow queries.
-- Enable auto_explain in postgresql.conf
47.4 Using Index Advisors
Suggestions for indexing.
-- Use third-party tools like pganalyze
47.5 Query Profilers
Detailed query metrics.
-- Use tools like pgBadger
47.6 Performance Dashboards
Visual monitoring.
-- Grafana dashboards connected to Prometheus
47.7 Logging & Monitoring
Track DB health.
-- Configure logging settings
47.8 Load Testing
Simulate workload.
-- Use tools like pgbench
47.9 Query Rewriting
Optimize by rewriting queries.
-- Replace correlated subqueries with joins
47.10 Best Practices
Regularly review queries.
-- Analyze and tune queries periodically

48.1 Incremental Backups
Save only changes.
-- Use pgBackRest for incremental backups
48.2 Logical vs Physical Backups
Differences explained.
-- pg_dump (logical), pg_basebackup (physical)
48.3 Continuous Archiving
WAL log archiving.
archive_command = 'cp %p /mnt/server/archivedir/%f'
48.4 PITR Setup
Point-In-Time Recovery.
-- Setup recovery.conf for PITR
48.5 Backup Testing
Validate backups.
-- Restore backups regularly to test
48.6 Disaster Recovery Plans
Document procedures.
-- Include backup and failover steps
48.7 Automating Backups
Use cron or tools.
-- Schedule pg_dump scripts
48.8 Backup Compression
Save storage.
pg_dump mydb | gzip > backup.sql.gz
48.9 Backup Encryption
Secure sensitive data.
gpg -c backup.sql.gz
48.10 Monitoring Backup Jobs
Ensure backups run.
-- Use monitoring tools or scripts

49.1 Aggregation Functions
SUM, AVG, COUNT, MAX, MIN.
SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id;
49.2 Grouping Sets
Multiple groupings.
SELECT product, region, SUM(sales) FROM sales_data GROUP BY GROUPING SETS ((product), (region));
49.3 Rollup & Cube
Hierarchical aggregations.
SELECT product, region, SUM(sales) FROM sales_data GROUP BY ROLLUP(product, region);
49.4 Window Aggregates
Running totals.
SELECT order_date, SUM(total) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM orders;
49.5 Data Visualization Tools
Connect SQL to BI.
-- Use Tableau, Power BI, or Metabase
49.6 Exporting Reports
CSV, Excel formats.
COPY (SELECT * FROM sales) TO '/tmp/sales.csv' CSV HEADER;
49.7 Scheduled Reporting
Automate reports.
-- Use cron jobs or DB scheduler
49.8 SQL for Data Science
Use SQL for prep.
-- Clean and aggregate data for ML
49.9 Handling Large Datasets
Optimize analytics queries.
-- Use partitions and indexes
49.10 Reporting Best Practices
Accuracy and clarity.
-- Validate data and format reports properly

50.1 AI in SQL Databases
AI-powered query optimization.
-- Some DBs use ML for query tuning
50.2 Blockchain & SQL
Integrate immutable ledgers.
-- Use SQL to query blockchain data
50.3 Graph SQL Extensions
Graph data with SQL.
-- Use extensions like Apache AGE
50.4 Cloud-Native SQL Databases
Scalable cloud DBs.
-- Use Google Spanner, AWS Aurora
50.5 Multi-Model Databases
Support SQL & NoSQL.
-- Examples: ArangoDB, Cosmos DB
50.6 Serverless SQL
Pay per query.
-- Use services like AWS Athena
50.7 Edge SQL Databases
Databases on edge devices.
-- Lightweight DBs for IoT
50.8 Quantum Computing & SQL
Future tech.
-- Research stage integration
50.9 Data Privacy Enhancements
Differential privacy.
-- Anonymize data during queries
50.10 Future Trends Summary
Continuous innovation.
-- Stay updated with DB tech evolution

51.1 What is Data Warehousing?
Central repository for integrated data.
-- Combine data from multiple sources for analysis
51.2 ETL Processes
Extract, transform, load data.
-- Use tools like Apache NiFi or SSIS for ETL
51.3 Star Schema Design
Fact and dimension tables.
CREATE TABLE sales_fact (id INT, product_id INT, date_id INT, amount DECIMAL);
CREATE TABLE product_dim (product_id INT PRIMARY KEY, name TEXT);
51.4 Snowflake Schema
Normalized dimension tables.
-- Dimension tables are normalized into multiple related tables
51.5 Data Partitioning
Improve query performance.
CREATE TABLE sales PARTITION BY RANGE (sale_date);
51.6 Materialized Views
Precompute and store query results.
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) FROM sales GROUP BY month;
51.7 OLAP vs OLTP
Analytical vs transactional systems.
-- OLAP optimized for queries and reporting
51.8 Data Warehouse Tools
Popular platforms.
-- Examples: Amazon Redshift, Snowflake, Google BigQuery
51.9 Slowly Changing Dimensions
Track dimension changes over time.
-- Use type 2 SCD with history tables
51.10 Performance Tuning
Indexes, compression, and caching.
CREATE INDEX idx_sales_date ON sales(sale_date);

52.1 Introduction to Cloud Databases
Hosted DBs accessible over the internet.
-- Cloud providers: AWS RDS, Azure SQL Database
52.2 Benefits of Cloud SQL
Scalability, availability, managed services.
-- Automatic backups and failover
52.3 Connecting to Cloud DBs
Use connection strings securely.
psql "host=mycloudhost dbname=mydb user=myuser password=mypass sslmode=require"
52.4 Cloud Security Practices
Encryption, IAM roles, firewalls.
-- Enable SSL connections and least privilege access
52.5 Backup & Restore in Cloud
Automated snapshots.
-- Use cloud console or CLI tools to create backups
52.6 Scaling Cloud Databases
Vertical and horizontal scaling.
-- Increase instance size or add read replicas
52.7 Serverless SQL Options
Query data without managing servers.
-- AWS Athena, Google BigQuery
52.8 Monitoring & Alerts
Track performance and errors.
-- Use CloudWatch or Azure Monitor
52.9 Cost Management
Optimize spending.
-- Use reserved instances or auto pause features
52.10 Future of Cloud SQL
Trends like multi-cloud and AI integration.
-- Growing use of AI and serverless architectures

53.1 What are Stored Procedures?
Precompiled SQL code blocks.
CREATE PROCEDURE update_stock()
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE products SET stock = stock - 1 WHERE id = 101;
END;
$$;
53.2 Creating Functions
Return values and reusable logic.
CREATE FUNCTION get_total_sales() RETURNS INT AS $$
DECLARE total INT;
BEGIN
  SELECT SUM(amount) INTO total FROM sales;
  RETURN total;
END;
$$ LANGUAGE plpgsql;
53.3 Procedure Parameters
Input and output params.
CREATE PROCEDURE log_event(event_text TEXT) AS $$
BEGIN
  INSERT INTO logs(description) VALUES (event_text);
END;
$$ LANGUAGE plpgsql;
53.4 Calling Procedures & Functions
Use CALL or SELECT.
CALL update_stock();
SELECT get_total_sales();
53.5 Exception Handling
Catch errors within procedures.
BEGIN
  -- code
EXCEPTION WHEN OTHERS THEN
  RAISE NOTICE 'Error occurred';
END;
53.6 Transaction Control
Commit or rollback.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
53.7 Performance Tips
Avoid unnecessary loops.
-- Use set-based operations over loops
53.8 Debugging Stored Code
Use RAISE NOTICE.
RAISE NOTICE 'Variable value: %', var;
53.9 Versioning Stored Procedures
Manage changes.
-- Keep scripts in version control
53.10 Best Practices
Keep procedures small and modular.
-- Document and test procedures thoroughly

54.1 Indexing Strategies
B-tree, Hash, GiST indexes.
CREATE INDEX idx_name ON users(name);
54.2 Query Plan Analysis
Use EXPLAIN.
EXPLAIN SELECT * FROM orders WHERE status = 'shipped';
54.3 Optimizing Joins
Use appropriate join types.
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
54.4 Avoiding Full Table Scans
Use indexes.
-- Add indexes on frequently queried columns
54.5 Caching Query Results
Materialized views and caching.
REFRESH MATERIALIZED VIEW monthly_sales;
54.6 Connection Pooling
Manage DB connections efficiently.
-- Use PgBouncer or similar tools
54.7 Partitioning Large Tables
Improve query speed.
CREATE TABLE logs PARTITION BY RANGE (log_date);
54.8 Statistics & Histograms
Help optimizer choose plans.
ANALYZE orders;
54.9 Parallel Query Execution
Use multiple CPU cores.
-- Enable parallel queries in config
54.10 Monitoring Tools
Track performance metrics.
-- Use pg_stat_statements or pgBadger

55.1 User Authentication
Control DB access.
CREATE USER app_user WITH PASSWORD 'securepass';
55.2 Role-Based Access Control (RBAC)
Assign permissions by roles.
CREATE ROLE readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
55.3 Encryption at Rest
Protect stored data.
-- Enable TDE or disk encryption
55.4 Encryption in Transit
Use SSL/TLS.
-- Configure DB for SSL connections
55.5 SQL Injection Prevention
Use parameterized queries.
PREPARE stmt AS SELECT * FROM users WHERE email = $1;
EXECUTE stmt('test@example.com');
55.6 Auditing & Logging
Track access and changes.
-- Enable audit logs in DBMS
55.7 Regular Security Updates
Patch DB software.
-- Schedule regular updates
55.8 Password Policies
Enforce strong passwords.
-- Use password complexity rules
55.9 Least Privilege Principle
Minimal permissions.
REVOKE ALL ON mydb FROM PUBLIC;
55.10 Security Automation
Automate vulnerability scans.
-- Use tools like SQLMap and automated scripts

56.1 INNER JOIN
Returns matching rows from both tables.
SELECT * FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;
56.2 LEFT JOIN
Returns all rows from left table plus matches.
SELECT * FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
56.3 RIGHT JOIN
Returns all rows from right table plus matches.
SELECT * FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.id;
56.4 FULL OUTER JOIN
Returns all rows when there is a match in either.
SELECT * FROM customers
FULL OUTER JOIN orders ON customers.id = orders.customer_id;
56.5 CROSS JOIN
Returns Cartesian product.
SELECT * FROM colors
CROSS JOIN sizes;
56.6 SELF JOIN
Join table with itself.
SELECT a.name AS employee, b.name AS manager
FROM employees a
JOIN employees b ON a.manager_id = b.id;
56.7 USING Clause
Join on columns with same name.
SELECT * FROM orders
JOIN customers USING(customer_id);
56.8 NATURAL JOIN
Automatically join on same named columns.
SELECT * FROM orders
NATURAL JOIN customers;
56.9 JOIN with Aggregate
Join and group data.
SELECT c.name, COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;
56.10 JOIN Performance Tips
Index join keys.
CREATE INDEX idx_customer_id ON orders(customer_id);

57.1 What are Views?
Virtual tables based on queries.
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';
57.2 Creating Views
Simplify complex queries.
CREATE VIEW recent_orders AS
SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days';
57.3 Updating Views
Modify underlying query.
CREATE OR REPLACE VIEW active_users AS
SELECT * FROM users WHERE active = TRUE;
57.4 Indexed Views
Materialized views for performance.
-- Supported in some DBs like SQL Server
CREATE UNIQUE CLUSTERED INDEX idx_view ON active_users (user_id);
57.5 Creating Indexes
Improve query speed.
CREATE INDEX idx_lastname ON users(last_name);
57.6 Composite Indexes
Index multiple columns.
CREATE INDEX idx_name_dob ON users(last_name, date_of_birth);
57.7 Unique Indexes
Enforce uniqueness.
CREATE UNIQUE INDEX idx_email ON users(email);
57.8 Partial Indexes
Index subset of rows.
CREATE INDEX idx_active_users ON users(email) WHERE active = TRUE;
57.9 Index Maintenance
Rebuild and reorganize.
REINDEX TABLE users;
57.10 When Not to Use Indexes
Small tables or heavy writes.
-- Evaluate read/write ratio before indexing

58.1 Importance of Backup
Protect against data loss.
-- Regular backups critical for data safety
58.2 Types of Backups
Full, incremental, differential.
-- Full: all data; Incremental: changes since last backup
58.3 Backup Commands
Use native tools.
pg_dump mydb > backup.sql
58.4 Scheduling Backups
Automate with cron or jobs.
0 2 * * * pg_dump mydb > /backups/db_$(date +\%F).sql
58.5 Restoring Data
Load from backups.
psql mydb < backup.sql
58.6 Point-in-Time Recovery
Restore to specific moment.
-- Use WAL files and restore commands
58.7 Testing Backups
Verify integrity.
-- Restore backups to test servers periodically
58.8 Backup Encryption
Secure backup files.
gpg --encrypt --recipient user@example.com backup.sql
58.9 Cloud Backup Solutions
Use managed backups.
-- AWS S3, Azure Blob storage integration
58.10 Backup Retention Policies
Manage storage and compliance.
-- Keep backups for required time, then delete

59.1 Planning Migration
Assess source and target.
-- Analyze schemas, data volume, downtime
59.2 Exporting Data
Use export tools.
mysqldump mydb > export.sql
59.3 Data Transformation
Clean and convert formats.
-- Use ETL tools or scripts for transformation
59.4 Importing Data
Load into target DB.
psql newdb < export.sql
59.5 Schema Migration
Create tables and constraints.
-- Use tools like Liquibase or Flyway
59.6 Handling Data Integrity
Validate constraints.
-- Use checksums and data validation scripts
59.7 Downtime Minimization
Plan incremental migration.
-- Use replication or blue-green deployments
59.8 Migration Testing
Test before cutover.
-- Run queries on migrated data for validation
59.9 Rollback Plans
Prepare fallback strategies.
-- Backup original DB and scripts to revert changes
59.10 Post-Migration Tasks
Optimize and monitor.
-- Rebuild indexes, analyze performance

60.1 Benefits of Automation
Saves time, reduces errors.
-- Automate repetitive SQL tasks
60.2 SQL Scripting Basics
Use .sql files with commands.
-- Write multiple SQL statements in a file
SELECT * FROM users;
UPDATE users SET active = TRUE WHERE last_login > NOW() - INTERVAL '30 days';
60.3 Using Shell Scripts
Automate DB tasks via shell.
#!/bin/bash
psql mydb -f update_users.sql
60.4 Scheduled Jobs
Run scripts on schedule.
# Cron example: run every day at 3 AM
0 3 * * * /path/to/script.sh
60.5 Using SQL Agents
DB built-in schedulers.
-- SQL Server Agent jobs for automation
60.6 Dynamic SQL
Generate SQL commands programmatically.
EXECUTE('SELECT * FROM ' + @tableName);
60.7 Error Handling in Scripts
Catch and log errors.
BEGIN TRY
  -- SQL statements
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
END CATCH;
60.8 Logging and Notifications
Track script results.
-- Send email on job completion or failure
60.9 Version Control for Scripts
Use git or SVN.
git add myscript.sql
git commit -m "Added automation script"
60.10 Best Practices
Modular, documented, tested scripts.
-- Comment your scripts and use consistent naming