Eserciziario di informatica/Ricerche in SQL

N.B. La soluzione degli esercizi che seguono richiede le conoscenze acquisite ai punti progettazione dati, uso di sql per la definizione dei dati e uso di sql per la gestione dei dati.

A. Ogni vino di una certa annata viene vinificato da una cantina che lo produce partendo da una o più qualità di uva (uvaggi). Del vino interessano la gradazione alcolica legale e delle cantine il volume totale dei serbatoi. Dopo avere sviluppato il modello E-R relativo ai dati descritti, scrivere le istruzioni SQL che permettono di:

1. Contare entro quanti vini entra ogni uvaggio.
2. Elencare le cantine che vinificano un solo vino.
3. Determinare la gradazione legale del vino che viene composto con il maggior numero di uvaggi.
4. Determinare la quantità massima di ogni vino che può essere prodotto per ogni annata.

B. Riferendosi alla base di dati:

giornaleDiMagazzino(articolo, documento, carico, scarico,data)
magazzino(cod, desc,sottoscorta,consistenza, pU, note, categoria)
con il vincolo di integrità referenziale:
giornaleDiMagazzino.articolo |--> magazzino.cod
dove si immagina di specificare la deperibilità di un bene scrivendo tra le note "deperibile".

1. Elencare gli articoli con prezzo maggiore del prezzo medio.
2. Elencare gli articoli con pU maggiore del massimo pU degli articoli deperibili.
3. Elencare gli articoli con pU maggiore di quello di almeno un articolo sottoscorta.
4. Individuare la categoria del prodotto presente con il maggior numero di articoli.
5. Elencare gli articoli che non sono stati movimentati nel Maggio 2007.

Soluzione.

select desc from magazzino 
where (pU > (select avg(pU) from magazzino));

select *  from magazzino
where (pU > (select max(pU) from magazzino where note = "deperibile"));

select * from magazzino
where pU > ANY (select pU from magazzino where (consistenza <= sottoscorta));

select categoria, count(*)  from magazzino
group by categoria having (count(*) >= ALL(select count(*) from magazzino group by categoria));

select * from magazzino
where cod NOT IN (select cod from giornaleDiMagazzino
 where (data >= "01/05/2007") AND (data <= "31/05/2007"));

C.Creare una base di dati che rappresenti le corse disputate, i cavalli che le hanno vinte e chi ne fosse il fantino. Dei cavalli interessa anche la scuderia di appartenenza, oltre al nome e all’anno di nascita; un cavallo appartiene ad una sola scuderia, non sono ammesse comproprietà. Delle scuderie interessano nome, nazionalità e domicilio. Dei fantini interessano nome, cognome, soprannome e anno di nascita. Ciascuna corsa viene disputata in un ippodromo con un certo nome in una tal data conferisce un trofeo. Al vincitore di ciascuna corsa è corrisposto un premio in denaro.

In seguito sviluppare le query in SQL che permettano di:

1. Determinare il numero dei cavalli di ciascuna scuderia.
2. Determinare il nome del cavallo che ha vinto il maggiore numero di corse.
3. Determinare il nome del cavallo che ha vinto il minore numero di corse.
4. Determinare il numero di vittorie di ciascuna scuderia.
5. Individuare il fantino che ha guadagnato di più con i premi.
6. Determinare il numero di vittorie di ciascuna nazione.
7. Determinare l’ippodromo che ha ospitato più corse.

Soluzione:

Cavallo(id, scuderia, nome, anno)
Fantino(cf, nome, cognome, soprannome, anno)
Corsa(id, cavallo, fantino, data, ippodromo, premio, trofeo)
Scuderia(id, nome, nazionalità, domicilio)
Cavallo.scuderia|--> Scuderia.id
Corsa.fantino|-->Fantino.cf
Corsa.cavallo|-->Cavallo.id

Select Scuderia.nome, count(*) as [Cavalli gestiti]
From Scuderia, Cavallo
Where (Scuderia.id = Cavallo.scuderia)
Group by Scuderia.nome;

Select Cavallo.nome From Cavallo,Corsa
Where (Cavallo.id = Corsa.cavallo)
Group by Cavallo.nome
Having Count(*) >= ALL
(Select Count(*) From Cavallo,Corsa
 Where (Cavallo.id = Corsa.cavallo)
 Group by Cavallo.nome);

Select Cavallo.nome From Cavallo,Corsa
Where (Cavallo.id = Corsa.cavallo)
Group by Cavallo.nome
Having Count(*) <= ALL
(Select Count(*) From Cavallo,Corsa
 Where (Cavallo.id = Corsa.cavallo)
 Group by Cavallo.nome);

Select Scuderia.nome, Count(*) as [Numero vittorie] From Scuderia, Cavallo, Corsa
Where (Scuderia.id = Cavallo.scuderia) AND (Cavallo.id = Corsa.cavallo)
Group By Scuderia.nome;

Select Fantino.nome, Fantino.cognome, Sum(premio) as [Totale vincite] From Fantino, Corsa
Where (Fantino.id = Corsa.fantino) Group By Fantino.nome, Fantino.cognome
Having Sum(premio) >= ALL
(Select Sum(premio) From Fantino, Corsa
Where (Fantino.id = Corsa.fantino)
Group By Fantino.nome, Fantino.cognome);

D. Ciascuno dei prodotti minerari estratti in varie regioni d’Europa deve essere sottoposto ad un ben determinato processo di raffinazione. Ciascun prodotto ha un certo costo al kg. In alcune regioni sono poi ubicati gli impianti entro ciascuno dei quali si effettuano uno o più di questi processi.

1. Determinare quali minerali vengano estratti in una regione specificata dall’esecutore.
2. Determinare il costo al grammo di tutti i minerali.
3. Determinare in quali impianti si raffinano minerali il costo al kg dei quali sia superiore ad 1 euro.

E. Si costruisca il modello dei dati relativo alle vaccinazioni alle quali sono sottoposti i bambini di una città. Ogni vaccinazione viene somministrata da un medico ad un certo bambino. Rappresentare anche i genitori di tali bambini.

1. Elencare il cognome e il luogo di nascita dei bambini di una certa età.
2. Elencare tutte le vaccinazioni fatte da un certo bambino.
3. Determinare i figli di un certo genitore.
4. Contare i figli di ogni genitore.
5. Contare da quanti medici sia stato vaccinato ogni bambino.

F.Ogni navigazione di una nave è caratterizzata da un porto di partenza e da uno di arrivo. Ignoriamo la possibilità di scali intermedi. In ogni navigazione vi sono dei passeggeri e delle merci trasportate in una certa quantità e di un certo valore.

1. Contare il numero delle navigazioni effettuate da ogni nave.
2. Determinare il valore totale delle merci di trasportate in una determinata navigazione.
3. Determinare il valore medio delle merci trasportate.