Desing: composite attributes - optional attributes
Query: order by - distinct

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

Layer 1 name surname address telephNo posCode city streetNo street province CONTACT title (...) -

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 DrGiorgio 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.

select surname, name, TelephNo
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.

select surname, name, street, streetNo, city
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.

select name, surname, TelephNo
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.

select city
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.

select distinct city
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".

select *
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.

select surname, name
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.

select distinct city
from contact
where (province="TN")
order by city
city
Borgo
Pergine
Trento

Challenge

Challenge - 1

What table will the query below generate?

select *
from contact
order by id

Challenge - 2

What table will the query below generate?

select distinct posCode
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).