sql 문제풀이 조각 모음 [MYSQL]

1. !=  사용 시 null은 포함되지 않는다.

-- [!=2] : 2를 제외한 숫자만 출력
select name from Customer where referee_id != 2

-- [!=2 and is null] : 2가 아닌 수 + null 출력
select name from Customer where referee_id != 2 or referee_id is null

 

2. null이 아닌 값을 찾을 땐 is not null 을 사용한다. ( != null (x))

 

3. coalesce() : 해당 컬럼값이 null일 시 대체할 값을 설정한다. 

coalesce(컬럼명, 대체값)

select Prices.product_id, coalesce(round(sum(price*units)/sum(units),2), 0) as average_price 
from Prices left outer join UnitsSold
on Prices.product_id = UnitsSold.product_id and purchase_date between start_date and end_date 
group by product_id

 

4. where 절에 집계함수를 사용할 수 없다. 

WHERE 절은 개별 행의 조건을 검사하는 것이 아니라 행 자체를 필터링한다. 집계 함수는 행 단위가 아닌 그룹 단위로 작동하기 때문에 WHERE 절에서는 그룹화된 결과에 대한 조건을 지정할 수 없다.

--count(activity_type)>0 를 where 절에 넣을 시 오류 발생 "Invalid use of group function"

select activity_date as day, count(DISTINCT user_id) as active_users from Activity 
where activity_date between '2019-06-28' and '2019-07-27' 
group by activity_date having count(activity_type)>0

 

5. count(*) : 해당 컬럼의 행의 수

-- a number that appeared only once in the MyNumbers table.

select num from MyNumbers group by num having count(*)=1

 

예시 문제

619. Biggest Single Number
Solved
Easy
Topics
Companies
SQL Schema
Pandas Schema
Table: MyNumbers

+-------------+------+
| Column Name | Type |
+-------------+------+
| num         | int  |
+-------------+------+
This table may contain duplicates (In other words, there is no primary key for this table in SQL).
Each row of this table contains an integer.
 

A single number is a number that appeared only once in the MyNumbers table.

Find the largest single number. If there is no single number, report null.

The result format is in the following example.

 

Example 1:

Input: 
MyNumbers table:
+-----+
| num |
+-----+
| 8   |
| 8   |
| 3   |
| 3   |
| 1   |
| 4   |
| 5   |
| 6   |
+-----+
Output: 
+-----+
| num |
+-----+
| 6   |
+-----+
Explanation: The single numbers are 1, 4, 5, and 6.
Since 6 is the largest single number, we return it.
Example 2:

Input: 
MyNumbers table:
+-----+
| num |
+-----+
| 8   |
| 8   |
| 7   |
| 7   |
| 3   |
| 3   |
| 3   |
+-----+
Output: 
+------+
| num  |
+------+
| null |
+------+
Explanation: There are no single numbers in the input table so we return null.

 

6. concat(문자열A, 문자열B) / substr(문자열, 시작위치, 문자길이)

-- concatenate 문자 연결하기
-- substring 문자 자르기

select user_id, concat(upper(substr(name, 1, 1)), lower(substr(name, 2))) as name from Users 
order by user_id

 

7. 공백 포함 문자 찾기 - % 문자%

-- 'conditions' contains 0 or more code separated by spaces. 
--  Type I Diabetes always starts with DIAB1 prefix.

SELECT patient_id, patient_name, conditions FROM Patients
WHERE conditions LIKE 'DIAB1%' OR conditions LIKE '% DIAB1%' OR conditions LIKE 'DIAB1% %';

 

8. delete문은 해당하는 행(레코드) 전체 또는 테이블 전체를 삭제할 때 사용한다. 

DELETE 문은 데이터베이스에서 레코드를 삭제할 때 사용된다. 보통은 특정 조건에 해당하는 레코드를 삭제하거나, 테이블의 모든 레코드를 삭제할 때 사용된다. 특정 컬럼을 모두 삭제하려면 DELETE 문이 아닌 ALTER TABLE 문을 사용해야 한다.

(x)
DELETE p1.id ~

(o)
ALTER TABLE Person
DROP COLUMN id;

 

9. group_concat(컬럼명 order by 컬럼명)

-- group_concat : 그룹 내의 값들을 콤마를 사용해 하나의 문자열로 합쳐준다.

select sell_date, count(distinct product) as num_sold, 
group_concat(distinct product order by product) as products 
from Activities group by sell_date order by sell_date

| sell_date  | num_sold | products                     |
| ---------- | -------- | ---------------------------- |
| 2020-05-30 | 3        | Basketball,Headphone,T-Shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |

 

10. in 연산자 뒤의 서브쿼리는 한 컬럼의 값만 추출해야 한다. 여러 컬럼이 있을 시 비교 불가.

select name from Employee where id in 
(select managerId from 
(select managerId, count(managerId) from Employee group by managerId having count(managerId)>=5) as t1)

 

11. 날짜 포맷 설정

--mysql
date_format(trans_date, '%Y-%m-%d')
 
 --oracle
 to_char(trans_date, 'yyyy-mm-dd')

 

12. 별칭 주의.

from -> on,join -> where -> gruop by, having -> select -> distinct -> order by 순으로 수행되기 때문에  select절의 별칭은 group by와 where절에서 사용할 수 없다. 필드를 직접 비교하면 된다.

(mysql에선 가능)

(x)
select to_char(trans_date, 'yyyy-mm') as month from Transactions group by month

(o)
select to_char(trans_date, 'yyyy-mm') as month from Transactions 
group by to_char(trans_date, 'yyyy-mm')

 

13. having 절에서는 집계된 열만 사용할 수 있다.

having 절은 그룹화된 결과 집합에 대한 필터링을 수행하는데, 이 때 having 절 내에서는 집계 함수를 사용하여 계산된 값에 대한 조건을 지정할 수 있다. 집계 함수인 sum, count, min, max등이 사용된 후에 조건을 걸어야 하며, 집계 함수 외의 다른 열은 having절에서 직접적으로 참조할 수 없다.

집계 함수를 사용하여 계산된 결과값만을 조건으로 지정해야 하며, 집계 함수 외의 열을 사용해야 한다면, 이를 where절에서 처리해야 한다.

(x) 
having s1.year = s2.first_year
-- s1.year 컬럼은 집계 함수가 아니므로 이 위치에 사용될 수 없다.

(x)
having min(order_date) = customer_pref_delivery_date
-- customer_pref_delivery_date는 집계 함수가 아니라 개별 레코드의 필드다. 따라서 HAVING 절에서 직접적으로 참조할 수 없다.

14. group by 절에 집계함수를 직접 사용할 수 없다. 

group by  절은 일반적으로 집계 함수가 아닌 열을 기준으로 그룹화하는 데 사용된다.

15. 동일한 조건을 on 조건절에 두는 것과 where 조건절에 두는 것, 이 둘의 결과는 다를 수도 있다.

on 절은 전체 테이블 비교하고, where 절은 해당 행별로 비교한다.
on 절은 두 테이블 간의 관계를 설정하고 특정 조인 조건을 지정하는 데 사용되며, 이것은 전체 테이블 간의 비교를 의미한다. 반면에 where 절은 이미 결합된 결과에 대해 특정 조건을 적용하여 결과를 필터링하는 데 사용되며, 이것은 각 행에 대한 비교를 의미한다.
즉, on 절은 결합된 결과를 만들기 위해 전체 테이블 간의 관계를 설정하고, where 절은 이미 결합된 결과에서 각 행에 대해 추가적인 조건을 적용하여 결과를 필터링하는 것이다.

select s1.product_id, first_year, quantity, price 
from Sales s1 join (select product_id, min(year) as first_year from Sales group by product_id) as s2 
on s1.product_id = s2.product_id where s1.year = s2.first_year

 

예시문제

1070. Product Sales Analysis III
Solved
Medium
Topics
Companies
SQL Schema
Pandas Schema
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 select the product id, year, quantity, and price for the first year of every product sold.

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_id | first_year | quantity | price |
+------------+------------+----------+-------+ 
| 100        | 2008       | 10       | 5000  |
| 200        | 2011       | 15       | 9000  |
+------------+------------+----------+-------+

 

16. mysql에서는 일반적으로 찾는 값이 없을 경우 빈 결과를 반환한다. 단, 서브쿼리가 빈 결과를 반환할 경우 외부 쿼리에서는 null을 반환한다.

select (select distinct salary from Employee order by salary desc limit 1 offset 1) as SecondHighestSalary

-- 서브쿼리 결과 : 빈칸 
-- 외부쿼리 결과 : null