joelgilbert/NL2SQL
0
1-- ===================================================================2-- Sample E-Commerce Database Schema and Data3-- Perfect for testing the NL2SQL system!4-- ===================================================================5 6-- Drop existing tables if they exist7DROP TABLE IF EXISTS order_items CASCADE;8DROP TABLE IF EXISTS orders CASCADE;9DROP TABLE IF EXISTS products CASCADE;10DROP TABLE IF EXISTS categories CASCADE;11DROP TABLE IF EXISTS customers CASCADE;12DROP TABLE IF EXISTS regions CASCADE;13 14-- ===================================================================15-- 1. REGIONS TABLE16-- ===================================================================17CREATE TABLE regions (18 region_id SERIAL PRIMARY KEY,19 region_name VARCHAR(50) NOT NULL,20 country VARCHAR(50) NOT NULL21);22 23INSERT INTO regions (region_name, country) VALUES24('North America', 'USA'),25('Europe', 'UK'),26('Asia Pacific', 'Singapore'),27('South America', 'Brazil'),28('Middle East', 'UAE');29 30-- ===================================================================31-- 2. CUSTOMERS TABLE32-- ===================================================================33CREATE TABLE customers (34 customer_id SERIAL PRIMARY KEY,35 first_name VARCHAR(50) NOT NULL,36 last_name VARCHAR(50) NOT NULL,37 email VARCHAR(100) UNIQUE NOT NULL,38 region_id INTEGER REFERENCES regions(region_id),39 signup_date DATE NOT NULL,40 is_active BOOLEAN DEFAULT true,41 loyalty_points INTEGER DEFAULT 042);43 44INSERT INTO customers (first_name, last_name, email, region_id, signup_date, is_active, loyalty_points) VALUES45('John', 'Smith', 'john.smith@email.com', 1, '2023-01-15', true, 1250),46('Emma', 'Johnson', 'emma.j@email.com', 1, '2023-02-20', true, 2100),47('Michael', 'Williams', 'mike.w@email.com', 2, '2023-03-10', true, 850),48('Sophia', 'Brown', 'sophia.b@email.com', 2, '2023-04-05', true, 3200),49('James', 'Davis', 'james.d@email.com', 3, '2023-05-12', true, 1500),50('Olivia', 'Miller', 'olivia.m@email.com', 3, '2023-06-18', true, 950),51('Robert', 'Wilson', 'robert.w@email.com', 1, '2023-07-22', false, 200),52('Ava', 'Moore', 'ava.m@email.com', 4, '2023-08-14', true, 1750),53('William', 'Taylor', 'william.t@email.com', 4, '2023-09-09', true, 4100),54('Isabella', 'Anderson', 'isabella.a@email.com', 5, '2023-10-03', true, 2800),55('David', 'Thomas', 'david.t@email.com', 5, '2023-11-11', true, 650),56('Mia', 'Jackson', 'mia.j@email.com', 2, '2023-12-05', true, 1900),57('Joseph', 'White', 'joseph.w@email.com', 1, '2024-01-08', true, 3500),58('Charlotte', 'Harris', 'charlotte.h@email.com', 3, '2024-02-14', true, 1100),59('Daniel', 'Martin', 'daniel.m@email.com', 2, '2024-03-19', false, 50);60 61-- ===================================================================62-- 3. CATEGORIES TABLE63-- ===================================================================64CREATE TABLE categories (65 category_id SERIAL PRIMARY KEY,66 category_name VARCHAR(50) NOT NULL,67 description TEXT68);69 70INSERT INTO categories (category_name, description) VALUES71('Electronics', 'Electronic devices and accessories'),72('Clothing', 'Apparel and fashion items'),73('Home & Garden', 'Home improvement and garden supplies'),74('Books', 'Physical and digital books'),75('Sports', 'Sports equipment and gear'),76('Toys', 'Toys and games for all ages');77 78-- ===================================================================79-- 4. PRODUCTS TABLE80-- ===================================================================81CREATE TABLE products (82 product_id SERIAL PRIMARY KEY,83 product_name VARCHAR(100) NOT NULL,84 category_id INTEGER REFERENCES categories(category_id),85 price DECIMAL(10, 2) NOT NULL,86 stock_quantity INTEGER NOT NULL,87 is_featured BOOLEAN DEFAULT false,88 created_date DATE NOT NULL89);90 91INSERT INTO products (product_name, category_id, price, stock_quantity, is_featured, created_date) VALUES92-- Electronics93('Wireless Headphones Pro', 1, 199.99, 45, true, '2023-01-10'),94('Smart Watch Series 5', 1, 299.99, 32, true, '2023-02-15'),95('Laptop Stand Aluminum', 1, 49.99, 120, false, '2023-03-20'),96('USB-C Hub 7-in-1', 1, 39.99, 85, false, '2023-04-12'),97('Mechanical Keyboard RGB', 1, 129.99, 28, true, '2023-05-08'),98 99-- Clothing100('Cotton T-Shirt Pack (3)', 2, 29.99, 200, false, '2023-01-25'),101('Denim Jeans Classic', 2, 59.99, 150, false, '2023-02-18'),102('Running Shoes Premium', 2, 89.99, 75, true, '2023-03-22'),103('Winter Jacket Insulated', 2, 149.99, 45, true, '2023-09-01'),104('Baseball Cap Adjustable', 2, 19.99, 180, false, '2023-04-10'),105 106-- Home & Garden107('LED Desk Lamp', 3, 34.99, 95, false, '2023-01-30'),108('Plant Pot Set (5)', 3, 24.99, 160, false, '2023-03-15'),109('Kitchen Knife Set', 3, 79.99, 55, true, '2023-02-20'),110('Bath Towel Set Premium', 3, 44.99, 110, false, '2023-04-05'),111('Garden Tool Kit', 3, 69.99, 40, false, '2023-05-12'),112 113-- Books114('Python Programming Guide', 4, 39.99, 88, true, '2023-02-01'),115('Mystery Novel Bestseller', 4, 14.99, 250, false, '2023-03-10'),116('Cook Book International', 4, 29.99, 120, false, '2023-04-15'),117('Self-Help Success Book', 4, 19.99, 175, false, '2023-05-20'),118 119-- Sports120('Yoga Mat Premium', 5, 34.99, 95, false, '2023-02-25'),121('Dumbbells Set 20lb', 5, 89.99, 60, true, '2023-03-18'),122('Tennis Racket Pro', 5, 129.99, 35, true, '2023-04-22'),123('Basketball Official Size', 5, 24.99, 140, false, '2023-05-15'),124 125-- Toys126('Building Blocks Set 500pc', 6, 49.99, 85, true, '2023-03-01'),127('Remote Control Car', 6, 69.99, 42, false, '2023-04-08'),128('Board Game Family Pack', 6, 34.99, 110, false, '2023-05-05');129 130-- ===================================================================131-- 5. ORDERS TABLE132-- ===================================================================133CREATE TABLE orders (134 order_id SERIAL PRIMARY KEY,135 customer_id INTEGER REFERENCES customers(customer_id),136 order_date TIMESTAMP NOT NULL,137 status VARCHAR(20) NOT NULL,138 total_amount DECIMAL(10, 2) NOT NULL,139 shipping_address TEXT140);141 142INSERT INTO orders (customer_id, order_date, status, total_amount, shipping_address) VALUES143-- Recent orders (last 30 days)144(1, '2024-11-20 10:30:00', 'delivered', 249.98, '123 Main St, New York, NY'),145(2, '2024-11-19 14:15:00', 'delivered', 129.99, '456 Oak Ave, Los Angeles, CA'),146(3, '2024-11-18 09:45:00', 'shipped', 89.99, '789 Pine Rd, London, UK'),147(4, '2024-11-17 16:20:00', 'delivered', 199.99, '321 Elm St, Manchester, UK'),148(5, '2024-11-16 11:00:00', 'processing', 349.97, '654 Maple Dr, Singapore'),149 150-- Last week151(6, '2024-11-15 13:30:00', 'delivered', 79.99, '987 Cedar Ln, Tokyo, Japan'),152(7, '2024-11-14 08:15:00', 'cancelled', 44.99, '147 Birch Ave, New York, NY'),153(8, '2024-11-13 15:45:00', 'delivered', 229.97, '258 Spruce St, São Paulo, Brazil'),154(9, '2024-11-12 10:00:00', 'delivered', 559.96, '369 Willow Rd, Rio de Janeiro, Brazil'),155(10, '2024-11-11 12:30:00', 'shipped', 149.99, '741 Ash Ct, Dubai, UAE'),156 157-- Last month158(11, '2024-10-28 14:20:00', 'delivered', 89.99, '852 Poplar Dr, Abu Dhabi, UAE'),159(1, '2024-10-25 16:45:00', 'delivered', 299.98, '123 Main St, New York, NY'),160(2, '2024-10-22 09:30:00', 'delivered', 159.98, '456 Oak Ave, Los Angeles, CA'),161(13, '2024-10-20 11:15:00', 'delivered', 449.97, '963 Beach Rd, Miami, FL'),162(3, '2024-10-18 13:00:00', 'delivered', 249.99, '789 Pine Rd, London, UK'),163 164-- Older orders165(4, '2024-09-15 10:00:00', 'delivered', 319.98, '321 Elm St, Manchester, UK'),166(5, '2024-09-10 14:30:00', 'delivered', 199.99, '654 Maple Dr, Singapore'),167(12, '2024-09-05 16:15:00', 'delivered', 129.99, '159 Lake St, Paris, France'),168(14, '2024-08-28 11:45:00', 'delivered', 279.97, '357 River Ave, Berlin, Germany'),169(6, '2024-08-20 09:20:00', 'delivered', 89.99, '987 Cedar Ln, Tokyo, Japan');170 171-- ===================================================================172-- 6. ORDER_ITEMS TABLE173-- ===================================================================174CREATE TABLE order_items (175 order_item_id SERIAL PRIMARY KEY,176 order_id INTEGER REFERENCES orders(order_id),177 product_id INTEGER REFERENCES products(product_id),178 quantity INTEGER NOT NULL,179 unit_price DECIMAL(10, 2) NOT NULL,180 subtotal DECIMAL(10, 2) NOT NULL181);182 183INSERT INTO order_items (order_id, product_id, quantity, unit_price, subtotal) VALUES184-- Order 1 items185(1, 1, 1, 199.99, 199.99),186(1, 4, 1, 49.99, 49.99),187 188-- Order 2 items189(2, 5, 1, 129.99, 129.99),190 191-- Order 3 items192(3, 8, 1, 89.99, 89.99),193 194-- Order 4 items195(4, 1, 1, 199.99, 199.99),196 197-- Order 5 items198(5, 2, 1, 299.99, 299.99),199(5, 4, 1, 49.99, 49.99),200 201-- Order 6 items202(6, 13, 1, 79.99, 79.99),203 204-- Order 7 items (cancelled)205(7, 14, 1, 44.99, 44.99),206 207-- Order 8 items208(8, 16, 1, 39.99, 39.99),209(8, 17, 2, 14.99, 29.98),210(8, 20, 5, 34.99, 174.95),211 212-- Order 9 items213(9, 2, 1, 299.99, 299.99),214(9, 21, 1, 89.99, 89.99),215(9, 22, 2, 129.99, 259.98),216 217-- Order 10 items218(10, 9, 1, 149.99, 149.99),219 220-- Order 11 items221(11, 8, 1, 89.99, 89.99),222 223-- Order 12 items224(12, 1, 1, 199.99, 199.99),225(12, 5, 1, 129.99, 129.99),226 227-- Order 13 items228(13, 6, 2, 29.99, 59.98),229(13, 7, 1, 59.99, 59.99),230(13, 10, 1, 19.99, 19.99),231 232-- Order 14 items233(14, 2, 1, 299.99, 299.99),234(14, 3, 1, 49.99, 49.99),235(14, 24, 2, 24.99, 49.98),236 237-- Order 15 items238(15, 13, 1, 79.99, 79.99),239(15, 15, 1, 69.99, 69.99),240 241-- Order 16 items242(16, 21, 2, 89.99, 179.98),243(16, 8, 1, 89.99, 89.99),244(16, 10, 1, 19.99, 19.99),245 246-- Order 17 items247(17, 1, 1, 199.99, 199.99),248 249-- Order 18 items250(18, 5, 1, 129.99, 129.99),251 252-- Order 19 items253(19, 22, 1, 129.99, 129.99),254(19, 8, 1, 89.99, 89.99),255(19, 16, 1, 39.99, 39.99),256 257-- Order 20 items258(20, 8, 1, 89.99, 89.99);259 260-- ===================================================================261-- CREATE INDEXES FOR BETTER QUERY PERFORMANCE262-- ===================================================================263CREATE INDEX idx_customers_region ON customers(region_id);264CREATE INDEX idx_customers_active ON customers(is_active);265CREATE INDEX idx_products_category ON products(category_id);266CREATE INDEX idx_orders_customer ON orders(customer_id);267CREATE INDEX idx_orders_date ON orders(order_date);268CREATE INDEX idx_orders_status ON orders(status);269CREATE INDEX idx_order_items_order ON order_items(order_id);270CREATE INDEX idx_order_items_product ON order_items(product_id);271 272-- ===================================================================273-- CREATE VIEWS FOR COMMON QUERIES274-- ===================================================================275 276-- Sales summary view277CREATE OR REPLACE VIEW sales_summary AS278SELECT 279 c.category_name,280 COUNT(DISTINCT o.order_id) as order_count,281 SUM(oi.quantity) as units_sold,282 SUM(oi.subtotal) as total_revenue283FROM categories c284JOIN products p ON c.category_id = p.category_id285JOIN order_items oi ON p.product_id = oi.product_id286JOIN orders o ON oi.order_id = o.order_id287WHERE o.status != 'cancelled'288GROUP BY c.category_name;289 290-- Customer lifetime value view291CREATE OR REPLACE VIEW customer_lifetime_value AS292SELECT 293 c.customer_id,294 c.first_name || ' ' || c.last_name as customer_name,295 c.email,296 r.region_name,297 COUNT(o.order_id) as total_orders,298 SUM(o.total_amount) as total_spent,299 AVG(o.total_amount) as avg_order_value300FROM customers c301LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status != 'cancelled'302LEFT JOIN regions r ON c.region_id = r.region_id303GROUP BY c.customer_id, c.first_name, c.last_name, c.email, r.region_name;304 305-- ===================================================================306-- GRANT PERMISSIONS (for read-only user)307-- ===================================================================308-- Run these commands separately after creating your readonly user:309-- 310-- GRANT CONNECT ON DATABASE your_database TO readonly_user;311-- GRANT USAGE ON SCHEMA public TO readonly_user;312-- GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;313-- GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;314-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;315 316-- ===================================================================317-- VERIFICATION QUERIES318-- ===================================================================319 320-- Check row counts321SELECT 'regions' as table_name, COUNT(*) as row_count FROM regions322UNION ALL323SELECT 'customers', COUNT(*) FROM customers324UNION ALL325SELECT 'categories', COUNT(*) FROM categories326UNION ALL327SELECT 'products', COUNT(*) FROM products328UNION ALL329SELECT 'orders', COUNT(*) FROM orders330UNION ALL331SELECT 'order_items', COUNT(*) FROM order_items;332 333-- Show sample data334SELECT 'Sample Customers:' as info;335SELECT customer_id, first_name, last_name, email FROM customers LIMIT 5;336 337SELECT 'Sample Products:' as info;338SELECT product_id, product_name, price FROM products LIMIT 5;339 340SELECT 'Sample Orders:' as info;341SELECT order_id, customer_id, order_date, status, total_amount FROM orders LIMIT 5;342 343SELECT 'Sample Views:' as info;344SELECT * FROM sales_summary;345 346-- ===================================================================347-- DONE! Your database is ready for NL2SQL testing!348-- ===================================================================349