🗃️ SQL Reference & Learning Guide

Welcome to the SQL Reference Repository! This repository is a comprehensive, structured learning path and quick reference guide designed to take you from SQL fundamentals to advanced procedural database constructs.

All examples, queries, and scripts are optimized for MySQL and use a simulated, production-grade e-commerce database schema (ecom).

---

🗺️ Learning Path & Topics

The files in this repository are numbered to represent a logical learning progression. Click on any file to view the SQL script directly:

🛠️ Part 1: Schema Setup & Relations

1. 03. Source Data Setup: Sets up the sample ecom database schema and populates it with nearly 7,000 lines of transactional records.
2. 01. DDL Commands: Data Definition Language fundamentals (CREATE, ALTER, DROP, TRUNCATE, RENAME) and database level constraints (NOT NULL, UNIQUE).
3. 02. Keys in SQL: Constraints including Primary Keys, Foreign Keys (handling referential integrity with ON UPDATE CASCADE / ON DELETE CASCADE), Candidate, Composite, Surrogate, and Unique Keys.

📊 Part 2: Querying Foundations

4. 04. SQL Basics: Foundations of querying (SELECT, WHERE filtering, LIMIT, pattern matching with LIKE, sorting with ORDER BY, column aliasing, GROUP BY, and HAVING).
5. 05. SQL Joins: Combining datasets using INNER, LEFT, RIGHT, CROSS, and SELF joins. Includes a workaround for MySQL's lack of native FULL OUTER JOIN using UNION.
6. 06. DML Commands: Data Manipulation Language fundamentals (INSERT, UPDATE, DELETE).

🔄 Part 3: Data Transformations

7. 07. Numeric Transformations: Mathematical operations, column calculations, and rounding techniques.
8. 08. Date Transformations: Current date/time functions (NOW(), UTC_TIMESTAMP()), interval math (ADDDATE(), SUBDATE(), DATEDIFF()), component extraction (YEAR, MONTH, DAY, WEEKDAY, DAYNAME), formatting (DATE_FORMAT), and typecasting.
9. 09. String Transformations: Text processing tools including casting, CONCAT(), CONCAT_WS(), SUBSTRING(), REPLACE(), LOWER(), UPPER(), LENGTH(), REVERSE(), and REPEAT().
10. 10. Conditionals: Logical branching in queries using the CASE WHEN ... THEN ... ELSE ... END conditional flow.

📈 Part 4: Advanced Analytical SQL

11. 11. Window Functions: Analytical window operations (OVER), running totals, partition averages, and frame definitions (ROWS BETWEEN ... PRECEDING AND ... FOLLOWING).
12. 12. Ranking Functions: Row ordering mechanics utilizing ROW_NUMBER(), RANK(), and DENSE_RANK() alongside partition boundaries.
13. 13. Subqueries: Nested queries, scalar subqueries, and using subqueries as in-memory virtual tables.
14. 14. CTEs (Common Table Expressions): Writing cleaner and modular subqueries using the WITH syntax, including chaining multiple CTEs.
15. 15. Real Time Scenarios: Solving real-world production and interview scenarios:
    • Scenario 1: Finding the N-th value (e.g., 5th most expensive product overall or per category).
    • Scenario 2: Deduplicating table rows using ROW_NUMBER() partitioning.
    • Scenario 3: Accessing offset rows using LAG() and LEAD() (e.g., weather temp trends).

🗃️ Part 5: Database Programmability

16. 16. Views: Saving queries as virtual database views to abstract complex querying logic.
17. 17. Stored Procedures: Building reusable procedural scripts in MySQL with input parameters (IN) and custom block delimiters (//).
18. 18. Functions: Developing custom deterministic scalar functions (UDFs) to implement user-defined calculations (e.g., squaring a value).

---

🗄️ Database Schema (ecom)

The reference dataset simulates a transactional e-commerce business model structured as a star schema:

erDiagram
    dim_date {
        int date_key PK
        date date
        int day
        int month
        varchar month_name
        int quarter
        int year
        tinyint is_weekend
    }
    dim_customer {
        int customer_key PK
        varchar customer_id
        varchar first_name
        varchar last_name
        char gender
        varchar email
        varchar phone
        varchar country
        varchar state
        varchar city
        date join_date
    }
    dim_product {
        int product_key PK
        varchar product_id
        varchar product_name
        varchar category
        varchar brand
        decimal unit_price
        date launch_date
    }
    dim_store {
        int store_key PK
        varchar store_id
        varchar store_name
        varchar region
        varchar country
        varchar city
    }
    fact_sales {
        int sales_id PK
        int date_key FK
        int customer_key FK
        int product_key FK
        int store_key FK
        int quantity_sold
        decimal unit_price
        decimal discount
        decimal total_amount
    }
    fact_sales }|--|| dim_date : date_key
    fact_sales }|--|| dim_customer : customer_key
    fact_sales }|--|| dim_product : product_key
    fact_sales }|--|| dim_store : store_key

Loading

Table Definitions

• dim_date: Contains day, month, year, quarters, and weekend markers (is_weekend) for time-series aggregation.
• dim_customer: Holds profile information including IDs, demographics (gender), location, contact info, and registration dates (join_date).
• dim_product: Product catalog detailing brands, categories, unit prices, and launch dates.
• dim_store: Store location dimensions categorizing stores by city, country, and regional segments.
• fact_sales: Central transactional fact table containing quantity sold, product pricing, applicable discounts, and total transaction amounts, linked to the surrounding dimensions.

---

🚀 Setup & Execution Guide

Prerequisites

• A MySQL Server (v8.0+ recommended) or any compatible MySQL environment (e.g., MariaDB).
• A SQL client like MySQL Workbench, DBeaver, or VS Code SQL tools.

Database Setup

1. Open your SQL client and connect to your MySQL Server.
2. Run the schema creation and data import script:

   # If using command-line:
   mysql -u username -p < "3. Souce_data_setup.sql"

   This creates the ecom database, sets up the tables, and populates them.
3. Open any topic file (e.g., 4.SQL basics.sql) and run the queries against the newly populated ecom database.

---

📝 Concept Cheat-Sheet

Here are a few critical concepts highlighted in this repository:

1. SQL Order of Query Execution

Although written in a different order, SQL executes queries in this sequence:

1. FROM / JOIN: Identifies the source tables and matches rows.
2. WHERE: Filters records based on conditions.
3. GROUP BY: Groups matching records.
4. HAVING: Filters groups based on conditions.
5. SELECT: Evaluates fields, expressions, and aliases.
6. ORDER BY: Sorts output records.
7. LIMIT: Restricts output row count.

2. Ranking Functions Comparison

Suppose you have records with identical values, e.g., prices (90, 90, 100):

• ROW_NUMBER(): Evaluates to 1, 2, 3. (Assigns sequential ranks, never repeats values).
• RANK(): Evaluates to 1, 1, 3. (Assigns identical ranks to duplicates; skips ranks afterwards).
• DENSE_RANK(): Evaluates to 1, 1, 2. (Assigns identical ranks to duplicates; does not skip any ranks).

---

🤝 Contributing & Usage

This repository serves as a personal SQL cheat sheet and workspace. Feel free to copy snippets, practice the questions, or modify scripts for learning!