-- TEAM NAME (GROUP 1)
-- MEMBERS:
-- Dizon, Harry Leonerr T.
-- Punzalan, Gian Kirby N.
-- Salazar, Frederick Jr. E.


SELECT '============================================';
SELECT '================ TEAM REPORTS ==============';
SELECT '============================================';

SELECT '--------------------------------------------';
SELECT '------Setup (needed for B1 and B2)----------';
SELECT '--------------------------------------------';
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 '--------------------------------------------';
SELECT '--------B1 Category summary-----------------';
SELECT '--------------------------------------------';
-- B1 roles: Member 1 (Salazar) writes SQL, Member 2 (Punzalan) runs and checks, Member 3 (Dizon) records output
SELECT category, COUNT(*) AS line_count,
SUM(qty) AS units, SUM(qty * unit_price) AS revenue
FROM sales WHERE status = 'Completed'
GROUP BY category ORDER BY category;
SELECT 'Total comparison: 2250 + 780 + 630 = 3660, which equals the A3 revenue of 3660.' AS b1_total_comparison;
-- B1 output: Accessories | 4 | 6  | 2250
--            Snacks      | 4 | 28 | 780
--            Stationery  | 5 | 39 | 630


-- Total check as a query (should return 3660):
SELECT SUM(revenue) AS b1_total
FROM (SELECT SUM(qty * unit_price) AS revenue
      FROM sales WHERE status = 'Completed'
      GROUP BY category);


SELECT '--------------------------------------------';
SELECT '----B2 Categories reaching the target-------';
SELECT '--------------------------------------------';
-- B2 roles: Member 2 (Punzalan) writes SQL, Member 3 (Dizon) checks, Member 1 (Salazar) records output
SELECT category, SUM(qty * unit_price) AS revenue,
ROUND(AVG(qty * unit_price), 2) AS avg_line_revenue
FROM sales WHERE status = 'Completed'
GROUP BY category HAVING SUM(qty * unit_price) >= 700
ORDER BY revenue DESC, category ASC;
SELECT 'WHERE filters individual rows before grouping (only Completed sales), while HAVING filters the grouped totals after aggregation (revenue of at least 700).' AS where_vs_having;
SELECT 'Accessories has the highest qualifying revenue at 2250 pesos.' AS highest_qualifying_category;
SELECT 'Accessories and Snacks reached the 700-peso target, while Stationery fell short at 630 pesos despite having the most lines. Accessories led with 2250 pesos because its few lines carry high unit prices.' AS interpretation;
-- B2 output: Accessories | 2250 | 562.5
--            Snacks      | 780  | 195.0


SELECT '--------------------------------------------';
SELECT '----------Team check and contribution-------';
SELECT '--------------------------------------------';
SELECT 'Team check: B1 category total (3660) equals A3 total (3660). Member 3 (Dizon) led the comparison; everyone explained one clause.' AS team_check;


SELECT '--------------------------------------------';
SELECT 'ID 1: Salazar, Frederick Jr. E.' AS member;
SELECT 'Wrote the B1 SQL, recorded the B2 output, and confirmed the B1 total of 3660 matches A3.' AS action;
SELECT 'GP, HD' AS confirmed_by;
SELECT 'GROUP BY splits the Completed sales into one row per category so COUNT and SUM work for each group.' AS clause_explanation;


SELECT '--------------------------------------------';
SELECT 'ID 2: Punzalan, Gian Kirby N.' AS member;
SELECT 'Ran and checked the B1 query, then wrote the B2 SQL with the HAVING threshold.' AS action;
SELECT 'FS, HD' AS confirmed_by;
SELECT 'HAVING keeps only the categories whose total revenue is at least 700 after the sums are computed.' AS clause_explanation;


SELECT '--------------------------------------------';
SELECT 'ID 3: Dizon, Harry Leonerr T.' AS member;
SELECT 'Recorded the B1 output, checked the B2 query, and led the final comparison of totals.' AS action;
SELECT 'FS, GP' AS confirmed_by;
SELECT 'WHERE status = ''Completed'' removes the cancelled lines before any grouping happens.' AS clause_explanation;


SELECT '--------------------------------------------';
SELECT 'Feedback: B1 and B2 results match the expected values; no errors found.' AS feedback;
SELECT 'Successful check: B1 category revenues (2250 + 780 + 630) sum to 3660, matching the A3 total.' AS successful_check;


SELECT '============================================';
SELECT '=== INDIVIDUAL: Salazar, Frederick Jr. E. ===';
SELECT '============================================';

SELECT '--------------------------------------------';
SELECT '------S Build the database and tables-------';
SELECT '--------------------------------------------';
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;
-- The primary key of categories is the category and the primary key of sales is the sale_id. The foreign key is sales.category referencing categories.category.
SELECT '--------------------------------------------';
SELECT '------A1 Completed Stationery records-------';
SELECT '--------------------------------------------';
SELECT sale_id, product, qty
FROM sales
WHERE status = 'Completed'
 AND category = 'Stationery' AND qty >= 4
ORDER BY sale_id;
SELECT '--------------------------------------------';
SELECT '--A2 Three highest revenue Completed lines--';
SELECT '--------------------------------------------';
SELECT sale_id, product, qty * unit_price AS revenue
FROM sales
WHERE status = 'Completed'
ORDER BY revenue DESC, sale_id ASC
LIMIT 3;
SELECT '--------------------------------------------';
SELECT '---------A3 Completed sales summary---------';
SELECT '--------------------------------------------';
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';
-- line_count counts each sale line once, while units adds up to the quantities, so units (73) is larger than line_count (13).
SELECT '--------------------------------------------';
SELECT '--------Individual contribution-------------';
SELECT '--------------------------------------------';
SELECT 'Wrote the B1 SQL, recorded the B2 output, and confirmed the B1 total of 3660 matches A3.' AS action;
SELECT 'GP, HD' AS confirmed_by;
SELECT 'GROUP BY splits the Completed sales into one row per category so COUNT and SUM work for each group.' AS clause_explanation;


SELECT '============================================';
SELECT '=== INDIVIDUAL: Punzalan, Gian Kirby N. ====';
SELECT '============================================';

SELECT '--------------------------------------------';
SELECT '------S Build the database and tables-------';
SELECT '--------------------------------------------';
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;
-- Primary keys are category (categories table) and sale_id (sales table), linked by sales.category as a foreign key.

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

SELECT '--------------------------------------------';
SELECT '--A2 Three highest revenue Completed lines--';
SELECT '--------------------------------------------';
SELECT sale_id, product, (qty * unit_price) AS revenue
FROM sales
WHERE status = 'Completed'
ORDER BY revenue DESC, sale_id ASC
LIMIT 3;

SELECT '--------------------------------------------';
SELECT '---------A3 Completed sales summary---------';
SELECT '--------------------------------------------';
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';
-- Line count is the total number of sales rows, while units is the total count of items sold.

SELECT '--------------------------------------------';
SELECT '--------Individual contribution-------------';
SELECT '--------------------------------------------';
SELECT 'Ran and checked the B1 query, then wrote the B2 SQL with the HAVING threshold.' AS action;
SELECT 'FS, HD' AS confirmed_by;
SELECT 'HAVING keeps only the categories whose total revenue is at least 700 after the sums are computed.' AS clause_explanation;


SELECT '============================================';
SELECT '=== INDIVIDUAL: Dizon, Harry Leonerr T. ====';
SELECT '============================================';

SELECT '--------------------------------------------';
SELECT '------S Build the database and tables-------';
SELECT '--------------------------------------------';
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;
-- The primary key of categories is category and the primary key of sales is sale_id, and sales.category is a foreign key referencing categories.category.

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

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

SELECT '--------------------------------------------';
SELECT '---------A3 Completed sales summary---------';
SELECT '--------------------------------------------';
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';
-- line_count counts each sale line once, while units adds up the quantities on those lines, so units (73) is larger than line_count (13).

SELECT '--------------------------------------------';
SELECT '--------Individual contribution-------------';
SELECT '--------------------------------------------';
SELECT 'Recorded the B1 output, checked the B2 query, and led the final comparison of totals.' AS action;
SELECT 'FS, GP' AS confirmed_by;
SELECT 'WHERE status = ''Completed'' removes the cancelled lines before any grouping happens.' AS clause_explanation;