---

-- 1
CREATE DATABASE IF NOT EXISTS lab_dyncol;
USE lab_dyncol;

CREATE TABLE components (
    Name VARCHAR(100),
    Category VARCHAR(50),
    Specs BLOB
);

-- 2
INSERT INTO components (Name, Category, Specs)
VALUES ('Intel Core i7-12700', 'CPU', 
    COLUMN_CREATE('Cores', 12, 'Threads', 20, 'Clock', 3.6 AS DECIMAL(3,1), 'TDP', 125));

INSERT INTO components (Name, Category, Specs)
VALUES ('NVIDIA GeForce RTX 4070', 'GPU', 
    COLUMN_CREATE('vram_gb', 12, 'memory_type', 'GDDR6X', 'tdp', 200));

INSERT INTO components (Name, Category, Specs)
VALUES ('Corsair Vengeance LPX', 'RAM', 
    COLUMN_CREATE('capacity_gb', 32, 'speed_mhz', 3200, 'type', 'DDR4'));

-- 3.1
SELECT Name, Category, COLUMN_GET(Specs, 'TDP' AS INT) AS tdp FROM components;
SELECT Name, Category, COLUMN_GET(Specs, 'tdp' AS INT) AS tdp FROM components;

-- 3.2
SELECT Name, Category 
FROM components 
WHERE COLUMN_GET(Specs, 'vram_gb' AS INT) >= 12;

-- 3.3
SELECT Name, Category 
FROM components 
WHERE COLUMN_EXISTS(Specs, 'base_clock');

-- 4.1
UPDATE components 
SET Specs = COLUMN_ADD(Specs, 'price', 35000.00 AS DECIMAL(10,2)) 
WHERE Name = 'Intel Core i7-12700';

-- 4.2
UPDATE components 
SET Specs = COLUMN_DELETE(Specs, 'type') 
WHERE Name = 'Corsair Vengeance LPX';

-- 4.3
UPDATE components 
SET Specs = COLUMN_ADD(Specs, 'tdp', 220) 
WHERE Name = 'NVIDIA GeForce RTX 4070';

-- 5
ALTER TABLE components 
ADD COLUMN tdp_virtual INT AS (COLUMN_GET(Specs, 'TDP' AS INT)) VIRTUAL;
ALTER TABLE components 
ADD COLUMN tdp_virtual2 INT AS (COLUMN_GET(Specs, 'tdp' AS INT)) VIRTUAL;

SELECT Name, Category, tdp_virtual, tdp_virtual2 FROM components;