11. ์ฌ๋ฌ ๊ธฐ์ค์ผ๋ก ์ ๋ ฌํ๊ธฐ
select ANIMAL_ID, NAME, DATETIME
from ANIMAL_INS
order by NAME, DATETIME desc;
12. ์ด๋ฆ์ el์ด ๋ค์ด๊ฐ๋ ๋๋ฌผ ์ฐพ๊ธฐ
select ANIMAL_ID, NAME
from ANIMAL_INS
where ANIMAL_TYPE = 'Dog' and NAME like '%el%'
order by NAME;
13. ๋์ด ์ ๋ณด๊ฐ ์๋ ํ์ ์ ๊ตฌํ๊ธฐ
select count(*) as USERS
from USER_INFO
where AGE is null;
14. ๊ฐ์ฅ ๋น์ผ ์ํ ๊ตฌํ๊ธฐ
select max(PRICE) as MAX_PRICE
from PRODUCT;
15. NULL ์ฒ๋ฆฌํ๊ธฐ
select ANIMAL_TYPE
, ifnull(NAME, 'No name')
, SEX_UPON_INTAKE
from ANIMAL_INS
order by ANIMAL_ID;
16. ๊ฒฝ๊ธฐ๋์ ์์นํ ์ํ์ฐฝ๊ณ ๋ชฉ๋ก ์ถ๋ ฅํ๊ธฐ
select WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, ifnull(FREEZER_YN, 'N')
from FOOD_WAREHOUSE
where ADDRESS like '๊ฒฝ๊ธฐ๋%'
order by WAREHOUSE_ID;
17. ๊ฐ์๋์ ์์นํ ์์ฐ๊ณต์ฅ ๋ชฉ๋ก ์ถ๋ ฅํ๊ธฐ
select FACTORY_ID, FACTORY_NAME, ADDRESS
from FOOD_FACTORY
where ADDRESS like '๊ฐ์๋%'
order by FACTORY_ID;
18. DATETIME์์ DATE๋ก ํ ๋ณํ
select ANIMAL_ID
, NAME
, date_format(DATETIME, '%Y-%m-%d') as '๋ ์ง'
from ANIMAL_INS
order by ANIMAL_ID;
- ์ค๋ฅ ์ํฉ
- date(DATETIME) ์ผ๋ก ์๋ → ' 2018-01-22 00:00:00' ํ์์ผ๋ก ์ถ๋ ฅ๋จ.
- date_format(DATETIME, 'yyyy-mm-dd') ์๋ → ์๋ฌ
- ์ต์ข
์๋ ๋ฐฉ๋ฒ
date_format(DATETIME, '%Y-%m-%d')
19. ํ๋ถ์ธ๊ณผ ๋๋ ์ผ๋ฐ์ธ๊ณผ ์์ฌ ๋ชฉ๋ก ์ถ๋ ฅํ๊ธฐ
select DR_NAME
, DR_ID
, MCDP_CD
, date_format(HIRE_YMD, '%Y-%m-%d')
from DOCTOR
where MCDP_CD in ('CS', 'GS')
order by HIRE_YMD desc, DR_NAME;
20. ๊ฐ๊ฒฉ์ด ์ ์ผ ๋น์ผ ์ํ์ ์ ๋ณด ์ถ๋ ฅํ๊ธฐ
select PRODUCT_ID
, PRODUCT_NAME
, PRODUCT_CD
, CATEGORY
, PRICE
from FOOD_PRODUCT
order by PRICE desc
limit 1;
21. ์ด๋ฆ์ด ์๋ ๋๋ฌผ์ ์์ด๋
select ANIMAL_ID
from ANIMAL_INS
where NAME is null
order by ANIMAL_ID;
22. ์กฐ๊ฑด์ ๋ง๋ ํ์์ ๊ตฌํ๊ธฐ
select count(USER_ID) as USERS
from USER_INFO
where JOINED between '2021-01-01' and '2021-12-31'
and AGE between 20 and 29;
23. ์ค์ฑํ ์ฌ๋ถ ํ์
ํ๊ธฐ
select ANIMAL_ID, NAME
, case when SEX_UPON_INTAKE in ('Neutered %', 'Spayed %') then 'O'
else 'X'
end '์ค์ฑํ'
from ANIMAL_INS
order by ANIMAL_ID;
select ANIMAL_ID, NAME
, case when SEX_UPON_INTAKE like 'Neutered %' or 'Spayed %' then 'O'
else 'X'
end '์ค์ฑํ'
from ANIMAL_INS
order by ANIMAL_ID;
- ์ค๋ฅ ์ํฉ
case when ์ปฌ๋ผ in (๊ฐ1, ๊ฐ2)์ ์์ผ๋์นด๋(%) ์ฌ์ฉํด์ ์ค๋ฅ.
- ์ต์ข
์๋ ๋ฐฉ๋ฒ
์์ผ๋์นด๋(%) ์ฌ์ฉ ๊ฐ๋ฅํ LIKE ๊ตฌ๋ฌธ ์ฌ์ฉ
24. ์นดํ
๊ณ ๋ฆฌ ๋ณ ์ํ ๊ฐ์ ๊ตฌํ๊ธฐ
select substr(PRODUCT_CODE, 1, 2) as CATEGORY
, count(distinct product_id) as PRODUCTS
from PRODUCT
group by 1
order by 1;
25. ๊ณ ์์ด์ ๊ฐ๋ ๋ช ๋ง๋ฆฌ ์์๊น
select ANIMAL_TYPE
, count(distinct ANIMAL_ID) as count
from ANIMAL_INS
group by ANIMAL_TYPE
order by ANIMAL_TYPE asc;
26. ์
์ ์๊ฐ ๊ตฌํ๊ธฐ(1)
select hour(DATETIME) as HOUR
, count(ANIMAL_ID) as COUNT
from ANIMAL_OUTS
where hour(DATETIME) between 9 and 19
group by hour(DATETIME)
order by hour(DATETIME);
- ์ค๋ฅ ์ํฉ
- ์๋ ๋ฐฉ๋ฒ
- ์ต์ข
๋ฌธ์ ํด๊ฒฐ ๋ฐฉ๋ฒ
27. ์ง๋ฃ๊ณผ๋ณ ์ด ์์ฝ ํ์ ์ถ๋ ฅํ๊ธฐ
select MCDP_CD as '์ง๋ฃ๊ณผ ์ฝ๋',
count(distinct PT_NO) as '5์์์ฝ๊ฑด์'
from APPOINTMENT
where date_format(APNT_YMD, '%Y-%m') = '2022-05'
group by MCDP_CD
order by count(distinct PT_NO), MCDP_CD;
28. 12์ธ ์ดํ์ธ ์ฌ์ ํ์ ๋ชฉ๋ก ์ถ๋ ฅํ๊ธฐ
select PT_NAME, PT_NO, GEND_CD, AGE, coalesce(TLNO, 'NONE') as TLNO
from PATIENT
where AGE <= 12 and GEND_CD='W'
order by AGE desc, PT_NAME;
29. ์ธ๊ธฐ ์๋ ์์ด์คํฌ๋ฆผ
select FLAVOR
from FIRST_HALF
order by TOTAL_ORDER desc, SHIPMENT_ID;
30. ์๋์ฐจ ์ข
๋ฅ ๋ณ ํน์ ์ต์
์ด ํฌํจ๋ ์๋์ฐจ ์ ๊ตฌํ๊ธฐ
select CAR_TYPE
, count(distinct CAR_ID) as CARS
from CAR_RENTAL_COMPANY_CAR
where OPTIONS like '%ํตํ์ํธ%' or '%์ด์ ์ํธ%' or '%๊ฐ์ฃฝ์ํธ%'
group by CAR_TYPE
order by CAR_TYPE
select CAR_TYPE
, count(distinct CAR_ID) as CARS
from CAR_RENTAL_COMPANY_CAR
where OPTIONS like '%ํตํ์ํธ%' or OPTIONS like '%์ด์ ์ํธ%' or OPTIONS like '%๊ฐ์ฃฝ์ํธ%'
group by CAR_TYPE
order by CAR_TYPE
- ์ค๋ฅ ์ํฉ
where ์ปฌ๋ผ์ ์กฐ๊ฑด์ or๋ก ๋ฌ ๋, ๋งค๋ฒ ์ปฌ๋ผ์ ์ ์ง ์์.
- ์ต์ข
์๋ ๋ฐฉ๋ฒ
or๋ก ์ฌ๋ฌ ์กฐ๊ฑด์ ๋ถ์ผ ๋, '์ปฌ๋ผ like ์กฐ๊ฑด'์ ๋งค๋ฒ ์ ์ด์ผ ํจ.