Write an SQL query 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.
寫一個 SQL 查詢有關奇數和沒有「boring」描述的影片 依照百分比進行降冪排序結果。
Table: Cinema
+----------------+----------+ | Column Name | Type | +----------------+----------+ | id | int | | movie | varchar | | description | varchar | | rating | float | +----------------+----------+ id is the primary key 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]
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.
Solution:
1. 選擇標題 id, movie, description, rating。
2. 來自 Cinema 的 table。
3. 尋找 description 的字符中前後沒有’boring’。
4. 且 id % 2 的餘數為 1。(找奇數)
5. 依照 rating 做降冪排序。
Code.1:
SELECT id, movie, description, rating FROM Cinema WHERE (description NOT LIKE 'boring%' OR '%boring') AND id % 2 = 1 ORDER BY rating DESC;