Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement