Advanced SQL queries

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)