SQL

1-MONTH SQL COURSE STRUCTURE

Week 1: SQL Basics (Foundations + CRUD)

Goal: Understand the fundamentals and be able to write simple queries.

Day 1: Introduction to Databases & SQL

• What is a Database?

• Types: Relational vs Non-Relational

• What is SQL?

• RDBMS (MySQL, PostgreSQL, SQL Server)

• SQL syntax rules

Day 2: Data Types

• Numeric, String, Date/Time

• NULL concept

Day 3: Creating and Managing Databases

• CREATE DATABASE

• CREATE TABLE

• Data type selection

• Constraints:

  • PRIMARY KEY

  • NOT NULL

  • UNIQUE

  • DEFAULT

Day 4: Insert, Update, Delete

• INSERT INTO

• UPDATE

• DELETE

• Handling errors & constraints

Day 5: SELECT Basics

• SELECT

• WHERE clause

• Comparison operators (=, >, <, LIKE, BETWEEN, IN)

• ORDER BY

• LIMIT

Day 6: Practical Exercises

• Designing tables

• Writing CRUD queries

• Filtering data

Day 7: Mini Project (Week 1)

Build a Student Management Database

• Students table

• Courses table

• Departments table
  Practice CRUD and simple filters.

Week 2: Intermediate SQL (Joins, Keys, Functions)

Goal: Master joins, group operations, and functions.

Day 8: Keys and Relationships

• Primary key

• Foreign key

• One-to-One

• One-to-Many

• Many-to-Many

Day 9: Joins (Very Important)

• INNER JOIN

• LEFT JOIN

• RIGHT JOIN

• FULL JOIN

• CROSS JOIN

Day 10: More on Joins

• Self join

• Natural join

• Multiple table joins

Day 11: Aggregate Functions

• COUNT, SUM, AVG, MIN, MAX

• GROUP BY

• HAVING

Day 12: String & Date Functions

• CONCAT, LENGTH, UPPER, LOWER

• NOW, DATEDIFF, DATE_ADD, EXTRACT

Day 13: Subqueries

• Single-row subquery

• Multi-row subquery

• EXISTS vs IN

Day 14: Mini Project (Week 2)

Build a Sales Database

• Products

• Orders

• Customers
  Use JOIN, GROUP BY, and Subqueries.

Week 3: Advanced SQL (Views, Indexing, Optimization)

Goal: Learn advanced database concepts for performance and real-world apps.

Day 15: Views

• CREATE VIEW

• Updating views

• When to use views

Day 16: Indexes

• What is an index

• CREATE INDEX

• Composite indexes

• Index performance impacts

Day 17: Transactions

• ACID properties

• START TRANSACTION

• COMMIT

• ROLLBACK

Day 18: Stored Procedures

• Creating stored procedures

• IN/OUT parameters

• When to use SPs

Day 19: Triggers

• BEFORE INSERT/UPDATE/DELETE

• AFTER INSERT/UPDATE/DELETE

• Use cases

Day 20: Functions

• Creating user-defined functions

• Difference between SP and functions

Day 21: Mini Project (Week 3)

Build a Banking Database

• Transactions

• Accounts

• Customers
  Use views, triggers, indexes, SPs.

Week 4: Real-World SQL + Projects + Optimization

Goal: Build production-level queries and optimize performance.

Day 22: Normalization

• 1NF, 2NF, 3NF

• BCNF

• Denormalization

Day 23: Query Optimization

• EXPLAIN keyword

• Avoiding full table scans

• Understanding execution plans

Day 24: Joins vs Subqueries (Performance)

• When to use subqueries

• When to avoid nested selects

Day 25: Window Functions (Advanced)

• OVER()

• ROW_NUMBER

• RANK & DENSE_RANK

• PARTITION BY

Day 26: CTEs (Common Table Expressions)

• WITH keyword

• Recursive CTEs

Day 27: SQL Security

• SQL injection

• Sanitization

• User privileges

• GRANT / REVOKE

Day 28: Mini Project (Week 4)

Build a Full E-Commerce Database Project:

• Users

• Products

• Categories

• Orders

• Payments
  Use: CTEs, Window functions, Optimization.

Day 29–30: Final Project

Create a complete database for one of the following:

• Hospital Management

• Library Management

• Food Delivery App

• Railway Reservation System

Deliverables:

• ER diagram

• SQL schema

• 10+ CRUD queries

• 10+ join queries

• 5 advanced queries (CTE, window functions)

• 1 stored procedure

• 1 trigger

• Indexing + optimization report