SQLize | PHPize | SQLtest

-- Step 1: Gumawa ng bagong table na tbl_milkteashop CREATE TABLE tbl_milkteashop ( mt_id INT PRIMARY KEY AUTO_INCREMENT, mt_name VARCHAR(150) NOT NULL, mt_price DECIMAL(5,2) NOT NULL ); -- Step 2: Magdagdag ng bagong column (mt_flavor at mt_size) ALTER TABLE tbl_milkteashop ADD mt_flavor VARCHAR(50), ADD mt_size VARCHAR(10); -- Step 3: Baguhin ang column size ng mt_name mula VARCHAR(150) to VARCHAR(200) ALTER TABLE tbl_milkteashop MODIFY COLUMN mt_name VARCHAR(200); -- Step 4: Magdagdag ng bagong supplier table (para sa foreign key relationship) CREATE TABLE tbl_supplier ( supplier_id INT PRIMARY KEY AUTO_INCREMENT, supplier_name VARCHAR(100) NOT NULL ); -- Step 5: Magdagdag ng supplier_id column sa tbl_milkteashop at gawing foreign key ALTER TABLE tbl_milkteashop ADD supplier_id INT, ADD FOREIGN KEY (supplier_id) REFERENCES tbl_supplier(supplier_id) ON DELETE CASCADE; -- Step 6: Mag-delete ng column (mt_size) sa tbl_milkteashop ALTER TABLE tbl_milkteashop DROP COLUMN mt_size; -- Step 7: Mag-insert ng sample data para makita ang result INSERT INTO tbl_supplier (supplier_name) VALUES ('MilkTea Supplier Inc.'), ('Tea World'); INSERT INTO tbl_milkteashop (mt_name, mt_price, mt_flavor, supplier_id) VALUES ('Wintermelon', 99.99, 'Classic', 1), ('Okinawa', 109.99, 'Brown Sugar', 2), ('Matcha', 119.99, 'Matcha', 1), ('Taro', 105.99, 'Taro', 2), ('Classic', 89.99, 'Original', 1), ('Wintermelon', 99.99, 'Classic', 1); -- Duplicate para sa DISTINCT test -- Step 8: Mag-display ng lahat ng MilkTea records SELECT * FROM tbl_milkteashop; -- Step 9: Mag-sort ng Milk Tea ayon sa presyo (Mura to Mahal) SELECT mt_name, mt_flavor, mt_price FROM tbl_milkteashop ORDER BY mt_price ASC; -- Step 10: Mag-sort ng Milk Tea ayon sa flavor, tapos presyo (Mahal to Mura) SELECT mt_name, mt_flavor, mt_price FROM tbl_milkteashop ORDER BY mt_flavor ASC, mt_price DESC; -- Step 11: Mag-display ng unique Milk Tea flavors (DISTINCT) SELECT DISTINCT mt_flavor FROM tbl_milkteashop; -- Step 12: Maghanap ng Milk Tea na mas mura sa ₱100 at i-sort (Mahal to Mura) SELECT mt_name, mt_price FROM tbl_milkteashop WHERE mt_price < 100 ORDER BY mt_price DESC;