Linguaggio SQL: fondamenti D B M G

(1)

DB M G

Linguaggio SQL: fondamenti

(2)

DB M G

Operatori insiemistici

Operatore UNION

Operatore INTERSECT Operatore EXCEPT

(3)

DB M G

IMPIEGATO (Matricola, Cognome, Nome, CodDip, Stipendio) DIPARTIMENTO (Codice, NomeDip, Città)

SELECT CodDip FROM IMPIEGATO

WHERE Stipendio > 45.000 EXCEPT

SELECT Codice

FROM DIPARTIMENTO WHERE Città = ‘Torino’;

SELECT Codice

EXCEPT

WHERE Stipendio > 45.000;

SELECT Codice

INTERSECT SELECT CodDip FROM IMPIEGATO

WHERE Stipendio > 45.000;

WHERE Stipendio > 45.000

AND CodDip NOT IN (SELECT Codice

FROM DIPARTIMENTO WHERE Città = ‘Torino’);

WHERE Stipendio > 45.000 AND CodDip = (SELECT CodDip

FROM DIPARTIMENTO WHERE Città <> ‘Torino’);

(4)

DB M G

Operatori insiemistici

(5)

DB M G

Operatore UNION

Operatore insiemistico di unione

Esegue l’unione delle due espressioni relazionali A e B

le espressioni relazionali A e B possono essere generate da istruzioni SELECT

richiede la compatibilità di schema tra A e B rimozione dei duplicati

UNION rimuove i duplicati

UNION ALL non rimuove i duplicati

A UNION B

(6)

DB M G

UNION: esempio

Trovare il codice dei prodotti di colore rosso o forniti dal fornitore F2 (o entrambe le cose)

(7)

DB M G

UNION: esempio

CodP NomeP Colore Taglia Magazzino P1 Maglia Rosso 40 Torino

P2 Jeans Verde 48 Milano

P3 Camicia Blu 48 Roma

P4 Camicia Blu 44 Torino

P5 Gonna Blu 40 Milano

P6 Bermuda Rosso 42 Torino

P FP

CodF CodP Qta

F1 P1 300

F1 P2 200

F1 P3 400

F1 P4 200

F1 P5 100

F1 P6 100

F2 P1 300

F2 P2 400

F3 P2 200

F4 P3 200

F4 P4 300

F4 P5 400

(8)

DB M G

UNION: esempio

Trovare

il codice dei prodotti di colore rosso

o forniti dal fornitore F2 (o entrambe le cose)

SELECT CodP FROM P

WHERE Colore='Rosso'

(9)

DB M G

UNION: esempio

SELECT CodP FROM P

WHERE Colore='Rosso'

P

CodP P1 P6

Trovare

il codice dei prodotti di colore rosso

o forniti dal fornitore F2 (o entrambe le cose)

(10)

DB M G

UNION: esempio

SELECT CodP FROM FP

WHERE CodF='F2'

Trovare il

codice dei prodotti

di colore rosso o

forniti dal fornitore F2

(o entrambe le cose)

(11)

DB M G

Trovare il

codice dei prodotti

di colore rosso o

forniti dal fornitore F2

(o entrambe le cose)

UNION: esempio

SELECT CodP FROM FP

WHERE CodF='F2'

FP

CodF CodP Qta

F1 P1 300

F1 P2 200

F1 P3 400

F1 P4 200

F1 P5 100

F1 P6 100

F2 P1 300

F2 P2 400

F3 P2 200

F4 P3 200

F4 P4 300

F4 P5 400

CodP P1 P2

(12)

DB M G

UNION: esempio

SELECT CodP FROM P

WHERE Colore='Rosso' UNION

SELECT CodP FROM FP

WHERE CodF='F2';

(13)

DB M G

UNION: esempio

SELECT CodP FROM P

SELECT CodP FROM FP

WHERE CodF='F2';

R

CodP P1 P2 CodP P6

P1 P2 CodP

P1 P6

(14)

DB M G

UNION: esempio

SELECT CodP FROM P

SELECT CodP FROM FP

WHERE CodF='F2';

R

CodP P1 P2

Rimozione P6

del duplicato

(15)

DB M G

UNION: esempio

SELECT CodP FROM P

SELECT CodP FROM FP

WHERE CodF='F2';

Compatibilità di schema

(16)

DB M G

UNION ALL: esempio

SELECT CodP FROM P

WHERE Colore='Rosso' UNION ALL

SELECT CodP FROM FP

WHERE CodF='F2';

(17)

DB M G

UNION ALL: esempio

SELECT CodP FROM P

WHERE Colore='Rosso' UNION ALL

SELECT CodP FROM FP

WHERE CodF='F2';

R

CodP P1 P1 P2 CodP P6

P1 P2 CodP

P1 P6

(18)

DB M G

Operatori insiemistici

(19)

DB M G

Operatore insiemistico di intersezione

Esegue l’intersezione delle due espressioni relazionali A e B

le espressioni relazionali A e B possono essere generate da istruzioni SELECT

richiede la compatibilità di schema tra A e B

Operatore INTERSECT

A INTERSECT B

(20)

DB M G

INTERSECT: esempio

P

CodF NomeF NSoci Sede

F1 Andrea 2 Torino

F2 Luca 1 Milano

F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia

F

Trovare le città che sono sia sede di fornitori, sia magazzino di prodotti

(21)

DB M G

INTERSECT: esempio

SELECT Sede FROM F

Sede Torino Milano Milano Torino Venezia CodF NomeF NSoci Sede

F1 Andrea 2 Torino

F2 Luca 1 Milano

F

(22)

DB M G

INTERSECT: esempio

SELECT Magazzino FROM P

P

Magazzino Torino Milano

Roma Torino Milano

Torino

Trovare

le città che sono

sia sede di fornitori, sia

magazzino di prodotti

(23)

DB M G

INTERSECT: esempio

SELECT Sede FROM F

INTERSECT

SELECT Magazzino FROM P;

(24)

DB M G

INTERSECT: esempio

SELECT Sede FROM F

INTERSECT

Torino Milano

R

Sede Torino Milano Milano Torino Venezia

Roma Torino Milano

Torino

(25)

DB M G

Equivalenza con altri operatori

L’operazione di intersezione può essere eseguita anche mediante

il join

l’operatore IN

(26)

DB M G

Equivalenza con il join

La clausola FROM contiene le relazioni interessate dall’intersezione

La clausola WHERE contiene condizioni di join tra gli attributi presenti nella clausola SELECT delle espressioni relazionali A e B

(27)

DB M G

Equivalenza con il join: esempio

(28)

DB M G

Equivalenza con il join: esempio

SELECT Sede FROM F, P ...

(29)

DB M G

Equivalenza con il join: esempio

SELECT Sede FROM F, P

WHERE F.Sede=P.Magazzino;

(30)

DB M G

Equivalenza con l’operatore IN

Una delle due espressioni relazionali diviene

un’interrogazione nidificata mediante l’operatore IN

Gli attributi nella clausola SELECT esterna, uniti eventualmente da un costruttore di tupla,

costituiscono la parte sinistra dell’operatore IN

(31)

DB M G

Equivalenza con IN: esempio

(32)

DB M G

Equivalenza con IN: esempio

WHERE Magazzino IN (SELECT Sede FROM F);

(33)

DB M G

Operatori insiemistici

(34)

DB M G

Operatore EXCEPT

A EXCEPT B

Operatore insiemistico di differenza

Sottrae l’espressione relazionale B all’espressione relazionale A

richiede la compatibilità di schema tra A e B

(35)

DB M G

EXCEPT: esempio

P

CodF NomeF NSoci Sede

F1 Andrea 2 Torino

F2 Luca 1 Milano

F

Trovare le città che sono sede di fornitori, ma non magazzino di prodotti

(36)

DB M G

EXCEPT: esempio

SELECT Sede FROM F

Sede Torino Milano Milano Torino Venezia CodF NomeF NSoci Sede

F1 Andrea 2 Torino

F2 Luca 1 Milano

F

Trovare

le città che sono sede di fornitori

, ma non magazzino di prodotti

(37)

DB M G

EXCEPT: esempio

P

Roma Torino Milano

Torino

Trovare

le città che sono

sede di fornitori, ma non

magazzino di prodotti

(38)

DB M G

EXCEPT: esempio

SELECT Sede FROM F

EXCEPT

(39)

DB M G

EXCEPT: esempio

SELECT Sede FROM F

EXCEPT

SELECT Magazzino

FROM P; ^Venezia

R

Sede Torino Milano Milano Torino Venezia

Roma Torino Milano

Torino

(40)

DB M G

Equivalenza con l’operatore NOT IN

L’operazione di differenza può essere eseguita anche mediante l’operatore NOT IN

l’espressione relazionale B è nidificata all’interno dell’operatore NOT IN

gli attributi nella clausola SELECT dell’espressione relazionale A, uniti eventualmente da un

costruttore di tupla, costituiscono la parte sinistra dell’operatore NOT IN

(41)

DB M G

Equivalenza con l’operatore NOT IN: esempio Trovare le città che sono sede di fornitori, ma non magazzino di prodotti

(42)

DB M G

Equivalenza con l’operatore NOT IN: esempio

SELECT Sede FROM F

WHERE Sede NOT IN (SELECT Magazzino FROM P);

(43)

DB M G

Linguaggio SQL: fondamenti

(44)

DB M G

⁴⁴

Istruzioni di aggiornamento

Introduzione

Istruzione INSERT Istruzione DELETE Istruzione UPDATE

(45)

DB M G

⁴⁵

Istruzioni di aggiornamento (1/3)

Inserimento di tuple Cancellazione di tuple Modifica di tuple

(46)

DB M G

⁴⁶

Istruzioni di aggiornamento (2/3)

INSERT

inserimento di nuove tuple in una tabella DELETE

cancellazione di tuple da una tabella UPDATE

modifica del contenuto di tuple in una tabella

(47)

DB M G

⁴⁷

Istruzioni di aggiornamento (3/3)

Le operazioni di aggiornamento modificano lo stato della base di dati

è necessario verificare che siano rispettati i vincoli di integrità

(48)

DB M G

⁴⁸

Istruzioni di aggiornamento (3/3)

Le operazioni di aggiornamento modificano lo stato della base di dati

è necessario verificare che siano rispettati i vincoli di integrità

Ogni istruzione può aggiornare il contenuto di una sola tabella

(49)

DB M G

⁴⁹

Istruzione INSERT

Inserimento di una sola tupla

assegnazione di un valore costante ad ogni attributo

(50)

DB M G

⁵⁰

Istruzione INSERT

Inserimento di una sola tupla

assegnazione di un valore costante ad ogni attributo

Inserimento di più tuple

lette da altre tabelle mediante un’istruzione SELECT

(51)

DB M G

⁵¹

Inserimento di una tupla

INSERT INTO

NomeTabella [(ElencoColonne)]

VALUES

(ElencoCostanti);

(52)

DB M G

⁵²

Inserimento di una tupla: esempio (n.1)

Inserire il prodotto P7 con nome: maglia, colore:

viola, taglia: 40, magazzino: Venezia

(53)

DB M G

⁵³

Inserimento di una tupla: esempio (n.1)

INSERT INTO P (CodP, NomeP, Colore, Taglia, Magazzino) VALUES ('P7', 'Maglia', 'Viola',40,'Venezia');

(54)

DB M G

⁵⁴

È inserita nella tabella P una nuova tupla con i valori specificati

Inserimento di una tupla: esempio (n.1)

(55)

DB M G

⁵⁵

Omettere la lista dei campi equivale a specificare tutti i campi secondo l'ordine di creazione delle colonne nella tabella

Inserimento di una tupla: esempio (n.1)

(56)

DB M G

⁵⁶

Omettere la lista dei campi equivale a specificare tutti i campi secondo l'ordine di creazione delle colonne nella tabella

Se la tabella cambia schema, l’istruzione non è più applicabile

Inserimento di una tupla: esempio (n.1)

(57)

DB M G

⁵⁷

Inserimento di una tupla: esempio (n.2)

Inserire il prodotto P8 con magazzino: Genova, taglia: 42

(58)

DB M G

⁵⁸

Inserimento di una tupla: esempio (n.2)

INSERT INTO P (CodP, Magazzino, Taglia) VALUES ('P8', 'Genova', 42);

(59)

DB M G

⁵⁹

a NomeP e Colore è assegnato il valore NULL

Inserimento di una tupla: esempio (n.2)

(60)

DB M G

⁶⁰

Inserire il prodotto P8 con magazzino : Genova, taglia: 42

a NomeP e Colore è assegnato il valore NULL

Per tutti gli attributi il cui valore non è specificato, il dominio dell’attributo deve consentire il valore NULL

Inserimento di una tupla: esempio (n.2)

(61)

DB M G

⁶¹

Inserimento: integrità referenziale

INSERT INTO FP (CodF, CodP, Qta) VALUES ('F20', 'P20', 1000);

Inserire una nuova fornitura relativa al fornitore F20, prodotto P20 e quantità 1000

(62)

DB M G

⁶²

Vincolo di integrità referenziale

è necessario che P20 e F20 siano già presenti rispettivamente in P e F

Inserimento: integrità referenziale

(63)

DB M G

⁶³

Vincolo di integrità referenziale

è necessario che P20 e F20 siano già presenti rispettivamente in P e F

se il vincolo non è soddisfatto, l’inserimento non deve essere eseguito

Inserimento: integrità referenziale

(64)

DB M G

⁶⁴

Inserimento di più record

INSERT INTO

NomeTabella [(ElencoColonne)]

Interrogazione;

Sono inserite in

NomeTabella

tutte le tuple selezionate dall’interrogazione

Interrogazione Interrogazione

è un’istruzione SELECT arbitraria

non può contenere la clausola ORDER BY

(65)

DB M G

⁶⁵

Inserimento di più record: esempio

FORNITURE-TOTALI (CodP, TotQta) Per ogni prodotto, inserire nella tabella

FORNITURE-TOTALI la quantità totale fornita

(66)

DB M G

⁶⁶

Per ogni prodotto, inserire nella tabella

FORNITURE-TOTALI la quantità totale fornita dati aggregati estratti dalla tabella FP

Inserimento di più record: esempio

SELECT CodP, SUM(Qta) FROM FP

GROUP BY CodP

FORNITURE-TOTALI (CodP, TotQta)

(67)

DB M G

⁶⁷

Inserimento di più record: esempio

INSERT INTO FORNITURE-TOTALI (CodP, TotQta) (SELECT CodP, SUM(Qta)

FROM FP

GROUP BY CodP);

FORNITURE-TOTALI (CodP, TotQta) Per ogni prodotto, inserire nella tabella

FORNITURE-TOTALI la quantità totale fornita

(68)

DB M G

⁶⁸

Istruzione DELETE

DELETE FROM

NomeTabella [

WHERE

predicato];

Cancellazione dalla tabella

NomeTabella

di tutte le tuple che soddisfano il predicato

(69)

DB M G

⁶⁹

Istruzione DELETE

DELETE FROM

NomeTabella [

WHERE

predicato];

Cancellazione dalla tabella

NomeTabella

di tutte le tuple che soddisfano il predicato

Occorre sempre verificare che la cancellazione non causi la violazione di vincoli di integrità referenziale

(70)

DB M G

⁷⁰

Cancellare tutte le forniture

Istruzione DELETE: esempio (n.1)

DELETE FROM FP;

(71)

DB M G

⁷¹

Cancellare tutte le forniture

Senza clausola WHERE tutte le tuple soddisfano il predicato di selezione

elimina il contenuto della tabella FP la tabella non è eliminata

Istruzione DELETE: esempio (n.1)

DELETE FROM FP;

(72)

DB M G

⁷²

Istruzione DELETE: esempio (n.2)

Cancellare la tupla corrispondente al fornitore con codice F1

(73)

DB M G

⁷³

Istruzione DELETE: esempio (n.2)

DELETE FROM F WHERE CodF='F1';

(74)

DB M G

⁷⁴

Istruzione DELETE: esempio (n.2)

Se in FP esistono forniture che fanno riferimento ai fornitori cancellati, la base di dati perde la

propria integrità

avviene una violazione del vincolo di integrità referenziale tra FP e F

occorre propagare la cancellazione

(75)

DB M G

⁷⁵

Istruzione DELETE: esempio (n.2)

DELETE FROM FP WHERE CodF='F1';

(76)

DB M G

⁷⁶

Istruzione DELETE: esempio (n.2)

DELETE FROM FP WHERE CodF='F1';

Per mantenere la coerenza è necessario

completare le operazioni su entrambe le tabelle

(77)

DB M G

⁷⁷

Cancellare i fornitori di Milano

Se in FP esistono forniture che fanno riferimento ai fornitori cancellati, è violato il vincolo di

integrità referenziale tra FP e F

occorre cancellare anche tali forniture in FP

Istruzione DELETE: esempio complesso

DELETE FROM F

WHERE Sede='Milano';

(78)

DB M G

⁷⁸

DELETE FROM F

DELETE FROM FP

WHERE CodF IN (SELECT CodF FROM F

WHERE Sede='Milano');

Istruzione DELETE: esempio complesso

(79)

DB M G

⁷⁹

DELETE FROM F

DELETE FROM FP

In quale ordine devono essere eseguite le due operazioni di cancellazione?

Istruzione DELETE: esempio complesso

(80)

DB M G

⁸⁰

DELETE FROM F

WHERE Sede=‘Milano';

DELETE FROM FP

WHERE Sede=‘Milano');

Istruzione DELETE: esempio complesso

Ordine corretto di esecuzione

(81)

DB M G

⁸¹

Istruzione UPDATE

UPDATE

NomeTabella

SET

colonna = espressione {, colonna=espressione}

[

WHERE

predicato];

Tutti i record della tabella

NomeTabella

che soddisfano il predicato sono modificati in base alle assegnazioni

colonna=espressione

nella clausola SET

(82)

DB M G

⁸²

Aggiornamento di una tupla

Aggiornare le caratteristiche del prodotto P1:

assegnare giallo al colore, incrementare la taglia di 2 e assegnare NULL a magazzino

(83)

DB M G

⁸³

Aggiornamento di una tupla

UPDATE P

SET Colore = 'Giallo', Taglia=Taglia+2, Magazzino = NULL WHERE CodP='P1';

Aggiornare le caratteristiche del prodotto P1:

(84)

DB M G

⁸⁴

Aggiornamento di una tupla

UPDATE P

SET Colore = 'Giallo', Taglia=Taglia+2, Magazzino = NULL WHERE CodP='P1';

È aggiornata la tupla individuata dal codice P1 Aggiornare le caratteristiche del prodotto P1:

(85)

DB M G

⁸⁵

Aggiornamento multiplo

Aggiornare il numero dei soci al doppio del valore per tutti i fornitori di Milano

(86)

DB M G

⁸⁶

Aggiornamento multiplo

UPDATE F

SET NSoci=2*NSoci WHERE Sede='Milano';

(87)

DB M G

⁸⁷

Aggiornamento multiplo

UPDATE F

SET NSoci=2*NSoci WHERE Sede='Milano';

Sono aggiornate tutte le tuple individuate dal predicato nella clausola WHERE

(88)

DB M G

⁸⁸

Aggiornamento con sottointerrogazione

Aggiornare a 10 la quantità fornita per tutti i fornitori di Milano

(89)

DB M G

⁸⁹

Aggiornamento con sottointerrogazione

Aggiornare a 10 la quantità fornita per tutti i fornitori di Milano

UPDATE FP SET Qta = 10

(90)

DB M G

⁹⁰

Aggiornamento di più tabelle

Modificare con il valore F9 il codice del fornitore F2

UPDATE F

SET CodF='F9'

WHERE CodF='F2';

Se in FP esistono forniture che fanno riferimento ai codici dei fornitori aggiornati, è violato il vincolo di integrità referenziale

occorre aggiornare anche tali forniture in FP

(91)

DB M G

⁹¹

Aggiornamento di più tabelle

UPDATE F

SET CodF='F9'

WHERE CodF='F2';

UPDATE FP SET CodF='F9'

WHERE CodF='F2';

(92)

DB M G

⁹²

Aggiornamento di più tabelle

UPDATE F

SET CodF='F9'

WHERE CodF='F2';

UPDATE FP SET CodF='F9'

WHERE CodF='F2';

Per mantenere la coerenza è necessario

completare l'aggiornamento di entrambe le tabelle