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

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.

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