Supermarket database

CREATE DATABASE supermarket;
USE supermarket;

CREATE TABLE Positions (
position_id CHAR(5) NOT NULL PRIMARY KEY,
position_name VARCHAR(100) NOT NULL
);

INSERT INTO Positions (position_id, position_name) VALUES
('P001', 'Manager'),
('P002', 'Cashier'),
('P003', 'Sales Associate'),
('P004', 'Security Guard'),
('P005', 'Accountant'),
('P006', 'Customer Service Representative'),
('P007', 'Janitor'),
('P008', 'Delivery Staff');

SELECT * FROM Positions;

CREATE TABLE Staffs (
staff_id CHAR(10) NOT NULL PRIMARY KEY,
position_id CHAR(5) NOT NULL,
full_name VARCHAR(100) NOT NULL,
phone_number CHAR(10) NOT NULL,
salary DOUBLE NOT NULL,
hire_date DATE NOT NULL,
gender CHAR(10) NOT NULL,
CONSTRAINT fk_staffs_positions FOREIGN KEY (position_id) REFERENCES Positions(position_id) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO Staffs (staff_id, position_id, full_name, phone_number, salary, hire_date, gender) VALUES
('S10001', 'P001', 'John Smith', '0987654321', 1500.00, '2022-01-15', 'Male'),
('S10002', 'P001', 'Emma Johnson', '0978456123', 1600.00, '2021-05-10', 'Female'),
('S20013', 'P002', 'James Brown', '0965234789', 1200.30, '2023-02-20', 'Male'),
('S20024', 'P002', 'Olivia Wilson', '0956783412', 1250.00, '2022-07-05', 'Male'),
('S30035', 'P003', 'William Davis', '0945123768', 1100.50, '2023-08-18', 'Female'),
('S30046', 'P003', 'Sophia Martinez', '0936125478', 1150.00, '2021-09-30', 'Female'),
('S40057', 'P004', 'Michael Taylor', '0925789632', 1050.60, '2023-06-25', 'Male'),
('S40068', 'P004', 'Isabella Anderson', '0914782365', 1080.70, '2022-03-12', 'Female'),
('S50079', 'P005', 'Daniel Thomas', '0903678945', 980.00, '2023-01-01', 'Female'),
('S50080', 'P005', 'Mia Hernandez', '0892563147', 1000.40, '2021-12-22', 'Male'),
('S60091', 'P006', 'Matthew Moore', '0881456239', 1400.00, '2022-11-11', 'Male'),
('S60102', 'P006', 'Charlotte Jackson', '0870345128', 1450.00, '2023-04-08', 'Female'),
('S70113', 'P007', 'David White', '0869237415', 1100.00, '2021-06-19', 'Female'),
('S70124', 'P007', 'Amelia Harris', '0858123569', 1120.10, '2022-09-14', 'Female'),
('S80135', 'P008', 'Benjamin Clark', '0847012458', 1300.00, '2023-05-21', 'Male'),
('S80146', 'P008', 'Evelyn Lewis', '0836904781', 1350.80, '2021-08-30', 'Female'),
('S20191', 'P002', 'Ethan Scott', '0781357924', 1220.00, '2022-05-15', 'Male'),
('S40192', 'P004', 'Ava Green', '0770246813', 1060.30, '2023-09-12', 'Female'),
('S60193', 'P006', 'Alexander Adams', '0769135702', 1420.00, '2021-07-05', 'Male');

select * from staffs;

CREATE TABLE Accounts (
username VARCHAR(50) NOT NULL PRIMARY KEY,
position_id CHAR(5) NOT NULL,
staff_id CHAR(10) NOT NULL,
password VARCHAR(255) NOT NULL,
CONSTRAINT fk_accounts_staffs FOREIGN KEY (staff_id) REFERENCES Staffs(staff_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_accounts_positions FOREIGN KEY (position_id) REFERENCES Positions(position_id) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO Accounts (username, position_id, staff_id, password) VALUES
('S10001@P001', 'P001', 'S10001', 'X7f!p9Kz'),
('S10002@P001', 'P001', 'S10002', 'Jd3LmNq'),
('S20013@P002', 'P002', 'S20013', 'Qw8&YrTp'),
('S20024@P002', 'P002', 'S20024', 'Zk5DsNv'),
('S30035@P003', 'P003', 'S30035', 'Vx1#HcMw'),
('S30046@P003', 'P003', 'S30046', 'Bn4@KpXz'),
('S40057@P004', 'P004', 'S40057', 'Mm7!QsLp'),
('S40068@P004', 'P004', 'S40068', 'Rt9YwNz'),
('S50079@P005', 'P005', 'S50079', 'Xz3&VcMq'),
('S50080@P005', 'P005', 'S50080', 'Pw5NdKt'),
('S60091@P006', 'P006', 'S60091', 'Yq8#LxHp'),
('S60102@P006', 'P006', 'S60102', 'Nm2@ZkVt'),
('S70113@P007', 'P007', 'S70113', 'Hp6!QxYw'),
('S70124@P007', 'P007', 'S70124', 'Kt4$VcMz'),
('S80135@P008', 'P008', 'S80135', 'Mw9&DsNp'),
('S80146@P008', 'P008', 'S80146', 'Lx1KpYq'),
('S20191@P002', 'P002', 'S20191', 'Xz6&QwNp'),
('S40192@P004', 'P004', 'S40192', 'Kt2VcYq'),
('S60193@P006', 'P006', 'S60193', 'Mw4#NmZp');

select * from accounts;

CREATE TABLE Categories (
category_id CHAR(10) NOT NULL PRIMARY KEY,
category_name VARCHAR(50) NOT NULL,
description LONGTEXT NOT NULL
);

INSERT INTO Categories (category_id, category_name, description) VALUES
('BEV102', 'Beverages', 'Soft drinks, coffee, tea, and juices'),
('DAI215', 'Dairy Products', 'Milk, cheese, butter, and yogurt'),
('BAK327', 'Bakery', 'Bread, cakes, pastries, and cookies'),
('MEA431', 'Meat & Seafood', 'Fresh and frozen meat, fish, and seafood'),
('FRU547', 'Fruits & Vegetables', 'Fresh fruits and vegetables'),
('SNA659', 'Snacks', 'Chips, biscuits, candies, and chocolates'),
('FRO763', 'Frozen Foods', 'Frozen meals, vegetables, and desserts'),
('PER879', 'Personal Care', 'Shampoo, soap, toothpaste, and skincare'),
('HOU981', 'Household Supplies', 'Cleaning products, paper towels, and detergents'),
('CAN104', 'Canned Goods', 'Canned vegetables, fruits, and meats'),
('ALC216', 'Beverage Alcohol', 'Beer, wine, and spirits'),
('GRA328', 'Grains & Cereals', 'Rice, pasta, oats, and breakfast cereals'),
('CON432', 'Condiments & Sauces', 'Ketchup, mayonnaise, soy sauce, and spices'),
('BAB546', 'Baby Products', 'Diapers, baby food, and baby care essentials'),
('PET658', 'Pet Supplies', 'Pet food, grooming products, and accessories'),
('ELE001', 'Electronics', 'Consumer electronics such as phones, headphones, and power banks.');

select * from categories;

CREATE TABLE Suppliers (
supplier_id CHAR(10) NOT NULL PRIMARY KEY,
supplier_name VARCHAR(50) NOT NULL,
hotline CHAR(10) NOT NULL,
email VARCHAR(50) NOT NULL
);

INSERT INTO Suppliers (supplier_id, supplier_name, hotline, email) VALUES
('BEV1017', 'Coca-Cola Company', '0123 4567', '[email protected]'),
('BEV2058', 'PepsiCo', '0456 7890', '[email protected]'),
('DAI3079', 'Vinamilk', '0789 1234', '[email protected]'),
('DAI4121', 'Nestlé Dairy', '0234 5678', '[email protected]'),
('BAK5195', 'ABC Bakery', '0567 8901', '[email protected]'),
('BAK6247', 'Kinh Đô Bakery', '0678 9012', '[email protected]'),
('MEA7388', 'Vissan Meat', '0789 0123', '[email protected]'),
('MEA84567', 'CP Vietnam', '0890 1234', '[email protected]'),
('FRU95443', 'FruitKing', '0901 2345', '[email protected]'),
('FRU1086', 'Dalat Farm', '0123 5678', '[email protected]'),
('SNA21345', 'Orion Vina', '0345 6789', '[email protected]'),
('SNA3299', 'Oishi', '0456 7890', '[email protected]'),
('FRO4172', 'Ajinomoto', '0567 8901', '[email protected]'),
('FRO529234', 'Cau Tre Foods', '0678 9012', '[email protected]'),
('PER63445', 'Unilever', '0789 0123', '[email protected]'),
('PER74165', 'P&G Vietnam', '0890 2345', '[email protected]'),
('HOU85243', 'Lix Co.', '0901 3456', '[email protected]'),
('HOU9631', 'Sunlight', '0123 4567', '[email protected]'),
('CAN107', 'Heinz', '0345 6789', '[email protected]'),
('CAN214', 'Vissan Canned', '0456 7890', '[email protected]'),
('ALC309', 'Sabeco', '0567 8901', '[email protected]'),
('ALC415456', 'Habeco', '0678 9012', '[email protected]'),
('GRA52865', 'Quaker Oats', '0789 0123', '[email protected]'),
('GRA6393', 'Vinacam Rice', '0890 2345', '[email protected]'),
('CON748', 'Chin-Su Foods', '0901 3456', '[email protected]'),
('CON859', 'Knorr Vietnam', '0123 4567', '[email protected]'),
('BAB964', 'Pampers', '0345 6789', '[email protected]'),
('BAB10534', 'Johnson’s Baby', '0456 7890', '[email protected]'),
('PET213', 'Whiskas', '0567 8901', '[email protected]'),
('PET324', 'Pedigree', '0678 9012', '[email protected]'),
('PET437', 'Me-O', '0789 0123', '[email protected]'),
('PET542', 'Royal Canin', '0890 2345', '[email protected]'),
('DAI65345', 'Ba Vì Dairy', '0901 3456', '[email protected]'),
('SNA765', 'Lay’s Chips', '0123 4567', '[email protected]'),
('HOU876', 'OMO', '0345 6789', '[email protected]'),
('ELE101', 'Samsung Electronics', '0123 4567', '[email protected]'),
('ELE202', 'Sony Corporation', '0456 7890', '[email protected]');

select * from suppliers;

CREATE TABLE Products (
product_id CHAR(15) NOT NULL PRIMARY KEY,
category_id CHAR(10) NOT NULL,
supplier_id CHAR(10) NOT NULL,
product_name VARCHAR(100) NOT NULL,
price DOUBLE NOT NULL,
stock_quantity INT NOT NULL,
unit VARCHAR(50) NOT NULL,
description LONGTEXT NOT NULL,
CONSTRAINT fk_products_categories FOREIGN KEY (category_id) REFERENCES Categories(category_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_products_suppliers FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO Products (product_id, category_id, supplier_id, product_name, price, stock_quantity, unit, description) VALUES
('BEV1010001', 'BEV102', 'BEV1017', 'Coca-Cola Original', 0.99, 500, 'Bottle (500ml)', 'Refreshing carbonated soft drink with classic cola taste.'),
('BEV1010002', 'BEV102', 'BEV2058', 'Pepsi Max', 1.10, 450, 'Can (330ml)', 'Sugar-free cola with bold taste and zero calories.'),
('BEV1010003', 'BEV102', 'BEV1017', 'Fanta Orange', 1.20, 300, 'Bottle (1L)', 'Citrus-flavored carbonated soft drink with real fruit taste.'),
('DAI3070004', 'DAI215', 'DAI3079', 'Vinamilk Fresh Milk', 1.50, 400, 'Box (1L)', 'Pasteurized fresh milk rich in calcium and vitamins.'),
('DAI3070005', 'DAI215', 'DAI4121', 'Nestlé Yogurt', 2.00, 250, 'Cup (180g)', 'Creamy probiotic yogurt with natural fruit flavors.'),
('DAI3070006', 'DAI215', 'DAI65345', 'Ba Vì Sweetened Condensed Milk', 1.75, 600, 'Can (380g)', 'Thick, sweetened condensed milk perfect for coffee and desserts.'),
('BAK5190007', 'BAK327', 'BAK5195', 'ABC French Baguette', 2.50, 120, 'Loaf', 'Classic crispy French-style baguette, freshly baked.'),
('BAK5190008', 'BAK327', 'BAK6247', 'Kinh Đô Butter Croissant', 3.20, 150, 'Pack (3 pcs)', 'Flaky, buttery croissant with a soft interior.'),
('BAK5190009', 'BAK327', 'BAK5195', 'Chocolate Chip Cookies', 4.00, 200, 'Box (300g)', 'Crispy cookies with rich chocolate chips.'),
('MEA7380010', 'MEA431', 'MEA7388', 'Vissan Pork Belly', 5.50, 100, 'Kg', 'Fresh and tender pork belly, ideal for grilling and stewing.'),
('MEA7380011', 'MEA431', 'MEA84567', 'CP Chicken Drumsticks', 4.20, 250, 'Kg', 'High-quality chicken drumsticks with a juicy texture.'),
('MEA7380012', 'MEA431', 'MEA7388', 'Frozen Salmon Fillet', 9.80, 80, 'Kg', 'Premium Norwegian salmon fillet, rich in Omega-3.'),
('FRU9540013', 'FRU547', 'FRU95443', 'Fresh Banana', 1.20, 300, 'Kg', 'Sweet and ripe Cavendish bananas, rich in potassium.'),
('FRU9540014', 'FRU547', 'FRU1086', 'Dalat Strawberries', 6.50, 150, 'Box (500g)', 'Fresh, juicy strawberries from Dalat highlands.'),
('FRU9540015', 'FRU547', 'FRU1086', 'Organic Carrots', 2.20, 220, 'Kg', 'Crisp and sweet organic carrots, perfect for salads and cooking.'),
('SNA2130016', 'SNA659', 'SNA21345', 'Orion Choco Pie', 3.99, 500, 'Box (12 pcs)', 'Soft chocolate-coated cakes with marshmallow filling.'),
('SNA2130017', 'SNA659', 'SNA3299', 'Oishi Prawn Crackers', 2.50, 400, 'Pack (200g)', 'Crunchy prawn-flavored snack, lightly salted.'),
('SNA2130018', 'SNA659', 'SNA765', 'Lay’s Classic Potato Chips', 3.50, 450, 'Bag (150g)', 'Crispy and lightly salted potato chips.'),
('FRO4170019', 'FRO763', 'FRO4172', 'Ajinomoto Gyoza', 5.99, 200, 'Pack (400g)', 'Japanese-style dumplings filled with pork and vegetables.'),
('FRO4170020', 'FRO763', 'FRO529234', 'Cau Tre Spring Rolls', 4.50, 300, 'Pack (500g)', 'Vietnamese spring rolls with pork and shrimp filling.'),
('FRO4170021', 'FRO763', 'FRO529234', 'Frozen Mixed Vegetables', 3.20, 350, 'Bag (1Kg)', 'Pre-cut frozen vegetables, perfect for stir-frying.'),
('HOU8520022', 'HOU981', 'HOU85243', 'Lix Laundry Detergent', 8.50, 500, 'Bottle (3L)', 'Powerful cleaning detergent with fresh scent.'),
('HOU9630023', 'HOU981', 'HOU9631', 'Sunlight Dishwashing Liquid', 4.99, 600, 'Bottle (750ml)', 'Removes grease and leaves dishes sparkling clean.'),
('HOU8760024', 'HOU981', 'HOU876', 'OMO Washing Powder', 7.20, 450, 'Bag (2.5Kg)', 'Deep clean technology, removes tough stains.'),
('ALC3090025', 'ALC216', 'ALC309', 'Sabeco Saigon Beer', 1.80, 700, 'Can (330ml)', 'Classic Vietnamese beer with a smooth taste.'),
('ALC4150026', 'ALC216', 'ALC415456', 'Habeco Hanoi Beer', 1.70, 650, 'Can (330ml)', 'Crisp and refreshing Hanoi-style beer.'),
('ALC4150027', 'ALC216', 'ALC309', 'Heineken Premium Lager', 2.20, 500, 'Bottle (330ml)', 'Internationally famous lager with rich malt flavor.'),-- Baby Products (BAB)
('BAB9640028', 'BAB546', 'BAB964', 'Pampers Baby Diapers', 12.99, 400, 'Pack (60 pcs)', 'Soft and absorbent diapers for all-day comfort.'),
('BAB1050029', 'BAB546', 'BAB10534', 'Johnson’s Baby Lotion', 5.50, 300, 'Bottle (500ml)', 'Gentle lotion for baby’s delicate skin.'),
('BAB9640030', 'BAB546', 'BAB964', 'Pampers Baby Wipes', 3.99, 500, 'Pack (80 sheets)', 'Mild and hypoallergenic wipes for sensitive skin.'),
('BEV1010031', 'BEV102', 'BEV1017', 'Sprite Lemon Soda', 1.15, 400, 'Bottle (500ml)', 'Refreshing lemon-lime flavored soda with crisp taste.'),
('BEV1010032', 'BEV102', 'BEV2058', 'Lipton Ice Tea Lemon', 1.30, 350, 'Can (330ml)', 'Cold and refreshing lemon-flavored iced tea.'),
('BEV1010033', 'BEV102', 'BEV1017', 'Minute Maid Orange Juice', 2.50, 300, 'Bottle (1L)', 'Freshly squeezed orange juice, rich in vitamin C.'),
('DAI3070034', 'DAI215', 'DAI3079', 'Vinamilk Chocolate Milk', 1.75, 400, 'Box (1L)', 'Delicious chocolate-flavored milk, rich in calcium.'),
('DAI3070035', 'DAI215', 'DAI4121', 'Nestlé Condensed Milk', 2.20, 500, 'Can (380g)', 'Sweetened condensed milk for coffee and desserts.'),
('DAI3070036', 'DAI215', 'DAI65345', 'Ba Vì Yogurt Drink', 1.99, 350, 'Bottle (330ml)', 'Fermented yogurt drink with probiotics, great for digestion.'),
('BAK5190037', 'BAK327', 'BAK6247', 'Kinh Đô Mooncake', 5.50, 200, 'Box (2 pcs)', 'Traditional mooncake filled with lotus seed paste.'),
('BAK5190038', 'BAK327', 'BAK5195', 'ABC Whole Wheat Bread', 3.00, 300, 'Loaf', 'Healthy whole wheat bread, rich in fiber.'),
('BAK5190039', 'BAK327', 'BAK6247', 'Pandan Chiffon Cake', 4.50, 150, 'Pack (500g)', 'Soft and fluffy chiffon cake with natural pandan flavor.'),
('MEA7380040', 'MEA431', 'MEA84567', 'CP Frozen Chicken Wings', 4.80, 300, 'Kg', 'Premium frozen chicken wings, perfect for frying.'),
('MEA7380041', 'MEA431', 'MEA7388', 'Vissan Beef Steak', 9.99, 120, 'Kg', 'High-quality beef steak, tender and juicy.'),
('MEA7380042', 'MEA431', 'MEA84567', 'Shrimp (Frozen)', 6.99, 250, 'Kg', 'Fresh frozen shrimp, ideal for grilling or frying.'),
('SNA2130043', 'SNA659', 'SNA21345', 'Orion Custard Cake', 3.75, 450, 'Box (12 pcs)', 'Soft sponge cake filled with rich custard cream.'),
('SNA2130044', 'SNA659', 'SNA3299', 'Oishi Cheese Sticks', 2.80, 400, 'Pack (200g)', 'Crispy cheese-flavored snacks, perfect for kids.'),
('SNA2130045', 'SNA659', 'SNA765', 'Lay’s Sour Cream Chips', 3.99, 500, 'Bag (150g)', 'Savory potato chips with sour cream and onion flavor.'),
('FRO4170046', 'FRO763', 'FRO4172', 'Ajinomoto Fried Rice', 5.20, 300, 'Pack (500g)', 'Frozen fried rice with vegetables and chicken.'),
('FRO4170047', 'FRO763', 'FRO529234', 'Cau Tre Fish Balls', 4.99, 400, 'Pack (500g)', 'Frozen fish balls, perfect for soups and hotpots.'),
('FRO4170048', 'FRO763', 'FRO529234', 'Frozen Chicken Nuggets', 6.50, 350, 'Pack (1Kg)', 'Crunchy breaded chicken nuggets, ready to fry.'),
('ALC3090049', 'ALC216', 'ALC309', 'Tiger Beer', 1.85, 600, 'Can (330ml)', 'Refreshing and smooth beer with a balanced taste.'),
('ALC4150050', 'ALC216', 'ALC415456', 'Budweiser Lager', 2.50, 500, 'Bottle (330ml)', 'Premium American-style lager with a crisp finish.'),
('ELE0000001', 'ELE001', 'ELE101', 'Samsung Galaxy A54', 750.00, 100, 'Piece', 'A smartphone with a 6.4-inch Super AMOLED display and a 50MP camera.'),
('ELE0000005', 'ELE001', 'ELE101', 'Samsung Galaxy S24 Ultra', 1299.99, 50, 'Piece', 'Flagship smartphone with a 6.8-inch Dynamic AMOLED 2X display, Snapdragon 8 Gen 3, 200MP camera.'),
('ELE0000003', 'ELE001', 'ELE202', 'Sony WH-1000XM5', 399.99, 50, 'Piece', 'Premium noise-canceling headphones with 30-hour battery life and Bluetooth 5.2.'),
('ELE0000004', 'ELE001', 'ELE202', 'Sony SRS-XB43', 199.99, 80, 'Piece', 'A powerful portable Bluetooth speaker with extra bass and waterproof design.');

select * from products;

CREATE TABLE Customers (
customer_id CHAR(10) NOT NULL PRIMARY KEY,
gender CHAR(10) NOT NULL,
phone_number CHAR(10) NOT NULL,
full_name VARCHAR(100) NOT NULL,
location POINT NOT NULL,
email VARCHAR(100) NOT NULL
);

INSERT INTO Customers (customer_id, gender, phone_number, full_name, location, email) VALUES
('CUSTAB12C3', 'Male', '1202555012', 'James Anderson', ST_GeomFromText('POINT(40.7128 -74.0060)'), '[email protected]'),
('CUSTX9Y86D', 'Female', '4420718387', 'Emma Brown', ST_GeomFromText('POINT(51.5074 -0.1278)'), '[email protected]'),
('CUSTK1L2M3', 'Male', '3314020304', 'Louis Martin', ST_GeomFromText('POINT(48.8566 2.3522)'), '[email protected]'),
('CUSTP5Q6S8', 'Female', '4903051234', 'Sophia Müller', ST_GeomFromText('POINT(52.5200 13.4050)'), '[email protected]'),
('CUSTD9E7G6', 'Male', '8133345566', 'Taro Tanaka', ST_GeomFromText('POINT(35.6895 139.6917)'), '[email protected]'),
('CUSTH5J7K8', 'Female', '8223456789', 'Ji-Hyun Kim', ST_GeomFromText('POINT(37.5665 126.9780)'), '[email protected]'),
('CUSTB1C2E4', 'Male', '5524987654', 'Carlos Silva', ST_GeomFromText('POINT(-23.5505 -46.6333)'), '[email protected]'),
('CUSTW5X6Y8', 'Female', '6141234568', 'Olivia Wilson', ST_GeomFromText('POINT(-33.8688 151.2093)'), '[email protected]'),
('CUSTM9O7P6', 'Male', '6281234567', 'Budi Santoso', ST_GeomFromText('POINT(-6.2088 106.8456)'), '[email protected]'),
('CUSTQ1R3T4', 'Female', '9198765432', 'Priya Sharma', ST_GeomFromText('POINT(28.6139 77.2090)'), '[email protected]');

select * from customers;

CREATE TABLE Orders (
order_id CHAR(15) NOT NULL PRIMARY KEY,
customer_id CHAR(10) NOT NULL,
staff_id CHAR(10) NOT NULL,
order_date DATE NOT NULL,
CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_orders_staffs FOREIGN KEY (staff_id) REFERENCES Staffs(staff_id) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO Orders (order_id, customer_id, staff_id, order_date) VALUES
('ORDX9Y86D4A', 'CUSTX9Y86D', 'S20013', '2024-05-14'),
('ORDK1L2M3Z9', 'CUSTK1L2M3', 'S20024', '2024-06-21'),
('ORDP5Q6S8W7', 'CUSTP5Q6S8', 'S20024', '2024-07-08'),
('ORDD9E7G6B3', 'CUSTD9E7G6', 'S20191', '2024-08-19'),
('ORDH5J7K8V1', 'CUSTH5J7K8', 'S20191', '2024-09-30'),
('ORDB1C2E4M2', 'CUSTB1C2E4', 'S20024', '2024-10-25'),
('ORDW5X6Y8K5', 'CUSTW5X6Y8', 'S20013', '2024-11-13'),
('ORDM9O7P6N8', 'CUSTM9O7P6', 'S20013', '2024-12-27'),
('ORDQ1R3T4C7', 'CUSTQ1R3T4', 'S20024', '2025-01-17'),
('ORDAB12C3X4', 'CUSTAB12C3', 'S20013', '2025-02-05'),
('ORDCUSTX9Y4', 'CUSTX9Y86D', 'S20024', '2025-03-01'),
('ORDCUSTK1L5', 'CUSTK1L2M3', 'S20191', '2025-03-10');

select * from orders;

CREATE TABLE OrderDetails (
order_detail_id CHAR(10) NOT NULL PRIMARY KEY,
order_id CHAR(15) NOT NULL,
product_id CHAR(15) NOT NULL,
price DOUBLE NOT NULL,
quantity INT NOT NULL,
total_price DOUBLE GENERATED ALWAYS AS (price * quantity) STORED,
CONSTRAINT fk_oddts_orders FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_oddts_products FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO OrderDetails (order_detail_id, order_id, product_id, price, quantity) VALUES
('48291', 'ORDX9Y86D4A', 'BEV1010001', 0.99, 5),
('73025', 'ORDX9Y86D4A', 'DAI3070004', 1.50, 2),
('67903', 'ORDX9Y86D4A', 'SNA2130016', 3.99, 2),
('15682', 'ORDK1L2M3Z9', 'ALC4150026', 1.70, 6),
('90437', 'ORDK1L2M3Z9', 'SNA2130016', 3.99, 3),
('58214', 'ORDP5Q6S8W7', 'FRO4170019', 5.99, 1),
('39870', 'ORDP5Q6S8W7', 'MEA7380010', 5.50, 4),
('67123', 'ORDD9E7G6B3', 'FRU9540013', 1.20, 10),
('21094', 'ORDD9E7G6B3', 'BAK5190008', 3.20, 2),
('83562', 'ORDH5J7K8V1', 'DAI3070006', 1.75, 3),
('32917', 'ORDH5J7K8V1', 'FRO4170020', 4.50, 2),
('54876', 'ORDB1C2E4M2', 'ELE0000001', 750.00, 1),
('90145', 'ORDW5X6Y8K5', 'ELE0000005', 1299.99, 1),
('76280', 'ORDM9O7P6N8', 'DAI3070035', 2.20, 4),
('39821', 'ORDM9O7P6N8', 'BAK5190037', 5.50, 1),
('47369', 'ORDQ1R3T4C7', 'MEA7380040', 4.80, 3),
('68520', 'ORDQ1R3T4C7', 'ALC3090049', 1.85, 6),
('12039', 'ORDQ1R3T4C7', 'BEV1010001', 0.99, 5),
('94830', 'ORDAB12C3X4', 'SNA2130043', 3.75, 4),
('56729', 'ORDAB12C3X4', 'FRO4170046', 5.20, 2),
('39401', 'ORDCUSTX9Y4', 'FRO4170047', 4.99, 5),
('72056', 'ORDCUSTX9Y4', 'DAI3070036', 1.99, 3),
('10583', 'ORDCUSTK1L5', 'HOU8760024', 7.20, 2),
('83642', 'ORDCUSTK1L5', 'ALC4150050', 2.50, 4);

select * from orderdetails;

CREATE TABLE Payments (
payment_id CHAR(10) NOT NULL PRIMARY KEY,
customer_id CHAR(10) NOT NULL,
order_id CHAR(15) NOT NULL,
payment_date DATE NOT NULL,
payment_method VARCHAR(20) NOT NULL,
CONSTRAINT fk_payments_customers FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_payments_orders FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO Payments (payment_id, customer_id, order_id, payment_date, payment_method) VALUES
('4839201', 'CUSTX9Y86D', 'ORDX9Y86D4A', '2024-05-14', 'Credit Card'),
('5917328', 'CUSTK1L2M3', 'ORDK1L2M3Z9', '2024-06-21', 'Debit Card'),
('3758219', 'CUSTP5Q6S8', 'ORDP5Q6S8W7', '2024-07-08', 'Mobile Payment'),
('8294730', 'CUSTD9E7G6', 'ORDD9E7G6B3', '2024-08-19', 'Cash'),
('9283015', 'CUSTH5J7K8', 'ORDH5J7K8V1', '2024-09-30', 'Credit Card'),
('1748293', 'CUSTQ1R3T4', 'ORDQ1R3T4C7', '2025-01-17', 'Cash'),
('9385721', 'CUSTAB12C3', 'ORDAB12C3X4', '2025-02-05', 'Debit Card'),
('1028374', 'CUSTK1L2M3', 'ORDCUSTK1L5', '2025-03-10', 'Mobile Payment'),
('2059374', 'CUSTX9Y86D', 'ORDCUSTX9Y4', '2025-03-01', 'Credit Card'),
('5849372', 'CUSTB1C2E4', 'ORDB1C2E4M2', '2024-10-25', 'Credit Card'),
('6748291', 'CUSTB1C2E4', 'ORDB1C2E4M2', '2024-11-29', 'Credit Card'),
('4918265', 'CUSTW5X6Y8', 'ORDW5X6Y8K5', '2024-11-13', 'Debit Card'),
('5830274', 'CUSTW5X6Y8', 'ORDW5X6Y8K5', '2024-12-11', 'Debit Card'),
('6928410', 'CUSTW5X6Y8', 'ORDW5X6Y8K5', '2025-01-08', 'Debit Card');

select * from payments;

show tables;