-----------------------------------------------------------------
-----------------------------------------------------------------
--
--	base.sql
--	03-02-2004
--	utilizzo base di comandi sql
--	per provare utilizzare il comando
--	psql -s <nomedb>
--	al prompt dogitare
--	\i esempio.sql
-----------------------------------------------------------------

-----------------------------------------
--	crea la tabella studente
------------------------------------------

CREATE TABLE studente (
	matricola	int,
	cognome		varchar(30),
	nome		varchar(30),
	peso		int,
	altezza		int,
	nascita		date
);

------------------------------------------
-- 	inserisce una riga nella tabella
------------------------------------------

-- 1. 	Inseriamo una lista di valori nello stesso ordine 
--	specificato durante la creazione

INSERT INTO studente
	VALUES (100, 'Porcelli', 'Lorenzo',97,192,'18-01-1957');

-- 2.	Inseriamo una lista di valori specificando la colonna interessata.
--	Le colonne possono essere indicate in qualsiasi ordine

INSERT INTO studente (nome, cognome, peso,altezza,matricola,nascita)
	VALUES	('Giorgio','Alberti',80,178,200,'23-05-1951');

-- 3.	Possono mancare delle colonne perchè il dato corrispondente è sconosciuto
--

INSERT INTO studente (nome, cognome, altezza,matricola,nascita)
	VALUES	('Francesco','Alberti',178,300,'23-05-1951');

----------------------------------------
-- 	Inseriamo alcuni studenti ....

INSERT INTO studente
	VALUES (500, 'Manzoni', 'Lorenzo',75,172,'28-05-1977');

INSERT INTO studente
	VALUES (600, 'Manzini', 'Luciano',78,179,'25-07-1979');

INSERT INTO studente
	VALUES (500, 'Giacometti', 'Alessio',65,168,'8-08-1979');

INSERT INTO studente
	VALUES (500, 'Zanetti', 'Matteo',75,179,'8-10-1977');

-----------------------------------------
--	Query 
----------------------------------------

--	Tutti gli elementi ..

SELECT * FROM studente;

--	un semplice calcolo per l'indice di massa corporea
-- 	specificando una nuova colonna

SELECT cognome, nome, (peso*10000 / (altezza*altezza) ) AS massa_corporea FROM studente;

--	elenco dei maggiorenni

SELECT cognome, nome FROM studente
        WHERE EXTRACT(YEAR FROM AGE(nascita)) >= 18;

--	elenco  degli studenti in ordine decrescente per altezza

SELECT cognome, nome FROM studente
        ORDER BY altezza DESC;

--	elenco degli studenti in ordine di altezza decrescente ed alfabetico

SELECT cognome, nome FROM studente
        ORDER BY altezza DESC, cognome, nome;

-- 	per ogni anno di nascita, la media delle altezze ordinato per anno di nascita

SELECT EXTRACT (year FROM nascita) AS annoNascita, AVG(altezza) AS altezzaMedia  FROM studente
        GROUP BY EXTRACT (year FROM nascita)
	ORDER BY annoNascita;

-------------------------------------------------------------------------
-- 	query con controllo effettuato attraverso ESPRESSIONI REGOLARI
--	le possibilità sono veramente tante!!
--	vedere il manuale e provare
--------------------------------------------------------------------------

-- 1.	ricerca tutti gli studenti che hanno il cognome che inizia con 'A'
--
	
SELECT cognome, nome FROM studente
        WHERE cognome ~ '^A'
        ORDER BY cognome, nome;

-- 2.	ricerca tutti gli studenti che hanno il cognome che inizia con 'A' o 'a'
--
	
SELECT cognome, nome FROM studente
        WHERE cognome ~* '^A'
        ORDER BY cognome, nome;


-- 3.	ricerca tutti gli studenti che hanno il cognome che contiene una  'A' o 'a'
--
	
SELECT cognome, nome FROM studente
        WHERE cognome ~* 'A'
        ORDER BY cognome, nome;

-- 4.	ricerca tutti gli studenti che hanno il cognome che inizia per vocale maiuscola
--
	
SELECT cognome, nome FROM studente
        WHERE cognome ~ '^[AEIUO]'
        ORDER BY cognome, nome;

----------------------------------------------------------------------------------
--	I confronti si possono effettuare anche con l'operatore LIKE
--	anche in questo caso vedere il manuale per valutare tutte le possibilità
-----------------------------------------------------------------------------------

--	Ricerca tutti i cognomi che iniziano per P

SELECT cognome, nome FROM studente
        WHERE cognome LIKE 'P%'
        ORDER BY cognome, nome;

----------------------------------------------------------------------------------
--	Modifichiamo la struttura della tabella 
--	Inseriamo un nuovo attributo
--	Aggiorniamo i dati con valori per il nuovo attributo
---------------------------------------------------------------------------------

--	Aggiungiamo la colonna classe

ALTER TABLE studente
	ADD COLUMN classe VARCHAR(8);

-- 	Aggiorniamo tutti gli studenti inserendo 5bi nella classe

UPDATE studente

	SET classe = '5bi';

---------------------------------------------------------------------------------------------------
--	Aggiungiamo nuovi dati con il comando COPY
--	Procedere nel seguente modo:
-- 1.	Creare con il vi un file testo contenente i dati che si vogliono inserire, 
--	nell'ordine previsto nella creazione della tabella.
--	Ogni attributo DEVE essere separato da un carattere (ad esempio ;)
--	Ogni linea deve contenere i dati relativi ad una linea della tabella
--
-- 2.	Procedere all'aggiornamento con il comando sql COPY.
--	Se si è amministratore scrivere così
--		COPY studente
--			FROM 'nome assoluto del file'
--			DELIMITER ';';
--
--	Se si è utente normale il comando COPY può fare riferimento solo allo stdin
--	Allora da shell Unix
--		cat nomeDelFile | psql -c "COPY studente FROM stdin DELIMITER ';';" nomeDataBase
--
--
---------------------------------------------------------------------------------------------------

--	Per ogni classe ricercare altezza minima, massima, media

SELECT  classe, MIN(altezza) AS altezzaMinima,
        MAX(altezza) AS altezzaMassima,
        AVG(altezza) AS altezzaMedia    FROM studente
        GROUP BY classe;

--	Ricercare il più alto della scuola

SELECT cognome, nome, classe    FROM studente
        WHERE altezza = (SELECT MAX(altezza) FROM studente);

--	Ricerca il più alto di ogni classe

SELECT s1.cognome, s1.nome, s1.altezza
        FROM studente s1, studente s2
        WHERE s1.classe = s2.classe
        GROUP BY s2.classe, s1.cognome, s1.nome, s1.altezza
        HAVING  s1.altezza =  MAX(s2.altezza)
        ORDER BY s1.cognome, s1.nome ;

--	Ricerca il più alto di ogni classe 
--	utilizzando una query per determinare il valore correlato

SELECT s1.cognome,s1.nome, s1.altezza,s1.classe
        FROM studente s1
        WHERE altezza = (
                                SELECT MAX(s2.altezza)
                                        FROM studente s2
                                        WHERE s1.classe = s2.classe
                        )
        ORDER BY classe, cognome, nome;


---------------------------------------------------------------------------
--	Creaiamo una nuova tabella, classe, in relazione 1:N con studente
--	Classe ha chiave
---------------------------------------------------------------------------

CREATE TABLE classe (
        codice  VARCHAR(8) PRIMARY KEY,
        descrizione     VARCHAR(30),
        aula            int
);

--------------------------------------------------------------------------
-- 	Creiamo una nuova tabella studente, con chiave primaria 
-- 	e chiave esterna classe.
--	La chiave esterna, come realizzata, impedirà di inserire un nuovo 
--	dato senza un riferimento in classe, 
--	e impedirà sia l'aggiornamento, sia la cancellazione di una classe
--	se ha un riferimento in studente
--------------------------------------------------------------------------

CREATE TABLE student (
        matricola       int PRIMARY KEY,
        cognome         varchar(30),
        nome            varchar(30),
        peso            int,
        altezza         int,
        nascita         date,
        classe          VARCHAR(8) REFERENCES classe
);

--	Inseriamo  nuove classi in classe

INSERT INTO classe
	VALUES ('3bi', '3 B informatici', 13);

INSERT INTO classe
	VALUES ('4bi', '4 B informatici', 15);

INSERT INTO classe
	VALUES ('5bi', '5 B informatici', 25);

-----------------------------------------------------------------
-- 	Ricopiamo la tabelle studente in student
----------------------------------------------------------------

INSERT INTO student
        SELECT * FROM studente;

--	La cancellazione della classe 5bi provoca un messaggio di errore

DELETE  FROM classe
        WHERE codice = '5bi';

-----------------------------------------------------------------
--	Creiamo la tabella docente
--	Inseriamo dei dati
--	Creiamo una relazione insegna tra docente e classe
------------------------------------------------------------------

-- 1	Crea tabella docente

CREATE TABLE docente (
        codice          INT PRIMARY KEY,
        cognome         VARCHAR(30),
        nome            VARCHAR(30),
        nascita         DATE
);

-- 2	Inseriamo alcune voci

INSERT INTO docente
	VALUES (100,'Ugo','Antonella','10/10/1960');

INSERT INTO docente
	VALUES (125,'Punta','Stefano','10/10/1965');

INSERT INTO docente
	VALUES (127,'Fossati','Paolo','10/10/1962');

INSERT INTO docente
	VALUES (105,'Grillo','Gloria','10/10/1860');

INSERT INTO docente
	VALUES (115,'Piana','Giovanna','10/10/1765');

-- 3	La relazione tra docenti e classe è m:n
--	Si deve creare la nuova tabella insegna

CREATE TABLE insegna (
        docente INT REFERENCES docente,
        classe  VARCHAR(8) REFERENCES classe,
        materia VARCHAR(20),

        PRIMARY KEY (docente, classe, materia)
);


INSERT INTO insegna
        VALUES (100, '5bi', 'inglese');

INSERT INTO insegna
        VALUES (105, '5bi', 'matematica');

INSERT INTO insegna
        VALUES (125, '5bi', 'lab informatica');

INSERT INTO insegna
        VALUES (125, '5bi', 'lab sistemi');

INSERT INTO insegna
        VALUES (100, '4bi', 'inglese');

INSERT INTO insegna
        VALUES (105, '4bi', 'matematica');

INSERT INTO insegna
        VALUES (125, '4bi', 'lab informatica');

INSERT INTO insegna
        VALUES (125, '4bi', 'lab sistemi');

-------------------------------------------------------------
--	JOIN tra tabelle
------------------------------------------------------------

--	Elenco degli insegnanti di una classe

SELECT DISTINCT d.cognome, d.nome
        FROM docente d, insegna i
        WHERE (i.classe = '5bi') AND (i.docente = d.codice)
        ORDER BY cognome, nome;

--	Elenco degli alunni a cui insegna un determinato insegnante

SELECT DISTINCT s.cognome, s.nome
        FROM docente d, insegna i, student s
        WHERE   (d.cognome = 'Grillo') AND (d.nome = 'Gloria')
                AND (d.codice = i.docente)
                AND (i.classe = s.classe)
        ORDER BY s.cognome, s.nome;