Question

Question: Write a single SQL query that, for each pair of actors, lists their two actor_ids
and the number of films in which both actors appeared.

DATABASE SCHEMA

CREATE TABLE actor (
actor_id INTEGER,
first_name TEXT,
last_name TEXT);

CREATE TABLE film (
film_id INTEGER,
title TEXT,
description TEXT,
length INTEGER);

CREATE TABLE film_actor (
actor_id INTEGER,
film_id INTEGER);

Answer 1

CREATE TABLE actor (
actor_id INTEGER,
first_name TEXT,
last_name TEXT);


CREATE TABLE film (
film_id INTEGER,
title TEXT,
description TEXT,
length INTEGER);


CREATE TABLE film_actor (
actor_id INTEGER,
film_id INTEGER);

ANSWER:

See in the question : we have to find the pair of actor who have worked in the same film and also find the 
No of film.

>> So here we have to check all the possible pair (means take join) if actor and also make join from the table
film_actor which will help to find the actor and film_id in which that actor present.

Then Query will be like:

    SELECT s.actor_id,p.actor_id,count(*) FROM actor AS s , actor AS p ,film_actor AS f , film_actor AS G
    WHERE 
    (s.actor_id <> p.actor_id) AND (s.actor_id = f.actor_id) AND (p.actor_id = G.actor_id) AND (f.film_id = G.film_id);

so here we can see that we have taken join to find all the possible combination and given many condition to select

pair of actor and count the no of film in which both actor is present.