2. Not boring movies

Grokking SQL for Tech Interviews

Ask Author

Back to course home

0% completed

Table of Contents

Problem

Example

Expected Output

Try it YourSelf

Solution

Step 1: SELECT \ FROM cinema

Step 2: Filtering

Step 3: ORDER BY rating DESC

Problem

Table: Cinema

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| id             | int      |
| movie          | varchar  |
| description    | varchar  |
| rating         | float    |
+----------------+----------+
id is the primary key (column with unique values) for this table.
Each row contains information about the name of a movie, its genre, and its rating.
rating is a 2 decimal places float in the range [0, 10]

Write a solution to report the movies with an odd-numbered ID and a description that is not "boring".

Return the result table ordered by rating in descending order.

Example

Expected Output

Try it YourSelf

MYSQL

. . . .

Solution

The goal is to retrieve records that adhere to specific criteria selectively. Initially, the query focuses on films with odd IDs, utilizing the modulo operation '%'. Simultaneously, records labeled as 'boring' in the description are excluded to enhance the overall quality of the selection.

Then, arrange the results by rating them in descending order, ensuring that the highest-rated films meeting the defined criteria are prioritized.

SELECT *
FROM   Cinema
WHERE  id % 2 = 1
       AND description != 'boring'
ORDER  BY rating DESC

Let's break down the query step by step:

Step 1: SELECT * FROM cinema

This step selects all columns (*) from the "cinema" table.

Output After Step 1:

+----+------------+-------------+--------+
| id | movie      | description | rating |
+----+------------+-------------+--------+
| 1  | War        | great 3D    | 8.9    |
| 2  | Science    | fiction     | 8.5    |
| 3  | Irish      | boring      | 6.2    |
| 4  | Ice song   | Fantasy     | 8.6    |
| 5  | House card | Interesting | 9.1    |
+----+------------+-------------+--------+

Step 2: Filtering

WHERE  id % 2 = 1 AND description != 'boring'

This step filters the rows based on two conditions:

id % 2 = 1 (Select only rows where the id is odd).
description != 'boring' (Exclude rows where the description is 'boring').

Output After Step 2:

+----+------------+-------------+--------+
| id | movie      | description | rating |
+----+------------+-------------+--------+
| 1  | War        | great 3D    | 8.9    |
| 5  | House card | Interesting | 9.1    |
+----+------------+-------------+--------+

Step 3: ORDER BY rating DESC

ORDER  BY rating DESC

This step orders the result set based on the "rating" column in descending order.

Final Output:

+----+------------+-------------+--------+
| id | movie      | description | rating |
+----+------------+-------------+--------+
| 5  | House card | Interesting | 9.1    |
| 1  | War        | great 3D    | 8.9    |
+----+------------+-------------+--------+

Mark as Completed

Table of Contents

Problem

Example

Expected Output

Try it YourSelf

Solution

Step 1: SELECT \ FROM cinema

Step 2: Filtering

Step 3: ORDER BY rating DESC