Problem
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| program_date | date |
| content_id | int |
| channel | varchar |
+---------------+---------+
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| content_id | varchar |
| title | varchar |
| Kids_content | enum |
| content_type | varchar |
+------------------+---------+
Write an SQL query to report the distinct titles of the kid-friendly movies streamed in June 2020.
Solution
Method 1 - Subquery
Code
Sql
SELECT title FROM Content
WHERE content_type = 'Movies' AND Kids_content = 'Y' AND content_id IN (
SELECT content_id FROM TVProgram
WHERE MONTH(program_date) = '6');
Method 2 - Join
Code
Sql
Alternative. This joins the tables rather than uses a subquery. It also makes sure the year is correct. Something I missed.
SELECT DISTINCT c.title
FROM Content c
JOIN TVProgram p
ON c.content_id = p.content_id
WHERE c.Kids_content = 'Y'
AND c.content_type = 'Movies'
AND MONTH(p.program_date) = 6
AND YEAR(p.program_date) = 2020;