Not Boring Movies
문제 설명
Create table If Not Exists cinema (id int, movie varchar(255), description varchar(255), rating float(2, 1))
Truncate table cinema
insert into cinema (id, movie, description, rating) values ('1', 'War', 'great 3D', '8.9')
insert into cinema (id, movie, description, rating) values ('2', 'Science', 'fiction', '8.5')
insert into cinema (id, movie, description, rating) values ('3', 'irish', 'boring', '6.2')
insert into cinema (id, movie, description, rating) values ('4', 'Ice song', 'Fantacy', '8.6')
insert into cinema (id, movie, description, rating) values ('5', 'House card', 'Interesting', '9.1')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]
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.
입출력 예
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.
Oracle Query
select *
from cinema
where description!='boring' and mod(id, 2) = 1
order by rating desc;* 참고 링크 : https://leetcode.com/problems/not-boring-movies/discuss/1192216/simple-or-100-memory-or-oracle
MOD ( ) - 나눈 나머지 값 반환
select mod(3,2)
from dual
-- 결과값: 1* 참고 링크 : https://devjhs.tistory.com/404, https://20140501.tistory.com/80
출처
'코딩테스트 > Leet Code' 카테고리의 다른 글
| [Leet Code SQL] 183. Customers Who Never Order (0) | 2022.03.22 |
|---|---|
| [Leet Code SQL] 627. Swap Salary (0) | 2022.03.22 |
| [Leet Code SQL] 596. Classes More Than 5 Students (0) | 2022.03.10 |
| [Leet Code SQL] 595. Big Countries (0) | 2022.03.04 |
| [Leet Code] 217. Contains Duplicate python (0) | 2022.02.25 |