Laboratorio di Sistemi Informativi

Soluzioni esercizi - SQL

Esercizio 1

Determinare l'elenco dei voli che partono da Pescara:

select idaereo, idsrc, iddst, ora
from voli, aeroporti
where aeroporti.id=idsrc and nome='Pescara';
Determinare l'elenco delle persone che hanno una prenotazione in prima classe:

select nome
from prenotazioni as p, voli, posti
where idvolo=voli.id and voli.idaereo=posti.idaereo and
p.idposto=posti.idposto an classe='first';
Determinare l'elenco dei voli che hanno prenotazioni in attivo:

select distinct voli.id,idaereo, idsrc, iddst, ora
from voli, prenotazioni as p
where p.idvolo=voli.id;

oppure, usando delle sotto-interrogazioni:

select * from voli
where id in (select idvolo from prenotazioni);
Determinare le persone che hanno prenotazioni su due voli diversi:

select distinct p1.nome
from prenotazioni as p1, prenotazioni as p2
where p1.nome=p2.nome and p1.idvolo!=p2.idvolo;

oppure, usando le sotto-interrogazioni:

select distinct nome from prenotazioni where nome in (select nome from prenotazioni as p2 where p2.idvolo!=prenotazioni.idvolo)

Esercizio 2

Visualizzare, per ogni volo, il numero di posti in business class che sono stati prenotati:

select v.id, count(*)
   from voli as v, prenotazioni as pr, posti as po
   where v.id=pr.idvolo and po.idaereo=v.idaereo and po.idposto=pr.idposto and classe='business'
   group by v.id;

Visualizzare il numero totale di prenotazioni per ogni volo, includendo i voli senza prenotazioni:

select id, count(nome)
from voli left join prenotazioni on idvolo=id
group by id;
Visualizzare le persone che hanno più di una prenotazione in corso, e il relativo numero di prenotazioni:

select nome, count(*)
   from prenotazioni
   group by nome
   having count(*)>1;

oppure

select distinct p1.nome, count(distinct p1.idposto)
   from prenotazioni as p1, prenotazioni as p2
   where p1.nome=p2.nome and (p1.idvolo!=p2.idvolo or p1.idposto!=p2.idposto)
   group by p1.nome;

Esercizio 3

Creare una tabella temporanea con il comando

CREATE TABLE temporanea (

  x INT

);

Inserire in questa tabella il risultato della query interna, con

INSERT INTO temporanea SELECT
SUM(valore) AS s FROM t4 GROUP BY categoria;

e calcolare la query esterna con

SELECT AVG(x) FROM temporanea;

Esercizio 4

Si inizia col creare la tabella che dovrà contenere le parole dal dizionario:

create table dizionario (
id int primary key auto_increment,
parola varchar(100)
);

Notare il campo id di tipo auto_increment che serve per associare ogni parola con un numero intero. Successivamente si caricano i dati che si trovano nel file italian.txt dentro la tabella con

load data local infile 'italian.txt' into table dizionario (parola);

Si crea un tabella nuoveprenotazioni che dovrà contenere le nuove prenotazioni con i nomi modificati. Siccome la nuova tabella ha esattamente la stessa struttua di quella vecchia, si può usare una variante di CREATE TABLE che crea una nuova tabella (vuota) ma con la stessa struttura di una vecchia:

create table nprenotazioni like prenotazioni;

Ora si aggiungono due attributi numeri alla tabella prenotazioni (quella originale), che dovranno essere riempiti con valori casuali, da far corrispondere agli indici delle parole nella tabella dizionario.

alter table prenotazioni add nomeid int;
alter table prenotazioni add cognomeid int;

Con select count(*) from dizionario scopriamo che sono presenti 116879 parole. Dunque dobbiamo riempire i campi nomeid e cognomeid con numeri casuali tra 1 e 116879. Si fa con:

update prenotazioni set nomeid=ceiling(rand()*116879);
update prenotazioni set cognomeid=ceiling(rand()*116879);

Adesso, per ottenere la tabella da noi voluta, possiamo semplicemente fare un join tra prenotazioni e dizionario (due copie di dizionario, in realtà) sulla base di nomeid e cognomeid. La query da dare è:

select idvolo, idposto, concat((d1.parola,' ',d2.parola)
from prenotazioni, dizionario as d1, dizionario d2
where nomeid=d1.id and cognomeid=d2.id;

È possibile salvare il risultato di questa query nella tabella nprenotazioni con

insert into nprenotazioni
select idvolo, idposto, concat(d1.parola,' ',d2.parola)
from prenotazioni, dizionario as d1, dizionario d2
where nomeid=d1.id and cognomeid=d2.id;

Volendo fare quanto suggerito alla fine dell'esercizio, ovvero far sì che nome e cognome inizino con una lettera maiuscola, bisogna ricorrere all'uso delle funzioni stringa di MySQL e sostituire la query di cui sopra con

select idvolo, idposto,
concat(ucase(left(d1.parola,1)),substr(d1.parola,2),' ',
ucase(left(d2.parola,1)),substr(d2.parola,2))
from prenotazioni, dizionario as d1, dizionario d2
where nomeid=d1.id and cognomeid=d2.id;

Volendo invece modificare direttamente la tabella prenotazioni, si può dare il seguente comando update:

update prenotazioni, dizionario as d1, dizionario as d2

  set nome=concat(d1.parola,' ',d2.parola)

  where nomeid=d1.id and cognomeid=d2.id;