Lezione 12-11-09 Sintassi sql

Sintassi sql
Creazione tabella
Il codice sql oramai in larga parte è generato dal sistema ma in informatica molto
spesso vale la regola dell’ 80/20 cioè l’80% del lavoro si fa con il 20% di sforzo mentre il
20% del lavoro si fa con l’80% di sforzo. Questo succede molto spesso con l’sql, infatti
tramite il sistema automatico delle QBE si parte con codice base facilmente generato il
quale poi deve essere adattato tramite piccoli cambiamenti alle proprie esigenze, ciò che il
generatore automatico di codice non riesce a fare. Una funzionalità dell’sql è quella di DDL
( data definition language ), che ha un costrutto simile a quest’esempio:
CREATE TABLE DEPARTMENT(
DNAME
VARCHAR(10)
NOT NULL,
DNUMBER
INTEGER
NOT NULL,
MGRSSN
CHAR(9),
MGRSTARTDATE
CHAR(9),);

CREATE TABLE DEPARTMENT(); -> Questo costrutto permette di creare una
tabella con il nome DEPARTMENT, all’interno delle parentesi tonde vanno elencati
tutti gli attributi della tabella.

VARCHAR() -> tipo di dato definito da sql: il dato è rappresentato da un vettore di
caratteri di dimensione variabile indicata tra parentesi ( in questo caso il DNAME
sarà rappresentato da un vettore di al massimo 10 caratteri). Un VARCHAR si usa
per stringhe di dimensioni maggiori rispetto ai CHAR e a seconda del sistema
utilizzato il VARCHAR può avere dimensioni massime diverse: ad esempio in
ORACLE ha dimensione massima di 32000 caratteri, in SQL Server di 64000
caratteri mentre in Access è stato inglobato in CHAR.
Il VARCHAR è usato per far fronte al problema di allocare spazio inutilmente che molto
probabilmente non sarà usato. Comunque è più oneroso rispetto al CHAR,ciò è bene
saperlo in quanto rallentamenti in un database possono essere dovuti alla presenza di
molti VARCHAR.

INTEGER -> tipo di dato definito da sql: il dato è rappresentato da un numero
intero che può essere a 16, 32 o 64 bit.

CHAR() -> tipo di dato definito da sql: il dato è rappresentato da una stringa di
lunghezza fissa indicata tra parentesi( in questo caso
MGRSSN e
MGRSTARTDATE saranno rappresentati da una stringa di 9 caratteri ).
Di solito le stringhe possono avere una dimensione massima di 255 caratteri.

NOT NULL -> condizione sull’attributo( vincolo in line):l’attributo non può assumere
il valore NULL altrimenti verrà visualizzato un errore. Vi sono due metodi per la
gestione degli errori: a priori e a posteriori.
La correzione degli errori a priori è vantaggiosa rispetto alla correzione a posteriori.
La dimensione massima del VARCHAR varia anche a seconda della codifica dei
caratteri utilizzata. Di solito quando si installano i database è possibile definire alcuni
parametri che si chiamano PARAMETRI DI LOCALIZZAZIONE ( LOCALE ).Questi
parametri vengono chiesti al momento dell’installazione perché ci possono essere
database installati in Italia che servono solo l’Italia e usano una codifica a 8 bit (UTF-8),
mentre ci possono essere database che sono installati ad esempio in America e devono
servire tutto il mondo e quindi diverse lingue,in questo caso si ricorre alla localizzazione
che si può dividere in singola e multi.
Il database può immagazzinare stringhe relative a più linguaggi (una stessa stringa
può significare cose diverse)quindi ci si basa su due concetti fondamentali: tipo di dato
utilizzato per racchiudere una stringa e set di caratteri utilizzato per codificare una stringa.
Il set di caratteri può essere singolo per un database( localizzazione singola ) o multiplo
(multilocalizzazione).
Creazione tabella (Continuazione)
CREATE TABLE DEPARTMENT(
DNAME
VARCHAR(10)
NOT NULL,
DNUMBER
INTEGER
NOT NULL,
MGRSSN
CHAR(9),
MGRSTARTDATE
CHAR(9),
PRIMARY KEY(DNUMBER) ,
UNIQUE (DNAME) ,
FOREIGN KEY(MGRSSN) REFERENCES EMPLOYEE(SSN)) ;

PRIMARY KEY() -> l’attributo tra parentesi è chiave primaria.

UNIQUE() -> il sistema verifica ad ogni inserimento di un nuovo record,riguardante
l’attributo tra parentesi, che non vi siano altri record, già esistenti, uguali a quello
inserito.
Se vi sono duplicati viene restituito un codice di errore.

FOREIGN KEY()REFERENCES -> il valore tra parentesi è l’equivalente di uno
che si trova all’interno della tabella indicata da REFERENCES.

DROP TABLE NOME_TABELLA -> serve per eliminare una tabella.
L’attributo OLE o BLOB ha dimensione fino a 2 gb e può contenere vari file tra cui foto.
Come il varchar il suo utilizzo appesantisce il database quindi ultimamente si preferisce
avere i file in un disco e nel database avere solo i pathname per richiamarli.
Come le CREATE TABLE, esistono le ALTER TABLE che hanno questo costrutto:
ALTER TABLE NOME_TABELLA ADD NOME_COLONNA TIPO_COLONNA
Ad esempio:
ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12)
L’attributo JOB avrà come valore di default NULL anche se si può impostare un altro
valore di default predefinito.
CREATE TABLE DEPARTMENT(
------------------------------------------------------FOREIGN KEY(MGRSSN) REFERENCES EMPLOYEE(SSN)
ONDELETE SETDEFAULT ON UPDATE CASCADE );
Vincoli più complessi
Gestione ad eventi nell’sql

On delete setdefault questo vincolo permette in seguito ad una cancellazione che il
valore assunto dall’integrità referenziale sia settato ad un valore di default.
Esempio -> Se ci sono studenti iscritti ad ingegneria ed economia e cancello tutte le
facoltà dal database posso fare 2 scelte: cancello le facoltà e cancello tutti gli studenti
oppure cancello le facoltà e gli studenti restano iscritti ad esempio all’ ateneo che è il
valore di default.

On update cascade: cambiando un valore questo viene cambiato in tutte le sue
ricorrenzze:(esempio) tutti gli studenti vanno a roma, cambio roma con new york e
tutti gli studenti vanno a new york.

DNO INTEGER DEFAULT 1, -> ogni volta che creo una nuova riga dno assume
valore di default =1
In sql2 e sql99
1. Date = yyyy-mm-dd modificabile
2. Time = hh:mm:ss -> Access aggiunge anche i millesimi di secondo
3. Time(i) -> hh-mm-ss + i cifre addizionali che specificano le frazioni di
secondo
4. Time stamp: mette in coda data e tempo
Tempo di sistema richiesto in un dato momento
SELECT
<attribute list> corrisponde ad una project (sceglie le colonne)
FROM
<table list>
corrisponde ad una join (sceglie le tabelle)
WHERE
<condition>;
corrisponde ad una select (sceglie le righe)
Esempio di una classica select:
ordine da seguire
SELECT BDATE, ADDRESS
3
FROM EMPLOYEE
1
WHERE FNAME='John' AND MINIT='B' AND LNAME='Smith';
2
Dalla tabella EMPLOYEE vengono selezionati tutti gli impiegati la cui riga del nome
è John B. Smith e si prendono le colonne BDATE e ADDRESS.
Le query si può dire che devono essere lette quasi al contrario
Esiste la possibilità che a volte vengano selezionati gli attributi senza prendere la
chiave primaria allora si incorre in un numero di righe uguali che si possono risolvere in
due modi:
Select distinct fa si che due righe uguali restino distinte;
Select unique fa si che due righe uguali vengano riunificate in una unica.
RINOMINAZIONE
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
EMPLOYEE
FROM EMPLOYEE AS E, EMPLOYEE AS S
Is
supervisor
WHERE E.SUPERSSN=S.SSN;
Per fare il join sulla stessa tabella di assegnano due soprannomi diversi alla stessa tabella
(in questo caso vengono usati ‘e’ ed ‘s’ per distinguere employee da supervisor.
Questi soprannomi prendono il nome di aliases.
CARATTERI SPECIALI
* -> il carattere asterisco è usato per indicare “TUTTO”
Esempio:
SELECT *
FROM EMPLOYEE
Seleziona tutte le colonne della tabella Employee
Un altro uso dell’asterisco è questo:
lettera* -> cerca tutte le parole che iniziano con ‘lettera’.Esempio:
WHERE last name = b* usato per cercare persone con il cognome che inizia per b
*lettere* -> cerca ricorrenza di quel suffisso nelle parole.Esempio:
WHERE lastname =*ino* usato per cercare persone che hanno il suffisso ‘ino’ nel
cognome
Oltre all’asterisco abbiamo il carattere ‘_’ usato per sostituire una sola lettera. Esempio:
WHERE last name = _b* per cercare persone il cui cognome ha come seconda
lettera la b
Più ‘_’ equivalgono a più lettere.Esempio:
WHERE cod fisc= __ABC* usato per trovare codici fiscali con la 3,4,5 lettera
uguale ad ABC
Se i simboli jolly sono caratteri che fanno parte dei nomi,per riconoscerli vengono usati dei
caratteri di escape varianti in ogni database.
UNION
(SELECT PNAME
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith')
UNION
(SELECT PNAME
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith')

UNION permette di fare l’unione tra due query,l’unica cosa alla quale si deve fare
attenzione è che le due tabelle siano union-compatible ovvero abbiano lo stesso
numero di colonne con lo stesso dominio di definizione.
QUERY NIDIFICATE (NESTED QUERY)
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research' )
La seconda query in questo caso restituisce una tabella, non uno scalare, percio
invece di usare DNO= si scrive DNO IN il risultato della QUERY tra parentesi e usato
come ambito per scegliere i DNO. Ciò che si fa è una Select su 2 tabelle differenti e poi si
uniscono i risultati.(Altro modo per fare una join).Le query nidificate non sono sviluppate
dalle QBE.
CONTAINS
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE ( (SELECT PNO
FROM WORKS_ON
WHERE SSN=ESSN)
CONTAINS
(SELECT PNUMBER
FROM PROJECT
WHERE DNUM=5) );
L’insieme della seconda select deve contenere l’insieme della terza select.
EXISTS e NOT EXISTS
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E
WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE E.SSN=ESSN AND E.SEX=SEX
AND E.FNAME=DEPENDENT_NAME);

EXISTS(QUERY) ritorna un valore vero o falso se l’insieme è pieno o vuoto, se è
pieno si sviluppa la query su EMPLOYEE
Da notare in questo esempio che nella query più interna viene richiamata una variabile
della query più esterna(ciò si può fare anche con il join)

NOT EXISTS è analogo.
Se si vogliono selezionare delle righe il cui valore è NULL si deve usare questo costrutto:
SELECT NOME_COLONNA
FROM NOME_TABELLA
WHERE NOME_RIGA IS NULL
Si usa IS perché si deve fare una eguaglianza tra oggetti mentre l’”=” si usa quando si
deve fare una eguaglianza tra contenuti.
SELECT E.LNAME AS EMPLOYEE_NAME, S.LNAME AS SUPERVISOR_NAME
FROM (EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE AS S
ON E.SUPERSSN=S.SSN);
Per adesso, si è visto che, la condizione di join era espressa nella WHERE invece un altro
modo di scrivere la join è con ON questo costrutto permette di specificare la condizione di
join direttamente nel FROM
FUNZIONE DI AGGREGAZIONE
SELECT MAX (SALARY), MIN (SALARY), AVG (SALARY)
FROM EMPLOYEE;
Questo costrutto permette di selezionare il massimo il minimo e la media del salario degli
impiegati. Questa query avrà come risposta una sola riga con lo stipendio massimo
minimo e medio
SELECT DNO, COUNT (*), AVG (SALARY)
FROM EMPLOYEE
GROUP BY DNO;
In questa query gli impiegati vengono raggruppati per dipartimento ,numero di impiegati e
media dei salari
COUNT è un contatore che in questo caso conta il numero di impiegati.
QUERY COMPLETA
SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT (*) > 2;
HAVING è l’equivalente della WHERE ma viene eseguita dopo la funzione di
aggregazione cosa che la WHERE non potrebbe fare.
Dopo l’HAVING si può aggiungere la clausola ORDER BY per ordinare i risultati in base
ad un determinato criterio.
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Costrutto completo di una query
QUERY DI COMANDO
Inserimento in una tabella
INSERT INTO EMPLOYEE
VALUES ('Richard', 'K', 'Marini', '653298653', '1962-12-30', '98
Oak Forest,Katy,TX', 'M', 37000, '987654321', 4);
In questo modo è possibile inserire un record in una tabella(Nell’esempio viene inserito il
valore di VALUES nella tabella EMPLOYEE)
INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL)
SELECT DNAME, COUNT (*)
FROM (DEPARTMENT JOIN EMPLOYEE ON DNUMBER=DNO)
GROUP BY DNAME;
Con questo costrutto si possono inserire dati da un’altra tabella,in questo esempio viene
creata una tabella e viene riempita con i risultati della query.
CANCELLAZIONE DA UNA TABELLA
DELETE FROM EMPLOYEE
WHERE LNAME='Brown';
Con questo costrutto si eliminano record da una tabella.
Caione Cristian 10043090
Bozzardi Aurora 10042734