Sistemi per il recupero delle informazioni RIASSUNTO: IL LINGUAGGIO SQL.
Sistemi per il recupero delle informazioni
description
Transcript of Sistemi per il recupero delle informazioni
Sistemi per il recupero delle informazioni
RIASSUNTO
PROGETTAZIONE
Ricordiamo le fasi della progettazione di una Base di Dati
ESEMPIO
ESEMPIO
VIENE TRADOTTO NELLO SCHEMA
MUSEI (NomeM, Città, Indirizzo, Direttore)
ARTISTI (NomeA, Nazionalità, DataN, DataM)
OPERE (Codice, Anno, Titolo, NomeM*, NomeA*)
PERSONAGGI (Personaggio, Codice*)
DIPINTI (Codice*, Tipo, Larghezza, Altezza)
SCULTURE (Codice*, Materiale, Altezza, Peso)
Ennupla. È un insieme finito di coppie (Attributo, valore atomico)
Relazione. È un insieme finito (eventualmente vuoto) di ennuple con la stessa struttura.
I campi di un’ennupla sono atomici (numeri, stringhe o il valore NULL).
Un’ennupla si usa per rappresentare entità e la relazione si usa per rappresentare classi di entità.
ENNUPLA E RELAZIONEI meccanismi per definire una base di dati con il modello relazionale sono solo due:
• l’ennupla • la relazione.
TERMINOLOGIA - I
attributo: corrisponde (non sempre) ad un attributo del modello E-R. Diversamente dal modello E-R, gli attributi sono sempre univoci (ad un sol valore) ed atomici (non composti)
L’ordine degli attributi non è significativo
dominio (di un attributo): è l’insieme dei valori che può assumere un attributo.
chiave primaria di una relazione: un attributo che identifica univocamente le ennuple della relazione. Gli attributi della chiave primaria vengono sottolineati
TERMINOLOGIA - II
Una relazione si definisce dandole un nome ed elencando fra parentesi tonde il tipo delle sue ennuple. La definizione di una relazione è detta schema della relazione
R(A1:T1, A2:T2,..., An:Tn)
R è il nome della relazione A1, A2,... sono gli attributi della relazione T1, T2,... sono i tipi degli attributi (interi, reali, booleani, stringhe)
Per semplicità omettiamo la specifica dei tipi, per cui lo schema di relazione è R(A1, A2,...,An)
RELAZIONE E TABELLA
Orario
Insegnamento Docente Aula Ora
Analisi matem. I Luigi Neri N1 8:00Basi di dati Piero Rossi N2 9:45
Chimica Nicola Mori N1 9:45Fisica I Mario Bruni N1 11:45Fisica II Mario Bruni N3 9:45
Sistemi inform. Piero Rossi N3 8:00
ORARIO (Insegnamento, Docente, Aula, Ora)
Relazione e tabella sono sinonimi
SCHEMAOrario
Insegnamento Docente Aula Ora
Analisi matem. I Luigi Neri N1 8:00Basi di dati Piero Rossi N2 9:45
Chimica Nicola Mori N1 9:45Fisica I Mario Bruni N1 11:45Fisica II Mario Bruni N3 9:45
Sistemi inform. Piero Rossi N3 8:00
ORARIO (Insegnamento, Docente, Aula, Ora)
Le colonne della tabella formano lo schema della relazione
Lo schema della relazione è la descrizione della struttura di una relazione
ISTANZEOrario
Insegnamento Docente Aula Ora
Analisi matem. I Luigi Neri N1 8:00Basi di dati Piero Rossi N2 9:45
Chimica Nicola Mori N1 9:45Fisica I Mario Bruni N1 11:45Fisica II Mario Bruni N3 9:45
Sistemi inform. Piero Rossi N3 8:00
ORARIO (Insegnamento, Docente, Aula, Ora)
I contenuti delle righe della tabella formanole istanze della relazione
Un’istanza di uno schema di relazione è un insieme finito di ennuple. osserviamo che tutte le ennuple di una relazione hanno la stessa struttura
DATABASE
Corso AulaDocente
RossiBasi di dati DS3
BruniReti N3
NeriSistemi N3
BruniControlli G
Corsi
Nome PianoEdificio
OMIDS3 Terra
PincherleG Primo
OMIN3 Terra
Aule
CorsiSedi Corso Aula
Reti N3
Sistemi N3
Controlli G
PianoEdificio
OMI Terra
Pincherle Primo
OMI Terra
Un database e’ un insieme di tabelle
Lo schema relazionale è la descrizione della struttura di una base di dati insieme di schemi di relazione e insieme di vincoli
DA MODELLO A OGGETTI A MODELLO RELAZIONALE
La trasformazione di uno schema a oggetti in uno schema relazionale avviene eseguendo i seguenti passi:
1. rappresentazione delle classi2. rappresentazione delle associazioni uno a uno e uno a molti;3. rappresentazione delle associazioni molti a molti o non binarie;4. rappresentazione delle gerarchie di inclusione;5. rappresentazione degli attributi multivalore;6. appiattimento gli attributi composti
1. Rappresentazione delle classi
Una classe C è rappresentata da una relazione R i cui attributi sono quelli di C
si traduce in
Studenti(Matricola, Nome, AnnoImmatric)
2. Rappresentazione delle associazioni uno a uno e uno a molti
Studenti(Matricola, Nome, AnnoImmatric, Codice*)CorsidiLa(Codice, Nome, Tipo)
la chiave esterna Codice* rappresenta l’associazione è_iscritto
N.B. È un grave errore fare il contrario. Lo schema:
Studenti(Matricola, Nome, AnnoImmatric)CorsidiLa(Codice, Nome, Tipo, Matricola*)
rappresenta Corsi di Laurea ai quali può essere iscritto un solo studente!
3. Rappresentazione delle associazioni molti a molti o non binarie
Una ennupla di Insegna rappresenta una coppia (Corso_di_Lurea, Docente) di oggetti in associazione.
CorsidiLa(Codice,Nome,Facoltà,Tipo)Docenti(CodDoc, Settore)
Insegna(Codice*, CodDoc*)
Il docente identificato dal CodDoc 1592 insegna ai corsi di laurea identificati dai codici Inf, SBC e Mat , il docente identificato dal CodDoc 3014 insegna al corso di laurea identificato dai codici Inf, ecc...
4. Rappresentazione delle gerarchie fra classiPartizionamento orizzontale
Tre schemi indipendenti, uno per ogni classe, contenenti tutti gli attributi di ciascuna classe
Anche con questa soluzione viene ignorata la gerarchia: si perde la superclasse
Si osservi che con nessuna delle tre soluzioni è in generale possibile esprimere i vincoli strutturali della gerarchia, vale a dire i vincoli di disgiunzione e di totalità
5. Rappresentazione delle proprietà multivalore
Film(CodFilm,Titolo,Regista,Anno)
Attori(CodFilm*, Attore)
Riassumendo
Le regole di traduzione Entità: diventano tabelle ed i loro identificatori chiavi primarie Associazioni 1-1: se obbligatorie si procede come per le 1-N scegliendo il
lato in cui includere gli attributi e la chiave esterna; se una opzionale si includono gli attributi e la chiave esterna sul lato“obbligatorio”; se entrambe opzionali si costruisce una tabella autonoma come per il caso N-N.
Associazioni1-N:gli attributi dell’associazione e la chiave primaria della tabella relativa all’entità dal lato “N” sono inclusi nella tabella relativa all’entità dal lato“1”.
Associazioni N-N: diventano tabelle con chiave primaria formata dall’unione delle chiavi delle entità coinvolte
Informazione incompleta
ll modello relazionale impone ai dati una struttura rigida: - le informazioni sono rappresentate per mezzo di ennuple - solo alcuni formati di ennuple sono ammessi: quelli che
corrispondono agli schemi di relazione
I dati disponibili possono non corrispondere al formato previsto
Franklin RooseveltDelano
Nome CognomeSecondoNome
Winston Churchill
Charles De Gaulle
Josip Stalin
Es: PERSONE(Nome, SecondoNome, Cognome)
Informazione incompleta: soluzioni?
Non conviene (anche se spesso si fa) usare valori del dominio (0, stringa nulla, “99”, ...):
- potrebbero non esistere valori “non utilizzati” - in fase di utilizzo (nei programmi) sarebbe necessario ogni volta
tener conto del “significato” di questi valori
Tecnica rudimentale ma efficace:- valore nullo: denota l’assenza di un valore del dominio (e non è un
valore del dominio) - si possono (e debbono) imporre restrizioni sulla presenza di valori
nulli
Vincoli, schemi e istanze
I vincoli corrispondono a proprietà del mondo reale modellato dalla base di dati interessano a livello di schema (con riferimento cioè a tutte le
istanze)
Ad uno schema associamo un insieme di vincoli e consideriamo corrette (valide, ammissibili) le istanze che soddisfano tutti i vincoli
Nel modello relazionale si possono esprimere i seguenti vincoli: Vincolo di chiave Vincolo di chiave esterna Tipo di un attributo Attributo obbligatorio Vincoli strutturali delle associazioni (non completamente)
Una base di dati "scorretta"
Studente Voto Lode Corso
32 01276545
276545 30 e lode 02
787643 27 e lode 03
739430 24 04
Esami
Matricola
276545
787643
787643
Cognome
Rossi
Neri
Bianchi
Nome
Mario
Piero
Luca
Studenti
787643
787643
32
27 e lode
739430
Tipi di vincoli
Vincoli intrarelazionali: coinvolgono una singola relazione del database. Esempi
- vincoli di ennupla- vincoli di chiave - vincoli su valore
Vincoli interrelazionali: coinvolgono diverse relazioni del database. Esempio: vincoli di integrità referenziale
Vincoli di ennupla, esempio
Impiegato
Rossi
Neri
Bruni
Stipendi Lordo
55.000
45.000
47.000
Netto
42.500
35.000
36.000
Ritenute
12.500
10.000
11.000
Lordo = (Ritenute + Netto)
Esprimono condizioni sui valori di ciascuna ennupla, indipendentemente dalle altre ennuple
Una possibile sintassi: espressione booleana di atomi che confrontano valori di attributo o espressioni aritmetiche su di essi
Importanza delle chiavi
L’esistenza delle chiavi garantisce l’accessibilità a ciascun dato della base di dati
Le chiavi permettono di correlare i dati in relazioni diverse: il modello relazionale è basato su valori
In presenza di valori nulli, i valori della chiave non permettono di identificare le ennuple di realizzare facilmente i riferimenti da altre relazioni
Chiave primaria
Chiave su cui non sono ammessi nulli Notazione: sottolineatura
Matricola
78763
65432
Nome
Piero
Mario87654
43289
Mario
Cognome
Neri
Neri
Rossi
Neri Mario
Corso
Ing Mecc
Ing Inf
Ing Civile
NULL
Nascita
86765 MarioNULL Ing Inf 5/12/78
10/7/79
NULL
3/11/76
5/12/78
Informazioni in relazioni diverse sono correlate attraverso valori comuni in particolare, valori delle chiavi (primarie)
le correlazioni debbono essere "coerenti“
Un vincolo di integrità referenziale (“foreign key”) fra gli attributi X di una relazione R1 e un’altra relazione R2 impone ai valori su X in R1 di comparire come valori della chiave primaria di R2
ES: vincoli di integrità referenziale fra: l’attributo Vigile della relazione INFRAZIONI e la relazione VIGILI
Integrità referenziale
Matricola
3987
3295
9345
VigiliCognome
Rossi
Neri
Neri
Nome
Luca
Piero
Mario
Mori Gino7543
Infrazioni
Codice
34321
73321
64521
53524
Data
1/2/95
4/3/95
5/4/96
5/2/98
Vigile
3987
3295
3295
9345
Prov Numero
MI
TO
PR
PR
39548K
E39548
839548
839548
3295
3295
3987
3987
9345
3987
9345
9345
3295
3295
3295
Infrazioni
Codice
34321
73321
64521
53524
Data
1/2/95
4/3/95
5/4/96
5/2/98
Vigile
3987
3295
3295
9345
Prov Numero
MI
TO
PR
PR
39548K
E39548
839548
839548
Auto Prov Numero
MI
TO
PR
E39548
F34268
839548
Cognome
Rossi
Rossi
Neri
Nome
Mario
Mario
Luca
TO E39548
TO
E39548
Violazione di vincolo di integrità referenziale
Integrità referenziale e valori nulli
Impiegati Matricola
34321
64521
53524
Cognome
Rossi
Neri
Verdi
Progetto
IDEA
XYZ
NULL
Progetti Codice
IDEA
BOH
XYZ
Inizio
01/2000
07/2001
09/2001
Durata
36
24
24
Costo
200
120
150
73032 Bianchi IDEA
Azioni compensative
Esempio: Viene eliminata una ennupla causando una violazione
Comportamento “standard”: Rifiuto dell'operazione
Azioni compensative: Eliminazione in cascata Introduzione di valori nulli
Eliminazione in cascata
Impiegati Matricola
34321
64521
53524
Cognome
Rossi
Neri
Verdi
Progetto
IDEA
XYZ
NULL
73032 Bianchi IDEA
Progetti Codice
IDEA
BOH
XYZ
Inizio
01/2000
07/2001
09/2001
Durata
36
24
24
Costo
200
120
150
XYZ 07/2001 24 120XYZ 07/2001 24 120XYZ 07/2001 24 120
53524 Neri XYZ
Introduzione di valori nulli
Impiegati Matricola
34321
64521
53524
Cognome
Rossi
Neri
Verdi
Progetto
IDEA
XYZ
NULL
73032 Bianchi IDEA
Progetti Codice
IDEA
BOH
XYZ
Inizio
01/2000
07/2001
09/2001
Durata
36
24
24
Costo
200
120
150
XYZ 07/2001 24 120XYZ 07/2001 24 120XYZ 07/2001 24 120
NULL
Esercizio
La segreteria di un corso di laurea deve gestire alcune informazioni relative all’orario delle lezioni. informazioni: aule, corsi, lezioni
le aule sono identificate da un codice, di ogni aula interessa il numero dei posti, l’edificio in cui è situata, se ha o non ha proiettore
alcune aule sono dotate di calcolatori, in questo caso interessa sapere il numero dei pc
i corsi sono identificati da un codice; di ogni corso interessa il nome e il docente
le lezioni sono caratterizzate da un’ora d’inizio, un’ora di fine, un giorno della settimana, il semestre
ogni lezione è tenuta in un’aula e si riferisce ad un corso
Sistemi per il recupero delle informazioni
ALGEBRA RELAZIONALE
Accesso ai Dati nei Sistemi Relazionali
Una base di dati può essere utilizzata con due modalità:
interattivamente: l’utente interagisce direttamente con la base di dati presentando al sistema una richiesta di dati. Tale richiesta prende il nome di interrogazione (query). L’interrogazione viene interpretata dal sistema, che in risposta restituisce i dati richiesti. Nella richiesta devono essere specificate le proprietà dei dati che interessano. Se ad es. vogliamo l’elenco dei libri scritti da Calvino, nella richiesta deve essere specificata questa proprietà. L’interrogazione deve essere formulata per mezzo di un linguaggio formale.
mediante programmi: questo uso è riservato ad utenti programmatori. Le interrogazioni fanno parte di un programma applicativo che può essere eseguito dal sistema numerose volte, ed il risultato delle interrogazioni può essere utilizzato dal programma per successive elaborazioni
NOME MATRICOL INDIRIZZO TELEFONO
Mario Rossi 123456 Via Etnea 1 222222
Ugo Bianchi 234567 Via Roma 2 333333
Teo Verdi 345678 Via Enna 3 444444
CORSO MATRICOLA VOTO Programmazione 345678 27 Architetture 123456 30 Programmazione 234567 18 Matematica Discreta
345678 22
Architettura 345678 30
CORSO PROFESSORE Programmazione Ferro Architetture Pappalardo Matematica Discreta Lizzio
Quali Professori hanno dato piu' di 24 a Teo Verdi ed in quali corsi?
PROFESSORE CORSO Programmazione Ferro Architetture Pappalardo
ESEMPI DI QUERY
Come esempio di linguaggi per l’uso interattivo di basi di dati, relazionali, vediamo
l’algebra relazionale: insieme di operatori su relazioni che danno come risultato relazioni. Non si usa come linguaggio di interrogazione dei DBMS ma come rappresentazione interna delle interrogazioni.
il linguaggio SQL (Structured Query Language), che offre una sintassi per l’algebra relazionale.
Il termine algebra è dovuto al fatto che sono previsti operatori (query) che agiscono su relazioni e producono altre relazioni come risultato.
Gli operatori possono essere combinati per formare espressioni complesse.
LINGUAGGI RELAZIONALI
Algebra relazionale
Insieme di operatori su relazioni che producono relazioni (tabelle) e possono essere composti per svolgere operazioni più
complesse
Gli operatori fondamentali dell’algebra relazionale sono: Ridenominazione; Unione; Intersezione; Differenza; Proiezione; Restrizione (o Selezione); Prodotto.
I simboli R,S,... denotano relazioni, A, B,…attributi e X,Y,…insiemi di attributi
OPERATORI FONDAMENTALI
Paternità
Padre Figlio
Adamo Caino
Abramo Isacco
Adamo Abele
Genitore Padre (Paternità)
Padre Figlio
Adamo Caino
Abramo Isacco
Adamo Abele
Genitore
RIDENOMINAZIONE
Operatore unario
Modifica il nome di un attributo senza cambiarne il valore: restituisce la relazione ottenuta sostituendo in R gli attributi A, B,… con gli attributi A’, B’,…
DEFINIZIONE OPERATORIALE: AA’ (R)
OPERATORI INSIEMISTICI
Le relazioni sono degli insiemi, quindi possiamo applicare gli operatori sugli insiemi
I risultati debbono essere relazioni
E’ possibile applicare unione, intersezione, differenza solo a relazioni definite sugli stessi attributi
Siano R ed S relazioni dello stesso tipo allora
L’unione di R con S restituisce una relazione dello stesso tipo con le ennuple che stanno in R in S, o in entrambe.
L’intersezione di R con S restituisce una relazione dello stesso tipo con le ennuple che stanno contemporaneamente sia in R sia in S.
La differenza di R con S restituisce una relazione dello stesso tipo con le ennuple che stanno in R ma non in S.
DEFINIZIONE OPERATORIALE RS RS R - S
UNIONE, INTERSEZIONE, DIFFERENZA
ESEMPIO DI UNIONE
ESEMPIO DI INTERSEZIONE
ESEMPIO DI DIFFERENZA
“Paternita’” e “Maternità” sono attributi con nomi diversi ma entrambi sono “Genitori”
Soluzione: ridenominare gli attributi
ESEMPIO: UNIONE?
Maternità
Madre
???
ESEMPIO: RIDENOMINAZIONE E UNIONE
selezione
proiezione
operatori "ortogonali“
selezione:decomposizione orizzontale
proiezione:decomposizione verticale
Produce risultati: su un sottoinsieme degli attributi dell’operando con valori da tutte le n-uple della relazione
Data la relazione R su insieme di attributi X={A1,A2,…An} e un sottoinsieme Y di X, la proiezione di R su Y è la relazione ottenuta da R considerando solo i valori sugli attributi di Y
DEFINIZIONE OPERATORIALE: Y(R)
La cardinalità di Y(R), cioè il numero degli elementi che lo compongono, puo’ essere minore di R nel caso di duplicati
PROIEZIONE
Cognome Filiale StipendioMatricola
Neri Milano 645998
Neri Napoli 557309
Rossi Roma 645698
Rossi Roma 449553
cognome e filiale di tutti gli impiegati
Cognome, Filiale (Impiegati)
ESEMPIO PROIEZIONE
Si riduce la cardinalita’ del risultato rispetto all’operando
Produce risultati: con lo stesso schema dell’operando con un sottoinsieme delle ennuple dell’operando: quelle che soddisfano
la condizione
Data la relazione R la restrizione di R alla condizione C restituisce una relazione dello stesso tipo di R avente per valori gli elementi di R che soddisfano la condizione C.
La condizione di selezione è formata da operatori booleani (AND, OR, NOT) condizione atomiche: termini che possono contenere
confronti fra attributi (per esempio, Stipendio>Tasse, dove Stipendio e Tasse sono attributi)
confronti fra attributi e costanti (per esempio, Età 60, dove Età è un attributo)
DEFINIZIONE OPERATORIALE: Condizione(R)
SELEZIONE (o RESTRIZIONE)
Operatori booleani
sintassi: Condizione (Operando)
Condizione: espressione booleana (come quelle dei vincoli di ennupla) semantica: il risultato contiene le ennuple dell'operando che soddisfano la
condizione
Connettivi logici (AND), (OR), (NOT)
Operatori di confronto = (uguale) (diverso) (maggiore) (minore) (maggiore o uguale) (minore o uguale)
Cognome Filiale StipendioMatricola
Neri Milano 645998
Rossi Roma 557309
Neri Napoli 645698
Milano Milano 449553
Impiegati
Milano Milano 449553 Neri Napoli 645698
impiegati che guadagnano più di 50
Stipendio > 50 (Impiegati)
ESEMPIO RESTRIZIONE
Impiegati
Cognome Filiale StipendioMatricola
Neri Milano 645998
Rossi Roma 557309
Neri Napoli 645698
Milano Milano 449553
impiegati che guadagnano più di 50 e lavorano a Milano
Stipendio > 50 AND Filiale = 'Milano' (Impiegati)
Rossi Roma 557309
Neri Napoli 645698
Milano Milano 449553
Neri Milano 645998
Impiegati
Cognome Filiale StipendioMatricola
Neri Milano 645998
Rossi Roma 557309
Neri Napoli 645698
Milano Milano 449553
impiegati che hanno lo stesso nome della filiale presso cui lavorano
Cognome = Filiale(Impiegati)
Neri Milano 645998
Rossi Roma 557309
Neri Napoli 645698
Milano Milano 449553
Selezione e proiezione
Combinando selezione e proiezione, possiamo estrarre interessanti informazioni da una relazione
matricola e cognome degli impiegati che guadagnano più di 50
Stipendio > 50 (Impiegati) Matricola,Cognome ( )
Cognome Filiale StipendioMatricola
Neri Milano 645998Rossi Roma 557309
Neri Napoli 645698Milano Milano 449553 Milano Milano 449553 Neri Napoli 645698
Selezione con valori nulli
Cognome Filiale EtàMatricola
Neri Milano 455998Rossi Roma 327309
Bruni Milano NULL9553
Impiegati
SEL Età > 40 (Impiegati)
• la condizione atomica è vera solo per valori non nulli
Selezione con valori nulli: soluzione
SEL Età > 40 (Impiegati) la condizione atomica è vera solo per valori non nulli per riferirsi ai valori nulli esistono forme apposite di condizioni:
IS NULL
IS NOT NULL si potrebbe usare (ma non serve) una "logica a tre valori" (vero, falso,
sconosciuto) Quindi:
SEL Età>30 (Persone) SEL Età30 (Persone) SEL Età IS NULL (Persone)
=
SEL Età>30 Età30 Età IS NULL (Persone)
=
Persone
Cognome Filiale EtàMatricola
Neri Milano 455998Rossi Roma 327309
Bruni Milano NULL9553
Impiegati
Neri Milano 455998Bruni Milano NULL9553
SEL (Età > 40) OR (Età IS NULL) (Impiegati)
Neri Milano 455998Bruni Milano NULL9553
JOIN NATURALE
operatore binario (generalizzabile) produce un risultato
sull'unione degli attributi degli operandi con ennuple costruite ciascuna a partire da una ennupla di ognuno degli
operandi
Permette di combinare ennuple da relazioni diverse basandosi sui valori degli attributi
Sia R con attributi XY ed S con attributi YZ. Il join naturale produce una relazione di attributi XYZ; ennuple del risultato sono ottenute combinando le ennuple di R e S che hanno gli stessi valori negli attributi con lo stesso nome
ESEMPIO - I
join completo: ogni ennupla contribuisce al risultato
ESEMPIO - II
Join non completo: alcuni valori tra gli attributi comuni non coincidono, quindi, alcune ennuple non partecipano al JOIN
ESEMPIO - III
Join vuoto: caso limite potrebbe anche succedere che nessuna ennupla trovi il
corrispettivo
ESEMPIO - IV
L’altro caso estremo del JOIN ogni ennupla di R1 si combina con ogni ennupla di R2
la cardinalita’ del risultato e’ il prodotto delle cardinalita’
Cardinalità del join
Il join di R1 e R2 contiene un numero di ennuple compreso fra zero e il prodotto di |R1| e |R2|
se il join coinvolge una chiave di R2, allora il numero di ennuple è compreso fra zero e |R1|
se il join coinvolge una chiave di R2 e un vincolo di integrità referenziale, allora il numero di ennuple è pari a |R1|
R1(A,B) , R2 (B,C)in generale
0 |R1 JOIN R2| |R1| |R2| se B è chiave in R2
0 |R1 JOIN R2| |R1|
se B è chiave in R2 ed esiste vincolo di integrità referenziale fra B (in R1) e R2:|R1 JOIN R2| = |R1|
Impiegato Reparto
Rossi A
Neri B
Bianchi B
Reparto Capo
B Mori
C Bruni
Neri B Mori
Impiegato Reparto Capo
Bianchi B Mori
A
C
Join, una difficoltà
alcune ennuple non contribuiscono al risultato: vengono "tagliate fuori"
Join esterno
Il join esterno estende, con valori nulli, le ennuple che verrebbero tagliate fuori da un join (interno)
esiste in tre versioni: sinistro, destro, completo
Join esterno
sinistro: mantiene tutte le tuple del primo operando, estendendole con valori nulli, se necessario
destro: ... del secondo operando ... completo: … di entrambi gli operandi ...
Impiegato RepartoRossi ANeri B
Bianchi B
ImpiegatiReparto Capo
B MoriC Bruni
Reparti
Neri B MoriImpiegato Reparto Capo
Bianchi B Mori
Impiegati JOINLEFT Reparti
C
Rossi A NULL
ARossi
Impiegato RepartoRossi ANeri B
Bianchi B
ImpiegatiReparto Capo
B MoriC Bruni
Reparti
Neri B MoriImpiegato Reparto Capo
Bianchi B Mori
Impiegati JOINRIGHT Reparti
A
NULL C Bruni
C Bruni
Impiegato RepartoRossi ANeri B
Bianchi B
ImpiegatiReparto Capo
B MoriC Bruni
Reparti
Neri B MoriImpiegato Reparto Capo
Bianchi B Mori
Impiegati JOINFULL Reparti
NULL C Bruni
C BruniARossi
Rossi A NULL
Join e proiezioni
Impiegato RepartoRossi ANeri B
Bianchi B
Reparto CapoB MoriC Bruni
Neri B MoriImpiegato Reparto Capo
Bianchi B Mori
Impiegato RepartoNeri B
Bianchi B
Reparto CapoB Mori
Proiezioni e join
Neri B MoriImpiegato Reparto Capo
Bianchi B BruniVerdi A Bini
Neri BImpiegato Reparto
Bianchi BVerdi A
B MoriReparto Capo
B BruniA Bini
Verdi A Bini
Neri B MoriImpiegato Reparto Capo
Bianchi B BruniNeri B Bruni
Bianchi B Mori
Join e proiezioni
R 1(X1), R 2(X2)
PROJX1 (R 1 JOIN R2 ) R 1
R(X), X = X1 X2
(PROJX1 (R)) JOIN (PROJX2
(R)) R
Prodotto cartesiano
un join naturale su relazioni senza attributi in comune contiene sempre un numero di ennuple pari al prodotto delle cardinalità
degli operandi (le ennuple sono tutte combinabili )
Rossi A
Neri B
Bianchi B
Impiegato RepartoImpiegati
A Mori
B BruniB BruniB Bruni
Codice Capo
Reparti
Impiegati JOIN Reparti
Impiegato Reparto CapoCodice
Rossi A MoriAAA
Rossi A B Bruni
Neri B MoriA
Neri B B Bruni
Bianchi B MoriA
Bianchi B B Bruni
Prodotto cartesiano
Il prodotto cartesiano, in pratica, ha senso (quasi) solo se seguito da selezione:
SELCondizione (R1 JOIN R2) L'operazione viene chiamata theta-join e indicata con
R1 JOINCondizione R2
La condizione C è spesso una congiunzione (AND) di atomi di confronto A1 A2 dove è uno degli operatori di confronto (=, >, <, …)
se l'operatore è sempre l'uguaglianza (=) allora si parla di equi-join
Rossi ANeri B
Bianchi B
Impiegato Reparto
Impiegati
A MoriB BruniB BruniB Bruni
Codice Capo
Reparti
Impiegati JOINReparto=Codice Reparti
Impiegato Reparto CapoCodiceRossi A MoriAAARossi A B BruniNeri B MoriANeri B B Bruni
Bianchi B MoriABianchi B B Bruni
Rossi A MoriAAANeri B B Bruni
Bianchi B B Bruni
Esempi
Impiegati Nome Età StipendioMatricola
Bianchi 37 385998Rossi 34 457309
Bruni 43 425698Neri 42 359553
Mori 45 504076Lupi 46 608123
Supervisione Impiegato Capo
59987309
56989553
4076
56985698
40764076
8123
Nome Età StipendioMatricola
Bianchi 37 385998Rossi 34 457309
Bruni 43 425698Neri 42 359553
Mori 45 504076Lupi 46 608123
SELStipendio>40(Impiegati)
Bianchi 37 385998Neri 42 359553
Rossi 34 457309
Bruni 43 425698Mori 45 504076Lupi 46 608123
Rossi 34 457309Bruni 43 425698Mori 45 504076Lupi 46 608123
Trovare matricola, nome, età e stipendio degli impiegati che guadagnano più di 40 milioni
Nome Età StipendioMatricola
Bianchi 37 385998Rossi 34 457309
Bruni 43 425698Neri 42 359553
Mori 45 504076Lupi 46 608123
Bianchi 37 385998Neri 42 359553
Rossi 34 457309
Bruni 43 425698Mori 45 504076Lupi 46 608123
Rossi 34 457309Bruni 43 425698Mori 45 504076Lupi 46 608123
PROJMatricola, Nome, Età (SELStipendio>40(Impiegati))
Trovare matricola, nome ed età degli impiegati che guadagnano più di 40 milioni
Sistemi per il recupero delle informazioni
IL LINGUAGGIO SQL
INTRODUZIONE
Le interrogazioni devono essere scritte in un linguaggio formale con caratteristiche tali da renderlo adatto ad esprimere interrogazioni sulla BD, e da essere facilmente interpretato dal sistema. Il linguaggio generalmente usato si chiama SQL (Structured Query Language) È un linguaggio di interrogazione e manipolazione della base dati e delle informazioni in essa contenute
Creato negli anni ’70 presso IBM , inizialmente solo come linguaggio di interrogazione. Ora è linguaggio di riferimento per DataBase relazionali. Standardizzato grazie al lavoro di ISO (international standard organization) e ANSI (american national standard institute)
SQL
originariamente "Structured Query Language", ora "nome proprio" linguaggio con varie funzionalità:
contiene sia il DDL (schema) sia il DML(istanza) ne esistono varie versioni
vediamo gli aspetti essenziali, non i dettagli
prima proposta SEQUEL (1974); prime implementazioni in SQL/DS e Oracle (1981) dal 1983 ca. "standard di fatto" standard (1986, poi 1989 e infine 1992, 1999) - ISO, ANSI
standard per i software che usano il modello relazionale recepito solo in parte
DDL, DML, DCL Data Definition Language (DDL)
permette di creare e cancellare DB o di modificarne la struttura. Sono i comandi DDL a definire la struttura del DB e quindi i dati in esso contenuti. Ma non fornisce gli strumenti per modificare i dati stessi: per tale scopo si usa il DML. L’utente deve avere i permessi necessari per agire sulla struttura del DB che vengono dati tramite il DCL
Data Manipulation Language (DML) permette di inserire, cancellare, modificare e leggere i dati all’interno delle tabelle
di un DB. La struttura di questi dati deve essere già stata definita tramite il DDL. Il permesso di accedere ai dati deve essere assegnato all’utente tramite il DCL.
Data Controlo Language (DCL) serve a fornire o revocare agli utenti i permessi per poter usare i comandi DML e
DDL oltre agli stessi comandi DCL.
CREAZIONE DI TABELLE
Per definire una relazione (detta tabella nella terminologia SQL), si usa il comando “create table”: definisce uno schema di relazione e ne crea un’istanza vuota; specifica attributi, domini e vincoli; ad esempio
Libri(titolo, autore, codice_isbn)
CREATE TABLE, esempio
CREATE TABLE Impiegato(Matricola CHAR(6) PRIMARY KEY, Nome CHAR(20) NOT NULL,
Cognome CHAR(20) NOT NULL, Dipart CHAR(15),Stipendio NUMERIC(9) DEFAULT 0, FOREIGN KEY(Dipart) REFERENCES Dipartimento(NomeDip),
UNIQUE (Cognome,Nome) )
Domini Il “Dominio” indica i valori che un attributo può assumere e le operazioni che possono
essere compiute su di esso.
Domini elementari (predefiniti) Carattere: singoli caratteri o stringhe, anche di lunghezza variabile Bit: singoli booleani (flag) o stringhe di bit Numerici:
esatti (es: numeric, decimal) approssimati (es: float)
Data, ora, intervalli di tempo – UTC (Universal time coordinate) Introdotti in SQL:1999:
Boolean BLOB, CLOB (Binary/Character large object): per grandi immagini e testi
Domini definiti dall'utente (semplici, ma riutilizzabili)
VINCOLI
A ogni attributo possono essere associati dei vincoli
■ default: indica il valore che un attributo deve avere quando viene inserito un record che, in corrispondenza di quell’attributo non ha assegnato alcun valore
■ not null: i valori inseriti in quel campo devono essere diversi non nulli■ Es: Cognome CHAR(20) not null
■ unique: il valore può comparire una volta sola
■ primary key: chiave primaria, (una sola, implica NOT NULL)
VINCOLI
■ Il valore di un attributo dichiarato NOT NULL va obbligatoriamente specificato quando si aggiunge un’ennupla alla relazione.
■ Un altro vincolo è l’eventuale chiave primaria dichiarata con l’opzione primary key. Gli attributi della chiave primaria non possono assumere valori NULL.
Quando nella definizione di una tabella sono dichiarati dei vincoli il sistema che gestisce la BD controlla che le operazioni che modificano la tabella inserendo nuove ennuple o modificando i valori di attributi non violino i vincoli dichiarati. Se un vincolo può essere violato l’operazione non viene eseguita e viene segnalata una condizione di errore
Matricola CHAR(6) PRIMARY KEY
Matricola CHAR(6), …, PRIMARY KEY (Matricola)
PRIMARY KEY
CHIAVI ESTERNE
Vediamo come introdurre una chiave esterna attraverso il comando Foreign Key
create table studenti (nome char(20),matricola char(8) not
null,provincia char(2),anno_nascita smallint,primary key
(matricola) foreign key (codice) references CDL,
on delete no action,)
create table CDL (facoltà char(20),nome char(20) not null,primary key (codice),)
CHIAVI ESTERNEQuando si dichiara un vincolo di chiave esterna, il sistema fa i seguenti controlli:
1. quando si inserisce un’ennupla nella tabella Studenti, o quando si modifica il campo chiave esterna, il valore della chiave esterna deve essere presente in un’ennupla della tabella CDL;
2. quando si elimina un’ennupla dalla tabella CDL, se il valore della sua chiave primaria è usato come valore di una chiave esterna di un’ennupla della tabella Studenti, allora sono possibili tre scelte:
a. on delete no action: per proibire la cancellazione dell’ennupla da CDL. Questa opzione vale anche quando si modifica il valore della chiave primaria di CDL;b. on delete cascade, per eliminare sia l’ennupla da CDL che tutte le ennuple di Studenti che usano il valore della chiave primaria dell’ennupla che si elimina;c. on delete set null, per eliminare l’ennupla da CDL e porre a null il valore della chiave esterna di tutte le ennuple di Studenti che usano il valore della chiave primaria dell’ennupla che si elimina.
MODIFICA DEI DATI
Nuovi dati si inseriscono nella tabella con il comando INSERT.
Ad esempio, per aggiungere una nuova ennupla alla relazione Studenti si dà il comando
INSERT INTO Studenti VALUES ("Tizio", "081575", "MI", “1985”)
Per cambiare invece l’attributo Provincia da “MI” a “TO” per lo studente con Matricola "081575", si dà il comando:
UPDATE StudentiSET Provincia = “TO”
WHERE Matricola = "081575"
Per eliminare invece l’ennupla dello studente con matricola "081575", si dà il comando:
DELETE Studenti WHERE Matricola = "081575"
Transazioni in SQL
Istruzioni fondamentali begin transaction: specifica l'inizio della transazione (le operazioni non vengono eseguite
sulla base di dati) commit work: le operazioni specificate a partire dal begin transaction vengono eseguite rollback work: si rinuncia all'esecuzione delle operazioni specificate dopo l'ultimo begin
transaction . Tutte le modifiche effettuate sui dati in precedenza (a partire dall’inizio della transazione) sono cancellate. Annulla la transazione
begin transaction;
update ContoCorrente
set Saldo = Saldo – 10
where NumeroConto = 12345 ;
update ContoCorrente
set Saldo = Saldo + 10
where NumeroConto = 55555 ;
commit work;
RECUPERO DEI DATI: IL COMANDO SELECT
OBIETTIVI: Scrivere una query in linguaggio SQL selezionare ed elencare tutte le righe e le colonne di una tabella selezionare ed elencare determinate colonne di una tabella selezionare ed elencare le colonne di più tabelle
Anche se la parola query può essere tradotta in interrogazione o domanda, una query SQL non è necessariamente una domanda, può essere un comando per svolgere una delle seguenti operazioni: creare o cancellare una tabella inserire, modificare o cancellare campi ricercare informazioni specifiche in più tabelle e restituire i risultati in un
particolare ordine modificare i parametri di protezione di un database
ESEMPIO - I
Si consideri il seguente schema relazionale
Catalogo ( ISBN, Titolo, CasaEd, AnnoEd)
Supponiamo che interessi conoscere il titolo e la casa editrice dei libri pubblicati nel 2001. Occorre:
1. consultare la relazione Catalogo
ESEMPIO - II
2. considerare solo le ennuple in cui AnnoEd = 2001
3. prelevare da queste ennuple i valori degli attributi Titolo e CasaEd
Questa sequenza di operazioni viene eseguita dal DBMS, purché gli venga trasmesso un opportuno comando (interrogazione) nel linguaggio SQL:
SELECT Titolo, CasaEdFROM Catalogo
WHERE AnnoEd = 2001
dove SELECT, FROM e WHERE sono parole riservate del linguaggio SQL.
STRUTTURA DEL COMANDO
Una interrogazione (query) SQL agisce sulle relazioni definite nella base di dati, e restituisce come risultato una relazione. questa viene in generale visualizzata sul monitor, oppure stampata; può
anche essere memorizzata nella base di dati o può essere utilizzata in altre interrogazioni.
Nei casi più semplici una interrogazione SQL deve specificare quali sono le informazioni che interessano in quali relazioni si trovano quali proprietà devono avere
SELECT
Quali sono le informazioni che interessano
SELECT Attributo1,Attributo2,...
è presente in ogni interrogazione e definisce lo schema della relazione risultato. Più avanti vedremo che può avere una forma più complessa.
Esempio:SELECT Titolo, CasaEd
significa che ci interessano il titolo e la casa editrice
Le singole colonne verranno elencate nello stesso ordine indicato.
Il comando Select in SQL equivale all’operazione di proiezione dell’algebra relazionale.
FROM
In quali relazioni si trovano
FROM Relazione1,Relazione2,...
è presente in ogni interrogazione e specifica quali relazioni occorre visitare per ottenere il risultato.
Esempio:FROM Catalogo
significa che per estrarre le informazioni che interessano occorre prendere in esame la relazione Catalogo.
Per selezionare dati da un’altra tabella è sufficiente modificare la clausola FROM
WHERE
Quali proprietà devono essere soddisfatte
WHERE Condizione
La condizione è espressa sugli attributi delle relazioni specificate nella clausola FROM.
Può non essere presente, quando non si vogliono specificare condizioni.
Esempio:
WHERE AnnoEd = 2001
significa che interessano informazioni relative ai libri editi nel 2001.
ESEMPIO - I
Abbiamo visto che l’interrogazione
SELECT Titolo, CasaED
FROM Catalogo
WHERE AnnoEd = 2001
restituisce la relazione
contenente titolo e casa editrice dei libri editi nel 2001
ESEMPIO - II
Invece l’interrogazione
SELECT Titolo, CasaED
FROM Catalogo
restituisce la relazione
contenente titolo e casa editrice di tutti i libri presenti nel catalogo
Riassumendo…
Riepilogando la forma generale di un interrogazione SQL è, nei casi più semplici, la seguente:
SELECT Attributo1,Attributo2,...
FROM Relazione1,Relazione2,...
[WHERE Condizione]
Le parole in maiuscolo sono parole riservate del linguaggio SQL, sono fisse e specificano le clausole dell’ interrogazione; la clausola WHERE può mancare
Le parole in minuscolo sono variabili, e rappresentano le relazioni, gli attributi, le condizioni che riguardano la specifica interrogazione
Nome Età
Persone
Reddito
Andrea 27 21
Maria 55 42
Anna 50 35
Filippo 26 30
Luigi 50 40
Franco 60 20
Olga 30 41
Sergio 85 35
Luisa 75 87
Aldo 25 15
Andrea 27 21
Aldo 25 15
Filippo 26 30
Andrea 27 21
Aldo 25 15
Filippo 26 30
21
15
30
Reddito
Nome Età
Persone
Reddito
Andrea 27
Maria 55
Anna 50
Filippo 26
Luigi 50
Franco 60
Olga 30
Sergio 85
Luisa 75
Aldo 25
21
42
35
30
40
20
41
35
87
15
Nome e reddito delle persone con meno di trenta anni PROJNome, Reddito(SELEta<30(Persone))
SELECT Nome, RedditoFROM PersoneWHERE Eta < 30
SELECT, abbreviazioni (alias)
SELECT Nome, RedditoFROM PersoneWHERE Eta < 30
SELECT p.nome as nome, p.reddito as reddito FROM persone as p
WHERE p.eta < 30
Selezione, senza proiezione
Nome, età e reddito delle persone con meno di trenta anni
SELEta<30(Persone)
SELECT *FROM PersoneWHERE Eta < 30
Proiezione, senza selezione
Nome e reddito di tutte le persone
PROJNome, Reddito(Persone)
SELECT Nome, Reddito FROM Persone
Condizione complessa
SELECT *FROM PersoneWHERE reddito > 25 and (eta < 30 or eta > 60)
EVITARE I DUPLICATI
Si consideri la seguente interrogazione
SELECT CasaEdFROM Catalogo
Se una casa editrice è presente nel catalogo con 1000 libri, il suo nome comparirà 1000 volte nel risultato
Se vogliamo evitare che ciò avvenga, scriveremo
SELECT DISTINCT CasaEdFROM Catalogo
che ha come risultato le case editrici presenti nel catalogo, rappresentate
una sola volta
DISTINCT
In generale la specifica DISTINCT nella clausola SELECT elimina i duplicati dal risultato
La forma generale di un interrogazione SQL che abbiamo visto fin qui è quindi la seguente:
SELECT [DISTINCT] Attributo1,Attributo2,...
FROM Relazione1,Relazione2,...
[ WHERE Condizione]
dove le parti racchiuse tra parentesi quadre possono mancare
L’uso di *
Nella clausola SELECT si può specificare * in luogo della lista di attributi; in tal caso il risultato contiene tutti gli attributi delle relazioni specificate nella clausola FROM.
L’asterisco (*) di select * indica al database di fornire TUTTE le colonne associate alla tabella
SELECT *
FROM Catalogo
WHERE CasaEd = “Feltrinelli”
Restituisce come risultato
Le parole SELECT e FROM consentono a una query di caricare dei dati.
La parola chiave DISTINCT limita l’output delle query poiché consente di escludere i valori duplicati di una colonna.
E’ possibile creare una query generica e includere tutte le colonne con l’istruzione SELECT *. E’ anche possibile selezionare solo alcune colonne e anche modificare l’ordine in cui devono essere presentate.
RIEPILOGO
Obiettivi ampliare la query con qualche nuovo termine introdurre gli operatori.
In particolare impareremo a: capire cos’è una espressione e come si utilizza capire cos’è una condizione e come si utilizza familiarizzare con la clausola WHERE imparare ad usare gli operatori aritmetici, di confronto, di caratteri,
logici e di insiemi conoscere altri utili operatori
ESPRESSIONI, CONDIZIONI E OPERATORI
ESPRESSIONI
La definizione di espressione è semplice: un’espressione restituisce un valore
Nella seguente istruzione, NOME, INDIRIZZO, TELEFONO E RUBRICA sono espressioni:
SELECT NOME, INDIRIZZO, TELEFONO, RUBRICAFROM RUBRICA;
NOME è
La seguente espressione:WHERE NOME = ‘ROSSI’
contiene una condizione di una espressione booleana. Questa condizione potrà essere TRUE (vera) o FALSE (falsa) rispettivamente se la colonna NOME contiene ROSSI oppure no.
CONDIZIONI - I Tutte le volte che si vuole trovare un particolare elemento o gruppo di
elementi in un database, occorre specificare una o più condizioni. Le condizioni sono introdotte dalla clausola WHERE.
nell’esempio precedente la condizione è NOME = ‘ROSSI’. Per trovare tutti gli impiegati che hanno lavorato più di 100 ore la condizione potrebbe essere: NUMERODIORE > 100
Le condizioni consentono di effettuare query selettive. Nella forma più comune includono una variabile, una costante e un operatore di confronto. Variabile………………………….. NOME Costante…………………………..’ROSSI’ Operatore di confronto………. >
Per scrivere una query condizionale bisogna conoscere la clausola WHERE e gli operatori. La condizione presente nella clausola WHERE può avere una struttura molto complessa
CONDIZIONI - II
In generale le condizioni sono formate combinando predicati con gli operatori booleani and, or e not
Predicato: è una condizione semplice del tipo E1 cfr E2 ove: cfr è un operatore di confronto, cioè uno degli operatori
= < > <= >= <> (diverso) E1 ed E2 sono espressioni, che possono essere attributi, costanti
oppure espressioni formate con gli usuali operatori aritmetici. Molto spesso E1 è un attributo. E2 può essere un comando SELECT
Esempi · AnnoEd > 1980 and CasaEd = “Feltrinelli” · AnnoEd = 2000 and (CasaEd = “Einaudi” or CasaEd = “Mondadori”)
I predicati hanno valore true (vero) oppure false (falso).
TABELLA DI VERITA’
Gli operatori booleani rispettano le seguenti tabelle di verità:
true and true = true
true or true = true
not true = false
true and false = false
true or false = true
not false = true
false and false = false
false or false = false
LA CLAUSOLA WHERE
La sintassi della clausola WHERE è la seguente:
WHERE <condizione di ricerca> La condizione presente nella clausola WHERE è ottenuta combinando
predicati con gli operatori booleani. Gli attributi che compaiono nei predicati devono appartenere alle relazioni presenti nella clausola FROM
La clausola WHERE rende selettive le query, senza questa clausola la query visualizzerebbe tutti i record della tabella
Consideriamo il solito schema di relazione Catalogo e una sua istanza
LA CLAUSOLA WHERE
SELECT Titolo, CasaEdFROM Catalogo
WHERE Anno = 2001 and CasaEd = “Einaudi”
SELECT Titolo, CasaEdFROM Catalogo
WHERE Anno = 2001 or CasaEd = “Einaudi”
SELECT Titolo, CasaEdFROM Catalogo
WHERE Anno = 2000 and CasaEd <> “Feltrinelli”
ESEMPIO
SELECT CasaEd, AnnoFROM Catalogo
WHERE Titolo = “L’amante” andAnno = (SELECT max(Anno) FROM Catalogo WHERE Titolo =
“L’amante” )
Viene dapprima calcolata la SELECT tra parentesi, ed il suo risultato viene utilizzato per valutare la condizione; La SELECT esterna restituisce come risultato la CasaEd e L’Anno della più recente edizione dell’ Amante presente nel Catalogo
In questo esempio è stato fatto uso di una struttura detta SOTTOSELECT , o SELECT annidata. Questa ha lo scopo di estrarre dal DB un valore da utilizzare in una espressione. Si osservi che la Sottoselect ha come risultato un singolo valore, altrimenti il confronto non si può effettuare
E’ un potente gruppo di strumenti a base della conoscenza del linguaggio SQL
Gli operatori sono gli elementi utilizzati all’interno delle espressioni per specificare le condizioni necessarie a caricare i dati.
Possono essere divisi nei seguenti gruppi:
aritmetici di confronto di caratteri logici di insieme
GLI OPERATORI
Non funziona con i tipi di dati che hanno cifre decimali
I primi quattro operatori si spiegano da soli.
L’operatore modulo restituisce il resto di una divisione.
Ad esempio:
5 % 2 = 1
6 % 2 = 0
1. + (somma)
2. - (sottrazione)
3. / (divisione)
4. * (moltiplicazione)
5. % (modulo o resto)
GLI OPERATORI ARITMETICI
GLI OPERATORI ARITMETICI
2 * 6 + 9 / 3 vale 12 + 3 = 15
mentre l’espressione
2 * (6 + 9) / 3 vale 2 * 15 / 3 = 10
Se vengono inseriti più operatori aritmetici in una espressione senza parentesi, essi vengono valutati nell’ordine: moltiplicazione, divisione, modulo, somma e sottrazione.
Ad esempio:
SQL> SELECT * FROM PREZZO
ELEMENTO PREZZOINGROSSO
Pomodori 3,40
Patate 5,10
Banane 6,70
Rape 4,50
Arance 8,90
Mele 2,30
SQL> SELECT ELEMENTO, PREZZOINGROSSO, PREZZOINGROSSO + 1.50 FROM PREZZO
ELEMENTO PREZZOINGROSSO PREZZOINGROSSO + 1.50
Pomodori 3,40 4,90
Patate 5,10 6,60
Banane 6,70 8,20
Rape 4,50 6,00
Arance 8,90 10,40
Mele 2,30 4,80
La terza colonna (PREZZOINGROSSO + 1,50) non si trova nella tabella originale (in entrambi i casi sono state selezionate con il carattere * tutte le colonne).
SQL consente di creare colonne virtuali o derivate combinando o modificando le colonne esistenti.
OPERATORI ARITMETICI: SOMMA (+)
E’ possibile assegnare una intestazione più comprensibile alla nuova colonna:
ELEMENTO PREZZOINGROSSO PREZZODETTAGLIO
Pomodori 3,40 4,90
Patate 5,10 6,60
Banane 6,70 8,20
Rape 4,50 6,00
Arance 8,90 10,40
Mele 2,30 3,80
SQL> SELECT ELEMENTO, PREZZOINGROSSO,
(PREZZOINGROSSO + 1.50) PREZZODETTAGLIO FROM PREZZO
OPERATORI ARITMETICI: SOMMA (+)
L’operatore meno svolge due funzioni, la prima è quella di cambiare segno ad un numero:
SQL> SELECT * FROM MINMAX
REGIONE TEMPMAX TEMPMIN
Piemonte -4 10
Toscana 4 13
Sicilia 10 19
Lombardia -2 9
Friuli -3 8
REGIONE TEMPMAX TEMPMIN
Piemonte 4 -10
Toscana -4 -13
Sicilia -10 -19
Lombardia 2 -9
Friuli 3 -8
SQL> SELECT REGIONE, -TEMPMAX, -TEMPMIN FROM MINMAX
OPERATORI ARITMETICI: SOTTRAZIONE (-)
OPERATORI ARITMETICI: SOTTRAZIONE (-)
REGIONE MINIME MASSIME DIFFERENZE
Piemonte -4 10 14
Toscana 4 13 9
Sicilia 10 19 9
Lombardia -2 9 11
Friuli -3 8 11
SQL> SELECT REGIONE,
TEMPMAX MINIME,
TEMPMIN MASSIME,
(TEMPMIN - TEMPMAX) DIFFERENZA
FROM MINMAX;
Oltre che aver creato la nuova colonna questa query ha corretto (solo sullo schermo) i nomi di quelle errate.
La seconda (e ovvia) funzione dell’operatore meno è quella di sottrarre i valore di una colonna da quelli di un’altra colonna.
Ad esempio
L’operatore divisione ha un solo significato, per vedere gli effetti di una vendita a metà prezzo basta digitare la seguente istruzione:
SQL> SELECT ELEMENTO PRODOTTO, PREZZOINGROSSO, (PREZZOINGROSSO/2) PREZZOVENDITA FROM PREZZO
ELEMENTO PREZZOINGROSSO PREZZOVENDITA
Pomodori 3,40 1,70
Patate 5,10 2,55
Banane 6,70 3,35
Rape 4,50 2,25
Arance 8,90 4,45
Mele 2,30 1,15
OPERATORI ARITMETICI: DIVISIONE (/)
Anche l’’operatore moltiplicazione è semplice da usare, ad esempio questa query visualizza l’effetto di uno sconto del 10% sui prezzi di tutti i prodotti:
SQL> SELECT ELEMENTO PRODOTTO, PREZZOINGROSSO, (PREZZOINGROSSO*0.9) NUOVOPREZZO FROM PREZZO;
ELEMENTO PREZZOINGROSSO NUOVOPREZZO
Pomodori 3.40 3.06
Patate 5.10 4.59
Banane 6.70 6.03
Rape 4.50 4.05
Arance 8.90 8.01
Mele 2.30 2.07
OPERATORI ARITMETICI: MOLTIPLICAZIONE (*)
L’operatore modulo restituisce il resto intero di una operazione di divisione.
Esempio:
SQL> SELECT * FROM RESTI
NUMERATORE DENOMINATORE
10 5
8 3
23 9
1024 16
E’ possibile creare una nuova colonna, RESTO, dove registrare il resto della divisione tra NUMERATORE e DENOMINATORE
SQL> SELECT NUMERATORE, DENOMINATORE,
(NUMERATORE % DENOMINATORE) RESTO
FROM RESTI NUMERATORE DENOMINATORE RESTO
10 5 0
8 3 2
23 9 5
1024 16 0
OPERATORI ARITMETICI: MODULO (%)
Questi operatori confrontano le espressioni e restituiscono uno di questi tre valori: TRUE, FALSE, Unkown. I primi due sono semplici da spiegare, TRUE significa vero e FALSE significa falso, il terzo, Unknow, identifica l’assenza di dati in una colonna, cioè NULL.
Molte implementazioni SQL cambiano Unknown in FALSE e forniscono un operatore speciale, IS NULL, per verificare la condizione NULL (assenza di dati).
SQL> SELECT * FROM PREZZO WHERE PREZZOINGROSSO = NULL;No row selected
SQL> SELECT * FROM PREZZO WHERE PREZZOINGROSSO IS NULL; ELEMENTO PREZZOINGROSSO
Limoni
Nel database la colonna prezzoingrosso della riga Limoni non contiene dati (non è zero)
OPERATORI DI CONFRONTO
Nella clausola WHERE il segno uguale è l’operatore di confronto
più utilizzato, molto comodo per selezionare un valore tra tanti.
SQL> SELECT * FROM AMICI;
COGNOME NOME CITTA DATA DI NASCITA TELEFONO
ROSSI ALE MILANO 1/1/1970 02 3425678
BIANCHI SABY TORINO 25/5/1985 011 6707221
BROWN JO PISA 12/10/1968 050 880245
NERI ALE BOLOGNA 13/11/1986 051 6711
SQL> SELECT * FROM AMICI WHERE NOME = ‘ALE’;COGNOME NOME CITTA DATA DI
NASCITATELEFONO
ROSSI ALE MILANO 1/1/1970 02 3425678
NERI ALE BOLOGNA 13/11/1986 051 6711
SQL> SELECT * FROM AMICI WHERE NOME = ‘Ale’; no row selected.
OPERATORI DI CONFRONTO: =
questi operatori operano nel seguente modo modo:
SQL> SELECT * FROM PREZZO;
ELEMENTO PREZZOINGROSSO
Pomodori 3.40
Patate 5.10
Banane 6.70
Rape 4.50
Arance 8.90
Mele 2.30
SQL> SELECT * FROM PREZZO
WHERE PREZZOINGROSSO > 4.50;
ELEMENTO PREZZOINGROSSO
Patate 5.10
Banane 6.70
Arance 8.90
SQL> SELECT * FROM PREZZO
WHERE PREZZOINGROSSO >= 4.50;
ELEMENTO PREZZOINGROSSO
Patate 5.10
Banane 6.70
Rape 4.50
Arance 8.90
Non si usano apici per racchiudere il numero 4.50
OPERATORI DI CONFRONTO: > , >=
questi operatori operano in senso inverso al precedente:
COGNOME NOME CITTA DATA DI NASCITA
TELEFONO
ROSSI ALE MILANO 1/1/1970 02 3425678
BIANCHI SABY TORINO 25/5/1985 011 6707221
BROWN JO PISA 12/10/1968 050 880245
NERI ALE BOLOGNA 13/11/1986 051 6711
COGNOME NOME CITTA DATA DI NASCITA TELEFONO
ROSSI ALE MILANO 1/1/1970 02 3425678
BROWN JO PISA 12/10/1968 050 880245
SQL> SELECT * FROM AMICI;
SQL> SELECT * FROM AMICI WHERE CITTA <= ‘MILANO’ ;
OPERATORI DI CONFRONTO: <, <=
Operatore di disuguaglianza: consente di trovare dati escludendone altri,
cioè il simbolo (<>) oppure (!=) si legge “diverso da”.
Per trovare gli amici tranne ALE (cioè con il nome diverso da ALE):
COGNOME NOME CITTA DATA DI NASCITA TELEFONO
BIANCHI SABY TORINO 25/5/1985 011 6707221
BROWN JO PISA 12/10/1968 050 880245
In molte implementazione SQL è indifferente usare la forma (<>) anzichè (!=)
SQL> SELECT * FROM AMICI WHERE NOME <> ‘ALE’;
OPERATORI DI CONFRONTO: <>, !=
NOME POSIZIONE NUMEROPARTE
FEGATO DESTRA-ADDOME 1
CUORE PETTO 2
FARINGE GOLA 3
VERTEBRE CENTRO-DORSO 4
INCUDINE ORECCHIO 5
RENE DORSO 6
OPERATORI DI CARATTERE
Consentono di manipolare il modo in cui debbono essere rappresentate le stringhe durante la preparazione delle condizioni che selezionano i dati.
Come fare a trovare tutte le parti che si trovano nella zona dorsale del corpo?
Osservando la tabella è possibile
individuarne due, ma hanno nomi differenti.
SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘%DORSO%’;
NOME POSIZIONE NUMEROPARTE
VERTEBRE CENTRO-DORSO 4
RENE DORSO 6
SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘DORSO%’;
NOME POSIZIONE NUMEROPARTE
RENE DORSO 6
SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘%DORSO’;NOME POSIZIONE NUMEROPARTE
VERTEBRE CENTRO-DORSO 4
OPERATORI DI CARATTERE: LIKE
L’operatore LIKE consente di estrarre dati che somigliano ad un certo schema
SQL> SELECT * FROM PARTI WHERE NOME LIKE ‘F%’;
NOME POSIZIONE NUMEROPARTE
FEGATO DESTRA-ADDOME 1
FARINGE GOLA 3
SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘f%’;no rows selected.
L’operatore like è sempre sensibile ai caratteri minuscoli/maiuscoli
OPERATORI DI CARATTERE: LIKE
Come fare a trovare tutte le parti che iniziano per ‘F’? Osservando la tabella è possibile individuarne due, ma hanno nomi differenti.
NOME POSIZIONE NUMEROPARTE
FEGATO DESTRA-ADDOME 1
CUORE PETTO 2
FARINGE GOLA 3
VERTEBRE CENTRO-DORSO 4
INCUDINE ORECCHIO 5
RENE DORSO 6
REGOLE
A LIKE maschera A NOT LIKE maschera
Controlla che il valore dell’attributo A sia o non sia conforme alla maschera. maschera è una sequenza qualunque di caratteri che può contenere i caratteri speciali “ - “ e “ % ”
Una parola è conforme alla maschera se · I caratteri della maschera diversi da – e da % coincidono con quelli
della parola. · Al carattere – nella maschera corrisponde un qualunque carattere
della parola · Al carattere % nella maschera corrisponde una qualunque sequenza,
anche vuota, di caratteri nella parola
ESEMPIO
? CodiceCliente, Cognome e Nome dei Clienti il cui Codice contiene dalla quarta posizione in avanti i caratteri MRC75
SELECT CodiceCliente, Cognome, Nome
FROM Clienti
WHERE CodF LIKE ---MRC75%
ad es. BCEMRC7548, 123MRC75, j23MRC75e6732 sono tutte parole conformi alla maschera ---MRC75%
SQL> SELECT NOME || COGNOME NOMECOMPLETO FROM AMICI;
COGNOME NOME CITTA PROVINCIA TELEFONO
ROSSI ALE MILANO MI 02 3425678
BIANCHI SABY TORINO TO 011 6707221
BROWN JO PISA PI 050 880245
NERI ALE BOLOGNA BO 051 6711
Il simbolo || serve a concatenare due stringhe:
NOMECOMPLETO
ROSSI ALE
BIANCHI SABY
BROWN JO
NERI ALE
L’OPERATORE DI CONCATENAZIONE (||)
Negli esempi precedenti è stato effettuato sempre un controllo alla volta. Questo metodo va bene per i casi semplici, ma come fareste a trovare tutti quei dipendenti i cui nomi iniziano con la lettera ‘B’ e che hanno più di 50 giorni di ferie?
Gli operatori logici separano due o più condizioni nella clausola WHERE di un’istruzione SQL.
Essi sono: AND OR NOT
OPERATORI LOGICI
OPERATORI LOGICI: AND
COGNOME NUMDIP ANNI FERIEGODUTE
ABITA 101 2 4
BACCHI 104 5 23
BLESSI 107 8 45
BOLIVAR 233 4 80
BOLDI 210 15 100
COSTALES 211 10 78
L’operatore logico AND indica che entrambe le espressioni che si trovano ai suoi lati devono essere soddisfatte per restituire il valore TRUE (vero). Se una solo delle espressioni non è soddisfatta l’operatore AND restituisce FALSE.Ad esempio, per sapere quali impiegati hanno lavorato per l’azienda per 5 anni o meno ed hanno utilizzato più di 20 giorni di ferie, si può scrivere:
SQL> SELECT *FROM FERIEWHERE ANNI <= 5 ANDFERIEGODUTE > 20;
COGNOME NUMDIP ANNI FERIEGODUTE
BACCHI 104 5 23
BOLIVAR 233 4 80
COGNOME NUMDIP ANNI FERIEGODUTE
ABITA 101 2 4
BACCHI 104 5 23
BLESSI 107 8 45
BOLIVAR 233 4 80
BOLDI 210 15 100
COSTALES 211 10 78
L’operatore logico OR puo’ essere utilizzato per combinare una serie di condizioni. Se una di queste è soddisfatta viene restituito TRUE
SQL> SELECT *FROM FERIEWHERE COGNOME LIKE ‘BO
%’ORCOGNOME LIKE ‘CO%’;COGNOME NUMDIP ANNI FERIEGODUTE
BOLDI 210 15 100
BOLIVAR 233 4 80
COSTALES 211 10 78
L’operatore OR richiede che una soltanto delle condizioni sia vera affinchè dati possano essere restitutiti
OPERATORI LOGICI: OR
COGNOME NUMDIP ANNI FERIEGODUTE
ABITA 101 2 4
BACCHI 104 5 23
BLESSI 107 8 45
BOLIVAR 233 4 80
BOLDI 210 15 100
COSTALES 211 10 78
L’operatore logico NOT ha il compito di invertire il significato di una condizione
SQL> SELECT *FROM FERIEWHERE COGNOME NOT LIKE ‘B
%’;
COGNOME NUMDIP ANNI FERIEGODUTE
ABITA 101 2 4
COSTALES 211 10 78
OPERATORI LOGICI: NOT
L’operatore UNION restituisce il risultato di due query escludendo le righe duplicate.
Esempio: quante persone diverse giocano in entrambe le squadre?
COGNOME
ABITA
BRAVO
CARLINI
DECCA
ESTERLE
FUNDARI
GIANI
COGNOME
ABITA
BACCO
CARLINI
DINI
ESTERLE
FALCONI
GIANI
SQL> SELECT COGNOME FROM CALCETTOUNIONSELECT COGNOME FROM CALCIO;
calcetto
COGNOME
ABITA
BACCO
BRAVO
CARLINI
DECCA
DINI
ESTERLE
FALCONI
FUNDARI
GIANI
A + B - C
A BC
Sono stati esclusi i doppioni
calcio
OPERATORI DI INSIEMI: UNION
L’operatore UNION ALL restituisce il risultato di due query incluse le righe duplicate.
COGNOME
ABITA
BRAVO
CARLINI
DECCA
ESTERLE
FUNDARI
GIANI
COGNOME
ABITA
BACCO
CARLINI
DINI
ESTERLE
FALCONI
GIANI
SQL> SELECT COGNOME FROM CALCETTOUNION ALLSELECT COGNOME FROM CALCIO;
calcio
calcetto
COGNOME
ABITA
BRAVO
CRLINI
DECCA
ESTERLE
FUNDARI
GIANI
ABITA
BACCO
CARLINI
DINI
ESTERLE
FALCONI
GIANI
Sono incluse tutte le righe
Esempio: quante persone giocano al calcetto e quante al calcio?
A + B
A BC
OPERATORI DI INSIEMI: UNION
L’operatore INTERSECT restituisce soltanto le righe che vengono trovate in entrambe le query
COGNOME
ABITA
BRAVO
CARLINI
DECCA
ESTERLE
FUNDARI
GIANI
COGNOME
ABITA
BACCO
CARLINI
DINI
ESTERLE
FALCONI
GIANI
SQL> SELECT COGNOME FROM CALCETTOINTERSECTSELECT COGNOME FROM CALCIO;
calcio
calcetto
COGNOME
ABITA
CRLINI
ESTERLE
GIANI
Sono inclusi solo le righe comuni
Esempio: quali persone giocano in entrambe le squadre?
C
A BC
OPERATORI DI INSIEMI: INTERSECT
L’operatore MINUS restituisce le righe della prima query che non sono presenti nella seconda
COGNOME
ABITA
BRAVO
CARLINI
DECCA
ESTERLE
FUNDARI
GIANI
COGNOME
ABITA
BACCO
CARLINI
DINI
ESTERLE
FALCONI
GIANI
SQL> SELECT COGNOME FROM CALCETTOMINUSSELECT COGNOME FROM CALCIO;
calcio
calcetto
COGNOME
BACCCO
DINI
FALCONI
Seleziona quelli che giocano solo al calcetto
Esempio: quali persone giocano solo al calcetto?
A - C
A BC
OPERATORI DI INSIEMI: MINUS
Cognome Filiale EtàMatricola
Neri Milano 455998Rossi Roma 327309
Bruni Milano NULL9553
Impiegati
Neri Milano 455998Bruni Milano NULL9553
SEL Età > 40 OR Età IS NULL (Impiegati)
Neri Milano 455998
Bruni Milano NULL9553
Gestione dei valori nulli
Gli impiegati la cui età è o potrebbe essere maggiore di 40
select *from impiegati
where eta > 40 or eta is null
Interrogazioni su più relazioni
Nella clausola FROM possono essere presenti più relazioni. Ciò è necessario quando le informazioni per eseguire l’interrogazione sono distribuite su relazioni diverse, vale a dire: quando gli attributi presenti nella clausola SELECT o nella clausola WHERE appartengono a relazioni diverse
Si consideri il seguente schema relazionale
Film(CodFilm,Titolo,Regista,Anno)
Attori(CodFilm*, Attore)
e supponiamo di volere i titoli dei film in cui recita C. Eastwood. L’attributo Titolo è nella relazione Film mentre l’attributo Attore è nella relazione Attori. Occorre pertanto visitare entrambe le relazioni
ESEMPIO - I
.Supponiamo di avere le seguenti istanze di relazione:
ESEMPIO - II
Il DBMS esegue la seguente procedura: Viene costruita una relazione concatenando le ennuple di Film e di
Attori che sono in associazione (tali che CodFilm=CodFilm*)
Vengono prese in considerazione solo le ennuple in cui l’attributo Attore ha valore C.Eastwood.
ESEMPIO - III
Viene prelevato l’attributo Titolo
Questa interrogazione in SQL si scrive:SELECT Titolo
FROM Film, AttoriWHERE Film.CodFilm = Attori.CodFilm
and Attore = “C.Eastwood”
dove Film.CodFilm ed Attori.CodFilm rappresentano il valore di CodFilm nella relazione Film e nella relazione Attori rispettivamente.
La condizione Film.CodFilm = Attori.CodFilm serve ad esprimere il collegamento tra le ennuple di Film e quelle di Attori. Solo in questo modo C.Eastwood sarà associato ad un film in cui recita.
ESEMPIO - IV
Per motivi di chiarezza e per evitare ambiguità, è opportuno specificare, per ogni attributo, la relazione cui appartiene , con la notazione Relazione.Attributo. Pertanto l’interogazione precedente diventa
SELECT Film.TitoloFROM Film, Attori
WHERE Film.CodFilm = Attori.CodFilmand Attori.Attore = “C.Eastwood”
Per motivi di brevità è opportuno assegnare nella clausola FROM un nome abbreviato alle relazioni, da utilizzare nelle altre clausole dell’interrogazione:
SELECT F.TitoloFROM Film F, Attori A
WHERE F.CodFilm = A.CodFilmand A.Attore = “C.Eastwood”
JOIN
Sottolineiamo il fatto che fra le due relazioni deve esistere un collegamento (una chiave esterna in una relazione, chiave primaria nell’altra), e che nella clausola WHERE dell’interrogazione deve essere esplicitato tale collegamento. Osserviamo che nella clausola FROM può essere presente un qualunque numero di relazioni, purché queste siano collegate tra di loro, e nella clausola WHERE siano specificati tutti i collegamenti.
L’operazione che associa le ennuple di due relazioni (ad es. le ennuple di Film con quelle di Attori) è quella di join, e la condizione di eguaglianza tra la chiave esterna di una relazione e la chiave primaria di un’altra (ad es Film.CodFilm = Attori.CodFilm) è detto predicato di join.
SQL e algebra relazionale
R1(A1,A2) R2(A3,A4)
select R1.A1, R2.A4 from R1, R2where R1.A2 = R2.A3
prodotto cartesiano (FROM) selezione (WHERE) proiezione (SELECT)
PROJ A1,A4 (SELA2=A3 (R1 JOIN R2))
ESEMPIO DIFFICILE
Si consideri lo schema relazionale:
FILM(CodiceDVD, Titolo, Regista, Anno)
ATTORI(Nome, Nazionalità)
RECITA(CodiceDVD*, Nome*, Personaggio)
DVD(Collocazione, CodiceDVD*, DataNoleg, CodiceCliente*)
CLIENTI(CodiceCliente, Cognome, Nome, Indirizzo, Telefono)
e si voglia estrarre Cognome e Nome dei Clienti che hanno noleggiato dvd relativi a film in cui recitano attori francesi
ESEMPIO DIFFICILECognome e Nome sono attributi della relazione Clienti.
Clienti è collegata a DVD tramite CodiceCliente,
DVD è collegata a Film tramite CodiceDVD, Film è collegato a Recita tramite CodiceDVD, ed infine Recita è collegato con Attori tramite Nome; finalmente in Attori troviamo l’attributo Nazionalità, e possiamo quindi verificare la condizione di ricerca.
In SQL tale interrogazione è piuttosto fastidiosa da scrivere:
SELECT Cl.Cognome, Cl.Nome
FROM Clienti Cl, DVD D, Film F, Recita R, Attori A
WHERE Cl.CodiceCliente = D.CodiceCliente
and D.CodiceDVD = F.CodiceDVD
and F.CodiceDVD = R.CodiceDVD
and R.Nome = A.Nome
and A.Nazionalità = “francese”
164
ESEMPIO DIFFICILE
Può essere utile, per individuare le relazioni da specificare nella clausola FROM, considerare lo schema E-R rappresentato dallo schema relazionale: da tale schema risulta evidente che per collegare Clienti con Attori occorre attraversare tutte le classi intermedie.
Dvd
Nome Età
Persone
Reddito
Andrea 27
Maria 55
Anna 50
Filippo 26
Luigi 50
Franco 60
Olga 30
Sergio 85
Luisa 75
Aldo 25
21
42
35
30
40
20
41
35
87
15
MadreMaternità Figlio
Luisa
Anna
Anna
Maria
Maria
Luisa
Maria
Olga
Filippo
Andrea
Aldo
Luigi
PadrePaternità Figlio
Luigi
Luigi
Franco
Franco
Sergio
Olga
Filippo
Andrea
Aldo
Franco
ALTRI ESEMPI - Selezione, proiezione e join
I padri di ogni persona
PROJPadre(paternita JOIN Figlio =Nome persone)
Il nome e l‘età dei figli di Luisa
PROJNome, eta(
SELMADRE=„Luisa“ (maternita)
JOIN Figlio =Nome
persone
) SELECT distinct padre FROM persone, paternita WHERE figlio = nome
SELECT distinct padre FROM persone, paternita WHERE figlio = nome and madre=‚Luisa‘
Funzioni di aggregazione
SQL consente di estrarre dalla Base di Dati informazioni che non sono esplicitamente presenti, ma si ottengono da quelle presenti utilizzando opportune funzioni dette funzioni di aggregazione.
Studenti (Matricola, Nome, CorsodiLaurea)
Esami (Matricola*, CodiceAF*, Voto)
AttivitàFormativa(CodiceAF, NomeAF, CFU)
Le funzioni di aggregazione consentono di estrarre dal DB informazioni quali il numero di esami sostenuti da un determinato studente, il numero di studenti che hanno sostenuto un determinato esame, valori medi, massimi, minimi ecc.
COUNT
? Numero di esami sostenuti dallo studente con Matricola 123SELECT Count(*)FROM EsamiWHERE Matricola=123
Count(*) indica un conteggio: vengono contate le ennuple (ricordiamo che * indica l’intera ennupla) di Esami che soddisfano alla condizione Matricola=123. Restituisce il numero di righe che soddisfano la condizione specificata nella clausola WHERE
Al risultato di una funzione di aggregazione può essere dato un nome tramite il costrutto as:
SELECT Count(*) as Numero_Esami_AA252FROM Esami EWHERE CodiceMateria=AA252
SUM
? Numero di crediti acquisiti dallo studente con Matricola 123
SELECT Sum(CFU) as Crediti_di_123
FROM Esami E, AttivitàFormative A
WHERE E.CodiceAF = A.CodiceAF
and E.Matricola=123 Sum(CFU) indica l’ordinaria somma aritmetica dei valori (che devono
essere numerici) dell’attributo CFU. Consideriamo la seguente istanza del DB
SUM
Il join E.CodiceAF = A.CodiceAF da luogo alla seguente relazione
La condizione E.Matricola=123 da luogo alla seguente relazione
SUM
La funzione Sum esegue la somma dei valori dell’attributo CFU e si ottiene il risultato desiderato
L’uso delle funzioni di aggregazione è limitato al caso in cui il risultato sia costituito da un solo valore; non possono cioè essere presenti allo stesso tempo nella clausola SELECT sia attributi che funzioni di aggregazione. La seguente interrogazione, ad es. è sbagliata (del resto avrebbe poco senso)
SELECT Voto, Count(*)FROM EsamiWHERE Matricola=123
MAX e MIN
? Il voto più basso dello studente con Matricola 123SELECT Min(Voto)FROM EsamiWHERE Matricola=123
Il voto più alto dello studente con Matricola 123SELECT Max(Voto)FROM EsamiWHERE Matricola=123
La funzione MAX (MIN) serve a trovare il valore massimo (€minimo) di una colonna.
Riassumendo
Le funzioni di aggregazione previste da SQL sono: avg media aritmetica (valori numerici) count numero di valori max valore massimo min valore minimo sum somma (valori numerici)
Min e Max, quando sono applicati a valori non numerici, danno rispettivamente il primo e l’ultimo valore nell’ordine alfabetico.
ORDER BY
La clausola ORDER BY, specificata dopo SELECT FROM WHERE fa sì che il risultato sia ordinato; si può scegliere fra ordinamento crescente (se non si specifica nulla), o decrescente (se si specifica desc).
L’ordinamento può essere fatto anche su più attributi.
Nome e reddito delle persone con meno di trenta anni in ordine alfabetico
select nome, reddito
from persone
where eta < 30
order by nome
Persone
Nome Reddito
Andrea 21
Aldo 15
Filippo 30
Persone
Nome Reddito
Andrea 21
Aldo 15
Filippo 30
select nome, redditofrom personewhere eta < 30
select nome, redditofrom personewhere eta < 30order by nome
NULL
A IS NULL , A IS NOT NULL controlla che l’ attributo A abbia o non abbia valore nullo
? La collocazione dei dvd non noleggiatiSELECT CollocazioneFROM DVDWHERE CodiceCliente is null
? La collocazione dei dvd noleggiati dopo il 1/1/08SELECT CollocazioneFROM DVDWHERE DataNoleg is not null and DataNoleg > 1/1/05
L’uso del predicato is [not] null è l’unico modo per stabilire se una dvd è o non è noleggiat0.
Sistemi per il recupero delle informazioni
ESERCIZI
ESERCIZIO 1
Nell’ esercizio che segue sono dati degli schemi di Basi di Dati relazionali, e delle richieste di informazioni da estrarre dalle Basi di Dati.
Esprimere tali richieste con interrogazioni SQL.
SCHEMA RELAZIONALE:
ATTORI (CodAttore, Nome, AnnoNascita, Nazionalità);RECITA (CodAttore*, CodFilm*)FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità, Regista, Genere)PROIEZIONI (CodProiezione, CodFilm*, CodSala*, Incasso, DataProiezione)SALE (CodSala, Posti, Nome, Città)
ESERCIZIO 1
Scrivere le interrogazioni SQL che restituiscono le seguenti informazioni:
1- Il nome di tutte le sale di Verona
2- Il titolo dei film di F. Fellini prodotti dopo il 1960.
3- Il titolo e la durata dei film di fantascienza giapponesi o francesi prodotti dopo il 1990
4- I titolo dei film dello stesso regista di “Casablanca”
5- Il titolo ed il genere dei film proiettati il giorno di Natale 2004
6- Il titolo dei film in cui recita M. Mastroianni oppure S.Loren
7- Il numero di sale di Messina con più di 60 posti
ESERCIZIO 1
1- Il nome di tutte le sale di Verona
SELECT s.Nome FROM Sale s WHERE s.Città = 'Pisa‘
2- Il titolo dei film di F. Fellini prodotti dopo il 1960.
SELECT f.Titolo FROM Film f WHERE f.Regista = “Fellini” AND f.AnnoProduzione > 1960
3- Il titolo e la durata dei film di fantascienza giapponesi o francesi prodotti dopo il 1990
SELECT f.Titolo, f.Durata FROM Film f WHERE f.Genere=”Fantascienza” and ((f.Nazionalità=”Giapponese” or f.Nazionalità=”Francese”) and f.AnnoProduzione >1990
ESERCIZIO 14- I titolo dei film dello stesso regista di “Casablanca”
SELECT f.Titolo FROM Film f
WHERE f.Regista = (SELECT f.Regista FROM Film f WHERE f.Titolo = “Casablanca”)
5- Il titolo ed il genere dei film proiettati il giorno di Natale 2004
SELECT DISTINCT f.Titolo, f.Genere FROM Film f, Proiezioni p
WHERE p DataProiezione =25/12/04 and f.CodFilm=p.CodFilm
6- Il titolo dei film in cui recita M. Mastroianni oppure S.Loren
SELECT DISTINCT f.Titolo FROM Film f, Recita r, Attore a
WHERE (a.Nome = “M.Mastrianni” OR a.Nome = ”S.Loren”)
AND f.CodFilm = r.CodFilm AND r.CodAttore = a.CodAttore
7- Il numero di sale di Messina con più di 60 posti
SELECT count(*) FROM Sale s
WHERE s.Città = “Messina” and s.Posti > 60
ESERCIZIO 2
SCHEMA RELAZIONALE:
ROMANZI(CodiceR, Titolo, NomeAut*, Anno)
PERSONAGGI(NomeP, CodiceR*, sesso, ruolo)
AUTORI(NomeAut, AnnoN, AnnoM:optional, Nazione)
FILM(CodiceF, Titolo, Regista, Produttore, Anno, CodiceR*)
ESERCIZIO 2
1- Il titolo dei romanzi del 19° secolo 2- Il titolo, l’autore e l’anno di pubblicazione dei romanzi di autori russi,
ordinati per autore e, per lo stesso autore, ordinati per anno di pubblicazione 3- I personaggi principali (ruolo =”P”) dei romanzi di autori viventi. 4. I romanzi dai quali è stato tratto un film con lo stesso titolo del romanzo 5- Il titolo, il regista e l’anno dei film tratti dal romanzo “Robin Hood” 6- Per ogni autore italiano, l’anno del primo e dell’ultimo romanzo.
ESERCIZIO 3
SCHEMA RELAZIONALE:
STUDENTI (Matricola, NomeS, CorsoLaurea*, AnnoN)
CORSIDILAUREA (CorsoLaurea, TipoLaurea, Facoltà)
FREQUENTA (Matricola*, CodCorso*)
CORSI (CodCorso, NomeCorso, CodDocente*)
DOCENTI (CodDocente, NomeD, Dipartimento)
ESERCIZIO 3
1- Il nome e l’anno di nascita degli studenti iscritti a Editoria e Giornalismo, in ordine rispetto al nome
2- Matricola e nome degli studenti di un corso di laurea triennale (tipoLaurea = 'L') che seguono un corso di un docente di nome Anna.
3- Per ogni tipo di laurea, il tipoLaurea e l’età media degli studenti
4- Il codice dei corsi frequentati da più di 5 studenti e tenuti da docenti del Dipartimento di Informatica
5- Per ogni studente della Facoltà di Lettere e Filisofia, la matricola ed il numero di corsi seguiti
6- Matricola e nome degli studenti che non frequentano nessun corso
7- Nome e CodDocente dei docenti che insegnano qualche corso seguito da più di 5 studenti
Esercizi
Dato il seguente schema:
AEREOPORTO(Città, Nazione, NumPiste)
VOLO(IdVolo, GiornoSett, CittaPart, OraPart, CittaArr, OraArr, TipoAereo)
AEREO(TipoAereo, NumPasseggeri, QtaMerci)
In SQL:
1. Creare il Database
2. Città con un aereoporto di cui non è noto il numero di piste
3. Città e orario di partenza dei voli del lunedì
4. Nazione e numero piste dell’aereoporto da cui parte il volo con IdVolo=‘A001’
2 – Città con un aeroporto di cui non è noto il numero di pisteSELECT Citta FROM Aereoporto WHERE NumPiste IS NULL
3 – Città e Orario di partenza dei voli del LunedìSELECT CittaPart, OraPart FROM Volo WHERE GiornoSett=‘LUN’
4 – Nazione e Numero di piste dell’aeroporto da cui parte il volo con
IdVolo=‘A001’SELECT Aeroporto.nazione, Aereoporto.NumPiste FROM Aeroporto, Volo
WHERE Aeroporto.citta=Volo.cittapart AND Volo.IdVolo=‘A001’
ALTRI ESERCIZI
1. Città di partenza, orario di partenza, città di arrivo, orario di arrivo degli aerei con merci>1000
2. Il tipo di aereo e il numero dei passeggeri dei voli che arrivano a Torino
3. Il numero dei voli che partono il venerdì da Francoforte
4. La quantità dei voli che partono da ogni città
5. Le città, numero dei voli in arrivo, nazione, avente numero di volo in arrivo maggiore di 10
6. Il numero dei voli internazionali che partono il lunedì da Torino