Seconda Esercitazione Autoguidata

OBIETTIVI DELL'ESERCITAZIONE:

• effettuare query con group-by e operatori di aggregazione
• effettuare query annidate
• definizione e uso di viste

1. CREARE UNA BASE DI DATI

Selezionate il comando New Database dal menù file. Specificate il nome della base di dati che volete creare, ad esempio MyDatabase, e selezionate il tasto OK: Cloudview creerà la cartella c:\programmi\cloudscape_5.0\bin\MyDatabase. Per specificare un percorso diverso da quello di default (c:\programmi\cloudscape_5.0\bin) utilizzate il tasto Directory. In laboratorio vi consigliamo di creare una cartella personale in cui memorizzare le vostre basi di dati, ad esempio c:\nomestudente.

2. CREARE LE TABELLE

Si consideri la tabella PERSONE, contenente i seguenti attributi

• Nome : 20 caratteri (chiave primaria);
• Eta : intero (3 cifre)
• Reddito : intero (10 cifre);
• Sesso : 'M' oppure 'F';
• Residenza : 20 caratteri.

• Genitore : 20 caratteri;
• Figlio : 20 caratteri;
• OrdFiglio : intero (2 cifre).

(Genitore,Figlio) è chiave primaria. Inoltre, ogni figlio è una persona ed ogni genitore è una persona. Si forniscano le istruzioni SQL per creare le due tabelle.

Per scrivere ed eseguire le istruzioni SQL in Cloudview, utilizzate la SQL Window dell'Edit Panel.

3. EFFETTUARE INTERROGAZIONI

Fornite le interrogazioni SQL per rispondere alle seguenti domande. Per verificare la correttezza delle proprie soluzioni, caricate nella base di dati il file \\Sauron\Esercitazioni\Basidati\Eser-2000-10-17\eser2-db.sql, e confrontate i risultati ottenuti con quelli contenuti nel file \\Sauron\Esercitazioni\Basidati\Eser-2000-10-17\eser2-3-ris.txt. Per caricare il file relativo alla base di dati usate il bottone script della SQL Window.

1. Trovare il numero di luoghi di residenza distinti;
2. Trovare il minimo, il massimo, la somma e la media dei redditi di persone raggruppate per residenza;
3. Trovare il reddito medio dei padri per città di residenza;
4. Trovare le persone che sono genitori di almeno 2 figli;
5. Trovare l'elenco ordinato dei genitori i cui figli guadagnano tutti piu` di 20 milioni.
   Dare una soluzione che fa uso dell'operatore di differenza (attenzione questa sintassi non è accettata da Cloudscape), una che fa uso di una query annidata, ed una che fa uso di una vista;
6. Definire una vista EtaMediaFigli che restituisce per ogni persona l'età media dei suoi figli.
   Visualizzare il contenuto della vista;
7. Utilizzare la vista EtaMediaFigli per trovare tra tutti i genitori quello i cui figli hanno la massima età media.

4. ESERCIZIO PER CASA

Fornite le interrogazioni SQL per rispondere alle seguenti domande.

1. Trovare l'età media dei geniori di Olga;
2. Trovare il reddito complessivo dei figli di Anna e Luigi;
3. Trovare il minimo, il massimo, la somma e la media dei redditi delle donne residenti a Milano;
4. Trovare per ogni persona il numero dei suoi figli;
5. Trovare il reddito delle persone che sono genitori di almeno 2 figli.
   Fornire una soluzione senza usare raggruppamento, ed una usando reggruppamento.
   Come si generalizzano le due soluzioni al caso in cui si voglia trovare il reddito di persone con almeno 3, 4, 5, ... figli?
6. Definire due viste PrimoFiglioOrd e PrimoFiglioEta che restituiscono per ogni persona il primo figlio.
   PrimoFiglioOrd deve usare il valore dell'attributo ordFiglio.
   PrimoFiglioEta deve usare l'età dei figli.
   Visualizzare i contenuti delle due viste e confrontarli. Motivare le differenze sulla base dei dati memorizzati nelle tabelle Persone e Genitori.

Sul sito del corso trovate disponibile la base di dati utilizzata al punto precedente all'indirizzo homepage-corso/esercitazioni/eser-2002-10-17/eser2-db.sql.

Le soluzioni degli esercizi 3 e 4 saranno disponibili fra una settimana all'indirizzo homepage-corso/esercitazioni/eser-2002-10-17/soluzioni.