North wind-SQLite.sql

-- =========================================================
-- BARE MINIMUM NORTHWIND FOR TEACHING SQL
-- =========================================================

-- 1. SCHEMA DEFINITION
-- ---------------------------------------------------------

DROP TABLE IF EXISTS OrderDetails;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS Customers;

CREATE TABLE Customers (
    CustomerID TEXT PRIMARY KEY,
    CompanyName TEXT,
    ContactName TEXT,
    Country TEXT
);

CREATE TABLE Products (
    ProductID INTEGER PRIMARY KEY,
    ProductName TEXT,
    UnitPrice NUMERIC
);

CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    CustomerID TEXT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
);

CREATE TABLE OrderDetails (
    OrderID INTEGER,
    ProductID INTEGER,
    UnitPrice NUMERIC,
    Quantity INTEGER,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
);

-- 2. TARGETED TEACHING DATASET
-- ---------------------------------------------------------

-- 5 Customers (Note: 'ANTON' will have no orders, perfect for teaching LEFT JOINs)
INSERT INTO Customers (CustomerID, CompanyName, ContactName, Country) VALUES 
('ALFKI', 'Alfreds Futterkiste', 'Maria Anders', 'Germany'),
('ANATR', 'Ana Trujillo Emparedados', 'Ana Trujillo', 'Mexico'),
('ANTON', 'Antonio Moreno Taquería', 'Antonio Moreno', 'Mexico'),
('AROUT', 'Around the Horn', 'Thomas Hardy', 'UK'),
('BERGS', 'Berglunds snabbköp', 'Christina Berglund', 'Sweden');

-- 5 Products (Easy numbers for manual math verification)
INSERT INTO Products (ProductID, ProductName, UnitPrice) VALUES 
(1, 'Chai', 18.00),
(2, 'Chang', 19.00),
(3, 'Aniseed Syrup', 10.00),
(4, 'Chef Antons Cajun Seasoning', 22.00),
(5, 'Chef Antons Gumbo Mix', 21.00);

-- 5 Orders (ALFKI gets multiple orders to teach COUNT/GROUP BY)
INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES 
(10001, 'ALFKI', '2023-01-15'),
(10002, 'ALFKI', '2023-02-20'),
(10003, 'ANATR', '2023-03-05'),
(10004, 'AROUT', '2023-04-12'),
(10005, 'BERGS', '2023-05-25');

-- 8 Order Details (Order 10001 & 10004 have multiple items to teach SUM and multi-table joins)
INSERT INTO OrderDetails (OrderID, ProductID, UnitPrice, Quantity) VALUES 
(10001, 1, 18.00, 2),   -- ALFKI buys 2 Chai
(10001, 2, 19.00, 3),   -- ALFKI buys 3 Chang
(10002, 3, 10.00, 5),   -- ALFKI buys 5 Aniseed Syrup
(10003, 4, 22.00, 1),   -- ANATR buys 1 Cajun Seasoning
(10004, 5, 21.00, 4),   -- AROUT buys 4 Gumbo Mix
(10004, 1, 18.00, 1),   -- AROUT buys 1 Chai
(10005, 2, 19.00, 10);  -- BERGS buys 10 Chang

North wind.md

sqlite3 northwind_minimal.sqlite < northwind_minimal.sql

https://github.com/jpwhite3/northwind-SQLite3

https://github.com/kaiodt/northwind-data-analysis-sql/blob/main/imgs/northwind-er-diagram.png

https://www.kaggle.com/code/munawarsaudagar/sql-basics-with-northwind-database