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
contatti
(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 contatti
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 contatti
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 contatti
where (cognome='Feola') and (provincia='TN')
| id | titolo | 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 contatti
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 contatti
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 contatti
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 contatti
order by provincia, localita, cap, via, civico desc
| provincia | localita | cap | via | civico |
|---|---|---|---|---|
| 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 contatti
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 contatti
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 contatti
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 contatti
order by id
Challenge - 2
Qual è il risultato di questa select?
from contatti
where (cognome != 'Feola')
order by cap desc
Challenge - 3
Qual è il risultato di questa select?
from contatti
where ((provincia = 'TN') or (provincia = 'BO') or (provincia = 'CE'))
and (cognome = 'Rossi')
Challenge - 4
Creare una query che isoli l’id di quei contatti che hanno il valore del campo numero civico inferiore a 10.
Challenge - 5
Creare una query che restituisca l’elenco di tutti i cognomi presenti in rubrica (una sola volta ogni cognome) in ordine alfabetico.
Challenge - 6
Creare una query che restituisca cognome e indirizzo di tutti i contatti che non risiedono nella provincia di Bologna (in ordine alfabetico).
Challenge - 7
Creare una query che mostri un elenco ordinato di tutti i titoli utilizzati nel database.
Challenge - 8
Creare una query che mostri un elenco (cognome e nome) ordinato di tutti trentini e di tutti i casertani. Attenzione: tra i trentini siano esclusi quelli che abitano nelle seguenti località: Cles, Dermulo, Coredo, Tassullo; tra i casertani siano esclusi quelli che abitano a Maddaloni, Marcianise, Casagiove.
Challenge - 9
Creare una query che mostri un elenco dei contatti che abitano a Roma in via Colombo dal civico 350 al civico 135. Vogliamo che l'elenco sia ordinato per numero civico in ordine inverso.
Challenge - 10
Oggi è il 26 Agosto: sant'Alessandro. Mostrare un elenco dei contatti che oggi festeggiano l'onomastico. Per ogni contatto vogliamo che vengano mostrati tutti i dati (meno l'id).