SQL_코드카타(2024.01.29)

91. Not Boring Movies

테이블 : Cinema

+----------------+----------+
| Column Name | Type |
+----------------+----------+
| id | int |
| movie | varchar |
| description | varchar |
| rating | float |
+----------------+----------+
id is the primary key (column with unique values) 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]

문제

홀수 ID와 "boring"가 아닌 영화를 보고하는 솔루션을 작성하세요 .
로 정렬된 결과 테이블을 rating 내림차순 으로 반환합니다 .

output

+----+------------+-------------+--------+
| id | movie | description | rating |
+----+------------+-------------+--------+
| 5 | House card | Interesting | 9.1 |
| 1 | War | great 3D | 8.9 |
+----+------------+-------------+--------+

👩🏻‍💻My Coding

• 홀수 ID = id%2 <>0
• description <> "boring"
• where로 묶어주기

select * 
from cinema 
where description <>"boring" and id % 2 <> 0
order by rating desc 

---

92. Average Selling Price

테이블1 : Price

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| start_date | date |
| end_date | date |
| price | int |
+---------------+---------+
(product_id, start_date, end_date) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates the price of the product_id in the period from start_date to end_date.
For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id.

테이블 2 : UnitsSold

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| purchase_date | date |
| units | int |
+---------------+---------+
This table may contain duplicate rows.
Each row of this table indicates the date, units, and product_id of each product sold.

문제

각 제품의 평균 판매 가격을 구하는 해를 작성하세요. 소수점 이하 2자리로 반올림 average_price 해야 합니다 .
어떤 순서로든 결과 테이블을 반환합니다 .

Output

Output:
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1 | 6.96 |
| 2 | 16.96 |
+------------+---------------+
Explanation:
Average selling price = Total Price of Product / Number of products sold.
Average selling price for product 1 = ((100 5) + (15 20)) / 115 = 6.96
Average selling price for product 2 = ((200 15) + (30 30)) / 230 = 16.96

👩🏻‍💻 My Coding

select product_id,
    round(price_sum / unit_sum, 2) as average_price
from(
    select p.product_id,
    (p.price * u.units) as price_sum,
    sum(u.units) as unit_sum
    from prices p left join unitssold u on p.product_id = u.product_id
    group by 1
)a

조인에 대한 조건이 더 필요하다.
문제를 자세히 보면 날짜가 있는데 start date와 end_date 사이에 purchase_date가 위치한다.
조인에 between 조건을 함께 쓴다

select p.product_id,
round(sum(p.price * u.units) / sum(u.units),2) as average_price
from prices p left join unitssold u on p.product_id = u.product_id
    and u.purchase_date between p.start_date and p.end_date 
group by 1

---

92. Project Employees I

테이블 1 : Project

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
(project_id, employee_id) is the primary key of this table.
employee_id is a foreign key to Employee table.
Each row of this table indicates that the employee with employee_id is working on the project with project_id.

테이블2 : Employee

+------------------+---------+
| Column Name | Type |
+------------------+---------+
| employee_id | int |
| name | varchar |
| experience_years | int |
+------------------+---------+
employee_id is the primary key of this table. It's guaranteed that experience_years is not NULL.
Each row of this table contains information about one employee.

문제

각 프로젝트에 대한 모든 직원의 평균 경력 연수를 2자리로 반올림하여 보고하는 SQL 쿼리를 작성합니다 .
어떤 순서로든 결과 테이블을 반환합니다 .

output

+-------------+---------------+
| project_id | average_years |
+-------------+---------------+
| 1 | 2.00 |
| 2 | 2.50 |
+-------------+---------------+
Explanation: The average experience years for the first project is (3 + 2 + 1) / 3 = 2.00 and for the second project is (3 + 2) / 2 = 2.50

👩🏻‍💻 My Coding

select p.project_id,
       round(avg(experience_years),2) as average_years
from project p inner join employee e on p.employee_id = e.employee_id
group by 1