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
906Blade Runner
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(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.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8