basi_di_dati/compitino/secondo_compitino/queries.tex

% !TEX root = ../main.tex

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

    Mostrare l'IdRicetta e il Nome delle ricette create da birrai di nome
    Giovanni.
\begin{lstlisting}[style=SQLu,escapechar=@]
SELECT r.IdRicetta, r.Nome
FROM Ricette r
JOIN Persone p ON p.IdPersona = r.IdCreatrice
WHERE p.Nome = 'Giovanni'
\end{lstlisting}
    \item Uso di group by con having, where e sort.

    Per ogni birrificio che abbia fatto almeno un acquisto quest'anno,
    riportare l'IdBirrificio e il numero di diversi fornitori da cui ha
    acquistato quest'anno, se questo numero è almeno di 3.
    Ordinare il risultato dal birrificio che ha avuto più fornitori a quello
    che ne ha avuti meno.
\begin{lstlisting}[style=SQLu,escapechar=@]
SELECT fa.IdBirrificio,
       COUNT(DISTINCT fa.IdFornitore) DiversiFornitori
FROM Fatture fa
WHERE fa.Data >= '2020-01-01'
GROUP BY fa.IdBirrificio
HAVING COUNT(DISTINCT fa.IdFornitore) >= 3
ORDER BY COUNT(DISTINCT fa.IdFornitore) DESC
\end{lstlisting}
    \item Uso di join, group by con having e where.

    Dei fornitori da cui ha ordinato il birrificio `Pirati Rossi', mostrare la
    ragione sociale, l'importo totale e l'importo medio delle fatture, purché
    l'importo totale sia superiore a 10 euro.
\begin{lstlisting}[style=SQLu,escapechar=@]
SELECT fo.RagioneSociale, SUM(fa.Importo) ImportoTotale,
       AVG(fa.Importo) ImportoMedio
FROM Fornitori fo
JOIN Fatture fa ON fa.IdFornitore = fo.IdFornitore
JOIN Birrifici b ON b.IdBirrificio = fa.IdBirrificio
WHERE b.Nome = 'Pirati Rossi'
GROUP BY fo.IdFornitore, fo.RagioneSociale
HAVING SUM(fa.Importo) > 10
\end{lstlisting}
    \item Uso di select annidata con quantificazione esistenziale.

    Mostrare il soprannome de* birrai* che siano aut*r* di almeno una ricetta.
\begin{lstlisting}[style=SQLu,escapechar=@]
SELECT b.Soprannome
FROM Birraie b
WHERE EXISTS (SELECT *
              FROM Ricette r
              WHERE r.IdCreatrice = b.IdPersona)
\end{lstlisting}
\clearpage

    \item Uso di select annidata con quantificazione universale.

    Mostrare il nome e il cognome de* clienti che hanno ordinato da un solo
    birrificio.

\textbf{Traduco in notazione insiemistica:}
\begin{lstlisting}[style=SQLu,escapechar=@]
{p1.Nome, p1.Cognome | (p1 @$\in$@ Persone, pre1 @$\in$@ Prenotazioni,
                        pre1.IdCliente = p1.IdPersona,
                        pro1 @$\in$@ Produzioni,
                        pro1.IdProduzione = pre1.IdProduzione,
                        r1 @$\in$@ Ricette,
                        r1.IdRicetta = pro1.IdRicetta) .
    @$\forall$@ (pre2 @$\in$@ Prenotazioni, pre2.IdCliente = pre1.IdCliente
       pro2 @$\in$@ Produzioni, pro2.IdProduzione = pre2.IdProduzione,
       r2 @$\in$@ Ricette, r2.IdRicetta = pro2.IdRicetta) .
       (r2.IdBirrificio = r1.IdBirrificio)}
\end{lstlisting}

\textbf{Sostituisco il $\forall x . P$ con $\neg\exists x . \neg P$}
\begin{lstlisting}[style=SQLu,escapechar=@]
{p1.Nome, p1.Cognome | (p1 @$\in$@ Persone, pre1 @$\in$@ Prenotazioni,
                        pre1.IdCliente = p1.IdPersona,
                        pro1 @$\in$@ Produzioni,
                        pro1.IdProduzione = pre1.IdProduzione,
                        r1 @$\in$@ Ricette,
                        r1.IdRicetta = pro1.IdRicetta) .
    @$\neg\exists$@ (pre2 @$\in$@ Prenotazioni, pre2.IdCliente = pre1.IdCliente
        pro2 @$\in$@ Produzioni, pro2.IdProduzione = pre2.IdProduzione,
        r2 @$\in$@ Ricette, r2.IdRicetta = pro2.IdRicetta) .
        (r2.IdBirrificio @$\neq$@ r1.IdBirrificio)}
\end{lstlisting}

    \textbf{Scrivo quindi la query}, inserendo l'\texttt{IdPersona} e la parola chiave
    \texttt{DISTINCT} per rimuovere i duplicati (ma non le persone omonime).
\begin{lstlisting}[style=SQLu,escapechar=@]
SELECT DISTINCT p1.IdPersona, p1.Nome, p1.Cognome
FROM Persone p1
JOIN Prenotazioni pre1 ON pre1.IdCliente = p1.IdPersona
JOIN Produzioni pro1 ON pro1.IdProduzione = pre1.IdProduzione
JOIN Ricette r1 ON r1.IdRicetta = pro1.IdRicetta
WHERE NOT EXISTS (SELECT *
                  FROM Prenotazioni pre2
                  JOIN Produzioni pro2
                       ON pro2.IdProduzione = pre2.IdProduzione
                  JOIN Ricette r2 ON r2.IdRicetta = pro2.IdRicetta
                  WHERE pre2.IdCliente = pre1.IdCliente
                        AND r2.IdBirrificio <> r1.IdBirrificio)
\end{lstlisting}

    \item Uso di subquery di confronto quantificato.

    Per ogni birrificio, mostrare l'IdBirrificio e l'ultimo NumeroLotto
    prodotto in quel birrificio (sapendo che il NumeroLotto è progressivo).
\begin{lstlisting}[style=SQLu,escapechar=@]
SELECT r1.IdBirrificio, pro1.NumeroLotto
FROM Produzioni pro1
JOIN Ricette r1 ON r1.IdRicetta = pro1.IdRicetta
WHERE pro1.NumeroLotto >= ANY (SELECT pro2.NumeroLotto
                               FROM Produzioni pro2
                               JOIN Ricette r2
                                    ON r2.IdRicetta = pro2.IdRicetta
                               WHERE r2.IdBirrificio = r1.IdBirrificio)
\end{lstlisting}
\end{enumerate}
\clearpage