8/8 날짜 출력 형식, DECODE, CASE, GROUP, HAVING, WHERE, 그룹함수, ROLLUP, CUBE

*커맨드 창으로 Mysql 실행하기

시작 > 

> 비밀번호 치고 > show databases;

하면 나의 구조를 보여줌

sql문 쳐서 실행시킬 수도 있음.

 

*오라클과 다르게 Mysql에서는 number는 int로 , varchar2 는 varchar로 쓴다.

 

sql문

--1. salgrade 데이터 전체 보기
SELECT * FROM salgrade;

--2. scott에서 사용가능한 테이블 보기
SELECT * FROM tab;

--3. emp TABLE에서 사번, 이름 , 급여, 업무, 입사일
SELECT empno, ename, sal, job, hiredate FROM emp;

--4. emp Table에서 급여가 2000미만인 사람 에 대한 사번, 이름, 급여 항목 조회
SELECT empno, ename, sal FROM emp WHERE sal<2000;

--5. emp Table에서 80/02이후에 입사한 사람에 대한  사번,이름,업무,입사일 
SELECT empno, ename, job, hiredate FROM emp WHERE hiredate >= '80/02/01';

--6. emp Table에서 급여가 1500이상이고 3000이하 사번, 이름, 급여  조회(BETWEEN 사용 권장)
SELECT empno, ename, sal FROM emp WHERE sal >= 1500 AND sal <= 3000;
SELECT empno, ename, sal FROM emp WHERE sal BETWEEN 1500 AND 3000;

--7. emp Table에서 사번, 이름, 업무, 급여 출력 [ 급여가 2500이상이고 업무가 MANAGER인 사람]
SELECT empno, ename, job, sal FROM emp WHERE sal >= 2500 and job = 'MANAGER';

--8. emp Table에서 이름, 급여, 연봉 조회 [단 조건은  연봉 = (급여+상여) * 12  , null을 0으로 변경]
SELECT ename, sal, (sal+NVL(comm,0))*12 AS 연봉 FROM emp;

--9. emp Table에서  81/02 이후에 입사자들중 xxx는 입사일이 xxX [ 전체 Row 출력 ] --> 2가지 방법 다
SELECT CONCAT(CONCAT(ename, '는 입사일이 '), hiredate) FROM emp WHERE hiredate>= '81/02/01';
SELECT ename || '는 입사일이 ' || hiredate FROM emp WHERE hiredate>= '81/02/01';

--10.emp Table에서 이름속에 T가 있는 사번,이름 출력
SELECT empno, ename FROM emp WHERE ename like '%T%';

-- LAST_DAY, NEXT_DAY
-- LAST_DAY 함수는 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수
-- NEXT_DAY 함수는 해당 일을 기준으로 명시된 요일의 다음 날짜를 변환하는 함수
-- 오늘이 속한 달의 마지막 날짜와 다가오는 일요일의 날짜를 출력하여라
SELECT SYSDATE, LAST_DAY(SYSDATE), NEXT_DAY(SYSDATE, '일') FROM dual;
-- 오늘이 속한 달의 마지막 날짜와 다가오는 토요일의 날짜를 출력하여라
SELECT SYSDATE, LAST_DAY(SYSDATE), NEXT_DAY(SYSDATE, '토') FROM dual;

-- ROUND, TRUNC 함수
-- TO_CHAR 함수는 날짜나 숫자를 문자로 변환하기 위해 사용 (별 3개)
-- hiredate를 일, 월, 년 기준으로 반올림시킴.
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD HH24:MI:SS') normal,
    TO_CHAR(ROUND(SYSDATE, 'dd'), 'YY/MM/DD') round_dd,
    TO_CHAR(ROUND(SYSDATE,'mm'), 'YY/MM/DD') round_mm,
    TO_CHAR(ROUND(SYSDATE,'yy'), 'YY/MM/DD') round_yy
FROM dual;

SELECT TO_CHAR(hiredate, 'YY/MM/DD HH24:MI:SS') normal,
    TO_CHAR(ROUND(hiredate, 'dd'), 'YY/MM/DD') round_dd,
    TO_CHAR(ROUND(hiredate,'mm'), 'YY/MM/DD') round_mm,
    TO_CHAR(ROUND(hiredate,'yy'), 'YY/MM/DD') round_yy
FROM professor;

 

[묵시적인 데이터 타입 변환]

 

[날짜 출력 형식의 종류 : 실무에서는 YYYY, YY 이 두가지를 많이 쓴다.  (이것만 알아두면 됨)]

 

(실무에서는  MM, MON, DD, DY, DAY를 많이쓴다. (이것만 알아두면 됨))

--학생 테이블에서 전인하 학생의 학번과 생년월일 중에서 년월만 출력하여라
SELECT studno, TO_CHAR(birthdate, 'YYMM') birthdate FROM student WHERE name='전인하';

 

 

-- 숫자를 문자 형식으로 변환
-- 보직수당을 받는 교수들의 이름, 급여, 보직수당, 
--그리고 급여와 보직수당을 더한 값에 12를 곱한 결과를 연봉으로 출력하여라
SELECT name, sal, comm, TO_CHAR((sal+comm)*12, '9,999')anual_sal FROM professor WHERE comm IS NOT NULL;

SELECT TO_NUMBER('123') FROM dual;

-- 학생의 주민등록번호에서 생년월일을 추출하여 ‘YY/MM/DD’ 형태로 출력하여라
SELECT SUBSTR(idnum, 1,2) || '/' || SUBSTR(idnum, 3, 2) || '/' || SUBSTR(idnum, 5,2) FROM student;
SELECT TO_CHAR(TO_DATE(SUBSTR(idnum,1,6),'YYMMDD'), 'YY/MM/DD') BirthDate1 FROM student;

-- NVL 함수는 NULL을 0 또는 다른 값으로 변환하기 위한 함수 (중요)
-- 101번 학과 교수의 이름, 직급, 급여, 보직수당, 급여와 보직수당의 합계를 출력하여라. 
-- 단, 보직수당이 NULL인 경우에는 보직수당을 0으로 계산한다
SELECT name, position, sal, NVL(comm,0), sal+ NVL(comm,0) as 합계 FROM professor WHERE deptno = 101;

--NVL2 함수 (중요)
--NVL2(?,?,?) 함수는 첫 번째 값이 NULL이 아니면 두 번째 값을 출력하고, 첫 번째 값이 NULL이면 세 번째 인수 값을 출력하는 함수
--102번 학과 교수중에서 보직수당을 받는 사람은 급여와 보직수당을 더한 값을 급여 총액으로 출력하여라. 
--단, 보직수당을 받지 않는 교수는 급여만 급여 총액으로 출력하여라.
SELECT name, position, sal, comm, NVL2(comm, sal+comm, sal) total FROM professor WHERE deptno=102;

--NULLIF 함수
--NULLIF 함수는 두 개의 표현식을 비교하여 값이 동일하면 NULL을 반환하고,일치하지 않으면 첫 번째 표현식의 값을 반환
-- 교수 테이블에서 이름의 바이트 수와 사용자 아이디의 바이트 수를 비교해서 같으면 NULL을 반환하고 같지 않으면 이름의 바이트 수를 반환
SELECT name, userid, LENGTHB(name), LENGTHB(userid), 
    NULLIF(LENGTHB(name), LENGTHB(userid)) nullif_result FROM professor;
    
-- COALESCE 함수
-- COALESCE 함수는 인수중에서  NULL이 아닌 첫 번째 인수를 반환하는 함수
-- 교수 테이블에서 보직수당이 NULL이 아니면 보직수당을 출력하고, 
-- 보직수당이 NULL이고 급여가 NULL이 아니면 급여를 출력, 보직수당과 급여가 NULL이면 0을 출력
SELECT name, comm, sal, COALESCE(comm, sal, 0) Co_result FROM professor;

 

 

[decode함수 (별 5개 아주 중요)]

 

DECODE 문 사용 시 주의사항 (중요, 많이 나옴)

 

--DECODE 함수 *****
--DECODE 함수는 기존 프로그래밍 언어에서 IF문이나 CASE 문으로 표현되는 복잡한 알고리즘을 
--하나의 SQL 명령문으로 간단하게 표현할 수 있는 유용한 기능 
--DECODE 함수에서 비교 연산자는 ‘=‘만 가능
--교수 테이블에서 교수의 소속 학과 번호를 학과 이름으로 변환하여 출력하여라. 
--학과 번호가 101이면 ‘컴퓨터공학과’, 102이면 ‘멀티미디어학과’, 201이면 ‘전자공학과’, 
--나머지 학과 번호는 ‘기계공학과’(default)로 변환
--자바의 if문과 비슷함.
SELECT name, deptno, DECODE(deptno,   101, '컴퓨터공학과'
                                    , 102, '멀티미디어학과'
                                    , 201, '전자공학과'
                                          ,'기계공학과'
                            ) as 학과이름
FROM professor;

--CASE 함수
--CASE 함수는 DECODE 함수의 기능을 확장한 함수 
--DECODE 함수는 표현식 또는 칼럼 값이 ‘=‘ 비교를 통해 조건과 일치하는 경우에만 다른 값으로 대치할 수 있지만,
--CASE 함수에서는 산술 연산, 관계 연산, 논리 연산과 같은 다양한 비교가 가능
--또한 WHEN 절에서 표현식을 다양하게 정의
--버전 8.1.7에서부터 지원되었으며, 9i에서 SQL, PL/SQL에서 완벽히 지원 
--DECODE 함수에 비해 직관적인 문법체계와 다양한 비교 표현식 사용
--자바의 switch문과 거의 비슷함.
--DECODE보다 CASE문을 권장함.
SELECT name, deptno, 
    CASE WHEN deptno = 101 THEN '컴퓨터공학과'
         WHEN deptno = 102 THEN '멀티미디어학과'
         WHEN deptno = 103 THEN '전자공학과'
         ELSE                   '기계공학과'
    END deptname
FROM professor;

--교수 테이블에서 소속 학과에 따라 보너스를 다르게 계산하여 출력하여라. 학과 번호별로 보너스는 다음과 같이 계산한다. 
--학과 번호가 101이면 보너스는 급여의 10%, 102이면 20%, 201이면 30%, 나머지 학과는 0%
SELECT name, deptno, sal,
    CASE WHEN deptno = 101 THEN sal*0.1
         WHEN deptno = 102 THEN sal*0.2
         WHEN deptno = 201 THEN sal*0.3
         ELSE sal*0
    END bonus
FROM professor;

 

[과제]

---------------         Home Work           --------------------
--1. emp Table 의 이름을 대문자, 소문자, 첫글자만 대문자로 출력
SELECT ename, UPPER(ename), LOWER(ename), INITCAP(ename) FROM emp;

--2. emp Table 의  이름, 업무, 업무를 2-5사이 문자 출력
SELECT ename, job, SUBSTR(job, 2, 4) FROM emp;

--3. emp Table 의 이름, 이름을 10자리로 하고 왼쪽에 #으로 채우기
SELECT ename, LPAD(ename, 10, '#') FROM emp;

-- 4. emp Table 의  이름, 업무, 업무가 MANAGER면 관리자로 출력 (V)
SELECT ename, job, DECODE (job, 'MANAGER', '관리자') as V FROM emp;
SELECT ename, job, REPLACE(job, 'MANAGER', '관리자') from emp;

--5. emp Table 의  이름, 급여/7을 각각 정수, 소숫점 1자리, 정수 10의 자리로   반올림하여 출력
SELECT ename, sal/7, round(sal/7), round(sal/7, 1), round(sal/7, -1) FROM emp;

--6.  emp Table 의  이름, 급여/7을 각각 정수, 소숫점 1자리, 정수 10의 자리에서 절사하여 출력
SELECT ename, sal/7, TRUNC(sal/7), TRUNC(sal/7, 1), TRUNC(sal/7, -1) FROM emp;

--7. emp Table 의  이름, 급여/7한 결과를 반올림,절사,ceil,floor
SELECT ename, sal/7, ROUND(sal/7), TRUNC(sal/7), CEIL(sal/7), FLOOR(sal/7) FROM emp;

--8. emp Table 의  이름, 급여, 급여/7한 나머지
SELECT ename, sal, MOD(sal, 7) FROM emp;

--9. emp Table 의 이름, 급여, 입사일, 입사기간(각각 날짜,월)출력
SELECT ename, sal, hiredate, 
       SUBSTR(hiredate, 4, 2) as 입사월, 
       SUBSTR(hiredate, 7, 2) as 입사일 
FROM emp;

SELECT ename, sal, hiredate,
    ROUND(sysdate-hiredate),
    ROUND(months_between(sysdate, hiredate))
FROM emp;

--10.emp Table 의  job 이 'CLERK' 일때 10% ,'ANALYST' 일때 20% 
--                                 'MANAGER' 일때 30% ,'PRESIDENT' 일때 40%
--                                 'SALESMAN' 일때 50% 
--                                 그외일때 60% 인상 하여 
--   empno, ename, job, sal, 및 각 인상 급여를 출력하세요(CASE/Decode문 사용)
SELECT empno, ename, job, sal, 
    CASE WHEN job = 'CLERK' THEN sal*1.1
         WHEN job = 'ANALYST' THEN sal*1.2
         WHEN job = 'MANAGER' THEN sal*1.3
         WHEN job = 'PRESIDENT' THEN sal*1.4
         WHEN job = 'SALESMAN' THEN sal*1.5
         ELSE sal*1.6
    END 인상급여
FROM emp;

SELECT empno, ename, job, sal, 
    DECODE(job, 'CLERK', sal*1.1,
                'ANALYST', sal*1.2,
                'MANAGER', sal*1.3,
                'PRESIDENT', sal*1.4,
                'SALESMAN', sal*1.5
                , sal*1.6
            ) as 인상급여
FROM emp;

 

 

[GROUP 함수]

 

(질문 나올 수 있음)

GROUP BY를 한 경우에는 무조건 WHERE절이 아닌 HAVING절을 사용한다.

 

[그룹함수의 종류(COUNT부터 AVG까지는 필수로 알아야 함)]

 

-- 그룹함수란?
--테이블의 전체 행을 하나 이상의 컬럼을 기준으로 그룹화하여 그룹별로 결과를 출력하는 함수

--1) COUNT 함수
--테이블에서 조건을 만족하는 행의 갯수를 반환하는 함수 (null이면 세지 않는다.)
--문1)101번 학과 교수의 수를 출력하여라
select count(*) from professor where deptno = 101;
--문2)101번 학과 교수중에서 보직수당을 받는 교수의 수를 출력하여라
SELECT count(comm) FROM professor WHERE deptno = 101;

-- 102번 학과 학생들의 몸무게 평균과 합계를 출력하여라
SELECT AVG(weight), SUM(weight) FROM student WHERE deptno = 102;

-- 교수 테이블에서 급여의 표준편차와 분산을 출력
SELECT STDDEV(sal), VARIANCE(sal) FROM professor;

-- 학과별 학생들의 인원 수, 몸무게 평균과 합계를 출력하여라
SELECT deptno, count(*), AVG(weight), SUM(weight) FROM student GROUP BY deptno;

-- 교수 테이블에서 학과별로 교수 수와 보직수당을 받는 교수 수를 출력하여라
SELECT deptno, count(*), count(comm) FROM professor GROUP BY deptno;

-- 교수 테이블에서 학과별로 교수 수와 보직수당을 받는 교수 수를 출력하여라. 단 학과별 교수 수가 2명 이상인 경우만.
--GROUP BY에서 조건을 걸 때는 WHERE이 아닌 HAVING 절을 넣는다. 헷갈리므로 주의하기.
SELECT deptno, count(*), count(comm) FROM professor GROUP BY deptno HAVING COUNT(*)>1;

-- 학생 수가 4명이상이고 평균키가 168이상인  학년에 대해서 학년, 학생 수, 평균 키, 평균 몸무게를 출력
-- 단, 평균 키와 평균 몸무게는 소수점 첫 번째 자리에서 반올림 하고, 
-- 출력순서는 평균 키가 높은 순부터 내림차순으로 출력
SELECT grade, count(*), ROUND(AVG(height)), ROUND(AVG(weight)) FROM student 
GROUP BY grade
HAVING AVG(height)>=168 AND count(*)>=4
ORDER BY AVG(height) DESC;

-- 1. 최근 입사 사원과 가장 오래된 사원의 입사일 출력 (emp)
SELECT MAX(hiredate), MIN(hiredate) FROM emp;

-- 2. 부서별 최근 입사 사원과 가장 오래된 사원의 입사일 출력 (emp)
SELECT deptno, MAX(hiredate), MIN(hiredate) FROM emp GROUP BY deptno;

--3. 부서별, 직업별 count & sum[급여]    (emp)
SELECT deptno, job, COUNT(*), SUM(SAL) FROM emp GROUP BY deptno, job ORDER BY deptno, job;

--4. 부서별 급여총액 3000이상 부서번호,부서별 급여최대    (emp)
SELECT deptno, MAX(sal) FROM emp GROUP BY deptno HAVING SUM(sal) >= 3000;

--5. 전체 학생을 소속 학과별로 나누고, 같은 학과 학생은 다시 학년별로 그룹핑하여, 
-- (*group by에 쓴 항목은 select문 뒤에도 똑같이 작성해줘야 함.)
--   학과와 학년별 인원수, 평균 몸무게를 출력, 
-- (단, 평균 몸무게는 소수점 이하 첫번째 자리에서 반올림 )  STUDENT
SELECT deptno, grade, COUNT(*), ROUND(AVG(weight)) FROM student GROUP BY deptno, grade;

 

 

[ROLLUP, CUBE 연산자 (중요하진 않음)]

--ROLLUP 연산자
--GROUP BY 절의 그룹 조건에 따라 전체 행을 그룹화하고 각 그룹에 대해 부분합을 구하는 연산자
--문1) 소속 학과별로 교수 급여 합계와 모든 학과 교수들의 급여 합계를 출력하여라
SELECT deptno, SUM(sal) FROM professor GROUP BY ROLLUP(deptno);

출력 결과에서 deptno가 null값으로 나오는 sum(sal)의 값이 모든 학교 교수들의 급여 합계이다.

 

--문2) ROLLUP 연산자를 이용하여 학과 및 직위별 교수 수, 학과별 교수 수, 전체 교수 수를 출력하여라
SELECT deptno, position, COUNT(*)
FROM professor
GROUP BY ROLLUP (deptno, position);

출력 결과에서 position에 NULL이 있는 곳이 deptno(학과) 별 교수 수이며
deptno와 position 에 전부 NULL이 있는 곳은 전체 교수 수이다.

 

--CUBE 연산자
--ROLLUP에 의한 그룹 결과와 GROUP BY 절에 기술된 조건에 따라 그룹 조합을 만드는 연산자
--rollup과 거꾸로 합계를 알려준다.
--문1) CUBE 연산자를 이용하여 학과 및 직급별 교수 수, 학과별 교수 수, 전체 교수 수를 출력하여라.
SELECT deptno, position, COUNT(*) FROM professor GROUP BY CUBE(deptno, position);

출력 결과