/* 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
--42
    SELECT cognome, nome
    FROM Studenti
    WHERE classe LIKE '5%';

.print
--43
    SELECT voto
    FROM Voti
    WHERE codstudente='S001';

.print
--44
    SELECT nome, cognome
    FROM Docenti d, Voti v
    WHERE d.coddocente=v.coddocente
    AND codstudente='S003';

.print
--45
    SELECT cognome, nome, descrizione
    FROM Docenti d, Materie m
    WHERE d.codmateria=m.codmateria;

.print
--46
    SELECT cognome, nome, voto
    FROM Studenti s, Voti v
    WHERE s.codstudente=v.codstudente
    AND v.codmateria='Mat';