DayThree.sql

-- Day3
USE BOOTCAMP_2408;

CREATE TABLE CUSTOMERS (
	ID INTEGER PRIMARY KEY, -- UNQIUE, NOT NULL
	CUSTOMER_NAME VARCHAR(50),
    VIP VARCHAR(1)
);

CREATE TABLE ORDERS (
	ID INTEGER PRIMARY KEY, -- UNQIUE, NOT NULL
	TOTAL_AMOUNT DECIMAL(11,2),
    CUSTOMER_ID INTEGER,
    -- CUSTOMER_ID -> ORDERS.CUSTOMER_ID (CURRENT TABLE COLUMN)
    -- ID -> CUSTOMERS.ID (ANOTHER TABLE PK)
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID)
);

INSERT INTO CUSTOMERS VALUES (1, 'JOHN LAU', 'Y');
-- INSERT INTO CUSTOMERS VALUES (1, 'PETER WONG', 'N'); -- PK EXISTS
INSERT INTO CUSTOMERS VALUES (2, 'PETER WONG', 'N');

INSERT INTO ORDERS VALUES (1, 100.9, 2); -- OK, CUSTOMER_ID 2 EXISTS
-- INSERT INTO ORDERS VALUES (2, 999.99, 3); -- NOT OK, CUSTOMER_ID 3 NOT EXISTS IN CUSTOMERS
INSERT INTO ORDERS VALUES (2, 999.99, 1);

-- TYPES OF TABLE
-- 1. SETUP TABLE (ADMIN SYSTEM CONTROL)
-- 2. TRANSACTION TABLE (USER ACTION)
-- 3. CUSTOMER BASED TABLE (USER ACTION)

-- SETUP TABLE (ADMIN SYSTEM CONTROL)
CREATE TABLE SETUP_DELIVERY_FEE(
	ORDER_TYPE VARCHAR(1),
    EFF_DATE DATE,
	TRAN_DATE DATE,
    AMOUNT DECIMAL(6,2),
    STATUS VARCHAR(1), -- 'A', 'I'
    PRIMARY KEY (ORDER_TYPE, EFF_DATE)
);

-- JAVA
-- CTE: SUB-QUERY
WITH MAX_EFF_DATE AS (
	SELECT MAX(EFF_DATE) AS MAX_DATE -- RESULT -> ONE ROW
	FROM SETUP_DELIVERY_FEE SDF
	WHERE SDF.ORDER_TYPE = 'C'
    AND SDF.EFF_DATE <= CURDATE() -- TODAY
    AND S.STATUS = 'A'
)
SELECT S.AMOUNT AS DELIVERY_FEE
FROM SETUP_DELIVERY_FEE S, MAX_EFF_DATE MED -- N X 1
WHERE S.ORDER_TYPE = 'C'
AND S.STATUS = 'A'
AND S.EFF_DATE = MED.MAX_DATE;

-- ALTERNATIVE: SUB-QUERY
-- THE EFFECTIVE DATE MAY COMPARE WITH SYSTEM DATE
SELECT S.AMOUNT AS DELIVERY_FEE
FROM SETUP_DELIVERY_FEE S
WHERE S.ORDER_TYPE = 'C'
AND S.STATUS = 'A'
AND S.EFF_DATE = (SELECT MAX(EFF_DATE) AS MAX_DATE -- RESULT -> ONE ROW
					FROM SETUP_DELIVERY_FEE SDF
					WHERE SDF.ORDER_TYPE = S.ORDER_TYPE
                    AND SDF.EFF_DATE <= CURDATE() -- TODAY
                    AND S.STATUS = 'A');

--
UPDATE SETUP_DELIVERY_FEE
SET STATUS = 'I'
WHERE ORDER_TYPE = 'C'
AND EFF_DATE = '2024-09-03';

-- AUTO_INCREMENT
CREATE TABLE FACULTY (
    ID INTEGER PRIMARY KEY AUTO_INCREMENT, -- DB auto find the max(id) and then + 1
    FACULTY_NAME VARCHAR(100) NOT NULL,
    FACULTY_HIRE_DATE DATE NOT NULL
);