SQL Challenges-1: Actors and Directors who jointly worked three or more movies

SQL Challenges-1: Exercise-28 with Solution

From the following tables write a SQL query to find those directors and actors who worked together at least three or more movies. Return the director and actor name.

Input:

Table: actor_test

Structure:

Data:

Table: director_test

Structure:

Data:

Input:

Table: movie_test

Structure:

Data:

Table: mov_direction_test

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

CREATE TABLE actor_test (act_id int not null unique, act_name varchar(255));
INSERT INTO actor_test VALUES( 101,'James Stewart');  
INSERT INTO actor_test VALUES( 102,'Deborah Kerr');  
INSERT INTO actor_test VALUES( 103,'Peter OToole');  
INSERT INTO actor_test VALUES( 104,'Robert De Niro');  
INSERT INTO actor_test VALUES( 105,'F. Murray Abraham');  
INSERT INTO actor_test VALUES( 106,'Harrison Ford');  
INSERT INTO actor_test VALUES( 107,'Bill Paxton');  
INSERT INTO actor_test VALUES( 108,'Stephen Baldwin');  
INSERT INTO actor_test VALUES( 109,'Jack Nicholson');  
INSERT INTO actor_test VALUES( 110,'Mark Wahlberg');

CREATE TABLE director_test (dir_id int not null unique, dir_name varchar(255));
INSERT INTO director_test VALUES(201,'Alfred Hitchcock ');
INSERT INTO director_test VALUES(202,'Jack Clayton');
INSERT INTO director_test VALUES(203,'James Cameron');
INSERT INTO director_test VALUES(204,'Michael Cimino');
INSERT INTO director_test VALUES(205,'Milos Forman');
INSERT INTO director_test VALUES(206,'Ridley Scott');
INSERT INTO director_test VALUES(207,'Stanley Kubrick');
INSERT INTO director_test VALUES(208,'Bryan Singer');
INSERT INTO director_test VALUES(209,'Roman Polanski');

CREATE TABLE movie_test(mov_id int not null unique, movie_name varchar(255));
INSERT INTO movie_test VALUES(901,'Vertigo');                     
INSERT INTO movie_test VALUES(902,'Aliens');                     
INSERT INTO movie_test VALUES(903,'Lawrence of Arabia');                     
INSERT INTO movie_test VALUES(904,'The Deer Hunter');                     
INSERT INTO movie_test VALUES(905,'True Lies');                     
INSERT INTO movie_test VALUES(906,'Blade Runner');                     
INSERT INTO movie_test VALUES(907,'Eyes Wide Shut');                     
INSERT INTO movie_test VALUES(908,'Titanic');                     
INSERT INTO movie_test VALUES(909,'Chinatown');                     
INSERT INTO movie_test VALUES(910,'Ghosts of the Abyss');

CREATE TABLE mov_direction_test (dir_id int, mov_id int, act_id int,
FOREIGN KEY(dir_id) REFERENCES director_test(dir_id),
FOREIGN KEY(act_id) REFERENCES actor_test(act_id),
FOREIGN KEY(mov_id) REFERENCES movie_test(mov_id)
);

INSERT INTO mov_direction_test VALUES(201,901,101);
INSERT INTO mov_direction_test VALUES(203,902,107);
INSERT INTO mov_direction_test VALUES(204,904,104);
INSERT INTO mov_direction_test VALUES(203,905,107);
INSERT INTO mov_direction_test VALUES(206,906,106);
INSERT INTO mov_direction_test VALUES(203,908,107);
INSERT INTO mov_direction_test VALUES(209,909,109);
INSERT INTO mov_direction_test VALUES(203,910,107);

SELECT dir_name, act_name
FROM mov_direction_test md
LEFT JOIN director_test dr ON md.dir_id=dr.dir_id
LEFT JOIN  actor_test ac ON md.act_id=ac.act_id
LEFT JOIN movie_test mt ON md.mov_id=mt.mov_id
GROUP BY dir_name,act_name
HAVING COUNT(*)>2;

Sample Output:

dir_name     |act_name   |
-------------|-----------|
James Cameron|Bill Paxton|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Distributor who purchased all types of item from the company.
Next: Students achieved 100 percent in every subject in every year.