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.
The result format is in the following example.
Example 1:
Input:
Cinema table:
+----+------------+-------------+--------+
| id | movie | description | rating |
+----+------------+-------------+--------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card | Interesting | 9.1 |
+----+------------+-------------+--------+
Output:
+----+------------+-------------+--------+
| id | movie | description | rating |
+----+------------+-------------+--------+
| 5 | House card | Interesting | 9.1 |
| 1 | War | great 3D | 8.9 |
+----+------------+-------------+--------+
Explanation:
We have three movies with odd-numbered IDs: 1, 3, and 5. The movie with ID = 3 is boring so we do not include it in the answer.
Code in SQL
# MySQL query statement below
SELECT * FROM Cinema WHERE id %2 != 0 AND description != "boring" ORDER BY rating DESC;
This SQL query is written to retrieve data from a table called "Cinema." Let me explain it step by step in plain English:
SELECT *: This part of the query instructs the database to retrieve all columns (indicated by the asterisk) from the "Cinema" table.
FROM Cinema: It specifies that the data we want to retrieve is located in the "Cinema" table.
WHERE id % 2 != 0: Here, we're applying a filter to the rows. It's looking for rows where the "id" column is not divisible evenly by 2, which essentially means it's looking for rows with odd values in the "id" column.
AND description != "boring": In addition to the previous filter, this condition further refines the selection. It only includes rows where the "description" column does not contain the word "boring."
ORDER BY rating DESC: Finally, it specifies how the selected rows should be sorted. It arranges them in descending order (indicated by "DESC") based on the values in the "rating" column. So, the rows with the highest ratings will appear at the top of the result set.
In summary, this SQL query retrieves all columns for rows in the "Cinema" table where the "id" is odd and the "description" is not "boring," and it sorts these rows by their "rating" values in descending order.
Top comments (0)