Given this table-
drop table casting;
drop table shows;
drop table dancers;
create table Shows(
Show_ID NUMBER NOT NULL,
Title VARCHAR2(30),
Composer_Name VARCHAR2(30),
Genre VARCHAR2(10),
Primary Key (Show_ID)
);
create table Dancers(
Dancer_ID NUMBER NOT NULL,
Dancer_Name VARCHAR2(30),
Country VARCHAR2(20),
Primary Key (Dancer_ID)
);
create table Casting(
Show_ID NUMBER NOT NULL,
Dancer_ID NUMBER NOT NULL,
Character_Name VARCHAR2(30),
Primary Key (Show_ID, Dancer_ID),
Foreign Key (Show_ID)references Shows,
Foreign Key (Dancer_ID) references Dancers
);
INSERT INTO Shows Values(100, 'Cats', 'Andrew Lloyd Webber', 'Fantasy');
INSERT INTO Shows Values(200, 'Les Miserables', 'Alain Boublil', 'Historical');
INSERT INTO Shows Values(300, 'Wicked', 'Stephen Schwartz', 'Fantasy');
INSERT INTO Shows VALUES(400, 'Phantom of the Opera', 'Andrew Lloyd Webber', 'Romance');
INSERT INTO Dancers Values(10, 'Sherlock Holmes', 'England');
INSERT INTO Dancers Values(15, 'Jane Austen', 'England');
INSERT INTO Dancers Values(20, 'John Knightly', 'USA');
INSERT INTO Casting VALUES (100, 10, 'Gus');
INSERT INTO Casting Values (200, 15, 'Fantine');
INSERT INTO Casting Values (300, 20, 'Wizard');
INSERT INTO Casting Values (400, 15, 'Christine');
How would you query this:
a. List the show titles and composers’ names of all shows with the dancers from England