Esercitazione02-SQL
-
Upload
minaandr64 -
Category
Documents
-
view
52 -
download
1
description
Transcript of Esercitazione02-SQL
Andrea [email protected]
© 2007 Andrea Turati
Structured Query Language parte 2
Come fare quel che ci pare con una base di dati relazionale (e vivere felici)
Informatica Applicata per Ingegneria Biomedica - SQL/2 2 © 2007 Andrea Turati
Query di join(1)
Talvolta i dati voluti sono una combinazione di quelli presenti in più tabelle
Esempio :
Se si vuole conoscere nome e cognome dei clienti che hanno fatto qualche ordine e l’importo dei rispettivi ordini, occorre collegare queste due tabelle
Per utilizzare come origine dei dati due o più tabelle possiamo utilizzareil prodotto cartesiano di queste: l’operatore è la virgola (“,”) da utilizzare nella clausola FROM.
clienti
ordini
N
1
Clienti
Cod_fisc Nome Cognome
Ordini
Cliente Importo Quantità Cod_prod
Informatica Applicata per Ingegneria Biomedica - SQL/2 3 © 2007 Andrea Turati
Query di join(2)
Il prodotto cartesiano è anche detto JOIN tra tabelle. Il risultato del prodotto cartesiano tra due tabelle è una tabella con tutte le possibili combinazioni dei record delle due:
Tab 2
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3
Tab 1
D E
D1 E1
D2 E2
x
D E A B C
D1 E1 A1 B1 C1
D1 E1 A2 B2 C2
D1 E1 A3 B3 C3
D2 E2 A1 B1 C1
D2 E2 A2 B2 C2
D2 E2 A3 B3 C3
=
Attraverso il select ... from ... where è possibile:- effettuare una proiezione su una parte dei campi- indicare una condizione che deve essere verificata da tutte le tuple
select *
from Tab1, Tab2;
≡
select E,B,C from Tab1, Tab2 where (??) ;
Informatica Applicata per Ingegneria Biomedica - SQL/2 4 © 2007 Andrea Turati
Query di join – es.(1)
select Clienti.*, Ordini.Cod_fisc,
Ordini.Importofrom Clienti, Ordini
where Clienti.Cod_fisc = Ordini.Cod_fisc;
Clienti
Cod_fisc Nome Cognome
cod1 Marco Pace
cod3 Sara Gelo
cod2 Luca Neri
Ordini
Cod_fisc Importo Cod_Prod Quantità
cod1 200 A1 2
cod2 10 B54 4
cod2 100 S32 5
Nome Cognome Cod_fisc Cod_fisc Importo
Marco Pace cod1 cod1 200
Luca Neri cod2 cod2 10
Luca Neri cod2 cod2 100
Individuare i clienti e l’importo dei relativi ordini
ordiniclienti
Informatica Applicata per Ingegneria Biomedica - SQL/2 5 © 2007 Andrea Turati
Query di join(3)
Il prodotto cartesiano può essere realizzato anche attraverso il comando JOINIl comando JOIN permette anche di specificare direttamente nella clausola FROM le condizioni di selezione delle righe della tabella ‘prodotto’
Tab 1
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3
Tab 2
D E C
D1 E1 C1
D2 E2 C3
D3 E3 C4
D4 E4 C6
JOIN ON Tab1.C = Tab2.C
A B C D E
A1 B1 C1 D1 E1
A3 B3 C3 D2 E2
Informatica Applicata per Ingegneria Biomedica - SQL/2 6 © 2007 Andrea Turati
Query di join – es.(2)
Azienda
IDAzienda Fatturato Rag Soc
A1 100 SRL
A2 200 SPA
A3 150 SNC
Dipendente
IDDipend. Nome Azienda
D1 Pippo A1
D2 Pluto A3
D3 Paperino A1
D4 Topolino A1
D5 Minni A2
SELECT Nome, Azienda, FatturatoFROM Azienda JOIN Dipendente
ON Azienda.IDAzienda = Dipendente.Azienda;
In una query con JOIN sono coinvolte più tabelle– può capitare che ci siano dei campi omonimi : per non fare confusione si
usa il “.” (dot notation) per specificare a quale tabella appartiene ogni campo
Individuare i dipendenti, le aziende in cui lavorano ed i relativi fatturati
Informatica Applicata per Ingegneria Biomedica - SQL/2 7 © 2007 Andrea Turati
Tipi di join
Il tipo di join che abbiamo considerato è l’INNER JOIN: esso permette di includere nella tabella risultato solo i record che si corrispondono come specificato dalla condizione ON su un campo di ciascuno dei record
Altri tipi di JOIN sono gli OUTER JOIN: essi permettono di includere nella combinazione anche dei record che non hanno record corrispondenti nell’altra tabella secondo la condizione ON
–In particolare:•LEFT OUTER JOIN
Permette di includere nella combinazione tutti i record della prima tabella e quelli della seconda che soddisfano la condizione
•RIGHT OUTER JOINPermette di includere nella combinazione tutti i record della seconda tabella e quelli della prima che corrispondono
Informatica Applicata per Ingegneria Biomedica - SQL/2 8 © 2007 Andrea Turati
Outer join – es.(1)
select Clienti.*, Cod_Prod
from Clienti left outer join Ordini onClienti.Cod_fisc = Ordini.Cod_fisc_cliente;
Cod_fisc Nome Cognome Cod_Prod
cod1 Marco Pace A1
cod2 Luca Neri S32
cod2 Luca Neri B54
cod3 Sara Gelo
Individuare i clienti e gli eventuali prodotti da essi acquistati
Clienti
Cod_fisc Nome Cognome
cod1 Marco Pace
cod3 Sara Gelo
cod2 Luca Neri
Ordini
Cod_fisc_cliente Importo Cod_Prod Quantità
cod1 200 A1 2
cod2 10 B54 4
cod2 30 S32 5
cod4 50 F87 3
Informatica Applicata per Ingegneria Biomedica - SQL/2 9 © 2007 Andrea Turati
Outer join – es.(2)
select Clienti.Nome, Clienti.Cognome, Ordini.Cod_fisc_cliente as Cod_fisc, Ordini.Importo
from Clienti right outer join ordini onClienti.Cod_fisc = Ordini.Cod_fisc_cliente;
Nome Cognome Cod_fisc Importo
Marco Pace cod1 200
Luca Neri cod2 10
Luca Neri cod2 30
cod4 50
Individuare gli ordini e le info sui clienti, se presenti
Clienti
Cod_fisc Nome Cognome
cod1 Marco Pace
cod3 Sara Gelo
cod2 Luca Neri
Ordini
Cod_fisc_cliente Importo Cod_Prod Quantità
cod1 200 A1 2
cod2 10 B54 4
cod2 30 S32 5
cod4 50 F87 3
Informatica Applicata per Ingegneria Biomedica - SQL/2 10 © 2007 Andrea Turati
Esercizio
Aereoporto (Città, Paese, NumeroDiPiste)Volo (FlightID, Giorno, CittàPart, OraPart, CittàArr, OraArr, TipoAereo)
Aereo (TipoAereo, NumeroPasseggeri)
1. Trovare il paese di arrivo del volo AZ274
select Aereoporto.Paesefrom Aereoporto, Volowhere (FlightID = “AZ274”) and (CittàArr = Città);
select Aereoporto.Paesefrom Aereoporto inner join Volo
on CittàArr = Cittàwhere (FlightID = “AZ274”);
Informatica Applicata per Ingegneria Biomedica - SQL/2 11 © 2007 Andrea Turati
Esercizio
Aereoporto (Città, Paese, NumeroDiPiste)Volo (FlightID, Giorno, CittàPart, OraPart, CittàArr, OraArr, TipoAereo)
Aereo (TipoAereo, NumeroPasseggeri)
2. Visualizzare il tipo di aereo e il numero di passeggeri dei voli in partenza da Boston; se non ci sono le informazioni dell’aereo, visualizzarne solo il tipo
select Volo.TipoAereo, Aereo.NumeroPasseggerifrom Volo left join Aereo
on Volo.TipoAereo = Aereo.TipoAereowhere Volo.CittàPart = “Boston”;
Informatica Applicata per Ingegneria Biomedica - SQL/2 12 © 2007 Andrea Turati
Esercizio
CD (CDNumber, Title, Year, Price)Track (CDNumber, PerformanceCode, trackNo)
Recording (Performance, SongTitle, Year)Composer (CompName, SongTitle)
Singer ( SingerName, PerformanceCode)
Dato il precedente schema scrivere le seguenti query in SQL:1. Le persone che hanno scritto e suonato canzoni il cui titolo inizia per
“D”2. I titoli dei CD che contengono canzoni il cui anno di registrazione non è
conosciuto3. Le tracce dei CD con numero di serie 78574. Fornirle in ordine di numero
indicando il cantante per le tracce per cui è definito.
Informatica Applicata per Ingegneria Biomedica - SQL/2 13 © 2007 Andrea Turati
Soluzione(1)
1.1select SingerNamefrom Singer, Recording, Composerwhere (Singer.PerformanceCode = Recording.Performance) AND
(Recording.SongTitle = Composer.SongTitle) AND(SingerName = CompName) AND (SongTitle like "D*")
1.2select SingerNamefrom (Singer join Recording
on Singer.PerformanceCode = Recording.Performance)join Composer on Recording.SongTitle=Composer.SongTitle
where (SingerName = CompName) AND (SongTitle like "D*")
Informatica Applicata per Ingegneria Biomedica - SQL/2 14 © 2007 Andrea Turati
Soluzione(2)
2.select CD.titlefrom CD join Track on CD.CDNumber = Track.CDNumber
join Recording on Track.PerformanceCode =Recording.Performance
where Recording.Year is null
3.select trackNo, Singer.SingerNamefrom Track left join Singer
on Track.PerformanceCode = Singer.PerformanceCodewhere Track.CDNumber = 78754order by trackNo
Informatica Applicata per Ingegneria Biomedica - SQL/2 15 © 2007 Andrea Turati
Funzioni aggregate(1)
Spesso abbiamo bisogno di informazioni che riassumono il contenuto di sottoinsiemi di record.In tali casi calcolare tali dati a partire dai singoli sarebbe un dispendio di risorseSQL mette a disposizione diverse funzioni per fare ciò in modo semplice, quelle principali sono:
AVG(campox) valore medio dei valori di campox
MAX(campox) massimo valore assunto da campox
MIN(campox) minimo valore assunto da campox
SUM(campox) somma dei valori della colonna ‘campox’
COUNT(campox) numero di righe nella colonna ‘campox’
Informatica Applicata per Ingegneria Biomedica - SQL/2 16 © 2007 Andrea Turati
Funzioni aggregate(2)
A parte “count”, le altre funzioni ammettono come argomento una espressione– sum e avg
• Attributo o campo calcolato (numeri o intervalli di tempo)
– min e max• Attributo o espressione il cui risultato sia ordinabile (anche per le stringhe di
cartatteri)
La presenza di una condizione WHERE nella query naturalmente limita il numero di righe su cui sono calcolate le funzioniPer fare calcoli sui valori distinti si può utilizzare l’operatore distinct
–Es.: avg(DISTINCT campox)
Esistono altre operazioni possibili: dipende dal tipo di DBMS che si sta utilizzando
–Es.: first(campox), last(campox)
Informatica Applicata per Ingegneria Biomedica - SQL/2 17 © 2007 Andrea Turati
Funzioni aggregate - es.(1)
Persone
Nome Compenso_mensile
Impiego Mesi_di_lavoro
Angelo 1.000 ricercatore 3
Marco 1.500 ricercatore 5
Martina 4.000 capo area 6
select count(*) as NumImpiegatifrom Persone;
select sum(Compenso_mensile*Mesi_di_lavoro) as spese_ricfrom Personewhere Impiego = ‘ricercatore’;
Estrarre ilnumero degli impiegati
Estrarre il totaledei compensi per iricercatori
NumImpiegati
3
Spese_ric
10.500
Informatica Applicata per Ingegneria Biomedica - SQL/2 18 © 2007 Andrea Turati
Funzioni aggregate - es.(2)
Persone
Nome Compenso_mensile
Impiego Mesi_di_lavoro
Angelo 3.000 ricercatore 3
Marco 2.500 ricercatore 5
Martina 4.000 capo area 6
select avg(Compenso_mensile) as stipendio_mediofrom Persone;
select max(Compenso_mensile) as tetto_stipendifrom Persone;
Estrarre lo stipendiomedio delle persone
Estrarre lo stipendiomassimo
stipendio_medio
3.160
tetto_stipendi
4.000
Informatica Applicata per Ingegneria Biomedica - SQL/2 19 © 2007 Andrea Turati
Funzioni aggregate - Raggruppamento
select Cognome, Nome, max(stipendio) from Impiegatowhere Dipartimento = NomeDip
and Città = “Milano”;
Cosa fa questa operazione?– Problema: non è detto che dia un solo valore di Cognome, Nome
come risultato
Per questo motivo l’SQL non consente di mescolare fra i campi della SELECT attributi semplici e funzioni aggregate a meno che non si usi la clausola
GROUP BY
Informatica Applicata per Ingegneria Biomedica - SQL/2 20 © 2007 Andrea Turati
Raggruppamento
Il raggruppamento di record consente di dividere le tabelle (originarie o risultati di operazioni di selezione) in gruppi logici distintiL’operatore usato è GROUP BYSui gruppi così creati è possibile poi effettuare operazioni di calcolo applicando le funzioni aggregate
select Campo1, func(Campo2)from Tabellawhere Campo1 [cond]group by Campo1;
il raggruppamento può avvenire su uno o più campise avviene su più campi il risultato sarà una tabella con un numero di righe pari al prodotto dei campi su cui è effettuato il raggruppamento
Nella select posso specificare solo campi presenti nella clausola GROUP BY oppure una funzione sugli altri campi
Informatica Applicata per Ingegneria Biomedica - SQL/2 21 © 2007 Andrea Turati
Raggruppamento di dati – es.
Persone
Nome Compenso_mensile
Impiego Mesi_di_lavoro
Angelo 3.000 ricercatore 3
Marco 2.500 ricercatore 5
Luca 4.200 capo area 10
Martina 4.000 capo area 6
select avg(Compenso_mensile) as Paga_media, Impiegofrom Personegroup by Impiego;
Paga_media Impiego
2.750 ricercatore
4.100 capo area
Estrarre lo stipendio medio di ogni tipologia di impiego
Informatica Applicata per Ingegneria Biomedica - SQL/2 22 © 2007 Andrea Turati
Filtri su raggruppamenti
Per imporre delle condizioni sui valori di certe grandezze caratteristiche di un raggruppamento si può usare il comando HAVING
select avg(Compenso_mensile) as Paga_media, Impiegofrom Personegroup by Impiegohaving avg(Compenso mensile) > 3.000;
Paga_media Impiego
4.100 capo area
Estrarre lo stipendio medio di ogni Tipologia di impiego se superiore a3.000
Informatica Applicata per Ingegneria Biomedica - SQL/2 23 © 2007 Andrea Turati
Esercizio
Aereoporto (Città, Paese, NumeroDiPiste)Volo (FlightID, Giorno, CittàPart, OraPart, CittàArr, OraArr, TipoAereo)
Aereo (TipoAereo, NumeroPasseggeri)
Dato il precedente schema scrivere le seguenti query in SQL:1. Il numero di voli che lasciano Parigi il Giovedì2. Il numero di voli internazionali che lasciano città canadesi3. Le città francesi dalle quali partono più di 20 voli diretti verso la
Germania
Informatica Applicata per Ingegneria Biomedica - SQL/2 24 © 2007 Andrea Turati
Soluzioni1.select count(*)from Volowhere Giorno = “Giovedì” and CittàPart = “Parigi”;
2.select count(*)from Aereporto as A1 join Volo on A1.Città = Volo.CittàPart
join Aeroporto as A2 on Volo.CittàArr = A2.Cittàwhere A1.Paese = “Canada” and A2.Paese <> “Canada”;
3. select A1.cittàfrom Aereporto as A1 join Volo on A1.Città = Volo.CittàPart
join Aeroporto as A2 on Volo.CittàArr = A2.Cittàwhere A1.Paese = “Francia” and A2.Paese = “Germania”group by A1.Cittàhaving count(*) > 20;
Informatica Applicata per Ingegneria Biomedica - SQL/2 25 © 2007 Andrea Turati
EsercizioAereoporto (Città, Paese, NumeroDiPiste)
Volo (FlightID, Giorno, CittàPart, OraPart, CittàArr, OraArr, TipoAereo)Aereo (TipoAereo, NumeroPasseggeri)
Trovare gli aereoporti del Belgio che hanno solo voli domestici a. usando not inb. usando not exists
select CittàPart from Aeroporto join Volo on CittàPart = Cittàwhere Paese = "Belgio" and CittàPart not in
(select CittàPartfrom Aereoporto as A1 join Volo on A1.Città = CittàPart
join Aereoporto as A2 on CittàArr = A2.Cittàwhere A1.Paese = “Belgio” and A2.Paese <> “Belgio”);
select CittàPart from Aeroporto join Volo as A1 on CittàPart = Cittàwhere Paese = "Belgio" and not exists
(select * from Volo join Aereoporto as A2 on A2.Città = CittàArrwhere A1.Città = CittàPart and A2.Paese <> “Belgio” );
Informatica Applicata per Ingegneria Biomedica - SQL/2 26 © 2007 Andrea Turati
Operazioni insiemistiche(1)
UNIONconsente di unire il risultato di più queryINTERSECTconsente di ottenere l’insieme di record che sono comuni ai risultati delle diverse queryEXCEPTconsente di avere come risultato i record che sono nella prima query e non nella seconda
INTERSECT e EXCEPT non aggiungono nulla a quanto visto, mentre la UNION arricchisce il potere espressivo di SQL
Informatica Applicata per Ingegneria Biomedica - SQL/2 27 © 2007 Andrea Turati
Operazioni insiemistiche(2)
Tutti questi operatori possono essere utilizzati solo per query i cui risultati siano compatibili: ovvero abbiano lo stesso numero di campi ed i tipi dei campi corrispondenti (in ordine) siano confrontabili– Se i campi hanno nomi diversi viene usato il nome del primo
operando
Al contrario del resto degli operatori di interrogazione SQL, gli operatori insiemistici eliminano i duplicati– E’ più logico che sia così (ricorda gli insiemi)– Non accresce il costo dell’operazione (comunque è necessario
analizzare tutte le righe)– Se si vogliono i duplicati usare ALL
Informatica Applicata per Ingegneria Biomedica - SQL/2 28 © 2007 Andrea Turati
Operazioni insiemistiche – es.(1)
select Cod_fisc, Data_ric as Data from Paz_Cardiolwhere Data_ric > 30/06/01unionselect Cod_fisc, Data_acc from Accessi_Pronto_Soccwhere Data_acc > 30/06/01;
Paz_Cardiol
Cod_fisc Data_ric By_pass
cod1 10/11/01 N
cod3 02/05/01 S
cod2 23/01/01 N
Accessi_Pronto_Socc
Cod_fisc Tipo_interv Diagnosi Data_acc
cod5 tipo1 diag1 20/10/01
cod9 tipo2 diag2 02/09/01
cod12 tipo3 diag3 10/06/01
cod21 tipo4 diag4 13/03/01
Cod_fisc Data
cod1 10/11/01
cod5 20/10/01
cod9 02/09/01
Estrarre codice e data di ricovero di tutti i pazienticardiologici ricoverati dopo il 30/06 e di tutti i pazienti del PS conaccesso dopo il 30/06
Informatica Applicata per Ingegneria Biomedica - SQL/2 29 © 2007 Andrea Turati
Operazioni insiemistiche – es.(2)
select Cod_fisc from paz_cardiolwhere Data_ric > 30/06/01intersectselect Cod_fisc from accessi_pronto_soccwhere Data_acc > 30/06/01;
ESERCIZIO: fare la stessa query senza INTERSECT
Paz_Cardiol
Cod_fisc Data_ric By_pass
cod1 10/11/01 N
cod3 02/05/01 S
cod2 23/08/01 N
Accessi_Pronto_Socc
Cod_fisc Tipo_interv Diagnosi Data_acc
cod5 tipo1 diag1 20/10/01
cod2 tipo2 diag2 02/09/01
cod12 tipo3 diag3 10/06/01
cod1 tipo4 diag4 13/07/01
Cod_fisc
cod1
cod2
Estrarre codice fiscali di tutti i pazienti cardiologici ricoverati dopo il 30/06 che hanno fatto accesso al PS dopo la stessa data.
Informatica Applicata per Ingegneria Biomedica - SQL/2 30 © 2007 Andrea Turati
Data Definition Language
DROP
DDL DML DCLComandi SQL (Structured Query Language)
CREATE INSERT GRANTUPDATE REVOKEDELETESELECT
DDL
ALTER
Informatica Applicata per Ingegneria Biomedica - SQL/2 31 © 2007 Andrea Turati
SQL come DDL
SQL presenta tutti i costrutti necessari per la definizione e le modifica degli schemi di un DB– Normalmente si affianca alle interfacce proprietarie fornite dai
produttori di DBMS relazionali
Gli oggetti manipolabili con il DDL sono le Tabelle, le Viste, gli Utenti, ecc....
Per le tabelle:– Il comando CREATE TABLE permette di creare tabelle
specificando i nomi ed i tipi delle colonne– Il comando ALTER TABLE consente di aggiungere (attraverso
l’ADD) e, talvolta, cancellare (attraverso il DROP) colonne di una tabella
– Il comando DROP TABLE consente di cancellare tabelle
Informatica Applicata per Ingegneria Biomedica - SQL/2 32 © 2007 Andrea Turati
Creazione di tabelle
create table NomeTabella(
NomeAttributo Dominio [ValoreDiDefault] [Vincoli]{ , NomeAttributo Dominio [ValoreDiDefault] [Vincoli] }AltriVincoli
)
Ogni tabella viene definita con un nome e un elenco di attributi che ne definiscono lo schemaOgni attributo è caratterizzato da un nome, un dominio (tipo) e dei vincoli
Informatica Applicata per Ingegneria Biomedica - SQL/2 33 © 2007 Andrea Turati
Valori di default
Il valore di default consente di specificare per ogni attributo il valore che assume automaticamente all’inserimento di una nuova riga nella tabella
default (GenericoValore | user | null)– GenericoValore valore del dominio– user identificativo dell’utente– null è il valore inserito se non si indica nulla
Esempio:– NumeroFigli integer default 0
Informatica Applicata per Ingegneria Biomedica - SQL/2 34 © 2007 Andrea Turati
Vincoli intrarelazionali
Not null– Indica che non è ammesso un valore nullo per un certo attributo
Cognome char(20) not nullUnique– Si specifica per un attributo o un insieme di attributi e impone che i valori
siano una superchiaveNome char(20) not null,Cognome char(20) not null,unique(Nome,Cognome)
Chiave primaria– E’ possibile impostare il vincolo di chiave primaria una sola volta per
tabella• E’ invece possibile usare più volte unique e not null
– Il vincolo può essere definito su un attributo o su un insieme di attributiNome char(20),Cognome char(20),primary key (Cognome,Nome)
Vincoli di integrità generici– check(condizione)
Stipendio integer(9) check(Stipendio > 0)
Informatica Applicata per Ingegneria Biomedica - SQL/2 35 © 2007 Andrea Turati
Vincoli interrelazionali (1)
E’ possibile definire vincoli di integrità referenziale– Crea un legame fra i valori di un attributo della tabella corrente
(interna) e i valori di un attributo di un’altra tabella (esterna)– Impone che per ogni riga della tabella interna il valore, se diverso
da null, sia presente nei valori dell’attributo della tabella esterna– L’attributo della tabella esterna deve essere unique (tipicamente
è chiave primaria)– Il tutto può essere imposto anche su un insieme di attributi
Si usano i costrutti references e foreign key
Informatica Applicata per Ingegneria Biomedica - SQL/2
Vincoli interrelazionali/es. (1)
create table Impiegato(
Matricola char(6) primary key,Nome char(20) not null,Cognome char(20) not null,Dipart char(15) references Dipartimento(NomeDip),foreign key
(Nome, Cognome) references Anagrafica(Nome,Cognome))
36 © 2007 Andrea Turati
Due modi per esprimere il vincolo di integrità referenziale
create table Dipartimento(
NomeDip char(15) primary key,Via char(20),NumeroUffici integer,…
)
create table Anagrafica(
Nome char(20),Cognome char(20),Indirizzo char(40),…primary key (Nome, Cognome)
)
Informatica Applicata per Ingegneria Biomedica - SQL/2 37 © 2007 Andrea Turati
Il comando ALTER consente in generale di modificare la struttura degli oggetti dello schema di una base di dati, quindianche le tabelle:– Modificare il nome di una tabella
alter table VecchioNomeTabellarename NuovoNomeTabella;
– Cancellare colonnealter table NomeTabelladrop column NomeColonnaDaEliminare;
– Aggiungere colonnealter table NomeTabellaadd column NomeNuovaColonna char(10);
– Modificare il nome delle colonnealter table NomeTabellachange VecchioNomeColonna NuovoNomeColonna char(20);
– Modificare il tipo delle colonnealter table NomeTabellamodify NomeColonna tinyint not null;
– Modificare i vincoli di integrità associati ad una tabellaalter table NomeTabelladrop foreign key NomeChiaveEsternaDaEliminare;
Modifica delle tabelle
Informatica Applicata per Ingegneria Biomedica - SQL/2 38 © 2007 Andrea Turati
Creazione, modifica, cancellazionedi una tabella
Creazione:create table Persona(
Cod_fisc char(16) primary key,Nome char(25),Stipendio integer
);
Modifica:alter table Persona (
add Mesi_di_lavoro integer);
Cancellazione:drop table Persona;
Cod_fisc Nome Stipendio
Cod_fisc Nome Stipendio Mesi_di_lavoro
Informatica Applicata per Ingegneria Biomedica - SQL/2 39 © 2007 Andrea Turati
create table Sciatore(
Nome char(25) primary key,Paese char(25),Età integer
)
create table Gareggia(
NomeSciatore char(25) references Sciatore(Nome),NomeGara char(25),Piazzamento integer,primary key (NomeSciatore, NomeGara),foreign key (NomeGara) references Gara(Nome)
)
Esercizio
create table Gara(
Nome char(25) primary key,Luogo char(30),Paese char(25),Lunghezza integer
)
Sciatore( Nome, Paese, Età)Gareggia(NomeSciatore, NomeGara, Piazzamento)
Gara (Nome, Luogo, Paese, Lunghezza )
Definire in SQL le tabelle, indicando i vincoli di integrità referenziale
Informatica Applicata per Ingegneria Biomedica - SQL/2 40 © 2007 Andrea Turati
Altri comandi di Data Manipulation Language
DROP
DDL DML DCLComandi SQL (Structured Query Language)
CREATE INSERT GRANTUPDATE REVOKEDELETESELECT
DML
ALTER
Informatica Applicata per Ingegneria Biomedica - SQL/2 41 © 2007 Andrea Turati
Comandi di modifica dell’istanza della base di dati
INSERT into tabella …– Permette di inserire in una tabella una nuova riga (specificando i
valori per tutti i campi, oppure solo alcuni campi, con la possibilità di inserire valori provenienti da una query)
UPDATE tabella set …– Permette di modificare i valori di una singola riga oppure di un
insieme di righe
DELETE tabella …– Permette di cancellare alcune o tutte le righe di una tabella
Informatica Applicata per Ingegneria Biomedica - SQL/2 42 © 2007 Andrea Turati
Insert
insert into nometabella (listaAttributi)values (listaValori);
– La listaAttributi deve essere “compatibile” con la listaValori: nella colonna corrispondente al primo attributo verrà inserito il primo valore, e così via ( Attenzione alla posizione!)
– Se non viene specificata la lista di attributi si intendono tutti gli attributi (nell’ordine specificato dalla definizione della tabella)
– La listaValori può essere sostituita da una query il cui risultato sia costituito da una lista di attributi compatibile con listaAttributi.
Informatica Applicata per Ingegneria Biomedica - SQL/2 43 © 2007 Andrea Turati
Update
update nometabellaset AttributoX = ValoreXwhere Condizione1;
– Il ValoreX deve essere di tipo compatibile con l’AttributoX– La condizione WHERE permette di indicare i record che si vuole
aggiornare• Se non è specificata alcuna condizione, verranno modificati tutti i
record, impostando a tutti il valoreX per la colonna AttributoX
– Il ValoreX può essere il risultato di una query– L’espressione Attibuto=Valore può essere ripetuta più volte per
modificare diversi attributi con un’unica istruzione (gli elementi della lista saranno separati da una virgola)
Informatica Applicata per Ingegneria Biomedica - SQL/2 44 © 2007 Andrea Turati
Delete
delete from nometabellawhere Condizione1;
– La condizione WHERE permette di individuare i record da cancellare
• Vengono cancellati tutti i record che soddisfano la condizione
– La clausola WHERE è opzionale: se non viene specificata alcuna condizione, vengono cancellati tutti i record della tabella
• La tabella rimane comunque, anche se alla fine non contiene alcun record (a differenza di DROP, che cancella l’intera tabella)
Informatica Applicata per Ingegneria Biomedica - SQL/2 45 © 2007 Andrea Turati
Inserimento e modifica di dati – es.(1)
insert into Persone (Cod_fisc, Nome)
values (“cod9”, “Lucia”);
Persone
Cod_fisc Nome Cognome
cod1 Marco Pace
cod3 Sara Gelo
Cod_fisc Nome Cognome
cod1 Marco Pace
cod3 Sara Gelo
cod9 Lucia
Clienti
Cod_fisc Nome Cognome Prodotto
cod8 Luigi Strada re456
cod7 Lara Santi f54t
insert into Persone
select Cod_fisc, Nome, Cognome
from clienti;
Cod_fisc Nome Cognome
cod1 Marco Pace
cod3 Sara Gelo
cod8 Luigi Strada
cod7 Lara Santi
Informatica Applicata per Ingegneria Biomedica - SQL/2 46 © 2007 Andrea Turati
Inserimento e modifica di dati – es.(2)
update Persone set Cod_fisc = “cod11”where (Nome = “Marco”)and (Cognome = “Pace”);
Persone
Cod_fisc Nome Cognome Compenso_mensile
cod1 Marco Pace 85
cod4 Luigi Gioia 105
cod3 Sara Gelo 102
Persone
Cod_fisc Nome Cognome Compenso_mensile
cod11 Marco Pace 85
cod4 Luigi Gioia 105
cod3 Sara Gelo 102
delete from Personewhere Compenso_mensile < 100;
Persone
Cod_fisc Nome Cognome Compenso_mensile
cod4 Luigi Gioia 105
cod3 Sara Gelo 102
Informatica Applicata per Ingegneria Biomedica - SQL/2 47 © 2007 Andrea Turati
Bibliografia
Paolo Atzeni, Stefano Ceri, Stefano Paraboschi, Riccardo TorloneBasi di Dati – Modelli e linguaggi di interrogazione, 2002 - McGraw Hill– 4.1, 4.2, 4.4
Teach Yourself SQL in 10 minutessecond editionBen Forta2001, SAMS