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 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 qty * unit_price 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';

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';

select * from categories ;
select * from sales ;