level2_estore_intermediate.db

-- =============================================
-- The Growing E-Store Sample Database
-- For SQL Learning At Intermediate Level
-- =============================================

-- =============================================
--CREATING TABLES
-- =============================================

CREATE TABLE Customers (								
customer_id INTEGER PRIMARY KEY,								
name TEXT,								
email TEXT,								
join_date TEXT								
);								
								
CREATE TABLE Products (								
product_id INTEGER PRIMARY KEY,								
product_name TEXT,								
category TEXT,								
price REAL,								
stock_quantity INTEGER								
);								
								
CREATE TABLE Orders (								
order_id INTEGER PRIMARY KEY,								
customer_id INTEGER,								
order_date TEXT,								
status TEXT,								
FOREIGN KEY(customer_id) REFERENCES Customers(customer_id)								
);								
								
CREATE TABLE Order_Items (								
order_item_id INTEGER PRIMARY KEY,								
order_id INTEGER,								
product_id INTEGER,								
quantity INTEGER,								
FOREIGN KEY(order_id) REFERENCES Orders(order_id),								
FOREIGN KEY(product_id) REFERENCES Products(product_id)								
);								


-- =============================================
-- INSERTING SAMPLE DATA
-- =============================================


INSERT INTO Customers (name, email, join_date) VALUES								
(1, 'Alice Smith', 'alice@email.com', '2024-01-15'),								
(2, 'Bob Johnson', 'bob@email.com', '2024-02-20'),								
(3, 'Charlie Lee', 'charlie@email.com', '2024-03-05'),								
(4, 'David Kim', 'david.kim@email.com', '2024-05-10'),								
(5, 'Eva Gomez', 'eva@email.com', '2024-07-01');								
								
INSERT INTO Products (product_name, category, price, stock_quantity) VALUES								
(1, 'Laptop', 'Electronics', 1200.00, 50),								
(2, 'Coffee Maker', 'Appliances', 80.00, 200),								
(3, 'SQL Essentials Book', 'Books', 45.00, 500),								
(4, 'Headphones', 'Electronics', 150.00, 150),								
(5, 'Blender', 'Appliances', 60.00, 300),								
(6, 'Smartphone', 'Electronics', 700.00, 100);								
								
INSERT INTO Orders (customer_id, order_date, status) VALUES								
(1, '2025-08-01', 'Shipped'),								
(2, '2025-08-03', 'Shipped'),								
(1, '2025-09-05', 'Shipped'),								
(4, '2025-09-07', 'Shipped'),								
(3, '2025-10-10', 'Shipped'),								
(5, '2025-10-12', 'Shipped'),								
(1, '2025-10-15', 'Processing'),								
(2, '2025-11-01', 'Shipped'),								
(4, '2025-11-04', 'Shipped');								
								
INSERT INTO Order_Items (order_id, product_id, quantity) VALUES								
(1, 1, 1), (1, 3, 2), -- Order 1								
(2, 2, 1), (2, 5, 1), -- Order 2								
(3, 4, 1), (3, 6, 1), -- Order 3								
(4, 3, 3), (4, 4, 1), -- Order 4								
(5, 1, 1), (5, 6, 1), -- Order 5								
(6, 2, 2), (6, 5, 1), -- Order 6								
(7, 4, 2), -- Order 7								
(8, 3, 5), -- Order 8								
(9, 6, 1); -- Order 9								


-- =============================================
-- DATABASE READY FOR USE
-- =============================================