Quaderno 2

/* Es 1 */
SELECT NomeD, Ricercatori
FROM (SELECT D.CodD, D.NomeD, COUNT(DISTINCT R.CodR) AS Ricercatori
    FROM RICERCATORE AS R, DIPARTIMENTO AS D
    WHERE R.CodR NOT IN (   SELECT R.CodR
                            FROM RICERCATORE AS R, AZIENDA AS A, CONTRATTO-DI-RICERCA AS CDR
                            WHERE CDR.CodA = A.CodA
                                AND R.CodR = CDR.CodR
                                AND (A.TipoA <> 'Grande azienda' OR CDR.Importo <= 100000)
                            )
        AND D.CodD = R.CodD
    GROUP BY
        D.CodD) AS TAB
;

/* Es 2 */
SELECT T.Settore-Industriale, D.CodD, D.NomeD
FROM (  SELECT TMP1.CodD, TMP1.Settore-Industriale
        FROM (  SELECT R.CodD, A.Settore-Industriale, COUNT(*) AS NumContratti
                FROM RICERCATORE AS R, AZIENDA AS A, CONTRATTO-DI-RICERCA AS CDR
                WHERE A.CodA = CDR.CodA
                    AND R.CodR = CDR.CodR-ResponsabileScientifico
                    AND CDR.DataInizio > '30/06/2015'
                GROUP BY R.CodD, A.Settore-Industriale) AS TMP1,
            (   SELECT TMP.Settore-Industriale, MAX(NumContratti) AS MaxNumContratti
                FROM (  SELECT R.CodD, A.Settore-Industriale, COUNT(*) AS NumContratti
                        FROM RICERCATORE AS R, AZIENDA AS A, CONTRATTO-DI-RICERCA AS CDR
                        WHERE A.CodA = CDR.CodA
                            AND R.CodR = CDR.CodR-ResponsabileScientifico
                            AND CDR.DataInizio > '30/06/2015'
                        GROUP BY R.CodD, A.Settore-Industriale) AS TMP
                GROUP BY TMP.Settore-Industriale) AS TMP2
        WHERE TMP1.Settore-Industriale = TMP2.Settore-Industriale
            AND TMP1.NumContratti = TMP2.MaxNumContratti) AS T, DIPARTIMENTI AS D
WHERE D.CodD = T.CodD
;

/* Es 3 */
SELECT AU.CodAutore, AU.Cognome, AU.Università, COUNT(*) AS TotArticoli
FROM AUTORE AS AU, EDIZIONE_CONFERENZA AS EC, AUTORE_PRESENTA_ARTICOLO AS APA
WHERE APA.CodAutore = AU.CodAutore
    AND APA.Conferenza = EC.Conferenza
    AND APA.Edizione = EC.Edizione
    AND (APA.CodAutore NOT IN ( SELECT AA.CodAutore
                                FROM AUTORI_ARTICOLO AS AA, ARTICOLO AS AR
                                WHERE AA.CodArticolo = AR.CodArticolo
                                    AND AR.Argomento <> 'Data Mining'
                                )
        AND APA.CodAutore IS NOT NULL
        )
GROUP BY AU.CodAutore, AU.Cognome, AU.Università
;
GROUP BY AU.CodAutore, Conferenza, Edizione, Cognome, Università;
Togliere JOIN con Edizione_Conferenza;
Nel NOT IN cambiare AA con APA;

/* Es 4 */
SELECT CA.CodS
FROM CALENDARIO AS CA
WHERE CA.CodD IN (  SELECT CodD
                    FROM COMPETENZE
                    GROUP BY CodD
                    HAVING NumArgo = (  SELECT MAX(NumArgo)
                                        FROM (  SELECT CodD, COUNT(*) AS NumArgo
                                            FROM COMPETENZE
                                            GROUP BY CodD) AS Tab2)
                )
;

/* Es 5 */
SELECT T2.MatrDoc, T3.CodCorso
FROM (  SELECT MatrDoc, CodCorso, MAX(MediaStudenti) AS MaxMediaPresenze
        FROM (  SELECT C.MatrDoc, L.CodCorso, AVG(NumStudentiPresenti) AS MediaPresenze
                FROM LEZIONE AS L, CORSO AS C
                WHERE C.CodCorso = L.CodCorso
                    AND C.MatrDoc NOT IN (  SELECT MatrDoc
                                            FROM CORSO
                                            WHERE Area <> 'basi di dati'
                                            )
                    AND C.MatrDoc IS NOT NULL
                GROUP BY C.MatrDoc, L.CodCorso) AS T1
        GROUP BY MatrDoc) AS T2,
    (   SELECT CodCorso, AVG(NumStudentiPresenti) AS MediaPresenze
        FROM LEZIONE
        GROUP BY CodCorso) AS T3
WHERE T3.MediaPresenze = T2.MaxMediaPresenze
    AND T3.CodCorso = T2.CodCorso
;

/* Es 6 */
SELECT MatricolaS, Cognome, Corso_di_Laurea
FROM STUDENTE
WHERE MatricolaS NOT IN (   SELECT V.MatricolaS
                            FROM VALUTAZIONE_HOMEWORK_CONSEGNATI AS V, HOMEWORK_DA_CONSEGNARE AS H
                            WHERE V.CodHW = H.CodHW
                                AND V.DataConsegna > H.DataScadenzaPrevista
                            )
    AND MatricolaS IN ( SELECT MatricolaS
                            FROM VALUTAZIONE_HOMEWORK_CONSEGNATI
                            WHERE (CodHW, Valutazione) IN ( SELECT CodHW, MAX(Valutazione)
                                                                FROM VALUTAZIONE_HOMEWORK_CONSEGNATI
                                                                GROUP BY CodHW)
                            GROUP BY MatricolaS
                            HAVING COUNT(*) = ( SELECT COUNT(*)
                                                FROM HOMEWORK_DA_CONSEGNARE
                                                )
                        )
;