• 01. 595 - Big Countries(리트코드)
• 02. 620 - Not Boring Movies(리트코드)
• 03. 182 - Duplicate Emails(리트코드)
• 04. 175 - Combine Two Tables(리트코드)

01. 595 - Big Countries(리트코드)

A country is big if:

• it has an area of at least three million (i.e., 3000000 km2), or
• it has a population of at least twenty-five million (i.e., 25000000).

Write an SQL query to report the name, population, and area of the big countries.

Return the result table in any order.

The query result format is in the following example.

-- 내 문제 풀이(정답)
SELECT name, population, area
FROM World
WHERE area >= 3000000 OR population >= 25000000;

02. 620 - Not Boring Movies(리트코드)

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.

-- 내 문제 풀이(정답)
SELECT id, movie, description, rating
FROM Cinema
WHERE id%2 = 1 AND description != 'boring'
ORDER BY rating DESC

-- MOD 함수 활용
SELECT id, movie, description, rating
FROM Cinema
WHERE MOD(id,2) = 1 AND description != 'boring'
ORDER BY rating DESC

03. 182 - Duplicate Emails(리트코드)

Write an SQL query to report all the duplicate emails.

Return the result table in any order.

The query result format is in the following example.

-- 내 문제 풀이(정답이긴 하지만 효율적 x => 수정필요)
SELECT P1.email
FROM Person P1 INNER JOIN Person P2 ON P1.id = P2.id
GROUP BY P1.email
HAVING COUNT(*) >= 2

-- 굳이 INNERJOIN 필요없는 문제였다..
SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Id) >= 2

04. 175 - Combine Two Tables(리트코드)

Write an SQL query to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.

Return the result table in any order.

The query result format is in the following example.

SELECT firstName, lastName, city, state
FROM Person P 
	 LEFT JOIN Address A ON P.personId = A.personId