SQL Course | ClassNotes.Site

Course Outline

1Introduction to SQL & Databases

0/4

1.1 What is SQL?

Introduction to Structured Query Language (SQL)

History and evolution of SQL standards

Relational databases and ACID properties

Database management systems (DBMS) overview

SQL vs NoSQL comparison

1.2 Database Design Principles

Tables, rows, and columns fundamentals

Data types and constraints

Primary keys and unique identifiers

Foreign keys and relationships

Database schemas and naming conventions

1.3 Setting Up Your Environment

MySQL installation and setup

PostgreSQL installation and configuration

SQL Server basics

DBeaver GUI tool installation

Command-line SQL clients and tools

Assignment: Create Your First Database

2SELECT and WHERE Clauses

0/4

2.1 Basic SELECT Statements

SELECT * and retrieving all columns

Selecting specific columns

Column aliases and renaming

DISTINCT keyword for unique values

LIMIT clause for result restrictions

2.2 WHERE Clause and Operators

Comparison operators (=, !=, >, <, >=, <=)

Logical operators (AND, OR, NOT)

IN and NOT IN operators

BETWEEN operator for ranges

LIKE and pattern matching, NULL checks

2.3 Sorting and Pagination

ORDER BY ascending and descending

Multi-column sorting

LIMIT clause for pagination

OFFSET for skipping rows

TOP clause in SQL Server

Assignment: Query Real Data

3JOINs and Relationships

0/4

3.1 INNER JOIN

Introduction to INNER JOIN syntax and basic usage

Combining matching records from two tables

ON clause and join conditions explained

Joining multiple tables with multiple conditions

Table aliases and naming conventions for clarity

3.2 OUTER JOINs

LEFT JOIN: Keeping all rows from left table

RIGHT JOIN: Keeping all rows from right table

FULL OUTER JOIN: Combining all rows from both tables

Handling NULL values in joined results

CROSS JOIN: Creating Cartesian products

3.3 Advanced Join Techniques

Self joins: Joining a table to itself

Complex join conditions with multiple criteria

Multi-column joins for composite keys

Join performance optimization techniques

Comparing UNION vs JOIN for combining data

Assignment: Complex Joins

4Aggregation and Grouping

0/4

4.1 Aggregate Functions

COUNT function: Counting rows and non-null values

SUM, AVG, MAX, MIN: Basic statistical calculations

STRING_AGG: Combining multiple strings into one

JSON_AGG: Building JSON from row data

DISTINCT with aggregates for unique value calculations

4.2 GROUP BY and HAVING

GROUP BY: Organizing data into logical groups

Grouping by single and multiple columns

HAVING clause: Filtering groups after aggregation

Difference between WHERE and HAVING clauses

Common patterns: Top N per group, running totals

4.3 Window Functions

ROW_NUMBER: Assigning unique numbers to rows

RANK and DENSE_RANK: Handling ties in rankings

LAG and LEAD: Accessing previous and next row values

PARTITION BY: Creating separate windows within groups

Window frame specification: ROWS and RANGE clauses

Assignment: Data Analysis Queries

5Subqueries and CTEs

0/4

5.1 Subqueries Basics

Introduction to scalar subqueries in SELECT clause

Inline views: Using subqueries in FROM clause

EXISTS operator: Testing for row existence

IN operator: Matching against subquery results

Performance considerations: Correlated vs non-correlated

5.2 Common Table Expressions (CTEs)

WITH clause: Creating named query subsets

Recursive CTEs: Handling hierarchical data

Chaining multiple CTEs together

CTE scope and lifecycle within queries

Readability improvements with named CTEs

5.3 Advanced Query Patterns

Nested CTEs: Composing multiple levels of queries

Correlated subqueries: Row-by-row processing

Materialization: Optimizing subquery execution

Performance optimization techniques for complex queries

Common pitfalls: NULL handling and logical errors

Assignment: CTE Mastery

6Indexes and Query Optimization

0/4

6.1 Index Types and Creation

Primary key indexes: Enforcing uniqueness

Unique indexes: Preventing duplicate values

Composite indexes: Indexing multiple columns

B-tree structure: How indexes organize data

CREATE INDEX syntax and naming conventions

6.2 Query Execution Plans

EXPLAIN PLAN: Understanding query execution

Index usage: How database chooses indexes

Cost analysis: Reading execution plan metrics

Seek vs Scan: Index utilization strategies

Estimated vs actual rows: Performance prediction

6.3 Performance Tuning

Query rewriting: Optimizing query logic

Statistics: Updating table and index statistics

Partitioning: Splitting large tables

Monitoring: Identifying slow queries

Caching and materialized views: Avoiding redundant work

Assignment: Optimize Performance

7Transactions and Constraints

0/4

7.1 Constraints and Data Integrity

PRIMARY KEY: Unique row identification

FOREIGN KEY: Referential integrity between tables

UNIQUE constraint: Preventing duplicate values

CHECK and NOT NULL: Domain and mandatory value constraints

DEFAULT values: Setting automatic default data

7.2 ACID Transactions

BEGIN: Starting a transaction

COMMIT: Saving all changes permanently

ROLLBACK: Undoing changes within a transaction

Savepoints: Creating rollback points within transactions

Isolation levels: READ UNCOMMITTED to SERIALIZABLE

7.3 Locking and Concurrency

Row locks: Preventing concurrent modifications

Table locks: Exclusive and shared lock modes

Deadlock detection: Identifying circular wait conditions

Optimistic locking: Using version columns

Lock escalation: Managing resource usage efficiently

Assignment: Safe Data Operations

8Stored Procedures and Functions

0/4

8.1 Stored Procedures

CREATE PROCEDURE: Defining reusable code blocks

Input parameters: Passing data to procedures

Output parameters: Returning values from procedures

Control flow: IF, WHILE, CASE statements

Error handling: TRY-CATCH blocks in procedures

8.2 User-Defined Functions

Scalar functions: Functions returning single values

Table-valued functions: Returning result sets

Deterministic functions: Consistent results for inputs

Recursive functions: Hierarchical data processing

Performance: Inline functions vs multi-statement functions

8.3 Triggers and Event Handling

BEFORE triggers: Actions before INSERT, UPDATE, DELETE

AFTER triggers: Actions after data modifications

INSERT, UPDATE, DELETE triggers: Event-specific logic

Audit trails: Tracking data changes automatically

Cascading triggers: Handling dependent operations

Assignment: Build Stored Objects

9Database Design and Normalization

0/4

9.1 Normalization Forms

First Normal Form (1NF): Eliminating repeating groups

Second Normal Form (2NF): Partial dependency removal

Third Normal Form (3NF): Transitive dependency elimination

Boyce-Codd Normal Form (BCNF): Stronger form of 3NF

Denormalization tradeoffs: Performance vs consistency

9.2 ER Diagrams and Schema Design

Entity-relationship modeling: Identifying entities

Attributes and primary keys: Data representation

Cardinality: One-to-one, one-to-many, many-to-many

Relationships: Dependencies between entities

Design patterns: Common schema structures

9.3 Scalability and Advanced Patterns

Horizontal sharding: Distributing data across servers

Replication: Master-slave and master-master setups

Data warehousing: OLAP vs OLTP databases

Time-series data: Handling temporal databases

Archive strategies: Managing data retention policies

Assignment: Design Production Database

10Real-World Projects & Advanced Topics

0/6

10.1 E-Commerce Database

Users table: Customer profiles and authentication

Products catalog: Item details, categories, pricing

Orders and order items: Tracking customer purchases

Payments: Transaction management and status tracking

Inventory management: Stock levels and availability

10.2 Hospital Management System

Patients: Demographics, medical history, contact info

Doctors: Specializations, schedules, credentials

Appointments: Scheduling and availability management

Medical records: Diagnoses, treatments, prescriptions

Billing: Charges, payments, insurance claims

10.3 Analytics Dashboard Database

User analytics: Traffic, behavior, demographics

Event tracking: User interactions and conversions

Reporting: Custom dashboards and KPI tracking

Aggregations: Pre-computed summaries for performance

Time-series data: Historical trends and forecasting

10.4 Social Media Database

Users: Profiles, bios, follower relationships

Posts: Content, media, timestamps, visibility

Comments: Threaded discussions, nesting levels

Likes and reactions: Engagement tracking

Feed algorithms: Ranking and personalization data

10.5 Advanced Topics

JSON handling: Storing and querying semi-structured data

Full-text search: Indexed text searching capabilities

Temporal data: Versioning and time-travel queries

Geospatial queries: Location-based searches

Graph databases: Hierarchical and network data patterns

Capstone Project

What You'll Learn

Master SQL fundamentals and database design
Write complex queries with JOINs and subqueries
Query optimization and index strategies
Database transactions and data integrity
Stored procedures and triggers
Database design and normalization

Quick Navigation

1. Introduction 2. SELECT & WHERE 3. JOINs 4. Aggregation 5. Subqueries 6. Indexes 7. Transactions 8. Procedures 9. Design 10. Projects

Need Help?

Get personalized guidance and support

SQL Masterclass

Your Learning Journey