In SQL è possibile costruire delle interrogazioni nidificate (subquery) inserendo un comando SELECT all’interno di un altro comando SELECT.
La condizione scritta dopo il comando WHERE confronta il valore di un attributo con il risultato di un altro comando SELECT.
Elenco con cognome e nome dei dipendenti che hanno lo stipendio base inferiore allo stipendio medio di tutti i dipendenti :
SELECT cognome, nome FROM personale WHERE stipbase <
(SELECT AVG (stipbase) FROM personale);
Ricercare i dipendenti,elencando in ordine alfabetico cognome, nome e descrizione della filiale dove lavorano, per i quali lo stipendio risulta uguale al valore massimo tra tutti gli stipendi dei dipendenti con la funzione di impiegato:
SELECT cognome, nome, descrizione FROM personale, sede
WHERE filiale = codfil AND stipbase = ( SELECT MAX (stipbase) FROM personale
WHERE funzione = ‘impiegato’) ORDER BY cognome, nome;
48
n
Nella costruzione di una SUBQUERY si possono usare le clausole:
ANY:la condizione di ricerca è vera se il confronto è vero per almeno uno dei valori restituiti.
Esempio Elenco dipendenti che non sono impiegati e che hanno lo stipendio superiore a quello di uno qualsiasi tra gli impiegati:
SELECT cognome, nome, funzione FROM personale
WHERE funzione <> ‘impiegato’
AND stipbase > ANY ( SELECT stipbase FROM personale
WHERE funzione = ‘impiegato’);
ALL:la condizione di ricerca è vera se il confronto è vero per tutti i valori restituiti. Esempio Elenco dipendenti che non sono impiegati e che hanno lo stipendio
superiore a quello di tutti gli impiegati: SELECT cognome, nome, funzione
FROM personale
WHERE funzione <> ‘impiegato’
AND stipbase > ALL ( SELECT stipbase
FROM personale
49
IN:la condizione di ricerca è vera se il valore di un attributo è compreso tra quelli restituiti dalla SELECT nidificata..
Esempio Elenco dipendenti che lavorano nelle filiali che hanno più di 10 dipendenti: SELECT cognome, nome
FROM personale
WHERE filiale IN ( SELECT filiale
FROM personale GROUP BY filiale
HAVING COUNT (*) > 10);
EXISTS:la condizione di ricerca è vera se la SELECT nidificata produce una o più righe come risultato.
Esempio Elenco dipendenti solo se esistono dipendenti di sesto livello: SELECT cognome, nome
FROM personale
WHERE EXISTS ( SELECT stipbase
FROM personale WHERE livello= 6);
50
Problema n° 1
Testo del problema
Le informazioni relative alle attività sportive studentesche devono essere organizzate in una base di dati.Gli studenti, dei quali si conservano le informazioni
anagrafiche,frequentano gli istituti superiori, e possono partecipare a una o più
manifestazioni sportive (specialità sportive diverse, giornate diverse,campionati che durano per mesi o gare di un giorno.) Per ogni attività sportiva le scuole indicano un professore che svolge la funzione di riferimento e di allenatore:ogni professore
segue una sola manifestazione, ma una stessa manifestazione può essere seguita da professori diversi di scuole diverse.
Definire il modello del database e rappresentare le seguenti interrogazioni:
Numero di studenti che partecipano ad una determinata manifestazione sportiva. Elenco anagrafico degli allenatori di un’attività sportiva.
Elenco delle scuole (denominazione) con il numero di studenti che partecipano. Elenco delle scuole (denominazione, indirizzo, telefono) che hanno almeno uno
studente che partecipa ad una determinata manifestazione sportiva.
Elenco allenatori (cognome nome) e scuole (denominazione) di appartenenza in ordine alfabetico.
Numero degli studenti di una determinata scuola che partecipano a ciascuna delle manifestazioni sportive.
51
Le ENTITA’ che possono essere individuate nel problema sono: - ISTITUTO istituti di appartenenza di alunni e professori.
- STUDENTE studenti delle diverse scuole che partecipano alla manifestazione. - PROFESSORE professori che svolge la funzione di allenatore.
- MANIFESTAZIONE attività sportive.
ATTRIBUTI:
ISTITUTO codice istituto, denominazione, indirizzo, telefono.
STUDENTE codice studente, cognome, nome, data nascita.
PROFRSSORE codice professore, cognome, nome.
MANIFESTAZIONE codice manifestazione, descrizione, luogo,data inizio per i tornei e campionati o di svolgimento per le gare di una giornata.
ASSOCIAZIONI:
ISTITUTO – STUDENTI UNO A MOLTI: ogni istituto può essere la scuola di uno o più studenti, ogni studente deve essere iscritto in un solo istituto.
STUDENTE – MANIFESTAZIONE MOLTI A MOLTI:ogni studente può essere il
partecipante di una o più manifestazioni, ogni manifestazione deve essere seguita da uno o più studenti.
MANIFESTAZIONE – PROFESSORE UNO A MOLTI:ogni manifestazione può essere seguita da uno o più professori, come da vincolo nel testo del problema,ogni professore può essere l’allenatore di una sola manifestazione.
ISTITUTO – PROFESSORE UNO A MOLTI :ogni istituto può essere la sede di uno o più professori,ogni professore deve avere la cattedra in un solo istituto.
52 MODELLO E/R STUDENTE ISTITUTO MANIFESTAZIONE PROFESSORE Iscritto in Scuola di Curata da Allenatore in Sede di Docente presso Partecipante a Seguita da
Utilizzando le regole di derivazione si passa dal modello concettuale al modello logico, cioè alle tabelle:le associazioni uno a molti si rappresentano introducendo le chiavi esterne nelle tabelle studenti e professori per indicare l’istituto di appartenenza; nella tabella professori viene aggiunta anche un’altra chiave esterna per l’associazione con l’entità manifestazioni; l’associazione molti a molti tra studenti e manifestazioni genera una nuova tabella iscrizioni che contiene le chiavi primarie di studenti e manifestazioni più l’attributo data di iscrizione.
53
istututi codice istituto primaria carattere 4
denominazione carattere 35
indirizzo carattere 25
telefono carattere 12
studenti codicestu primaria numerico 5
cognstu carattere 30
nomestu carattere 20
datanascita data/ora 8
classe numerico 1
codiceistituto esterna carattere 4 manifestazioni codicemanif primaria carattere 3
descrizione carattere 25
luogo carattere 20
datainizio data/ora 8
professori codiceprof primaria numerico 3
cognprof carattere 30
nomeprof carattere 20
codiceistituto esterna carattere 4 codicemanif esterna carattere 3 Iscrizioni codicestud esterna numerico 5 codicemanif esterna carattere 3
54
INTERROGAZIONI
Numero degli studenti che partecipano a una determinata manifestazione sportiva:
SELECT COUNT (*)
FROM manifestazioni, iscrizioni
WHERE manifestazioni.codicemanif = iscrizione.codicemanif AND descrizione = [manifestazione da controllare];
Elenco anagrafico degli allenatori di una manifestazione:
SELECT cognprof, nomeprof
FROM manifestazioni, professori
WHERE manifestazioni.codicemanif = professori.codicemanif AND descrizione = [manifestazione da controllare]
ORDER BY cognprof, nomeprof;
Elenco delle scuole con il numero di studenti che partecipano:
SELECT denominazione, COUNT (DISTINCT codicestud) FROM studenti, istituti
WHERE studenti.codiceistituto = istituti.codiceistituto GROUP BY denominazione;
55
Elenco delle scuole (denominazione, indirizzo, telefono) che hanno almeno uno studente che partecipa ad una determinata manifestazione sportiva:
SELECT denominazione, indirizzo, telefono
FROM manifestazioni, iscrizioni, studenti, istituti
WHERE manifestazioni.codicemanif = iscrizione.codicemanif AND studenti.codicestud =iscrizioni.codicestud
AND studenti.codiceistituto = istituti.codiceistituto AND descrizione = [manifestazione da controllare]; GROUP BY denominazione, indirizzo, telefono
HAVING COUNT (*) >0;
Elenco allenatori e scuole di appartenenza in ordine alfabetico:
SELECT cognprof, nomeprof, denominazione FROM professori, istituti
WHERE professori.codiceistituto = istituti.codiceistituto ORDER BY cognprof, nomeprof;
Numero studenti di una determinata scuola che partecipano a ciascuna delle manifestazioni
SELECT descrizione, COUNT (*)
FROM manifestazioni, iscrizioni, studenti, istituti
WHERE manifestazioni.codicemanif = iscrizione.codicemanif AND studenti.codicestud =iscrizioni.codicestud
AND studenti.codiceistituto = istituti.codiceistituto AND descrizione = [scuola da controllare];
56
Problema n° 2
Testo del problema
Si vogliono organizzare le informazioni relative ai clienti, ai loro rappresentanti e alle fatture emesse.Ogni rappresentante ha tanti clienti, mentre a un cliente corrisponde un solo rappresentante. Per semplicità si supponga di registrare solo la data e
l’importo totale di ogni fattura, senza specificare le righe di dettaglio sugli articoli venduti.
Definire il modello del database e rappresentare le seguenti interrogazioni: Elenco dei clienti di un rappresentante.
Numero dei clienti affidati a un determinato rappresentante. Cognome e nome del rappresentante di un determinato cliente.
Elenco delle fatture di un codice cliente prefissato, in ordine di data. Fatturato raggruppato per codice rappresentante.
Numero delle fatture emesse raggruppate per codice rappresentante.
Nome del cliente e data di emissione per la fattura con importo massimo. Nome del rappresentante corrispondente alla fattura con importo massimo. Codice e nome dei clienti per i quali il fatturato totale è superiore a una cifra
prefissata.
Codice e nome dei rappresentanti per i quali il numero delle fatture è superiore a un numero prefissato.
57
ANALISI DEI DATI
Le entità che possono essere individuate nel problema sono: CLIENTE clienti ai quali vengono emesse fatture;
RAPPRESENTANTE agenti ai quali sono assegnati clienti; FATTURA vendite effettuate dai rappresentanti.
ATTRIBUTI
CLIENTE codicecliente, ragione sociale, indirizzo, telefono, partitaIVA; RAPPRESENTANTE codi rappresentante, cognome, nome, zona assegnata; FATTURA numero fattura, data emissione, importo.
ASSOCIAZIONI
RAPPRESENTANTE – CLIENTE UNO A MOLTI: ogni rappresentante ha tanti clienti, ad un cliente corrisponde un solo rappresentante.
CLIENTE – FATTURA UNO A MOLTI: per lo stesso cliente si possono emettere più fatture, ma ciascuna fattura è intestata a un solo cliente.
58
MODELLO E/R
CLIENTE RAPPRESENTANTE
FATTURA
Ogni rappresentante può essere agente per uno o più clienti, ogni cliente deve essere affidato a un solo rappresentante.
Ogni cliente può essere l’intestatario di una o o più fatture, ogni fattura deve essere emessa per un solo cliente.
Tabelle
RAPPRESENTANTI:codicerap,cognomerap, nomerap, zona;
CLIENTI:codicecli, ragionesociale, indirizzo, telefono, paritaiva,codicerap; FATTURE:numerofatt, datafatt, importo, codicecli.
59
Problema n° 3 Testo del problema
Un’agenzia di viaggi intende organizzare in un database tutte le informazioni riguardanti la sua attività:i viaggi o vacanze, le organizzazioni turistiche o tour operator, i clienti dei propri servizi, le nazioni, località o città che sono destinazione dei viaggi/vacanze.
Tutte le attività proposte ai clienti sono dei pacchetti: ciascuno di essi si riferisce a
un’organizzazione turistica, e riguardano una precisa zona del mondo o nazione o città. Ci possono essere offerte di viaggi con la stessa destinazione da parte di organizzazioni diverse. Per esempio ci può essere una crociera nel mediterraneo proposta dall’organizzazione turistica A,
oppure un soggiorno di una settimana a Vienna offerta dall’organizzazione turistica B. Di ogni pacchetto offerto occorre conoscere modalità e prezzi; di ogni località o nazione è
opportuno tenere memorizzate informazioni su condizioni climatiche, moneta, ecc. Un cliente può naturalmente acquistare in date diverse molti viaggi o vacanze.
Definire il modello del database e rappresentare le seguenti interrogazioni: Elenco dei viaggi/vacanza con prezzo inferiore a un prezzo prefissato. Elenco dei soggiorni con prezzo in una località prefissata.
Elenco dei clienti, con nome e indirizzo, che hanno fatto un viaggio con una determinata destinazione.
Numero dei pacchetti offerti da un’organizzazione turistica prefissata.
Elenco delle organizzazioni turistiche, con nome e telefono, che hanno almeno 5 pacchetti in archivio.
60