뭘 공부?
1. 데이터베이스의 이해
1-1. 데이터베이스란?
‘데이터의 집합’이다.
1-2. DBMS란?
데이터베이스를 ‘데이터의 집합’이라고 정의한다면, 이런 데이터베이스를 관리하고 운영하는 소프트웨어를 DBMS(Database Management System)
라고 합니다. 다양한 데이터가 저장되어 있는 데이터베이스는 여러 명의 사용자나 응용 프로그램과 공유하고 동시에 접근이 가능해야 합니다.
1-3. DBMS의 종류
DBMS와 같은 소프트웨어는 특정 목적을 처리하기 위한 프로그램이다. 데이터베이스를 사용하기 위해서도 소프트웨어, 즉 DBMS를 설치해야 하는데 대표적으로 MySQL, 오라클(Oracle), SQL 서버, MariaDB 등이 있다.
1-4. DBMS의 분류
DBMS의 유형은 계층형(Hierarchical), 망형(Network), 관계형(Relational), 객체지향형(Object-Oriented), 객체관계형(Object-Relational) 등으로 분류된다.
현재 사용되는 DBMS 중에는 관계형 DBMS(RDBMS)가 가장 많은 부분을 차지하며, MySQL도 관계형 DBMS에 포함된다.
A. 계층형 DBMS
처음으로 등장한 DBMS 개념으로 1960년대에 시작되었다. 계층형 DBMS의 문제는 처음 구성을 완료한 후에 이를 변경하기가 상당히 까다롭다는 것이다. 또한 다른 구성원을 찾아가는 것이 비효율적이다. 따라서 지금은 사용하지 않는 형태다.
계층형 DBMS의 예시
B. 망형 DBMS
계층형 DBMS의 문제점을 개선하기 위해 1970년대에 등장했다. 하위에 있는 구성원끼리도 연결된 유연한 구조를 가진다. 하지만 망형 DBMS를 잘 활용하려면 프로그래머가 모든 구조를 이해해야만 프로그램 작성이 가능하다는 단점이 존재한다. 역시 지금은 거의 사용하지 않는 형태입니다.
망형 DBMS 예시
C.관계형 DBMS
줄여서 RDBMS라고 부른다. MySQL뿐만 아니라, 대부분의 DBMS가 RDBMS 형태로 사용된다. RDBMS의 데이터베이스는 테이블(table)이라는 최소 단위로 구성되며, 이 테이블은 하나 이상의 열(column)과 행(row)으로 이루어져 있습니다.표의 모양이 바로 테이블이라고 생각하면 된다.
2. 관계형 데이터베이스의 이해
2-1. 관계형 데이터베이스(RDB)
현재 가장 많이 사용되고 있는 데이터베이스의 한 종류. 테이블로 이루어져 있음. 테이블은 key와 value의 관계를 나타낸다.
테이블은 이름을 가지고 있다. 위와 같은 테이블이 또 다른 테이블들과 연결되어 있는 형태가 관계형 데이터베이스라고 생각하면 된다.
2-2. 관계형 데이터베이스의 특징
•
데이터의 분류, 정렬, 탐색 속도가 빠르다.
•
오랫동안 사용된 만큼 신뢰성이 높고, 어떤 상황에서도 데이터의 무결성을 보장해준다.
•
기존에 작성된 스키마를 수정하기가 어렵다.
•
데이터베이스의 부하를 분석하는 것이 어렵다.
2-3. 관계형데이터베이스의 용어
•
key : 행의 식별자로 사용되는 열을 primary key 라고 부름. 테이블에 저장된 레코드를 고유하게 식별하는 후보 키(candidate key) 중에서 데이터베이스 설계자가 지정한 속성이 primary key
•
관계 : 테이블 간의 관계를 의미. 관계를 맺는 테이블의 수에 따라 다음과 같이 나뉨.
◦
일대일 관계
◦
일대다 관계
◦
다대다 관계
→ 이때 등장하는 것이 외래 키(foreign key)임. 외래키는 한 테이블의 키 중에서 다른 테이블의 행을 식별할 수 있는 키를 의미.
3. SQL이란
SQL(Structured Query Language)은 관계형 데이터베이스에서 사용되는 언어다. 관계형 DBMS 중 MySQL를 배우려면 SQL을 필수로 익혀야 한다. SQL이 데이터베이스를 조작하는 ‘언어’이긴 하지만 일반적인 프로그래밍 언어(C, 자바, 파이썬 등)와는 조금 다른 특성을 갖는다.
SQL은 특정 회사에서 만드는 것이 아니라 국제 표준화 기구에서 SQL에 대한 표준을 정해서 발표하고 있다. 이를 표준 SQL이라고 합니다. 그런데 문제는 SQL을 사용하는 DBMS를 만드는 회사가 여러 곳이기 때문에 표준 SQL이 각 회사 제품의 특성을 모두 포용하지 못한다는 점이다. 그래서 DBMS를 만드는 회사에서는 되도록 표준 SQL을 준수하되, 각 제품의 특성을 반영한 SQL을 사용한다.
다음 그림을 보면 3가지 DBMS 제품(오라클, SQL 서버, MySQL)이 모두 표준 SQL을 포함하고 있다. 그래서 표준 SQL을 익히면 대부분의 DBMS에 공통적으로 적용할 수 있다.
각 DBMS는 추가로 자신만의 기능도 가지고 있어서 이렇게 변경된 SQL을 오라클은 PL/SQL, SQL서버는 T-SQL, MySQL은 SQL로 부른다.
2. SQL 구성
•
DDL (데이터 정의 언어)
︎ create, alter, drop 등의 명령어•
DML (데이터 조작 언어)
︎ insert, update, delete, select 등의 명령어•
DCL (데이터 제어 언어)
︎ grant, revoke, commit, rollback3. SQL의 종류
많은 관계형 데이터베이스 관리 시스템 (RDBMS) 중에서 가장 널리 사용되고 널리 사용되는 두 가지가 Oracle 및 SQL Server다.
오라클과 SQL Server 사이에는 근본적인 차이점이 많다. 가장 중요한 차이점은 사용하는 언어다. 오라클은 PL / SQL
(프로 시저 언어 SQL)을 사용하고 SQL Server는 Transact-SQL과 같은 T-SQL을 사용한다.
3-1. 오라클과 SQL SERVER
대표적인 차이점
3-2. MSSQL, 얜 또 뭔데?
서버를 윈도우로 두는 회사들은 MSSQL을 선호한다. 대용량 데이터베이스로 적합한 것은 MSSQL이나 Oracle이다.
ORACLE과 MSSQL의 차이점3-3. MSSQL 본격 탐구
SSMS 를 다운받는데, sql server management studio라고 부르고 client tool이며 그 자체로 서버가 아니다. 사용자가 sql server에 연결하기 위해서 ssms라는 도구를 사용하는 것뿐
1.
데이터 조회와 가공을 위한 SQL문법 공부(mysql공부할 것임)
생활코딩
2.
SQL을 통한 데이터 분석 공부(실습)
문제를 제공해주는 사이트(programmers(쉬움)→leetcode)
Day 1 QUIZ from PROGRAMMERS
DATETIME 데이터에서 시각만 뽑으려면 HOUR() 를 사용한다.
어떤 변수에 특정 값을 할당해주는 함수 : SET —>ex) SET @변수 = 값;
SET 사용시 대입 연산자를 '='를 사용하고 그 외에는 := (오라클에서만 사용하는 대입연산자) 를 사용해야 한다.
Problem 1
SET @HOUR = -1;
SELECT (@HOUR := @HOUR + 1) AS HOUR,
(SELECT COUNT(HOUR(DATETIME))
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME)=@HOUR) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23;
Problem2
출력되는 데이터 수를 한정할 수 있는 함수 LIMIT —> ex) LIMIT 1; : 데이터 출력 값들 중 하나만 출력. 맨 마지막에 지정해준다.
하지만!!! ORACLE은 사용 불가하다. 대신 사용하는 것이 ROWNUM=1 인데, LIMIT과 사용방식이 좀 다르다.
LIMIT 은 쿼리가 ORDER BY절까지 모두 수행한 후 결과에서 원하는 행의 데이터를 위에서부터 가져오는 것이고, ROWNUM은 쿼리가 완전히 수행되지 않은 원 데이터의 정렬순서대로 번호를 매기기 때문에 전혀 다른 결과가
출력된다.
ROWNUM은 ≤와 < 연산만 사용가능하다. 단, 1행은 다른 예외다.
예시를 들어보겠다.
다른 SQL에서 사원명(ENAME)로 정렬한 후 2행만 가져오는 쿼리문은 다음과 같다.
같은 방식으로 ORACLE에서 ROWNUM을 수행하면 다음과 같다.
결론은 오라클에서 LIMIT과 동일한 결과를 얻고 싶다면 subquery 내에서 미리 정렬을 수행한 다음 where절에서 ROWNUM을 수행하면 된다.
LIMIT의 또 다른 장점은 원하는 구간의 행을 출력할 수 있다는 것이다. 즉, 처음행부터 뽑을 필요 없이 중간부터 행을 뽑을 수도 있다.
7번째 행부터 3건의 행을 출력하는 쿼리다.
오라클에서 원하는 구간의 행을 출력하려면 또 다시 subquery문을 필요로 하고, ROW_NUMBER(): window 함수 라는 순위를 구해주는 함수를 사용해 행의 번호를 지정한 다음 해당 번호의 구간을 잘라줘야 한다.
~ programmers (rownum / limit) ~
1.
•
mysql
SELECT A.NAME, A.DATETIME
FROM ANIMAL_INS A
LEFT JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL
ORDER BY A.DATETIME
LIMIT 3;
SQL
복사
•
oracle
select name,
datetime
from (
select i.name,
i.datetime
from animal_ins i
left join animal_outs o on i.animal_id = o.animal_id
where o.animal_id is null
order by i.datetime
)
where rownum <= 3;
SQL
복사
2.
오랜 기간 보호한 동물(2)
•
mysql
SELECT A.ANIMAL_ID,
A.NAME
FROM ANIMAL_OUTS A
JOIN ANIMAL_INS B
ON A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY (A.DATETIME - B.DATETIME) DESC
LIMIT 2;
SQL
복사
•
oracle
SELECT ANIMAL_ID,
NAME
FROM (
SELECT O.*
FROM ANIMAL_OUTS O
JOIN ANIMAL_INS I ON O.ANIMAL_ID = I.ANIMAL_ID
ORDER BY (O.DATETIME - I.DATETIME) DESC
)
WHERE ROWNUM <= 2;
SQL
복사
Problem 3
MOD(필드,나눌숫자) : 특정 숫자로 나누고 나머지를 출력해주는 함수(ORACLE 만 사용 가능)
—> ex) 짝수인 값 : MOD(필드값,2)=0 , 홀수인 값 : MOD(필드값,2)=1
—> 물론, (필드값%2)=0 또는 (필드값%2)=1 라고 해도 같다.
UNIQUE vs DISTINCT
같음. 단지 unique가 좀더 오래된 예약어
Problem 4
Weather Observation Station 6 문제다.
도시 이름 중에 모음으로 시작하는 도시를 출력한다. 중복은 제거한다.
풀이1) SUBSTR() , IN 사용
SELECT DISTINCT CITY
FROM STATION
WHERE SUBSTR(CITY,1,1) IN ('A','E','I','O','U');
SQL
복사
풀이2) LIKE ‘모음%’ 사용
SELECT DISTINCT CITY
FROM STATION
WHERE (CITY LIKE 'A%' OR CITY LIKE 'E%' OR CITY LIKE 'I%' OR CITY LIKE 'O%' OR CITY LIKE 'U%');
SQL
복사
도시 이름 중에 모음으로 끝나는 도시를 출력. 중복은 제거 ( LIKE ‘%모음’ 사용 )SELECT DISTINCT CITY
FROM STATION
WHERE (CITY LIKE '%a' OR CITY LIKE '%e' OR CITY LIKE '%i' OR CITY LIKE '%o' OR CITY LIKE '%u');
SQL
복사
맨 앞도 모음으로 시작하고 끝도 모음으로 끝나는 도시를 출력. 중복은 제거 (SUBSTR + LIKE 모두 사용)SELECT DISTINCT CITY
FROM STATION
WHERE SUBSTR(CITY,1,1) IN ('A','E','I','O','U')
AND (CITY LIKE '%a' OR CITY LIKE '%e' OR CITY LIKE '%i' OR CITY LIKE '%o' OR CITY LIKE '%u');
SQL
복사
Problem 5
문자열을 뒤에서부터 자르기 —> SUBSTR(문자열,-뒤에서부터의개수)
SELECT DISTINCT CITY
FROM STATION
WHERE SUBSTR(CITY,1,1) NOT IN ('A','E','O','I','U') -- 앞에 모음으로 시작하지 않거나
OR SUBSTR(CITY,-1) NOT IN ('a','e','i','o','u'); -- 뒤가 모음으로 시작하지 않는
SQL
복사
Problem 6
CASE WHEN 사용해서 다중조건 활용하기
SELECT
CASE WHEN A+B<=C OR A+C<=B OR B+C<=A THEN 'Not A Triangle'
ELSE CASE WHEN A=B AND B=C AND A=C THEN 'Equilateral'
ELSE CASE WHEN A=B OR B=C OR A=C THEN 'Isosceles'
ELSE 'Scalene'
END END END
FROM TRIANGLES;
SQL
복사
Problem 7
TRUNC() : 버림값을 구하는 기능. 단순히 입력한 수치에서 자릿수만큼의 값만 구하고 나머지는 버린다.
SELECT TRUNC(AVG(POPULATION) -- 소수점만 버리기 ( FLOOR 도 가능 )
FROM CITY;
SQL
복사
Problem 8
REPLACE() 는 숫자값도 바꿀 수 있다. 하지만 결과는 문자라는 것에 유의해야 한다.
Problem 10
New Companies : left join 과 중첩 join을 연습할 수 있다.
SELECT A.company_code,
A.founder,
COUNT(DISTINCT LM.lead_manager_code),
COUNT(DISTINCT SM.senior_manager_code),
COUNT(DISTINCT M.manager_code),
COUNT(DISTINCT E.employee_code)
FROM Company A
LEFT JOIN Lead_Manager LM ON A.company_code = LM.company_code
LEFT JOIN Senior_Manager SM ON LM.lead_manager_code = SM.lead_manager_code
LEFT JOIN Manager M ON SM.senior_manager_code = M.senior_manager_code
LEFT JOIN Employee E ON M.manager_code = E.manager_code
GROUP BY A.company_code, A. founder
ORDER BY A.company_code;
SQL
복사
Problem 11
오라클은 “” 와 ‘’ 사용이 다르다.
“”은 칼럼명을 감싸줄 때 사용하고
‘’은 문자열을 감싸줄 때 사용한다.
Problem 12
데이터의 중앙값 구하기
ORACLE : MEDIAN() 이라는 함수가 존재
MySQL : PERCENT_RANK() 함수 사용 —> .5 가 되는 값이 중앙값
# ORACLE
SELECT ROUND(MEDIAN(LAT_N),4)
FROM STATION;
# MYsql
SELECT ROUND(LAT_N,4)
FROM (SELECT LAT_N, PERCENT_RANK() OVER(ORDER BY LAT_N) percent
FROM STATION) A
WHERE percent = 0.5;
SQL
복사
Problem 13
•
INNER JOIN 과 CASE WHEN 의 사용
SELECT
CASE WHEN G.Grade < 8 THEN NULL ELSE S.Name END AS NULLorNAME,
G.Grade,
S.Marks
FROM Students S
INNER JOIN Grades G on S.Marks BETWEEN G.Min_Mark AND G.Max_Mark
ORDER BY G.Grade DESC, S.Name, S.Marks;
SQL
복사
Problem 14 - Top Competitors
•
중첩 inner join 과 group by, having
SELECT
H.hacker_id,
H.name
FROM Submissions S
JOIN Challenges C ON S.challenge_id = C.challenge_id
JOIN Difficulty D ON C.difficulty_level = D.difficulty_level
JOIN Hackers H ON S.hacker_id = H.hacker_id
WHERE S.score = D.score
GROUP BY H.hacker_id, H.name
HAVING COUNT(DISTINCT S.submission_id)>= 2
ORDER BY COUNT(DISTINCT S.submission_id) DESC, H.hacker_id;
SQL
복사
Problem 15 - Binary Tree Nodes
left join 과 case when 을 활용해서 풀 수 있음
SELECT
DISTINCT BST1.N,
CASE WHEN BST1.P IS NULL THEN 'Root'
WHEN BST2.N IS NULL THEN 'Leaf'
ELSE 'Inner'
END AS Place
FROM BST BST1
LEFT JOIN BST BST2 ON BST1.N = BST2.P
ORDER BY BST1.N;
SQL
복사
Problem 16 - 날짜에서 특정 부분추출하기
SOL1)
EXTRACT 함수를 사용한다. 이때, 보통 날짜와 시간을 함께 사용할 때 데이터타입은 DATETIME인데, EXTRACT를 사용하기 위해서는 DATE타입이나 TIMESTAMP 타입으로 변경해주어야 한다.
EXTRACT(추출할 값 FROM CAST(칼럼명 AS DATE또는TIMESTAMP))
SOL2) TO_CHAR 함수를 사용한다.
-- SOL1)
SELECT EXTRACT(HOUR FROM CAST(DATETIME AS TIMESTAMP)) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY EXTRACT(HOUR FROM CAST(DATETIME AS TIMESTAMP))
HAVING EXTRACT(HOUR FROM CAST(DATETIME AS TIMESTAMP)) >= 9
ORDER BY HOUR;
-- SOL2)
SELECT TO_NUMBER(TO_CHAR(DATETIME,'HH24')) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY TO_CHAR(DATETIME,'HH24')
HAVING TO_CHAR(DATETIME,'HH24') BETWEEN 9 AND 19
ORDER BY HOUR;
SQL
복사
Problem 17 -
•
CONNECT BY LEVEL : LEVEL은 순위를 의미한다. 연속된 숫자를 조회할 때 사용한다.
EX1) 연속된 숫자를 가지는 테이블 생성
SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 10; -- 10이하의 연속된 숫자를 생성
SQL
복사
EX2) 2020년 1월부터 12월까지 출력
SELECT '2020년 ' || LPAD(LEVEL,2,0) || '월' AS NO
FROM DUAL
CONNECT BY LEVEL <= 12;
SQL
복사
EX3) 데이터 복제
대량의 데이터를 만들거나 동일 데이터를 여러 건 만들 때 유용할 수 있다. —> 조인을 이용하는데
조인 고리를 무시하게 되면 N*M 만큼의 데이터가 생성되어서 복제된다.
예를들어, EMP 데이터를 조인고리를 주지 않고 LEVEL ≤ 2 와 조인을 하면 EMP 데이터가 2배 복제된다.
SELECT Y.NO, X.*
FROM (
SELECT *
FROM EMP e
) X, (
SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 2
) Y
ORDER BY ENAME, JOB, NO;
SQL
복사
위의 개념을 활용해서 문제를 풀어보면
SOL1)
SELECT A.NO, COUNT(B.HOUR)
FROM(SELECT (LEVEL-1) AS NO
FROM DUAL
CONNECT BY LEVEL <= 24) A
LEFT JOIN (SELECT TO_NUMBER(TO_CHAR(DATETIME,'HH24')) AS HOUR
FROM ANIMAL_OUTS) B
ON A.NO = B.HOUR
GROUP BY A.NO -- GROUP BY를 JOIN 이 끝난 뒤에 할 수도 있고
ORDER BY A.NO;
SOL2)
SELECT A.NO HOUR, NVL(B.CNT,0) AS COUNT -- NVL(NULL값이 들어있는 열, NULL대체값)
FROM(SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 24) A
LEFT JOIN (SELECT TO_NUMBER(TO_CHAR(DATETIME,'HH24')) AS HOUR, COUNT(*) AS CNT
FROM ANIMAL_OUTS
GROUP BY TO_NUMBER(TO_CHAR(DATETIME,'HH24'))) B -- B테이블내에서 먼저 그룹핑을 해도 된다.
ON A.NO = B.HOUR
ORDER BY A.NO;
SQL
복사
Problem 18
•
출력 : hacker_id, name, 만든 문제수
•
정렬 : 문제수 많은 순으로 정렬, → 같다면 hacker_id 순으로 정렬
•
필터 : 문제수가 같은데 최대 문제수보다 작으면 그 학생들은 출력x
1.
먼저, 만든 문제 개수가 곂치지 않는 값들을 알아봐야 한다.
SELECT sub1.hacker_id, sub1.challeges_created
FROM (SELECT hacker_id, COUNT(*) AS challenges_created
FROM Challenges
GROUP BY hacker_id) sub1
GROUP BY sub1.challenges_created
HAVING COUNT(*) = 1 -- 문제수가 동일하지 않고 unique한 문제수만 필터
SQL
복사
2.
만든 문제 개수(challenges_created)의 최댓값을 찾는다.
SELECT MAX(sub1.challenges_created)
FROM(SELECT COUNT(*) AS challenges_created
FROM Challenges
GROUP BY hacker_id) sub1;
SQL
복사
3.
최댓값이면서 중복되는 값이라면 출력한다. 하지만, 최댓값이 아닌데 중복되는 값을 출력하면 안된다.
즉, 출력될 수 있는 값들은 중복되지만 최대문제수인 행들과 중복되지 않은 문제수를 가지고 있는 행들이다. (1이거나 2를 만족하는 행들)
SQL
복사
주의해야 할 점!! GROUP BY 절이 SELECT절보다 먼저 실행되지만, SELECT절의 alias를 사용할 수 있다. 결론 : GROUP BY, HAVING, ORDER BY절에서 SELECT절의 alias 사용 가능
참고로, WHERE절에서는 SELECT절의 alias를 사용할 수 없다. 실제 칼럼명만 가능하다.
ex) 위의 예시
SELECT HOUR(DATETIME) AS 'c1', COUNT(*) AS 'COUNT'
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) <= 19
GROUP BY c1
ORDER BY c1;
SQL
복사
Problem 19 - Swap Salary(leetcode)
•
UPDATE 와 CASE WHEN 을 병합
UPDATE SALARY
SET sex = CASE WHEN sex='f' then 'm'
WHEN sex='m' then 'f'
END;
SQL
복사
Problem 20 - 중복 데이터 하나만 남기고 제거
1.
먼저 각 행의 고유한 값을 부여할 수 있는 rowid나 고유키가 있어야 한다.
2.
중복되는 행들을 하나만 남기고 제거하려면 중복되는 기준인 열로 묶고, MAX(rowid)를 이용해서 하나의 데이터만 남긴다.
→ 어떻게 중복되는 값들을 고르지?
MAX(rowid) 값보다 작은 rowid를 가진 데이터들을 필터링해주면된다.
→ 삭제는 어떻게 하지?
WHERE 절에서 IN함수를 사용하고 최종적으로 DELETE 함수를 사용해주면 된다.
SELECT sub1.*
FROM(SELECT id,
email,
COUNT(*) OVER (PARTITION BY email) CNT,
MIN(id) over (PARTITION BY email) MIN_email
FROM Person) sub1 --이메일로 그룹핑해서 중복개수 구하고, 그룹핑된 이메일의 고유키 id중 최소 id를 구함
WHERE sub1.CNT > 1 -- 중복되는 값을 가지는 데이터들만 추출
AND sub1.id > sub1.MIN_email; -- 그 중에서도 최소id보다 큰 데이터들만 추출해서 데이터 하나만 남기고 중복되는 데이터들 추출
-------------------------------------------------------------------
-- sol1)
DELETE
FROM Person
WHERE Person.id IN (SELECT sub1.id
FROM(SELECT id,
email,
COUNT(*) OVER (PARTITION BY email) CNT,
MIN(id) OVER (PARTITION BY email) MIN_ID,
FROM Person) sub1 --이메일로 그룹핑해서 중복개수 구하고, 그룹핑된 이메일의 고유키 id중 최소 id를 구함
WHERE sub1.CNT > 1 -- 중복되는 값을 가지는 데이터들만 추출
AND sub1.id > sub1.MIN_ID)
-- sol2) 굳이 COUNT(*)를 구할 필요가 없다.
DELETE
FROM Person
WHERE id NOT IN (SELECT MIN(id)
FROM Person
GROUP BY email);
-- sol3) delete에 join 활용하기 --> 오라클에서는 delete에서 join을 지원하지 않는다.(하지만, mysql이라고 생각하고 풀어보면)
DELETE A -- A의 테이블에서만 삭제
FROM Person A
INNER JOIN Person B ON A.email = B.email
WHERE A.id > B.id -- 조인되었을 때 중복되는 값들 중 id가 최솟값인 데이터들을 추출하기 원했기 때문에
SQL
복사
problem 21 - top earners(hackerrank)
-- WHERE 절 서브쿼리
SELECT months * salary AS earnings,
COUNT(*) AS CNT
FROM Employee
WHERE months * salary = (SELECT MAX(months*salary) FROM Employee)
GROUP BY earnings;
-- HAVING 절 서브쿼리
SELECT months * salary AS earnings,
COUNT(*) AS CNT
FROM Employee
GROUP BY months*salary
HAVING months*salary = (SELECT MAX(months*salary) FROM Employee);
-- FROM 절 서브쿼리
SELECT *
FROM (SELECT months*salary AS earnings,
COUNT(*) AS CNT
FROM Employee
GROUP BY months*salary
ORDER BY months*salary DESC)
WHERE ROWNUM = 1;
SQL
복사
problem 22 - department highest salary(leetcode)
•
inner join 두번, join의 조건에 두 가지 이상의 조건 걸기 가능
-- SOL1) INNER JOIN 을 두번 사용,
SELECT D.name AS Department,
E.name AS Employee,
E.salary AS Salary
FROM Employee E
INNER JOIN (
SELECT departmentId, MAX(salary) as max_salary
FROM Employee
GROUP BY departmentId) dh ON E.departmentId = dh.departmentId
AND E.salary = dh.max_salary
INNER JOIN Department D ON E.departmentId = D.id;
-- SOL2) 서브쿼리와 이너조인을 결합
SELECT ms.department
,ms.name AS Employee
,ms.Salary
From(
SELECT Employee.name
,Salary
,MAX(Salary) OVER (PARTITION BY departmentId) AS max_salary
,Department.name AS department
FROM Employee
INNER JOIN Department Employee.departmentId = Department.id
) AS ms
WHERE ms.Salary = ms.max_salary
-- SOL3) DENSE_RANK() / RANK()
SELECT ms.department
,ms.name AS Employee
,ms.salary
FROM(SELECT Employee.name
,salary
,RANK(salary) OVER(PARTITION BY departmentId ORDER BY salary DESC) AS rank
,Department.name AS department
FROM Employee
INNER JOIN Department ON Employee.departmentId = Department.id
) ms
WHERE ms.rank == 1;
SQL
복사
Tip: SELECT 절에서 연산한 결과물은 WHERE 절에서 사용할 수 없다. → SOL) 서브쿼리로 한번 감싸줘야 한다.
problem 23 - challenges
SELECT c.hacker_id, h.name, COUNT(c.challenge_id) AS c_count -- 먼저, 필요한 변수들을 가져오고
FROM Challenges c
INNER JOIN Hackers h ON c.hacker_id = h.hacker_id -- 조인을 해줘야 hacker의 이름을 가져올 수 있다.
GROUP BY c.hacker_id, h.name -- grouping은 h.name까지 해줘야 출력가능하다.
/* 조건 1 : 만든 문제 수가 최대값인 경우 */
HAVING COUNT(c.challenge_id) = (SELECT MAX(cnt)
FROM(SELECT COUNT(*) AS cnt
FROM Challenges
GROUP BY hacker_id) A)
/* 조건 2 : 최댓값이 아니라면 중복되지 않는 문제수를 만든 경우 */
OR COUNT(c.challenge_id) IN (SELECT B.cnt
FROM (SELECT COUNT(*) AS cnt
FROM Challenges
GROUP BY hacker_id) B
GROUP BY B.cnt
HAVING COUNT(B.cnt) = 1)
ORDER BY c_count DESC, c.hacker_id;
-- with 절 사용
WITH counter AS(
SELECT Hackers.hacker_id,
Hackers.name,
COUNT(*) AS challenges_created
FROM Challenges
INNER JOIN Hackers ON Challenges.hacker_id = Hackers.hacker_id
GROUP BY Hackers.hacker_id, Hackers.name
)
SELECT
FROM counter
WHERE challenges_created = (SELECT MAX(challenges_created) FROM counter) -- 조건1
OR challenges_created IN (SELECT challenges_created
FROM counter
GROUP BY challenges_created
HAVING count(*) = 1) -- 조건2
ORDER BY counter.challenges_created DESC, counter.hacker_id;
SQL
복사
problem 24 - The Report(hackerrank)
SELECT (CASE WHEN g.Grade < 8 THEN NULL ELSE s.Name END) AS Name, -- case when 절을 사용해서 출력값을 조건에 맞게 지정할 수 있다.
g.Grade,
s.Marks
FROM Students s, Grades g
WHERE s.Marks BETWEEN g.Min_Mark AND g.Max_Mark -- join 절에 between and 절이 들어갈 수 있다.
ORDER BY g.Grade DESC, Name, s.Marks;
SQL
복사
problem 25 - Consecutive Numbers
-- sol 1) lead over 을 사용한 풀이
SELECT DISTINCT num AS ConsecutiveNums
FROM (SELECT id, num,
LEAD(id,2,-1) OVER(PARTITION BY num ORDER BY id) AS id2
FROM Logs) a
WHERE a.id2 = a.id + 2;
-- sol 2) lead over 을 사용한 풀이2
SELECT DISTINCT Num AS ConsecutiveNums
FROM (SELECT Num,
LEAD(Num,1) OVER (ORDER BY id) AS next,
LEAD(Num,2) OVER (ORDER BY id) AS afternext
FROM Logs) l
WHERE Num = next and Num = afternext;
-- sol 3) lag over 을 사용한 풀이
SELECT DISTINCT Num AS ConsecutiveNums
FROM(SELECT Num,
LAG(Num,1) OVER (ORDER BY id) AS before,
LAG(Num,2) OVER (ORDER BY id) AS morebefore
FROM Logs) l
WHERE Num = before AND Num = morebefore;
-- sol 4) join을 사용한 풀이
SELECT DISTINCT l.num AS ConsecutiveNums
FROM Logs l
INNER JOIN Logs l_next ON l.id + 1 = l_next.id
INNER JOIN Logs l_next2 ON l.id + 2 = l_next2.id
WHERE l.num = l_next.num AND l.num = l_next2.num;
SQL
복사
Tip - 누적합 구하기
•
window 절의 이용 : sum(특성1) over ( order by 특성2 ) as CumSum
: 특성2 로 정렬한 뒤, 특성1의 누적값을 CumSum 특성으로 할당
•
조인 활용 + group by
SELECT e1.Id,
e1.Name,
e1.kg,
e1.Line,
SUM(e2.kg) AS CumSum
FROM Elavtor e1
INNER JOIN Elavtoer e2
ON e1.Id = e2.Id
AND e1.Line >= e2.Line
GROUP BY 1,2,3,4;
SQL
복사
•
select 절 서브쿼리
SELECT e1.Id,
e1.Name,
e1.kg,
e1.Line,
(SELECT SUM(e2.kg)
FROM Elavator e2
WHERE e1.Id = e2.Id
AND e1.Line >= e2.Line) AS CumSum
FROM Elavator e1
SQL
복사
problem 26 - department top three salaries
SELECT ms.department
,ms.employee
,ms.salary
FROM(SELECT Employee.name AS employee
,Department.name AS department
,Employee.salary
,DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS rank
FROM Employee
JOIN Department ON Employee.departmentId = Department.id
) ms
WHERE ms.rank <= 3;
SQL
복사
problem 27 - Nth highest salary (leetcode 177)
•
rownum
•
dense_rank()
•
max() / min() / DISTINCT()
/* sol1 */
CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS
result NUMBER;
BEGIN
SELECT
salary
INTO
RESULT
FROM
(
SELECT
emp.salary
, DENSE_RANK() OVER(ORDER BY emp.salary DESC) AS ranking
FROM
employee emp
) vemp
WHERE
vemp.ranking = N
AND
ROWNUM = 1
;
RETURN result;
END;
/* sol2 */
CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS
result NUMBER;
BEGIN
SELECT salary into result
FROM(
SELECT salary, rownum r1
FROM(
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
)
)
WHERE r1=N;
RETURN result;
END;
/* sol3 */
CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS
result NUMBER;
BEGIN
SELECT DISTINCT Salary INTO result
FROM (
SELECT Salary, DENSE_RANK() OVER(ORDER BY Salary DESC) AS RK
FROM Employee
) T
WHERE RK = N;
RETURN result;
END;
SQL
복사
8. 12 JOIN
“FROM 기준 테이블 JOIN 연관 테이블 ON 조건” 의 형태로 사용하며 ON에는 기준 테이블과 연관 테이블의 관계를 나타내는 조건을 설정한다. “JOIN .. ON 조건” 형식을 “(+)” 로 변경할 수도 있다.
•
JOIN : 기준 테이블과 대상 테이블에 매칭하는 필드값이 있는 경우에 사용
•
LEFT JOIN : 기준 테이블의 모든 필드값을 보이고 대상 테이블에 매칭하는 필드값이 있는 경우에 검색하고 그렇지 않으면 공백처리한다.
•
RIGHT JOIN : 대상 테이블의 모든 필드값을 보이고 기준 테이블에 매칭하는 필드값이 있는 경우에 검색하고 그렇지 않으면 공백처리한다.
•
FULL OUTER JOIN (= FULL JOIN) : 기준 테이블과 대상 테이블에 상호 매칭하는 필드값이 있는 경우에 검색하고, 그렇지 않으면 공백처리한다. 양쪽 테이블에 있는 데이터를 모두 보여준다.
JOIN 사용하지 않고 하는 조인 방법
SELECT B.CLASS_NM,
A.STUDENT_NO,
A.STUDENT_NM,
A.KOR,
A.ENG,
A.MAT,
A.TOT,
A.AVG
FROM TB_GRADE A, TB_CLASS_INFO B
WHERE A.CLASS_CD = B.CLASS_CD;
SQL
복사
연관 검색할 때 별명을 사용하면 SQL을 간결하게 작성할 수 있다. 이제, TB_GRADE_08 과 TB_GRADE_09테이블을 이용해서 다양한 JOIN연습을 하겠다. 두 테이블에 속한 변수는 TEST_CD다. TEST_CD 변수 중 공통적으로 가지고 있는 값들이 있고 각 테이블에만 존재하는 값들이 존재한다.
1.
두 테이블에 공통적인 시험코드를 보낸다. 일반적인 JOIN
SELECT A.TEST_CD AS "08_TEST_CD",
A.KOR AS "08_KOR",
A.ENG AS "08_ENG",
A.MAT AS "08_MAT",
B.TEST_CD AS "09_TEST_CD",
B.KOR AS "09_KOR",
B.ENG AS "09_ENG",
B.MAT AS "09_MAT"
FROM TB_GRADE_08 A
JOIN TB_GRADE_09 B
ON A.TEST_CD = B.TEST_CD;
SQL
복사
2.
LEFT JOIN
SELECT A.TEST_CD AS "08_TEST_CD",
A.KOR AS "08_KOR",
A.ENG AS "08_ENG",
A.MAT AS "08_MAT",
B.TEST_CD AS "09_TEST_CD",
B.KOR AS "09_KOR",
B.ENG AS "09_ENG",
B.MAT AS "09_MAT"
FROM TB_GRADE_08 A
LEFT JOIN TB_GRADE_09 B
ON A.TEST_CD = B.TEST_CD;
SQL
복사
A와 B에 공통적으로 있는 값은 정상적으로 JOIN을 수행하고 A에만 있는 데이터들은 집어넣되 B에 해당하는 값에는 null이 삽입된다. 한편 B에만 있는 데이터는 들어오지 않는다.
위의 결과를 “(+)”를 활용해서 바꾸면 아래와 같다.
SELECT A.TEST_CD AS "08_TEST_CD",
A.KOR AS "08_KOR",
A.ENG AS "08_ENG",
A.MAT AS "08_MAT",
B.TEST_CD AS "09_TEST_CD",
B.KOR AS "09_KOR",
B.ENG AS "09_ENG",
B.MAT AS "09_MAT"
FROM TB_GRADE_08 A, TB_GRADE_09 B
WHERE A.TEST_CD = B.TEST_CD(+);
SQL
복사
3.
RIGHT JOIN
SELECT A.TEST_CD AS "08_TEST_CD",
A.KOR AS "08_KOR",
A.ENG AS "08_ENG",
A.MAT AS "08_MAT",
B.TEST_CD AS "09_TEST_CD",
B.KOR AS "09_KOR",
B.ENG AS "09_ENG",
B.MAT AS "09_MAT"
FROM TB_GRADE_08 A
RIGHT JOIN TB_GRADE_09 B
ON A.TEST_CD = B.TEST_CD;
SQL
복사
위의 결과를 “(+)”를 이용해서 바꾸면
SELECT A.TEST_CD AS "08_TEST_CD",
A.KOR AS "08_KOR",
A.ENG AS "08_ENG",
A.MAT AS "08_MAT",
B.TEST_CD AS "09_TEST_CD",
B.KOR AS "09_KOR",
B.ENG AS "09_ENG",
B.MAT AS "09_MAT"
FROM TB_GRADE_08 A, TB_GRADE_09 B
WHERE A.TEST_CD(+) = B.TEST_CD;
SQL
복사
4.
FULL OUTER JOIN (= FULL JOIN) : 두 테이블에 있는 데이터를 모두 보이지만 공통되지 않는 값들에 대해서는 null을 넣어준다.
SELECT A.TEST_CD AS "08_TEST_CD",
A.KOR AS "08_KOR",
A.ENG AS "08_ENG",
A.MAT AS "08_MAT",
B.TEST_CD AS "09_TEST_CD",
B.KOR AS "09_KOR",
B.ENG AS "09_ENG",
B.MAT AS "09_MAT"
FROM TB_GRADE_08 A
FULL JOIN TB_GRADE_09 B
ON A.TEST_CD = B.TEST_CD;
SQL
복사
A와 B에 각각 존재하는 매칭되지 않는 값들에 대해서는 null을 입력해준다.
8.13 CASE
조건대상이 있을수도 없을 수도 있지만 큰 틀은 다음과 같다. SELECT에서 사용한다.
CASE [조건대상] WHEN ‘비굣값’ THEN 결과
ELSE 조건을 모두 만족하지 않을 경우의 결과
END
예제 1. 성적 테이블의 국어, 영어, 수학 점수의 분포에 따라 숫자값을 수,우,미,양,가 로 바꾸자. —> 조건이 많기 때문에 CASE를 사용한다.
SELECT CLASS_CD,
STUDENT_NO,
STUDENT_NM,
CASE WHEN KOR>=91 AND KOR <=100 THEN '수'
WHEN KOR>=80 AND KOR<=89 THEN '우'
WHEN KOR>=70 AND KOR<=79 THEN '미'
WHEN KOR>=60 AND KOR<=69 THEN '양'
ELSE '가'
END AS KOR,
CASE WHEN ENG>=91 AND ENG <=100 THEN '수'
WHEN ENG>=80 AND ENG<=89 THEN '우'
WHEN ENG>=70 AND ENG<=79 THEN '미'
WHEN ENG>=60 AND ENG<=69 THEN '양'
ELSE '가'
END AS ENG,
CASE WHEN MAT>=91 AND MAT <=100 THEN '수'
WHEN MAT>=80 AND MAT<=89 THEN '우'
WHEN MAT>=70 AND MAT<=79 THEN '미'
WHEN MAT>=60 AND MAT<=69 THEN '양'
ELSE '가'
END AS MAT,
TOT,
AVG
FROM TB_GRADE;
SQL
복사
예제2. 성적 테이블의 반코드(CLASS_CD)에 따라 이름을 종합입시반, 단과반, 대학편입반, 일반인반 으로 바꿔보자.
SELECT CASE CLASS_CD WHEN 'A' THEN '종합입시반'
WHEN 'B' THEN '단과반'
WHEN 'C' THEN '대학편입반'
WHEN 'D' THEN '일반인반'
END AS CLASS_NM,
STUDENT_NO,
STUDENT_NM
FROM TB_GRADE;
SQL
복사
8.14 ROWNUM
ROWNUM 구문은 레코드를 검색할 때 생성되는 행 번호를 구해준다. 레코드에 고정된 것이 아니라 검색 결과에 따라 유동적으로 바뀐다.
예제 1. 고객 테이블의 전체 행 번호를 검색한다.
SELECT ROWNUM,
CUSTOMER_CD,
CUSTOMER_NM,
MW_FLG,
BIRTH_DAY,
PHONE_NUMBER
FROM TB_CUSTOMER;
SQL
복사
만약, 행번호와(rownum) 전체 레코드를 같이 출력하고 싶다면 테이블에 꼭 별명을 지정하고 “별명.*” 형식과 ROWNUM을 사용한다.
SELECT ROWNUM, A.*
FROM TB_CUSTOMER A;
예제2. 고객테이블에서 남성인 고객을 행 번호와 함께 검색해 고객명으로 정렬한다.
SELECT ROWNUM, CUSTOMER_CD,
CUSTOMER_NM,
MW_FLG,
BIRTH_DAY,
PHONE_NUMBER
FROM TB_CUSTOMER
WHERE MW_FLG='M'
ORDER BY CUSTOMER_NM;
SQL
복사
참고로 행 번호는 정렬 전(ORDER BY 전)의 행 번호가 출력되므로 정렬 순서를 바꿔도 행 번호는 바뀌지 않는다.
8.15 NULL
NULL은 필드에 할당된 값이 없는 상태다. IS NULL 명령은 값이 없는 데이터를 검색한다.
예를 들어, “WHERE REG_DT IS NULL” 의 결과는 REG_DT필드에 값이 없는 데이터를 검색한다. 반면,
IS NOT NULL 명령은 값이 있는 데이터를 검색한다.
주로 WHERE문에 사용한다.
예제1. 고객 테이블에서 전화번호가 없거나 이메일이 없는 고객을 검색한다.
SELECT CUSTOMER_CD,
MW_FLG,
PHONE_NUMBER,
EMAIL
FROM TB_CUSTOMER
WHERE PHONE_NUMBER IS NULL
OR EMAIL IS NULL;
SQL
복사
예제2. 고객 테이블에서 남성 중 이메일이 없거나, 여성 중 이메일이 있는 고객을 검색해 여성, 남성 순으로 보인다. (IS NULL, ORDER BY)
SELECT *
FROM TB_CUSTOMER
WHERE (MW_FLG='M'
AND EMAIL IS NULL)
OR (MW_FLG='W'
AND EMAIL IS NOT NULL)
ORDER BY MW_FLG DESC;
SQL
복사
예제3. 고객 테이블에서 고객명이 ‘강원진’, ‘박승대’, ‘이혜옥’, ‘남궁소망’ , ‘한찬희’ 인 고객 중 전화번호가 있는 고객을 검색한다. ( IN, IS NOT NULL)
SELECT *
FROM TB_CUSTOMER
WHERE CUSTOMER_NM IN ('강원진','박승대','이혜옥','남궁소망','한찬희')
AND PHONE_NUMBER IS NOT NULL;
SQL
복사
8.16 DUAL
DUAL 테이블은 오라클에서 기본적으로 설치되는 SYSTEM권한의 1행으로 구성된 테이블이다.
이 테이블은 값이 ‘X’인 DUMMY 필드를 가지고 있다.
일반적으로 검색보다는 수치 계산 값, 함수 실행 결과, 문자 출력값을 확인하는 데 사용된다.
예를 들어, “SELECT 3*2 FROM DUAL;” 은 3X2 의 결과인 6을 출력한다.
•
SELECT 연산, 함수실행결과, 문자 출력값 등
FROM DUAL;
•
SELECT 연산, 함수실행결과, 문자 출력값 등
FROM DUAL CONNECT BY LEVEL <= n;
CONNECT BY LEVEL을 사용하면 다중 행 조회가 가능하다.예제1. DUAL 테이블의 내용을 검색한다.
SELECT *
FROM DUAL;
SQL
복사
예제2. DUAL 테이블로 다음 연산의 값과 함수의 결과를 확인한다.
SELECT 123*45,
SYSDATE + 30,
'1Day SQL' || ' 실습',
SUBSTR('20190820',1,4)
FROM DUAL;
SQL
복사
결과는 한 행에 나타나는데, 각 값들을 필드로 한다.
예제3. DUAL 테이블로 ‘2019-1’에서 ‘2019-5’까지 문자열 행을 만든다. (총 5행이 만들어져야 하므로 CONNECT BY LEVEL 사용해야 한다)
SELECT '2019-' || ROWNUM
FROM DUAL
CONNECT BY LEVEL <= 5;
SQL
복사
DUAL테이블은 오라클에서 지원하는 테이블이므로, 다른 데이터베이스를 사용할 경우 DUAL기능을 사용하고자 한다면 다음 명령어로 DUAL 테이블을 만든다.CREATE TABLE DUAL
(
DUMMY VARCHAR2(1)
);
INSERT INTO DUAL VALUES('X');
COMMIT;
SQL
복사
COMMIT 명령으로 입력한 내용을 데이터베이스에 반영한다. 임시로 변경한 내용을 데이터베이스에 반영하는 기능이다.
8.17 UPDATE
UPDATE 명령은 조건식을 만족하는 데이터의 필드값을 바꾼다. 만약 조건식이 없다면 그냥 해당 필드값을 변경하고자 하는 값으로 모두 변경한다.
UPDATE 와 SET은 한 쌍.
•
UPDATE 테이블명
SET 필드명1 = 값1, 필드명2 = 값2, ..., 필드명n = 값n
WHERE 조건식;
예제1. 성적 테이블의 전체 학생을 검색해 A반과 B반의 합계와 평균을 확인한다.
SELECT *
FROM TB_GRADE;
SQL
복사
예제2. 성적 테이블에서 A반, B반인 학생을 대상으로 국어, 영어, 수학 점수의 합계와 평균을 계산해 바꾼다. (UPDATE SET, WHERE)
UPDATE TB_GRADE
SET TOT = KOR + ENG + MAT,
AVG = ROUND((KOR + ENG + MAT)/3,1)
WHERE CLASS_CD IN ('A','B');
SQL
복사
결과는 그냥 ‘몇개 행이 업데이트되었습니다.’ 라고 나온다.
예제3. 성적 테이블의 전체 학생을 검색해 A반, B반의 바뀐 합계와 평균을 확인한다.
SELECT *
FROM TB_GRADE;
SQL
복사
예제4. 성적 테이블에서 모든 학생을 대상으로 국어, 영어, 수학 점수의 합계와 평균을 계산해 바꾼다.
UPDATE TB_GRADE
SET TOT = KOR + ENG + MAT,
AVG = ROUND((KOR + ENG + MAT)/3,1);
SELECT *
FROM TB_GRADE;
SQL
복사
예제 5. 성적 테이블에서 UPDATE명령으로 계산한 모든 학생의 합계와 평균을 데이터베이스에 반영한다.
COMMIT;
SQL
복사
커밋 완료
8.18 INSERT
INSERT 명령은 지정한 테이블의 필드에 데이터를 입력한다. 입력값을 지정해서 입력하거나, SELECT에서 구한 값을 입력할 수 있으며 조건에 따라 원하는 테이블에 데이터를 입력하는 것도 가능하다.
INSERT INTO 테이블명 [(필드명)] VALUES (값1, 값2,...); 의 형태로 사용한다.
필드명을 지정하지 않고 바로 VALUES를 쓸 경우에는 모든 필드값에 데이터를 입력한다는 의미이므로 정확한 순서대로 입력해야 한다.
•
INSERT INTO 테이블명 VALUES (값1,값2,...,값n);
•
INSERT INTO 테이블명 (필드명1, 필드명2,...,필드명n) VALUES (값1,값2,....,값n);
지정되지 않은 필드는 NULL값이 허용되는 데이터여야 한다.두가지 실습으로 진행할 것이다. 첫 번째 실습은 성적 테이블에 새로운 학생인 ‘독고승재’, ‘이도희’ 학생을 입력하고 확인한다. 두번째 실습은 포인트 테이블의 등록일시 중 연도를 비교해 해당 데이터를 연도별 포인트 테이블에 입력한다.
첫 번째 실습을 진행한다.
예제1. 성적 테이블에서 전체 학생을 검색한다.
SELECT *
FROM TB_GRADE;
SQL
복사
예제2. 성적 테이블에 A반 3번 ‘독고승재’ 학생을 입력한다.
INSERT INTO TB_GRADE (CLASS_CD, STUDENT_NO, STUDENT_NM, KOR, ENG, MAT)
VALUES ('A', 3, '독고승재', 97, 88, 95);
SQL
복사
INSERT INTO 테이블명 (필드명1, 필드명2,...,필드명n) VALUES (값1,값2,....,값n); 을 사용함
예제3. 성적 테이블에 B반 4번 ‘이도희’ 학생을 입력한다.
INSERT INTO TB_GRADE VALUES ('B', 4, '이도희', 86,92,90,0,0);
SQL
복사
INSERT INTO 테이블명 VALUES (값1,값2,...,값n); 을 사용했다. 모든 필드값이 추가되므로
예제4. 성적 테이블을 전체 검색해서 추가한 내용을 확인한다.
SELECT *
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO;
SQL
복사
예제5. 성적 테이블에 입력한 ‘독고승재’ 와 ‘이도희’ 학생의 정보를 데이터베이스에 반영한다.
COMMIT;
SQL
복사
두번째 실습을 진행한다.
예제 6. 정보를 입력할 연도별 포인트 테이블을 검색한다.
SELECT * FROM TB_POINT_2017;
SELECT * FROM TB_POINT_2018;
SELECT * FROM TB_POINT_2019;
SQL
복사
초기 데이터가 없다.
예제7. 포인트 테이블의 등록일시 앞 4자리 연도가 ‘2017’이면 2017년 포인트 테이블에 적립 내용을 입력하고, ‘2018’이면 2018년 포인트 테이블에 적립 내용을 입력하고, ‘2019’이면 2019년 포인트 테이블에 적립 내용을 입력한다. 조건에 해당하지 않으면 2019년 포인트 테이블에 입력한다. ( 조건이 있기 때문에 입력할때 조건에 따라 다른 테이블에 입력되는 INSERT 구문을 사용한다)
INSERT ALL
WHEN SUBSTR(REG_DTTM,1,4) = '2017' THEN INTO TB_POINT_2017
WHEN SUBSTR(REG_DTTM,1,4) = '2018' THEN INTO TB_POINT_2018
WHEN SUBSTR(REG_DTTM,1,4) = '2019' THEN INTO TB_POINT_2019
ELSE INTO TB_POINT_2019
SELECT CUSTOMER_CD,
SEQ_NO,
POINT_MEMO,
POINT,
REG_DTTM
FROM TB_POINT
WHERE REG_DTTM >= '20170101000000';
SQL
복사
예제8. 포인트 테이블의 적립 내용을 연도별로 구분해 입력한 연도별 테이블을 검색한다.
SELECT * FROM TB_POINT_2017;
SELECT * FROM TB_POINT_2018;
SELECT * FROM TB_POINT_2019;
데이터베이스에 반영한다.
COMMIT;
SQL
복사
8.19 MERGE
MERGE 명령은 조건에 따라 지정한 테이블의 데이터를 입력하거나 수정한다. 조건식에 따라 데이터가 있다면 UPDATE 구문을 처리하고, 데이터가 없다면 INSERT 구문을 처리한다. (P.137)
MERGE INTO 수정할 테이블명
USING (테이블명| 뷰 | 서브쿼리)
ON 조건식
WHEN MATCHED THEN
UPDATE SET 필드명 1 = 값1, 필드명2 = 값2, ...
WHEN NOT MATCHED THEN
INSERT ... VALUES 구문;
실습할 내용은 고객 테이블과 추가고객 테이블을 대상으로 고객 테이블의 고객코드가 추가고객 테이블에 없다면 신규 고객 정보로 입력하고, 있다면 기존 고객 정보를 바꾸는 것이다.
예제1. 고객 테이블의 전체 내용을 검색한다.
SELECT *
FROM TB_CUSTOMER;
R
복사
예제2. 추가고객 테이블의 전체 내용을 검색한다.
SELECT *
FROM TB_ADD_CUSTOMER;
R
복사
박승대와 전미래 고객이 있는데 박승대는 이미 있는 데이터의 업데이트 사항이고 전미래 고객은 추가 데이터 사항이다.
박승대 고객은 전화번호 내용을 업데이트할 것이고 전미래 고객인 새롭게 입력할 것이다.
예제3. 추가고객 테이블의 고객코드와 같은 고객코드가 고객 테이블에 있다면 업데이트 할 내용을 반영하고 같은 고객코드가 없다면 새 내용을 추가한다.
MERGE INTO TB_CUSTOMER CU
USING TB_ADD_CUSTOMER NC
ON (CU.CUSTOMER_CD = NC.CUSTOMER_CD)
WHEN MATCHED THEN
UPDATE SET CU.CUSTOMER_NM = NC.CUSTOMER_NM,
CU.MW_FLG = NC.MW_FLG,
CU.BIRTH_DAY = NC.BIRTH_DAY,
CU.PHONE_NUMBER = NC.PHONE_NUMBER
WHEN NOT MATCHED THEN
INSERT (CU.CUSTOMER_CD, CU.CUSTOMER_NM, CU.MW_FLG,
CU.BIRTH_DAY, CU.PHONE_NUMBER, CU.EMAIL,
CU.TOTAL_POINT, CU.REG_DTTM)
VALUES (NC.CUSTOMER_CD, NC.CUSTOMER_NM, NC.MW_FLG,
NC.BIRTH_DAY, NC.PHONE_NUMBER,'',0,'20191015082030');
R
복사
예제4. 고객 테이블의 전체 내용을 다시 확인한다
SELECT *
FROM TB_CUSTOMER;
COMMIT;
R
복사
8. 20 DELETE
DELETE 명령은 조건에 만족하는 데이터(레코드)를 삭제한다. “DELETE FROM 테이블명 WHERE 조건식” 형식이다.
조건식으로 SELECT 명령에 사용하는 모든 구문을 사용할 수 있다.
DELETE
FROM 테이블명
WHERE 조건식;
참고로 DELETE FROM 테이블명; 하면 모든 레코드를 삭제한다.
실습할 내용은 성적 테이블에서 ‘나경숙’, ‘박승대’, ‘이아름’ 학생의 데이터를 삭제하는 것이다.
예제1. 성적 테이블의 전체 학생을 검색한다.
SELECT *
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO;
R
복사
예제2. 성적 테이블에서 ‘나경숙’, ‘박승대’, ‘이아름’ 학생을 삭제한다.
DELETE
FROM TB_GRADE
WHERE STUDENT_NM IN ('나경숙','박승대','이아름');
R
복사
예제3. 다시 성적 테이블을 확인해서 삭제되었는지 본다.
SELECT *
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO;
COMMIT; # 삭제한 내용 반영
R
복사
8.21 COMMIT
COMMIT 명령은 데이터베이스에 바뀐 내용을 반영한다. INSERT, UPDATE, DELETE 명령을 수행할 때 바로 데이터베이스에 반영되지 않고 임시 저장된다. COMMIT 명령은 이런 임시 저장된 내용을 데이터베이스에 반영하는 것이다.
실습할 내용은 성적 테이블에서 ‘독고승재’, ‘이도희’ 학생의 국어, 영어, 수학 점수의 합계와 평균을 바꾸고 데이터베이스에 반영하는 COMMIT 명령어 내용이다.
예제1. 성적 테이블의 전체 학생을 검색한다.
SELECT *
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO;
R
복사
이도희 학생과 독고승재 학생 모두 합계와 평균값이 없다.
예제2. 성적 테이블에서 ‘독고승재’, ‘이도희’ 학생의 국어, 영어, 수학 점수의 합계와 평균을 계산해 바꾼다.
UPDATE TB_GRADE
SET TOT = KOR + ENG + MAT,
AVG = ROUND((KOR + ENG + MAT)/3, 1)
WHERE STUDENT_NM IN ('독고승재', '이도희');
R
복사
예제3. 다시 성적 테이블의 바뀐 내용을 확인한다.
SELECT *
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO;
COMMIT;
R
복사
참고로 ROLLBACK 명령은 임시로 변경한 내용을 데이터베이스에 반영하지 않고 이전 상태로 돌리는 기능이다. COMMIT의 반대다.8.22 ROLLBACK
ROLLBACK 명령은 데이터베이스에 바뀐 내요을 취소하고 최종 COMMIT 상태로 돌아간다.
실습할 내용은 성적 테이블에서 B반 , D반 학생의 데이터를 삭제하고, 이를 데이터베이스에 반영하기 전의 상태로 돌아가는 ROLLBACK 명령어 실습이다.
예제1. 성적 테이블의 전체 학생을 검색한다.
SELECT *
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO;
SQL
복사
예제2. 성적 테이블에서 B반, D반 학생을 삭제한다.
DELETE
FROM TB_GRADE
WHERE CLASS_CD IN ('B','D');
SQL
복사
예제3. 삭제된 성적 테이블을 확인한다.
SELECT *
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO;
SQL
복사
예제4. 삭제한 내용을 취소하고 이전 상태로 복구한다.
ROLLBACK;
SQL
복사
롤백 명령은 현재 삭제한 데이터뿐만 아니라 복구 시점 이후에 새롭게 입력하거나 변경한 데이터도 이전 상태로 되돌린다.예제 5. 다시 성적테이블을 검색해서 B, D 반이 복구되었는지 확인한다.
SELECT *
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO;
SQL
복사
8.23 트랜잭션의 이해
트랜잭션은 데이터베이스 작업에서 하나로 묶을 수 있는 업무를 가리킨다. 일반적으로 데이터베이스를 조작하는 응용프로그램의 일괄 작업에서 연관 테이블 간에 맞지 않는 정보 입력을 방지할 목적으로 쓰인다.
트랜잭션의 실습을 진행한다. 첫 번째 실습은 고객 테이블의ㅣ 누적포인트에 포인트 테이블의 포인트 합을 반영하는 과정에서 부정합이 발생해 롤백하는 내용이다.
예제1. 고객 테이블의 고객코드가 ‘2017042’인 고객을 검색해 누적포인트값을 확인한다.
SELECT *
FROM TB_CUSTOMER
WHERE CUSTOMER_CD = '2017042';
SQL
복사
TOTAL_POINT 가 280300 이다.
예제2. 포인트 테이블을 전체 검색해 고객코드가 ‘2017042’인 고객의 포인트 값을 확인한다.
SELECT *
FROM TB_POINT
WHERE CUSTOMER_CD = '2017042';
SQL
복사
예제3. 포인트 테이블에 고객코드가 ‘2017042’인 고객의 포인트 적립 내용을 입력한다.
INSERT INTO TB_POINT VALUES('2017042',5,'드라이기 구매 포인트 적립',3200,'20190801234530');
SQL
복사
예제4. 포인트 테이블에서 고객코드가 ‘2017042’인 고객의 포인트 적립 내용을 재확인한다.
SELECT *
FROM TB_POINT
WHERE CUSTOMER_CD = '2017042';
SQL
복사
예제5. 고객 테이블에서 고객코드가 ‘2017042’인 고객의 누적포인트 값을 포인트 테이블의 포인트 합으로 바꾼다.
UPDATE TB_CUSTOMER CU
SET CU.TOTAL_POINT = (SELECT SUM(CP.POINT)
FROM TB_POINT CP
WHERE CP.CUSTOMER_CD = CU.CUSTOMER_CD
GROUP BY CP.CUSTOMER_CD)
WHERE CP.CUSTOMER_CD = '2017042';
SQL
복사
WHERE 구문에서 CU가 아닌 CP라 해서 오류가 발생했다. 따라서 ROLLBACK 의 필요성이 나타남
ROLLBACK;
SQL
복사
ROLLBACK을 했기 때문에 INSERT 부터 UPDATE까지 모두 하기 전으로 되돌린다.
예제 7. 다시 INSERT 를 진행하고 위의 과정에서 오류가 생긴 CP를 CU로 고쳐주는 과정까지 진행한다.
INSERT INTO TB_POINT VALUES('2017042',5,'드라이기 구매 포인트 적립',3200,'20190801234530');
UPDATE TB_CUSTOMER CU
SET CU.TOTAL_POINT = (SELECT SUM(CP.POINT)
FROM TB_POINT CP
WHERE CP.CUSTOMER_CD = CU.CUSTOMER_CD
GROUP BY CP.CUSTOMER_CD)
WHERE CU.CUSTOMER_CD = '2017042';
SQL
복사
예제8. 고객 테이블의 고객코드가 ‘2017042’인 고객을 검색해 누적포인트 값을 확인해보자.
SELECT *
FROM TB_CUSTOMER
WHERE CUTOMER_CD = '2017042';
SQL
복사
TOTAL_POINT 가 283500 이다.
예제9. 이제 데이터베이스에 변경 사항을 모두 반영한다.
COMMIT;
SQL
복사
8.24 서브쿼리의 이해
서브쿼리는 SELECT 구문 안에 새로운 SELECT 구문으로 구성하는 방식이다. 서브쿼리를 별명으로 설정해 상위 쿼리에서 ‘별명.필드명’으로 참조할 수 있다.
SELECT S1.필드명1, S1.필드명2,...
FROM ( SELECT 필드명1, 필드명2, ...
FROM 테이블명
WHERE 조건식) S1
WHERE 조건식;
SELECT 필드명1, 필드명2, ...
FROM 테이블명
WHERE 필드명 [IN | =] (SELECT 필드명
FROM 테이블명
WHERE 조건식);
서브쿼리는 복잡한 쿼리를 단계별로 구현하거나 현행 단계의 쿼리에서 특정 SQL 명령을 적용하지 못할 경우 상위 쿼리에서 처리할 수 있게 한다.이제 서브쿼리를 실습하겠다.
예제1. 포인트 테이블(TB_POINT)에서 고객코드(CUSTOMER_CD)가 ‘2019000’ 이후인 고객 중 포인트 합(SUM(POINT))이 10,000보다 큰 여성(MW_FLG=’W’)을 대상으로 고객코드, 고객명, 포인트 합계를 검색한다.
SELECT S2.CUTOMER_CD, (SELECT CT.CUSTOMER_NM
FROM TB_CUSTOMER CT
WHERE CT.CUSTOMER_CD = S2.CUSTOMER_CD) AS CUTOMER_NM,
S2.TOT_POINT
FROM (
SELECT S1.CUTOMER_CD, S1.TOT_POINT
FROM(SELECT CUSTOMER_CD, SUM(POINT) AS TOT_POINT
FROM TB_POINT
WHERE CUSTOMER_CD >= '2019000'
GROUP BY CUSTOMER_CD) S1
WHERE TOT_POINT > 10000
) S2
WHERE S2.CUSTOMER_CD IN (SELECT CUTOMER_CD
FROM TB_CUSTOMER
WHERE MW_FLG='W');
SQL
복사
DAY 2 QUIZ FROM PROGRAMMERS
•
IFNULL(컬럼명, NULL시 값); : 컬럼에 값이 존재하면 그 값을 출력하고 NULL이면 NULL시 값으로 대체한다. SELECT 문에 쓸 수 있다.
8.25 EXISTS
EXISTS 구문은 서브쿼리의 결과가 있으면 데이터를 검색한다. EXISTS 앞에 NOT을 붙이면 서브쿼리의 결과가 없으면 검색한다.
SELECT 검색필드
FROM 테이블
WHERE [NOT] EXISTS (SELECT [필드|의미없는 문자]
FROM 테이블
WHERE 조건식);
필드 대신 의미없는 문자를 쓸 경우에는 서브쿼리의 결과가 있거나 없음을 판단한다. 예를들어 “EXISTS (SELECT ‘A’ ...)” 구문은 검색 결과가 있는 조건이고, “NOT EXISTS (SELECT ‘B’...)”는 검색 결과가 없는 조건이다.예제1. 고객 포인트 테이블의 고객코드가 고객 테이블의 남성인 고객코드와 같으면 포인트 적립 내용을 검색한다.
SELECT *
FROM TB_POINT A
WHERE EXISTS (SELECT 'A'
FROM TB_CUSTOMER B
WHERE A.CUSTOMER_CD = B.CUSTOMER_CD
AND B.MW_FLG = 'M');
SQL
복사
8.26 검색 조건을 입력해서 SQL을 실행
SQL 구문에 ‘:검색조건명’ 을 입력하면 실행할 때 조건 입력창에 조건 값을 입력해 검색한다.
•
필드 = : 검색조건명
•
함수(:검색조건명)
WHERE CLASS_CD = :반코드 에서 ‘:반코드’ 에 ‘A’를 입력하면 SQL 실행 시 “WHERE CLASS_CD = ‘A’” 로 실행한다.
WHERE A필드 = TO_DATE(:생년월일) 에서 ‘:생년월일’에 ‘20190820’을 입력하면 SQL 실행 시 “WHERE A필드 = TO_DATE(’20190820’)” 로 실행한다.
실행하면 “바인드 입력” 화면이 뜨고 조건 값을 입력한 뒤 적용버튼을 눌러서 결과를 확인한다.
예제1. 성적 테이블에서 반코드를 ‘:v1’ 검색 조건명으로 설정해 ‘A’, ‘D’, ‘F’ 를 각각 입력하고 검색한다.
SELECT *
FROM TB_GRADE
WHERE CLASS_CD = :v1
ORDER BY CLASS_CD, STUDENT_NO;
SQL
복사
예제2. 고객 테이블에서 ‘:고객명’, ‘:성별’ 을 검색 조건명으로 설정해 ‘고객명’ 조건에 ‘이’를 입력하고, ‘성별’ 조건에 ‘W’를 입력해 검색한다.
SELECT *
FROM TB_CUSTOMER
WHERE CUSTOMER_NM LIKE '%' || :고객명 || '%'
AND MW_FLG = :성별;
SQL
복사
8.27 주석 처리
SQL 구문 안에 REM 명령어나 ‘—’, ‘/* */’ 문자로 설명이나 메모를 입력할 수 있다.
•
REM 설명
•
— 설명
•
/* 설명 */
REM :: 고객 정보 검색
— 고객코드
/* 누적포인트 계산 */
—> “:: 고객 정보 검색, 고객코드, 누적포인트 계산” 부분은 SQL에서 실행하지 않는다.
예제 1. SQL 구문에 “CUSTOMER_CD가 ‘2018000’ 이상인 고객을 검색” 이라는 주석을 넣고, 고객 테이블에서 고객코드가 ‘2018000’보다 큰 고객의 고객코드, 고객명, 성별, 생일, 전화번호, 이메일, 포인트, 등록일을 검색하면서 전화번호, 이메일은 주석으로 처리한 결과를 보인다.
REM ***************************
REM :: CUSTOMER_CD가 '2018000' 이상인 고객을 검색
REM ***************************
SELECT CUSTOMER_CD, --고객코드
CUSTOMER_NM, -- 고객명
MW_FLG, -- 성별
BIRTH_DAY, -- 생일
/*
PHONE_NUMBER, 전화
EMAIL, 이메일
*/
TOTAL_POINT, --포인트
REG_DTTM -- 등록일
FROM TB_CUSTOMER
WHERE CUSTOMER_CD >= '2018000';
SQL
복사
9장 집합 명령어
이번 장에서는 집합 명령어인 UNION, UNION ALL, INTERSECT, MINUS 명령을 실습한다.
•
UNION : 중복 허용하지 않는 합집합
•
UNION ALL : 중복 허용하는 합집합
•
INTERSECT : 교집합
•
MINUS : 차집합
성적 테이블에서 국어, 영어, 수학 점수가 90 이상인 학생 그룹 3개를 만들고, 3개 중 2개 그룹의 합집합,교집합, 차집합을 구한다.
집합 명령어를 사용할 때 테이블은 상관없지만 대응하는 필드의 개수와 속성은 같아야 한다. (예를들어, 날짜 값이 문자 값에 대응하거나 문자 값이 수치에 대응할 수 없다.)
예제1. 과목의 성적별로 학생 그룹을 만든다. A그룹은 국어 점수가 90 이상인 학생, B그룹은 영어 점수가 90 이상인 학생, C그룹은 수학 점수가 90 이상인 학생이다.
SELECT STUDENT_NM --A
FROM TB_GRADE
WHERE KOR >= 90
ORDER BY CLASS_CD,STUDENT_NO;
SELECT STUDENT_NM --B
FROM TB_GRADE
WHERE ENG >= 90
ORDER BY CLASS_CD,STUDENT_NO;
SELECT STUDENT_NM --C
FROM TB_GRADE
WHERE MAT >= 90
ORDER BY CLASS_CD,STUDENT_NO;
SQL
복사
9.1 UNION
합집합 할 각 테이블에 대한 SELECT 구문에서 위치별로 각기 대응하는 필드를 검색해 중복 없는 합집합을 만든다.
SELECT 필드명1, 필드명2, ...
FROM 테이블명1
UNION
SELECT 필드명1, 필드명2,...
FROM 테이블명2;
예제1. 국어 점수가 90점 이상인 A그룹과 영어 점수가 90점 이상인 B그룹의 학생명을 중복 없는 합집합으로 검색한다. (UNION)
SELECT STUDENT_NM
FROM TB_GRADE
WHERE KOR >= 90
UNION
SELECT STUDENT_NM
FROM TB_GRADE
WHERE ENG >= 90;
SQL
복사
9.2 UNION ALL
합집합 할 각 테이블에 대한 SELECT 구문에서 위치별로 각기 대응하는 필드를 검색해 중복을 허용하는 합집합을 만든다. 당연히 중복을 허용하니까 전체 레코드의 수는 합치는 테이블의 레코드 수의 합과 같다.
SELECT 필드명1, 필드명2, ...
FROM 테이블명1
UNION ALL
SELECT 필드명1, 필드명2,...
FROM 테이블명2;
예제1. 국어 점수가 90점 이상인 A그룹과 영어 점수가 90점 이상인 B그룹의 학생명을 중복을 허용한 합집합으로 검색한다.
SELECT STUDENT_NM
FROM TB_GRADE
WHERE KOR >= 90
UNION ALL
SELECT STUDENT_NM
FROM TB_GRADE
WHERE ENG >= 90;
SQL
복사
A그룹과 B그룹에 모두 있는 서유리와 한찬희 학생은 2건씩 중복으로 나왔다.
9.3 INTERSECT
합집합 할 각 테이블에 대한 SELECT 구문에서 위치별로 각기 대응하는 필드를 검색해 교집합을 만든다. 당연히 교집합이니까 각 테이블에 모두 속한 데이터를 보인다.
SELECT 필드명1, 필드명2, ...
FROM 테이블명1
INTERSECT
SELECT 필드명1, 필드명2,...
FROM 테이블명2;
예제1. 수학 점수가 90점 이상인 C그룹과 국어 점수가 90점 이상인 A그룹의 학생명을 교집합으로 검색한다.
SELECT STUDENT_NM
FROM TB_GRADE
WHERE MAT >= 90
INTERSECT
SELECT STUDENT_NM
FROM TB_GRADE
WHERE KOR >= 90;
SQL
복사
C그룹과 A그룹에 모두 속한 김진숙, 독고승재, 서유리 학생이 나온다.
9.4 MINUS
합집합 할 각 테이블에 대한 SELECT 구문에서 위치별로 각기 대응하는 필드를 검색해 차집합을 만든다. MINUS 앞에 오는 테이블에 유일하게 있는 데이터를 보인다. 필드값이 같으면 제외한다.
SELECT 필드명1, 필드명2, ...
FROM 테이블명1
MINUS
SELECT 필드명1, 필드명2,...
FROM 테이블명2;
—> ‘테이블명1 - 테이블명2’ 라고 생각하면 됨.
예제1. 수학 점수가 90점 이상인 C그룹의 학생명에서 국어 점수가 90점 이상인 A그룹의 학생명을 빼고 검색한다.
SELECT STUDENT_NM
FROM TB_GRADE
WHERE MAT >= 90
MINUS
SELECT STUDENT_NM
FROM TB_GRADE
WHERE KOR >= 90;
SQL
복사
10장 날짜 관련 함수
이번장에는 시스템 날짜 구하기, 날짜 형식 바꾸기, 특정일 기준의 날짜 계산 명령과 함수를 실습한다.
10.1 SYSDATE
데이터베이스가 설치된 서버나 개인 컴퓨터의 일시를 특정 형식으로 검색한다.
특정 형식이란 “YYYY/MM/DD HH:MI:SS” 와 같은 형식을 말한다.
더하기, 빼기 등의 연산이 가능하며, 연산할 때 기준은 1일이다. (즉, 1은 1일을 의미하고, 3은 3일, 0.5는 12시간을 의미한다.)
예시로 “SELECT SYSDATE FROM DUAL;” 의 결과는 ‘2022/04/01 16:45:50’ 형식의 날짜다.
•
SYSDATE + 수치 : 현재일시에서 수치 이후의 일시
•
SYSDATE - 수치 : 현재일시에서 수치 이전의 일시
예제1. SYSDATE와 CURRENT_DATE로 현재 일시를 검색한다.
SELECT SYSDATE, CURRENT_DATE
FROM DUAL;
SQL
복사
변수에 SYSDATE, CURRENT_DATE 가 생겼다. 값은 같다.
예제 2. 현재 일시를 기준으로 30일 전, 60일 이후 일시를 출력한다.
SELECT SYSDATE-30, SYSDATE+60
FROM DUAL;
SQL
복사
예제3. 현재 일시를 기준으로 3시간 이후의 일시를 출력한다.
1일을 가장 작은 단위인 초로 바꾸면 86400초다. —> 1초는 1/86400 다.
1분은 (1/86400)*60 이다.
1시간은 (1/86400)*60*60 이다.
1일은 (1/86400)*60*60*24
SELECT SYSDATE ,SYSDATE + ((1/86400)*60*60)*3 AS CHG_DTTM
FROM DUAL;
SQL
복사
10.2 SYSDATE - TO_CHAR()
TO_CHAR() 함수는 ‘날짜’를 ‘날짜형식’의 형식화된 문자로 바꾸는 기능이다. 매개변수인 날짜는 SYSDATE이거나 문자형 날짜를 변환해 사용하기도 한다.
날짜형식은 ‘YYYYMMDD’, ‘YYYY-MM-DD’, ‘YYYY-MM-DD HH-MI-SS’ 등이 있다.
예를들어 “TO_CHAR(SYSDATE, ‘YYYY/MM/DD’)” 의 결과는 현재 일자가 2022년 4월 1일인 경우 ‘2022/04/01’ 이 된다.
예제1. 현재 일시를 ‘YYYYMMDD’ 형식의 문자로 바꾼다.
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') AS TODAY
FROM DUAL;
SQL
복사
예제2. 현재 일시를 12시간 기준으로 ‘YYYY/MM/DD HH:MI:SS’ 형식의 문자로 바꾼다.
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS') AS TODAY_TM
FROM DUAL;
SQL
복사
예제3. 현재 일시를 24시간 기준으로 ‘YYYY/MM/DD HH:MI:SS’ 형식의 문자로 바꾼다.
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS TODAY_TM
FROM DUAL;
SQL
복사
10.3 SYSDATE - TO_DATE()
TO_DATE()함수는 매개변수로 입력한 ‘날짜형식 문자열’을 날짜로 바꾸는 기능이다. 날짜로 바뀌기 때문에 연산이 가능하다.
예를들어 “TO_DATE(’20190820’) + 1” 의 결과는 2019년 8월 21일이다.
예제1. ‘20190820’ 문자열을 날짜로 바꾼다.
SELECT TO_DATE('20190820') AS DT
FROM DUAL;
SQL
복사
예제2. ‘20190820140245’ 문자열을 날짜로 바꾼다.
SELECT TO_DATE('20190820140245') AS DTTM
FROM DUAL;
SQL
복사
예제3. 2019년 8월 20일을 기준으로 5일 후의 날짜를 구한다.
SELECT TO_CHAR(TO_DATE('20190820')+5,'YYYY-MM-DD') AS CHG_DT
FROM DUAL;
SQL
복사
예제4. 현재 일시에서 고객 테이블의 생년월일을 빼고 365일로 나눠 현재일 기준의 고객 나이를 구한다.
SELECT CUSTOMER_CD,
CUSTOMER_NM,
MW_FLG,
BIRTH_DAY,
ROUND((SYSDATE - TO_DATE(BIRTH_DAY))/365,1) AS YEAR_TERM
FROM TB_CUSTOMER
WHERE CUSTOMER_CD IN('2017042','2017053');
SQL
복사
10.4 NEXT_DAY()
NEXT_DAY()함수는 지정일 이후의 날짜를 구하는 기능이다. 매개변수로 입력한 ‘기준일자’이후 ‘요일문자열(또는 요일번호)’에 해당하는 요일의 날짜를 구한다.
‘기준일자’는 날짜 형식 문자열이거나 날짜다.
‘요일문자열’은 ‘일요일’,’월요일’,...’토요일’로 입력하고, ‘요일번호’는 1,2,3,...,7 로 입력한다.
예를들어 “NEXT_DAY(SYSDATE, ‘일요일’)” 과 “NEXT_DAY(SYSDATE, 1)” 은 현재일 다음의 일요일에 대한 날짜를 구하는 것으로 같다.
예제1. 2019년 8월 20일 이후 금요일 날짜를 구한다.
SELECT TO_CHAR(NEXT_DAY('20190820','금'),'YYYY-MM-DD') AS NEXT_DAY
FROM DUAL;
SQL
복사
또는
SELECT TO_CHAR(NEXT_DAY('20190820',6),'YYYY-MM-DD') AS NEXT_DAY
FROM DUAL;
SQL
복사
예제2. 2019년 8월 20일 이후 월요일 날짜를 구한다.
SELECT TO_CHAR(NEXT_DAY('20190820',2),'YYYY-MM-DD') AS NEXT_DAY
FROM DUAL;
--또는
SELECT TO_CHAR(NEXT_DAY('20190820','월'),'YYYY-MM-DD') AS NEXT_DAY
FROM DUAL;
SQL
복사
10.5 LAST_DAY()
LAST_DAY()함수는 특정 월의 마지막 날짜를 구하는 기능이다. 매개변수로 입력한 ‘기준일자’가 포함된 월의 마지막 일시를 구한다.
‘기준일자’는 날짜형식 문자열이거나 날짜다.
예를들어 LAST_DAY(’20190520’) 는 2019년 5월 31일을 구한다.
예제 1. 2019년 5월 20일, 2019년 9월 3일, 현재 일시를 포함하는 각 월의 마지막 날짜를 구한다.
SELECT SYSDATE AS CUR_DATE, -- 현재날짜
LAST_DAY('20190520') AS "5_MONTH_LAST_DAY",
LAST_DAY('20190903') AS "9_MONTH_LAST_DAY",
LAST_DAY(SYSDATE) AS CUR_LAST_DAY
FROM DUAL;
SQL
복사
예제2. 2019년도 각 월의 마지막 날짜를 구한다.
SELECT SUBSTR(S1.DATE2019,1,6) AS MONTH2019,
TO_CHAR(LAST_DAY(TO_DATE(S1.DATE2019)),'DD') AS LAST_DAY
FROM (
SELECT '20190101' AS DATE2019 FROM DUAL UNION ALL
SELECT '20190201' AS DATE2019 FROM DUAL UNION ALL
SELECT '20190301' AS DATE2019 FROM DUAL UNION ALL
SELECT '20190401' AS DATE2019 FROM DUAL UNION ALL
SELECT '20190501' AS DATE2019 FROM DUAL UNION ALL
SELECT '20190601' AS DATE2019 FROM DUAL UNION ALL
SELECT '20190701' AS DATE2019 FROM DUAL UNION ALL
SELECT '20190801' AS DATE2019 FROM DUAL UNION ALL
SELECT '20190901' AS DATE2019 FROM DUAL UNION ALL
SELECT '20191001' AS DATE2019 FROM DUAL UNION ALL
SELECT '20191101' AS DATE2019 FROM DUAL UNION ALL
SELECT '20191201' AS DATE2019 FROM DUAL
)S1;
SQL
복사
10.6 ADD_MONTHS()
ADD_MONTHS() 함수는 지정일에 개월을 더해주는 함수다. 매개변수로 입력한 ‘기준일자’에서 ‘개월수’를 더한 날짜를 구한다.
ADD_MONTHS(기준일자, 개월수) : 결과값은 날짜다
개월수에 1이면 1개월 후, -2이면 2개월 전이다.
기준일자에는 ‘날짜 형식의 문자열’ 이거나 날짜다.
예를들어 ADD_MONTHS(’20190820’ , 2) 는 2019년 8월 20일에서 2개월 후의 날짜를 구해준다.
~ MySQL ~
•
DATE_ADD(now(), INTERVAL 1 DAY) : 다음날짜 → ORACLE : SYSDATE + 1
•
DATE_ADD(now(), INTERVAL 1 WEEK) : 일주일 뒤 날짜 → ORACLE : SYSDATE + 7
•
DATE_ADD(now(), INTERVAL 1 MONTH) : 한달 뒤 날짜 → ORACLE : ADD_MONTHS(기준일자, 1)
•
DATE_ADD(now(), INTERVAL 1 YEER) : 1년 뒤 날짜 → ORACLE : ADD_MONTHS(기준일자, 12)
예제1. 2019년 8월 20일 기준으로 3개월 전, 1개월 전, 1개월 후, 3개월 후의 날짜를 구한다.
SELECT ADD_MONTHS('20190820',-3) AS "3_BF_MON_DAY",
ADD_MONTHS('20190820',-1) AS "1_BF_MON_DAY",
ADD_MONTHS('20190820',1) AS "1_AFT_MON_DAY",
ADD_MONTHS('20190820',3) AS "3_AFT_MON_DAY"
FROM DUAL;
SQL
복사
예제2. 현재 일시를 기준으로 2개월 전과 2개월 후의 날짜를 ‘YYYY-MM-DD’ 형식으로 구한다.
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS CUR_DATE,
TO_CHAR(ADD_MONTHS(SYSDATE,-2),'YYYY-MM-DD') AS "2_BF_MON_DAY",
TO_CHAR(ADD_MONTHS(SYSDATE,2),'YYYY-MM-DD') AS "2_AFT_MON_DAY"
FROM DUAL;
SQL
복사
10.7 MONTHS_BETWEEN()
MONTHS_BETWEEN() 함수는 두 날짜의 개월 차이를 구하는 기능이다.
매개변수인 ‘기준일자’ 에서 ‘비교일자’를 뺀 개월수를 구한다.
‘기준일자’ 와 ‘비교일자’ 모두 날짜 형식의 문자열이나 날짜를 입력한다.
만약 결과가 1이면 1개월, 3.2이면 3.2 개월이다.
만약, 기준일자보다 비교일자가 더 크면 음수값을 반환한다.
예를들어 MONTHS_BETWEEN(SYSDATE, TO_DATE(’20200101’,’YYYYMMDD’)) 의 결과는 현재일부터 2020년 1월 1일과의 개월 차이다.
예제1. 2019년 10월 5일과 2019년 8월 20일, 2019년 7월 3일과 2019년 12월 8일, 현재 일시와 2019년 7월 1일의 개월 차이를 구한다.
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CUR_DTTM,
ROUND(MONTHS_BETWEEN('20191005','20190820'),2) AS MONTH_TERM_1,
ROUND(MONTHS_BETWEEN('20190703','20191208'),2) AS MONTH_TERM_2,
ROUND(MONTHS_BETWEEN(SYSDATE,'20190701'),2) AS MONTH_TERM_3
FROM DUAL;
SQL
복사
예제2. 고객코드가 ‘2019001’인 고객의 최근 적립 사항을 확인하기 위해 포인트 테이블에 고객 포인트 적립 내역을 등록한 일시를 현재 일시와 비교해 개월 차이를 구한다.
SELECT CUSTOMER_CD,
SEQ_NO,
POINT_MEMO,
POINT,
REG_DTTM,
TO_CHAR(SYSDATE,'YYYY-MM-DD') AS 현재일자,
ROUND(MONTHS_BETWEEN(SYSDATE,reg_dttm),1) AS 개월차
FROM TB_POINT
WHERE CUSTOMER_CD = '2019001';
SQL
복사
DAY 3 QUIZ FROM PROGRAMMERS
DATE 의 형식을 변환하는 함수는 DATE_FORMAT(DATE, 형식) 이다.
형식에는 %Y (4자리 연도) , %y (2자리 연도), %m(월), %d(일), %H(24시간), %h(12시간), %i, %s 가 있다.
예시로는 DATE_FORMAT(DATETIME, ‘%Y-%m-%d’) AS 날짜
DAY 4 QUIZ FROM HACKERRANK
SELECT DISTINCT CITY
FROM STATION
WHERE MOD(ID,2) = 0; —나머지 연산자 MOD()
WEATHER OBSERVATION STATION 5 다시
정규표현식 공부
항상 앞에는 REGEXP_EXP 를 언급해줘야 함.
패턴 구분자 중에 ^[패턴] 은 패턴으로 시작하는 문자열을 의미하고 [패턴]$ 은 패턴으로 끝나는 문자열을 의미.
[ ] 은 [ ] 안에 나열된 패턴에 해당하는 문자열을 찾음.
WEATHER OBSERVATION STATION 8 아래 다시
SELECT DISTINCT CITY
FROM STATION
WHERE REGEXP_LIKE(LOWER(CITY),'^[aeiou]')
INTERSECT
SELECT DISTINCT CITY
FROM STATION
WHERE REGEXP_LIKE(LOWER(CITY),'[aeiou]$');
WEATEHER OBSERVATION STATION 9 SOLUTION
SELECT DISTINCT CITY
FROM STATION
WHERE NOT REGEXP_LIKE(CITY,’^[AEIOU]’);
HIGHER THAN 75 MARKS
1.
문자열 뒤에서부터 자르기 ( SUBSTR(해당문자열, -(몇자리)) )
11장 수치 관련 함수
11.1 ABS()
ABS() 함수는 수치의 절댓값을 구하는 기능이다.
•
ABS(수치)
SELECT ABS(34),
ABS(12.34),
ABS(0),
ABS(-42)
FROM DUAL;
SQL
복사
11.2 MOD()
MOD()함수는 나머지값을 구하는 기능이다. “MOD(10,3)”의 결과는 10을 3으로 나눈 나머지 값이다.
SELECT MOD(4,2),
MOD(9,5),
MOD(12,0),
MOD(-13,4),
MOD(-11,-3)
FROM DUAL;
SQL
복사
11.3 SQRT()
SQRT() 함수는 제곱근을 구하는 기능이다. 매개변수로 입력한 수치의 제곱근을 구한다.
SELECT SQRT(2),
SQRT(9),
SQRT(14),
SQRT(16)
FROM DUAL;
SQL
복사
11.4 CEIL()
CEIL() 함수는 수치의 올림값을 구하는 기능으로 입력한 수치를 양의 방향으로 올린값을 구한다.
SELECT CEIL(5.1),
CEIL(12.8),
CEIL(-5.1),
CEIL(-12.8)
FROM DUAL;
SQL
복사
11.5 FLOOR()
FLOOR() 함수는 수치의 내림값을 구하는 기능으로 입력한 수치를 음의 방향으로 내린값을 구한다.
SELECT FLOOR(5.1),
FLOOR(12.8),
FLOOR(-5.1),
FLOOR(-12.8)
FROM DUAL;
SQL
복사
11.6 ROUND()
ROUND() 함수는 반올림값을 구하는 기능으로 입력한 수치를 반올림해 자릿수만큼의 값을 구한다. 자릿수가 음수면 1단위,10단위,100단위에서 반올림하는 것이다.
SELECT ROUND(5.1345,2),
ROUND(12.8763, 2),
ROUND(-5.1345, 2),
ROUND(-12.8763, 2)
FROM DUAL;
SQL
복사
SELECT ROUND(1234.5678, -1),
ROUND(1234.5678, -2)
FROM DUAL;
SQL
복사
11.7 TRUNC()
TRUNC()함수는 버림값을 구하는 기능으로, 입력한 수치를 버림해 자릿수만큼의 값을 구한다. 예를들어 “TRUNC(12.578, 2)”의 결과는 소수점 3자리에서 버림한 12.57이다.
SELECT TRUNC(5.1345,2),
TRUNC(12.8763, 2),
TRUNC(-5.1345, 2),
TRUNC(-12.8763, 2)
FROM DUAL;
SQL
복사
SELECT TRUNC(1234.5678, -1),
TRUNC(1234.5678,-2)
FROM DUAL;
SQL
복사
SELECT TRUNC(SYSDATE,'YYYY') AS DATE1,
TRUNC(SYSDATE,'MM') AS DATE2,
TRUNC(SYSDATE, 'DD') AS DATE3
FROM DUAL;
SQL
복사
날짜를 버린 자리는 초기화 상태로 남는다.
11.8 POWER()
POWER() 함수는 승수값을 구하는 기능으로, 입력한 수치의 승수값을 구한다. 예를들어 “POWER(2,3)” 의 값은 2의 3승인 8이다.
SELECT POWER(4,2),
POWER(5,3),
POWER(-6,3),
POWER(-6,4),
POWER(2,-3),
POWER(2,-4)
FROM DUAL;
SQL
복사
12장 문자열 관련 함수
문자열 조작을 위한 함수를 실습하겠다.
12.1 UPPER()
UPPER() 함수는 영문을 대문자로 바꾸는 기능이다. 매개변수로 입력한 문자열을 대문자로 바꾼다.
한글이나 수치형 문자열은 바꾸지 않는다.
SELECT UPPER('1Day SQL Example : 123 : 오라클')
FROM DUAL;
SQL
복사
예제2. 기타정보 테이블에서 고객영문명을 대문자로 바꾼다.
SELECT UPPER(CUSTOMER_ENG_NM) AS UPPER_CUSTOMER_ENG_NM,
CUSTOMER_CD,
CUSTOMER_ENG_NM,
ITEM_LIST
FROM TB_ETC_INFO;
SQL
복사
12.2 LOWER()
LOWER()함수는 영문을 소문자로 바꾸는 기능이다. 매개변수로 입력한 문자열을 소문자로 바꾼다.
마찬가지로 수치형이나 한글은 바꾸지 않는다.
SELECT LOWER('1Day SQL Example : 123 : 오라클')
FROM DUAL;
SQL
복사
예제2. 기타정보 테이블에서 고객영문명을 소문자로 바꾼다.
SELECT LOWER(CUSTOMER_ENG_NM) AS LOWER_CUSTOMER_ENG_NM,
CUSTOMER_CD,
CUSTOMER_ENG_NM,
ITEM_LIST
FROM TB_ETC_INFO;
SQL
복사
12.3 SUBSTR(), SUBSTRB()
SUBSTR()과 SUBSTRB()는 문자열의 일부를 구하는 기능으로, 매개변수로 입력한 문자열의 시작위치부터 취득개수만큼의 문자를 구한다. 취득개수가 생략되면 마지막 문자까지 구한다.
SUBSTR과 SUBSTRB의 차이는 한글(또는 전각문자)에서 한 글자의 자릿수를 SUBSTR은 1자리로, SUBSTRB는 2자리로 인식한다는 것이다.
예제1. ‘1Day SQL Example : 123 : 오라클’ 문자열에서 처음부터 8개 문자를 substr()함수로 구한다.
SELECT SUBSTR('1Day SQL Example : 123 : 오라클', 1, 8)
FROM DUAL;
SQL
복사
예제2. ‘1Day SQL Example : 123 : 오라클’ 문자열에서 20번째 위치부터 3개 문자를 substr()함수로 구한다.
SELECT SUBSTR('1Day SQL Example : 123 : 오라클',20,3)
FROM DUAL;
SQL
복사
예제3. ‘1Day SQL Example : 123 : 오라클’ 문자열에서 26번째 위치부터 3개 문자를 SUBSTR()함수로 구한다.
SELECT SUBSTR('1Day SQL Example : 123 : 오라클',26,3),
SUBSTR('1Day SQL Example : 123 : 오라클',26)
FROM DUAL;
SQL
복사
예제4. ‘1Day SQL Example : 123 : 오라클’ 문자열에서 26번째 위치부터 6개 문자와 3개 문자를 각각SUBSTRB()함수로 구한다.
SELECT SUBSTRB('1Day SQL Example : 123 : 오라클',26,6) AS TEXT1,
SUBSTRB('1Day SQL Example : 123 : 오라클',26,3) AS TEXT2,
SUBSTRB('1Day SQL Example : 123 : 오라클',26) AS TEXT3
FROM DUAL;
SQL
복사
예제5. ‘20190820’ 문자열을 ‘2019-08-20’ 으로 바꾼다.(SUBSTR과 || 사용)
SELECT SUBSTR(S1.DT,1,4) || '-' ||
SUBSTR(S1.DT,5,2) || '-' ||
SUBSTR(S1.DT,7) AS DT_FMT
FROM (SELECT '20190820' AS DT FROM DUAL) S1;
SQL
복사
예제6. 고객 테이블에서 전화번호 4번째 위치의 문자가 ‘-’가 아닌 고객을 검색한다.
SELECT *
FROM TB_CUSTOMER
WHERE SUBSTR(PHONE_NUMBER,4,1) <> '-';
SQL
복사
12.4 LENGTH(), LENGTHB()
LENGTH()와 LENGTHB()는 문자열의 길이를 구하는 기능이다. 매개변수로 입력한 문자열의 길이를 구한다. NULL값이면 0이고, 공백(’ ‘)은 1이다.
SUBSTR 과 SUBSTRB가 그랬듯이 LENGTH와 LENGTHB의 차이는 한글(또는 전각문자) 1글자의 길이를 1자리로 하는 것이 LENGTH이고 2자리로 보는 것이 LENGTHB라는 것이다.
예제1. ‘1Day SQL’, ‘ SQL ‘, ‘오라클’, ‘오라클SQL’ 문자열의 길이를 LENGTH() 함수로 구한다.
SELECT LENGTH('1Day SQL'),
LENGTH(' SQL '),
LENGTH('오라클'),
LENGTH('오라클 SQL')
FROM DUAL;
SQL
복사
예제2. ‘1Day SQL’, ‘ SQL ‘, ‘오라클’, ‘오라클SQL’ 문자열의 길이를 LENGTHB()함수로 구한다.
SELECT LENGTHB('1Day SQL'),
LENGTHB(' SQL '),
LENGTHB('오라클'),
LENGTHB('오라클 SQL')
FROM DUAL;
SQL
복사
예제3. ‘1Day SQL Example : 123 : 오라클’ 문자열의 길이를 LENGTH() 와 LENGTHB() 함수로 구한다.
SELECT LENGTH('1Day SQL : 123 : 오라클'),
LENGTHB('1Day SQL : 123 : 오라클')
FROM DUAL;
SQL
복사
예제4. 기타정보 테이블에서 영문고객명의 길이를 LENGTH()함수로 검색한다.
SELECT LENGTH(CUSTOMER_ENG_NM) AS 길이,
CUSTOMER_ENG_NM,
CUSTOMER_CD,
ITEM_LIST
FROM TB_ETC_INFO;
SQL
복사
12.5 RPAD()
RPAD() 함수는 일정 크기의 왼쪽 정렬한 문자열을 만드는 기능으로, 매개변수로 입력한 개수만큼 공백을 만들어 문자열을 왼쪽에 위치시키고 나머지는 채울문자로 채운다. ‘채울문자’를 생략하면 공백으로 채운다. 예를들어 RPAD(’AB’,4,’*’) 의 결과는 왼쪽부터 AB를 배치하고 나머지 2 공간에는 ‘*’ 으로 채운 AB** 가 된다.
예제1. ‘1Day SQL’을 14자리 공백의 왼쪽에 위치시키고, 나머지 부분은 ‘*’ 문자로 채운다.
SELECT RPAD('1Day SQL',14,'*')
FROM DUAL;
SQL
복사
예제2. 기타정보 테이블에서 영문고객명을 15자리 문자열의 왼쪽에 위치시키고 나머지 오른쪽 부분은 ‘_’문자로 채운다.
SELECT RPAD(CUSTOMER_ENG_NM,15,'_') AS CUSTOMER_ENG_NM,
CUSTOMER_CD,
ITEM_LIST
FROM TB_ETC_INFO;
SQL
복사
12.6 LPAD()
LPAD()함수는 일정 크기의 오른쪽 정렬한 문자열을 만드는 기능으로, 매개변수로 입력한 ‘개수’만큼 공백을 만들어 ‘문자열’을 오른쪽에 위치시키고 나머지는 ‘채울문자’로 채운다.
예제1. ‘1Day SQL’을 14자리 공백의 오른쪽에 위치시키고, 나머지 부분은 ‘*’ 문자로 채운다.
SELECT LPAD('1Day SQL',14,'*')
FROM DUAL;
SQL
복사
예제2. 기타정보 테이블에서 영문고객명을 15자리 문자열의 오른쪽에 위치시키고 나머지 왼쪽 부분은 공백 문자로 채운다.
SELECT LPAD(CUSTOMER_ENG_NM,15) AS CUSTOMER_ENG_NM,
CUSTOMER_CD,
ITEM_LIST
FROM TB_ETC_INFO;
SQL
복사
12.7 RTRIM()
RTRIM() 함수는 문자열의 오른쪽 공백을 없애는 기능이다.
예제1. ‘ 1Day SQL ‘ 문자열의 오른쪽 공백을 없애고, 문자열과 길이를 구한다.
SELECT LENGTH(' 1Day SQL '),
RTRIM(' 1Day SQL '),
LENGTH(RTRIM(' 1Day SQL ')) AS 길이
FROM DUAL;
SQL
복사
12.8 LTRIM()
LTRIM() 함수는 문자열의 왼쪽 공백을 없애는 기능이다.
예제1. ‘ 1Day SQL ‘ 문자열의 왼쪽 공백을 없애고, 문자열과 길이를 구한다.
SELECT LENGTH(' 1Day SQL '),
LTRIM(' 1Day SQL '),
LENGTH(LTRIM(' 1Day SQL ')) AS 길이
FROM DUAL;
SQL
복사
12.9 TRIM()
TRIM() 함수는 문자열의 양쪽 공백을 없애는 기능이다.
예제1. ‘ 1Day SQL ‘ 문자열의 왼쪽 공백을 없애고, 문자열과 길이를 구한다.
SELECT LENGTH(' 1Day SQL '),
TRIM(' 1Day SQL '),
LENGTH(TRIM(' 1Day SQL ')) AS 길이
FROM DUAL;
SQL
복사
12.10 INITCAP()
INITCAP() 함수는 영단어의 첫 문자를 대문자로 바꾸고 나머지는 소문자로 바꾸는 기능으로 각 단어의 구분은 공백으로 한다.
물론 첫문자가 영문자일 때만 해당된다.SELECT INITCAP('oracle sql')
FROM DUAL;
SQL
복사
결과는 Oracle Sql 이다.
예제1. ‘1day sql example : 123 : 오라클’ 문자열에서 각 단어의 첫 번째 문자를 대문자로 바꾼다.
SELECT INITCAP('1day sql example : 123 : 오라클')
FROM DUAL;
SQL
복사
예제2. 기타정보 테이블에서 영문고객명의 단어별 첫 번째 문자를 대문자로 바꿔 검색한다.
SELECT INITCAP(CUSTOMER_ENG_NM),
CUSTOMER_ENG_NM,
CUSTOMER_CD,
ITEM_LIST
FROM TB_ETC_INFO;
SQL
복사
12.11 CONCAT()
CONCAT() 함수는 값을 연결하는 기능으로 문자열, 수치, 날짜 등이 쓰인다. ‘||’ 는 여러 값을 연결하는 문자열을 만든다. 결과는 문자다.
•
CONCAT(값1,값2)
•
값1 || 값2
예제1. ‘1Day SQL’ 문자열과 ‘ 실습’ 문자열을 연결한다.
SELECT CONCAT('1Day SQL',' 실습')
FROM DUAL;
--또는
SELECT '1Day SQL' || ' 실습'
FROM DUAL;
SQL
복사
예제2. ‘1Day SQL’ 문자열, ‘Example’ 문자열, 수치 123을 연결한다.
SELECT CONCAT('1Day SQL','Example'),
'1Day SQL' || 'Example',
'1Day SQL' || 'Example' || 123
FROM DUAL;
SQL
복사
CONCAT() 함수는 2개의 값을 연결하고, 3개 이상의 값을 연결하려면 ‘||’ 기호를 쓴다. 12.12 INSTR(), INSTRB()
INSTR(), INSTRB() 함수는 ‘문자열’에서 ‘찾을문자열’의 위치를 구하는데, ‘시작위치’가 있으면 ‘시작위치’부터 검색하고, 없으면 첫 문자부터 검색한다. ‘검색차수’가 있으면 ‘검색차수’번째 순서에 있는 ‘찾는문자열’의 위치를 구한다. INSTR과 INSTRB의 차이는 1글자의 길이를 1자리로, 2자리로 구하는 차이다.
•
INSTR( 문자열, 찾을문자열[, 시작위치[, 검색차수]])
•
INSTRB( 문자열, 찾을문자열[, 시작위치[, 검색차수]])
예제1. ‘1Day 오라클 SQL’ 문자열에서 ‘오라클’과 ‘SQL’ 문자열의 위치를 INSTR() 함수로 구한다.
SELECT INSTR('1Day 오라클 SQL','오라클') AS LOC1,
INSTR('1Day 오라클 SQL', 'SQL') AS LOC2
FROM DUAL;
SQL
복사
예제2. ‘1Day 오라클 SQL’ 문자열에서 ‘오라클’과 ‘SQL’ 문자열의 위치를 INSTRB() 함수로 구한다.
SELECT INSTRB('1Day 오라클 SQL','오라클') AS LOC1,
INSTRB('1Day 오라클 SQL','SQL') AS LOC2
FROM DUAL;
SQL
복사
예제3. ‘1Day SQL Example : 123 : 오라클’ 문자열에서 2번째 ‘:’ 문자의 위치를 INSTR() 함수로 구한다.
SELECT INSTR('1Day SQL Example : 123 : 오라클', ':',
INSTR('1Day SQL Example : 123 : 오라클',':')+1) AS LOC
FROM DUAL;
SQL
복사
12.13 REPLACE()
REPLACE() 함수는 ‘문자열’의 ‘대상문자’를 ‘교체문자’로 바꾼다.
예제1. ‘1Day SQL Example’ 문자열에서 ‘Example’을 ‘실습’으로 바꾼다.
SELECT REPLACE('1Day SQL Example','Example','실습')
FROM DUAL;
SQL
복사
예제2. ‘2019-08-20’ 문자열에서 ‘-’ 문자를 공백으로 바꾼다.
SELECT REPLACE('2019-08-20','-','') AS CHG_DT
FROM DUAL;
SQL
복사
13. 계층형 쿼리(start with ~ connect by)
계층형 쿼리는 한 테이블에 담겨 있는 여러 레코드들이 서로 상하 관계(부모, 자식)를 이루며 존재할 때, 이 관계에 따라 레코드를 상하위한 구조로 가져올 때 사용되는 sql이다. 예를들면 회사 조직도같은 경우다.
이러한 구조의 테이블을 상 하위 구조의 일목요연한 레코드로 select할 때 사용하는 것이 오라클의 START WITH ~ CONNECT BY구문이다.
13-1. START WITH
계층 질의의 루트(부모행)로 사용될 행을 지정한다. 즉, 어떤 레코드를 최상위 레코드로 잡을지 지정한다.
•
서브 쿼리를 사용할 수 있다.
예시를 들기 위해서 위의 회사 조직도 데이터를 직접 만들어본다.
CREATE TABLE TB_CMM_DEPT
(
DEPT_CD VARCHAR2(8) NOT NULL PRIMARY KEY,
PAR_DEPT_CD VARCHAR2(8),
DEPT_NM VARCHAR2(50),
USE_YN CHAR(1),
REG_DTM DATE,
REG_USER VARCHAR2(30),
MOD_DTM DATE,
MOD_USER VARCHAR2(30)
);
-- 만약 데이터 삽입된 내용이 다음과 같다고 하면
DEPT_CD PAR_DEPT_CD DEPT_NM USE_YN
--------- ------------- ---------- -----------
DEPT_001 NULL A회사 Y
DEPT_002 DEPT_001 경영지원부 Y
DEPT_003 DEPT_001 영업본부 Y
DEPT_004 DEPT_002 회계팀 Y
DEPT_005 DEPT_002 인사팀 Y
DEPT_006 DEPT_002 재무팀 Y
DEPT_007 DEPT_003 국내영업팀 Y
DEPT_008 DEPT_003 해외영업팀 Y
DEPT_009 DEPT_003 영업기획팀 Y
DEPT_010 DEPT_003 영업지원팀 Y
SQL
복사
A 회사(DEPT_001) 가 최상위 레코드인 경우SELECT *
FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD IS NULL;
SQL
복사
경영지원부(DEPT_002) 가 최상위 레코드인 경우SELECT *
FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD = 'DEPT_002';
SQL
복사
서브 쿼리를 사용할 경우(부서명이 ‘경영지원부’ 부서코드를 조건에 넣음)SELECT *
FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD = (SELECT DEPT_CD
FROM TB_CMM_DEPT
WHERE TB_DEPT_NM = '경영지원부');
SQL
복사
13-1. CONNECT BY
이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정할 수 있다.
PRIOR 연산자를 함께 사용해서 계층구조로 표현할 수 있다.
•
CONNECT BY PRIOR 자식칼럼 = 부모칼럼 : 부모에서 자식으로 트리 구성(Top to Down)
•
CONNECT BY PRIOR 부모칼럼 = 자식칼럼 : 자식에서 부모로 트리 구성(Bottom to Top)
•
CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 사용해서 무한 루프 방지
•
서브쿼리를 사용할 수 없다.
예를들어, 최상위 행(부모행) 으로 DEPT_001을 선택했다고 가정해보자.
SELECT *
FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD = 'DEPT_001'; -- 최상위 행 선택 , 총 2개 --> 경영지원부, 영업본부
SQL
복사
부모행 방향으로 전개할 것인가?(Bottom to top)
자식행 방향으로 전개할 것인가?(top to down)
방향은 ‘prior’가 어디에 지정되어 있느냐에 따라 달라지는데, 다음과 같다. 상식으로 이해하면 된다.
SELECT DEPT_CD,
PAR_DEPT_CD,
DEPT_NM
FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD = 'DEPT_001'
CONNECT BY PRIOR PAR_DEPT_CD = DEPT_CD; -- 부모행 방향으로 전개되는 BOTTOM TO UP 방식이다.
-- 결과
DEPT_CD PAR_DEPT_CD DEPT_NM
--------- ----------- ---------
DEPT_002 DEPT_001 경영지원부
DEPT_001 A회사
DEPT_003 DEPT_001 영업본부
DEPT_001 A회사
SELECT DEPT_CD,
PAR_DEPT_CD,
DEPT_NM
FROM TB_CMM_DEPT
START WITH PAR_DEPT_CD = 'DEPT_001'
CONNECT BY PRIOR DEPT_CD = PAR_DEPT_CD; -- 자식행 방향으로 전개되는 TOP TO DOWN 방식이다.
-- 결과
DEPT_CD PAR_DEPT_CD DEPT_NM
--------- ---------- ------------
DEPT_002 DEPT_001 경영지원부
DEPT_004 DEPT_002 회계팀
DEPT_005 DEPT_002 인사팀
DEPT_006 DEPT_002 재무팀
DEPT_003 DEPT_001 영업본부
DEPT_007 DEPT_003 국내영업팀
DEPT_008 DEPT_003 해외영업팀
DEPT_009 DEPT_003 영업기획팀
DEPT_010 DEPT_003 영업지원팀
SQL
복사
13-2. LEVEL Pseudocolumn
LEVEL은 계층구조 쿼리에서 수행 결과의 Depth를 표현하는 의사칼럼이다.
의사칼럼이란, ‘가짜 칼럼’을 의미한다. 즉, 실제 정의된 칼럼은 아니고 단순히 레코드들이 최상의 레코드로부터 몇단계 깊이에 있는지를 참고하는 용도로만 사용된다.
SELECT LEVEL AS LVL,
TCD.DEPT_CD,
TCD.PAR_DEPT_CD,
TCD.DEPT_NM
FROM TB_CMM_DEPT TCD
START WITH TCD.PAR_DEPT_CD = 'DEPT_001'
CONNECT BY TCD.PAR_DEPT_CD = PRIOR TCD.DEPT_CD;
-- 결과
LVL DEPT_CD PAR_DEPT_CD DEPT_NM
--- --------- ----------- -------------
1 DEPT_002 DEPT_001 경영지원부
2 DEPT_004 DEPT_002 회계팀
2 DEPT_005 DEPT_002 인사팀
2 DEPT_006 DEPT_002 재무팀
1 DEPT_003 DEPT_001 영업본부
2 DEPT_007 DEPT_003 국내영업팀
2 DEPT_008 DEPT_003 해외영업팀
2 DEPT_009 DEPT_003 영업기획팀
2 DEPT_010 DEPT_003 영업지원팀
SQL
복사
13-3. ORDER SIBLINGS BY + 칼럼명
계층구조 쿼리에서 편하게 정렬 작업을 할 수 있다.
단순히 ORDER BY를 사용하는 것보다 가독성이 좋게 정렬해준다.
-- 1. ORDER BY 사용시
SELECT LEVEL AS LVL,
TCD.DEPT_CD,
TCD.PAR_DEPT_CD,
TCD.DEPT_NM
FROM TB_CMM_DEPT TCD
START WITH TCD.PAR_DEPT_CD = 'DEPT_001'
CONNECT BY TCD.PAR_DEPT_CD = PRIOR TCD.DEPT_CD
ORDER BY TCD.DEPT_CD;
-- 1. ORDER BY 결과
LVL DEPT_CD PAR_DEPT_CD DEPT_NM
--- --------- ------------ ---------------
1 DEPT_002 DEPT_001 경영지원부
1 DEPT_003 DEPT_001 영업본부
2 DEPT_004 DEPT_002 회계팀
2 DEPT_005 DEPT_002 인사팀
2 DEPT_006 DEPT_002 재무팀
2 DEPT_007 DEPT_003 국내영업팀
2 DEPT_008 DEPT_003 해외영업팀
2 DEPT_009 DEPT_003 영업기획팀
2 DEPT_010 DEPT_003 영업지원팀
SQL
복사
ORDER SIBLINGS BY를 사용하면 알파벳순만이 아닌 계층 구조에 최적화된 상태로 레코드를 가져온다.
-- 2. ORDER SIBLINGS BY 사용시
SELECT LEVEL AS LVL,
TCD.DEPT_CD,
TCD.PAR_DEPT_CD,
TCD.DEPT_NM
FROM TB_CMM_DEPT TCD
START WITH TCD.PAR_DEPT_CD = 'DEPT_001'
CONNECT BY TCD.PAR_DEPT_CD = PRIOR TCD.DEPT_CD
ORDER SIBLINGS BY TCD.DEPT_CD;
-- 2. ORDER SIBLINGS BY 결과
LVL DEPT_CD PAR_DEPT_CD DEPT_NM
--- --------- ------------ ----------
1 DEPT_002 DEPT_001 경영지원부
2 DEPT_004 DEPT_002 회계팀
2 DEPT_005 DEPT_002 인사팀
2 DEPT_006 DEPT_002 재무팀
1 DEPT_003 DEPT_001 영업본부
2 DEPT_007 DEPT_003 국내영업팀
2 DEPT_008 DEPT_003 해외영업팀
2 DEPT_009 DEPT_003 영업기획팀
2 DEPT_010 DEPT_003 영업지원팀
SQL
복사
CONNECT BY의 실행순서는 다음과 같다.1.
START WITH 절
2.
CONNECT BY 절
3.
WHERE 절
with
이름이 부여된 서브쿼리. view와 쓰임이 비슷. 차이점은 view는 drop을 하기전까지는 없어지지 않지만, with절은 한번 실행할 쿼리문내 정의되어 있을 경우, 그 쿼리문안에서만 실행된다.
•
사용 이유 : 복잡한 sql에서 동일 서브쿼리에 대해 반복적으로 사용하는 경우 그 서브쿼리에 이름을 부여해 재사용할 수 있게 함으로서 쿼리 성능을 높이게 할 수 있다.
in MySQL
•
WITH 문 여러개 생성하기
WITH 별칭1 AS(
...
), 별칭2 AS(
...
)
SQL
복사
DAY 5 QUIZ FROM HACKERRANK
The Blunder 다시!
Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard's key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeros removed), and the actual average salary.
Write a query calculating the amount of error (i.e.: actual average monthly salaries), and round it up to the next integer.
Input Format
The EMPLOYEES table is described as follows:
SELECT CEIL(avg(Salary)-avg(REPLACE(Salary,0,'')))
FROM EMPLOYEES;
SQL
복사
Symmetric Pairs 다시!
SELECT A.X, A.Y
FROM FUNCTIONS A, FUNCTIONS B
WHERE A.X = B.Y
AND B.X = A.Y
GROUP BY A.X, A.Y
HAVING COUNT(A.X) > 1 OR A.X < A.Y
ORDER BY A.X;
SQL
복사
Interview 다시!
contests
colleges
challenges
view_stats
submission_stats