Problem

Table: Cinema

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

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

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.

The query result format is in the following example.

Examples

Example 1:

Input: Cinema table:

1
2
3
4
5
6
7
8
9

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

1
2
3
4
5
6

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

Method 1 - Using Mod Operator or Function

Code

1
2
3

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

SELECT * FROM Cinema
WHERE description != 'boring' AND MOD(id, 2) != 0
ORDER BY rating DESC;

Method 2 - Using Bitwise & Operator

Code

1

select * from cinema where description <> 'boring' and id & 1 = 1 order by rating DESC