Advanced SQL queries

The relational schema:

MOVIE(TITLE,DIRECTOR,YEAR)
CAST(TITLE,ACTOR)

Queries:

1. Find the actors that only played in Star Wars and Return of Jedi.

   SELECT C1.ACTOR
   FROM CAST C1, CAST C2
   WHERE C1.TITLE='Star Wars' 
     AND C2.TITLE='Return of Jedi'
     AND C1.ACTOR=C2.ACTOR
     AND NOT EXISTS
         (SELECT * FROM CAST C3
          WHERE C3.ACTOR=C1.ACTOR
            AND C3.TITLE<>'Star Wars'
            AND C3.TITLE<>'Return of Jedi')
   

2. List all the actors and for each actor give the maximum number of movies he/she made in a single year.

   CREATE VIEW MA(T,D,Y,A) AS
     SELECT M.TITLE,M.DIRECTOR,M.YEAR,C.ACTOR
     FROM MOVIE M, CAST C
     WHERE M.TITLE=C.TITLE;
   CREATE VIEW MY(A,C) AS
     SELECT A, COUNT(*)
     FROM MA
     GROUP BY A,Y;
   SELECT A, MAX(C)
   FROM MY
   GROUP BY A;
   

3. Find the actors with the longest career span (the difference between the year of the latest and the year of the earliest movie of the actor).

   CREATE VIEW CAREER(A,S) AS
     SELECT A, MAX(YEAR)-MIN(YEAR)
     FROM MA
     GROUP BY A;
   SELECT A 
   FROM CAREER
   WHERE S=(SELECT MAX(S) FROM CAREER);
   

4. Find the actors that were never unemployed for more than 3 years. (We assume here that the actors are unemployed between two consecutive movies.)

   SELECT ACTOR FROM CAST C
   WHERE NOT EXISTS
     (SELECT * 
      FROM MA M1, MA M2
      WHERE M1.A=C.A
        AND M2.A=C.A
        AND M1.Y-3>M2.Y
        AND NOT EXISTS
        (SELECT *
         FROM MA M3
         WHERE M3.A=C.A
           AND M1.Y>M3.Y
           AND M3.Y>M2.Y))
   

5. Find all the actors that made more movies with Spielberg than with any other director.

   CREATE VIEW AD(A,D,CT)
   AS SELECT A, D, COUNT(TITLE)
      FROM MA
      GROUP BY A, D;
   
   SELECT A 
   FROM AD T1
   WHERE
     (SELECT CT FROM AD T2
      WHERE T2.D='Spielberg'
        AND T2.A=T1.A)
     >ALL
     (SELECT CT FROM AD T2
      WHERE T2.D<>'Spielberg'
        AND T2.A=T1.A)
   

The relational schema:

EMP(NAME,DEPT,SALARY)

Queries:

1. Find the employees who make more than twice the average salary in their department.

   CREATE VIEW DS(D,S,C) AS
     SELECT DEPT,SUM(SALARY),COUNT(*)
     FROM EMP
     GROUP BY DEPT;
   SELECT E.NAME
   FROM EMP E, DS 
   WHERE E.DEPT=DS.D
     AND E.SALARY>2*(DS.S/DS.C);
   

2. Find the departments whose salary total is more than twice the average departmental salary total.

   SELECT D
   FROM DS
   WHERE D.S>2*(SELECT AVG(S) FROM DS)
   

3. Find the employees whose salaries are among the top 100 salaries.

   SELECT E1.NAME
   FROM EMP E1 
   WHERE 99>= (SELECT COUNT(DISTINCT SALARY)
   	    FROM EMP E2
   	    WHERE E2.SALARY>E1.SALARY)