Un jeu d’essai :
1 2 3 4 5 6 7 8 9
| insert into SERIES (seriesTitle, releaseYear, alsoKnownAs, channelId) values
('BUGS', 1995, '', (select channelId from CHANNEL where channelName = 'BBC'))
, ('Vingt ans dans un mur', 1948, 'La vie d''une brique', (select channelId from CHANNEL where channelName = 'M6'))
, ('Vingt ans au plafond', 1950, 'La vie d''une poutre', (select channelId from CHANNEL where channelName = 'M6'))
, ('Vingt ans dans un toit', 1960, 'La vie d''une ardoise', (select channelId from CHANNEL where channelName = 'M6'))
, ('The Avengers', 1975, '', (select channelId from CHANNEL where channelName = 'BBC'))
, ('McGyver', 1984,'',(select channelId from CHANNEL where channelName = 'ABC'))
, ('Maguy', 1983,'',(select channelId from CHANNEL where channelName = 'FR3'))
, ('Agence Acapulco', 1993, '', (select channelId from CHANNEL where channelName = '?')); |
1 2
| select seriesTitle, releaseYear, alsoKnownAs, channelName
from SERIES as s join CHANNEL as c on s.channelId =c.channelId ; |
=>
seriesTitle releaseYear alsoKnownAs channelName
BUGS 1995 BBC
Vingt ans dans un mur 1948 La vie d'une brique M6
Vingt ans au plafond 1950 La vie d'une poutre 6
Vingt ans dans un toit 1960 La vie d'une ardoise M6
The Avengers 1975 BBC
McGyver 1984 ABC
Maguy 1983 FR3
Agence Acapulco 1993 ?
1 2 3 4 5 6
| insert into PERSON values
('Onésime Durand'), ('Yves-Xavier Dupont')
, ('Ian Hendry'), ('Arthur Hewlett'), ('Douglas Muir'), ('Michael Troy'), ('Alison Armitage')
, ('Honor Blackman'), ('Julie Stevens'), ('Jon Rollason'), ('Paul Whitsum'), ('Patrick Newell')
, ('Diana Rigg')
; |
select personName from PERSON
=>
personName
Alison Armitage
Arthur Hewlett
Diana Rigg
Douglas Muir
Honor Blackman
Ian Hendry
Jon Rollason
Julie Stevens
Michael Troy
Onésime Durand
Patrick Newell
Paul Whitsum
Yves-Xavier Dupont
1 2 3 4 5 6
| insert into CHAR_PLAYED values
('Donald'), ('Mickey'), ('Le couvreur'), ('Le charpentier')
, ('Dr David Keel'), ('Le Supérieur'), ('Carol Wilson'), ('One-Ten')
, ('Cathy Gale'), ('Miz Emma Peel'), ('Tommy Chase'), ('Katherine Cat')
; |
select characterName from CHAR_PLAYED ;
=>
Donald
Mickey
Le couvreur
Le charpentier
Dr David Keel
Le Supérieur
Carol Wilson
One-Ten
Cathy Gale
Miz Emma Peel
Tommy Chase
Katherine Cat
insert into CASTING (seriesId, periode, personId, characterId, appearanceOrder) values
(
(select seriesId from SERIES where seriesTitle = 'Vingt ans dans un toit')
, ''
, (select personId from PERSON where personName='Onésime Durand')
, (select characterId from CHAR_PLAYED where characterName ='Le couvreur')
, 1
)
,
(
(select seriesId from SERIES where seriesTitle = 'Vingt ans dans un toit')
, ''
, (select personId from PERSON where personName='Yves-Xavier Dupont')
, (select characterId from CHAR_PLAYED where characterName ='Le charpentier')
, 1
)
,
(
(select seriesId from SERIES where seriesTitle = 'Vingt ans dans un mur')
, '1948'
, (select personId from PERSON where personName='Onésime Durand')
, (select characterId from CHAR_PLAYED where characterName='Donald')
, 1
)
,
(
(select seriesId from SERIES where seriesTitle = 'Vingt ans dans un mur')
, '1949'
, (select personId from PERSON where personName='Onésime Durand')
, (select characterId from CHAR_PLAYED where characterName='Mickey')
, 2
)
,
(
(select seriesId from SERIES where seriesTitle = 'Vingt ans dans un mur')
, '1948'
, (select personId from PERSON where personName='Yves-Xavier Dupont')
, (select characterId from CHAR_PLAYED where characterName='Mickey')
, 1
)
,
(
(select seriesId from SERIES where seriesTitle = 'Vingt ans dans un mur')
,'1949'
, (select personId from PERSON where personName='Yves-Xavier Dupont')
, (select characterId from CHAR_PLAYED where characterName='Donald')
, 2
)
,
(
(select seriesId from SERIES where seriesTitle = 'Agence Acapulco')
, '1993'
, (select personId from PERSON where personName='Michael Troy')
, (select characterId from CHAR_PLAYED where characterName='Tommy Chase')
, 1
)
,
(
(select seriesId from SERIES where seriesTitle = 'Agence Acapulco')
, '1998'
, (select personId from PERSON where personName='Michael Troy')
, (select characterId from CHAR_PLAYED where characterName='Tommy Chase')
, 2
)
,
(
(select seriesId from SERIES where seriesTitle = 'Agence Acapulco')
, '1993'
, (select personId from PERSON where personName='Alison Armitage')
, (select characterId from CHAR_PLAYED where characterName='Katherine Cat')
, 1
)
,
(
(select seriesId from SERIES where seriesTitle = 'Agence Acapulco')
, '1998'
, (select personId from PERSON where personName='Alison Armitage')
, (select characterId from CHAR_PLAYED where characterName='Katherine Cat')
, 2
)
;
1 2 3 4 5 6
| select seriesTitle, periode, personName, characterName, appearanceOrder
from CASTING as c
join SERIES as s on c.seriesId = s.seriesId
join PERSON as p on c.personId = p.personId
join CHAR_PLAYED as h on c.characterId = h.characterId
order by seriesTitle, characterName, appearanceOrder ; |
=>
seriesTitle periode personName characterName appearanceOrder
Agence Acapulco 1993 Alison Armitage Katherine Cat 1
Agence Acapulco 1998 Alison Armitage Katherine Cat 2
Agence Acapulco 1993 Michael Troy Tommy Chase 1
Agence Acapulco 1998 Michael Troy Tommy Chase 2
Vingt ans dans un mur 1948 Onésime Durand Donald 1
Vingt ans dans un mur 1949 Yves-Xavier Dupont Donald 2
Vingt ans dans un mur 1948 Yves-Xavier Dupont Mickey 1
Vingt ans dans un mur 1949 Onésime Durand Mickey 2
Vingt ans dans un toit Yves-Xavier Dupont Le charpentier 1
Vingt ans dans un toit Onésime Durand Le couvreur 1
Partager