SHARE
TWEET

Untitled

a guest Apr 19th, 2019 94 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Giuseppe Ruggeri - s236765
  2. -- Quaderno 2
  3. -- ES1
  4. --1) Eseguo il join tra le tre tabelle coinvolte DIPARTIMENTO, RICERCATORE, CONTRATTO-DI-RICERCA
  5. --2) Filtro le tuple facendo la differenza mediante l'operatore NOT IN sottraendo tutti i ricercatori che hanno partecipato ad almeno
  6. --   un contratto con un importo inferiore o uguale a 100.000 euro o un contratto coinvolgente un'azienda che non sia 'Grande azienda';
  7. --3) Raggruppo a questo punto con un GROUP BY per codice dipartimento e nome dipartimento;
  8. --4) Seleziono infine il nome dipartimento e il numero di contratti validi stipulati;
  9. --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
  10. --'valido' ha stipulato almeno un contratto, ovvero tutti i dipartimenti dove COUNT(*) è strettamente maggiore di zero.
  11. --Per rispettare la richiesta del problema, ovvero, per OGNI dipartimento, visualizzarne il nome ed il numero di contratti,
  12. --il risultato è stato completato con l'operatore UNION che esegue una query identica ma opposta, filtrando, sta volta,
  13. --i ricercatori 'non validi', ovvero che hanno stipulato almeno un contratto inferiore ai 100.000 euro oppure con un'azienda non grande.
  14. --Con una SELECT sul nome dipartimento e uno zero costante, si crea il risultato finale.
  15.  
  16. SELECT D.NomeD, COUNT(*)
  17. FROM DIPARTIMENTO D, RICERCATORE R, CONTRATTO-DI-RICERCA CDR
  18. WHERE D.CodD = R.CodD
  19.     AND R.CodR = CDR.CodR-ResponsabileScientifico
  20.     AND R.CodR NOT IN (SELECT DISTINCT CDR2.CodR-ResponsabileScientifico
  21.                         FROM CONTRATTO-DI-RICERCA CDR2
  22.                         WHERE CDR2.Importo <= 100000 OR CDR2.CodA NOT IN (SELECT A.CodA
  23.                                                                             FROM AZIENDA A
  24.                                                                             WHERE A.TipoA = 'Grande azienda')
  25.                         )                              
  26. GROUP BY D.CodD, D.NomeD
  27.  
  28. UNION
  29.  
  30. SELECT DISTINCT D.NomeD, 0
  31. FROM DIPARTIMENTO D, RICERCATORE R, CONTRATTO-DI-RICERCA CDR
  32. WHERE D.CodD = R.CodD
  33.     AND R.CodR = CDR.CodR-ResponsabileScientifico
  34.     AND R.CodR IN (SELECT DISTINCT CDR2.CodR-ResponsabileScientifico
  35.                         FROM CONTRATTO-DI-RICERCA CDR2
  36.                         WHERE CDR2.Importo <= 100000 OR CDR2.CodA NOT IN (SELECT A.CodA
  37.                                                                             FROM AZIENDA A
  38.                                                                             WHERE A.TipoA = 'Grande azienda')
  39.                                                                            
  40.                                                                            
  41. ------------------------------------------------------------------------------------------------------------
  42.  
  43.  
  44. -- ES2
  45. -- A)
  46. --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';
  47. --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
  48. --   appartenga ai codici autori selezionati nella query interna di cui sopra mediante l'operatore NOT IN.
  49. --3) Si effettua quindi un GROUP BY sul codice autore, cognome, università (tutti funzionalmente legati e quindi non risultanti in un diverso raggruppamento),
  50. --   oltre al raggruppare per Conferenza ed Edizione, poichè il testo richiede il numero totale di articoli in ciascuna edizione di ogni conferenza.
  51. --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,
  52. --   poichè dalla tabella AUTORE_PRESENTA_ARTICOLO si evince che un articolo può essere presentato più di una volta.
  53.    
  54. SELECT AUT.CodAutore, AUT.Cognome, AUT.Università, COUNT(DISTINCT APAVALID.CodArticolo)
  55. FROM AUTORE_PRESENTA_ARTICOLO APAVALID, AUTORE AUT
  56. WHERE APAVALID.CodAutore NOT IN (SELECT DISTINCT APAOUT.CodAutore
  57.                                     FROM AUTORE_PRESENTA_ARTICOLO APAOUT
  58.                                     WHERE APAOUT.CodArticolo IN (SELECT A.CodArticolo
  59.                                                                     FROM ARTICOLO A
  60.                                                                     WHERE A.Argomento <> 'Data Mining')
  61.                                 )
  62.     AND APAVALID.CodAutore = AUT.CodAutore
  63. GROUP BY AUT.CodAutore, AUT.Cognome, AUT.Università, APAVALID.Conferenza, APAVALID.Edizione;
  64.  
  65. ------------------------------------------------------------------------------------------------------------
  66.  
  67. -- B)
  68. --Per la risoluzione del problema ho proceduto per step bottom-up, dalle query interne verso le query esterne:
  69. --1) Per prima cosa ho creato la Table Function NUM_ART_PER_EDIZIONE che permette di memorizzare per ogni edizione: Conferenza, Edizione, Codice autore
  70. --   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,
  71. --   ognuna relativa ad un autore diverso e con un numero diverso di articoli presentati;
  72. --2) Dalla tabella al punto (1), viene effettuato un nuovo raggruppamento per edizione (conferenza, edizione), sfruttando la nuova granularità, si selezionano
  73. --   cosìfacendo Conferenza, Edizione e numero di articoli massimo presentati per quell'edizione, memorizzati in un'altra opportuna Table Function MAX_ART_PER_EDIZIONE
  74. --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
  75. --   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,
  76. --   si utilizza ancora una volta una Table Function per memorizzare Conferenza, Edizione e CodAutoreMax, chiamata MAX_AUTORE_PER_EDIZIONE e predisporsi al join finale;
  77. --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,
  78. --   non resta che combinarla in una FROM con una tabella EDIZIONI_CONFERENZA EC, si effettua il join sui due attributi comuni;
  79. --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.
  80.    
  81. SELECT EC.NomeEdizione, MAX_AUTORE_PER_EDIZIONE.CodAutoreMax -- SELECT finale visualizzando nome edizione e codice autore richiesto associato
  82. FROM    (SELECT NAPE.Conferenza AS Conferenza, NAPE.Edizione AS Edizione, NAPE.CodAutore AS CodAutoreMax
  83.          FROM ( SELECT APARES.Conferenza AS Conferenza, APARES.Edizione AS Edizione, APARES.CodAutore AS CodAutore, COUNT(DISTINCT APARES.CodArticolo) AS NumArt
  84.                 FROM AUTORE_PRESENTA_ARTICOLO APARES
  85.                 GROUP BY APARES.Conferenza, APARES.Edizione, APARES.CodAutore
  86.              )
  87.              AS NAPE,  -- Tabella virtuale contenenete Conferenza, Edizione, CodiceAutore e numero di articoli di quell'edizione di quell'autore
  88.              
  89.             ( SELECT NUM_ART_PER_EDIZIONE.Conferenza AS Conferenza, NUM_ART_PER_EDIZIONE.Edizione AS Edizione,                                                                  MAX(NUM_ART_PER_EDIZIONE.NumArt) AS MaxNumArt
  90.                 FROM ( SELECT APA.Conferenza AS Conferenza, APA.Edizione AS Edizione, APA.CodAutore AS CodAutore, COUNT(DISTINCT APA.CodArticolo) AS NumArt
  91.                     FROM AUTORE_PRESENTA_ARTICOLO APA
  92.                     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
  93.                 GROUP BY NUM_ART_PER_EDIZIONE.Conferenza, NUM_ART_PER_EDIZIONE.Edizione
  94.             )
  95.             AS MAX_ART_PER_EDIZIONE -- Tabella virtuale contenente Conferenza, Edizione e massimo numero di articoli per quell'edizione
  96.          WHERE NAPE.Conferenza = MAX_ART_PER_EDIZIONE.Conferenza -- Join sugli attributi delle due tabelle
  97.             AND NAPE.Edizione = MAX_ART_PER_EDIZIONE.Edizione
  98.             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
  99.         )
  100.         AS MAX_AUTORE_PER_EDIZIONE, -- Tabella virtuale contenente Conferenza, Edizione e codice autore con massimo numero di articoli per quell'edizione
  101.         EDIZIONI_CONFERENZA EC -- Conferenze
  102. WHERE EC.Conferenza IN (SELECT ECV.Conferenza FROM EDIZIONI_CONFERENZA ECV GROUP BY ECV.Conferenza HAVING COUNT(*) >= 10) -- Filtro conferenze valide
  103.     AND EC.Conferenza = MAX_AUTORE_PER_EDIZIONE.Conferenza -- Join
  104.     AND EC.Edizione = MAX_AUTORE_PER_EDIZIONE.Edizione
  105.    
  106.    
  107. ------------------------------------------------------------------------------------------------------------
  108.  
  109.  
  110. --ES3
  111. --Ho scelto di procedere gradualmente introducendo vincoli di volta in volta ed utilizzando nomi specifici per
  112. --evitare completamente le ambiguità tra più istanze, "OUT" sta per "sta fuori".
  113. --1) In un primo step ho creato una Table Function chiamata HWMAX che costruisce una tabella costituita, per ogni homework da consegnare,
  114. --   dal codice homework e dalla valutazione più alta assegnata (CodHW, MaxVal);
  115. --2) Esternamente combino le tre tabelle STUDENTE S, VALUTAZIONE_HOMEWORK_CONSEGNATI VHC e la table function HWMAX definita pocanzi
  116. --   effettuando in primis una differenza, con l operatore NOT IN, sottraendo tutte le matricole degli studenti che hanno consegnato oltre
  117. --   la scadenza prevista per almeno un homework;
  118. --3) Sempre nella clausula WHERE esterna effettuo un join tra le tre tabelle considerate sugli attributi comuni ed infine filtro le tuple
  119. --   chiedendo che abbiano la valutazione massima per l homework considerato.
  120. --4) Infine, a partite dalla tabella costituita dagli attributi di Studente, VHC e la table function, con le tuple opportunamente filtrate,
  121. --   effettuo un GROUP BY sulla matricola, sul cognome e sul corso di laurea, sfruttando la clausula HAVING e chiedendo che, per ogni studente,
  122. --   di fatto, ci sia un numero di tuple (valutazioni diverse con valutazione massima) uguale al numero di homework da consegnare.
  123.    
  124.  
  125. SELECT S.MatricolaS, S.Cognome, S.Corso_di_Laurea
  126. FROM STUDENTE S, VALUTAZIONE_HOMEWORK_CONSEGNATI VHC,
  127.      (SELECT VHCMAX.CodHW AS CodHW, MAX(VHCMAX.Valutazione) AS MaxVal
  128.       FROM VALUTAZIONE_HOMEWORK_CONSEGNATI VHCMAX
  129.       GROUP BY VHCMAX.CodHW) AS HWMAX
  130. WHERE S.MatricolaS NOT IN ( SELECT VHCOUT.MatricolaS
  131.                             FROM VALUTAZIONE_HOMEWORK_CONSEGNATI VHCOUT, HOMEWORK_DA_CONSEGNARE HDC
  132.                             WHERE VHCOUT.CodHW = HDC.CodHW AND VHCOUT.DataConsegna > HDC.DataScadenzaPrevista )
  133.     AND S.MatricolaS = VHC.MatricolaS
  134.     AND VHC.CodHW = HWMAX.CodHW
  135.     AND VHC.Valutazione = HWMAX.MaxVal
  136. GROUP BY S.MatricolaS, S.Cognome, S.Corso_di_Laurea HAVING COUNT(*) = (SELECT COUNT(*) FROM HOMEWORK_DA_CONSEGNARE);
  137.  
  138.  
  139. ------------------------------------------------------------------------------------------------------------
  140.  
  141.  
  142. -- ES4)
  143. --1) Nella query nidificata ho calcolato i piatti validi;
  144. --2) Ho eseguito il join tra le tre tabelle PIATTO, PIATTO-ORDINATO e ORDINAZIONE sugli attributi comuni filtrando i piatti per prezzo > 10 euro;
  145. --2) Ho raggruppato le tuple con l operatore GROUP BY sugli attributi CodP, DescrizioneP e Data, imponendo con la clausula HAVING
  146. --   che il gruppo avesse un numero di tavoli distinti (per ogni data) almeno uguale a 10;
  147. --3) Esternamente utilizzo nuove istanze delle stesse tre tabelle integrali facendo nuovamente il join tra di esse,
  148. --   sta volta imponendo che il codice del piatto sia tra quelli validi precedentemente calcolati mediante l operatore IN;
  149. --4) Si effettua nuovamente un GROUP BY esterno identico poichè si è interessati alle informazioni, per ogni piatto valido, per ogni singola giornata;
  150. --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,
  151. --se si visualizzasse la quantità totale e l importo totale direttamente in quel momento, si perderebbero informazioni sui piatti ordinati che sono stati scartati
  152. --assieme ai gruppi scartati. Per tale motivo ho introdotto una query esterna che considera nuovamente i piatti da zero (solo validi) e ne visualizza
  153. --correttamente le informazioni di gruppo per ogni data.
  154.  
  155. SELECT PEXT.CodP, PEXT.DescrizioneP, SUM(POEXT.Quantità) AS QuantitàTot, SUM(POEXT.Quantità * PEXT.Prezzo) AS ImportoTot
  156. FROM PIATTO-ORDINATO POEXT, ORDINAZIONE OEXT, PIATTO PEXT
  157. WHERE PEXT.CodP IN ( SELECT P.CodP
  158.                      FROM PIATTO P, PIATTO-ORDINATO PO, ORDINAZIONE O
  159.                      WHERE P.Prezzo > 10 AND P.CodP = PO.CodP AND PO.NumOrdinazione = O.NumOrdinazione
  160.                      GROUP BY P.CodP, O.DATA
  161.                      HAVING COUNT(DISTINCT O.NumTavolo) >= 10 )
  162.     AND PEXT.CodP = POEXT.CodP
  163.     AND POEXT.NumOrdinazione = OEXT.NumOrdinazione
  164. GROUP BY PEXT.CodP, PEXT.DescrizioneP, OEXT.DATA
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top