[25.02.19]본캠프 3일차 코드카타+DBeaver연결 및 오류 해결+라이브세션(SQL,직무)

오늘 하루는 코드카타 풀면서 시작 !

코드카타

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

SELECT dr_name,dr_id, mcdp_cd
, date_format(hire_ymd,'20%y-%m-%d') hire_ymd
from doctor
where mcdp_cd='cs' or mcdp_cd='gs'
order by hire_ymd desc, dr_name

어제 쓴 date_format함수와 조금 달랐다.
요리조리 시도 해보다가 %y 앞에 20을 붙여줬다. ⭐

20. FOOD_PRODUCT 테이블에서 가격이 제일 비싼 식품의 식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격을 조회하는 SQL문을 작성해주세요.

SELECT product_id
    , product_name
    , product_cd
    , category
    , price
from food_product 
where price =( 
            select max(price)
            from food_product f2
            where price=f2.price)

21. 동물 보호소에 들어온 동물 중, 이름이 없는 채로 들어온 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.

SELECT animal_id
from animal_ins
where name is null
order by animal_id

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

SELECT count(user_id)
from user_info
where age between 20 and 29
and year(joined) ='2021'

Year함수를 이용해 2021만 추출해 필터링 하는 조건 적는 걸 구글링 도움을 받았다 !

🔴23.
보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.

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

❌오답
🔻정답으로 제출한 코드

SELECT animal_id, name
, case when sex_upon_intake like 'neutered%' or  
            sex_upon_intake like 'spayed%' then 'O'
        else 'X' 
        end 중성화
from animal_ins
order by animal_id

💡1. like ''에 % 넣어주기
💡2. 컬럼 다시 언급해주기 ( or만으로 병렬이 안되는 듯 하다)
.
슬랙 질문방에 나와같은 질문이 올라와있어서 답변을 참고했다.
정규식 함수를 사용해 아래와 같이 작성할 수도 있다고한다.
^은 문자 시작, ㅣ 는 or 의 의미

SELECT animal_id, name,
       CASE WHEN sex_upon_intake REGEXP '^(Neutered|Spayed)' THEN 'O'
            ELSE 'X' 
       END AS 중성화
FROM animal_ins
ORDER BY animal_id;

출처ㅣ참고링크

24. PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.

select s.category
, count(category) products 
from( 
        select substr(product_code,1,2) category
        from product ) s
group by category    
order by category

서브쿼리로 카테고리열에 필요한 코드 2글자를 추출하고, 본쿼리에서 개수를 count하고 그룹화/정렬 해주었다.

25. 동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.

SELECT animal_type, count(animal_type) count
from animal_ins
group by animal_type
order by count

고양이를 먼저 조회할 수 있는 쿼리식이 무엇인지 모르겠어서 대충 count개수로 정답을 맞히긴 했으나, 질문을 해보니 문제가 살짝 모호했던 것 같다. 고양이는 C, 강아지는 D로 시작하기 때문에 order by animal_type로 정렬해주면 된다.

오늘 Lv2끝내고싶어서 오후 개인 자습시간에 문제를 더 풀었다.
26.
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

select s.hour, count(s.hour)
from (select hour(datetime) hour
     from animal_outs) s
where hour between 9 and 19
group by s.hour
order by s.hour

각 시간대별로 나누는 서브쿼리를 먼저 작성하고 count 해줬다.
서브쿼리 이름 지정 잊지말기 ~!

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

SELECT MCDP_CD '진료과 코드', count(APNT_NO) '5월예약건수'
from appointment
where year(APNT_YMD)='2022'
and month(APNT_YMD)='05'
group by MCDP_CD
order by count(APNT_NO) ,MCDP_CD

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

SELECT PT_NAME, PT_NO, GEND_CD, AGE, ifnull(tlno, 'NONE')
from patient
where age<=12
and gend_cd='W'
order by age desc, pt_name

처음에 'none'으로 써서 계속 오류가 떴었는데, 문제에서 지정한 문자는 그대로 작성하기 !!

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

SELECT flavor
from first_half
order by total_order desc, shipment_id

🔴30.
CAR_RENTAL_COMPANY_CAR 테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요. 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요.

SELECT s.car_type, count(s.car_id) CARS
from ( 
        select car_id, car_type
        from CAR_RENTAL_COMPANY_CAR
        where options in ('통풍시트', '열선시트', '가죽시트') )  s
group by s.car_type
order by s.car_type

처음에 이렇게 쓰고 실행이 안돼서 엄청 답답해하고 있었는데..

in ('통풍시트', '열선시트', '가죽시트') 라고 쓰면 저 3개 중 하나가 options 컬럼의 값에 정확히 일치할때만 작동한다. (앞뒤로 여러 개 못붙음)
하지만 이 컬럼의 대부분은 '통풍시트, 가죽시트'같은 복합적인 형태로 이루어져있다.
=통풍시트, 열선시트, 가죽시트는 각 OPTIONS 컬럼에서 위치에 관계없이 존재할 수 있음.
EX.

• 열선시트,후방카메라
• 후방카메라,주차감지센서,열선시트

따라서 포함하는 것을 찾기 위해서는 like를 사용하고 or로 묶어줘야한다.
(like, '%문자%', 'or'로 연결! )
.
🔻수정한 최종 쿼리

SELECT car_type, count(car_id) CARS
from CAR_RENTAL_COMPANY_CAR
where options like '%통풍시트%'
or options like '%열선시트%'
or options like '%가죽시트%' 
group by car_type
order by car_type

.
.
.

SQL 세션

DATA, DB, QUERY, SQL 기본 개념

데이터

문자, 숫자, 이미지, 영상, 음성 형태의 "정보"

DataBase(DB)

저장된 데이터의 모음.
그리고 데이터가 수집되는 곳을 pc=서버 라고 한다.
여기에는 raw data 형태로 저장되어있다.
따라서 효율적으로 데이터를 관리해주는 DBMS가 필요하다.

DBMS

database management system 의 약자로, DB에 규칙성 및 정합성을 부여하며, 데이터 등록/정리/검색이 용이하도록 하는 Software system이다.
따라서, 특정 DB에 접근하기 위한 이정표 역할을 수행해주고, 부여된 규칙성으로 다수의 사용자가 동시접속 및 공유가 가능하게 해준다.
(나는 여러 DBMS중에서 MySQL을 사용하는 것이다)
정합성이란?
여러 시스템이나 데이터베이스 간의 데이터가 일관성 있게 유지되는 것을 의미

DBMS의 데이터 저장방식으로는 "관계형"이 가장 널리 사용된다.
✅ 관계형(RDBMS)
▪️행과 열로 이루어진 2차원의 구조
▪️ 계층형, 망형 구조가 발전된 형태
▪️데이터 구성 변경 가능
▪️데이터 복구 가능
▪️정규화를 통해 중복제거 및 이상치 제거 가능

Query

query는 DB에 정보를 요청하고 결과를 얻기 위해 사용되는 표준화된 언어이다.

---

.
.

. SQL의 작동순서 및 작성순서

다르다 !
☑️ 작동순서
FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY

☑️ 작성순서
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY

회사에서는 로그정의서 화면을 보고 업무를 하게된다고 한다 ~!
.
.

---

Dbeaver 연결

4번째 시도 만에 가능했다 ^^
프로퍼티 오류 수정
유저연동 에러
이 두개를 참고했다 !!(정말 친절하신 분들 ㅠㅠ💖)

오예 ㅠㅠㅠ 1월에도 계속해서 커넥트가 안돼서 워크벤치에서 테이블 생성해서 문제 풀고있었는데 ㅠ 초록색 두개가 날 너무 맘 편하게 한다...

그 이후는 다음과 같이 진행했다

Databases 에 커서 대고 우클릭 ▶ create new database ▶ basic 으로 이름 설정 ▶ Table에 커서 대고 데이터 가져오기 ▶ input files 에서 csv파일 불러오기 ▶ Data load Setting >source setting 에서 인코딩 부분을 euc-kr 로 변경(한글깨짐 방지) ▶ sql편집기로 이동

기본 세팅은 다음과 같다

select * 
from basic.users

(앞으로 쓸 csv파일이 담긴 구글드라이브는 북마크 해뒀다.)

---

느낀점&내일계획

오늘 직무세션이 굉장히 유익했다. 내용을 적고싶었지만 ..! 혹시나 튜터님들 개인 퐅폴이 노출되면 안될 수도 있으니,, 따로 기록해두었다.
벌써 수요일이 지났다! 코드타카도 30개 풀었다. 좀 많이 익숙해진 것 같다.
오늘 DBeaver에 새 데이터베이스도 만들어서 연결했다. 앞으로 할 작업들이 기대가 된다 ㅎ
연결 과정을 잘 기록해둬서 나중에 다른 기기로 연결할 때도 잘 참고할 수 있을 것 같다 !! 뿌듯해 !!🤩
내일 직무스터디 발표준비도 잘 마무리해서 금요일에 잘 마무리 하고싶다.

내일 계획
~10:00 알고리즘 코드타카
10:00 ~ 10:10 : 오전 데일리스크럼 작성
10:10 ~ 11:30 : 아티클 스터디
11:30 ~ 14:00 : 온라인 강의 학습 및 정비 시간
14:00 ~ 18:00 : 직무 스터디 회의 및 온라인 강의학습
18:00 ~ 20:20 : 온라인 강의 학습 및 정비 시간
20:20 ~ 21:00 : 오후 데일리스크럼 및 TIL 작성