Problem: Write an SQL query to report the movies with an odd-numbered ID and a description that is not "boring"
. Return the result table in descending order by rating
.
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 |
+----+------------+-------------+--------+
Result table:
+----+------------+-------------+--------+
| id | movie | description | rating |
+----+------------+-------------+--------+
| 5 | House card | Interesting | 9.1 |
| 1 | War | great 3D | 8.9 |
+----+------------+-------------+--------+
Mathematical Functions
Name | Description |
---|---|
ABS() | Return the absolute value |
ACOS() | Return the arc cosine |
ASIN() | Return the arc sine |
ATAN() | Return the arc tangent |
ATAN2() , ATAN() | Return the arc tangent of the two arguments |
CEIL() | Return the smallest integer value not less than the argument |
CEILING() | Return the smallest integer value not less than the argument |
CONV() | Convert numbers between different number bases |
COS() | Return the cosine |
COT() | Return the cotangent |
CRC32() | Compute a cyclic redundancy check value |
DEGREES() | Convert radians to degrees |
EXP() | Raise to the power of |
FLOOR() | Return the largest integer value not greater than the argument |
LN() | Return the natural logarithm of the argument |
LOG() | Return the natural logarithm of the first argument |
LOG10() | Return the base-10 logarithm of the argument |
LOG2() | Return the base-2 logarithm of the argument |
MOD() | Return the remainder 余数 |
PI() | Return the value of pi |
POW() | Return the argument raised to the specified power |
POWER() | Return the argument raised to the specified power |
RADIANS() | Return argument converted to radians |
RAND() | Return a random floating-point value |
ROUND() | Round the argument |
SIGN() | Return the sign of the argument |
SIN() | Return the sine of the argument |
SQRT() | Return the square root of the argument |
TAN() | Return the tangent of the argument |
TRUNCATE() | Truncate to specified number of decimal places |
mysql> select mod(234,10);
+-------------+
| mod(234,10) |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)
mysql> select 234%10;
+--------+
| 234%10 |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
mysql> select 234 mod 10;
+------------+
| 234 mod 10 |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
Solution
SELECT * FROM Cinema WHERE id%2!=0 AND description!='boring' ORDER BY rating desc;