PRAGMA foreign_keys = ON; 
DROP TABLE IF EXISTS sales; 
DROP TABLE IF EXISTS categories; 
CREATE TABLE categories ( 
category TEXT PRIMARY KEY NOT NULL 
); 
CREATE TABLE sales ( 
sale_id INTEGER PRIMARY KEY, 
category TEXT NOT NULL REFERENCES categories(category), 
product TEXT NOT NULL, 
qty INTEGER NOT NULL CHECK(qty > 0), 
unit_price REAL NOT NULL CHECK(unit_price >= 0), 
status TEXT NOT NULL CHECK(status IN ('Completed','Cancelled')) 
); 
INSERT INTO categories VALUES 
('Stationery'), ('Accessories'), ('Snacks'); 
INSERT INTO sales VALUES 
(1,'Stationery','Notebook',5,40,'Completed'), 
(2,'Stationery','Pen',10,10,'Completed'), 
(3,'Accessories','USB',2,300,'Completed'), 
(4,'Snacks','Biscuit',8,25,'Completed'), 
(5,'Stationery','Notebook',3,40,'Cancelled'), 
(6,'Accessories','Mouse',1,450,'Completed'), 
(7,'Snacks','Juice',6,30,'Completed'), 
(8,'Stationery','Pen',15,10,'Completed'), 
(9,'Accessories','USB',1,300,'Cancelled'), 
(10,'Snacks','Biscuit',4,25,'Completed'), 
(11,'Stationery','Folder',4,20,'Completed'), 
(12,'Accessories','Mouse',2,450,'Completed'), 
(13,'Stationery','Folder',5,20,'Completed'), 
(14,'Snacks','Juice',10,30,'Completed'), 
(15,'Accessories','USB',1,300,'Completed'); 

SELECT name FROM sqlite_master 
WHERE type = 'table' AND name IN ('categories','sales') 
ORDER BY name; 
SELECT COUNT(*) AS category_records FROM categories; 
SELECT COUNT(*) AS sales_records FROM sales; 

SELECT sale_id, product, qty
FROM sales
WHERE status = 'Completed' AND category = 'Stationery' AND qty >= 4
ORDER BY sale_id ASC;

SELECT sale_id, product, qty * unit_price AS revenue
FROM sales
WHERE status = 'Completed'
ORDER BY revenue DESC, sale_id ASC
LIMIT 3;

SELECT COUNT(*) AS line_count, SUM(qty) AS units,
SUM(qty * unit_price) AS revenue,
ROUND(AVG(qty * unit_price), 2) AS avg_line_revenue,
MIN(qty * unit_price) AS lowest_line_revenue,
MAX(qty * unit_price) AS highest_line_revenue
FROM sales 
WHERE status = 'Completed';