[사전캠프 13일차] SQLD 공부, SQL 과제

오늘 한 일은,

• SQL 공부
  • [SQLD 자격증 챌린지] 6주차, 7주차, 8주차 수강
  • 개인 퀘스트(달리기반) - [SQL 실전!] Lv5.

 

---

 

SQLD 공부: [SQLD 자격증 챌린지] 수강하기

Oracle

SQLD 시험을 준비하기 위해선 Oracle에서 SQL을 작성해봐야 한다.

굳이 프로그램을 깔거나(심지어 유료라고) 할 필요 없이 무료 웹 기반 플랫폼에 가입해서 SQL 실습을 진행하면 된다.

 

오라클 가입하기 : 

Live SQL

 

오라클에 가입하고서 SQL Worksheet에서 직접 실습할 수 있다.

그런데 이렇게 내가 작업한 내용들을 저장해주진 않아서 로그아웃 했다가 다시 들어가보면 이전에 작업한 내용들이 사라져 있다.

(이런 내용을 스크립트로 남길 수 있는데 추후 실습하면서 알려주실 거라고 함)

 

SQL 실습 들어가기 전 기본 개념 점검

관계형 데이터베이스(Relational Database)

• 관계형 데이터베이스 : 표 형태로 저장되는 데이터베이스
• 관계형 데이터베이스는 데이터의 생성, 수정, 삭제, 그리고 조회가 가능함
  • 생성, 수정, 삭제, 조회를 줄여서 CRUD라고 함
• RDBMS : 관계형 데이터베이스 구조에 기반한 관계형 데이터베이스 관리 시스템

SQL(Structured Query Language)

• SQL : 관계형 데이터베이스에 CRUD하기 위해 고안된 언어
• SQL 언어 분류
  • 데이터 정의 언어(Data Definition Language, DDL) 
    • 관계형 데이터베이스의 구조를 정의하는 언어
    • 데이터를 저장하기 위한 데이터베이스의 기본 구조를 잡는 데 사용
    • 테이블을 생성(CREATE), 변경(ALTER, RENAME), 삭제(DROP)함
  • 데이터 조작 언어(Data Manipulation Language, DML)
    • 테이블에 저장된 데이터를 조작하는 언어
    • 데이터를 삽입(INSERT), 조회(SELECT), 수정(UPDATE), 삭제(DELETE)함
  • 데이터 제어 언어(Data Control Language, DCL)
    • 특정한 사용자의 데이터베이스 사용 권한을 부여하거나 박탈하는 데 사용
  • 트랜잭션 제어어(Transaction Control Language, TCL)
    • COMMIT, ROLLBACK 등의 명령어를 수행
    • *트랙잭션의 특징: ACID (①원자성(Atomicity), ②일관성(Consistency), ③독립성(Isolation), ④지속성(Durability))
• SQL Syntax
  • 하나의 statement는 세미콜론(;)으로 마침
  • 문(Statement)과 절(Clause)
    • 문 : 독립적으로 실행할 수 있는 완전한 SQL 코드 조각. 문은 절로 구성됨.
    • 절 : 문의 하위 단위

테이블

• 관계형 데이터베이스는 데이터베이스의 기본 단위인 테이블 형태로 데이터를 저장
• 컬럼 = 열 = 속성
• 로우 = 행 = 레코드 = 인스턴스
• 기본키(Primary Key) : 데이터를 유일하게 구분하는 식별자
• 외부키(Foreign Key) : 다른 테이블의 기본키로 활용되고 있는 열과의 관계를 연결하는 컬럼

ERD

• ERD : 테이블 간 상관 관계를 도식화한 것
• 대표적으로 I/E표기법과 바커 표기법이 있음

---

SELECT문 

• SELECT : 테이블에서 데이터를 조회할 때 사용
• 작성 순서: SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY
• Alias(별명) : 컬럼 명을 보기 좋게 나타나도록 붙여주는 다른 명칭
  • 오라클에선 작은따옴표('')를 사용하면 에러가 발생함. 큰따옴표("")를 사용할 것!
• DISTINCT : 중복값을 제외한 데이터들만 조회
  • DISTINCT와 컬럼 여러 개를 적으면 지정한 컬럼들의 값의 조합이 고유한 경우에만 조회됨
    (김 지아, 김 철수)

산술연산자, 합성연산자

• 산술연산자
  • 사칙연산 : +, -, *, /
  • 나머지 : MOD()
  • FROM dual : 단순 사칙연산을 하고 싶을 때 SELECT문을 실행하기 위해 FROM절을 채워주고자 쓰는 것
• 합성연산자
  • 합성연산자 : 문자열을 하나로 합칠 때 사용
  • 오라클은 || 로 합침
  • CONCAT(문자형1, 문자형2) 

단일행 함수

문자형 함수

• LOWER() : 모든 문자를 소문자로 변경
• UPPER() : 모든 문자를 대문자로 변경
• CONCAT(문자열1, 문자열2) : 문자열1와 문자열2를 연결
• SUBSTR(문자열, m, n) :문자열을 m번째 문자부터 n개 추출 (n 생략 시 끝까지 추출)
•  LENGTH() : 문자열의 개수를 숫자값으로
• LTRIM(문자열, 지정문자), RTRIM(문자열, 지정문자)
  : 문자열의 왼쪽(L)/오른쪽(R) 첫 번째 문자부터 확인해 지정문자가 나타나면 해당 문자를 제거함
  • 지정문자 생략 시 공백을 제거함
  • 지정문자가 중간에 끼어있을 경우는 당연히 제거하지 않음
• TRIM(leading|trailing|both 지정문자 FROM 문자열)
  : 문자열에서 옵션에 따라 지정문자를 제거
  • 생략, both : 양쪽
  • leading : LTRIM과 동일
  • trailing : RTRIM과 동일

숫자형 함수

• ABS() : 절댓값
• MOD(m, n) : m을 n으로 나눈 나머지 값을 계산
• CEIL() : 올림
• FLOOR() : 내림
• ROUND(m, n) : m을 소수점 n자리에서 반올림 

날짜형 함수

• DATE_FORMAT(date, format) : date(날짜형 데이터)를 format의 문자열로 변환
  • %Y : 4자리 연도
  • %y : 2자리 연도
  • %m : 2자리 월
  • %d : 2자리 일
  • %H: 2자리 시간
• SYSDATE : 현재 날짜와 시간을 출력
• EXTRACT(시간|날짜단위 FROM 시간|날짜) : 날짜 데이터에서 년/월/일/시간/분/초 데이터를 출력
• TO_NUMBER(TO_CHAR(날짜데이터, date_format)) : 날짜 데이터에서 년/월/일 데이터를 출력
  • TO_NUMBER()를 제거하면 문자형 데이터로 출력됨

NULL 관련 함수

• NVL(판단 대상, 대체값) : 판단 대상에서 NULL값이 있을 경우 대체값으로 넣어라
• NULLIF(판단 대상, 비교할 값) : 판단 대상이 비교할 값과 일치할 경우 NULL값으로 대체

CASE문

• CASE 컬럼명 WHEN 조건 1 THEN 결과1 … ELSE 결과값 END
• DECODE(컬럼명, 기준1, 값1, 기준2, 값2, …)

250211 오라클에서 DECODE() 실습을 해 본 모습

 

---

 

WHERE

WHERE

• WHERE절은 단독으로 사용하지 않고, 조회, 수정, 삭제 명령어와 함께 사용
• FROM절 다음에 WHERE절을 작성

비교 연산자

• = : 같다
• > : 크다
• >= : 크거나 같다
• < : 작다
• <= : 작거나 같다
• <> : 같지 않다

SQL 연산자

• BETWEEN 값1 AND 값2 : 값1 이상 값2 이하
• IN (값1, 값2, …) : 괄호 안 값들 중 하나라도 일치하면 됨
• LIKE '비교 문자열' : 비교 문자열과 형태가 일치하면 됨
  • % : 어떤 문자를 포함한 모든 것을 조회 ('%토마토%' → '토마토'가 포함되면 전부 OK)
  • _ : 1개의 단일문자 ('_토마토_' → 중간에 '토마토'가 들어가는 총 5자리 문자열이면 OK)
• IS NULL : 비교 칼럼의 값이 NULL이면 됨
• IS NOT NULL : 비교 칼럼의 값이 NULL이 아니면 됨

논리 연산자

• AND : 앞에 오는 조건과 뒤에 오는 조건을 만족해야 참
• OR : 앞에 오는 조건과 뒤에 오는 조건, 둘 중 하나만 만족해도 참
• NOT : 뒤에 오는 조건의 결과를 반대로 만듦
• *여러 개의 논리 연산자 사용 시, 괄호 사용하여 우선순위를 명시적으로 표시할 것을 권장함

연산자의 우선순위

• ①괄호 > ②NOT 연산자 > ③비교 연산자, SQL 연산자 > ④ AND > ⑤OR

GROUP BY와 HAVING

집계 함수

• COUNT(*) : NULL값을 포함한 행의 수를 출력
• COUNT(표현식) : NULL값 제외한 행의 수를 출력
• SUM(표현식) : NULL값 제외한 합계 출력
• AVG(표현식) : NULL값 제외한 평균을 출력
• MAX(표현식) : NULL값 제외한 최댓값을 출력
• MIN(표현식) : NULL값 제외한 최솟값을 출력

GROUP BY

HAVING

• GROUP BY절로 그룹 지은 상태에서 원하는 조건으로 필터링
  • 이와 달리 WHERE절은 일반 조건이라고 이해하면 됨

ORDER BY

ORDER BY

• ORDER BY : 특정 칼럼을 기준으로 정렬하여 출력하기 위해 사용
• 기본적으로 정렬은 오름차순으로 정렬, 내림차순으로 정렬은 DESC를 추가

SELECT문 실행 순서

5. SELECT 칼럼명 [ALIAS명]         -- 5. 데이터의 값을 출력/계산
1. FROM 테이블명                   -- 1. 발췌대상 테이블 참조
2. WHERE 조건식                   -- 2. 발췌 대상 데이터가 아닌 것은 제거
3. GROUP BY 칼럼(Column)이나 표현식 -- 3. 행동들을 소그룹화
4. HAVING 그룹조건식               -- 4. 그룹핑된 값의 조건에 맞는 것만을 출력
6. ORDER BY 칼럼(Column)이나 표현식;-- 6. 데이터를 정렬

 

---

 

SQL 과제: 개인 퀘스트(달리기반) - [SQL 실전!]

Lv5. 가장 높은 월급을 받는 직원은?

Employees 테이블:

EmployeeID	Name	Department	Salary	ManagerID
1	Alice	HR	70000	NULL
2	Bob	IT	90000	1
3	Charlie	IT	80000	2
4	David	IT	85000	2
5	Eve	HR	75000	1
6	Frank	Finance	95000	NULL
7	Grace	Finance	80000	6
8	Heidi	IT	95000	2

250211 오늘은 오라클에 직접 테이블을 생성해서 했다..! 지난번에 과제할 때 정말 답답해서 참을 수가 없어서 직접 일일이 치는 노가다를..

Q1. 각 직원의 이름, 부서, 월급, 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 직원의 이름과 월급을 조회하기

 

내가 시도한 방법은,

① 부서별 가장 높은 월급을 받고 있는 직원들만 나오는 결과 테이블을 하나 만들고

②그 테이블과 기존의 Employees 테이블을 JOIN으로 연결시켜서

③두 테이블을 가지고 결과테이블과 같이 조회되도록 한다

 

우선 ①을 진행한다.

SELECT Name,
       Department,
       Salary,
       Ranking
FROM
(
	SELECT Name,
      	     Department,
     	     Salary,
     	     RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) Ranking
	FROM Employees
)a
WHERE Ranking = 1

우선 RANK() OVER 함수를 통해 순위를 계산하는 쿼리를 작성했다.

그리고 1위 데이터만 남기기 위해 처음에는 서브쿼리로 묶지 않고 바로 WHERE절을 작성했는데 무슨 이유에서인지 에러가 떴고, 혹시나 해서 서브쿼리로 묶어서 다시 SELECT문을 작성하며 거기에 WHERE절을 추가하니 원하던 대로 결과를 출력했다.

 

 

그리고 위의 테이블과 원래의 Employees 테이블을 JOIN한다.

SELECT
FROM Employees e LEFT JOIN
    (
    SELECT Name,
       Department,
       Salary,
       Ranking
	FROM
	(
    	SELECT Name,
          	   Department,
          	   Salary,
           	   RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) Ranking
  	    FROM Employees
	)a
	WHERE Ranking = 1
) b ON e.Department = b.Department

기존의 Employees 테이블에다 LEFT JOIN으로 ①의 테이블을 합쳐주는데, 이때 Department 컬럼을 기준으로 한다.

그러면 위와 같이 우측의 ①번 테이블도 Department에 따라 그에 상응하는 데이터를 띄운다.

 

그리고 마지막으로 결과 테이블과 같은 컬럼들이 조회되도록 SELECT절을 작성한다. 별명도 적절히 붙인다.

결과테이블과 같이 이름 순으로 조회되도록 ORDER BY를 추가했다.

SELECT e.Name,
       e.Department,
       e.Salary,
       b.Name Top_Earner,
       b.Salary Top_Salary
FROM Employees e LEFT JOIN
    (
    SELECT Name,
       Department,
       Salary,
       Ranking
	FROM
	(
    	SELECT Name,
          	   Department,
          	   Salary,
           	   RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) Ranking
  	    FROM Employees
	)a
	WHERE Ranking = 1
) b ON e.Department = b.Department
ORDER BY e.Name

 

 

Q2. 부서별 평균 월급이 가장 높은 부서와 해당 부서의 평균 월급을 조회하기

SELECT Department,
       AVG(Salary) Avg_Salary
FROM Employees
GROUP BY Department

우선 AVG()와 GROUP BY를 사용해서 부서별로 평균을 구하도록 작성한다.

SELECT Department,
       AVG(Salary) Avg_Salary
FROM Employees
GROUP BY Department
ORDER BY Avg_Salary DESC

이를 다시 평균 월급 기준으로 내림차순 정렬을 한 번 시켜주고,

SELECT *
FROM
(
    SELECT Department,
           AVG(Salary) Avg_Salary
	FROM Employees
	GROUP BY Department
	ORDER BY Avg_Salary DESC 
) a
WHERE ROWNUM = 1

(아니 바로 LIMIT를 쓰려는데 오라클은 LIMIT를 못 쓴다고.. 오라클은 귀찮네)

[참고] [Oracle] 오라클 limit 사용하는 방법 (페이징, rownum)

ROWNUM을 사용해서 첫 번째 줄만 출력하도록 작성하면 원하는 결과가 나온다.

---

 

개인 퀘스트 레벨이 올라갈수록 과제하는 데 시간이 많이 걸린다. 

아 그것만 아니었어도 SQLD 강의를 하나 더 들을 수 있었는데, 아쉽구만… 그래도 확실히 이론 끝나고 실습 파트로 넘어오니까 강의 수강하는 속도가 달라졌다.

어쩌면 SQLD 강의는 사전캠프 기간 안에 다 들을 수도 있겠다. 아자아자 화이팅~!!