1

Il linguaggio SQL

Massimo Paolucci

(paolucci@dist.unige.it)

DIST – Università di Genova

2

SQL: Structured Query Language

Generalita’

Linguaggio di interrogazione per le basi di dati.

Sviluppato presso i laboratori dell’IBM a meta’ degli ’70.

Adottato dall’ANSI (American National Standards Institute) come linguaggio standard per i Data Base Management System (DBMS) nel 1986.

3

SQL: Structured Query Language

Generalita’

SQL è un linguaggio “relazionalmente completo”

SQL permette di definire, manipolare ed interrogare dati

relazionali (unifica i linguaggi DDL, DML e QL)

SQL è un linguaggio non procedurale

SQL è costituito da un insieme di comandi relativamente ridotto

4

C Language Program

File System

SW- O.S.

HW

Query SQL

File System

SW- O.S.

Relational Data BaseManagement System (RDBMS)

File(s)File(s)

HW

5

Caratteristiche di SQL

• Linguaggio non procedurale: si specifica su quali dati si vuoleoperare, non come fare ad operare

• Esegue operazioni su insiemi di righe e non su una riga per volta

• Contiene primitive per:

– Data Definition Language (DDL)

creare, distruggere strutture dati

– Data Manipulation Language (DML)

inserire, cancellare, modificare dati

– Query Language (QL)

eseguire ricerche (interrogazioni) sui dati (selezioni, proiezioni, unioni)

• E’ un linguaggio “mapping oriented”una query è la richiesta di una relazione ottenuta definendocome combinare le relazioni disponibili per ottenere la risposta desiderata

6

Un esempio

Due tabelle usate dall’amministrazione di un’azienda

La relazione PERSONALE descrive le persone che lavorano neidipartimenti dell’azienda

PERSONALE MATR NOME MANSIONE MGR DATA_ASS SALARIO DIPNO 7782 ROSSI MANAGER 7839 10/3/90 2450 10 7839 VERDI PRESIDENTE 1/1/80 5000 10 7934 BIANCHI IMPIEGATO 7782 31/1/94 1300 10 7499 VIOLA VENDITORE 7698 15/6/93 1600 30 7521 BRUNI VENDITORE 7698 7/7/95 1250 30 7654 GRIGI VENDITORE 7698 5/9/95 1250 30 7698 GIALLI MANAGER 7839 12/10/89 2850 30 7844 NERI VENDITORE 7698 28/3/94 1500 30 7566 SCURI MANAGER 7839 20/4/92 3000 20 7369 CHIARI IMPIEGATO 7566 6/9/96 1000 20 7902 BIONDI ANALISTA 7566 12/2/95 1900 20

7

Un esempio

DIPARTIMENTI DIPNO DIPNOME CITTA'

10 CONTABILITA' GENOVA 20 RICERCA BOLOGNA 30 VENDITE MILANO

La relazione DIPARTIMENTI descrive i dipartimenti.

8

DDL: Create Table

CREATE [TEMPORARY] TABLE nomeTabella(nomeAttributo dominio [(size)] [valoreDiDefault] [vincoli]{, nomeAttributo dominio [(size)] [valoreDiDefault] [vincoli] }[altri vincoli])

CREATE DOMAIN nomeDominio AS tipoDiDato[valoreDiDefault] [vincoli]

DEFAULT <valoreGenerico | USER | NULL>

9

Tipi di Dato Elementari

SQL fornisce un insieme di tipi di dato (eventualmenteespandibile) per la definizione dei dati nelle colonne:

Carattere o testo

Bit (anche stringa di bit)

Tipi numerici (integer, smallint, real, float…)

Data (tempo)

Sui tipi e’ possibile definire un valore di default.

10

Tipi di Dato Elementari

CHARACTER [VARYING] [(Lunghezza)][CHARACTER SET NomeFamigliaCaratteri]

BIT [VARYING] [(Lunghezza)]

NUMERIC [(Precisione [, Scala])], DECIMAL [(Precisione [, Scala])]INTEGER, SMALLINT

FLOAT [(Precisione)], REAL, DOUBLE PRECISION

DATE, TIME [(Precisione)], TIMESTAMP [(Precisione)]

11Data type Storage size Description

BINARY 1 byte per character Any type of data may be stored in a field of this type. No translation of the data (for example, to text) is made. How the data is input in a binary field dictates how it will appear as output.

BIT 1 byte Yes and No values and fields that contain only one of two values.

TINYINT 1 byte An integer value between 0 and 255.

MONEY 8 bytes A scaled integer between – 922,337,203,685,477.5808 and 922,337,203,685,477.5807.

DATETIME(See DOUBLE)

8 bytes A date or time value between the years 100 and 9999.

UNIQUEIDENTIFIER 128 bits A unique identification number used with remote procedure calls.

REAL 4 bytes A single-precision floating-point value with a range of – 3.402823E38 to –1.401298E-45 for negative values, 1.401298E-45 to 3.402823E38 for positive values, and 0.

12Data type Storage size Description

FLOAT 8 bytes A double-precision floating-point value with a range of – 1.79769313486232E308to – 4.94065645841247E-324 for

negative values, 4.94065645841247E-324 to 1.79769313486232E308 for positive values, and 0.

SMALLINT 2 bytes A short integer between – 32,768 and 32,767. (See Notes)

INTEGER 4 bytes A long integer between – 2,147,483,648 and 2,147,483,647. (See Notes)

DECIMAL 17 bytes An exact numeric data type that holds values from 1028 - 1 through - 1028 - 1. You can define both precision (1 - 28) and scale (0 - defined precision). The default precision and scale are 18 and 0, respectively.

TEXT 2 bytes per character Zero to a maximum of 2.14 gigabytes.

IMAGE As required Zero to a maximum of 2.14 gigabytes. Used for OLE objects.

CHARACTER 2 bytes per character Zero to 255 characters.

13

Esempio: Create Table senza vincoli

Esempio: creare la tabella PERSONALE

CREATE TABLE personale( matr int,dipno smallint,nome char(20),mansione char(20),mgr int,data_ass date,salario numeric);

14

Esempio: Create Table senza vincoli

Esempio: creare la tabella DIPARTIMENTI

CREATE TABLE dipartimenti(dipno smallint,dipnome char(20),citta char(20));

15

Constraint: Vincoli nella Create Table

Single-field constraint:

CONSTRAINT name

{<PRIMARY KEY | UNIQUE | NOT NULL |>

REFERENCES foreigntable

[(foreignfield1, foreignfield2)]

[ON <DELETE | UPDATE>

<CASCADE | SET NULL | SET DEFAULT>]}

I piu’ diffusi sono i vincoli di integrita’ referenziale (vincoli di riferimento). Per la loro definizione SQL mette a disposizione ilvincolo di foreign key (chiave esterna).

16

Constraint: Vincoli nella Create Table

NOT NULL (indica che il valore nullo non e’ ammesso).

UNIQUE (indica che il valore corrispondente sia unico, cioe’ righe

differenti non possono possedere gli stessi valori).

PRIMARY KEY (chiave primaria - puo’ essere specificato una sola

volta per ogni tabella. La definizione di PK non ammette il valore

nullo!).

17

Constraint: Vincoli nella Create Table

Multiple-field constraint:

CONSTRAINT nome {

PRIMARY KEY (primary1[, primary2 [, ...]]) |

UNIQUE (unique1[, unique2 [, ...]]) |

NOT NULL (notnull1[, notnull2 [, ...]]) |

FOREIGN KEY [NO INDEX] (ref1[, ref2 [, ...]])

REFERENCES foreigntable

[(foreignfield1 [, foreignfield2 [, ...]])]

[ON UPDATE CASCADE | SET NULL]

[ON DELETE CASCADE | SET NULL]}

18

Esempio: Create Table con vincoli

Esempio: creare la tabella DIPARTIMENTI

CREATE TABLE dipartimenti(dipno smallint constraint pk1 primary key,dipnome char(20),citta char(20));

19

Esempio: Create Table con vincoli

Esempio: creare la tabella PERSONALE

CREATE TABLE personale( matr int constraint pluto primary key,dipno smallintconstraint fk1 foreign key (dipno)

references dipartimenti (dipno),nome char(20),mansione char(20),mgr int constraint fk2 references personale (matr),data_ass date,salario numeric);

20

DDL: Drop Table

DROP TABLE tabella [RESTRICT | CASCADE];

Esempio: distruggere la tabella PERSONALE

DROP TABLE personale

21

DDL: Alter Table

ALTER TABLE table {

ADD { COLUMN defAttributo |

CONSTRAINT defVincolo] } |

ALTER { COLUMN nomeAttributo

< SET DEFAULT default | DROP DEFAULT>} |

DROP { COLUMN nomeAttributo |

CONSTRAINT nomeVincolo }

}

22

DDL: Alter Table

Esempio: alterare la struttura della tabella PERSONALE

ALTER TABLE personale drop constraint fk1

ALTER TABLE personale add column stato_civile char(20)

ALTER TABLE personale drop column stato_civile

Esempio: alterare la struttura della tabella DIPARTIMENTI

ALTER TABLE dipartimenti alter column dipno char(20) notnull

23DML: Modificare il contenuto delletabelle

SQL fornisce dei comandi per la modifica dei dati nelle tabelle:

inserimento o cancellazione di righe, cancellazione di una tabella, cancellazione di una colonna.

Multiple-record append query:

INSERT INTO target [(field1[, field2[, ...]])] [IN

externaldatabase]

SELECT [source.]field1[, field2[, ...]

FROM tableexpression

24DML: Modificare il contenuto delletabelle

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]VALUES (value1[, value2[, ...])

UPDATE tableSET field = newvalueWHERE criteria

DELETE FROM tableWHERE criteria

25

DML: Esempi

INSERT INTO personaleVALUES (‘7782’, ‘ROSSI’, ‘MANAGER’, ‘7839’, 2450,‘10’)

UPDATE personaleSET salario = salario * 1,2

DELETE FROM personale

26

QL: Select

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1]

[, [table.]field2 [AS alias2] [, ...]]}

FROM tableexpression [, ...] [IN externaldatabase][WHERE... ][GROUP BY... ][HAVING... ][ORDER BY... ]

27

QL: I predicati di Select

SELECT [ ALL | DISTINCT | [TOP n [PERCENT]]]

FROM table

28

QL: La clausola Where di SelectPermette di definire condizioni logiche

• AND congiunzione• OR disgiunzione• NOT negazione• (...) parentesi

attraverso l’uso di operatori quali= uguaglianza>= maggiore o uguale<= minore o uguale<> disuguaglianzaIN inclusione

BETWEEN ... AND ... intervallo numerico/data

LIKE similitudine di testi

29

QL: La clausola Where - Esempi

• Visualizzare il contenuto di una tabella

SELECT * FROM PERSONALE;

• Proiettare campi di una tabella

SELECT DIPNO, MANSIONE FROM PERSONALE;

SELECT DISTINCT DIPNO, MANSIONE FROM PERSONALE; (vera PROJECT)

• Selezionare le righe di una tabella in base ad una condizionesemplice (SELECT)

SELECT * FROM DIPARTIMENTI WHERE DIPNO=‘20’;

SELECT * FROM PERSONALE WHERE MANSIONE=‘VENDITORE’;

30

QL: La clausola Where - Esempi• Selezionare le righe di una tabella in base ad una condizione

complessa (AND)

SELECT * FROM PERSONALE

WHERE MANSIONE=‘VENDITORE’

AND SALARIO >= 1300;

• Selezionare le righe di una tabella in base ad una condizione

complessa (OR)

SELECT * FROM PERSONALE

WHERE MANSIONE=‘VENDITORE’

OR SALARIO >= 1300;

31

QL: La clausola Where - Esempi• Alcuni esempi

SELECT * FROM PERSONALE WHERE DIPNO <>‘20’;

SELECT * FROM PERSONALE WHERE MANSIONE IN (‘VENDITORE’, ‘IMPIEGATO’);

SELECT * FROM PERSONALE WHERE MANSIONE NOT IN (‘VENDITORE’,

‘IMPIEGATO’);

SELECT * FROM PERSONALE WHERE SALARIO BETWEEN 2500 AND 4000;

SELECT NOME, SALARIO FROM PERSONALE WHERE NOME LIKE ‘G%’;

32

QL: La clausola Where - Esempi• Selezionare le righe con un campo per cui non è specificato un

valore (NULL)

SELECT NOME FROM PERSONALE WHERE MGR IS

NULL;

viceversa

SELECT NOME FROM PERSONALE WHERE MGR IS NOT

NULL;

33

QL: La clausola Where - Esempi• Selezionare le righe di una tabella secondo un ordine specificato

(ORDER BY)

SELECT NOME FROM PERSONALE WHERE DIPNO=‘20’

ORDER BY NOME;

SELECT NOME, SALARIO FROM PERSONALE

ORDER BY SALARIO DESC;

SELECT NOME, SALARIO FROM PERSONALE

ORDER BY SALARIO,NOME;

34

QL: La clausola Where - Esempi

• Ridefinire il nome delle colonne con etichette (AS)

SELECT NOME AS DIPENDENTE FROM PERSONALE;

• Calcolare espressioni in un query

SELECT NOME, SALARIO*12 AS REDDITO_ANNUO

FROM PERSONALE ORDER BY NOME;

35

QL: La clausola Where - Esempi

• Alcune funzioni che si applicano su insiemi di record (group function):

COUNT(*) conta il numero di record

MAX(...), MIN(...) trova il massimo, minimo in una colonna

AVG(...) calcola la media dei valori in una colonna

SUM(...) somma i valori di una colonna

SELECT SUM(SALARIO) FROM PERSONALE WHERE DIPNO=‘10’;

36

QL: Le Join • La JOIN senza condizioni: il prodotto cartesiano delle tabelle

SELECT * FROM PERSONALE, DIPARTIMENTI;

NOTA: il nome di un campo è univocamente identificato come

Tabella.campo

• La JOIN naturale

SELECT * FROM PERSONALE, DIPARTIMENTI

WHERE PERSONALE.DIPNO = DIPARTIMENTI.DIPNO;

• Eseguire una SELECT, JOIN e PROJECT relazionale con una solaSELECT SQL

SELECT NOME, DIPNOME FROM PERSONALE, DIPARTIMENTI

WHERE SALARIO>1500

AND PERSONALE.DIPNO = DIPARTIMENTI.DIPNO;

37

QL: Le Join

• Definire etichette (alias) per i nomi delle tabelle

SELECT NOME, DIPNOME FROM PERSONALE P,

DIPARTIMENTI D

WHERE P.DIPNO = D.DIPNO;

• La SELF-JOIN (unire la tabella con se stessa).

Esempio: trovare il nome dei superiori di ciascun dipendente

SELECT A.NOME AS Dipendente, B.NOME AS Superiore

FROM PERSONALE A, PERSONALE B

WHERE A.MGR = B.MATR;

38

QL: Le Join

• La TETA-JOIN: JOIN definita con condizioni generaliEsempio: trovare chi guadagna più di Rossi

SELECT A.NOME, A.SALARIO FROM PERSONALE A,

PERSONALE B

WHERE A.SALARIO>B.SALARIO

AND B.NOME = ‘ROSSI’;

39

QL: Outer e Inner JoinLe Join effettuano un confronto fra una o piu’ colonne di una

tabella e una o piu’ colonne in una o piu’ tabelle.

I tipi di join sono:• INNER JOIN: vengono selezionate solo le righe del prodotto

cartesiano per cui la condizione risulta vera.• OUTER JOIN: ha il compito di mantenere tutte le righe che fanno

parte di una o di entrambe le tabelle coinvolte (anche quandonon ci sono corrispondenze).

• LEFT: esegue una INNER JOIN esteso con le righe dellarelazione che compare a sinistra nel join per le quali non esisteuna corrispondente riga nella tabella di destra.

• RIGHT: esegue una INNER JOIN restituendo anche le righeescluse della relazione di destra.

• FULL: esegue una INNER JOIN restituendo anche le righeescluse di entrambe le relazioni.

40

Tabella Risultati

Nome Cliente Codice_Cliente Adam Barr 1 Sean Chai 2 Eva Corets 3 Erin O’Melia 4

Codice_Cliente Codice_Prodotto Quantità 1 2 15 1 3 5 4 1 37 3 5 11 4 2 1003

Nome_Cliente Codice_Cliente Quantità Adam Barr 1 15 Adam Barr 1 5 Erin O’Melia 4 37 Eva Corets 3 11 Erin O’Melia 4 1003

Tabella Clienti Tabella Vendite

SELECT nome_cliente, vendite.codice_cliente, quantitàFROM clienti INNER JOIN venditeON clienti.codice_cliente=vendite.codice_cliente

QL: Outer e Inner Join

41

SELECT nome_cliente, vendite.codice_cliente, quantitàFROM clienti LEFT JOIN vendite

ON clienti.codice_cliente = vendite.codice_cliente

Tabella Risultati

Nome Cliente Codice_Cliente Adam Barr 1 Sean Chai 2 Eva Corets 3 Erin O’Melia 4

Codice_Cliente Codice_prodotto Quantità 1 2 15 1 3 5 4 1 37 3 5 11 4 2 1003

Nome_Cliente Codice_Cliente Quantità Adam Barr 1 15 Adam Barr 1 5 Erin O’Melia 4 37 Eva Corets 3 11 Erin O’Melia 4 1003 Sean Chai NULL NULL

Tabella Clienti Tabella Vendite

QL: Outer e Inner Join

42

QL: Outer e Inner Join

• La OUTER-JOIN: unisce anche i record che non hanno

corrispondenze

Esempio: listare il nome di tutti i dipendenti con il nomedell’eventuale superiore

SELECT a.nome AS Dipendente, b.nome AS Capo

FROM personale a

LEFT JOIN personale b ON a.mgr=b.matr;

• LEFT (RIGHT) JOIN: i record della tabella di sinistra (destra) della condizione vengono uniti anche senza un corrispondentenella tabella di destra (sinistra).

FROM table1 [ LEFT | RIGHT ] JOIN table2ON table1.field1 compopr table2.field2

43

QL: Le sub-query

Una sub-query è una query che contiene un’altra query.

Le sub-query si distinguono in:

• sub-query che ritornano un sola riga

esempio: trovare chi guadagna più di Rossi

SELECT NOME FROM PERSONALE WHERE

SALARIO > (SELECT SALARIO FROM PERSONALE

WHERE NOME = ‘ROSSI’);

44

QL: Le sub-query

• sub-query che ritornano più righe

esempio: trovare chi guadagna più di tutti quelli del

dipartimento 20

SELECT NOME FROM PERSONALE WHERE

SALARIO > ALL

(SELECT SALARIO FROM PERSONALE

WHERE DEPNO = ‘20’);

(ALL = tutti gli elementi; ANY = almeno un elemento)

45

QL: Le sub-query • sub-query il cui risultato dipende dalla query principale

esempio: trovare chi guadagna più della media del proprio

dipartimento

SELECT NOME FROM PERSONALE A

WHERE SALARIO >

(SELECT AVG(SALARIO) FROM PERSONALE B

WHERE B.DIPNO = A.DIPNO);

• è possibile inserire più sub-query in AND o OR tra loro

• è possibile inserire sub-query dentro altre sub-query

46

QL: La clausola Group By • La clausola GROUP BY permette l’indicazione di sottinsiemi di

record della tabella su cui possono essere eseguite funzioni di gruppo.

Esempi:

- La somma di tutti gli stipendi

SELECT SUM(SALARIO) FROM PERSONALE;

- La somma degli stipendi per ciascun dipartimento

SELECT DIPNO, SUM(SALARIO)FROM PERSONALE

GROUP BY DIPNO;

- La somma degli stipendi per ciascun dipartimento con più di 4 dipendenti

SELECT DIPNO, SUM(SALARIO) FROM PERSONALE

GROUP BY DIPNO HAVING COUNT(*)>4;

47

QL: La clausola Group By

• la clausola WHERE agisce sui record prima della formazione dei

gruppi

• la clausola HAVING agisce sui record dopo la formazione dei

gruppi

• i gruppi si possono formare anche sulla base di più campi; ad

esempio

SELECT DIPNO, MANSIONE, COUNT(*) FROM PERSONALEGROUP BY DIPNO, MANSIONE;

48

QL: Gli operatori d’insieme • L’operatore UNION permette di unire il risultato ottenuto da

query distinte

• L’operatore INTERSECT permette di intersecare il risultato

ottenuto da query distinte

• L’operatore EXCEPT permette di sottrarre il risultato ottenuto da

query distinte