[PROGRAMMERS] SQL 고득점 Kit - SELECT (MySQL, Oracle)

✏️ [Lv. 1] 조건에 맞는 도서 리스트 출력하기

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

👾 구현

-- MySQL
SELECT book_id, date_format(published_date, '%Y-%m-%d') as published_date
from book
where category = '인문' and published_date like '2021%'
order by published_date;

--Oracle
SELECT book_id, to_char(published_date, 'yyyy-mm-dd') FROM book
WHERE to_char(published_date, 'yyyy') LIKE '2021' AND category = '인문'
ORDER BY published_date;

🤯 풀이

oracle 날짜 비교 to_char 해놓고 하기

---

✏️ [Lv. 1] 흉부외과 또는 일반외과 의사 목록 출력하기  

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

👾 구현

-- MySQL
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 asc

--Oracle
SELECT dr_name, dr_id, mcdp_cd, to_char(hire_ymd, 'yyyy-mm-dd')
FROM doctor
WHERE mcdp_cd IN ('CS', 'GS')
ORDER BY hire_ymd DESC, dr_name;

🤯 풀이

리스트는 () 안에, 오라클의 dateformat은 to_char() 이용할 것

---

✏️ [Lv. 1] 과일로 만든 아이스크림 고르기 

상반기 아이스크림 총주문량이 3,000보다 높으면서 아이스크림의 주 성분이 과일인 아이스크림의 맛을 총주문량이 큰 순서대로 조회하는 SQL 문을 작성해주세요.

👾 구현

-- MySQL
SELECT first_half.flavor
FROM first_half 
JOIN icecream_info ON first_half.flavor = icecream_info.flavor
WHERE total_order > 3000 AND ingredient_type = "fruit_based"
ORDER BY total_order DESC;

-- Oracle
SELECT first_half.flavor
FROM first_half 
JOIN icecream_info ON first_half.flavor = icecream_info.flavor
WHERE total_order > 3000 AND ingredient_type = 'fruit_based'
ORDER BY total_order DESC;

🤯 풀이

오라클에서 큰따옴표 쓰지 말 것 !!!

---

✏️ [Lv. 1] 과일로 만든 아이스크림 고르기 

상반기 아이스크림 총주문량이 3,000보다 높으면서 아이스크림의 주 성분이 과일인 아이스크림의 맛을 총주문량이 큰 순서대로 조회하는 SQL 문을 작성해주세요.

👾 구현

-- MySQL
SELECT first_half.flavor
FROM first_half 
JOIN icecream_info ON first_half.flavor = icecream_info.flavor
WHERE total_order > 3000 AND ingredient_type = "fruit_based"
ORDER BY total_order DESC;

-- Oracle
SELECT first_half.flavor
FROM first_half 
JOIN icecream_info ON first_half.flavor = icecream_info.flavor
WHERE total_order > 3000 AND ingredient_type = 'fruit_based'
ORDER BY total_order DESC;

🤯 풀이

오라클에서 큰따옴표 쓰지 말 것 !!!

---

✏️ [Lv. 1] 12세 이하인 여자 환자 목록 출력하기 

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

👾 구현

-- MySQL
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;

-- Oracle
SELECT pt_name, pt_no, gend_cd, age, NVL(tlno, 'NONE') as tlno
FROM patient
WHERE gend_cd = 'W' AND age <= 12
ORDER BY age desc, pt_name;

---

✏️ [Lv. 1] 평균 일일 대여 요금 구하기

CAR_RENTAL_COMPANY_CAR 테이블에서 자동차 종류가 'SUV'인 자동차들의 평균 일일 대여 요금을 출력하는 SQL문을 작성해주세요. 이때 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림하고, 컬럼명은 
AVERAGE_FEE 로 지정해주세요.

👾 구현

-- MySQL
SELECT ROUND(AVG(daily_fee)) AS AVERAGE_FEE
FROM car_rental_company_car
WHERE car_type = 'SUV';

-- Oracle
SELECT ROUND(AVG(daily_fee)) AS AVERAGE_FEE
FROM car_rental_company_car
WHERE car_type = 'SUV';

---

✏️ [Lv. 2] 3월에 태어난 여성 회원 목록 출력하기 

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

👾 구현

-- MySQL
SELECT member_id, member_name, gender, date_format(date_of_birth, '%Y-%m-%d')
FROM member_profile
WHERE date_of_birth LIKE '%-03-%' AND tlno is not null AND gender = 'W'
ORDER BY member_id asc

--Oracle
SELECT member_id, member_name, gender, TO_CHAR(date_of_birth, 'YYYY-MM-DD') as DATE_OF_BIRTH
FROM member_profile
WHERE gender = 'W' AND EXTRACT(month from date_of_birth) = 3 AND NOT tlno IS NULL
ORDER BY member_id;

 

🤯 풀이

오라클은 to_char / not '' is null 

조건이 많으면 열심히 보자..

---

✏️ [Lv. 1] 인기있는 아이스크림 

상반기에 판매된 아이스크림의 맛을 총주문량을 기준으로 내림차순 정렬하고 총주문량이 같다면 출하 번호를 기준으로 오름차순 정렬하여 조회하는 SQL 문을 작성해주세요.

👾 구현

-- MySQL
select flavor
from first_half
order by total_order desc, shipment_id;

-- Oracle
SELECT flavor
FROM first_half
ORDER BY total_order desc, shipment_id;

---

✏️ [Lv. 1] 조건에 부합하는 중고거래 댓글 조회하기 

USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 2022년 10월에 작성된 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 조회하는 SQL문을 작성해주세요. 결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고, 댓글 작성일이 같다면 게시글 제목을 기준으로 오름차순 정렬해주세요.
MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬해주세요.

👾 구현

-- MySQL
SELECT title, a.board_id, reply_id, b.writer_id, b.contents, 
    date_format(b.created_date, '%Y-%m-%d') AS created_date
FROM used_goods_board AS a JOIN used_goods_reply AS b
ON a.board_id = b.board_id
WHERE a.created_date LIKE "2022-10%"
ORDER by b.created_date, title;

-- Oracle
SELECT b.title, b.board_id, r.reply_id, r.writer_id, r.contents, TO_CHAR(r.created_date, 'YYYY-MM-DD') AS CREATED_DATE
FROM used_goods_board b
    INNER JOIN used_goods_reply r
    ON b.board_id = r.board_id
WHERE TO_CHAR(b.created_date, 'YYYY-MM') = '2022-10'
ORDER BY r.created_date, b.title;

 

🤯 풀이

날짜 조건은 게시글 작성일

---

✏️ [Lv. 1] 모든 레코드 조회하기 

동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID순으로 조회하는 SQL문을 작성해주세요

👾 구현

-- MySQL
SELECT *
FROM animal_ins
ORDER BY animal_id;

-- Oracle
SELECT * FROM animal_ins
ORDER BY animal_id;

 

---

✏️ [Lv. 1] 동물의 아이디와 이름 

동물 보호소에 들어온 모든 동물의 아이디와 이름을 ANIMAL_ID순으로 조회하는 SQL문을 작성해주세요. 

👾 구현

-- MySQL
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS ORDER BY ANIMAL_ID

-- Oracle
SELECT animal_id, name 
FROM animal_ins
ORDER BY animal_id;

---

✏️ [Lv. 1] 조건에 맞는 회원수 구하기 

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

👾 구현

-- MySQL
SELECT COUNT(*) AS USERS
FROM user_info
WHERE age between 20 and 29 AND date_format(joined, '%Y') = '2021';

-- 가능
WHERE 20 <= age AND age <= 29 AND date_format(joined, '%Y') = '2021';

-- 불가능
WHERE 20 <= age <= 29 AND date_format(joined, '%Y') = '2021';

-- Oracle
SELECT count(*) AS USERS
FROM user_info
WHERE to_char(joined, 'YYYY-MM-DD') like '2021%'
AND age BETWEEN 20 and 29;

 

🤯 풀이

MySQL의 date_format은 '%Y'

오라클은 'YYYY'

---

✏️ [Lv. 1] 상위 N개 레코드 

동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하는 SQL 문을 작성해주세요.

👾 구현

-- MySQL
SELECT NAME FROM ANIMAL_INS WHERE DATETIME = (SELECT MIN(DATETIME) FROM ANIMAL_INS)

-- Oracle
SELECT NAME
FROM (SELECT * FROM ANIMAL_INS ORDER BY DATETIME)
WHERE ROWNUM = 1;

 

🤯 풀이

rownum은 1부터 시작

---

✏️ [Lv. 1] 여러 기준으로 정렬하기

동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하는 SQL 문을 작성해주세요.

👾 구현

-- MySQL
SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC;

-- Oracle
SELECT animal_id, name, datetime
FROM animal_ins
ORDER BY name, datetime desc;

---

✏️ [Lv. 1] 역순 정렬하기 

동물 보호소에 들어온 모든 동물의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 ANIMAL_ID 역순으로 보여주세요. 

👾 구현

-- MySQL
SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC;

-- Oracle
SELECT name, datetime
FROM animal_ins
ORDER BY animal_id desc;

 

---

✏️ [Lv. 1] 강원도에 위치한 생산공장 목록 출력하기 

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

👾 구현

-- MySQL
SELECT factory_id, factory_name, address
FROM food_factory
WHERE address like '%강원도%'
ORDER BY factory_id

-- Oracle
SELECT factory_id, factory_name, address
FROM food_factory
WHERE address LIKE '강원도%'
ORDER BY factory_id;

---

✏️ [Lv. 1] 아픈 동물 찾기 

동물 보호소에 들어온 동물 중 아픈 동물의 아이디와 이름을 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요.

👾 구현

-- MySQL
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'SICK'
ORDER BY ANIMAL_ID;

-- Oracle
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'Sick'
ORDER BY ANIMAL_ID;

🤯 풀이

오라클 대소문자 구분할것!!

MySQL은 구분 없이 검색 가능

---

✏️ [Lv. 1] 어린 동물 찾기 

동물 보호소에 들어온 동물 중 젊은 동물의 아이디와 이름을 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요.

👾 구현

-- MySQL
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
ORDER BY ANIMAL_ID;

-- Oracle
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
ORDER BY ANIMAL_ID;

---