Esercitazione 4 SQL AA 22/23 Politecnico di Milano (MySQL)

Esercitazione4.sql

-- Creazione schema database
CREATE DATABASE E1;

-- Creazione tabelle database
CREATE TABLE Insegnante(
	Matricola INT PRIMARY KEY,
    Nome VARCHAR(64) NOT NULL,
    Cognome VARCHAR(64) NOT NULL,
    Citta VARCHAR(64),
    Stipendio INT NOT NULL
);

CREATE TABLE Corso(
	Codice INT AUTO_INCREMENT PRIMARY KEY,
    Nome VARCHAR(64) NOT NULL,
    Facolta VARCHAR(64) NOT NULL,
    NumeroCrediti INT NOT NULL,
    MatricolaProfessore INT NOT NULL
		REFERENCES Insegnante(Matricola)
        ON UPDATE CASCADE
        ON DELETE NO ACTION,
	NumeroStudenti INT NOT NULL
);

CREATE TABLE Studente(
	Matricola INT PRIMARY KEY,
    Nome VARCHAR(64) NOT NULL,
    Cognome VARCHAR(64) NOT NULL,
    Citta VARCHAR(64)
);

CREATE TABLE Esame(
	MatricolaStudente INT
		REFERENCES Studente(Matricola)
        ON UPDATE CASCADE
        ON DELETE NO ACTION,
	CodiceCorso INT
		REFERENCES Corso(Codice)
        ON UPDATE CASCADE
        ON DELETE NO ACTION,
	Voto INT NOT NULL,
    PRIMARY KEY(MatricolaStudente, CodiceCorso)
);

-- Inserimento informazioni nelle tabelle
INSERT INTO Insegnante VALUES(1, 'Piero', 'Nero', 'Milano', 2500);
INSERT INTO Insegnante VALUES(2, 'Carlo', 'Giallo', NULL, 1500);
INSERT INTO Insegnante VALUES(3, 'Marta', 'Blu', 'Milano', 2500);
INSERT INTO Insegnante VALUES(4, 'Marco', 'Rosa', 'Como', 1500);

INSERT INTO Studente VALUES(100, 'Mario', 'Rosso', 'Milano');
INSERT INTO Studente VALUES(200, 'Luca', 'Bianco', 'Verona');
INSERT INTO Studente VALUES(300, 'Gianni', 'Verde', 'Milano');
INSERT INTO Studente VALUES(400, 'Pino', 'Arancio', 'Como');

INSERT INTO Esame VALUES(100, 1, 33);
INSERT INTO Esame VALUES(100, 2, 30);
INSERT INTO Esame VALUES(200, 1, 28);
INSERT INTO Esame VALUES(300, 2, 20);
INSERT INTO Esame VALUES(300, 3, 25);

INSERT INTO Corso(Nome, Facolta, NumeroCrediti, MatricolaProfessore, NumeroStudenti) VALUES('Analisi 1', 'Ingegneria', 10, 1, 250);
INSERT INTO Corso(Nome, Facolta, NumeroCrediti, MatricolaProfessore, NumeroStudenti) VALUES('Meccanica', 'Ingegneria', 12, 2, 150);
INSERT INTO Corso(Nome, Facolta, NumeroCrediti, MatricolaProfessore, NumeroStudenti) VALUES('Smart Buildings', 'Architettura', 5, 3, 100);
INSERT INTO Corso(Nome, Facolta, NumeroCrediti, MatricolaProfessore, NumeroStudenti) VALUES('Informatica', 'Ingegneria', 10, 3, 200);

-- Query dell'esercizio

-- Trovare tutte le informazioni su tutti i professori
SELECT *
FROM Insegnante;

-- Trovare tutte le informazioni sui professori di Milano
SELECT *
FROM Insegnante
WHERE Citta = 'Milano';

-- Trovare le città in cui vivono i professori
SELECT DISTINCT Citta
FROM Insegnante;

-- Trovare tutte le informazioni sui professori di Milano in ordine alfabetico
SELECT *
FROM Insegnante
WHERE Citta = 'Milano'
ORDER BY Nome ASC, Cognome ASC;

-- Elencare in ordine alfabetico inverso cognome, nome e stipendio dei professori.
-- Lo stipendio dev'essere visualizzato in dollari invece che in euro.

SELECT Cognome, Nome, Stipendio * 1.1 AS StipendioUSD
FROM Insegnante
ORDER BY Cognome DESC, Nome DESC;

-- Elencare cognome, nome e stipendio dei professori che vivono a Milano o a Verona e guadagnano tra i 1000 ed i 2000 euro.
SELECT Cognome, Nome, Stipendio
FROM Insegnante
WHERE (Citta = 'Milano' OR Citta = 'Verona')
	AND (Stipendio BETWEEN 1000 AND 2000);
    
-- Trovare la somma degli stipendi dei professori che guadagnano più di 2000 euro
SELECT SUM(Stipendio)
FROM Insegnante
WHERE Stipendio > 2000;

-- Trovare il numero di città da cui provengono gli insegnanti contenuti nella base di dati
SELECT COUNT(DISTINCT Citta)
FROM Insegnante;

-- Trovare il numero di professori che guadagnano meno di 2000 euro
SELECT COUNT(*)
FROM Insegnante
WHERE Stipendio < 2000;

-- Trovare il massimo stipendio degli insegnanti il cui nome inizia per M e la cui terza lettera è una r
SELECT MAX(Stipendio)
FROM Insegnante
WHERE Nome LIKE 'M_r%';

-- Trovare lo stipendio medio degli insegnanti provenienti da Milano oppure Como.
SELECT AVG(Stipendio)
FROM Insegnante
WHERE Citta = 'Milano' OR Citta = 'Como';

-- Trovare il voto medio e il numero di esami sostenuti da ogni studente
SELECT MatricolaStudente AS Matricola, COUNT(*) AS EsamiSvolti, AVG(Voto) AS Media
FROM Esame
GROUP BY MatricolaStudente;

-- Trovare il voto medio e il numero di esami sostenuti da ogni studente con voto medio superiore a 25
SELECT MatricolaStudente AS Matricola, COUNT(*) AS EsamiSvolti, AVG(Voto) AS Media
FROM Esame
GROUP BY MatricolaStudente
HAVING AVG(Voto) >= 25;

-- Trovare il voto medio e il numero di esami sostenuti da ogni studente con voto medio superiore a 25,
-- non tenendo conto dei 30 e Lode
SELECT MatricolaStudente AS Matricola, COUNT(*) AS EsamiSvolti, AVG(Voto) AS Media
FROM Esame
WHERE Voto <> 33
GROUP BY MatricolaStudente
HAVING AVG(Voto) >= 25;

-- Elencare codice corso, matricola studente e voto degli esami sostenuti dagli studenti di Milano.
SELECT CodiceCorso, MatricolaStudente, Voto
FROM Esame, Studente
WHERE MatricolaStudente = Matricola AND Citta = 'Milano';

-- Trovare le informazioni sugli studenti e sugli esami che hanno sostenuto.
-- Devono essere inclusi nel risultato anche gli studenti che non hanno sostenuto esami.
SELECT *
FROM Studente LEFT JOIN Esame ON Matricola = MatricolaStudente;

-- Calcolare il voto medio degli esami dei corsi da 5 crediti della facoltà ‘Ingegneria’
-- tenuti da professori di nome ‘Piero’ la cui città di residenza non è nota.
SELECT AVG(Voto)
FROM Insegnante, Corso, Esame
WHERE Matricola = MatricolaProfessore
	AND Codice = CodiceCorso
    AND NumeroCrediti = 5
    AND Facolta = 'Ingegneria'
    AND Insegnante.Nome = 'Piero'
    AND Citta IS NULL;
    
-- Trovare matricola, cognome e nome degli studenti di Milano che hanno superato
-- esami per un totale di almeno 20 crediti.
SELECT Matricola, Cognome, Studente.Nome
FROM Studente, Esame, Corso
WHERE Matricola = MatricolaStudente
	AND Codice = CodiceCorso
GROUP BY Matricola, Cognome, Studente.Nome
HAVING SUM(NumeroCrediti >= 20);

-- Trovare la matricola degli studenti che hanno preso almeno un 30 e almeno un 30 e lode,
-- supponendo che il voto “30 e lode” sia memorizzato come 33.
SELECT MatricolaStudente
FROM Esame
WHERE Voto = 30
INTERSECT
SELECT MatricolaStudente
FROM Esame
WHERE Voto = 33;

-- Trovare i cognomi dei professori che non sono anche cognomi di studenti di Milano.
SELECT Insegnante.Cognome
FROM Insegnante
EXCEPT(
	SELECT Insegnante.Cognome
    FROM Insegnante, Studente
    WHERE Insegnante.Cognome = Studente.Cognome
);

-- Trovare i codici e i nomi dei corsi con il massimo numero di crediti.
SELECT Codice, Nome
FROM Corso
WHERE NumeroCrediti = (
	SELECT MAX(Corso.NumeroCrediti)
    FROM Corso
);

-- Trovare i codici e i nomi di tutti i corsi ad eccezione di quelli con il minor numero di crediti.
SELECT Codice, Nome
FROM Corso
WHERE NumeroCrediti <> (
	SELECT MIN(Corso.NumeroCrediti)
    FROM Corso
);

-- Trovare matricola, nome e cognome degli studenti che hanno preso almeno un voto maggiore
-- di 20 in esami con nome del corso che inizia con ‘AN’ e la cui quarta lettera è ‘L’.
SELECT Matricola, Studente.Nome, Cognome
FROM Studente, Esame, Corso
WHERE Codice = CodiceCorso
	AND Matricola = MatricolaStudente
    AND Voto > 20
    AND Corso.Nome LIKE 'An_l%';
    
-- Trovare le informazioni di tutti i professori che hanno degli omonimi.
SELECT *
FROM Insegnante
WHERE EXISTS (
	SELECT *
    FROM Insegnante I
    WHERE Insegnante.Nome = I.Nome
		AND Insegnante.Cognome = I.Cognome
		AND Insegnante.Matricola <> I.Matricola
);

-- Trovare la matricola, il nome e il cognome degli studenti che hanno superato gli esami di tutti i corsi.
SELECT Matricola, Nome, Cognome
FROM Studente, Esame
WHERE MatricolaStudente = Matricola
GROUP BY Matricola, Nome, Cognome
HAVING COUNT(*) = (
	SELECT COUNT(*)
    FROM Corso
);

-- Trovare le matricole degli studenti che hanno preso più 30 e lode che 30.
SELECT MatricolaStudente
FROM Esame
WHERE Voto = 33
GROUP BY MatricolaStudente
HAVING COUNT(*) > (
	SELECT E.MatricolaStudente
    FROM Esame E
    WHERE Voto = 30
		AND MatricolaStudente = E.MatricolaStudente
);