basi_di_dati/compitino/primo_compitino/queries.tex

% !TEX root = ../main.tex

\begin{enumerate}[label=\alph*.]
    \item Uso di proiezione, join e restrizione

    Riportare il titolo e l’URL dei siti web il cui titolo inizia con la lettera G (compreso il caso
    in cui il titolo sia esattamente 'G’).
\begin{lstlisting}[style=SQLu]
SELECT e.Titolo, sw.Url
FROM Edizioni e
JOIN SitiWeb sw ON e.IdEdizione = sw.IdEdizione
WHERE e.Titolo >= 'G' AND e.Titolo < 'H'
\end{lstlisting}
    \item Uso di group by con having, where e sort

    Riportare IdUtente e tempo totale di lettura, purché superiore a 10 minuti, escludendo
    le sessioni di lettura lunghe un’ora o più (probabilmente l’utente avrà lasciato aperta la
    pagina, non stava davvero leggendo: sui nostri siti peraltro dopo 55 minuti di inattività
    si attiva una animazione che copre l’articolo e chiede di premere un pulsante per
    continuare a leggere, altrimenti esegue il logout dell’utente dopo 5 minuti) e ordinando
    dall’utente che ha passato più minuti a leggere a quello che ne ha passati meno.
\begin{lstlisting}[style=SQLu]
SELECT v.IdUtente, SUM(v.TempoLettura) TempoTotaleLettura
FROM Visite v
WHERE v.TempoLettura < 60
GROUP BY v.IdUtente
HAVING SUM(v.TempoLettura) > 10
ORDER BY SUM(v.TempoLettura) DESC
\end{lstlisting}
    \item Uso di join, group by con having e where

    Riportare il codice articolo, il titolo, il numero di visite e il tempo totale di lettura degli
    articoli ad accesso libero (ovvero non premium) visti almeno 3 volte.
\begin{lstlisting}[style=SQLu]
SELECT a.IdArticolo, a.Titolo, COUNT(*) NumeroVisite,
       SUM(v.TempoLettura) TempoTotaleLettura
FROM Visite v
JOIN Articoli a ON a.IdArticolo = v.IdArticolo
WHERE a.Premium = 'N'
GROUP BY a.IdArticolo, a.Titolo
HAVING COUNT(*) >= 3
\end{lstlisting}

    Posso fare COUNT(*) perché la giunzione con chiave esterna IdArticolo (che è chiave
    primaria della tabella Articoli) avrà tante righe quante ce ne sono nella tabella Visite:
    per ogni riga di Visite c’è una sola riga di Articoli.
    Raggruppo anche per titolo oltre che per IdArticolo (anche se IdArticolo → Titolo)
    perché proietto poi anche il Titolo.
    \item Uso di select annidata con quantificazione esistenziale

    Riportare Cognome e Nome dei giornalisti che hanno scritto almeno un articolo sul
    giornale “Il Titanio".
\begin{lstlisting}[style=SQLu]
SELECT g.Cognome, g.Nome
FROM Giornalisti g
WHERE EXISTS (SELECT *
              FROM ArticoliGiornalisti ag
              JOIN Articoli a ON a.IdArticolo = ag.IdArticolo
              JOIN Edizioni e ON e.IdEdizione = a.IdEdizione
              WHERE ag.IdGiornalista = g.IdGiornalista
                    AND e.Titolo = 'Il Titanio')
\end{lstlisting}

    NOTA: avrei potuto fare una giunzione su IdGiornalista anziché una quantificazione
    esistenziale.
    \item Uso di select annidata con quantificazione universale

    Riportare Cognome e Nome dei giornalisti che hanno scritto solo articoli per giornali
    diretti da Peppone.
    Esprimo in notazione insiemistica:
\begin{lstlisting}[style=SQLu,escapechar=@]
{g.Cognome, g.Nome | g @$\in$@ Giornalisti .
    @$\forall$@ (ag @$\in$@ ArticoliGiornalisti, a @$\in$@ Articoli, e @$\in$@ Edizioni,
        a.IdArticolo = ag.IdArticolo,
        e.IdEdizione = a.IdEdizione,
        ag.IdGiornalista = g.IdGiornalista) .
    (e.Direttore = 'Peppone')}
\end{lstlisting}
    Trasformando il $\forall x.P$ in $\neg\exists x.\neg P$:
\begin{lstlisting}[style=SQLu,escapechar=@]
{g.Cognome, g.Nome | g @$\in$@ Giornalisti .
    @$\neg\exists$@ (ag @$\in$@ ArticoliGiornalisti, a @$\in$@ Articoli, e @$\in$@ Edizioni,
        a.IdArticolo = ag.IdArticolo,
        e.IdEdizione = a.IdEdizione,
        ag.IdGiornalista = g.IdGiornalista) .
    (e.Direttore = 'Peppone')}
\end{lstlisting}
    Scrivo quindi la query:
\begin{lstlisting}[style=SQLu,escapechar=@]
SELECT g.Cognome, g.Nome
FROM Giornalisti g
WHERE NOT EXISTS (SELECT *
                  FROM ArticoliGiornalisti ag
                  JOIN Articoli a
                    ON a.IdArticolo = ag.IdArticolo
                  JOIN Edizioni e
                    ON e.IdEdizione = a.IdEdizione
                  WHERE ag.IdGiornalista = g.IdGiornalista
                        AND e.Direttore != 'Peppone')
\end{lstlisting}

    NOTA: i giornalisti che non hanno scritto articoli compariranno nel risultato.
    \item Uso di subquery di confronto quantificato usando una subquery di tipo scalare

    Voglio premiare il dipendente che esercita la professione da più tempo: per fare questo,
    ho bisogno di sapere il codice INPS del giornalista con numero di iscrizione all’albo più
    basso.
\begin{lstlisting}[style=SQLu,escapechar=@]
SELECT gd.CodiceInps
FROM GiornalistiDipendenti gd
JOIN Giornalisti g ON g.IdGiornalista = gd.IdGiornalista
WHERE g.NumeroAlbo = ANY(SELECT MIN(g2.NumeroALbo)
                         FROM Giornalisti g2
                         JOIN GiornalistiDipendenti gd2
                           ON g2.IdGiornalista = gd2.IdGiornalista)
\end{lstlisting}
    La subquery scalare restituisce un singolo valore, mentre il confronto quantificato è utile
    quando la subquery restituisce un insieme di più valori. \lstinline[language=SQL]{= ANY} equivale a \lstinline[language=SQL]{IN}.
\end{enumerate}