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

# 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:

FieldTypeNullKeyDefaultExtra
act_idint(11)NoPRI
act_namevarchar(255)YES

Data:

act_idact_name
101James Stewart
102Deborah Kerr
103Peter OToole
104Robert De Niro
105F. Murray Abraham
106Harrison Ford
107Bill Paxton
108Stephen Baldwin
109Jack Nicholson
110Mark Wahlberg

Table: director_test

Structure:

FieldTypeNullKeyDefaultExtra
dir_idint(11)NOPRI
dir_namevarchar(255)YES

Data:

dir_iddir_name
201Alfred Hitchcock
202Jack Clayton
203James Cameron
204Michael Cimino
205Milos Forman
206Ridley Scott
207Stanley Kubrick
208Bryan Singer
209Roman Polanski

Input:

Table: movie_test

Structure:

FieldTypeNullKeyDefaultExtra
mov_idint(11)NoPRI
movie_namevarchar(255)YES

Data:

mov_idmovie_name
901Vertigo
902Aliens
903Lawrence of Arabia
904The Deer Hunter
905True Lies
907Eyes Wide Shut
908Titanic
909Chinatown
910Ghosts of the Abyss

Table: mov_direction_test

Structure:

FieldTypeNullKeyDefaultExtra
dir_idint(11)YESMUL
mov_idint(11)YESMUL
act_idint(11)YESMUL

Data:

dir_idmov_idact_id
201901101
203902107
204904104
203905107
206906106
203908107
209909109
203910107

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(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: