1

Basi di Dati

SStructuredtructured Q Queryuery L Languageanguage

AppuntiAppunti

Matteo Longhi

2

Introduzione

• Standard creato nel 1976 da IBM

• Aggiornato (versione 2) nel 1992 (ANSI X3.135 e ISO 9075)

• Consente di:– DDL: definire la struttura del DB– DML: modificare i dati contenuti nel DB

3

DDL: Data Definition LanguageCREATE TABLE Personale(

matricola char(5), cognome char(30),

nome char(20),codFiscale char(16) not null,dataAssunzione date,filiale smallint,Funzione char(15),livello smallint,stipBase float,via char(25),cap char(5),citta char(20),prov char(2)

);

Esempio:

4

Tipi di dato

• CHARACTER(n)

CHAR(n) Stringa di lunghezza n• DATE Data nella forma mm/gg/aa• TIME Ora nel formato hh:mm• INTEGER Numero intero da -2147483648 a -21474836487

• SMALLINT Numero da -32768 a 32767• REAL• FLOAT

5

Modifica dei DatiPer inserire un nuovo record:

INSERT INTO Personale(matricola,cognome,nome,…,prov)VALUES(‘AB541’,’Rossi’,’Mario’,…,’CO’);

Per aggiornare record esistenti:UPDATE PersonaleSET livello = 6WHERE livello=5;

Per cancellare record esistenti:DELETE FROM PersonaleWHERE stipBase<750;

6

Query

Le principali operazioni di query (interrogazione) sono:

Proiezione: visualizzare solo le colonne (campi) specificati

Selezione: visualizzare solo le righe (record) che soddisfano una certa condizione

Congiunzione (Join): unire più tabelle

7

Simple Query

Sintassi generale:

SELECT [DISTINCT] { <campo-expr> [AS <Alias>] }FROM { <tabella> [AS <Alias>] }[WHERE <Predicato>]

Legenda:[…] Opzionale{…} Almeno uno, separati dalla virgola

8

Simple Query: esempiTabelle:

PERSONALE (matricola, cognome, nome, codFiscale, dataAssunzione, filiale, funzione, livello, stipBase, via, cap, citta, prov)

DIPENDENZA (codFiliale, descrizione, indirizzo)

Query - cognome, nome e codice fiscale di tutto il personale:

SELECT cognome, nome, codFiscaleFROM Personale

Query - cognome, nome e codice fiscale degli impiegati:

SELECT cognome, nome, codFiscaleFROM PersonaleWHERE funzione=‘impiegato’

9

Simple Query: DISTINCT

DISTINCT: elimina le righe duplicate nella tabella risultante.

Query – elenco delle professioni presenti in azienda:

SELECT DISTINCT funzione

FROM Personale

Senza l’uso il distinct:

SELECT funzione

FROM Personale

funzione

Impiegato

Dirigente

Segretario

…funzione

Impiegato

Impiegato

Impiegato

Dirigente

Segretario

Segretario

…

10

Simple Query: * e AS

Il simbolo * sostituisce l’elenco di tutti i campi.

Query – tutti i dati dei dipendenti che abitano in provincia di milano:

SELECT *FROM PersonaleWHERE prov=‘MI’

La clausola AS permette di modificare l’intestazione di una colonna.

Query – l’elenco delle province da cui provengono i dipendenti:

SELECT DISTINCT prov AS ProvinciaFROM Personale

11

Query : congiunzione (JOIN)

Query – cognome, nome e indirizzo della filiale in cui lavora ogni dipendente

SELECT Personale.cognome, Personale.nome, Dipendenza.indirizzo

FROM Personale, Dipendenza

WHERE Personale.filiale=Dipendenza.codFiliale

E’ necessario specificare la corrispondenza tra la chiave primaria di una tabella e la chiave esterna dell’altra (condizione di join).

12

Query : JOIN (2)

Query – cognome, nome e indirizzo della filiale in cui lavora ogni dipendente

SELECT P.cognome, P.nome, D.indirizzo

FROM Personale AS P, Dipendenza AS D

WHERE P.filiale=D.codFiliale

13

Query : JOIN (3)

Nel caso sia necessario effettuare la congiunzione di tre tabelle:

SELECT *

FROM Tab1, Tab2, Tab3

WHERE Tab1.chiave=Tab2.chiaveExt AND Tab2.chiave=Tab3.chiaveExt

14

Query : Funzioni di Aggregazione

Funzione Valore restituito

COUNT numero di righe

SUM somma

AVG media

MIN valore minimo

MAX valore massimo

Il risultato è UN SOLO valore!

15

Query : Funzioni di Aggregazione (2)

Query – Numero di impiegati

SELECT COUNT(*)FROM PersonaleWHERE funzione=‘impiegato’

Query – Numero di province di provenienza degli impiegati

SELECT COUNT( DISTINCT(prov) )FROM PersonaleWHERE funzione=‘impiegato’

16

Query : Funzioni di Aggregazione (3)

Query – La somma degli stipendi che appartengono al livello 3

SELECT SUM(StipBase)FROM PersonaleWHERE livello=3

Query – Lo stipendio medio degli impiegati

SELECT AVG(StipBase)FROM PersonaleWHERE funzione=‘impiegato’

Query – Lo stipendio minimo e massimo

SELECT MIN(StipBase), MAX(StipBase)FROM Personale

17

Query : Condizioni di ricerca (1)

BETWEEN: controlla se un valore è compreso in un intervallo, estremi compresi

Query – Cognome e nome dei dipendenti assunti nel 2002

SELECT Cognome, NomeFROM PersonaleWHERE dataAss BETWEEN 01/01/2001 AND 31/12/2001

(NOT) IN: controlla se un valore appartiene ad un insiemeQuery – Cognome e nome dei dipendenti che risiedono nelle province

di Milano, Como, Lecco e Bergamo.

SELECT Cognome, NomeFROM PersonaleWHERE prov IN (‘MI’, ‘CO’, ‘LC’, ‘BG’)

18

Query : Condizioni di ricerca (2)

(NOT) LIKE: confronta un valore usando caratteri jollyQuery – I cognomi dei dipendenti che iniziano per “Ros”.

SELECT CognomeFROM PersonaleWHERE Cognome LIKE ‘Ros%’

IS (NOT) NULL: controlla se un campo non è stato compilatoQuery – Cognome e nome dei dipendenti di cui non si conosce la

provincia di residenza.

SELECT Cognome, NomeFROM PersonaleWHERE prov IS NULL

19

Query : Ordinamenti

ORDER BY ASC|DESC: ordina i risultati in senso crescente (ASC, di default) o decrescente (DESC)

Query – I cognomi dei dipendenti in ordine crescente

SELECT CognomeFROM PersonaleORDER BY Cognome

Query – I cognomi degli impiegati in ordine di decrescente di stipendio e, a parità di stipendio, in ordine di cognome crescente

SELECT Cognome, stipBaseFROM PersonaleWHERE funzione=‘impiegato’ORDER BY stipBase DESC, Cognome

20

Query : Raggruppamenti

GROUP BY: raggruppa le righe aventi lo stesso valore nelle colonne indicate.

le funzioni di aggregazione restituiscono un valore per ogni raggruppamento

Query – Il numero di impiegati in ciascuna filiale.

SELECT filiale, COUNT(*) AS NumeroDipendentiFROM PersonaleWHERE funzione=‘impiegato’GROUP BY filiale

21

Query : Raggruppamenti (2)

HAVING: aggiunge una condizione al GROUP BY. Si possono usare le funzioni di aggregazione (anche in

questo caso riferite ad ogni singolo raggruppamento).

Query – Lo stipendio medio degli impiegati nelle filiali con più di 10 dipendenti.

SELECT filiale, AVG(stipBase) AS StipendioMedioFROM PersonaleWHERE funzione=‘impiegato’GROUP BY filialeHAVING COUNT(*)>10

22

Query nidificate

Nella condizione (WHERE) è possibile confrontare un valore con il risultato di un’altra query.

Query – Cognome e nome dei dipendenti con stipendio inferiore alla media

SELECT cognome, nomeFROM PersonaleWHERE stipBase < ( SELECT AVG(stipBase)

FROM Personale )

NB: il confronto “<“ è possibile perché la seconda query, grazie alla funzione di aggregazione, restituisce UN SOLO risultato.

23

Query nidificate (2)

Query – Cognome, nome e indirizzo della filiale in cui lavorano i dipendenti per i quali lo stipendio è superiore alla media degli stipendi degli impiegati.

SELECT P.cognome, P.nome, D.indirizzoFROM Personale AS P, Dipendenza AS DWHERE P.filiale=D.codFiliale AND P.stipBase > ( SELECT AVG(stipBase)

FROM Personale WHERE funzione=‘impiegato’

)

24

Query nidificate: ANY

ANY: la condizione è vera se il confronto è vero per almeno uno dei valori restituiti dalla seconda query.

Query – Dipendenti che non sono impiegati e che hanno lo stipendio superiore a quello di un qualsiasi impiegato.

SELECT cognome, nome, funzioneFROM PersonaleWHERE funzione <>’impiegato’ AND stipBase > ANY ( SELECT stipBase

FROM Personale WHERE funzione=‘impiegato’

)

25

Query nidificate: ALL

ALL: la condizione è vera se il confronto è vero per ciascuno dei valori restituiti dalla seconda query.

Query – Dipendenti che non sono impiegati e che hanno lo stipendio superiore a quello di tutti gli impiegati.

SELECT cognome, nome, funzioneFROM PersonaleWHERE funzione <>’impiegato’ AND stipBase > ALL ( SELECT stipBase

FROM Personale WHERE funzione=‘impiegato’

)

26

Query nidificate: IN

(NOT) IN: la condizione è vera se il valore è compreso tra i valori restituiti dalla seconda query

Query – Cognome e nome dei dipendenti che lavorano in filiali con più di 10 dipendenti.

SELECT cognome, nomeFROM PersonaleWHERE filiale IN ( SELECT filiale

FROM Personale GROUP BY Filiale

HAVING count(*)>10 )

27

Query nidificate: EXIST

(NOT) EXIST: la condizione è vera se la seconda query restituisce almeno un risultato

Query – Elenco dei dipendenti solo se non esistono dipendenti di sesto livello.

SELECT cognome, nomeFROM PersonaleWHERE NOT EXIST ( SELECT *

FROM Personale WHERE livello=6

)