2. Not Boring Movies

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

1706518884894030 Image scaled to 55%

Expected Output

1706518909491837 Image scaled to 55%

Try it YourSelf

Database Exercise

Database Schema:


-- Database schema would be rendered here

Exercise Script:


-- Exercise script would be rendered here

Available actions: Execute

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    |
+----+------------+-------------+--------+