[SQL] 숫자, 문자열 함수, 날짜 함수 연습문제(프로그래머스)

SQL 연습문제 6-1

상품정보(products) 테이블에서 상품의 id, 상품명(name), 판매가격(retail_price)를 조회합니다.

판매가격은 소수점 2자리에서 반올림 합니다.

SELECT id, name, ROUND(retail_price, 2) AS RETAIL_PRICE
FROM `thelook_ecommerce.products`

SQL 연습문제 6-2

회원(users) 테이블에서 나이가 홀수인 유저만 조회하세요.

조회 항목은 id, first_name, last_name, age 입니다.

SELECT id, first_name, last_name, age
FROM `thelook_ecommerce.users`
WHERE MOD(age, 2) = 1

-> WHERE 잊지마

 

SQL 연습문제 6-3

회원(users) 테이블에서 전체이름(full_name)을 조회하세요.

성(first_name)과 이름(last_name)을 합쳐서 결과 예시와 같이 조회합니다.

이름(last_name)은 모두 대문자로 표시합니다.

SELECT CONCAT(first_name, ' ', UPPER(last_name)) AS FULL_NAME
FROM `thelook_ecommerce.users`

SQL 연습문제 6-4

회원(users) 테이블에서 회원아이디(id), 이메일(email), 가입연도(signup_year)을 조회하세요.

SELECT id, email,EXTRACT(YEAR FROM created_at) as signup_year
FROM `thelook_ecommerce.users`

SQL 연습문제 6-5

회원(users) 테이블에서 2020년 7월 1일 부터 2020년 7월 10일까지 가입한 회원정보를 조회하세요.

가입일시는 created_at 입니다.

SELECT *
FROM `thelook_ecommerce.users`
where created_at between '2020-07-01' and '2020-07-10'

select *
from `thelook_ecommerce.users`
where created_at >= '2020-07-01' and created_at < '2020-07-11';

SQL 연습문제 6-6

주문정보(orders) 테이블에서 현재로부터 1년전에서 오늘까지 주문한 데이터를 조회하세요.

• 현재일시 : current_datetime()
• 주문일시 : created_at

SELECT *
FROM `thelook_ecommerce.orders`
where date(created_at) >= date_sub(current_datetime(), interval 365 day)

SQL 연습문제 6-7

회원(users) 테이블에서 가입연도(signup_year), 연도별 가입자 수(user_count)를 조회하세요.

SELECT extract(year from created_at) as signup_year, 
        count(id) as user_count
from `thelook_ecommerce.users`
group by signup_year

SQL 연습문제 6-8

가입회원들이 시간대별 가입자수를 확인하려고 합니다.

회원(users) 테이블에서 0~23으로 표시되는 시간대(hour), 시간대별 가입자 수(user_count)를 조회하세요.

정렬 순서는 시간대(hour)순입니다.

SELECT extract(hour from created_at) as hour, count(id) as user_count
FROM `thelook_ecommerce.users`
group by hour
order by hour asc

SQL 연습문제 6-9

특정 연도의 월별 주문건수

주문정보(orders) 테이블에서 2020년도의 월별 주문건수를 조회하세요.

조회항목은 연도, 월, 주문건수 입니다.

select extract(year from created_at) as year, 
      extract(month from created_at) as month,
      count(order_id) as order_count
from `thelook_ecommerce.orders`
where extract(year from created_at) = 2020
group by year, month
order by month

SQL 연습문제 6-10

회원(users) 테이블에서 다음 내역을 조회하세요.

• id
• 이름(first_name)
• 이름의 길이(name_length)
• 이름(first_name)의 앞 3글자(part_name)
• 이름의 앞 3글자를 별표 처리한 이름(name_with_asterisk)

select id,
      first_name,
      length(first_name) as name_length,
      left(first_name, 3) as part_name,
      replace(first_name, left(first_name, 3), '***') as name_with_asterisk

from `thelook_ecommerce.users`

SQL 연습문제 6-11

회원(users) 테이블에서 전체 유저의 가입연도별 데이터를 조회하려고 합니다.

가입연도(signup_year), 연도별 가입자수(user_count), 최고나이(max_age), 최저나이(min_age), 평균나이(avg_age)를 조회하세요.

평균나이는 소수점 둘째자리까지 표시해주세요. 이하 소수점은 반올림처리하여 표시해주세요.

정렬 순서는 가입연도 순(signup_year)입니다.

select extract(year FROM created_at) as signup_year,
      count(id) as user_count,
      max(age) as max_age,
      min(age) as min_age,
      round(avg(age), 2) as avg_age
from `thelook_ecommerce.users`

group by signup_year
order by signup_year

SQL 연습문제 6-12

회원(users) 테이블에서 브라질 여성 유저의 시간대별 유저 가입자수를 조회하세요.

조회 항목은 다음과 같습니다.

• 시간대(hour)
• 가입자수(user_count)

select extract(hour from created_at) as hour, 
      count(id) as user_count
from `thelook_ecommerce.users`
where country = 'Brasil' and 
      gender = 'F'
group by hour
order by hour

where 쓰는 것을 까먹었음 !! 조건 제대로 보세요

 

SQL 연습문제 6-13

회원(users) 테이블에서 남성유저의 가입연도별 국가별 데이터를 조회하려고 합니다.

조회 항목은 다음과 같습니다.

• 가입 연도(signup_year)
• 국가명(country)
• 가입자수(user_count)
• 최고나이(max_age)
• 최저나이(min_age)
• 평균나이(avg_age)

정렬순서는 가입연도 내림차순, 가입자수 내림차순 입니다.

그룹핑 결과에서 가입자수가 100명 이상인 데이터만 표시해주세요.

select extract(year from created_at) as signup_year,
       country,
       count(id) as user_count,
       max(age) as max_age,
       min(age) as min_age,
       avg(age) as avg_age
from `thelook_ecommerce.users`
where gender = 'M'
group by signup_year, country
having user_count >= 100
order by signup_year desc, user_count asc

having 에 적어야 할 것을 where 에 적어서 계속 안 됐었음! 

 

SQL 연습문제 6-14

다음은 회원(users) 테이블에서의 이메일의 아이디부분만 조회하는 쿼리입니다.

select left(email,INSTR(email,'@')-1)
from `thelook_ecommerce.users`;

위 쿼리를 수정하여 이메일의 아이디부분의 4번째글자부터 뒤의 글자를 별표 5개로 변경처리하여 조회하세요.

select replace(email, substr(left(email,INSTR(email,'@')-1), 4), '*****')
from `thelook_ecommerce.users`;

 

프로그래머스 SQL 문제 6

프로그래머스 문제 6-1 (level 1)

PATIENT 테이블에서 12세 이하인 여자환자의 환자이름, 환자번호, 성별코드, 나이, 전화번호를 조회하는 SQL문을 작성해주세요. 이때 전화번호가 없는 경우, 'NONE'으로 출력시켜 주시고 결과는 나이를 기준으로 내림차순 정렬하고, 나이 같다면 환자이름을 기준으로 오름차순 정렬해주세요.

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, 'NONE') AS TLNO
FROM PATIENT
WHERE AGE <= 12 AND GEND_CD = 'W'
ORDER BY AGE DESC, PT_NAME ASC

IFNULL은 해당 컬럼에 NULL값이 있는 경우 다른 값으로 채워넣을 수 있음

IFNULL( , 채워 넣을 값)

프로그래머스 문제 6-2 (level 1)

FOOD_WAREHOUSE 테이블에서 경기도에 위치한 창고의 ID, 이름, 주소, 냉동시설 여부를 조회하는 SQL문을 작성해주세요. 이때 냉동시설 여부가 NULL인 경우, 'N'으로 출력시켜 주시고 결과는 창고 ID를 기준으로 오름차순 정렬해주세요.

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, 
ADDRESS, IFNULL(FREEZER_YN, 'N') AS FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '%경기도%'
ORDER BY WAREHOUSE_ID ASC

WHERE ADDRESS LIKE '%경기도%' 

프로그래머스 문제 6-3 (level 1)

FOOD_FACTORY 테이블에서 강원도에 위치한 식품공장의 공장 ID, 공장 이름, 주소를 조회하는 SQL문을 작성해주세요. 이때 결과는 공장 ID를 기준으로 오름차순 정렬해주세요.

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY 
WHERE ADDRESS LIKE '%강원도%'
ORDER BY FACTORY_ID ASC

프로그래머스 문제 6-4 (level 1)

BOOK 테이블에서 2021년에 출판된 '인문' 카테고리에 속하는 도서 리스트를 찾아서 도서 ID(BOOK_ID), 출판일 (PUBLISHED_DATE)을 출력하는 SQL문을 작성해주세요.
결과는 출판일을 기준으로 오름차순 정렬해주세요.

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d')
FROM BOOK
WHERE CATEGORY = '인문' AND YEAR(PUBLISHED_DATE) = 2021
ORDER BY PUBLISHED_DATE ASC

프로그래머스 문제 6-5 (level 1)

DOCTOR 테이블에서 진료과가 흉부외과(CS)이거나 일반외과(GS)인 의사의 이름, 의사ID, 진료과, 고용일자를 조회하는 SQL문을 작성해주세요. 이때 결과는 고용일자를 기준으로 내림차순 정렬하고, 고용일자가 같다면 이름을 기준으로 오름차순 정렬해주세요.

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') as HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD='CS' or MCDP_CD='GS'
ORDER BY HIRE_YMD DESC, DR_NAME ASC

DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') as HIRE_YMD

프로그래머스 문제 6-6 (level 1)

USER_INFO 테이블에서 2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원이 몇 명인지 출력하는 SQL문을 작성해주세요.

SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE AGE BETWEEN 20 AND 29 AND YEAR(JOINED) ='2021'

SELECT 에서 YEAR 가져올 때는 EXTRACT 사용하지만 WHERE에서 YEAR 가져올 때는 그냥 YEAR로 묶어주면 됨

-> 틀림) MY SQL에서만 YEAR( ) 되고 빅쿼리나 오라클에서는 사용 불가!  EXTRACT 사용해야 함

프로그래머스 문제 6-7 (level 1)

가장 최근에 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.

SELECT MAX(DATETIME) AS '시간'
FROM ANIMAL_INS

최근 = MAX

늦게 = MIN

프로그래머스 문제 6-8 (level 2)

입양 게시판에 동물 정보를 게시하려 합니다. 동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 "No name"으로 표시해 주세요.

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name'), SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

프로그래머스 문제 6-9 (level 2)

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

SELECT EXTRACT(hour FROM DATETIME) as HOUR, COUNT(ANIMAL_ID)

FROM ANIMAL_OUTS
WHERE EXTRACT(hour FROM DATETIME) BETWEEN 9 AND 19
group by HOUR
ORDER BY HOUR ASC

EXTRACT 할 때 (HOUR FROM~)임 중간에 , 안 들어감!

 

프로그래머스 문제 6-10 (level 2)

ANIMAL_INS 테이블에 등록된 모든 레코드에 대해, 각 동물의 아이디와 이름, 들어온 날짜1를 조회하는 SQL문을 작성해주세요. 이때 결과는 아이디 순으로 조회

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d')
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

프로그래머스 문제 6-11 (level 2)

MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬해주세요.

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH,'%Y-%m-%d') as DATE_OF_BIRTH
from MEMBER_PROFILE
where month(DATE_OF_BIRTH) = 03  AND GENDER = 'W' AND NOT ISNULL(TLNO)
order by MEMBER_ID

이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고 -> NOT ISNULL 쓰던가 !ISNULL 사용!

프로그래머스 문제 6-12 (level 2)

APPOINTMENT 테이블에서 2022년 5월에 예약한 환자 수를 진료과코드 별로 조회하는 SQL문을 작성해주세요. 이때, 컬럼명은 '진료과 코드', '5월예약건수'로 지정해주시고 결과는 진료과별 예약한 환자 수를 기준으로 오름차순 정렬하고, 예약한 환자 수가 같다면 진료과 코드를 기준으로 오름차순 정렬해주세요.

SELECT MCDP_CD AS '진료과 코드', COUNT(PT_NO) AS '5월예약건수'
FROM APPOINTMENT
WHERE YEAR(APNT_YMD) = '2022' AND MONTH(APNT_YMD) = '05'
GROUP BY MCDP_CD
ORDER BY COUNT(PT_NO), MCDP_CD

 문제 제대로 읽기~