[TIL8_23.1.16.] SQL Advanced | Assignment 3

ASSIGNMENT 3

과제 1

MySQL의DQL(Data Query Language)를 사용하여, 아래 결과가 출력될 수 있는 쿼리문을 작성해주세요. ‘2022-03-01’ 이후에 개봉한 영화들의 기본 정보와 해당 영화의 배급사의 정보를 다음과 같은 형태로 출력해주세요.

✍️💻 입출력

mysql> SELECT
    -> movies.id,
    -> movies.title,
    -> movies.rate,
    -> movies.open_date,
    -> distributors.business_name
    -> FROM movies
    -> INNER JOIN distributors
    -> ON movies.distributor_id=distributors.id
    -> WHERE movies.open_date >= "2022-03-01";
+----+-----------------------------+------+------------+------------------+
| id | title                       | rate | open_date  | business_name    |
+----+-----------------------------+------+------------+------------------+
|  3 | Result Of The Past          |  6.8 | 2022-03-01 | Nolan Unlimited  |
|  4 | Equality Of Time Travellers |  7.0 | 2022-04-01 | Thornhill and Co |
|  5 | Alerted By The Eyes         |  8.2 | 2022-05-01 | Nolan Unlimited  |
+----+-----------------------------+------+------------+------------------+

과제 2

페이지 하단 참고자료의 SQL 키워드들과 MySQL의DQL(Data Query Language)를 사용하여, 아래 결과가 출력될 수 있는 쿼리문을 작성해주세요. 특정 에이전시 회사(Agencyfluent)의 소속 배우들 정보와 해당 배우들이 출연한 영화 목록을 다음과 같은 형태로 출력해주세요.

✍️💻 입출력

mysql> SELECT
    -> actors.id AS actor_id,
    -> actors.name AS actor_name,
    -> JSON_ARRAYAGG(movies.title) AS filmography
    -> FROM movies_actors
    -> INNER JOIN movies ON movies.id = movies_actors.movie_id
    -> INNER JOIN actors ON movies_actors.actor_id = actors.id
    -> INNER JOIN agencies ON actors.agency_id = agencies.id
    -> WHERE actors.agency_id = 1
    -> GROUP BY actors.id;
+----------+------------------+-------------------------------------------------------------------------+
| actor_id | actor_name       | filmography                                                             |
+----------+------------------+-------------------------------------------------------------------------+
|        1 | Lottie Stevenson | ["Intruders In The Center Of The Earth", "Pilots With Spaceships"]      |
|        3 | Bret Blevins     | ["Intruders In The Center Of The Earth", "Equality Of Time Travellers"] |
|        5 | Aditya Malone    | ["Intruders In The Center Of The Earth"]                                |
|        7 | Gabriella Nava   | ["Result Of The Past"]                                                  |
|        9 | Alexandre Boyd   | ["Alerted By The Eyes"]                                                 |
+----------+------------------+-------------------------------------------------------------------------+
5 rows in set (0.01 sec)