SQL Question

[vman363]

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

[MSVSora]

Not an SQL expert but something like this?

 

SELECT SUBQUERY.Title, SUBQUERY.Composer
FROM
(
	SELECT shows.Title as Title, shows.Composer_Name as Composer, casting.Dancer_ID as Dancer_ID
	FROM shows
	INNER JOIN casting
	ON shows.Show_ID=casting.Show_ID
)
AS SUBQUERY
INNER JOIN dancers
on SUBQUERY.Dancer_ID=dancers.Dancer_ID
WHERE dancers.Country='England'

 

Edit:

 

To setup the database in my mysql setup had to change a bit of your initial SQL code to

drop table if exists casting;
drop table if exists shows;
drop table if exists dancers;


create table Shows(
Show_ID INTEGER NOT NULL,
Title VARCHAR(30),
Composer_Name VARCHAR(30),
Genre VARCHAR(10),
Primary Key (Show_ID)
);

create table Dancers(
Dancer_ID INTEGER NOT NULL,
Dancer_Name VARCHAR(30),
Country VARCHAR(20),
Primary Key (Dancer_ID)
);

create table Casting(
Show_ID INTEGER NOT NULL,
Dancer_ID INTEGER NOT NULL,
Character_Name VARCHAR(30),
Primary Key (Show_ID, Dancer_ID),
Foreign Key (Show_ID) references Shows(Show_ID),
Foreign Key (Dancer_ID) references Dancers(Dancer_ID)
);

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');