DATA BASE 명령어 모음 2

김용진·2023년 7월 19일

K-digital KIC캠퍼스 과정

목록 보기

12/28

concat('문자열','문자열') : 문자열을 연결해주는 함수, 최대 두개 까지만 가능하다.

						 concat, '문자열'||'문자열'

위와 같은 방식으로도 가능하다

initcap : 문장의 첫 글자를 대문자로 바꿔준다.

lower,upper : 문장 전체를 소문자 또는 대문자로 바꾸어준다.

LPAD('문장',정수,'문자'),RPAD('문장',정수,'문자') : 왼쪽(L) 또는 오른쪽(R)으로 선언된 정수만큼 문장포함 띄어 쓰기 세번째 파라미터에 문자를 넣으면 띄어쓰기 대신에 문자를 출력한다.

LPAD('good',7,'#') 의 결과는 ###good으로 나오게 된다.

LTRIM('문장1','문장2'),RTRIM() : '문장1'에 '문장2'가 나오면 삭제한다. 지우는 방식이 특이하다,

SELECT LTRIM('goodbye','g')
, LTRIM('goodbye','o')
두가지의 결과는 oodbye, goodbye가 나오게 된다. 앞에서부터 선언한 문자또는 문장이 나와야 삭제, 그렇지 않으면 삭제하지 않는다.
LTRIM('goodbye','go')을 선언하면 bye가 나오게된다. go까지만 삭제하지 않는다.

substr(pra1,정수1,정수2) : pra1의 문장을 pra1부터 pra2번째까지 출력, pra1를 음수로 선언할 경우 뒤에서부터 숫자를 선언한다.
정수1을 양수로 선언뒤 정수2를 넣어주지 않을 시 뒤의 문장을 모두 출력한다.

replace(문자1,문자2,문자3) : 문장을 A에서 B로 바꿔주는 구문, 문자1에 있는 문장을 문자2에 넣어주어 문자3으로 바꾸도록 선언할 수 있다.

sysdate : 현재 프로그램의 시간을 나타낸다, 해당 구문을 활용하여 다양한 방식으로 시간을 선언할 수 있다.

-- 7개월을 더한다
SELECT ADD_MONTHS(SYSDATE, 7)
FROM dual;

-- last_day : 현재달의 마지막 날짜
SELECT LAST_DAY(SYSDATE)
FROM dual;

-- 각 INTERVAL은 해당하는 란에 1씩 더하게한다.
SELECT SYSDATE + (INTERVAL '1' YEAR)
, SYSDATE + (INTERVAL '1' MONTH)
, SYSDATE + (INTERVAL '1' DAY)
, SYSDATE + (INTERVAL '1' HOUR)
, SYSDATE + (INTERVAL '1' MINUTE)
, SYSDATE + (INTERVAL '1' SECOND)
FROM dual;

TO_CHAR() : 문자열 변환
TO_DATE() : 날짜형 변환

두 함수는 겉으로 비슷하지만 CHAR타입변환, DATE타입변환으로 되어있어 서로 DATA타입이 다르다, 문자를 받아 시간으로 바꾸어주기에는 DATE가 더 좋은것으로 보인다.

NVL() : 널값을 다른 데이터로 변경하는 함수, NULL을 0으로 바꾸어 줄때 사용한다.
NVL(변경하고자 하는 열,0)을 사용하면 해당 열에 NULL로 선언되어 있는 값을 0으로 바꾸어 준다

DECODE() : JAVA구문으로 SWITCH혹은 IF문과 비슷한 계열,
예를 들어 DECODE(DEPARTMENT_ID, 20, 'MA' , 60, 'IT', 90,'EX','ETC')로 선언할 경우 20이면 MA로 출력, 60이면 IT로 출력, 90이면 EX로 출력, 그외는 ETC라고 선언한다.

SWITCH(DEPRTMENT_ID){
CASE '20':
RETURN 'MA';
BREAK;
CASE '60':
RETURN 'IT';
BREAK;
CASE '90':
RETURN 'EX';
BREAK;
DEFAULT:
RETURM ETC;
}

같은 구문이 될것같다.

CASE() : DECODE와 같은 계열, 하지만 쓰는 방식이 다르다.

SELECT FIRST_NAME ,LAST_NAME, 

CASE 
WHEN DEPARTMENT_ID = 20 THEN 'MA'
WHEN DEPARTMENT_ID = 60 THEN 'IT'
WHEN DEPARTMENT_ID = 90 THEN 'EX'
ELSE ''

END "DEPARTMENT" (END 선언이후 써준 문장은 AS함수와 같은 역할이다.)
FROM EMPLOYEES e

GROUP BY : 특정 열에 중복값을 기준으로 묶어준다.
HAVING : GROUP BY와 연동하여 사용하는 함수, 보통 WHERE로 선언 불가능한 조건문을 달아줄때 사용한다.

아래는 실습시 진행한 구문들이다.

SELECT * FROM EMPLOYEES e ;

-- 1. 연봉이 12000 초과되는 직원들의 LAST_NAME 및 연봉을조회한다.
-- 단, 급여 오름차순으로 조회

SELECT LAST_NAME ,SALARY
FROM EMPLOYEES e
WHERE SALARY > 12000
ORDER BY SALARY;

-- 2. 사원번호가 176인 사람의 LAST_NAME 부서 번호를 조회한다

SELECT LAST_NAME ,DEPARTMENT_ID
FROM EMPLOYEES e
WHERE EMPLOYEE_ID = 176;

-- 3. 연봉이 5000에서 12000의 범위 이외인 사람들의
-- LAST_NAME 및 연봉을 조회한다.

SELECT LAST_NAME ,SALARY
FROM EMPLOYEES e
WHERE NOT SALARY BETWEEN 5000 AND 12000;

-- ORDER BY SALARY 넣어서 위의 가정이 맞게 들어갔는지 확인 해 보는것도 좋다.

-- 4. 20번 및 50번 부서에서 근무하는 모든 사원들의
-- LAST_NAME 및 부서번호를 알파벳순으로 조회한다.

SELECT SALARY ,LAST_NAME , DEPARTMENT_ID
FROM EMPLOYEES e
WHERE DEPARTMENT_ID IN(20,50)
ORDER BY SALARY ASC,LAST_NAME ASC;

-- 5. 20번 및 50번 부서에서 근무하며,
-- 연봉이 5000~12000 사이인 사원들의 LAST_NAME 및 연봉을 조회한다.

SELECT LAST_NAME ,SALARY, DEPARTMENT_ID
FROM EMPLOYEES e
WHERE DEPARTMENT_ID IN(20,50)
AND SALARY BETWEEN 5000 AND 12000;

-- 6. LAST_NAME 첫 글자가 A인 사원들의 LAST_NAME을 조회한다.

SELECT LAST_NAME
FROM EMPLOYEES e
WHERE LAST_NAME LIKE 'A%';

-- 7. 매니저가없는 사람들의 LAST_NAME 및 JOB_ID를 조회한다.

SELECT LAST_NAME ,JOB_ID
FROM EMPLOYEES e
WHERE MANAGER_ID IS NULL;

-- 8. 커미션을 버는 모든 사원들의 LAST_NAME, 연봉 및 커미션을 조회한다.
-- 연봉은 역순으로 조회한다.

SELECT LAST_NAME ,SALARY ,COMMISSION_PCT
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY SALARY DESC;

-- concat : 문자열 연결

SELECT CONCAT('HELLO','bye'),CONCAT('good','bad')
FROM dual;

-- ||
SELECT CONCAT('good','bad') CONCAT,
'good'||'bad' operators
FROM dual;

--initcatp : 첫 글자를 대문자로

SELECT INITCAP('good morning')
FROM dual;

-- lower, upper
SELECT LOWER('GOOD'), UPPER('good')
FROM dual;

-- LPAD(), RPAD()

SELECT LPAD('good',6) "LPAD1"
, LPAD('good',7,'#') "LPAD2"
, LPAD('good',8,'L') "LPAD3"
FROM dual;

SELECT RPAD('good',6) "RPAD1"
, RPAD('good',7,'#') "RPAD2"
, RPAD('good',8,'L') "RPAD3"
FROM dual;

-- LTRIM(), RTRIM()

SELECT LTRIM('goodbye','g')
, LTRIM('goodbye','o')
--앞에 g로 시작되어있기 때문에 아무것도 지우지않음,
, LTRIM('goodbye','go')
-- go뒤에 o가있기에 goo모두 제거
FROM dual
;

SELECT RTRIM('goodbye','e')
, RTRIM('goodbye','y')
FROM dual
;
-- substr(pra1,pra2,pra3) pra1의 문장을 pra2부터 pra3번째까지 출력

SELECT SUBSTR('good morning john', 1, 4)
FROM dual;

SELECT SUBSTR('good morning john', 8, 4)
FROM dual;

-- 8번째 단어부터 끝까지 출력
SELECT SUBSTR('good morning john', 8)
FROM dual;

-- -를 붙여주면 뒤에서 시작함
SELECT SUBSTR('good morning john', -4)
FROM dual;

-- replace()
SELECT REPLACE ('good morning tom','morning','evenning')
FROM dual;

--시간을 나타내는 구문
-- sysdate
SELECT SYSDATE FROM dual;

-- 7개월을 더한다
SELECT ADD_MONTHS(SYSDATE, 7)
FROM dual;

-- last_day : 현재달의 마지막 날짜
SELECT LAST_DAY(SYSDATE)
FROM dual;

SELECT SYSDATE + (INTERVAL '1' YEAR)
, SYSDATE + (INTERVAL '1' MONTH)
, SYSDATE + (INTERVAL '1' DAY)
, SYSDATE + (INTERVAL '1' HOUR)
, SYSDATE + (INTERVAL '1' MINUTE)
, SYSDATE + (INTERVAL '1' SECOND)
FROM dual;
-- DAY에서 INTERVAL 넣으면 이후를 넣어줌

-- TO_CHAR() : 문자열 변환
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD')
, TO_CHAR(SYSDATE, 'YYYYMMDD')
, TO_CHAR(SYSDATE, 'YYYY-MM-DD')
, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
FROM DUAL;

-- TO_DATE() : 날짜형 변환
SELECT TO_DATE('2023-07-19','YYYY/MM/DD')
FROM DUAL;

-- NVL() : 널값을 다른 데이터로 변경하는 함수

SELECT FIRST_NAME ,LAST_NAME ,NVL(COMMISSION_PCT, 0) COMMISSION_PCT
FROM EMPLOYEES e
;

-- DECODE() : SWITCH구문

SELECT * FROM DEPARTMENTS d ;

SELECT DEPARTMENT_ID
, DECODE(DEPARTMENT_ID, 20, 'MA' , 60, 'IT', 90,'EX','ETC')
FROM DEPARTMENTS d
;

-- CASE() : ELSE IF 문과 비슷한 계열의 함수
SELECT FIRST_NAME ,LAST_NAME
, CASE WHEN DEPARTMENT_ID = 20 THEN 'MA'
WHEN DEPARTMENT_ID = 60 THEN 'IT'
WHEN DEPARTMENT_ID = 90 THEN 'EX'
ELSE ''
END "DEPARTMENT"
FROM EMPLOYEES e
;

/
문제1) EMPLOYEES 테이블에서 King의 정보를 소문자로 검색하고
사원번호,성명, 담당업무(소문자로),부서번호를 출력하라.
/
SELECT EMPLOYEE_ID ,FIRST_NAME ,LOWER(JOB_ID),DEPARTMENT_ID
FROM EMPLOYEES e
WHERE LOWER(LAST_NAME) = 'king';

/
문제2) EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고 사원번호,
성명, 담당업무(대문자로),부서번호를 출력하라.
/

SELECT EMPLOYEE_ID ,FIRST_NAME ,UPPER(JOB_ID),DEPARTMENT_ID
FROM EMPLOYEES e
WHERE UPPER(LAST_NAME) = 'KING';

/
문제3) DEPARTMENTS 테이블에서 부서번호와 부서이름, 위치번호를 합하여 출력하도록 하라.(||사용)
/

SELECT DEPARTMENT_ID||DEPARTMENT_NAME||LOCATION_ID
FROM DEPARTMENTS d ;

/
문제4) EMPLOYEES 테이블에서 30번 부서 중 사원번호 이름과 담당 아이디를 연결하여 출력하여라. (concat 사용)
/
--CONCAT은 최대 2개까지만 가능
SELECT CONCAT(CONCAT(DEPARTMENT_ID,LAST_NAME),MANAGER_ID)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID = 30
;
/*

문제 5. SALARY+SALARYnvl(COMMISSION_PCT ,0) 이 10000이상이면, 'good' 5000 이상이면, 'average', 1이상 5000미만이면 'bad'
0이면 no good 로 평가하고
EMPLOYEE_ID ,FIRST_NAME , SALARY ,COMMISSION_PCT,
SALARY+SALARYnvl(COMMISSION_PCT ,0) 평가를 출력해라.
/
SELECT EMPLOYEE_ID ,FIRST_NAME, COMMISSION_PCT
, CASE WHEN SALARY +SALARY NVL(COMMISSION_PCT,0) >= 10000 THEN 'good'
WHEN SALARY +SALARY NVL(COMMISSION_PCT,0) >= 5000 THEN 'average'
WHEN SALARY +SALARY NVL(COMMISSION_PCT,0) BETWEEN 1 AND 4999 THEN 'bad'
ELSE 'no good'
END GRADE
,SALARY ,SALARY +SALARY *NVL(COMMISSION_PCT,0) AS SAL
FROM EMPLOYEES e
ORDER BY SAL;

SELECT *
FROM EMPLOYEES e
;

-- 유니크(중복 제거, 중복값은 다 삭제)한 데이터를 조회하는 경우
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES e
;

-- GROUP BY : 데이터를 그룹화하여 그 결과를 가져올 때 사용
-- 집계함수와 짝을 이루어 사용 가능,
SELECT DEPARTMENT_ID
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
;

-- 부서별 급여의 합계
SELECT DISTINCT DEPARTMENT_ID ,SUM(SALARY)
FROM EMPLOYEES e
;
--에러 발생

SELECT DEPARTMENT_ID ,SUM(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
;

SELECT EMPLOYEE_ID ,DEPARTMENT_ID
FROM EMPLOYEES e
ORDER BY DEPARTMENT_ID
;

-- 부서별 사원수와 평균 급여를 구해보자, 부서별급여의 총 합까지 구해보자

SELECT COUNT(EMPLOYEE_ID) ,SUM(SALARY), AVG(EMPLOYEE_ID)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID
;

-- 부서별, 직급별(JOB_ID) 사원수와, 평균급여를 구해보자

SELECT DEPARTMENT_ID ,JOB_ID
, SUM(SALARY), COUNT(EMPLOYEE_ID), AVG(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID , JOB_ID
ORDER BY DEPARTMENT_ID , JOB_ID
;

-- 80번 부서의 부서별, 직급별(JOB_ID) 사원수와, 평균급여를 구해보자
SELECT DEPARTMENT_ID ,JOB_ID
, SUM(SALARY), COUNT(EMPLOYEE_ID), AVG(SALARY)
FROM EMPLOYEES e
WHERE DEPARTMENT_ID =80
GROUP BY DEPARTMENT_ID , JOB_ID
ORDER BY DEPARTMENT_ID , JOB_ID
;
-- 순서를 헤깔리면 안된다.

-- 부서가 존재하고, 사원수가 10명 이상인 부서를 조회
/*

WHERE는 집계함수를 사용할 수 없다.
HAVING을 이용하여 그룹화 이후 조건을 선언해줄 수 있다.
HAVING은 반드시 GROUP BY와 함께 사용한다.
*/

SELECT DEPARTMENT_ID ,COUNT()
FROM EMPLOYEES e
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID
HAVING COUNT() >= 10
ORDER BY DEPARTMENT_ID

;

SELECT *
FROM EMPLOYEES e ;
-- 문제1) EMPLOYEES 테이블에서 job_id가 'SA'로 시작하는 사람에 대하여
-- 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하여라.
SELECT AVG(SALARY),MAX(SALARY),MIN(SALARY),SUM(SALARY)
FROM EMPLOYEES e
WHERE JOB_ID LIKE 'SA%'
;
-- 문제2) EMPLOYEES 테이블에 등록되어 있는 인원수,
-- 커미션이 NULL이 아닌 인원수, 연봉평균,
-- 등록되어 있는 부서의 수를 구하여 출력하라.
SELECT COMMISSION_PCT ,COUNT(EMPLOYEE_ID),AVG(SALARY) ,DEPARTMENT_ID
FROM EMPLOYEES e
WHERE COMMISSION_PCT IS NOT NULL
GROUP BY COMMISSION_PCT ,DEPARTMENT_ID
;

-- 문제3) EMPLOYEES 테이블에서 부서별로 인원수,
-- 평균 급여, 최저급여, 최고 급여, 급여의 합을 구하여 출력하라.

SELECT DEPARTMENT_ID ,COUNT(*) ,AVG(SALARY),MAX(SALARY),MIN(SALARY),SUM(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
;

-- 문제4) EMPLOYEES 테이블에서 각 부서별로 인원수,
-- 급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력하여라.

SELECT DEPARTMENT_ID ,COUNT(*), AVG(SALARY),MAX(SALARY),MIN(SALARY), SUM(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
ORDER BY SUM(SALARY) DESC
;

-- 문제5) EMPLOYEES 테이블에서 부서별, 업무별 그룹하여 결과를 부서번호,
-- 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.

SELECT DEPARTMENT_ID ,JOB_ID ,COUNT(*), AVG(SALARY),SUM(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID ,JOB_ID
ORDER BY DEPARTMENT_ID
;

-- 문제6) EMPLOYEES 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호,
-- 인원수, 급여의 합을 구하여 출력하여라

SELECT DEPARTMENT_ID, COUNT(), SUM(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
HAVING COUNT()>4
ORDER BY DEPARTMENT_ID
;

-- 문제7) EMPLOYEES 테이블에서 합계 급여가 최대 10000이상인 부서에 대해서 부서번호,
-- 평균 급여, 급여의 합을 구하여 출력하여라.

SELECT DEPARTMENT_ID, AVG(SALARY), SUM(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY)>10000
ORDER BY DEPARTMENT_ID
;

-- 문제8) EMPLOYEES 테이블에서 업무별 급여의 평균이 10000 이상인 업무에 대해서 업무명,
-- 평균 급여, 급여의 합을 구하여 출력하라.

SELECT DEPARTMENT_ID,JOB_ID, AVG(SALARY), SUM(SALARY)
FROM EMPLOYEES e
GROUP BY DEPARTMENT_ID, JOB_ID
HAVING AVG(SALARY)>=10000
ORDER BY DEPARTMENT_ID
;

-- 문제9) EMPLOYEES 테이블에서 전체 월급이 10000을 초과하는
-- 각 업무에 대해서 업무와 월급여 합계를 출력하라.
-- 단 판매원(SA_)은 제외하고 월 급여 합계로 정렬(내림차순)하라.

SELECT JOBID, SUM(SALARY)
FROM EMPLOYEES e
WHERE JOB_ID NOT LIKE 'SA%'
GROUP BY JOB_ID
HAVING SUM(SALARY)>10000
ORDER BY SUM(SALARY) DESC
;

-- 문제10) 동일한 직업(JOB_ID)을 가진 사원들의 총 수를 조회한다.

SELECT JOB_ID ,COUNT(*)
FROM EMPLOYEES e
GROUP BY JOB_ID
ORDER BY JOB_ID
;

-- 문제11) 매니저의 사번 및 그 매니저가 관리하는 직원들 중
-- 최소 연봉을 받는 사원의 연봉을 조회한다.
-- 매니저가 없는 사람들은 제외한다.
-- 최소 연봉 기준 역순으로 조회한다.

SELECT EMPLOYEE_ID ,MANAGER_ID ,SALARY
FROM EMPLOYEES e
WHERE MANAGER_ID IS NOT NULL
ORDER BY SALARY DESC
;

SELECT MANAGER_ID ,MIN(SALARY)
FROM EMPLOYEES e
WHERE MANAGER_ID IS NOT NULL
GROUP BY MANAGER_ID
ORDER BY MIN(SALARY) DESC
;

김용진

메모리폼

이전 포스트

DATA BASE 명령어 모음

다음 포스트

DATA BASE 명령어 모음 2

K-digital KIC캠퍼스 과정

;

DATA BASE 명령어 모음

DATA BASE 2일차 강의_Join

0개의 댓글