M164 - Creating Databases and Inserting Data

What I Learned

Core competencies and skills developed throughout this module

Setting Up the Database Environment

Installed and used a local MySQL environment and learned the different ways of talking to a database server.

Running MySQL locally, for example with XAMPP
Working with phpMyAdmin, MySQL Workbench and the command line
Inspecting databases with SHOW DATABASES, SHOW TABLES and DESCRIBE
Creating users and understanding basic permissions

Creating & Altering Tables

Implemented a database schema with CREATE TABLE and adjusted it later with ALTER TABLE, choosing data types and constraints carefully.

Choosing data types like INT, DECIMAL, VARCHAR, TEXT and DATE
Using constraints: NOT NULL, UNIQUE, DEFAULT and AUTO_INCREMENT
Adding, changing and removing columns with ALTER TABLE
Knowing the storage size and limits of each data type

Inserting, Importing & Changing Data

Filled tables with data and kept it up to date, always paying attention to the WHERE clause when changing existing records.

Inserting single and multiple rows with INSERT INTO
Importing CSV files through phpMyAdmin
Updating records with UPDATE ... SET ... WHERE
Deleting records safely with DELETE and WHERE

Querying Data with SELECT

Wrote queries that select exactly the data needed, filtered, sorted and formatted for the question at hand.

Filtering with WHERE, AND, OR, IN, BETWEEN and LIKE
Sorting and limiting results with ORDER BY and LIMIT
Using aliases and string functions like CONCAT and UPPER
Handling NULL values with IS NULL and IFNULL

Joins & Aggregate Functions

Combined data from several tables and summarised it into meaningful key figures.

Combining tables with INNER JOIN and LEFT JOIN
Calculating with COUNT, SUM, AVG, MIN and MAX
Grouping results with GROUP BY
Filtering groups with HAVING

Keys & Referential Integrity

Secured the relationships between tables so that the database itself prevents inconsistent data.

Defining primary keys and composite keys
Creating foreign keys with FOREIGN KEY ... REFERENCES
Choosing ON DELETE / ON UPDATE behaviour (CASCADE, SET NULL, RESTRICT)
Checking an existing schema against the normal forms

Code Examples

Practical examples from the module

                create_shop.sql
            

CREATE DATABASE IF NOT EXISTS shop;
USE shop;

CREATE TABLE category (
    category_id INT          AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50)  NOT NULL UNIQUE
);

CREATE TABLE product (
    product_id  INT           AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100)  NOT NULL,
    price       DECIMAL(8,2)  NOT NULL DEFAULT 0.00,
    stock       INT           NOT NULL DEFAULT 0,
    category_id INT,
    CONSTRAINT fk_product_category
        FOREIGN KEY (category_id) REFERENCES category(category_id)
        ON DELETE SET NULL
);

-- Insert several rows in one statement
INSERT INTO category (name) VALUES ('Books'), ('Stationery');

INSERT INTO product (name, price, stock, category_id) VALUES
    ('SQL for Beginners', 34.90, 12, 1),
    ('Notebook A5',        4.50, 80, 2),
    ('Fountain Pen',      19.00,  0, 2);
            

                reports.sql
            

-- Number of products and average price per category
SELECT c.name             AS category,
       COUNT(p.product_id) AS products,
       ROUND(AVG(p.price), 2) AS avg_price
FROM category c
LEFT JOIN product p ON p.category_id = c.category_id
GROUP BY c.name
HAVING COUNT(p.product_id) > 0
ORDER BY avg_price DESC;

-- Products that are out of stock
SELECT name, price
FROM product
WHERE stock = 0 AND name LIKE '%Pen%';

-- Raise all stationery prices by 5 percent
UPDATE product
SET price = price * 1.05
WHERE category_id = 2;
            

Skills Acquired

Technologies and concepts mastered in this module

MySQL phpMyAdmin MySQL Workbench DDL DML SELECT Queries Joins Aggregate Functions GROUP BY & HAVING Primary & Foreign Keys Referential Integrity CSV Import

Key Takeaways

Never forget WHERE: An UPDATE or DELETE without it affects every single row.
Let the database protect itself: Constraints and foreign keys catch errors the application might miss.
Fit the type to the data: DECIMAL for money, DATE for dates, VARCHAR with a sensible length.
Join instead of duplicating: Related data belongs in its own table and is combined at query time.
Build queries step by step: Start with a simple SELECT and add joins and groups one at a time.