Skip to content

Instantly share code, notes, and snippets.

@ralfbecher

ralfbecher/orionbelt_1_DML_pg.sql

Created September 8, 2025 10:08
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ralfbecher/6b4660a6001445219c806205725ed25a to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ralfbecher/6b4660a6001445219c806205725ed25a to your computer and use it in GitHub Desktop.
Download ZIP
Load Sample Data
Raw
orionbelt_1_DML_pg.sql
SET schema 'orionbelt_1';
-- Clear existing data (if any)
TRUNCATE TABLE clientcomplaints CASCADE;
TRUNCATE TABLE acctbal CASCADE;
TRUNCATE TABLE returns CASCADE;
TRUNCATE TABLE shipments CASCADE;
TRUNCATE TABLE purchases CASCADE;
TRUNCATE TABLE sales CASCADE;
TRUNCATE TABLE calendar CASCADE;
TRUNCATE TABLE clients CASCADE;
TRUNCATE TABLE products CASCADE;
TRUNCATE TABLE employees CASCADE;
TRUNCATE TABLE suppliers CASCADE;
TRUNCATE TABLE channels CASCADE;
TRUNCATE TABLE countries CASCADE;
TRUNCATE TABLE regions CASCADE;
TRUNCATE TABLE banks CASCADE;
----------------------------------------------------------------------------------------------------
-- Insert Regions
----------------------------------------------------------------------------------------------------
INSERT INTO regions (regionid, regionname) VALUES
('R001', 'North America'),
('R002', 'Europe'),
('R003', 'Asia Pacific'),
('R004', 'Latin America'),
('R005', 'Middle East & Africa');
----------------------------------------------------------------------------------------------------
-- Insert Countries
----------------------------------------------------------------------------------------------------
INSERT INTO countries (countryid, countryname, region) VALUES
('US', 'United States', 'R001'),
('CA', 'Canada', 'R001'),
('UK', 'United Kingdom', 'R002'),
('DE', 'Germany', 'R002'),
('FR', 'France', 'R002'),
('JP', 'Japan', 'R003'),
('AU', 'Australia', 'R003'),
('CN', 'China', 'R003'),
('BR', 'Brazil', 'R004'),
('MX', 'Mexico', 'R004'),
('AE', 'United Arab Emirates', 'R005'),
('ZA', 'South Africa', 'R005');
----------------------------------------------------------------------------------------------------
-- Insert Banks
----------------------------------------------------------------------------------------------------
INSERT INTO banks (bankid, bankname) VALUES
('BNK001', 'First National Bank'),
('BNK002', 'Global Trust Bank'),
('BNK003', 'International Commerce Bank'),
('BNK004', 'Metropolitan Bank'),
('BNK005', 'United Financial Services');
----------------------------------------------------------------------------------------------------
-- Insert Employees
----------------------------------------------------------------------------------------------------
INSERT INTO employees (employeeid, employeename, department) VALUES
('EMP001', 'John Smith', 'Sales'),
('EMP002', 'Sarah Johnson', 'Sales'),
('EMP003', 'Michael Brown', 'Sales'),
('EMP004', 'Emily Davis', 'Purchasing'),
('EMP005', 'Robert Wilson', 'Purchasing'),
('EMP006', 'Lisa Anderson', 'Shipping'),
('EMP007', 'David Miller', 'Shipping'),
('EMP008', 'Jennifer Garcia', 'Customer Service'),
('EMP009', 'William Taylor', 'Sales'),
('EMP010', 'Jessica Martinez', 'Purchasing');
----------------------------------------------------------------------------------------------------
-- Insert Suppliers
----------------------------------------------------------------------------------------------------
INSERT INTO suppliers (supplierid, suppliername, suppliercountryid) VALUES
('SUP001', 'Tech Components Inc', 'US'),
('SUP002', 'Global Electronics Ltd', 'CN'),
('SUP003', 'European Parts GmbH', 'DE'),
('SUP004', 'Pacific Supplies Co', 'JP'),
('SUP005', 'American Manufacturing', 'US'),
('SUP006', 'Asia Trading Company', 'CN'),
('SUP007', 'UK Industrial Solutions', 'UK'),
('SUP008', 'Canadian Components Corp', 'CA');
----------------------------------------------------------------------------------------------------
-- Insert Channels
----------------------------------------------------------------------------------------------------
INSERT INTO channels (channelid, channelname) VALUES
('CH001', 'Online Store'),
('CH002', 'Retail Store'),
('CH003', 'Phone Sales'),
('CH004', 'Partner Channel'),
('CH005', 'Direct Sales'),
('CH006', 'Mobile App'),
('CH007', 'Wholesale');
----------------------------------------------------------------------------------------------------
-- Insert Products
----------------------------------------------------------------------------------------------------
INSERT INTO products (productid, productname, productcat, productsuppl, unitcost, unitprice, curr, unitsinstock) VALUES
('PRD001', 'Wireless Headphones', 'Electronics', 'SUP001', 45.00, 89.99, 'USD', 150),
('PRD002', 'Smartphone Case', 'Accessories', 'SUP002', 8.50, 24.99, 'USD', 300),
('PRD003', 'Bluetooth Speaker', 'Electronics', 'SUP003', 32.00, 79.99, 'USD', 75),
('PRD004', 'USB Cable', 'Accessories', 'SUP002', 3.20, 12.99, 'USD', 500),
('PRD005', 'Tablet Stand', 'Accessories', 'SUP004', 15.00, 39.99, 'USD', 120),
('PRD006', 'Power Bank', 'Electronics', 'SUP001', 25.50, 59.99, 'USD', 200),
('PRD007', 'Wireless Mouse', 'Electronics', 'SUP005', 18.75, 44.99, 'USD', 180),
('PRD008', 'Keyboard', 'Electronics', 'SUP003', 42.00, 99.99, 'USD', 90),
('PRD009', 'Monitor Stand', 'Accessories', 'SUP006', 28.00, 69.99, 'USD', 60),
('PRD010', 'Webcam', 'Electronics', 'SUP007', 55.00, 129.99, 'USD', 45);
----------------------------------------------------------------------------------------------------
-- Insert Clients
----------------------------------------------------------------------------------------------------
INSERT INTO clients (clientid, clientname, clientgender, clientemail, clientcountryid) VALUES
('CLI001', 'Alice Cooper', 'F', '[email protected]', 'US'),
('CLI002', 'Bob Johnson', 'M', '[email protected]', 'CA'),
('CLI003', 'Carol Smith', 'F', '[email protected]', 'UK'),
('CLI004', 'David Wilson', 'M', '[email protected]', 'DE'),
('CLI005', 'Emma Brown', 'F', '[email protected]', 'FR'),
('CLI006', 'Frank Davis', 'M', '[email protected]', 'JP'),
('CLI007', 'Grace Miller', 'F', '[email protected]', 'AU'),
('CLI008', 'Henry Garcia', 'M', '[email protected]', 'BR'),
('CLI009', 'Irene Taylor', 'F', '[email protected]', 'MX'),
('CLI010', 'Jack Anderson', 'M', '[email protected]', 'US'),
('CLI011', 'Karen Martinez', 'F', '[email protected]', 'CA'),
('CLI012', 'Louis Thompson', 'M', '[email protected]', 'UK'),
('CLI013', 'Maria Rodriguez', 'F', '[email protected]', 'AE'),
('CLI014', 'Nathan Lee', 'M', '[email protected]', 'ZA'),
('CLI015', 'Olivia White', 'F', '[email protected]', 'CN');
----------------------------------------------------------------------------------------------------
-- Insert Calendar (All dates used in transactions for 2024)
----------------------------------------------------------------------------------------------------
INSERT INTO calendar (date, ym, weekday, publicholiday) VALUES
-- January dates
('2024-01-01', '2024-01', 'Monday', 'Y'),
('2024-01-02', '2024-01', 'Tuesday', 'N'),
('2024-01-03', '2024-01', 'Wednesday', 'N'),
('2024-01-15', '2024-01', 'Monday', 'Y'),
-- February dates
('2024-02-14', '2024-02', 'Wednesday', 'N'),
('2024-02-15', '2024-02', 'Thursday', 'N'),
('2024-02-28', '2024-02', 'Wednesday', 'N'),
-- March dates
('2024-03-15', '2024-03', 'Friday', 'N'),
-- April dates
('2024-04-10', '2024-04', 'Wednesday', 'N'),
('2024-04-20', '2024-04', 'Saturday', 'N'),
-- May dates
('2024-05-20', '2024-05', 'Monday', 'N'),
-- June dates
('2024-06-15', '2024-06', 'Saturday', 'N'),
-- July dates
('2024-07-04', '2024-07', 'Thursday', 'Y'),
('2024-07-14', '2024-07', 'Sunday', 'N'),
-- August dates
('2024-08-12', '2024-08', 'Monday', 'N'),
-- September dates
('2024-09-02', '2024-09', 'Monday', 'Y'),
-- October dates
('2024-10-31', '2024-10', 'Thursday', 'N'),
-- November dates
('2024-11-28', '2024-11', 'Thursday', 'Y'),
-- December dates
('2024-12-01', '2024-12', 'Sunday', 'N'),
('2024-12-25', '2024-12', 'Wednesday', 'Y');
----------------------------------------------------------------------------------------------------
-- Insert Sales (Enhanced with multiple purchases per client, higher quantities, and repeat orders)
----------------------------------------------------------------------------------------------------
INSERT INTO sales (salesid, salesdate, salesclient, product, salesempid, salespaymenttype, saleschanid, salesquantity, salesamount, notes) VALUES
-- CLI001 - Multiple purchases including repeat orders of same products
('SAL001', '2024-01-02', 'CLI001', 'PRD001', 'EMP001', 'Credit Card', 'CH001', 5, 449.95, 'Bulk order - office setup'),
('SAL002', '2024-02-14', 'CLI001', 'PRD006', 'EMP002', 'Credit Card', 'CH001', 3, 179.97, 'Additional power banks'),
('SAL003', '2024-03-15', 'CLI001', 'PRD007', 'EMP001', 'Credit Card', 'CH001', 2, 89.98, 'Wireless mice for team'),
('SAL024', '2024-06-15', 'CLI001', 'PRD001', 'EMP003', 'Credit Card', 'CH001', 3, 269.97, 'Reorder headphones - new employees'),
('SAL025', '2024-09-02', 'CLI001', 'PRD006', 'EMP001', 'Credit Card', 'CH006', 5, 299.95, 'More power banks - expansion'),
-- CLI002 - Multiple purchases including repeat USB cable orders
('SAL004', '2024-01-03', 'CLI002', 'PRD002', 'EMP002', 'PayPal', 'CH001', 10, 249.90, 'Smartphone cases bulk'),
('SAL005', '2024-04-10', 'CLI002', 'PRD004', 'EMP003', 'PayPal', 'CH006', 15, 194.85, 'USB cables for resale'),
('SAL026', '2024-07-04', 'CLI002', 'PRD004', 'EMP002', 'PayPal', 'CH001', 20, 259.80, 'Reorder USB cables - high demand'),
('SAL027', '2024-10-31', 'CLI002', 'PRD002', 'EMP001', 'PayPal', 'CH001', 8, 199.92, 'Reorder cases - Halloween special'),
-- CLI003 - Multiple purchases including repeat speaker orders
('SAL006', '2024-02-14', 'CLI003', 'PRD003', 'EMP003', 'Cash', 'CH002', 2, 159.98, 'Valentine gifts'),
('SAL007', '2024-05-20', 'CLI003', 'PRD005', 'EMP009', 'Cash', 'CH002', 4, 159.96, 'Tablet stands for family'),
('SAL028', '2024-08-12', 'CLI003', 'PRD003', 'EMP003', 'Cash', 'CH002', 3, 239.97, 'Reorder speakers - birthday gifts'),
-- CLI004 - Multiple keyboard orders (business client)
('SAL008', '2024-03-15', 'CLI004', 'PRD008', 'EMP001', 'Credit Card', 'CH003', 8, 799.92, 'Corporate keyboard order'),
('SAL029', '2024-08-12', 'CLI004', 'PRD008', 'EMP009', 'Credit Card', 'CH005', 6, 599.94, 'Additional keyboards - new department'),
-- CLI005 - Multiple purchases including repeat webcam orders
('SAL009', '2024-04-10', 'CLI005', 'PRD009', 'EMP009', 'Bank Transfer', 'CH005', 6, 419.94, 'Monitor stands for office'),
('SAL010', '2024-06-15', 'CLI005', 'PRD010', 'EMP002', 'Bank Transfer', 'CH005', 3, 389.97, 'Webcams for remote work'),
('SAL030', '2024-11-28', 'CLI005', 'PRD010', 'EMP002', 'Bank Transfer', 'CH005', 4, 519.96, 'More webcams - team expansion'),
-- CLI006 - Multiple power bank orders
('SAL011', '2024-05-20', 'CLI006', 'PRD006', 'EMP002', 'Credit Card', 'CH001', 12, 719.88, 'Power banks for event'),
('SAL031', '2024-09-02', 'CLI006', 'PRD006', 'EMP003', 'Credit Card', 'CH001', 8, 479.92, 'Reorder power banks - next event'),
-- CLI007 - Multiple purchases including repeat mouse orders
('SAL012', '2024-06-15', 'CLI007', 'PRD007', 'EMP003', 'PayPal', 'CH006', 7, 314.93, 'Mice for gaming setup'),
('SAL013', '2024-08-12', 'CLI007', 'PRD001', 'EMP001', 'PayPal', 'CH001', 4, 359.96, 'Headphones for family'),
('SAL032', '2024-12-01', 'CLI007', 'PRD007', 'EMP003', 'PayPal', 'CH001', 5, 224.95, 'More gaming mice - Christmas gifts'),
-- CLI008 - Multiple keyboard orders
('SAL014', '2024-07-04', 'CLI008', 'PRD008', 'EMP001', 'Credit Card', 'CH002', 3, 299.97, 'Holiday sale keyboards'),
('SAL033', '2024-12-25', 'CLI008', 'PRD008', 'EMP009', 'Credit Card', 'CH002', 2, 199.98, 'Christmas keyboard gifts'),
-- CLI009 - Single purchase
('SAL015', '2024-08-12', 'CLI009', 'PRD009', 'EMP009', 'Cash', 'CH002', 2, 139.98, 'Store pickup stands'),
-- CLI010 - Multiple purchases including repeat webcam orders
('SAL016', '2024-09-02', 'CLI010', 'PRD010', 'EMP002', 'Credit Card', 'CH004', 5, 649.95, 'Partner channel webcams'),
('SAL017', '2024-10-31', 'CLI010', 'PRD003', 'EMP003', 'Credit Card', 'CH001', 3, 239.97, 'Halloween speakers'),
('SAL034', '2024-12-25', 'CLI010', 'PRD010', 'EMP002', 'Credit Card', 'CH004', 3, 389.97, 'Reorder webcams - holiday demand'),
-- CLI011 - Multiple headphone orders
('SAL018', '2024-10-31', 'CLI011', 'PRD001', 'EMP003', 'PayPal', 'CH001', 6, 539.94, 'Halloween bulk headphones'),
('SAL035', '2024-12-01', 'CLI011', 'PRD001', 'EMP001', 'PayPal', 'CH001', 4, 359.96, 'Reorder headphones - gifts'),
-- CLI012 - Multiple purchases including repeat orders
('SAL019', '2024-11-28', 'CLI012', 'PRD002', 'EMP001', 'Credit Card', 'CH001', 20, 499.80, 'Black Friday cases'),
('SAL020', '2024-12-01', 'CLI012', 'PRD004', 'EMP002', 'Credit Card', 'CH001', 25, 324.75, 'Black Friday cables'),
('SAL036', '2024-12-25', 'CLI012', 'PRD002', 'EMP001', 'Credit Card', 'CH001', 15, 374.85, 'Christmas cases reorder'),
-- CLI013 - Multiple speaker orders
('SAL021', '2024-12-25', 'CLI013', 'PRD003', 'EMP009', 'Bank Transfer', 'CH005', 4, 319.96, 'Christmas gifts'),
('SAL037', '2024-01-15', 'CLI013', 'PRD003', 'EMP001', 'Bank Transfer', 'CH005', 2, 159.98, 'New Year speakers'),
-- CLI014 - Multiple tablet stand orders
('SAL022', '2024-07-04', 'CLI014', 'PRD005', 'EMP001', 'Credit Card', 'CH002', 8, 319.92, 'Office equipment'),
('SAL038', '2024-11-28', 'CLI014', 'PRD005', 'EMP009', 'Credit Card', 'CH002', 6, 239.94, 'Additional stands - new hires'),
-- CLI015 - Multiple power bank orders
('SAL023', '2024-09-02', 'CLI015', 'PRD006', 'EMP002', 'PayPal', 'CH001', 10, 599.90, 'Power bank reseller'),
('SAL039', '2024-12-01', 'CLI015', 'PRD006', 'EMP003', 'PayPal', 'CH001', 12, 719.88, 'Reorder power banks - holiday sales');
----------------------------------------------------------------------------------------------------
-- Insert Purchases
----------------------------------------------------------------------------------------------------
INSERT INTO purchases (purchaseid, purchasedate, purchaseproduct, purchaseempid, purchasesupplier, purchasechanid, purchasequantity, purchaseprice, purchaseamount, notes) VALUES
('PUR001', '2024-01-02', 'PRD001', 'EMP004', 'SUP001', 'CH005', 100, 45.00, 4500.00, 'Bulk order'),
('PUR002', '2024-01-15', 'PRD002', 'EMP005', 'SUP002', 'CH007', 200, 8.50, 1700.00, 'Wholesale purchase'),
('PUR003', '2024-02-14', 'PRD003', 'EMP004', 'SUP003', 'CH005', 50, 32.00, 1600.00, 'Regular restock'),
('PUR004', '2024-03-15', 'PRD004', 'EMP010', 'SUP002', 'CH007', 300, 3.20, 960.00, 'High volume'),
('PUR005', '2024-04-10', 'PRD005', 'EMP005', 'SUP004', 'CH005', 80, 15.00, 1200.00, 'Standard order'),
('PUR006', '2024-05-20', 'PRD006', 'EMP004', 'SUP001', 'CH005', 150, 25.50, 3825.00, 'Popular item restock'),
('PUR007', '2024-06-15', 'PRD007', 'EMP010', 'SUP005', 'CH007', 120, 18.75, 2250.00, 'Summer inventory'),
('PUR008', '2024-07-04', 'PRD008', 'EMP005', 'SUP003', 'CH005', 60, 42.00, 2520.00, 'Mid-year restock'),
('PUR009', '2024-08-12', 'PRD009', 'EMP004', 'SUP006', 'CH007', 40, 28.00, 1120.00, 'Low inventory alert'),
('PUR010', '2024-09-02', 'PRD010', 'EMP010', 'SUP007', 'CH005', 30, 55.00, 1650.00, 'Premium products');
----------------------------------------------------------------------------------------------------
-- Insert Shipments (Realistic partial shipments - some orders never fully shipped)
----------------------------------------------------------------------------------------------------
INSERT INTO shipments (shipmentid, shipmentsalesid, shipmentdate, shipmentclient, shipmentproduct, shipmentempid, shipmenttype, shipmentquantity, shipmentamount, notes) VALUES
-- Full shipments
('SHP001', 'SAL001', '2024-01-03', 'CLI001', 'PRD001', 'EMP006', 'Standard', 5, 449.95, 'Complete order delivered'),
('SHP002', 'SAL004', '2024-01-03', 'CLI002', 'PRD002', 'EMP007', 'Express', 10, 249.90, 'Same day delivery - all items'),
('SHP003', 'SAL006', '2024-02-15', 'CLI003', 'PRD003', 'EMP006', 'Standard', 2, 159.98, 'Valentine gifts - complete'),
-- Partial shipments for SAL005 (15 USB cables ordered, only 12 shipped)
('SHP004', 'SAL005', '2024-04-10', 'CLI002', 'PRD004', 'EMP007', 'Standard', 10, 129.90, 'Partial shipment 1 - 10 cables'),
('SHP005', 'SAL005', '2024-04-20', 'CLI002', 'PRD004', 'EMP006', 'Standard', 2, 25.98, 'Partial shipment 2 - 2 cables (3 still pending)'),
-- Partial shipments for SAL008 (8 keyboards ordered, only 6 shipped)
('SHP006', 'SAL008', '2024-03-15', 'CLI004', 'PRD008', 'EMP007', 'Express', 4, 399.96, 'Partial shipment - 4 keyboards available'),
('SHP007', 'SAL008', '2024-04-10', 'CLI004', 'PRD008', 'EMP006', 'Standard', 2, 199.98, 'Partial shipment - 2 more keyboards (2 still backordered)'),
-- Full shipments
('SHP008', 'SAL002', '2024-02-14', 'CLI001', 'PRD006', 'EMP007', 'Express', 3, 179.97, 'Power banks delivered'),
('SHP009', 'SAL003', '2024-03-15', 'CLI001', 'PRD007', 'EMP006', 'Standard', 2, 89.98, 'Mice delivered'),
('SHP010', 'SAL007', '2024-05-20', 'CLI003', 'PRD005', 'EMP007', 'Standard', 4, 159.96, 'Tablet stands delivered'),
-- Partial shipments for SAL009 (6 monitor stands ordered, only 5 shipped)
('SHP011', 'SAL009', '2024-04-10', 'CLI005', 'PRD009', 'EMP006', 'Express', 4, 279.96, 'Partial shipment - 4 stands shipped'),
('SHP012', 'SAL009', '2024-05-20', 'CLI005', 'PRD009', 'EMP007', 'Standard', 1, 69.99, 'Partial shipment - 1 more stand (1 still pending)'),
-- Partial shipment for SAL011 (12 power banks ordered, only 9 shipped)
('SHP013', 'SAL011', '2024-05-20', 'CLI006', 'PRD006', 'EMP006', 'Standard', 6, 359.94, 'Partial shipment 1 - 6 power banks'),
('SHP014', 'SAL011', '2024-06-15', 'CLI006', 'PRD006', 'EMP007', 'Standard', 3, 179.97, 'Partial shipment 2 - 3 more (3 still unshipped)'),
-- Partial shipment for SAL010 (3 webcams ordered, only 2 shipped)
('SHP015', 'SAL010', '2024-06-15', 'CLI005', 'PRD010', 'EMP006', 'Express', 2, 259.98, 'Partial shipment - 2 webcams (1 still pending)'),
-- Full shipments
('SHP016', 'SAL012', '2024-06-15', 'CLI007', 'PRD007', 'EMP007', 'Standard', 7, 314.93, 'Gaming mice delivered'),
('SHP017', 'SAL014', '2024-07-04', 'CLI008', 'PRD008', 'EMP006', 'Standard', 3, 299.97, 'Holiday keyboards delivered'),
('SHP018', 'SAL015', '2024-08-12', 'CLI009', 'PRD009', 'EMP006', 'Pickup', 2, 139.98, 'Store pickup completed'),
-- Partial shipment for SAL013 (4 headphones ordered, only 3 shipped)
('SHP019', 'SAL013', '2024-08-12', 'CLI007', 'PRD001', 'EMP007', 'Standard', 3, 269.97, 'Partial shipment - 3 headphones (1 still backordered)'),
-- No shipment yet for SAL016 (5 webcams ordered - supply chain issues)
-- Full shipment
('SHP020', 'SAL017', '2024-10-31', 'CLI010', 'PRD003', 'EMP007', 'Standard', 3, 239.97, 'Halloween speakers delivered'),
-- Partial shipment for SAL018 (6 headphones ordered, only 4 shipped)
('SHP021', 'SAL018', '2024-10-31', 'CLI011', 'PRD001', 'EMP006', 'Express', 4, 359.96, 'Partial shipment - 4 headphones (2 still pending)'),
-- Partial shipments for SAL019 (20 cases ordered, only 17 shipped)
('SHP022', 'SAL019', '2024-11-28', 'CLI012', 'PRD002', 'EMP007', 'Express', 12, 299.88, 'Black Friday partial - 12 cases'),
('SHP023', 'SAL019', '2024-12-01', 'CLI012', 'PRD002', 'EMP006', 'Standard', 5, 124.95, 'Partial shipment - 5 more cases (3 still unshipped)'),
-- Full shipment
('SHP024', 'SAL020', '2024-12-01', 'CLI012', 'PRD004', 'EMP007', 'Standard', 25, 324.75, 'All cables shipped'),
-- Partial shipment for SAL021 (4 speakers ordered, only 3 shipped)
('SHP025', 'SAL021', '2024-12-25', 'CLI013', 'PRD003', 'EMP006', 'Express', 3, 239.97, 'Christmas gifts - 3 speakers (1 delayed)'),
-- Partial shipment for SAL022 (8 tablet stands ordered, only 6 shipped)
('SHP026', 'SAL022', '2024-07-04', 'CLI014', 'PRD005', 'EMP007', 'Standard', 6, 239.94, 'Office equipment - 6 stands (2 still pending)'),
-- Partial shipment for SAL023 (10 power banks ordered, only 8 shipped)
('SHP027', 'SAL023', '2024-09-02', 'CLI015', 'PRD006', 'EMP006', 'Standard', 5, 299.95, 'Partial shipment - 5 power banks'),
('SHP028', 'SAL023', '2024-10-31', 'CLI015', 'PRD006', 'EMP007', 'Standard', 3, 179.97, 'Partial shipment - 3 more (2 still unshipped)');
----------------------------------------------------------------------------------------------------
-- Insert Returns (Updated for new sales)
----------------------------------------------------------------------------------------------------
INSERT INTO returns (returnid, returnsalesid, returndate, returnempid, returnquantity, returnamount, notes) VALUES
('RET001', 'SAL001', '2024-01-15', 'EMP008', 2, 179.98, 'Defective headphones - 2 units'),
('RET002', 'SAL006', '2024-02-28', 'EMP008', 1, 79.99, 'Customer changed mind - 1 speaker'),
('RET003', 'SAL007', '2024-06-15', 'EMP008', 2, 79.98, 'Wrong color tablet stands'),
('RET004', 'SAL014', '2024-07-14', 'EMP008', 1, 99.99, 'Keyboard not as described'),
('RET005', 'SAL019', '2024-12-01', 'EMP008', 3, 74.97, 'Damaged cases from Black Friday order'),
('RET006', 'SAL018', '2024-11-28', 'EMP008', 1, 89.99, 'Duplicate headphone order'),
('RET007', 'SAL012', '2024-07-04', 'EMP008', 2, 89.98, 'Gaming mice - wrong model');
----------------------------------------------------------------------------------------------------
-- Insert Account Balances
----------------------------------------------------------------------------------------------------
INSERT INTO acctbal (accountid, iban, bankid, balanceamt) VALUES
('ACC001', 'US12BANK00000001234567', 'BNK001', 15000.50),
('ACC002', 'CA34BANK00000009876543', 'BNK002', 8750.25),
('ACC003', 'UK56BANK00000005555555', 'BNK003', 22000.00),
('ACC004', 'DE78BANK00000001111111', 'BNK001', 12500.75),
('ACC005', 'FR90BANK00000002222222', 'BNK004', 18900.30),
('ACC006', 'JP12BANK00000003333333', 'BNK005', 9200.85),
('ACC007', 'AU34BANK00000004444444', 'BNK002', 16750.40),
('ACC008', 'BR56BANK00000006666666', 'BNK003', 7300.15);
----------------------------------------------------------------------------------------------------
-- Insert Client Complaints
----------------------------------------------------------------------------------------------------
INSERT INTO clientcomplaints (complid, complclientid, compltext) VALUES
('COMP001', 'CLI001', 'Product arrived damaged, requesting replacement'),
('COMP002', 'CLI003', 'Delivery was delayed by 3 days without notification'),
('COMP003', 'CLI005', 'Received wrong product, ordered tablet stand but got mouse'),
('COMP004', 'CLI008', 'Poor customer service experience during phone call'),
('COMP005', 'CLI012', 'Website checkout process is confusing and slow'),
('COMP006', 'CLI013', 'Product quality does not match the description online');
----------------------------------------------------------------------------------------------------
-- Summary Report
----------------------------------------------------------------------------------------------------
-- Verification queries to check data integrity
SELECT 'Regions' as table_name, COUNT(*) as record_count FROM regions
UNION ALL
SELECT 'Countries', COUNT(*) FROM countries
UNION ALL
SELECT 'Banks', COUNT(*) FROM banks
UNION ALL
SELECT 'Employees', COUNT(*) FROM employees
UNION ALL
SELECT 'Suppliers', COUNT(*) FROM suppliers
UNION ALL
SELECT 'Channels', COUNT(*) FROM channels
UNION ALL
SELECT 'Products', COUNT(*) FROM products
UNION ALL
SELECT 'Clients', COUNT(*) FROM clients
UNION ALL
SELECT 'Calendar', COUNT(*) FROM calendar
UNION ALL
SELECT 'Sales', COUNT(*) FROM sales
UNION ALL
SELECT 'Purchases', COUNT(*) FROM purchases
UNION ALL
SELECT 'Shipments', COUNT(*) FROM shipments
UNION ALL
SELECT 'Returns', COUNT(*) FROM returns
UNION ALL
SELECT 'Account Balances', COUNT(*) FROM acctbal
UNION ALL
SELECT 'Client Complaints', COUNT(*) FROM clientcomplaints;
-- Additional verification: Check for repeat orders
SELECT
'Clients with repeat product orders' as analysis,
COUNT(DISTINCT salesclient) as client_count
FROM (
SELECT salesclient, product, COUNT(*) as order_count
FROM sales
GROUP BY salesclient, product
HAVING COUNT(*) > 1
) repeat_orders;
-- END OF TEST DATA SCRIPT
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment