PBA

CREATE DATABASE business;

USE business;

-- STEP 1
CREATE TABLE IF NOT EXISTS customers(
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS products(
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(30),
    category VARCHAR(30),
    price DECIMAL(8,2),
    stock INT
);

CREATE TABLE IF NOT EXISTS orders(
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    order_date DATE,
    quantity INT,
-- STEP 2
    CONSTRAINT fk_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
    CONSTRAINT fk_products FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);

-- STEP 3
INSERT INTO customers (name, address)
VALUES
    ('credo', 'SSV Blk 45 lot 108, general trias'),
    ('khier', 'Heneral Uno'),
    ('santiago', 'Buenavista II'),
    ('barron', 'Tiera Grande homes'),
    ('azer','blk 19 Lot 32 KAIA homes');

INSERT INTO products (product_name, category, price, stock)
VALUES
    ('Milky white', 'Soap', 20.00, 60),
    ('PenPenSis', 'School Supply', 15.25, 13),
    ('Stand Fan', 'Home Appliance', 3000.00, 6),
    ('Boy Bawang', 'Snack', 12.99, 300),
    ('Piatos', 'Snack', 15.00, 200);

INSERT INTO orders (customer_id, product_id, order_date, quantity)
VALUES
    (1, 3, '2024-09-18', 2),
    (2, 2, '2024-04-19', 4),
    (3, 4, '2024-05-10', 40),
    (4, 1, '2024-01-11', 1),
    (5, 5, '2024-02-24', 2);

-- UPDATE STEP 4
UPDATE products
SET price = 35.00
WHERE product_id = 1;

UPDATE customers
SET address = 'Natania Homes PK2'
WHERE customer_id = 3;


-- STEP 5 DELETE
ALTER TABLE orders
DROP FOREIGN KEY fk_products;

DELETE FROM products WHERE product_id = 3;

ALTER TABLE orders
ADD CONSTRAINT fk_products FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE;

-- STEP 6 generate a report
SELECT
    customers.name,
    customers.address,
    products.product_name,
    products.category,
    products.price,
    products.stock,
    orders.order_date
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
JOIN products ON orders.product_id = products.product_id;


-- STEP 7 display the total sales revenue
SELECT SUM(products.price * orders.quantity) AS 'Total Revenue'
FROM orders
JOIN products ON orders.product_id = products.product_id;