-- Aabha SQL course: synthetic data, PostgreSQL dialect, dedicated practice database only.
-- Setup writes new course tables. It deliberately does not DROP existing objects.
BEGIN;
CREATE SCHEMA aabha_sql;
CREATE TABLE aabha_sql.customers (
customer_id integer PRIMARY KEY, customer_name text NOT NULL,
city text, signup_date date NOT NULL
);
CREATE TABLE aabha_sql.products (
product_id integer PRIMARY KEY, product_name text NOT NULL,
category text NOT NULL, list_price numeric(10,2) NOT NULL CHECK(list_price>=0)
);
CREATE TABLE aabha_sql.orders (
order_id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES aabha_sql.customers,
order_date date NOT NULL,
status text NOT NULL CHECK(status IN ('completed','pending','cancelled')),
channel text NOT NULL CHECK(channel IN ('web','app'))
);
CREATE TABLE aabha_sql.order_items (
order_id integer REFERENCES aabha_sql.orders,
line_no integer,
product_id integer NOT NULL REFERENCES aabha_sql.products,
quantity integer NOT NULL CHECK(quantity>0),
unit_price numeric(10,2) NOT NULL CHECK(unit_price>=0),
line_discount numeric(10,2) NOT NULL CHECK(line_discount>=0),
PRIMARY KEY(order_id,line_no),
CHECK(line_discount<=quantity*unit_price)
);
CREATE TABLE aabha_sql.payments (
payment_id integer PRIMARY KEY, order_id integer NOT NULL REFERENCES aabha_sql.orders,
payment_date date NOT NULL, amount numeric(10,2) NOT NULL CHECK(amount>0),
status text NOT NULL CHECK(status IN ('succeeded','failed'))
);
CREATE TABLE aabha_sql.refunds (
refund_id integer PRIMARY KEY, order_id integer NOT NULL REFERENCES aabha_sql.orders,
refund_date date NOT NULL, amount numeric(10,2) NOT NULL CHECK(amount>0)
);
-- Raw intake deliberately allows defects; do not treat this as curated order data.
CREATE TABLE aabha_sql.raw_orders (
batch_row integer PRIMARY KEY, source_order_id integer,
customer_id integer, order_date_text text, status_text text,
amount numeric(10,2), loaded_at timestamp NOT NULL
);
INSERT INTO aabha_sql.customers VALUES
(1,'Asha','Pune','2024-12-10'),(2,'Ben','Bengaluru','2024-12-15'),
(3,'Chen','Mumbai','2025-01-01'),(4,'Divya','Pune','2025-01-15'),
(5,'Eren','Delhi','2025-02-01'),(6,'Farah',NULL,'2025-02-10'),
(7,'Gita','Delhi','2025-03-01'),(8,'Hari','Pune','2025-03-10');
INSERT INTO aabha_sql.products VALUES
(101,'Notebook','Stationery',120),(102,'Pen','Stationery',40),
(103,'Mug','Home',250),(104,'Bag','Travel',800),(105,'Guide','Books',500);
INSERT INTO aabha_sql.orders VALUES
(1001,1,'2025-01-05','completed','web'),(1002,2,'2025-01-10','completed','app'),
(1003,1,'2025-01-20','cancelled','web'),(1004,3,'2025-01-31','completed','web'),
(1005,4,'2025-02-01','completed','app'),(1006,2,'2025-02-14','completed','web'),
(1007,5,'2025-02-20','pending','web'),(1008,6,'2025-02-28','completed','web'),
(1009,3,'2025-03-01','completed','app'),(1010,1,'2025-03-10','completed','web'),
(1011,4,'2025-03-15','completed','web'),(1012,5,'2025-03-31','completed','app'),
(1013,8,'2025-03-20','cancelled','app'),(1014,2,'2025-03-31','completed','app');
INSERT INTO aabha_sql.order_items VALUES
(1001,1,101,2,120,0),(1001,2,102,2,40,0),(1002,1,104,1,800,0),
(1003,1,103,1,250,0),(1004,1,101,1,120,0),(1004,2,105,1,500,100),
(1005,1,103,2,250,50),(1006,1,104,1,800,80),(1006,2,102,3,40,0),
(1007,1,105,1,500,0),(1008,1,101,3,120,0),
(1009,1,103,1,250,0),(1009,2,105,1,500,0),
(1010,1,104,1,800,0),(1010,2,101,1,120,0),
(1011,1,102,5,40,0),(1012,1,105,1,500,50),
(1013,1,104,1,800,0),(1014,1,103,1,250,0);
INSERT INTO aabha_sql.payments VALUES
(1,1001,'2025-01-05',200,'succeeded'),(2,1001,'2025-01-05',120,'succeeded'),
(3,1002,'2025-01-10',800,'succeeded'),(4,1003,'2025-01-20',250,'failed'),
(5,1004,'2025-01-31',520,'succeeded'),(6,1005,'2025-02-01',450,'succeeded'),
(7,1006,'2025-02-14',500,'succeeded'),(8,1006,'2025-02-14',340,'succeeded'),
(9,1006,'2025-02-14',100,'failed'),(10,1007,'2025-02-20',500,'failed'),
(11,1008,'2025-02-28',360,'succeeded'),(12,1009,'2025-03-01',750,'succeeded'),
(13,1010,'2025-03-10',920,'succeeded'),(14,1011,'2025-03-15',200,'succeeded'),
(15,1012,'2025-03-31',450,'succeeded'),(16,1014,'2025-03-31',250,'succeeded');
INSERT INTO aabha_sql.refunds VALUES
(1,1001,'2025-01-25',20),(2,1005,'2025-02-10',50),
(3,1005,'2025-02-12',25),(4,1010,'2025-03-15',120);
INSERT INTO aabha_sql.raw_orders VALUES
(1,9001,1,'2025-03-01',' Completed ',320,'2025-04-01 09:00'),
(2,9001,1,'2025-03-01','COMPLETED',320,'2025-04-01 10:00'),
(3,9002,2,'2025-03-02','completed',-50,'2025-04-01 10:00'),
(4,9003,99,'2025-03-03','completed',100,'2025-04-01 10:00'),
(5,9004,3,'2025-02-30','completed',200,'2025-04-01 10:00'),
(6,9005,4,'2025-03-05','complete',150,'2025-04-01 10:00'),
(7,9006,NULL,'2025-03-06','pending',NULL,'2025-04-01 10:00'),
(8,9007,5,'2025-03-07','completed',450,'2025-04-01 10:00');
COMMIT;
