MS SQL v príkladoch: poddotazy v príkladoch

V predchádzajúcom kvíze, Kvíz - Odpojená ap., SQL otázky, Manag. Štúdio v C#-ADO.NET, sme si overili nadobudnuté skúsenosti z predchádzajúcich lekcií.

Minule sme si vyskúšali otázky cez viac tabuliek, dnes sa zameriame na poddotazy. Výsledok SQL dotazu totiž môže veľmi jednoducho slúžiť ako zdroj pre ďalšie SELECT. Buďte však opatrní, pretože sa jedná o časovo náročné operácie a poddotazy sú všeobecne veľmi pomalé.

Opäť som sa príklady snažil zoradiť podľa ich zložitosti.

Príklad 1: Vypísanie článkov, ktorých autorom je používateľ s prezývkou David.

SELECT
    Titulek,
    Perex,
    Obsah
FROM Clanek
WHERE AutorID = (
    SELECT
        UzivatelID
    FROM Uzivatel
    WHERE Nick = 'David'
);

Príklad 2: Zistenie priemerného počtu článkov na užívateľa.

SELECT
    AVG(X)
FROM
(
    SELECT
        COUNT(*) X
    FROM Clanek
    GROUP BY (AutorID)
) a;

Príklad 3: Zistiť priemerný výsledok zo zadaného testu.

SELECT
    AVG(Skore) Vysledek
FROM
(
    SELECT
        Skore
    FROM VysledekTestu
    WHERE (TestID = 1)
) a;

Príklad 4: Zistiť priemerný výsledok zo všetkých testov na portáli.

SELECT
    AVG(Skore) Vysledek
FROM
(
    SELECT
        Skore
    FROM VysledekTestu
) a;

Príklad 5: Aký je najlepší priemerný výsledok v testoch.

SELECT
    MAX(X) Vysledek
FROM (
    SELECT
        AVG(Skore) X
    FROM (
        SELECT
            Skore, UzivatelID
        FROM VysledekTestu
    ) a
    GROUP BY (UzivatelID)
) b;

Príklad 6: Vypísanie článkov, ktoré nepatria do žiadnej sekcie.

SELECT
    c.ClanekID,
    c.Titulek
FROM Clanek c
WHERE
    (SELECT
        COUNT(*)
     FROM ClanekSekce
     WHERE (ClanekSekce.ClanekID = c.ClanekID)) = 0;

Príklad 7: Vybrať užívateľov, ktorí majú najlepší výsledok z testu s určitým ID (vďaka poddotazu môžeme vybrať viac hodnôt).

SELECT
    Nick,
    Skore
FROM VysledekTestu, Uzivatel
WHERE (VysledekTestu.Skore=(
    SELECT
        MAX(Skore)
    FROM VysledekTestu
    WHERE TestID = 1)) AND
    (TestID = 1) AND
    (VysledekTestu.UzivatelID = Uzivatel.UzivatelID);

Príklad 8: Vypísanie komentárov, ktoré sú novšie, než všetky komentáre autora s danou prezývkou (slúži užívateľom k zobrazenie komentárov, ktoré ešte nečítali od svojej poslednej aktivity na webe, teda od napísania svojich komentárov).

SELECT
    Obsah,
    Datum
FROM Komentar
WHERE Datum > ALL (
    SELECT Datum
    FROM Komentar
        JOIN Uzivatel
            ON (Uzivatel.UzivatelID = Komentar.UzivatelID)
    WHERE (Uzivatel.Nick = 'Denny')
)
ORDER BY Datum DESC;

Príklad 9: Ktorý užívateľ napísal najvyšší počet článkov.

SELECT TOP 1
U.Nick, (
    SELECT
        COUNT(*)
    FROM Clanek
    WHERE (Clanek.AutorID = U.UzivatelID)
) CNT
FROM
Uzivatel U
ORDER BY CNT DESC;

Príklad 10: Ktorý užívateľ má najlepší priemerný výsledok v testoch.

SELECT TOP 1
    U.Nick, (
    SELECT
        AVG(Skore)
    FROM (
        SELECT
            Skore
        FROM VysledekTestu
        WHERE UzivatelID = U.UzivatelID
    ) X
    ) Vysledek
FROM Uzivatel U
ORDER BY Vysledek DESC;

Pohľady

Príklad 11: Rozhodli sme sa uložiť si príkaz na vypísanie najnovšieho článku do pohľadu.

CREATE VIEW NejnovejsiClanek AS
    SELECT TOP 1
        c.Perex,
        c.PrettyURL,
        c.Titulek,
        c.Publikovano,
        u.Nick
    FROM Clanek c, Uzivatel u
    WHERE (u.UzivatelID = c.AutorID)
    ORDER BY c.Publikovano DESC;

Následne budeme volať len skrátene:

SELECT *
FROM NejnovejsiClanek

Ďalšie otázky

Nasledujúce otázky som nechával na koniec, pretože mení obsah alebo štruktúru databázy a mohli by zasahovať do predchádzajúcich príkladov. Urobme si ich teda teraz:

Príklad 12: Občas je potrebné vymazať nevhodný komentár.

DELETE FROM
    Komentar
WHERE
    KomentarID = 5;

Príklad 13: Ukázalo sa ako potrebné pridať k článkom hodnotenia (podobné, ako like na facebooku).

ALTER TABLE Clanek ADD Palcu int;

Príklad 14: Pre obnovu tabuľky komentáre zo zálohy (kvôli útoku spambotmi) sa musí tabuľka najprv vymazať, preto bol dodaný nasledujúce dotaz.

DROP TABLE Komentar;

Nabudúce si povieme niečo o optimalizáciu MS SQL databázy.