🔍 SELECT vaicājumi

-- Iegūt visas kolonnas un rindas
SELECT * FROM products;

-- Iegūt konkrētas kolonnas
SELECT name, price FROM products;

-- Aliases (kolonnas pārsaukšana rezultātā)
SELECT
    name        AS produkta_nosaukums,
    price       AS cena,
    in_stock    AS ir_noliktavā
FROM products;

-- Aritmētika vaicājumā
SELECT
    name,
    price,
    price * 1.21  AS cena_ar_pvn,
    price * 0.9   AS cena_ar_atlaidi
FROM products;

-- Tekstu savienošana
SELECT CONCAT(first_name, ' ', last_name) AS pilnais_vards FROM users;

-- DISTINCT – unikālās vērtības
SELECT DISTINCT category FROM products;

-- Skaitīt rindas
SELECT COUNT(*) FROM products;

-- Salīdzināšanas operatori
SELECT * FROM products WHERE price = 29.99;
SELECT * FROM products WHERE price != 29.99;   -- arī: <>
SELECT * FROM products WHERE price > 50;
SELECT * FROM products WHERE price >= 50;
SELECT * FROM products WHERE price < 10;
SELECT * FROM products WHERE price <= 10;

-- NULL pārbaude (NEIZMANTOT = NULL!)
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT NULL;

-- Loģiskie operatori
SELECT * FROM products
WHERE price > 20 AND in_stock = 1;

SELECT * FROM products
WHERE category = 'Elektronika' OR category = 'Datori';

SELECT * FROM products
WHERE NOT in_stock = 1;

-- Kombinācija ar iekavām
SELECT * FROM products
WHERE (price > 50 OR category = 'Sadzīves') AND in_stock = 1;

-- LIKE – modeļa meklēšana
-- % = jebkurš skaits rakstzīmju
-- _ = viena jebkura rakstzīme

SELECT * FROM products WHERE name LIKE '%telefon%';  -- Satur "telefon"
SELECT * FROM products WHERE name LIKE 'Mob%';       -- Sākas ar "Mob"
SELECT * FROM products WHERE name LIKE '%Pro';       -- Beidzas ar "Pro"
SELECT * FROM products WHERE code LIKE 'A_B%';       -- Sākas A, trešais B

-- NOT LIKE
SELECT * FROM products WHERE name NOT LIKE '%Demo%';

-- BETWEEN – diapazonā (iekļaujošs)
SELECT * FROM products WHERE price BETWEEN 10.00 AND 99.99;
SELECT * FROM orders  WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 99;

-- IN – vērtību sarakstā
SELECT * FROM products WHERE category IN ('Elektronika', 'Datori', 'Sadzīves');
SELECT * FROM users    WHERE id IN (1, 5, 10, 23);

-- NOT IN
SELECT * FROM products WHERE category NOT IN ('Apģērbs', 'Rotaļlietas');

-- REGEXP – regulāras izteiksmes
SELECT * FROM users WHERE email REGEXP '^[a-z]+@gmail\.com$';

-- ORDER BY – kārtošana
SELECT * FROM products ORDER BY price;           -- Augošā (noklusējums = ASC)
SELECT * FROM products ORDER BY price ASC;       -- Skaidri norādīts
SELECT * FROM products ORDER BY price DESC;      -- Dilstošā kārtībā

-- Kārtot pēc vairākām kolonnām
SELECT * FROM products
ORDER BY category ASC, price DESC;

-- LIMIT – ierobežot rezultātu skaitu
SELECT * FROM products ORDER BY price LIMIT 10;    -- Pirmie 10

-- LIMIT ar OFFSET – lappusēšana (pagination)
-- LIMIT rindu_skaits OFFSET izlaist_skaitu
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;   -- 1. lappuse
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;  -- 2. lappuse
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;  -- 3. lappuse

-- Kompaktais formāts: LIMIT izlaist, skaits
SELECT * FROM products ORDER BY id LIMIT 0, 10;   -- 1. lappuse
SELECT * FROM products ORDER BY id LIMIT 10, 10;  -- 2. lappuse

-- Pirmais un pēdējais ieraksts
SELECT * FROM products ORDER BY id    ASC  LIMIT 1;  -- Vecākais
SELECT * FROM products ORDER BY id    DESC LIMIT 1;  -- Jaunākais
SELECT * FROM products ORDER BY price DESC LIMIT 1;  -- Dārgākais

-- CASE WHEN – IF/ELSE SQL iekšā
SELECT
    name,
    price,
    CASE
        WHEN price < 10  THEN 'Lēts'
        WHEN price < 100 THEN 'Vidēja cena'
        ELSE                  'Dārgs'
    END AS cenu_kategorija
FROM products;

-- IF() – īsāks variants
SELECT name, IF(in_stock = 1, 'Pieejams', 'Nav noliktavā') AS statuss
FROM products;

-- COALESCE – pirmā non-NULL vērtība
SELECT name, COALESCE(description, 'Apraksts nav pieejams') AS apraksts
FROM products;

-- IFNULL – aizstāt NULL ar vērtību
SELECT name, IFNULL(category, 'Bez kategorijas') AS kategorija
FROM products;

-- Subquery SELECT iekšā
SELECT
    name,
    price,
    (SELECT AVG(price) FROM products) AS videjā_cena,
    price - (SELECT AVG(price) FROM products) AS starpiba
FROM products
ORDER BY price DESC;