Module 0

Module 0: Introduction – Why SQL Matters for Applications

Welcome! Before we dive into writing code, let’s understand why SQL is such a fundamental skill for anyone involved in building or managing modern applications. Why can’t we just store everything in text files or spreadsheets?

Lesson 0.1: What Problems Does SQL Solve?

Imagine you’re building a simple online store. You need to keep track of several things:

  1. Your Users: Who has signed up? What are their names, email addresses, shipping details? How do they log in securely?
  2. Your Products: What are you selling? What are their names, descriptions, prices, and how many do you have in stock?
  3. Their Orders: Which user bought which products? When did they place the order? What was the total cost? Where should it be shipped?

Now, imagine trying to manage this information as your store grows.

  • Using Text Files? How would you quickly find a specific user’s order history? How would you ensure that when someone buys a product, the stock count is updated correctly every time, even if multiple people buy it at once? It would become incredibly messy and error-prone.
  • Using Spreadsheets? Better, but still limited. How do you easily link an order back to the specific user and the specific products involved without massive duplication or complex VLOOKUPs? What if multiple people on your team need to update product information simultaneously? Spreadsheets weren’t designed for this kind of complex relationship management or concurrent access.

This is where databases and SQL come in.

A Database is an organized collection of data, stored electronically. Think of it as a highly structured, efficient, and reliable digital filing cabinet.

A Database Management System (DBMS) is the software that allows us to create, manage, and interact with databases. Examples include PostgreSQL, MySQL, SQLite, SQL Server, Oracle, etc. The DBMS handles:

  • Storing data efficiently and safely.
  • Retrieving data quickly based on specific criteria.
  • Updating data reliably.
  • Managing relationships between different pieces of data (like linking users to their orders).
  • Ensuring data integrity (e.g., preventing an order from being created without a valid user).
  • Handling concurrent access (allowing multiple users or application processes to interact with the data simultaneously without corrupting it).

SQL (Structured Query Language) is the standard language used to communicate with most relational DBMS. It’s how we tell the database what we want to do: retrieve specific information, add new records, update existing ones, or delete old ones.

In short, SQL solves the problem of managing structured application data reliably, efficiently, and at scale. Whether it’s a web application, a mobile app backend, an analytics platform, or internal business software, databases and SQL are almost always working behind the scenes. Understanding SQL empowers you to interact directly with this crucial layer.

Lesson 0.2: Thinking in Tables – Relational Databases Explained

The most common type of database used in applications is the Relational Database. The core idea is simple but powerful: data is organized into Tables.

Think of a table like a spreadsheet, but with stricter rules:

  • Table: Represents a specific type of entity (e.g., users, products, orders).
  • Row (or Record): Represents a single instance of that entity (e.g., one specific user, one particular product, one individual order).
  • Column (or Attribute/Field): Represents a specific piece of information about that entity (e.g., first_name, email, price, order_date). Each column has a defined data type (like text, number, date).

The “Relational” Magic:

What makes these databases “relational” is their ability to define relationships between tables. We don’t store all the user’s information directly inside every order they place. Instead:

  1. The users table has a unique identifier for each user (let’s call it user_id). This is the Primary Key for the users table.
  2. The orders table also has its own unique identifier (order_id – its Primary Key).
  3. Crucially, the orders table also includes a column like user_id to store the ID of the user who placed that specific order. This user_id in the orders table is called a Foreign Key because it refers to the Primary Key of the users table.

This creates a link or relationship. By using these keys, we can easily query the database to find all orders placed by a specific user, or find the user details for a specific order, without duplicating information.

Our E-commerce Example Database Structure:

Throughout this course, we’ll use a simplified e-commerce database with the following main tables:

  • users: Stores information about registered users.
    • Columns: user_id (Primary Key), first_name, last_name, email, registration_date.
  • categories: Stores product categories.
    • Columns: category_id (Primary Key), category_name.
  • products: Stores information about the products for sale.
    • Columns: product_id (Primary Key), product_name, description, price, stock_quantity, category_id (Foreign Key linking to categories).
  • orders: Stores information about customer orders.
    • Columns: order_id (Primary Key), user_id (Foreign Key linking to users), order_date, total_amount, shipping_address.
  • order_items: Stores details about which products are in which order (a ‘linking’ table).
    • Columns: order_item_id (Primary Key), order_id (Foreign Key linking to orders), product_id (Foreign Key linking to products), quantity, price_per_unit.

(Don’t worry about memorizing this perfectly now; we’ll refer back to it constantly. The key takeaway is how data is split logically and linked together).

This structured approach keeps data organized, reduces redundancy, and allows for powerful and flexible querying – essential for any application.

Lesson 0.3: Setting Up Your Practice Environment

Theory is great, but SQL is a practical skill. You need to write and run queries to truly learn. Let’s get your practice environment set up!

Your Goal: Install a Database Management System (DBMS) and a tool to interact with it on your own computer.

Recommended Options (Choose One):

  1. SQLite + DB Browser for SQLite (Easiest for Beginners):
    • What: SQLite is a lightweight, file-based database. It doesn’t require a separate server process. DB Browser for SQLite is a free, user-friendly graphical tool to create databases, view data, and run SQL queries.
    • Pros: Very easy to install on Windows, Mac, and Linux. No complex configuration. Great for learning.
    • Cons: Lacks some advanced features of server-based databases and isn’t typically used for large, high-concurrency web applications (but perfect for learning SQL syntax).
    • Links:
  2. PostgreSQL + pgAdmin or DBeaver (Powerful & Popular):
    • What: PostgreSQL is a powerful, open-source, server-based relational database widely used in production applications. pgAdmin and DBeaver are popular graphical tools to manage and query PostgreSQL databases.
    • Pros: Full-featured, standards-compliant, widely used professionally.
    • Cons: Requires installing a database server and a separate client tool. Slightly more complex setup than SQLite.
    • Links:
  3. MySQL + MySQL Workbench or DBeaver (Powerful & Popular):
    • What: MySQL is another extremely popular open-source, server-based relational database, especially common in web development (often part of the LAMP/LEMP stack). MySQL Workbench is the official graphical tool; DBeaver also works well.
    • Pros: Very widely used, large community, good performance.
    • Cons: Similar setup complexity to PostgreSQL. Some minor syntax differences compared to PostgreSQL/SQLite (we’ll highlight major ones).
    • Links:
  4. Docker (Advanced Users): If you’re familiar with Docker, you can easily spin up containers for PostgreSQL or MySQL without installing them directly on your system.

Installation: Please follow the instructions provided on the websites linked above for your chosen database and client tool.

Loading the Sample Database:

Once you have your DBMS and client tool installed, you need to create our sample E-commerce database and populate it with data. We provide a SQL script file (.sql) that contains all the necessary commands.

Steps:

  1. Download the Script: Get the sample database script file here: [>>> Link to download your sample_database.sql script here <<<] (Note to course creator: You need to create this .sql file containing the CREATE TABLE and INSERT INTO statements below and make it available for download)
  2. Connect: Open your chosen SQL client tool (DB Browser, pgAdmin, MySQL Workbench, DBeaver, etc.) and connect to your database instance (or create a new SQLite database file).
  3. Open & Run Script: Find an option like “Open Script,” “Run SQL File,” or “Import.” Select the sample_database.sql file you downloaded and execute it. This will create the tables and insert the sample data.
  4. Verify: Use your client tool’s interface to browse the tables (users, products, categories, orders, order_items). You should see the tables listed, and if you view the data within them, you should see the sample records.

Sample Database Schema (for reference, included in the script):

SQL


CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique ID for the category
    category_name VARCHAR(255) NOT NULL UNIQUE -- Name of the category (e.g., 'Electronics')
);

-- Represents users of the application
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique ID for the user
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE, -- User's email, must be unique
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- When the user signed up
);

-- Represents products available for sale
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique ID for the product
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK(price >= 0), -- Price with 2 decimal places, must be non-negative
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK(stock_quantity >= 0), -- Available stock, non-negative
    category_id INTEGER, -- Link to the categories table
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL -- If category is deleted, set product's category to NULL
);

-- Represents orders placed by users
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique ID for the order
    user_id INTEGER NOT NULL, -- Link to the users table (who placed the order)
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- When the order was placed
    total_amount DECIMAL(12, 2) CHECK(total_amount >= 0), -- Total cost of the order
    shipping_address TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE -- If user is deleted, delete their orders too
);

-- Represents the individual items within an order (linking orders and products)
CREATE TABLE order_items (
    order_item_id INTEGER PRIMARY KEY AUTOINCREMENT, -- Unique ID for this line item
    order_id INTEGER NOT NULL, -- Link to the orders table
    product_id INTEGER NOT NULL, -- Link to the products table
    quantity INTEGER NOT NULL CHECK(quantity > 0), -- How many of this product were ordered
    price_per_unit DECIMAL(10, 2) NOT NULL CHECK(price_per_unit >= 0), -- Price of the product *at the time of order*
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE, -- If order is deleted, delete its items
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT -- Prevent deleting a product if it's part of an existing order item
);

-- Note: AUTOINCREMENT behavior might vary slightly (e.g., SERIAL in PostgreSQL).
-- Note: TIMESTAMP DEFAULT CURRENT_TIMESTAMP syntax might vary.
-- Note: ON DELETE clauses define what happens if a referenced record is deleted.

Sample INSERT Statements (A small sample – the .sql file will have more):

SQL

Now that you have your environment set up and the sample data loaded, you’re ready to start writing your first SQL queries in Module 1!

-- (Included in the downloadable script)
INSERT INTO categories (category_name) VALUES ('Electronics'), ('Books'), ('Clothing'), ('Home Goods');

INSERT INTO users (first_name, last_name, email) VALUES
('Alice', 'Smith', 'alice.s@email.com'),
('Bob', 'Johnson', 'bob.j@email.com'),
('Charlie', 'Davis', 'charlie.d@email.com');

INSERT INTO products (product_name, description, price, stock_quantity, category_id) VALUES
('Laptop Pro 15"', 'High-performance laptop', 1299.99, 50, 1),
('SQL Fundamentals', 'Learn SQL from scratch', 39.95, 100, 2),
('Wireless Mouse', 'Ergonomic wireless mouse', 25.50, 200, 1),
('Cotton T-Shirt', 'Comfortable cotton t-shirt', 19.99, 150, 3);

-- Sample order and order items (will be more complex in the script)
INSERT INTO orders (user_id, total_amount, shipping_address) VALUES (1, 1325.49, '123 Main St, Anytown'); -- Alice's order
-- Get the ID of the order just inserted (logic depends on SQL dialect/tool, assume it's 1 for example)
INSERT INTO order_items (order_id, product_id, quantity, price_per_unit) VALUES
(1, 1, 1, 1299.99), -- 1 Laptop Pro
(1, 3, 1, 25.50);   -- 1 Wireless Mouse