USE `lg_trenesanas_eksamenam_1`;

-- ============================================================
-- 1. VIEW izveidošana
-- ============================================================
CREATE OR REPLACE VIEW `lg_result_1` AS
SELECT
    dv.nr_pk                 AS `Nr_pk`,
    dv.nosaukums             AS `Darba_veids`,
    mv.apzimejums            AS `Mervieniba`,
    dv.cena_bez_pvn          AS `Vienibas_cena_Ls_bez_PVN`,
    k.nosaukums              AS `Kategorija`
FROM `darba_veidi` dv
JOIN `kategorijas`  k  ON k.kategorija_id  = dv.kategorija_id
JOIN `mervieniibas` mv ON mv.mervieniba_id = dv.mervieniba_id
ORDER BY
    k.kategorija_kods,
    CAST(SUBSTRING_INDEX(dv.nr_pk, ',', 1)  AS UNSIGNED),
    CAST(SUBSTRING_INDEX(dv.nr_pk, ',', -1) AS UNSIGNED);

-- ============================================================
-- 2. Datu izgūšana — pilnā tabulas forma (kā Excel variants)
-- ============================================================
SELECT
    dv.nr_pk                 AS `Nr. p.k.`,
    dv.nosaukums             AS `Darba veids`,
    mv.apzimejums            AS `Mērvienība`,
    dv.cena_bez_pvn          AS `Vienības cena. Ls (bez PVN)`
FROM `darba_veidi` dv
JOIN `kategorijas`  k  ON k.kategorija_id  = dv.kategorija_id
JOIN `mervieniibas` mv ON mv.mervieniba_id = dv.mervieniba_id
ORDER BY
    k.kategorija_kods,
    CAST(SUBSTRING_INDEX(dv.nr_pk, ',', 1)  AS UNSIGNED),
    CAST(SUBSTRING_INDEX(dv.nr_pk, ',', -1) AS UNSIGNED);

-- ============================================================
-- 3. Pārbaudes pieprasījumi
-- ============================================================

-- Visu kategoriju darbu skaits un vidējā cena
SELECT
    k.kategorija_kods            AS `Kat.`,
    k.nosaukums                  AS `Kategorija`,
    COUNT(*)                     AS `Darbu skaits`,
    MIN(dv.cena_bez_pvn)         AS `Min cena`,
    MAX(dv.cena_bez_pvn)         AS `Max cena`,
    ROUND(AVG(dv.cena_bez_pvn),2)AS `Vidējā cena`
FROM `darba_veidi` dv
JOIN `kategorijas` k ON k.kategorija_id = dv.kategorija_id
GROUP BY k.kategorija_id
ORDER BY k.kategorija_kods;