A video streaming service uses a relational database with two tables. FILM FilmID Title Director Genre Rating ReleaseYear F01 Space Voyager K. Adams Sci-Fi ...
Question 1 Report
A video streaming service uses a relational database with two tables.
FILM
| FilmID | Title | Director | Genre | Rating | ReleaseYear |
|---|---|---|---|---|---|
| F01 | Space Voyager | K. Adams | Sci-Fi | PG | 2022 |
| F02 | Silent Echo | M. Rivera | Thriller | 15 | 2021 |
| F03 | Ocean Quest | K. Adams | Adventure | PG | 2023 |
| F04 | Night Shift | L. Wong | Thriller | 18 | 2020 |
| F05 | Golden Days | M. Rivera | Drama | 12 | 2023 |
VIEWING
| ViewID | UserID | FilmID | ViewDate | Rating |
|---|---|---|---|---|
| V01 | U100 | F01 | 2024-04-01 | 4 |
| V02 | U200 | F03 | 2024-04-02 | 5 |
| V03 | U100 | F02 | 2024-04-03 | 3 |
| V04 | U300 | F01 | 2024-04-03 | 5 |
| V05 | U200 | F05 | 2024-04-05 | 4 |
Note: the Rating field in VIEWING stores user ratings (1 to 5 stars), which is different from the age Rating in FILM.
(a) Identify the primary key and foreign key in the VIEWING table. [2]
(b) Write an SQL query to find all films directed by 'K. Adams' released after 2021. [2]
Answer Details
(a) In the VIEWING table: [2]
- Primary key: ViewID [1]. It uniquely identifies each viewing record. No two rows share the same ViewID.
- Foreign key: FilmID [1]. It references the FilmID primary key in the FILM table, linking each viewing record to the film that was watched.
Note that UserID could also be considered a foreign key if a USER table existed, but based on the tables shown, only FilmID links to another table in the schema.
(b) Finding all films directed by K. Adams released after 2021: [2]
SELECT *
FROM FILM
WHERE Director = 'K. Adams' AND ReleaseYear > 2021[1] for WHERE Director = 'K. Adams', [1] for AND ReleaseYear > 2021.
Both conditions must be true simultaneously, so AND is the correct operator. The expected result:
| FilmID | Title | Director | Genre | Rating | ReleaseYear |
|---|---|---|---|---|---|
| F01 | Space Voyager | K. Adams | Sci-Fi | PG | 2022 |
| F03 | Ocean Quest | K. Adams | Adventure | PG | 2023 |
F01 (2022) and F03 (2023) are both directed by K. Adams and released after 2021. Note the question says "after 2021" (strictly greater than), so a film from 2021 would not be included.
Download The App On Google Playstore
Everything you need to excel in your exams
