fastapi_db.sql

-- FastAPI Machine Test SQL Script
-- Deliverable 2: MySQL schema + sample data

-- 1) Create Database
CREATE DATABASE IF NOT EXISTS fastapi_db;

-- 2) Use Database
USE fastapi_db;

-- 3) Create Tables
CREATE TABLE IF NOT EXISTS categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    price INT NOT NULL,
    category_id INT NOT NULL,
    CONSTRAINT fk_products_category
        FOREIGN KEY (category_id)
        REFERENCES categories(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- 5) Insert Sample Data (3+ categories, 5+ products)
INSERT INTO categories (name) VALUES
('Electronics'),
('Fashion'),
('Books');

INSERT INTO products (name, price, category_id) VALUES
('Laptop', 55000, 1),
('Smartphone', 25000, 1),
('Headphones', 3000, 1),
('T-Shirt', 800, 2),
('Python Handbook', 1200, 3);