[LeetCode] 595.Big Countries,1068. Product Sales Analysis 1

Select_Big Countries Basic Joins_Customer Who Visited but Not Make Any Transactions Basic Joins_Rising Temperature Subqueries_Employees Whose Manager Left the Company

595. Big Countries 

https://leetcode.com/problems/big-countries/ (아래 첨부)

Big Countries - LeetCode

Table: World
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| name        | varchar |
| continent   | varchar |
| area        | int     |
| population  | int     |
| gdp         | bigint  |
+-------------+---------+
name is the primary key (column with unique values) for this table.
Each row of this table gives information about the name of a country, the continent to which it belongs, its area, the population, and its GDP value.
 

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 a solution to find the name, population, and area of the big countries.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
World table:
+-------------+-----------+---------+------------+--------------+
| name        | continent | area    | population | gdp          |
+-------------+-----------+---------+------------+--------------+
| Afghanistan | Asia      | 652230  | 25500100   | 20343000000  |
| Albania     | Europe    | 28748   | 2831741    | 12960000000  |
| Algeria     | Africa    | 2381741 | 37100000   | 188681000000 |
| Andorra     | Europe    | 468     | 78115      | 3712000000   |
| Angola      | Africa    | 1246700 | 20609294   | 100990000000 |
+-------------+-----------+---------+------------+--------------+
Output: 
+-------------+------------+---------+
| name        | population | area    |
+-------------+------------+---------+
| Afghanistan | 25500100   | 652230  |
| Algeria     | 37100000   | 2381741 |
+-------------+------------+---------+

문제의 길이에 비해 답이 너무 간결하다. 정말 길지만 Big Countries로 규정하기 위해선 면적이나 인구 수 조건 중 하나만(=or) 만족하면 된다는 것에 착안해 답을 쓰면 끝이다 ....

select name, population, area 
from world 
where area >= 3000000 or population >= 25000000;

답이 너무 간결해서 난 내가 뭘 놓친 줄 알았다.... BUT? 성공  :)

1068. Product Sales Analysis 1

https://leetcode.com/problems/product-sales-analysis-i/

Product Sales Analysis I - LeetCode

맨 아래 Explanation은 조금 삭제하였다. 포인트는 테이블 2개를 붙여서 product_name, year, and price for each sale_id(the Sales table에 있음) 을 출력해내는 것이니까!

Table: Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) is the primary key (combination of columns with unique values) of this table.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows a sale on the product product_id in a certain year.
Note that the price is per unit.
 

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id is the primary key (column with unique values) of this table.
Each row of this table indicates the product name of each product.
 

Write a solution to report the product_name, year, and price for each sale_id in the Sales table.

Return the resulting table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Sales table:
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+
Output: 
+--------------+-------+-------+
| product_name | year  | price |
+--------------+-------+-------+
| Nokia        | 2008  | 5000  |
| Nokia        | 2009  | 5000  |
| Apple        | 2011  | 9000  |
+--------------+-------+-------+

우선적으로 한 번에 product_name, year, price을 출력해내야 하므로, table와 sales 를 join시켜주면서 시작하자. 이때 순서가 정해져 있기에 left join을 사용하였다.

SELECT p.product_name, s.year, s.price
FROM Sales s LEFT JOIN Product p ON
    s.product_id = p.product_id

* Join 의 종류에 대한 구분은 아래 이미지를 참고! 구글링하여 나온 이미지이다. 

사실 join이 조금 헷갈려서 찾아본 것도 있다 ㅎㅎ

그리고 결과는 성공 :-D

얼른 자야지 ....코