Transcript IV Lezione - 6 Novembre - Dipartimento di Matematica
Sistemi per il recupero delle informazioni
RIASSUNTO
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)
RELAZIONE E TABELLA
Orario Insegnamento Analisi matem. I Basi di dati Chimica Fisica I Fisica II Sistemi inform.
Docente Luigi Neri Piero Rossi Nicola Mori Mario Bruni Mario Bruni Piero Rossi Aula N1 N2 N1 N1 N3 N3 Ora 8:00 9:45 9:45 11:45 9:45 8:00 ORARIO (
Insegnamento, Docente, Aula, Ora
)
Relazione e tabella sono sinonimi
SCHEMA
Orario Insegnamento Analisi matem. I Basi di dati Chimica Fisica I Fisica II Sistemi inform.
Docente Luigi Neri Piero Rossi Nicola Mori Mario Bruni Mario Bruni Piero Rossi Aula N1 N2 N1 N1 N3 N3 Ora 8:00 9:45 9:45 11:45 9:45 8:00 ORARIO (
Insegnamento, Docente, Aula, Ora
)
Le colonne della tabella formano lo schema della relazione
ISTANZE
Orario Insegnamento Analisi matem. I Basi di dati Chimica Fisica I Fisica II Sistemi inform.
Docente Luigi Neri Piero Rossi Nicola Mori Mario Bruni Mario Bruni Piero Rossi Aula N1 N2 N1 N1 N3 N3 Ora 8:00 9:45 9:45 11:45 9:45 8:00 ORARIO (
Insegnamento, Docente, Aula, Ora
)
I contenuti delle righe della tabella formano le istanze della relazione
Corsi Corso Basi di dati Sistemi Reti Controlli Docente Rossi Neri Bruni Bruni
DATABASE
Aule Aula Nome DS3 DS3 N3 N3 N3 G G CorsiSedi Corso Sistemi Reti Controlli Aula N3 N3 G Edificio OMI OMI Pincherle Edificio OMI OMI Pincherle Piano Terra Terra Primo Piano Terra Terra Primo Un database e’ un insieme di tabelle
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 classi 2. 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
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
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 interrogazione (
query
).
richiesta
di dati. Tale richiesta prende il nome di 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
LINGUAGGI RELAZIONALI
Come esempio di linguaggi per vediamo l’uso interattivo di basi di dati, relazionali,
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 sintassi per (Structured Query Language), che offre una 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.
Algebra relazionale
Insieme di operatori
su relazioni
che producono relazioni (tabelle) e possono essere composti per svolgere operazioni più complesse
OPERATORI FONDAMENTALI
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
RIDENOMINAZIONE
Paternità Padre Adamo Adamo Abramo Figlio Abele Caino Isacco
Genitore
Padre (Paternità) Adamo Adamo Abramo Figlio Abele Caino Isacco
ESEMPIO DI UNIONE
ESEMPIO DI INTERSEZIONE
ESEMPIO DI DIFFERENZA
proiezione selezione
operatori "ortogonali“ selezione: decomposizione orizzontale proiezione: decomposizione verticale
ESEMPIO PROIEZIONE
cognome e filiale di tutti gli impiegati Matricola 7309 5998 9553 5698 Cognome Neri Neri Rossi Rossi Filiale Napoli Milano Roma Roma Stipendio 55 64 44 64
Cognome, Filiale (Impiegati)
ESEMPIO RESTRIZIONE
Impiegati che • guadagnano più di 50 • guadagnano più di 50 e lavorano a Milano • hanno lo stesso nome della filiale presso cui lavorano
Operatori booleani
Connettivi logici
(AND), (OR), (NOT)
Operatori di confronto
= (uguale)
(maggiore)
(diverso) (minore) (maggiore o uguale)
(minore o uguale)
impiegati che guadagnano più di 50 Impiegati Matricola 7309 5998 Cognome Rossi Neri Filiale Roma Milano Stipendio 55 64 5698 Neri Napoli
Stipendio > 50 (Impiegati)
64
impiegati che guadagnano più di 50 e lavorano a Milano Impiegati Matricola Cognome 5998 Neri Filiale Milano Stipendio 64
Stipendio > 50 AND Filiale = 'Milano' (Impiegati)
Selezione e proiezione
Combinando selezione e proiezione, possiamo estrarre interessanti informazioni da una relazione matricola e cognome degli impiegati che guadagnano più di 50
Matricola 7309 5998 Cognome Rossi Neri Filiale Roma Milano Stipendio 55 64 5698 Neri Napoli
Matricola,Cognome (
Stipendio > 50 64
Numero 1 2 3 4 Voto 25 13 27 28 Numero 1 2 3 4
PRODOTTO (Join)
Numero 1 2 3 4 Candidato Mario Rossi Nicola Russo Mario Bianchi Remo Neri Candidato Mario Rossi Nicola Russo Mario Bianchi Remo Neri Voto 25 13 27 28
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 R 1 si combina con ogni ennupla di R 2 la cardinalita’ del risultato e’ il prodotto delle cardinalita’
Impiegati Impiegato Rossi Neri Bianchi Impiegato Rossi Rossi Neri Neri Bianchi Bianchi
Prodotto cartesiano
Reparto A B B Codice A Impiegati JOIN Reparti Reparto A A B B B B Codice B A B A B Capo Mori Reparti Capo Mori Bruni Mori Bruni Mori Bruni
Sistemi per il recupero delle informazioni
DATABASE MANAGEMENT SYSTEM (DBMS)
COS’E’ UNA BASE DATI
Una base di dati è una raccolta di dati
permanenti
suddivisi in due categorie:
I METADATI I DATI
COS’E’ UNA BASE DATI I METADATI
i metadati, ovvero lo schema della base di dati , sono una raccolta di definizioni che descrivono
la struttura di alcuni insiemi dati, le restrizioni sui valori ammissibili dei dati le relazioni esistenti fra gli insiemi. Lo schema va definito prima di creare i dati ed è indipendente dalle applicazioni che usano la base di dati.
COS’E’ UNA BASE DATI I DATI
i dati, le rappresentazioni dei fatti conformi alle definizioni dello schema, con le seguenti caratteristiche: a) sono organizzati in insiemi omogenei , fra i quali sono definite delle relazioni.
b) sono molti , in assoluto e rispetto ai metadati, e non possono essere gestiti in memoria temporanea; c) sono permanenti , cioè, una volta creati, continuano ad esistere finché non sono esplicitamente rimossi; d) sono accessibili mediante transazioni ; e) sono protetti sia da accesso da parte di utenti non autorizzati, sia da corruzione dovuta a malfunzionamenti hardware e software; f) sono utilizzabili contemporaneamente da utenti diversi.
DataBase Management System (DBMS) - I
Le caratteristiche delle basi di dati sono garantite da un sistema per la gestione di basi di dati ( DBMS, Data Base Management System ), che ha il controllo dei dati e li rende accessibili agli utenti autorizzati.
Un DBMS linguaggi è un sistema centralizzato o distribuito che offre opportuni a) per definire lo schema della base di dati, b) per scegliere le strutture dati per la memorizzazione dei dati, c) per usare la base di dati interattivamente o da programmi.
DataBase Management System (DBMS) - II
Un sistema di gestione di basi di dati è un sistema software in grado di gestire collezioni di dati che siano grandi, condivise e persistenti , assicurando la loro affidabilità e privatezza .
Un DBMS deve essere efficiente ed efficace .
Esempi di prodotti software disponibili sul mercato: Access, DB2, Oracle, Informix, Sybase, SQLServer
I DATABASE SONO...
grandi
dimensioni (molto) maggiori della memoria centrale dei sistemi di calcolo utilizzati
il limite deve essere solo quello fisico dei dispositivi persistenti
hanno un tempo di vita indipendente dalle singole esecuzioni dei programmi che le utilizzano condivisi
ogni organizzazione è divisa in settori o comunque svolge diverse attività . Ciascun settore/attività ha un (sotto) sistema informativo (non necessariamente disgiunto)
Una base di dati e' una risorsa integrata, condivisa fra applicazioni Conseguenze:
Attivita' diverse su dati condivisi: meccanismi di autorizzazione Accessi di più utenti ai dati condivisi:controllo della concorrenza
I DBMS GARANTISCONO…
PRIVATEZZA
Si possono definire meccanismi di autorizzazione
l'utente A è autorizzato a leggere tutti i dati e a modificare quelli sul ricevimento l'utente B è autorizzato a leggere X e a modificare Y
AFFIDABILITA` (per le basi di dati):
resistenza a malfunzionamenti hardware e software
una base di dati è una risorsa pregiata e quindi deve essere conservata a lungo termine
I DBMS DEVONO ESSERE …
EFFICIENTI
Cercano di utilizzare al meglio le risorse di spazio di memoria (principale e secondaria) e tempo (di esecuzione e di risposta)
I DBMS, con tante funzioni, rischiano l'inefficienza e per questo ci sono grandi investimenti e competizione L’efficienza è anche il risultato della qualità delle applicazioni
EFFICACI
Cercano di rendere produttive le attività dei loro utilizzatori, offrendo funzionalità articolate, potenti e flessibili:
il corso è in buona parte dedicato ad illustrare come i DBMS perseguono l'efficacia
FUNZIONALITA’ DEI DBMS
Un DBMS offre specifiche funzionalità per i seguenti scopi: – definizione di basi di dati; – uso dei dati; – controllo dei dati; – amministrazione della base di dati; – distribuzione dei dati.
ARCHITETTURA STANDARD - I
utente utente utente utente utente Livello esterno Livello esterno Livello esterno Livello logico Livello interno database
LINGUAGGI PER DATABASE
Un altro contributo all’efficacia: disponibilità di vari linguaggi e interfacce
linguaggi testuali interattivi (
SQL
)
comandi (SQL) immersi in un linguaggio ospite (Pascal, Java, C ...)
comandi (SQL) immersi in un linguaggio ad hoc, con anche altre funzionalità (p.es. per grafici o stampe strutturate)
con interfacce amichevoli (senza linguaggio testuale)
Controllo dei dati
Una caratteristica molto importante dei DBMS meccanismi offerti per garantire le seguenti è il tipo di proprietà di una base di dati:
integrità affidabilità sicurezza
TRANSAZIONI (PER L‘UTENTE)
Esempi:
versamento presso uno presso sportello bancario
emissione di certificato anagrafico dichiarazione presso l’ufficio di stato civile prenotazione aerea
Due accezioni
Per l'utente:
programma a disposizione, da eseguire per realizzare una funzione di interesse
Per il sistema:
sequenza indivisibile di operazioni
Sistemi per il recupero delle informazioni
VISTE
Viste
Rappresentazioni diverse per gli stessi dati (
)
Relazioni di base: contenuto autonomo Relazioni derivate :
relazioni il cui contenuto è funzione del contenuto di altre relazioni (definito per mezzo di interrogazioni)
Le relazioni derivate possono essere definite su altre derivate Due tipi di relazioni derivate:
viste )
Viste materializzate e virtuali
relazioni derivate memorizzate nella base di dati
vantaggi:
immediatamente disponibili per le interrogazioni
svantaggi:
ridondanti
appesantiscono gli aggiornamenti
sono raramente supportate dai DBMS
relazioni virtuali (o viste ):
sono supportate dai DBMS (tutti)
una interrogazione su una vista viene eseguita "ricalcolando" la vista (o quasi)
Viste, esempio
Afferenza Impiegato Rossi Neri Bianchi Bianchi Reparto A B B B
una vista: Supervisione = PROJ Impiegato, Capo (Afferenza JOIN Direzione) Direzione Reparto A B B Capo Mori Bruni Bruni
Viste, motivazioni
Schema esterno: ogni utente vede solo
ciò che gli interessa e nel modo in cui gli interessa, senza essere distratto dal resto ciò che e' autorizzato a vedere (autorizzazioni)
:
si può semplificare la scrittura di interrogazioni: espressioni complesse e sottoespressioni ripetute
L'utilizzo di viste non influisce sull'efficienza delle interrogazioni
Viste e aggiornamenti, attenzione
Afferenza Impiegato Rossi Reparto A Direzione Reparto A Capo Mori Verdi Supervisione A Impiegato Capo Verdi Mori
Vogliamo inserire, nella vista, il fatto che Lupi ha come capo Bruni; oppure che Belli ha come capo Falchi; come facciamo?
Viste e aggiornamenti
"Aggiornare una vista":
modificare le relazioni di base in modo che la vista, "ricalcolata" rispecchi l'aggiornamento
L'aggiornamento sulle relazioni di base corrispondente a quello specificato sulla vista deve essere univoco
In generale però non è univoco!
Ben pochi aggionamenti sono ammissibili sulle viste
Sistemi per il recupero delle informazioni
IL LINGUAGGIO SQL
52
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 interrogazione. Ora ’70 presso IBM , inizialmente solo come linguaggio di è 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
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
PRIMARY KEY
Matricola CHAR(6) PRIMARY KEY
Matricola CHAR(6), …, PRIMARY KEY (Matricola)
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 references (codice) CDL, on delete no action , create table CDL ( facoltà char (20), nome char (20) not null , ) primary key (codice),
CHIAVI ESTERNE
Quando 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
primaria
un’ennupla
dalla tabella CDL, se il valore della sua chiave è 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.
Vincolo di integrità referenziale
Un vincolo di integrità referenziale (“ foreign key ”) fra gli attributi X di una relazione R 1 e un’altra relazione R 2 impone ai valori su X in R 1 comparire come valori della chiave primaria di R 2 di
Azioni compensative
Esempio:
Viene eliminata una ennupla causando una violazione
Comportamento “standard”:
Rifiuto dell'operazione
Azioni compensative:
Eliminazione in cascata
Introduzione di valori nulli
MODIFICA DEI DATI
Nuovi dati si inseriscono nella tabella con il comando
INSERT.
Ad esempio, per aggiungere una nuova ennupla alla relazione Studenti si comando dà il
INSERT INTO Studenti VALUES ("Tizio", "081575", "MI", “1985”)
Per cambiare invece Matricola "081575", si l’attributo Provincia da “MI” a “TO” per lo studente con dà il comando:
UPDATE Studenti SET Provincia = “TO” WHERE Matricola = "081575"
Per eliminare invece comando: l’ennupla dello studente con matricola "081575", si dà il
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 una query SQL non può essere tradotta in interrogazione o domanda, è 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 particolare ordine più tabelle e restituire i risultati in un 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, CasaEd FROM Catalogo WHERE AnnoEd = 2001
dove
SELECT
,
FROM
SQL.
e
WHERE
sono
parole riservate
del linguaggio
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; anche essere memorizzata nella base di dati o può 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 dell’algebra relazionale.
all’operazione di proiezione
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 semplici, la seguente: è, nei casi più
SELECT Attributo1,Attributo2,...
FROM Relazione1,Relazione2,...
[WHERE Condizione ]
Le parole in maiuscolo sono fisse e specificano le mancare
clausole
parole riservate
del linguaggio SQL, sono dell’ interrogazione; la clausola
WHERE
può Le parole in minuscolo sono
variabili
, e rappresentano le relazioni, gli attributi, le condizioni che riguardano la specifica interrogazione
Selezione e proiezione
Persone Nome e reddito delle persone con meno di trenta anni Nome Età Reddito Andrea 27 21 PROJ Nome, Reddito (SEL Eta<30 (Persone)) Aldo 25 15 Maria 55 42 SELECT Nome, Reddito FROM WHERE Persone Eta < 30 Anna Filippo 50 26 35 30 Luigi 50 40 Franco Olga Sergio Luisa 60 30 85 75 20 41 35 87
Persone Nome Anna 50 21 15 30 Reddito 35 Luigi Franco Olga Sergio Luisa 50 60 30 85 75 40 20 41 35 87
SELECT, abbreviazioni (alias)
SELECT Nome, Reddito FROM WHERE Persone 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 SEL Eta<30 (Persone) SELECT * FROM WHERE Persone Eta < 30
Proiezione, senza selezione
Nome e reddito di tutte le persone PROJ Nome, Reddito (Persone) SELECT Nome, Reddito FROM Persone
Condizione complessa
SELECT * FROM Persone WHERE reddito > 25 and (eta < 30 or eta > 60)
EVITARE I DUPLICATI
Si consideri la seguente interrogazione
SELECT CasaEd FROM Catalogo
S e 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 CasaEd FROM 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
RIEPILOGO
Le parole
SELECT
e
FROM
consentono a una
query
di caricare dei dati.
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.
La parola chiave
DISTINCT
limita l’output delle query poiché consente di escludere i valori duplicati di una colonna.
ESPRESSIONI, CONDIZIONI E OPERATORI 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
La definizione di espressione è semplice:
un’espressione restituisce un valore
Nella seguente istruzione, NOME, INDIRIZZO, TELEFONO E RUBRICA sono espressioni:
SELECT NOME, INDIRIZZO, TELEFONO, RUBRICA FROM 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 è tutti gli impiegati che hanno lavorato
NOME = ‘ROSSI’.
Per trovare 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 e gli operatori. La condizione presente nella clausola
WHERE WHERE
può avere una struttura molto complessa
CONDIZIONI - II
In generale le condizioni sono formate combinando operatori booleani
and
,
or
e
not predicati
con gli
Predicato
: è una condizione semplice del tipo
E1
cfr
E2
ove: cfr è un
operatore di confronto
, cioè uno degli operatori
= < > <= >= <>
(diverso)
E1
ed spesso
E2
sono espressioni, che possono essere attributi, costanti oppure espressioni formate con gli usuali operatori aritmetici. Molto
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
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, CasaEd FROM Catalogo WHERE Anno = 2001 and CasaEd = “Einaudi” SELECT Titolo, CasaEd FROM Catalogo WHERE Anno = 2001 or CasaEd = “Einaudi” SELECT Titolo, CasaEd FROM Catalogo WHERE Anno = 2000 and CasaEd <> “Feltrinelli”
ESEMPIO
SELECT CasaEd, Anno Anno = ( SELECT ) FROM Catalogo WHERE Titolo = “L’amante” and 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 ,
GLI OPERATORI
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
E’ un potente gruppo di strumenti a base della conoscenza del linguaggio SQL
GLI OPERATORI ARITMETICI
1.
2.
3.
+ (somma) - (sottrazione) / (divisione) 4.
5.
* (moltiplicazione) % (modulo o resto)
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
Non funziona con i tipi di dati che hanno cifre decimali
GLI OPERATORI ARITMETICI
Se vengono inseriti più operatori aritmetici in una espressione senza parentesi, essi vengono valutati nell’ordine: moltiplicazione, divisione, modulo, somma e sottrazione.
Ad esempio:
2 * 6 + 9 / 3
mentre l’espressione
2 * (6 + 9) / 3
vale vale
12 + 3 = 15 2 * 15 / 3 = 10
OPERATORI ARITMETICI: SOMMA (+)
SQL> SELECT * FROM PREZZO
ELEMENTO Pomodori Patate Banane Rape Arance Mele PREZZOINGROSSO 3,40 5,10 6,70 4,50 8,90 2,30
SQL> SELECT ELEMENTO, PREZZOINGROSSO, PREZZOINGROSSO + 1.50 FROM PREZZO
ELEMENTO PREZZOINGROSSO PREZZOINGROSSO + 1.50
Pomodori 3,40 4,90 Patate Banane Rape Arance Mele 5,10 6,70 4,50 8,90 2,30 6,60 8,20 6,00 10,40 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:
SQL> SELECT ELEMENTO , PREZZOINGROSSO , (PREZZOINGROSSO + 1.50) PREZZODETTAGLIO FROM PREZZO
ELEMENTO PREZZOINGROSSO
Pomodori Patate Banane Rape Arance Mele 3,40 5,10 6,70 4,50 8,90 2,30
PREZZODETTAGLIO
4,90 6,60 8,20 6,00 10,40 3,80
OPERATORI ARITMETICI: SOTTRAZIONE (-)
L’operatore meno svolge due funzioni, la prima è quella di cambiare segno ad un numero:
SQL> SELECT * FROM MINMAX
REGIONE
Piemonte Toscana Sicilia Lombardia Friuli
TEMPMAX
-4
TEMPMIN
10 4 10 -2 -3 13 19 9 8 SQL> SELECT REGIONE , -TEMPMAX , -TEMPMIN FROM MINMAX
REGIONE
Piemonte Toscana Sicilia Lombardia Friuli
TEMPMAX
4
TEMPMIN
-10 -4 -10 2 3 -13 -19 -9 -8
OPERATORI ARITMETICI: SOTTRAZIONE (-)
La seconda (e ovvia) funzione di una colonna da quelli di dell’operatore meno è quella di sottrarre i valore un’altra colonna.
Ad esempio REGIONE
Piemonte Toscana Sicilia Lombardia Friuli
MINIME
-4 4 10 -2 -3
MASSIME
10 13 19 9 8
DIFFERENZE
14 9 9 11 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.
OPERATORI ARITMETICI: DIVISIONE (/)
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
Pomodori Patate Banane Rape Arance Mele
PREZZOINGROSSO
3,40
PREZZOVENDITA
1,70 5,10 6,70 4,50 8,90 2,30 2,55 3,35 2,25 4,45 1,15
OPERATORI ARITMETICI: MOLTIPLICAZIONE (*)
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
Pomodori Patate Banane Rape Arance Mele
PREZZOINGROSSO
3.40
5.10
6.70
4.50
8.90
2.30
NUOVOPREZZO
3.06
4.59
6.03
4.05
8.01
2.07
OPERATORI ARITMETICI: MODULO (%)
L’operatore modulo restituisce il resto intero di una operazione di divisione. Esempio: NUMERATORE DENOMINATORE
SQL> SELECT * FROM RESTI
10 8 23 1024 E’ possibile creare una nuova colonna, RESTO, dove registrare il resto della divisione tra NUMERATORE e DENOMINATORE 5 3 9 16
SQL> SELECT NUMERATORE, DENOMINATORE, (NUMERATORE % DENOMINATORE) RESTO FROM RESTI
NUMERATORE DENOMINATORE 10 8 23 1024 5 3 9 16 RESTO 0 2 5 0
OPERATORI DI CONFRONTO
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 No row selected WHERE PREZZOINGROSSO = NULL ; 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
ROSSI BIANCHI BROWN NERI
NOME
ALE SABY JO ALE
CITTA
MILANO TORINO PISA BOLOGNA
DATA DI NASCITA
1/1/1970 25/5/1985 12/10/1968 13/11/1986
TELEFONO
02 3425678 011 6707221 050 880245 051 6711
SQL> SELECT * FROM AMICI WHERE NOME = ‘ALE’ ; COGNOME
ROSSI NERI
NOME
ALE ALE
CITTA
MILANO BOLOGNA
DATA DI NASCITA
1/1/1970 13/11/1986
TELEFONO
02 3425678 051 6711
SQL> SELECT * FROM AMICI
no row selected.
WHERE NOME = ‘Ale’ ;
OPERATORI DI CONFRONTO: > , >=
questi operatori operano nel seguente modo modo:
SQL> SELECT * FROM PREZZO ; ELEMENTO
Pomodori Patate Banane Rape Arance Mele
PREZZOINGROSSO
3.40
5.10
6.70
4.50
8.90
2.30
SQL> SELECT * FROM PREZZO WHERE PREZZOINGROSSO > 4.50; ELEMENTO
Patate Banane Arance
PREZZOINGROSSO
5.10
6.70
8.90
SQL> SELECT * FROM PREZZO WHERE PREZZOINGROSSO >= 4.50; ELEMENTO PREZZOINGROSSO
Patate Banane Rape Arance 5.10
6.70
4.50
8.90
Non si usano apici per racchiudere il numero 4.50
OPERATORI DI CONFRONTO: <, <=
questi operatori operano in senso inverso al precedente:
SQL> SELECT * FROM AMICI ; COGNOME
ROSSI BIANCHI BROWN NERI
NOME
ALE SABY JO ALE
CITTA
MILANO TORINO PISA BOLOGNA
DATA DI NASCITA
1/1/1970 25/5/1985 12/10/1968 13/11/1986
TELEFONO
02 3425678 011 6707221 050 880245 051 6711
SQL> SELECT * FROM AMICI WHERE CITTA <= ‘MILANO’ ; COGNOME
ROSSI BROWN
NOME
ALE JO
CITTA
MILANO PISA
DATA DI NASCITA
1/1/1970 12/10/1968
TELEFONO
02 3425678 050 880245
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):
SQL> SELECT * FROM AMICI WHERE NOME <> ‘ALE’; COGNOME
BIANCHI BROWN
NOME
SABY JO
CITTA
TORINO PISA
DATA DI NASCITA
25/5/1985 12/10/1968
TELEFONO
011 6707221 050 880245
In molte implementazione SQL è indifferente usare la forma (<>) anzichè (!=)
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.
NOME
FEGATO CUORE FARINGE VERTEBRE INCUDINE RENE
POSIZIONE
DESTRA-ADDOME PETTO GOLA CENTRO-DORSO ORECCHIO DORSO
NUMEROPARTE
1 2 3 4 5 6
OPERATORI DI CARATTERE: LIKE
L’operatore
LIKE
consente di estrarre dati che somigliano ad un certo schema
SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘%DORSO%’; NOME
VERTEBRE RENE
POSIZIONE
CENTRO-DORSO DORSO
NUMEROPARTE
4 6
SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘DORSO%’; NOME
RENE
POSIZIONE
DORSO
NUMEROPARTE
6
SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘%DORSO’; NOME
VERTEBRE
POSIZIONE
CENTRO-DORSO
NUMEROPARTE
4
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
FEGATO CUORE FARINGE VERTEBRE INCUDINE RENE
POSIZIONE
DESTRA-ADDOME PETTO GOLA CENTRO-DORSO ORECCHIO DORSO
NUMEROPARTE
1 2 3 4 5 6
SQL> SELECT * FROM PARTI WHERE NOME LIKE ‘F%’; NOME
FEGATO FARINGE
POSIZIONE
DESTRA-ADDOME GOLA
NUMEROPARTE
1 3
SQL> SELECT * FROM PARTI no rows selected.
WHERE POSIZIONE LIKE ‘f%’; L’operatore like è sempre sensibile ai caratteri minuscoli/maiuscoli
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 –
della parola nella maschera corrisponde un qualunque carattere ·
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%
L’OPERATORE DI SOTTOLINEATURA
Il carattere di sottolineatura è un carattere jolly singolo.
COGNOME
ROSSI BIANCHI BROWN NERI
NOME
ALE SABY JO ALE
CITTA
MILANO TORINO PISA BOLOGNA
PROVINCIA
MI TO PI BO
TELEFONO
02 3425678 011 6707221 050 880245 051 6711
SQL> SELECT * FROM AMICI WHERE PROVINCIA LIKE ‘_O’; COGNOME
BIANCHI NERI
NOME
SABY ALE
CITTA
TORINO BOLOGNA
PROVINCIA
TO BO
TELEFONO
011 6707221 051 6711
E’ possibile utilizzare più di un carattere di sottolineatura e può essere combinato con l’operatore LIKE.
L’OPERATORE DI CONCATENAZIONE (||)
Il simbolo || serve a concatenare due stringhe: COGNOME
ROSSI BIANCHI BROWN NERI
NOME
ALE SABY JO ALE
CITTA
MILANO TORINO PISA BOLOGNA
PROVINCIA
MI TO PI BO
TELEFONO
02 3425678 011 6707221 050 880245 051 6711
SQL> SELECT NOME || COGNOME NOMECOMPLETO FROM AMICI ; NOMECOMPLETO
ROSSI ALE BIANCHI BROWN NERI SABY JO ALE
OPERATORI LOGICI
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: AND
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 FERIE WHERE ANNI <= 5 AND FERIEGODUTE > 20; COGNOME
ABITA BACCHI BLESSI BOLIVAR BOLDI COSTALES
NUMDIP
101 104 107 233 210 211
ANNI
2 5 8 4 15 10
FERIEGODUTE
4 23 45 80 100 78
COGNOME
BACCHI BOLIVAR
NUMDIP
104 233
ANNI
5 4
FERIEGODUTE
23 80
OPERATORI LOGICI: OR
L’operatore logico OR puo’ essere utilizzato per combinare una serie di condizioni. Se una di queste è soddisfatta viene restituito TRUE
SQL> SELECT * FROM FERIE WHERE COGNOME LIKE ‘BO%’ OR COGNOME LIKE ‘CO%’; COGNOME
ABITA BACCHI BLESSI BOLIVAR BOLDI COSTALES
COGNOME
BOLDI BOLIVAR COSTALES
NUMDIP
210 233 211
ANNI
15 4 10
FERIEGODUTE
100 80 78
NUMDIP
101 104 107 233 210 211
ANNI
2 5 8 4 15 10
FERIEGODUTE
4 23 45 80 100 78 L’operatore OR richiede che una soltanto delle condizioni sia vera affinchè dati possano essere restitutiti
OPERATORI LOGICI: NOT
L’operatore logico NOT ha il compito di invertire il significato di una condizione COGNOME
ABITA BACCHI BLESSI BOLIVAR
NUMDIP
101 104 107 233
ANNI
2 5 8 4
FERIEGODUTE
4 23 45 80 BOLDI
SQL> SELECT * FROM FERIE WHERE COGNOME NOT LIKE ‘B%’;
COSTALES 210 211 15 10 100 78
COGNOME
ABITA COSTALES
NUMDIP
101 211
ANNI
2 10
FERIEGODUTE
4 78
OPERATORI DI INSIEMI: UNION
L’operatore
UNION
duplicate.
restituisce il risultato di due query escludendo le righe Esempio: quante persone diverse giocano in entrambe le squadre?
COGNOME
ABITA BRAVO CARLINI DECCA ESTERLE FUNDARI GIANI
calcetto SQL> SELECT COGNOME FROM CALCETTO UNION SELECT COGNOME FROM CALCIO ; Sono stati esclusi i doppioni COGNOME
ABITA BACCO BRAVO CARLINI DECCA DINI ESTERLE FALCONI FUNDARI GIANI
A C B
A + B - C
COGNOME
ABITA BACCO CARLINI DINI ESTERLE FALCONI GIANI
calcio
OPERATORI DI INSIEMI: UNION
L’operatore
UNION ALL
duplicate.
restituisce il risultato di due query incluse le righe Esempio: quante persone giocano al calcetto e quante al calcio?
SQL> SELECT COGNOME UNION ALL SELECT COGNOME FROM CALCETTO FROM CALCIO ; COGNOME COGNOME
ABITA ABITA BRAVO BRAVO CARLINI CRLINI DECCA
A COGNOME C
A + B
B
DECCA ESTERLE ABITA ESTERLE FUNDARI BACCO FUNDARI GIANI
calcetto
GIANI CARLINI DINI ABITA ESTERLE BACCO FALCONI CARLINI DINI GIANI
calcio
ESTERLE
Sono incluse tutte le righe
FALCONI GIANI
OPERATORI DI INSIEMI: INTERSECT
L’operatore
INTERSECT
entrambe le query restituisce soltanto le righe che vengono trovate in Esempio: quali persone giocano in entrambe le squadre?
SQL> SELECT COGNOME INTERSECT SELECT COGNOME FROM CALCETTO FROM CALCIO ; calcetto COGNOME
ABITA BRAVO CARLINI DECCA ESTERLE FUNDARI GIANI
A C B COGNOME
ABITA CRLINI ESTERLE GIANI
C
calcio COGNOME
ABITA BACCO CARLINI DINI ESTERLE FALCONI GIANI
Sono inclusi solo le righe comuni
OPERATORI DI INSIEMI: MINUS
L’operatore
MINUS
restituisce le righe della prima query che non sono presenti nella seconda Esempio: quali persone giocano solo al calcetto?
SQL> SELECT COGNOME MINUS SELECT COGNOME FROM CALCETTO FROM CALCIO ; COGNOME
ABITA BRAVO CARLINI DECCA ESTERLE FUNDARI GIANI
calcetto COGNOME
BACCCO DINI FALCONI
A C B
A - C
COGNOME
ABITA BACCO CARLINI DINI ESTERLE FALCONI GIANI
calcio Seleziona quelli che giocano solo al calcetto
Impiegati Matricola
Gestione dei valori nulli
Cognome Filiale Età
Gli impiegati la cui età è o potrebbe essere maggiore di 40
SEL
Età > 40 OR Età IS NULL (Impiegati) select * from impiegati where eta > 40 or eta is null
Interrogazioni su più relazioni
Nella clausola
FROM
possono essere presenti necessario quando le informazioni per eseguire più relazioni. Ciò è l’interrogazione sono distribuite su relazioni diverse, vale a dire: quando gli attributi presenti nella clausola diverse
SELECT
o nella clausola
WHERE
appartengono a relazioni 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, Attori WHERE 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 Relazione.Attributo. Pertanto cui appartiene , con la l’interogazione precedente diventa notazione
SELECT Film.Titolo
FROM Film, Attori WHERE Film.CodFilm = Attori.CodFilm
and 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.Titolo
FROM Film F, Attori A WHERE F.CodFilm = A.CodFilm
and 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 clausola
WHERE
dell’interrogazione deve nell’altra), e che nella essere esplicitato tale collegamento. Osserviamo che nella clausola un qualunque numero di relazioni,
FROM
può essere presente 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 Film.CodFilm = Attori.CodFilm) è detto
predicato di join
.
un’altra (ad es
SQL e algebra relazionale
R1(A1,A2) R2(A3,A4) select R1.A1, R2.A4 from R1, R2 where R1.A2 = R2.A3
prodotto cartesiano ( FROM ) selezione ( WHERE ) proiezione ( SELECT )
PROJ A1,A4 (SEL A2=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 DIFFICILE
Cognome 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”
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
136
Maternità Paternità Madre Luisa Luisa Anna Anna Maria Maria Padre Sergio Luigi Luigi Franco Franco Figlio Maria Luigi Olga Filippo Andrea Aldo Figlio Franco Olga Filippo Andrea Aldo Persone Nome Andrea Aldo Maria Anna Filippo Luigi Franco Olga Sergio Luisa Età 60 30 85 75 27 25 55 50 26 50 Reddito 21 15 42 35 30 40 20 41 35 87
ALTRI ESEMPI - Selezione, proiezione e join
I padri di ogni persona PROJ Padre (paternita JOIN Figlio =Nome persone) SELECT
distinct padre
FROM
persone, paternita
WHERE
figlio = nome
Il nome e l‘età dei figli di Luisa PROJ Nome, eta ( SEL MADRE=„Luisa“ (maternita) JOIN Figlio =Nome persone ) 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 123 SELECT Count(*) FROM Esami WHERE Matricola=123 Count(*) indica un conteggio:
indica vengono contate le ennuple (ricordiamo che * 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_AA252 FROM Esami E WHERE 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 Esami WHERE Matricola=123
MAX e MIN
? Il voto più basso dello studente con Matricola 123 SELECT Min(Voto) FROM Esami WHERE Matricola=123
Il voto più alto dello studente con Matricola 123 SELECT Max(Voto) FROM Esami WHERE 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
select nome, reddito from persone where eta < 30 Persone Nome Andrea Aldo Filippo Reddito 21 15 30 select nome, reddito from persone where eta < 30 order by nome Persone Nome Aldo Andrea Filippo Reddito 15 21 30
NULL
A IS NULL , A IS NOT NULL
controlla che l’ attributo A abbia o non abbia valore nullo
? La collocazione dei dvd non noleggiati
SELECT Collocazione FROM DVD WHERE CodiceCliente is null
? La collocazione dei dvd noleggiati dopo il 1/1/08
SELECT Collocazione FROM DVD WHERE 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 1
4 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.
2.
3.
4.
Creare il Database Città con un aereoporto di cui non è noto il numero di piste Città e orario di partenza dei voli del lunedì Nazione e numero piste dell’aereoporto da cui parte il volo con IdVolo=‘A001’
1- Creazione del database AEREOPORTO( Città, Nazione, NumPiste) VOLO(IdVolo, GiornoSett, CittaPart, OraPart, CittaArr, OraArr, TipoAereo) AEREO(TipoAereo, NumPasseggeri, QtaMerci) CREATE TABLE Aereoporto( Città CHAR(20) PRIMARY KEY, ) Nazione CHAR(20), NumPiste NUMERIC(2), ) CREATE TABLE Aereo ( tipoAereo Numeric(5) PRIMARY KEY, NumPasseggeri Numeric (4), QtaMerci Numeric (4)
AEREOPORTO( Città, Nazione, NumPiste) VOLO(IdVolo, GiornoSett, CittaPart, OraPart, CittaArr, OraArr, TipoAereo) ) AEREO(TipoAereo, NumPasseggeri, QtaMerci) CREATE TABLE Volo ( IdVolo CHAR(4), GiornoSett CHAR(4), CittaPart CHAR(20) REFERENCES Aereoporto(citta), OraPart CHAR(5), CittaArr CHAR(20) REFERENCES Aereoporto(citta), OraArr CHAR(5), TipoAereo NUMERIC(5), PRIMARY KEY(IdVolo, GiornoSett), FOREIGN KEY TipoAereo REFERENCES Aereo(TipoAereo)
2 – Città con un aeroporto di cui non è noto il numero di piste SELECT 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
3.
4.
5.
1.
2.
6.
Città di partenza, orario di partenza, città di arrivo, orario di arrivo degli aerei con merci>1000 Il tipo di aereo e il numero dei passeggeri dei voli che arrivano a Torino Il numero dei voli che partono il venerdì da Francoforte La quantità dei voli che partono da ogni città Le città, numero dei voli in arrivo, nazione, avente numero di volo in arrivo maggiore di 10 Il numero dei voli internazionali che partono il lunedì da Torino