More JOIN operations/ru

В этом руководстве рассматривается понятие JOIN. База данных состоит из трёх таблиц movie , actor and casting .

movie

id

title

yr

director

budget

gross

actor

id

name

casting

movieid

actorid

ord

More details about the database.

Summary

Фильмы 1962 года

Выведите фильмы, у которых yr равен 1962, а бюджет превышает 2000000 (выведите id и title).

SELECT id, title
 FROM movie
 WHERE yr=1962

SELECT id, title
 FROM movie
 WHERE yr=1962
   AND budget>2000000

Когда вышел «Гражданин Кейн»?

Выведите год выхода фильма «Гражданин Кейн» (Citizen Kane).

SELECT yr 
FROM movie 
WHERE title='Citizen Kane'

Фильмы «Звёздный путь»

Выведите все фильмы «Звёздный путь» (Star Trek), включая id, title и yr (названия всех этих фильмов начинаются со слов Star Trek). Отсортируйте результаты по году.

SELECT id,title, yr FROM movie
 WHERE title LIKE 'Star Trek%'
 ORDER BY yr

ID актрисы Гленн Клоуз

Какой номер id у актрисы Гленн Клоуз (Glenn Close)?

SELECT id FROM actor
  WHERE name= 'Glenn Close'

ID фильма «Касабланка»

Какой id у фильма «Касабланка» (Casablanca) 1942 года?

 SELECT id FROM movie WHERE title='Casablanca' and yr=1942

Get to the point

Актёрский состав «Касабланки»

Получите список актёров фильма «Касабланка» (Casablanca) 1942 года.

Актёрский состав — это список имён актёров, снимавшихся в фильме.

Используйте movieid=11768 (или значение, полученное в предыдущем задании).

SELECT name
  FROM casting, actor
  WHERE movieid=(SELECT id 
             FROM movie 
             WHERE title='Casablanca' and yr=1942)
    AND actorid=actor.id

Актёрский состав «Чужого»

Получите список актёров фильма «Чужой» (Alien)

SELECT name
  FROM movie, casting, actor
  WHERE title='Alien'
    AND movieid=movie.id
    AND actorid=actor.id

Фильмы с Харрисоном Фордом

Выведите фильмы, в которых снимался Харрисон Форд (Harrison Ford)

SELECT title
  FROM movie, casting, actor
 WHERE name='Harrison Ford'
    AND movieid=movie.id
    AND actorid=actor.id

Харрисон Форд во второстепенных ролях

Выведите фильмы, в которых снимался Харрисон Форд (Harrison Ford), но не в главной роли. Примечание: поле ord таблицы casting указывает позицию актёра. Если ord=1, то этот актёр исполняет главную роль

SELECT title
  FROM movie, casting, actor
 WHERE name='Harrison Ford'
    AND movieid=movie.id
    AND actorid=actor.id
  AND ord<>1

Главные актёры фильмов 1962 года

Выведите фильмы 1962 года вместе с актёрами, исполнившими в них главные роли.

SELECT title, name
  FROM movie, casting, actor
 WHERE yr=1962
    AND movieid=movie.id
    AND actorid=actor.id
    AND ord=1

---

Более сложные задания

---

Самые продуктивные годы Рока Хадсона

Какие годы были самыми продуктивными для Рока Хадсона (Rock Hudson)? Выведите год и количество фильмов, в которых он снялся, для каждого года, когда он снялся более чем в 2 фильмах.

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
WHERE name='Doris Day'
GROUP BY yr
HAVING COUNT(title) > 1

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
where name='Rock Hudson'
GROUP BY yr
HAVING COUNT(title) > 2

Главный актёр в фильмах с Джули Эндрюс

Выведите название фильма и актёра в главной роли для всех фильмов, в которых снималась Джули Эндрюс (Julie Andrews).

Джули Эндрюс снялась в ремейке фильма «Маленькая мисс Маркер» (Little Miss Marker) 1980 года, а не в оригинале 1934.

Поле Title не является уникальным, поэтому создайте в подзапросе таблицу с ID

SELECT movieid FROM casting
WHERE actorid IN (
  SELECT id FROM actor
  WHERE name='Julie Andrews')

SELECT title, name
  FROM movie, casting, actor
  WHERE movieid=movie.id
    AND actorid=actor.id
    AND ord=1
    AND movieid IN
    (SELECT movieid FROM casting, actor
     WHERE actorid=actor.id
     AND name='Julie Andrews')

С использованием вложенных запросов

С использованием нескольких JOIN

Актёры с 15 главными ролями

Получите список актёров, исполнивших не менее 15 главных ролей, в алфавитном порядке.

SELECT name
    FROM casting JOIN actor
      ON  actorid = actor.id
    WHERE ord=1
    GROUP BY name
    HAVING COUNT(movieid)>=15
ORDER BY name

Вышедшие в 1978 году

Выведите фильмы, вышедшие в 1978 году, отсортировав их сначала по количеству актёров в составе, а затем по названию.

  SELECT title, COUNT(actorid)
  FROM casting,movie                
  WHERE yr=1978
        AND movieid=movie.id
  GROUP BY title
  ORDER BY 2 DESC,1 ASC

с Артом Гарфанкелом

Выведите всех людей, которые работали с Артом Гарфанкелом (Art Garfunkel).

SELECT DISTINCT d.name
FROM actor d JOIN casting a ON (a.actorid=d.id)
JOIN casting b on (a.movieid=b.movieid)
JOIN actor c on (b.actorid=c.id
and c.name='Art Garfunkel')
WHERE d.id!=c.id

Очистить результаты

JOIN Quiz 2

На этом точно хватит. Вам ни при каких обстоятельствах не следует заглядывать в следующее руководство, посвящённое внешним соединениям.