
Lesson 3
Task
Suppose you want to design a database for storing your address book. You need to keep track of name, surname, address and telephone number of each contact.
Conceptual data model: ER
Note.
In a conceptual diagram a composite attribute is an attribute composed of other attributes.
Note.
An optional attribute does not need to be filled in.
Nota.
Be careful! Here the optional attribute is an attribute domain, too. This is not a rule.
Nota.
The domain of the title attribute is: Miss, Ms, Mr, Mrs, Dr, Prof., Adv..
Logical data model: relational
contact (id, title-, name, surname, street, streetNo, posCode, city, province, TelephNo)
AN INSTANCE OF DATABASE
id | title | name | surname | street | streetNo | posCode | city | province | TelephNo |
---|---|---|---|---|---|---|---|---|---|
1 | Luca | Rossi | Manzoni | 3 | 38057 | Pergine | TN | 3481234567 | |
2 | Adv. | 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 | Dr | Giorgio | Feola | Modena | 82 | 40100 | Bologna | BO | 3476644551 |
7 | Beatrice | Feola | Modena | 82 | 40100 | Bologna | BO | 3201597532 | |
8 | Michela | Zancanella | Genova | 4 | 40026 | Imola | BO | 3255666541 |
Query - 1
Create a query which will generate a table which contains surname, name and telephone number of all contacts in alphabetical order.
from contact
order by surname
surname | name | TelephNo |
---|---|---|
Cerruti | Vanessa | 3401112223 |
Curti | Giovanni | 3337894561 |
Feola | Giorgio | 3476644551 |
Feola | Beatrice | 3201597532 |
Fusco | Michele | 3209988774 |
Rossi | Luca | 3481234567 |
Verdi | Maria | 3804567894 |
Zancanella | Michela | 3255666541 |
Query - 2
Create a query which will generate a table which contains surname, name, street, streetNo and city of all the contacts who live in the province of Bologna.
from contact
where province="BO"
surname | name | street | streetNo | city |
---|---|---|---|---|
Feola | Giorgio | Modena | 82 | Bologna |
Feola | Beatrice | Modena | 82 | Bologna |
Zancanella | Michela | Genova | 4 | Imola |
Query - 3
Find name and telephone number of all the members of the "Feola" family.
from contact
where (surname="Feola")
name | surname | TelephNo |
---|---|---|
Giorgio | Feola | 3476644551 |
Beatrice | Feola | 3201597532 |
Query - 4
Let us find the list of all the cities in the database in reverse alphabetical order.
from contact
order by city desc
city |
---|
Trento |
Pergine |
Imola |
Borgo |
Bologna |
Note.
The symbol desc which stands for descendant generates a reverse sorting order.
Query - 5
Extract a list of all the cities in reverse alphabetical order. Each city must be displayed only once.
from contact
order by city desc
city |
---|
Trento |
Pergine |
Imola |
Borgo |
Bologna |
Note.
The symbol distinct will generate a table which contains only unique rows. Every row will be different from the others.
Query - 6
Retrieve all data of the contacts who live in Trentino and whose surname is "Feola".
from contact
where (surname="Feola") and (province="TN")
id | name | surname | street | streetNo | posCode | city | province | TelephNo |
---|
Query - 7
Retrieve a list of all contacts (surname and name) sorted alphabetically. If two contacts have the same surname, then their names must be listed in alphabetical order.
from contact
order by surname, name
surname | name |
---|---|
Cerruti | Vanessa |
Curti | Giovanni |
Feola | Beatrice |
Feola | Giorgio |
Fusco | Michele |
Rossi | Luca |
Verdi | Maria |
Zancanella | Michela |
Query - 8
Retrieve a list of all the cities in Trentino sorted alphabetically.
from contact
where (province="TN")
order by city
city |
---|
Borgo |
Pergine |
Trento |
Challenge
Challenge - 1
What table will the query below generate?
from contact
order by id
Challenge - 2
What table will the query below generate?
from contact
where (surname != "Feola")
order by posCode desc
Challenge - 3
Create a query which will return the ids of the contacts whose field "streetNo" is less than 10.
Challenge - 4
Extract a list of all the surnames (each surname must be displayed only once).
Challenge - 5
Create a query which will return surname and address of all the contacts who do not live in the province of Bologna (sorted alphabetically).