Esercizio 1

CLIENTE (Codice, Nome, Cognome, TipoDoc, NumeroDoc ) BICICLETTA (Num-bici, TipoBici, CostoOra, CostoGiorno ) PRENOTA (Num-Cli, TipoBici, Giorno, OraInizio, OraFine ) USA( Num-Cli, Num-Bici, Giorno, OraInizio, OraFine)

• Popolamento tabelle

Scrivere i comandi SQL per:

• Creare la tabella PRENOTA specificando gli opportuni vincoli e le opzioni per gestire l’integrità referenziale

  CREATE TABLE Prenota(
  	num_cli DECIMAL(5),
  	tipo_bici VARCHAR(3),
  	giorno DATE,
  	ora_inizio TIMESTAMP,
  	ora_fine TIMESTAMP,
  	FOREIGN KEY num_cli REFERENCES Cliente.codice ON DELETE CASCADE
  );
  

• Aumentare del 10% il costo orario e giornaliero delle mountain bike

  UPDATE TABLE Bicicletta 
  SET costo_ora = costo_ora * 1.1, costo_giorno = costo_giorno * 1.1
  WHERE tipo_bici = 'MB'
  

• Aggiungere alla tabella CLIENTE l’attributo email

  ALTER TABLE Cliente 
  ADD COLUMN email VARCHAR(30) DEFAULT NULL
  

Scrivere i comandi SQL per:

• Trovare per ogni bici che è stata usata da almeno 7 clienti per più di un’ora (si intende almeno un’ora per noleggio), e che sia stata complessivamente noleggiata almeno 20 volte, il codice e il tipo di bici e le ore complessive di noleggio

  SELECT b.numbici, b.tipobici, sum(u.orafine-u.orainizio) AS num_ore
  FROM bicicletta AS b JOIN usa AS u ON b.numbici=u.numbici
  WHERE b.numbici IN (
    SELECT numbici
    FROM usa
    WHERE (orafine-orainizio)>1
    GROUP BY numbici
    HAVING count(DISTINCT numcli)>=7
  )
  GROUP BY b.numbici, b.tipobici
  HAVING COUNT(*)>=20
  

• Trovare il nome e il cognome dei clienti che hanno prenotato solo bici da corsa

  select nome, cognome
  from cliente join Prenota on NumCli=Codice
  where tipobici='P'
  except
  select nome, cognome
  from cliente join Prenota on NumCli=Codice
  where tipobici<>'P'
  

• Trovare nome e cognome dei clienti che hanno prenotato almeno tre volte una mountain bike

  SELECT nome, cognome FROM cliente JOIN prenota ON codice = numcli
  WHERE tipobici = 'MB'
  GROUP BY codice, nome, cognome HAVING COUNT(*) >= 3
  

• Trovare il numero di documento dei clienti che hanno prenotato bici da corsa ma non le hanno mai usate.

  SELECT numerodoc FROM (cliente JOIN prenota ON codice = numcli) NATURAL JOIN bicicletta
  WHERE tipobici = 'P'
  EXCEPT
  SELECT numerodoc FROM (cliente JOIN usa ON codice = numcli) NATURAL JOIN bicicletta
  WHERE tipobici = 'P'
  

• Trovare, per ogni tipo di bicicletta, il cliente che l’ha usata il maggior numero di ore

  select Nome, Cognome, TipoBici
  from Cliente c join Usa u on u.NumCli=Codice
  join Prenota p on p.NumCli=Codice
  group by Codice, Nome, Cognome, TipoBici
  having sum(extract(hour from u.OraFine)-extract(hour from u.OraInizio)) >= all(
    select sum(extract(hour from uu.OraFine)-extract(hour from uu.OraInizio))
    from Cliente cc join Usa uu on uu.NumCli=cc.Codice
    join Prenota pp on pp.NumCli=cc.Codice
    where pp.TipoBici=p.TipoBici and cc.Codice<>c.Codice
    group by cc.Codice
  )
  

• Trovare le bici che sono state utilizzate sia di martedì che di giovedì ma mai di domenica

  select NumBici
  from Usa
  where extract(isodow from Giorno)=2
  
  INTERSECT
  
  select NumBici
  from Usa
  where extract(isodow from Giorno)=4
  
  ~~~~EXCEPT
  
  select NumBici
  from Usa
  where extract(isodow from Giorno)=7
  

Esercizio 2

In riferimento al seguente schema relazionale

AUTO (Targa, Marca, Cilindrata, Potenza, CodF*, CodAss*) PROPRIETARI (CodF, Nome, Residenza) ASSICURAZIONI (CodAss, Nome, Sede) SINISTRO (CodS, Località, Data) AUTOCOINVOLTE (CodS*, Targa*, ImportoDelDanno)

Definire in SQL le seguenti interrogazioni:

• Codice fiscale e Nome di coloro che possiedono più di un’auto

  SELECT CodF, nome FROM auto NATURAL JOIN proprietari
  GROUP BY CodF, nome HAVING COUNT(*) > 1