---

CREATE DATABASE IF NOT EXISTS ComputerComponents;
USE ComputerComponents;

CREATE TABLE Components (
    Name VARCHAR(100),
    Category VARCHAR(50),
    Specs BLOB
);
INSERT INTO Components (Name, Category, Specs) VALUES
('Intel Core i7-12700', 'CPU', COLUMN_CREATE('Cores', 12, 'Threads', 20, 'Clock', 3.6, 'TDP', 125)),
('NVIDIA GeForce RTX 4070', 'GPU', COLUMN_CREATE('vram_gb', 12, 'memory_type', 'GDDR6X', 'tdp', 200)),
('Corsair Vengeance LPX', 'RAM', COLUMN_CREATE('capacity_gb', 32, 'speed_mhz', 3200, 'type', 'DDR4'));
SELECT 
    Name, 
    Category, 
    COLUMN_GET(Specs, 'tdp' AS UNSIGNED) AS tdp
FROM Components;
SELECT 
    Name, 
    Category, 
    COALESCE(
        COLUMN_GET(Specs, 'tdp' AS UNSIGNED),
        COLUMN_GET(Specs, 'TDP' AS UNSIGNED)
    ) AS tdp
FROM Components;
SELECT Name, Category
FROM Components
WHERE COLUMN_GET(Specs, 'vram_gb' AS UNSIGNED) >= 12;
SELECT Name, Category
FROM Components
WHERE COLUMN_GET(Specs, 'base_clock' AS CHAR(100)) IS NOT NULL;
UPDATE Components
SET Specs = COLUMN_ADD(Specs, 'price', 320)
WHERE Category = 'CPU';
UPDATE Components
SET Specs = COLUMN_DELETE(Specs, 'type')
WHERE Category = 'RAM';
UPDATE Components
SET Specs = COLUMN_ADD(Specs, 'tdp', 220)
WHERE Category = 'GPU';
ALTER TABLE Components
ADD tdp_value INT UNSIGNED 
GENERATED ALWAYS AS (COALESCE(
    COLUMN_GET(Specs, 'tdp' AS UNSIGNED),
    COLUMN_GET(Specs, 'TDP' AS UNSIGNED)
)) VIRTUAL;