
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
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".
from teaching
where subject="ICT"
teacher |
---|
Ursomando |
Caio |
Query - 2
Find out all the subjects taught by "Tizio".
from teaching
where teacher="Tizio"
subject |
---|
Italian |
History |
Query - 3
Find out name and subject for all the teachers who teach "English".
from teaching
where subject = "English"
subject | teacher |
---|---|
English | Sempronio |
Query - 4
Find all the data for all the teachers.
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".
from teaching
where subject = "Maths"
id |
---|
Query - 6
Extract a list of all the teachers.
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?
from teaching
where subject = "ICT"
Challenge - 2
What result will the query below give?
from teaching
where teacher = "Tizio"
Challenge - 3
What result will the query below give?
from teaching
where subject = "English"
Challenge - 4
What result will the query below give?
from teaching
Challenge - 5
What result will the query below give?
from teaching
where subject = "Maths"
Challenge - 6
What result will the query below give?
from teaching
where id = 5
Challenge - 7
What result will the query below give?
from teaching
where teacher = "Woolf"
Challenge - 8
What result will the query below give?
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 |