나만 알아보는 SQL~~
SELECT * FROM student;
SELECT * FROM professor;
select * from department;
select * from salgrade;
select * from tab;
select * from emp;
select * from dept;
/* 날짜 함수 */
/* 101번 학과 교수들의 입사일을 일, 월, 년을 기준으로 반올림하여 출력 */
SELECT TO_CHAR(hiredate, 'YY/MM/DD HH24:MI:SS') hiredate,
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
WHERE deptno = 101;
/* 학생 테이블에서 전인하 학생의 학번과 생년월일 중에서 년월만 출력 */
SELECT TO_CHAR(studno, '9,999,999') studno, TO_CHAR(birthdate, 'YY-MM') birthdate
FROM student
WHERE name = '전인하';
/* 학생 테이블에서 102번 학과 학생의 이름, 학년, 생년월일을 출력 */
SELECT name, grade, studno, TO_CHAR(birthdate, 'Day Month DD, YYYY') birthdate
FROM student
WHERE deptno = 102;
/* 교수 테이블에서 101번 학과 교수의 이름과 입사일 출력 */
SELECT name, TO_CHAR(hiredate, 'MONTH DD, YYYY HH24:MI:SS PM') hiredate
FROM professor
WHERE deptno = 101;
/* 교수 테이블에서 101번 학과 교수의 이름, 직급, 입사일 출력 */
SELECT name, position, TO_CHAR(hiredate, 'MON "the" DDTH "of", YYYY') hiredate
FROM professor
WHERE deptno = 101;
/* 보직수당을 받는 교수들의 이름, 급여, 보직수당, 그리고 급여와 보직수당을 더한 값에 12를
곱한 결과를 연봉으로 출력 */
SELECT name, sal, comm, TO_CHAR((sal+comm)*12, '9,999') anual_sal
FROM professor
WHERE comm IS NOT NULL;
/* TO_DATE - 교수 테이블에서 입사일이 'june 01,01'인 교수의 이름과 입사일 출력 */
SELECT name, hiredate
FROM professor
WHERE hiredate = TO_DATE('6월 01, 01', 'MONTH DD, YY');
/* 출생한지 며칠째인지 출력, 열 레이블은 Lived day */
SELECT TRUNC(SYSDATE - TO_DATE('19910520', 'YYYY-MM-DD')) "Lived day"
FROM dual;
/* 출생일부터 현재일까지의 개월 수 출력, Lived month */
SELECT TRUNC(SYSDATE - TO_DATE('19960714', 'YYYY-MM-DD')) "Lived day",
ROUND(MONTHS_BETWEEN(SYSDATE, TO_DATE('19960714', 'YYYY-MM-DD'))) "Lived month"
FROM dual;
/* ---- 일반 함수 ---- */
/* NVL, NVL2*/
SELECT name, position, sal, comm, sal+comm,
sal+NVL(comm,0) s1, NVL(sal+comm, sal) s2
FROM professor
WHERE deptno = 201;
SELECT ename, sal, comm
, sal+comm
, NVL2(comm, sal+comm, sal)
, sal+NVL(comm, 0)
FROM emp;
/* 영어 변환 */
ALTER SESSION SET NLS_LANGUAGE = AMERICAN;
SELECT name, grade, TO_CHAR(birthdate, 'Day', 'NLS_DATE_LANGUAGE=KOREAN') ||''||
TO_CHAR(birthdate, 'Month DD, YYYY' ) birthdate
FROM student
WHERE deptno = 102;
/* 사람의 이름과 보너스를 출력하는 질의 작성. 보너스를 받지 않는다면 "NO." 출력.
열 레이블은 COMM */
SELECT ename, NVL(TO_CHAR(comm), 'NO.') comm
FROM emp;
desc emp;
/* NULLIF */
/* 교수 테이블에서 이름의 바이트 수와 사용자 아이디의 바이트 수를 비교해서 같으면 NULL을 반환하고
같지 않으면 이름의 바이트 수를 반환 */
SELECT name, userid, LENGTHB(name), LENGTHB(userid),
NULLIF(LENGTHB(SUBSTR(name, 1, 2)), LENGTHB(userid)) nullif_result
FROM professor;
/* COALESCE */
SELECT name, comm, sal, COALESCE(comm, sal, 0) CO_RESULT
FROM professor;
/* DECODE */
SELECT name, deptno,
DECODE(deptno, 101, ' 컴퓨터공학과', 102, ' 멀티미디어학과',
201, '전자공학과', '기계공학과') DNAME
FROM professor;
/* 학생 테이블에서 학과번호와 이름, 학과명을 출력하되 101번 학과 학생만
'Computer Science'로 출력하고 101번이 아닌 학생들은 학과명을 "ETC"로 출력
학과 번호가 없는 학생은 제외 */
SELECT deptno, name,
DECODE(deptno, 101, 'Computer Science', 'ETC') "학과명"
FROM student
WHERE name NOT IN ('황보_정호');
/* CASE -> DECODE 보다 더 좋음 */
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 0
END bonus
FROM professor;
/* 학생 테이블에서 생년월일에서 월을 추출하여 태어난 분기 출력 */
SELECT name, SUBSTR(birthdate, 4, 2) MONTH,
concat(TO_CHAR(birthdate, 'Q'), '/4') 분기
FROM student;
desc student;
/* ---- GROUP 함수 ---- */
SELECT AVG(weight), SUM(weight)
FROM student
WHERE deptno = 101;
/* 모든 사원의 최대 급여, 최저 급여, 합계 그리고 평균 급여 출력 */
SELECT MAX(sal) "Maximum", MIN(sal) "Minium",
SUM(sal) "Sum", ROUND(AVG(sal)) "Average"
FROM emp;
/* STDDEV, VARIANCE 표준편차, 분산 */
SELECT ROUND(STDDEV(sal), 6), ROUND(VARIANCE(sal), 4)
FROM professor;
/* ---- 데이터 그룹 ---- */
/* GROUP BY */
SELECT deptno, COUNT(*), COUNT(comm)
FROM professor
GROUP BY deptno;
/* 학과별로 소속 교수들의 평균 급여, 최소급여, 최대급여를 출력 */
SELECT deptno, AVG(sal), MIN(sal), MAX(sal)
FROM professor
GROUP BY deptno;
SELECT deptno, profno, sal
FROM professor
ORDER BY deptno;
/* 전체 학생을 소속 학과별로 나누고, 같은 학과 학생은 다시 학년별로 그룹핑하여, 학과와 학년별
이눤수, 평균 몸무게를 출력. 단, 평균 몸무게는 소수점 이하 첫번째 자리에서 반올림 */
SELECT deptno, grade, COUNT(*), ROUND(AVG(weight))
FROM student
GROUP BY deptno, grade
ORDER BY count(*);
/* ROLLUP, CUBE 소계, 합계 */
/* 소속 학과별로 교수 급여 합계와 모든 학과 교수들의 급여 합계 출력 */
SELECT deptno, SUM(sal)
FROM professor
GROUP BY ROLLUP(deptno);
/* ROLLUP 연산자를 이용하여 학과 및 직급별 교수 수, 학과별 교수 수, 전체 교수 수 출력 */
SELECT deptno, position, COUNT(*)
FROM professor
GROUP BY ROLLUP(deptno, position);
/* CUBE 연산자 */
SELECT deptno, position, COUNT(*)
FROM professor
GROUP BY CUBE(deptno, position);
/* GROUPING 함수 */
/* 전체 학생을 학과 학년별로 그룹화한 후, 학과와 학년별 그룹 인원수, 각 그룹 조합에서 학과와
학년 칼럼이 사용되었는지 여부 출력 */
SELECT deptno, grade, COUNT(*),
GROUPING(deptno) grp_dno,
GROUPING(grade) grp_grade
FROM student
GROUP BY ROLLUP(deptno, grade);
/* GROUPING SETS 함수 - GROUP BY UNION ALL 결과와 동일 */
SELECT deptno, grade, NULL, COUNT(*)
FROM student
GROUP BY deptno, grade
UNION ALL
SELECT deptno, NULL, TO_CHAR(birthdate, 'YYYY'), COUNT(*)
FROM student
GROUP BY deptno, TO_CHAR(birthdate, 'YYYY');
/* ↓↓↓↓ 같은 결과 */
SELECT deptno, grade, TO_CHAR(birthdate, 'YYYY'), COUNT(*)
FROM student
GROUP BY GROUPING SETS((deptno, grade), (deptno, TO_CHAR(birthdate, 'YYYY')));
/* ---- HAVING 절 ---- */
SELECT grade, COUNT(*), ROUND(AVG(height)) avg_height,
ROUND(AVG(weight)) avg_weight
FROM student
GROUP BY grade
HAVING COUNT(*) > 4
ORDER BY avg_height DESC;
/* 함수의 중첩 */
SELECT deptno, AVG(weight)
FROM student
GROUP BY deptno;
SELECT MAX(AVG(weight)) max_weight
FROM student
GROUP BY deptno;
/* 사원 테이블(emp)에서 급여를 1500 이상 받는 사원들 중에서 평균 급여가 2000이상인
부서 번호, 평균 급여 출력 */
SELECT deptno 부서번호, ROUND(AVG(sal)) 급여
FROM emp
WHERE sal >=1500
GROUP BY deptno
HAVING AVG(sal) >= 2000
ORDER BY deptno;
/* 학과별 학생 수가 최대 또는 최소인 학과의 학생 수를 출력 */
SELECT MAX(COUNT(studno)) max_cnt, MIN(COUNT(studno)) min_cnt
FROM student
GROUP BY deptno;
/* ---- 조인(join) ---- */
SELECT studno, name,
student.deptno, department.dname, department.loc
FROM student, department
WHERE student.deptno = department.deptno;
/* table 별명 - 별명 부여한 이후 별명만 써야 됨 */
SELECT s.studno, s.name,
s.deptno, d.dname, d.loc
FROM student s, department d
WHERE s.deptno = d.deptno;
SELECT s.studno, s.name, d.dname, d.loc
FROM student s, department d
WHERE s.deptno = d.deptno
AND s.name = '전인하';
/* 사원 테이블에서 DALLAS에 근무하는 사번, 이름, 부서번호, 부서이름, 부서 위치 출력 */
SELECT e.empno, e.ename, e.DEPTNO, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.loc = 'DALLAS';
/* 급여가 400이상인 교수 이름, 급여, 학과 번호, 학과 이름 출력 */
SELECT p.name, p.sal, p.deptno, d.dname
FROM professor p, department d
WHERE p.deptno = d.deptno
AND p.sal >= 400;
/* 컴퓨터공학과 학생들의 학번, 이름, 학과번호, 학과이름, 학과위치, 지도교수 이름, 급여 출력*/
SELECT s.studno 학번, s.name 학생이름, s.deptno 학과번호, d.dname 학과이름,
d.loc 학과위치, p.profno 지도교수번호, p.name 지도교수이름, p.sal 급여
FROM student s, professor p, department d
WHERE s.profno = p.profno
AND p.deptno = d.deptno
AND d.dname = '컴퓨터공학과';
/* 몸무게가 80kg 이상인 학생의 학번, 이름, 체중, 학과이름, 학과 위치를 출력 */
SELECT s.studno, s.name, d.dname, d.loc
FROM student s, department d
WHERE s.deptno = d.deptno
AND s.weight >= 80;
/* 조인의 종류 */
/* 카티션 */
SELECT studno, name, s.deptno,
d.deptno, d.dname
FROM student s, department d; /* 연결고리가 없어서 140행이 출력 */
SELECT name, student.deptno, dname
FROM student CROSS JOIN department;
SELECT s.studno, s.name, s.deptno, d.dname, d.loc
FROM student s, department d
WHERE s.deptno = d.deptno;
문제
1. 보너스를 받으면 이름과 보너스를 출력하고, 받지 않으면 이름과 200 출력해 보세요.(emp)
2. 총 급여가 $5,000이 넘는 각 JOB에 대해 JOB와 월급 총액을 출력하세요. (단, PRESIDENT를 제외시키고, 월급 총액별으로 정렬)
JOB PAYROLL
------------------ ----------
SALESMAN 5600
ANALYST 6000
MANAGER 8275
3.CHICAGO에 근무하는 모든 사원에 대해서 이름, 업무, 부서 번호 그리고 부서 이름을 출력하는 질의를 작성하세요.(emp, dept)
4. 사원명, 입사일 그리고 입사한 요일을 출력하세요. 열 레이블은 DAY 입니다.(***결과는 월요일부터 시작하는 요일 순으로 정렬하세요.)
ENAME HIREDATE DAY
-------------------- -------- ------------------------
ADAMS 87/07/13 월요일
SCOTT 87/07/13 월요일
MARTIN 81/09/28 월요일
CLARK 81/06/09 화요일
TURNER 81/09/08 화요일
KING 81/11/17 화요일
SMITH 80/12/17 수요일
JAMES 81/12/03 목요일
5. 1980, 1981, 1982, 1987년에 입사한 사원에 대해서 전체 사원 수와 연도별 사원 수를 출력하세요.
적당한 열레이블을 부여하세요. (emp)
TOTAL 1980 1981 1982 1987
---------- ---------- ---------- ---------- ----------
14 1 10 1 2
/* ---- 문제 ---- */
SELECT ename, COALESCE(comm, comm, 200) bonus
FROM emp;
desc emp;
SELECT job, SUM(sal) PAYROLL
FROM emp
GROUP BY job
HAVING SUM(sal) >= 5000
and job <> ('PRESIDENT')
ORDER BY SUM(SAL);
SELECT e.ename, e.job, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
and d.loc = 'CHICAGO';
SELECT ename, TO_CHAR(hiredate, 'YY/MM/DD'), TO_CHAR(hiredate, 'day', 'NLS_DATE_LANGUAGE=KOREAN') DAY
FROM emp
ORDER BY TO_CHAR(hiredate-1, 'D');
SELECT COUNT(*) TOTAL,
COUNT(DECODE(SUBSTR(hiredate,1,2), 80, hiredate)) "1980",
COUNT(DECODE(SUBSTR(hiredate,1,2), 81, hiredate)) "1981",
COUNT(DECODE(SUBSTR(hiredate,1,2), 82, hiredate)) "1982",
COUNT(DECODE(SUBSTR(hiredate,1,2), 87, hiredate)) "1987"
FROM emp;
'데이터분석' 카테고리의 다른 글
[23.06.26] SQL - 17(1) (0) | 2023.06.26 |
---|---|
[23.06.23] SQL - 16(1) (0) | 2023.06.23 |
[23.06.21] SQL - 14(1) (0) | 2023.06.21 |
[23.06.20] SQL - 13(1) (0) | 2023.06.20 |
[23.06.19] Python time series - 12(3) (0) | 2023.06.19 |