Lezione 3
Problema
Si vuole gestire una rubrica personale. Per ogni contatto si vuole tenere traccia del nome, del cognome, dell’indirizzo e del numero di telefono.
Schema Concettuale: ER
Nota.
Nello schema concettuale un attributo opzionale viene indicato con l'etichetta (0,1). Tale etichetta indica che ogni istanza della entità avrà al minimo 0 valori per questo campo ed al massimo 1.
Nota.
Il fatto che qui l'attributo opzionale sia anche un attributo di tipo dominio è solo un caso!
Nota.
Il dominio dell'attributo titolo è: Sig., Sig.ra, Avv., Dott., Dott.ssa, Prof., Prof.ssa.
Nota.
Nello schema concettuale un attributo composto è un attributo composto da altri attributi. Si indica semplicemente descrivendo con altri rami gli attributi che lo compongono.
Schema logico: relazionale
contatto
(id, titolo*, nome, cognome, via,
civico, cap, localita, provincia, numTelefono)
Nota.
Nello schema relazionale l'opzionalità si indica con un asterisco.
Nota.
L'attributo composto viene mappato nello schema relazionale inserendo direttamente e soltanto gli attributi che lo descrivono.
Esempio di istanza
| id | titolo | nome | cognome | via | civico | cap | localita | provincia | numTelefono |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Luca | Rossi | Manzoni | 3 | 38057 | Pergine | TN | 3481234567 | |
| 2 | Avv. | Maria | Verdi | Battisti | 5 | 38057 | Pergine | TN | 3804567894 |
| 3 | Giovanni | Curti | Roma | 68 | 38051 | Borgo | TN | 3337894561 | |
| 4 | Vanessa | Cerruti | Dante | 12 | 38121 | Trento | TN | 3401112223 | |
| 5 | Michele | Fusco | Virgilio | 5 | 38121 | Trento | TN | 3209988774 | |
| 6 | Dott. | Giorgio | Feola | Modena | 82 | 40100 | Bologna | BO | 3476644551 |
| 7 | Beatrice | Feola | Modena | 50 | 40100 | Bologna | BO | 3201597532 | |
| 8 | Michela | Zancanella | Genova | 4 | 40026 | Imola | BO | 3255666541 |
Interrogazione - 1
Mostrare un elenco formato da cognome, nome, via, civico e localita di tutti i contatti che abitano nella provincia di Bologna.
from contatto
where provincia='BO'
| cognome | nome | via | civico | localita |
|---|---|---|---|---|
| Feola | Giorgio | Modena | 82 | Bologna |
| Feola | Beatrice | Modena | 50 | Bologna |
| Zancanella | Michela | Genova | 4 | Imola |
Interrogazione - 2
Mostrare nominativo e numero di tutti i componenti della famiglia "Feola".
from contatto
where (cognome='Feola')
| nome | cognome | numTelefono |
|---|---|---|
| Giorgio | Feola | 3476644551 |
| Beatrice | Feola | 3201597532 |
Interrogazione - 3
Mostrare tutti i dati di quei contatti che abitano in trentino il cui cognome è "Feola".
from contatto
where (cognome='Feola') and (provincia='TN')
| id | nome | cognome | via | civico | cap | localita | provincia | numTelefono |
|---|
Interrogazione - 4
Mostrare un elenco formato da cognome, nome e numero di telefono in ordine alfabetico.
from contatto
order by cognome
| cognome | nome | numTelefono |
|---|---|---|
| Cerruti | Vanessa | 3401112223 |
| Curti | Giovanni | 3337894561 |
| Feola | Giorgio | 3476644551 |
| Feola | Beatrice | 3201597532 |
| Fusco | Michele | 3209988774 |
| Rossi | Luca | 3481234567 |
| Verdi | Maria | 3804567894 |
| Zancanella | Michela | 3255666541 |
Nota.
Notiamo come la clasusola order by espressa sul campo cognome lasci nell'ordine trovato nel db i valori relativi al nome (Feola Giorgio compare prima di Feola Beatrice così come nel db).
Interrogazione - 5
Mostrare un elenco formato da cognome, nome e numero di telefono in ordine alfabetico (a parità di cognome vogliamo ordinare per nome.
from contatto
order by cognome, nome
| cognome | nome | numTelefono |
|---|---|---|
| Cerruti | Vanessa | 3401112223 |
| Curti | Giovanni | 3337894561 |
| Feola | Beatrice | 3201597532 |
| Feola | Giorgio | 3476644551 |
| Fusco | Michele | 3209988774 |
| Rossi | Luca | 3481234567 |
| Verdi | Maria | 3804567894 |
| Zancanella | Michela | 3255666541 |
Nota.
Anche nella clausola order by possiamo indicare un elenco di campi piuttosto che uno solo.
Interrogazione - 6
Mostrare l’elenco di tutte le localita presenti in rubrica in ordine alfabetico inverso.
from contatto
order by localita desc
| localita |
|---|
| Trento |
| Trento |
| Pergine |
| Pergine |
| Imola |
| Borgo |
| Bologna |
| Bologna |
Nota.
La specifica desc che sta per descending inverte l'ordine di presentazione.
Interrogazione - 7
Mostrare tutti gli indirizzi ordinati prima per provincia, poi per localita, poi per codice di avviamento postale, poi per via ed infine per numero. L'ordinamento per numero deve però avvenire in senso inverso.
from contatto
order by provincia, localita, cap, via, numero desc
| BO | Bologna | 40100 | Modena | 82 |
| BO | Bologna | 40100 | Modena | 50 |
| BO | Imola | 40026 | Genova | 4 |
| TN | Borgo | 38051 | Roma | 68 |
| TN | Pergine | 38057 | Battisti | 5 |
| TN | Pergine | 38057 | Manzoni | 3 |
| TN | Trento | 38121 | Dante | 12 |
| TN | Trento | 38121 | Virgilio | 5 |
Interrogazione - 8
Mostrare l’elenco di tutte le localita presenti in rubrica in ordine alfabetico. Ogni localita deve comparire una sola volta.
from contatto
order by localita asc
| localita |
|---|
| Bologna |
| Borgo |
| Imola |
| Pergine |
| Trento |
Nota.
La specifica distinct produce una tabella con le righe tutte diverse tra loro.
Nota.
La specifica asc (ascending) nella clausola order by è il valore di default e generalmente viene omessa.
Interrogazione - 9
Mostrare un elenco (ordinato alfabeticamente) di tutte le localita presenti nel database e per ciascuna di esse mostrare la provincia.
from contatto
order by localita
| localita | provincia |
|---|---|
| Bologna | BO |
| Borgo | TN |
| Imola | BO |
| Pergine | TN |
| Trento | TN |
Nota.
Attenzione! In questa query ha senso far comparire accanto a ciascuna localita la provincia a cui appartiene ma di solito le interrogazioni che presentano il costrutto distinct elencano un solo valore. Si veda di seguito l'interrogazione 10.
Interrogazione - 10
from contatto
order by localita
| localita | cognome |
|---|---|
| Bologna | Feola |
| Borgo | Curti |
| Imola | Zancanella |
| Pergine | Rossi |
| Trento | Cerruti |
Nota.
Questa query presenta accanto a ciascuna localita il cognome relativo alla prima occorrenza di quella localita nel db: ovvero un'informazione inutile.
Challenge
Challenge - 1
Qual è il risultato di questa select?
from contatto
order by id
Challenge - 2
Qual è il risultato di questa select?
from contatto
where (cognome != 'Feola')
order by cap desc
Challenge - 3
Creare una query che isoli l’id di quei contatti che hanno il valore del campo numero civico inferiore a 10.
Challenge - 4
Creare una query che restituisca l’elenco di tutti i cognomi presenti in rubrica (una sola volta ogni cognome) in ordine alfabetico.
Challenge - 5
Creare una query che restituisca cognome e indirizzo di tutti i contatti che non risiedono nella provincia di Bologna (in ordine alfabetico).