deepm09/SQLDB_Tshirts

1-- Create the database2CREATE DATABASE atliq_tshirts;3USE atliq_tshirts;4 5-- Create the t_shirts table6CREATE TABLE t_shirts (7    t_shirt_id INT AUTO_INCREMENT PRIMARY KEY,8    brand ENUM('Van Huesen', 'Levi', 'Nike', 'Adidas') NOT NULL,9    color ENUM('Red', 'Blue', 'Black', 'White') NOT NULL,10    size ENUM('XS', 'S', 'M', 'L', 'XL') NOT NULL,11    price INT CHECK (price BETWEEN 10 AND 50),12    stock_quantity INT NOT NULL,13    UNIQUE KEY brand_color_size (brand, color, size)14);15 16-- Create the discounts table17CREATE TABLE discounts (18    discount_id INT AUTO_INCREMENT PRIMARY KEY,19    t_shirt_id INT NOT NULL,20    pct_discount DECIMAL(5,2) CHECK (pct_discount BETWEEN 0 AND 100),21    FOREIGN KEY (t_shirt_id) REFERENCES t_shirts(t_shirt_id)22);23 24-- Create a stored procedure to populate the t_shirts table25DELIMITER $$26CREATE PROCEDURE PopulateTShirts()27BEGIN28    DECLARE counter INT DEFAULT 0;29    DECLARE max_records INT DEFAULT 100;30    DECLARE brand ENUM('Van Huesen', 'Levi', 'Nike', 'Adidas');31    DECLARE color ENUM('Red', 'Blue', 'Black', 'White');32    DECLARE size ENUM('XS', 'S', 'M', 'L', 'XL');33    DECLARE price INT;34    DECLARE stock INT;35 36    -- Seed the random number generator37    SET SESSION rand_seed1 = UNIX_TIMESTAMP();38 39    WHILE counter < max_records DO40        -- Generate random values41        SET brand = ELT(FLOOR(1 + RAND() * 4), 'Van Huesen', 'Levi', 'Nike', 'Adidas');42        SET color = ELT(FLOOR(1 + RAND() * 4), 'Red', 'Blue', 'Black', 'White');43        SET size = ELT(FLOOR(1 + RAND() * 5), 'XS', 'S', 'M', 'L', 'XL');44        SET price = FLOOR(10 + RAND() * 41);45        SET stock = FLOOR(10 + RAND() * 91);46 47        -- Attempt to insert a new record48        -- Duplicate brand, color, size combinations will be ignored due to the unique constraint49        BEGIN50            DECLARE CONTINUE HANDLER FOR 1062 BEGIN END;  -- Handle duplicate key error51            INSERT INTO t_shirts (brand, color, size, price, stock_quantity)52            VALUES (brand, color, size, price, stock);53            SET counter = counter + 1;54        END;55    END WHILE;56END$$57DELIMITER ;58 59-- Call the stored procedure to populate the t_shirts table60CALL PopulateTShirts();61 62-- Insert at least 10 records into the discounts table63INSERT INTO discounts (t_shirt_id, pct_discount)64VALUES65(1, 10.00),66(2, 15.00),67(3, 20.00),68(4, 5.00),69(5, 25.00),70(6, 10.00),71(7, 30.00),72(8, 35.00),73(9, 40.00),74(10, 45.00);