-- Giuseppe Ruggeri - s236765
-- Quaderno 2
-- ES1
--1) Eseguo il join tra le tre tabelle coinvolte DIPARTIMENTO, RICERCATORE, CONTRATTO-DI-RICERCA
--2) Filtro le tuple facendo la differenza mediante l'operatore NOT IN sottraendo tutti i ricercatori che hanno partecipato ad almeno
--   un contratto con un importo inferiore o uguale a 100.000 euro o un contratto coinvolgente un'azienda che non sia 'Grande azienda';
--3) Raggruppo a questo punto con un GROUP BY per codice dipartimento e nome dipartimento;
--4) Seleziono infine il nome dipartimento e il numero di contratti validi stipulati;
--A questo punto il problema sembrerebbe concluso, ma la query in questo stadio ha prodotto in output solo i dipartimenti per cui almeno un ricercatore
--'valido' ha stipulato almeno un contratto, ovvero tutti i dipartimenti dove COUNT(*) è strettamente maggiore di zero.
--Per rispettare la richiesta del problema, ovvero, per OGNI dipartimento, visualizzarne il nome ed il numero di contratti,
--il risultato è stato completato con l'operatore UNION che esegue una query identica ma opposta, filtrando, sta volta,
--i ricercatori 'non validi', ovvero che hanno stipulato almeno un contratto inferiore ai 100.000 euro oppure con un'azienda non grande.
--Con una SELECT sul nome dipartimento e uno zero costante, si crea il risultato finale.

SELECT D.NomeD, COUNT(*)
FROM DIPARTIMENTO D, RICERCATORE R, CONTRATTO-DI-RICERCA CDR
WHERE D.CodD = R.CodD
    AND R.CodR = CDR.CodR-ResponsabileScientifico
    AND R.CodR NOT IN (SELECT DISTINCT CDR2.CodR-ResponsabileScientifico
                        FROM CONTRATTO-DI-RICERCA CDR2
                        WHERE CDR2.Importo <= 100000 OR CDR2.CodA NOT IN (SELECT A.CodA
                                                                            FROM AZIENDA A
                                                                            WHERE A.TipoA = 'Grande azienda')
                        )
GROUP BY D.CodD, D.NomeD

UNION

SELECT DISTINCT D.NomeD, 0
FROM DIPARTIMENTO D, RICERCATORE R, CONTRATTO-DI-RICERCA CDR
WHERE D.CodD = R.CodD
    AND R.CodR = CDR.CodR-ResponsabileScientifico
    AND R.CodR IN (SELECT DISTINCT CDR2.CodR-ResponsabileScientifico
                        FROM CONTRATTO-DI-RICERCA CDR2
                        WHERE CDR2.Importo <= 100000 OR CDR2.CodA NOT IN (SELECT A.CodA
                                                                            FROM AZIENDA A
                                                                            WHERE A.TipoA = 'Grande azienda')


------------------------------------------------------------------------------------------------------------


-- ES2
-- A)
--1) Nella query interna ho selezionato i codici autori che hanno effettuato almeno una presentazione di un articolo di un argomento diverso da 'Data Mining';
--2) Nella query esterna, dopo aver effettuato il join tra le presentazioni e gli autori, si filtrano le tuple imponendo che il codice autore non
--   appartenga ai codici autori selezionati nella query interna di cui sopra mediante l'operatore NOT IN.
--3) Si effettua quindi un GROUP BY sul codice autore, cognome, università (tutti funzionalmente legati e quindi non risultanti in un diverso raggruppamento),
--   oltre al raggruppare per Conferenza ed Edizione, poichè il testo richiede il numero totale di articoli in ciascuna edizione di ogni conferenza.
--4) Con una SELECT si selezionano infine gli attributi d'interesse e si contano il numero di articoli DISTINTI per ogni edizione di ogni conferenza,
--   poichè dalla tabella AUTORE_PRESENTA_ARTICOLO si evince che un articolo può essere presentato più di una volta.

SELECT AUT.CodAutore, AUT.Cognome, AUT.Università, COUNT(DISTINCT APAVALID.CodArticolo)
FROM AUTORE_PRESENTA_ARTICOLO APAVALID, AUTORE AUT
WHERE APAVALID.CodAutore NOT IN (SELECT DISTINCT APAOUT.CodAutore
                                    FROM AUTORE_PRESENTA_ARTICOLO APAOUT
                                    WHERE APAOUT.CodArticolo IN (SELECT A.CodArticolo
                                                                    FROM ARTICOLO A
                                                                    WHERE A.Argomento <> 'Data Mining')
                                )
    AND APAVALID.CodAutore = AUT.CodAutore
GROUP BY AUT.CodAutore, AUT.Cognome, AUT.Università, APAVALID.Conferenza, APAVALID.Edizione;

------------------------------------------------------------------------------------------------------------

-- B)
--Per la risoluzione del problema ho proceduto per step bottom-up, dalle query interne verso le query esterne:
--1) Per prima cosa ho creato la Table Function NUM_ART_PER_EDIZIONE che permette di memorizzare per ogni edizione: Conferenza, Edizione, Codice autore
--   e numero articoli di quell'edizione, presentati da quell'autore; in questo stadio, grazie alla GROUP BY su questi attributi, ci sono ancora più tuple per ogni edizione,
--   ognuna relativa ad un autore diverso e con un numero diverso di articoli presentati;
--2) Dalla tabella al punto (1), viene effettuato un nuovo raggruppamento per edizione (conferenza, edizione), sfruttando la nuova granularità, si selezionano
--   cosìfacendo Conferenza, Edizione e numero di articoli massimo presentati per quell'edizione, memorizzati in un'altra opportuna Table Function MAX_ART_PER_EDIZIONE
--3) Combinando adesso in una FROM le Table Function create al punto 1 ed al punto 2, NAPE e MAX_ART_PER_EDIZIONE, si effettua il join sugli attributi comuni
--   e sta volta si filtrano le tuple chiedendo che il numero di articoli presentati da quell'autore per quell'edizione corrisponda al massimo numero di articoli presentati per quell'edizione,
--   si utilizza ancora una volta una Table Function per memorizzare Conferenza, Edizione e CodAutoreMax, chiamata MAX_AUTORE_PER_EDIZIONE e predisporsi al join finale;
--4) Avendo finalmente ottenuto la tabella virtuale MAX_AUTORE_PER_EDIZIONE, che, per ogni edizione, associa il codice dell autore che ha presentato più articoli per quell'edizione,
--   non resta che combinarla in una FROM con una tabella EDIZIONI_CONFERENZA EC, si effettua il join sui due attributi comuni;
--5) Si effettua quindi infine, oltre al join, un filtro sulle conferenze valide e si procede a visualizzare, finalmente, il nome dell'edizione e il codice dell'autore.

SELECT EC.NomeEdizione, MAX_AUTORE_PER_EDIZIONE.CodAutoreMax -- SELECT finale visualizzando nome edizione e codice autore richiesto associato
FROM    (SELECT NAPE.Conferenza AS Conferenza, NAPE.Edizione AS Edizione, NAPE.CodAutore AS CodAutoreMax
         FROM ( SELECT APARES.Conferenza AS Conferenza, APARES.Edizione AS Edizione, APARES.CodAutore AS CodAutore, COUNT(DISTINCT APARES.CodArticolo) AS NumArt
                FROM AUTORE_PRESENTA_ARTICOLO APARES
                GROUP BY APARES.Conferenza, APARES.Edizione, APARES.CodAutore
             )
             AS NAPE,  -- Tabella virtuale contenenete Conferenza, Edizione, CodiceAutore e numero di articoli di quell'edizione di quell'autore

            ( SELECT NUM_ART_PER_EDIZIONE.Conferenza AS Conferenza, NUM_ART_PER_EDIZIONE.Edizione AS Edizione,                                                                  MAX(NUM_ART_PER_EDIZIONE.NumArt) AS MaxNumArt
                FROM ( SELECT APA.Conferenza AS Conferenza, APA.Edizione AS Edizione, APA.CodAutore AS CodAutore, COUNT(DISTINCT APA.CodArticolo) AS NumArt
                    FROM AUTORE_PRESENTA_ARTICOLO APA
                    GROUP BY APA.Conferenza, APA.Edizione, APA.CodAutore ) AS NUM_ART_PER_EDIZIONE -- Tabella virtuale contenente Conferenza, Edizione, Codice Autore e numero di articoli di quell'edizione di quell'autore
                GROUP BY NUM_ART_PER_EDIZIONE.Conferenza, NUM_ART_PER_EDIZIONE.Edizione
            )
            AS MAX_ART_PER_EDIZIONE -- Tabella virtuale contenente Conferenza, Edizione e massimo numero di articoli per quell'edizione
         WHERE NAPE.Conferenza = MAX_ART_PER_EDIZIONE.Conferenza -- Join sugli attributi delle due tabelle
            AND NAPE.Edizione = MAX_ART_PER_EDIZIONE.Edizione
            AND NAPE.NumArt = MAX_ART_PER_EDIZIONE.MaxNumArt -- Filtro le tuple della tabella selezionando solo quelle dove il numero articoli corrisponde al numero massimo articoli
        )
        AS MAX_AUTORE_PER_EDIZIONE, -- Tabella virtuale contenente Conferenza, Edizione e codice autore con massimo numero di articoli per quell'edizione
        EDIZIONI_CONFERENZA EC -- Conferenze
WHERE EC.Conferenza IN (SELECT ECV.Conferenza FROM EDIZIONI_CONFERENZA ECV GROUP BY ECV.Conferenza HAVING COUNT(*) >= 10) -- Filtro conferenze valide
    AND EC.Conferenza = MAX_AUTORE_PER_EDIZIONE.Conferenza -- Join
    AND EC.Edizione = MAX_AUTORE_PER_EDIZIONE.Edizione


------------------------------------------------------------------------------------------------------------


--ES3
--Ho scelto di procedere gradualmente introducendo vincoli di volta in volta ed utilizzando nomi specifici per
--evitare completamente le ambiguità tra più istanze, "OUT" sta per "sta fuori".
--1) In un primo step ho creato una Table Function chiamata HWMAX che costruisce una tabella costituita, per ogni homework da consegnare,
--   dal codice homework e dalla valutazione più alta assegnata (CodHW, MaxVal);
--2) Esternamente combino le tre tabelle STUDENTE S, VALUTAZIONE_HOMEWORK_CONSEGNATI VHC e la table function HWMAX definita pocanzi
--   effettuando in primis una differenza, con l operatore NOT IN, sottraendo tutte le matricole degli studenti che hanno consegnato oltre
--   la scadenza prevista per almeno un homework;
--3) Sempre nella clausula WHERE esterna effettuo un join tra le tre tabelle considerate sugli attributi comuni ed infine filtro le tuple
--   chiedendo che abbiano la valutazione massima per l homework considerato.
--4) Infine, a partite dalla tabella costituita dagli attributi di Studente, VHC e la table function, con le tuple opportunamente filtrate,
--   effettuo un GROUP BY sulla matricola, sul cognome e sul corso di laurea, sfruttando la clausula HAVING e chiedendo che, per ogni studente,
--   di fatto, ci sia un numero di tuple (valutazioni diverse con valutazione massima) uguale al numero di homework da consegnare.


SELECT S.MatricolaS, S.Cognome, S.Corso_di_Laurea
FROM STUDENTE S, VALUTAZIONE_HOMEWORK_CONSEGNATI VHC,
     (SELECT VHCMAX.CodHW AS CodHW, MAX(VHCMAX.Valutazione) AS MaxVal
      FROM VALUTAZIONE_HOMEWORK_CONSEGNATI VHCMAX
      GROUP BY VHCMAX.CodHW) AS HWMAX
WHERE S.MatricolaS NOT IN ( SELECT VHCOUT.MatricolaS
                            FROM VALUTAZIONE_HOMEWORK_CONSEGNATI VHCOUT, HOMEWORK_DA_CONSEGNARE HDC
                            WHERE VHCOUT.CodHW = HDC.CodHW AND VHCOUT.DataConsegna > HDC.DataScadenzaPrevista )
    AND S.MatricolaS = VHC.MatricolaS
    AND VHC.CodHW = HWMAX.CodHW
    AND VHC.Valutazione = HWMAX.MaxVal
GROUP BY S.MatricolaS, S.Cognome, S.Corso_di_Laurea HAVING COUNT(*) = (SELECT COUNT(*) FROM HOMEWORK_DA_CONSEGNARE);


------------------------------------------------------------------------------------------------------------


-- ES4)
--1) Nella query nidificata ho calcolato i piatti validi;
--2) Ho eseguito il join tra le tre tabelle PIATTO, PIATTO-ORDINATO e ORDINAZIONE sugli attributi comuni filtrando i piatti per prezzo > 10 euro;
--2) Ho raggruppato le tuple con l operatore GROUP BY sugli attributi CodP, DescrizioneP e Data, imponendo con la clausula HAVING
--   che il gruppo avesse un numero di tavoli distinti (per ogni data) almeno uguale a 10;
--3) Esternamente utilizzo nuove istanze delle stesse tre tabelle integrali facendo nuovamente il join tra di esse,
--   sta volta imponendo che il codice del piatto sia tra quelli validi precedentemente calcolati mediante l operatore IN;
--4) Si effettua nuovamente un GROUP BY esterno identico poichè si è interessati alle informazioni, per ogni piatto valido, per ogni singola giornata;
--La query è stata spezzata in query esterna ed interna per il semplice fatto che, una volta filtrati i gruppi per COUNT(DISTINT O.NumTavolo) >= 10,
--se si visualizzasse la quantità totale e l importo totale direttamente in quel momento, si perderebbero informazioni sui piatti ordinati che sono stati scartati
--assieme ai gruppi scartati. Per tale motivo ho introdotto una query esterna che considera nuovamente i piatti da zero (solo validi) e ne visualizza
--correttamente le informazioni di gruppo per ogni data.

SELECT PEXT.CodP, PEXT.DescrizioneP, SUM(POEXT.Quantità) AS QuantitàTot, SUM(POEXT.Quantità * PEXT.Prezzo) AS ImportoTot
FROM PIATTO-ORDINATO POEXT, ORDINAZIONE OEXT, PIATTO PEXT
WHERE PEXT.CodP IN ( SELECT P.CodP
                     FROM PIATTO P, PIATTO-ORDINATO PO, ORDINAZIONE O
                     WHERE P.Prezzo > 10 AND P.CodP = PO.CodP AND PO.NumOrdinazione = O.NumOrdinazione
                     GROUP BY P.CodP, O.DATA
                     HAVING COUNT(DISTINCT O.NumTavolo) >= 10 )
    AND PEXT.CodP = POEXT.CodP
    AND POEXT.NumOrdinazione = OEXT.NumOrdinazione
GROUP BY PEXT.CodP, PEXT.DescrizioneP, OEXT.DATA