level1_estore_basic.db

-- =============================================
-- The E-Store Basic Sample Database
-- For SQL Learning At Beginner Level
-- =============================================

-- =============================================
--CREATING TABLES
-- =============================================								
								
CREATE TABLE Customers (								
customer_id INTEGER PRIMARY KEY,								
name TEXT,								
email TEXT,								
city TEXT								
);								
								
CREATE TABLE Products (								
product_id INTEGER PRIMARY KEY,								
product_name TEXT,								
category TEXT,								
price REAL								
);								
								
CREATE TABLE Orders (								
order_id INTEGER PRIMARY KEY,								
customer_id INTEGER,								
order_date 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, city) VALUES								
('Alice Smith', 'alice@email.com', 'New York'),								
('Bob Johnson', 'bob@email.com', 'Chicago'),								
('Charlie Lee', 'charlie@email.com', 'New York'),								
('David Kim', 'david@email.com', 'San Francisco'),								
('Eva Gomez', 'eva@email.com', 'Chicago');								
								
INSERT INTO Products (product_name, category, price) VALUES								
('Laptop', 'Electronics', 1200.00),								
('Coffee Maker', 'Appliances', 80.00),								
('SQL Essentials Book', 'Books', 45.00),								
('Headphones', 'Electronics', 150.00),								
('Blender', 'Appliances', 60.00);								
								
INSERT INTO Orders (customer_id, order_date) VALUES								
(1, '2025-10-01'),  -- Alice								
(2, '2025-10-01'),  -- Bob								
(1, '2025-10-03'),  -- Alice								
(4, '2025-10-04'),  -- David								
(5, '2025-10-05');  -- Eva								
								
INSERT INTO Order_Items (order_id, product_id, quantity) VALUES								
(1, 1, 1),  -- Order 1 (Alice): 1 Laptop								
(1, 3, 1),  -- Order 1 (Alice): 1 SQL Book								
(2, 2, 1),  -- Order 2 (Bob): 1 Coffee Maker								
(3, 4, 2),  -- Order 3 (Alice): 2 Headphones								
(4, 3, 1),  -- Order 4 (David): 1 SQL Book								
(4, 5, 1);  -- Order 4 (David): 1 Blender		

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