Seconda Esercitazione Autoguidata

OBIETTIVI DELL'ESERCITAZIONE:

Utilizzare Oracle 9i Lite e l'ambiente MSQL per:

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

1. CREARE UNA BASE DI DATI

Per creare una nuova base di dati, operate dal prompt dei comandi. Se, ad esempio, volete creare una base di dati chiamata persone:

aprite un Prompt dei comandi;
digitate la stringa
```
createdb polite persone
```
e premete Invio. In questo modo viene creato il database persone con DSN polite. A questo punto, è possibile connettersi al database persone attraverso il comando
msql system/manager@jdbc:polite:persone

Nota: per digitare il carattere @ utilizzate la combinazione di tasti Altr Gr + ò

2. CREARE LE TABELLE

Si consideri la tabella PERSONE, contenente i seguenti attributi

nome : 20 caratteri (chiave);
eta : intero (3 cifre);
reddito : intero (10 cifre);
sesso : 'M' oppure 'F';
residenza : 20 caratteri.

e la tabella GENITORI, contenente i seguenti attributi

genitore : 20 caratteri;
figlio : 20 caratteri;
ordFiglio : intero (2 cifre).

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

Per scrivere le istruzioni SQL, utilizzate un qualsiasi editor di testo (ad esempio Notepad). Una volta scritto il codice salvatelo su un file, ad esempio c:\persone.sql, e richiamate lo script da MSQL tramite il comando

Start persone.sql.

Se il file non si trova direttamente in c:\, sostituite il nome del file con il path relativo.

3. EFFETTUARE INTERROGAZIONI

Fornite le interrogazioni SQL per rispondere alle seguenti domande.

Trovare il numero di luoghi di residenza distinti;
Trovare il minimo, il massimo, la somma e la media dei redditi di persone raggruppate per residenza;
Trovare il reddito medio dei padri per città di residenza;
Trovare le persone che sono genitori di almeno 2 figli;
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 MINUS, una che fa uso di una query annidata, ed una che fa uso di una vista;
Definire una vista EtaMediaFigli che restituisce per ogni persona l'età media dei suoi figli.
Visualizzare il contenuto della vista;
Utilizzare la vista EtaMediaFigli per trovare tra tutti i genitori quello i cui figli hanno la massima età media.

Per verificare la correttezza delle proprie soluzioni, popolate la base di dati con il file eser2-db.sql, e confrontate i risultati ottenuti con quelli contenuti nel file eser2-3-ris.txt. Per popolare la base di dati eseguite lo script SQL come descritto al punto precedente.

4. ESERCIZIO PER CASA

Fornite le interrogazioni SQL per rispondere alle seguenti domande.

Trovare l'età media dei genitori di Olga;
Trovare il reddito complessivo dei figli di Anna e Luigi;
Trovare il minimo, il massimo, la somma e la media dei redditi delle donne residenti a Milano;
Trovare per ogni persona il numero dei suoi figli;
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?
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.

Per verificare la correttezza delle proprie soluzioni, popolate la base di dati con il file eser2-db.sql, e confrontate i risultati ottenuti con quelli contenuti nel file eser2-4-ris.txt.

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