/* Problema N. 2*/
.header on
.mode column

CREATE TABLE Docenti(
  coddocente CHAR(5) PRIMARY KEY,
  cognome CHAR(20),
  nome CHAR(20),
  codmateria CHAR(5) REFERENCES materie(codmateria));

CREATE TABLE Materie(
  codmateria CHAR(5) PRIMARY KEY,
  descrizione CHAR(20)
);

CREATE TABLE Voti(
  idvoto INTEGER PRIMARY KEY,
  voto REAL NOT NULL,
  datavoto date,
  codmateria CHAR(5) references materie(codmateria),
  codstudente CHAR(5) references studenti(codstudente),
  coddocente CHAR(5) references docenti(coddocente)
);

CREATE TABLE Studenti(
  codstudente CHAR(5) PRIMARY KEY,
  cognome CHAR(20),
  nome CHAR(20),
  classe CHAR(4),
  email CHAR(40),
  cellulare CHAR(15)
);
INSERT INTO Studenti
     (codstudente, cognome, nome, classe, email, cellulare)
   values
    ("S001", "ROSSI", "MARIO", "3AAP", "mario.rossi@gmail.com", "390123456"),
    ("S002", "ARDOVINI", "MARIA VITTORIA", "5AAP", "mariavittoria.ardovini@francescoseveri.org", "390123457"),
    ("S003", "BORREA", "MARTA", "5AAP", "marta.borrea@francescoseveri.org", "390123458"),
    ("S004", "BOTTARO", "FLAVIO", "5AAP", "flavio.bottaro@francescoseveri.org", "0775123456"),
    ("S005", "CHIAPPINI", "MATTEO", "5AAP", "matteo.chiappini@francescoseveri.org", "0775123457"),
    ("S022", "BRACAGLIA", "ELISA", "5BAP", "elisa.bracaglia@francescoseveri.org", "390123451"),
    ("S029", "FERRAGONIO", "AURORA", "5BAP", "aurora.ferragonio@francescoseveri.org", "390123452"),
    ("S030", "FIACCO", "SIMONE", "5BAP", "simone.fiacco@francescoseveri.org", "390123453"),
    ("S031", "GRIGORE", "ALESSIO FIORELLO", "5BAP", "alessiofiorello.grigore@francescoseveri.org", "390123454"),
    ("S032", "LABBIENTO", "MATTEO", "5BAP", "matteo.labbiento@francescoseveri.org", "0775123461"),
    ("S033", "LAURETTI", "PAOLO", "5BAP", "paolo.lauretti@francescoseveri.org", "0775123462"),
    ("S034", "PROFETA", "FRANCESCO", "5BAP", "francesco.profeta@francescoseveri.org", "390123461"),
    ("S052", "MAGI", "SAMUELE", "5CAP", "samuele.magi@francescoseveri.org", "390123462"),
    ("S053", "MANDATORI", "ELISA", "5CAP", "elisa.mandatori@francescoseveri.org", "390123463"),
    ("S054", "PANICCIA", "ANTONIO", "5CAP", "antonio.paniccia@francescoseveri.org", "390123463");

INSERT INTO Studenti
     (codstudente, cognome, nome, classe, email)
   values
    ("S055", "SORGE", "NATALIA", "5CAP", "natalia.sorge@francescoseveri.org" ),
    ("S056", "TOMASI", "MARCO", "5CAP", "marco.tomasi@francescoseveri.org" );

INSERT INTO Materie
    (codmateria, descrizione)
VALUES 
    ('Mat','Matematica'),
    ('Inf','Informatica'),
    ('Ing','Inglese'),
    ('Ita','Italiano');

INSERT INTO Docenti
    (coddocente, cognome, nome, codmateria)
VALUES 
    ('D001','Addesse','Paola','Mat'),
    ('D002','Cappadozzi','Leopoldo','Inf'),
    ('D003','Gramiccia','Carla','Ing');

INSERT INTO Voti
    (idvoto, voto, datavoto, codmateria, codstudente, coddocente)
VALUES 
    (1,3, '2023-10-10','Ing','S001','D003'),
    (2,6, '2023-10-10','Mat','S002','D001'),
    (3,8, '2023-10-11','Mat','S003','D001'),
    (4,4, '2023-11-01','Inf','S001','D002'),
    (5,7, '2023-11-01','Inf','S002','D002'),
    (6,9, '2023-11-02','Inf','S003','D002');


.print '_______________________'

.print
--53
    SELECT voto, classe, Max(voto) AS migliore_mat
    FROM Voti v, Studenti s
    WHERE classe LIKE '3%'
    AND codmateria='Mat'
    AND v.codstudente=s.codstudente
    GROUP BY classe, voto;

.print
--54
    SELECT codstudente, MAX(voto) AS migliore, MIN(voto) AS peggiore
    FROM Voti
    WHERE codstudente='S003'
    GROUP BY codstudente;

.print
--55
    SELECT descrizione, datavoto, voto
    FROM Voti v, Materie m
    WHERE codstudente='S002'
    AND v.codmateria=m.codmateria
    ORDER BY descrizione, datavoto;

.print
--56
    SELECT d.cognome, s.classe, s.cognome, s.nome
    FROM Docenti d, Studenti s, Voti v
    WHERE d.cognome='Cappadozzi'
    AND d.coddocente=v.coddocente
    AND s.codstudente=v.codstudente
    ORDER BY s.classe, s.cognome, s.nome;

.print
--57
    SELECT cognome, nome, COUNT(voto) AS voto_6
    FROM Studenti s, Voti v
    WHERE codmateria='Inf'
    AND voto>=6
    AND s.codstudente=v.codstudente
    GROUP BY cognome, nome
    ORDER BY cognome, nome;

.print
--58
    SELECT cognome, nome, classe, COUNT(voto) AS voto_5
    FROM Studenti s, Voti v
    WHERE classe='5BAP'
    AND voto<5
    AND s.codstudente=v.codstudente
    GROUP BY cognome, nome;