Database desing: entity - attribute - primary key
Query: select - from - where - *

Lesson 1

Task

You are to create a database for keeping track of all of the teachers in a school and the subject(s) that each teacher teaches.

Conceptual data model: ER

Layer 1 TEACHING teacher subject

Note.

The entity "teaching" has the attributes "teacher" and "subject".

Logical data model: relational

teaching (id, teacher, subject)

Note.

In the relational data model we introduce the primary key: id.

Note.

The primary key must be underlined.

An instance of database

id teacher subject
1 Ursomando ICT
2 Ursomando Information Systems
3 Tizio Italian
4 Caio ICT
5 Sempronio English
6 Tizio History

Note.

The primary key is a number that is unique to each record in a database. This avoids duplicate records.

Query - 1

Find out all the teachers who teach "ICT".

select teacher
from teaching
where subject="ICT"
teacher
Ursomando
Caio

Query - 2

Find out all the subjects taught by "Tizio".

select subject
from teaching
where teacher="Tizio"
subject
Italian
History

Query - 3

Find out name and subject for all the teachers who teach "English".

select subject, teacher
from teaching
where subject = "English"
subject teacher
English Sempronio

Query - 4

Find all the data for all the teachers.

select *
from teaching
id teacher subject
1 Ursomando ICT
2 Ursomando Information Systems
3 Tizio Italian
4 Caio ICT
5 Sempronio English
6 Tizio History

Query - 5

Find the id for all the teachers who teach "Maths".

select id
from teaching
where subject = "Maths"
id

Query - 6

Extract a list of all the teachers.

select teacher
from teaching
teacher
Ursomando
Ursomando
Tizio
Caio
Sempronio
Tizio

Challenge

An instance of database

id teacher subject
1 Woolf Italian
2 D’annunzio Italian
3 D’annunzio History
4 Hitler History
5 Hitler ICT
6 Einstein Maths
7 Einstein ICT
8 Woolf English

Challenge - 1

What result will the query below give?

select teacher
from teaching
where subject = "ICT"

Challenge - 2

What result will the query below give?

select subject
from teaching
where teacher = "Tizio"

Challenge - 3

What result will the query below give?

select teacher, subject
from teaching
where subject = "English"

Challenge - 4

What result will the query below give?

select *
from teaching

Challenge - 5

What result will the query below give?

select *
from teaching
where subject = "Maths"

Challenge - 6

What result will the query below give?

select *
from teaching
where id = 5

Challenge - 7

What result will the query below give?

select teacher, id
from teaching
where teacher = "Woolf"

Challenge - 8

What result will the query below give?

select subject, teacher
from teaching
where subject = "Italian"

Challenge - 9

What result will the query below give?

id teacher subject
6 Einstein Maths

Challenge - 10

Create a query which will generate the table below

id subject
5 ICT
7 ICT

Challenge - 11

Create a query which will generate the table below

teacher
Woolf
D’annunzio
D’annunzio
Hitler
Hitler
Einstein
Einstein
Woolf

Challenge - 12

Create a query which will generate the table below

id subject
6 Maths