The database contains the following trablitsy: films, persons and creators. Creators has two foreign keys to films and persons, as well as the "Character of Participation" fields (Directed, Actor, Composer, etc.) and "Role". I want to display a list of films, the director of which also plays a major role in the film, indicating his surname and role.
create table films ( film_id tinyint identity(1, 1), film_name varchar(20) not null, film_studio varchar(25) not null, film_year int not null, film_country varchar(20) null, film_length tinyint not null, film_genre varchar(15) not null, constraint PK_films primary key(film_id), ); create table persons ( person_id tinyint identity(1, 1), person_name varchar(50) not null, person_bday date not null, person_dday date, constraint PK_persons primary key(person_id), ); create table creators ( creator_id tinyint identity(1, 1), creator_film tinyint not null, creator_person tinyint not null, creator_who varchar(20) not null, creator_role varchar(20), constraint PK_creators primary key(creator_id), constraint FK_CF foreign key(creator_film) references films(film_id), constraint FK_CP foreign key(creator_person) references persons(person_id), constraint CH_Who check(creator_who='director' or creator_who='actor' or creator_who='composer'), ); insert into films(film_name, film_studio, film_year, film_country, film_length, film_genre) values ('Film1', 'Studio1', 2018, 'USA', 100, 'Genre1') insert into films(film_name, film_studio, film_year, film_country, film_length, film_genre) values ('Film2', 'Studio2', 2018, 'USA', 120, 'Genre2') insert into films(film_name, film_studio, film_year, film_country, film_length, film_genre) values ('Film3', 'Studio3', 2000, 'England', 90, 'Genre3') insert into persons(person_name, person_bday, person_dday) values ('John Smitt', '1988-12-12', null) insert into persons(person_name, person_bday, person_dday) values ('Mel Gibson ', '1988-12-12', null) insert into persons(person_name, person_bday, person_dday) values ('Miley Cyrus', '2001-12-12', null) insert into persons(person_name, person_bday, person_dday) values ('Deadpool', '1999-12-12', null) insert into creators(creator_film, creator_person, creator_who) values (1, 1, 'Director') insert into creators(creator_film, creator_person, creator_who, creator_role) values (1, 1, 'Actor', 'Main') insert into creators(creator_film, creator_person, creator_who, creator_role) values (2, 3, 'Actor', 'Secondary') insert into creators(creator_film, creator_person, creator_who, creator_role) values (3, 4, 'Actor', 'Secondary') Expected output: Film1 John Smith Main
