' 생생한 꿈을 꾸는 몽쉘의 개발 블로그 '

* 테이블 만들기

① CREATE TABLE 테이블명( 컬럼명 타입, 컬럼명 타입, ~~);

② CREATE TABLE 새테이블명 AS SELECT * FROM 기존테이블명 WHERE 조건; (기존 테이블 복사)

③ SQL DEVELOPER에서 직접 오른쪽 마우스 클릭 후, 새테이블을 생성할 수도 있다.

(똑같이 칼럼명 적고 타입도 똑같이 맞춰주기!!)

​

*DML 데이터 조작문*

(1)INSERT : 기존 데이터베이스에 새로운 데이터 삽입

 

단일행 삽입

INSERT INTO 테이블명 (EMPNO, ENAME)

VALUES ('1234','TIGER');

​

INSERT INTO t_student

VALUES ('20190930', '홍길동', '전기전자', 3, 'A','F','170, 50);

​

-전체가 아닌 원하는 곳에만 넣을 수 있는 경우, 정확하게 밝혀주기

INSERT INTO TO_STUDENT(STU_NO, STU_NAME, STU_DEPT, STU_GRADE)

VALUES ('20191001', '가나다', '기계',2);

 

INSERT INTO TO_STUDENT(STU_GENDER, STU_NO, STU_NAME, STU_HEIGHT)

VALUES ('F', '20191002','초콜릿', '170');

​

*컬럼명 생략 가능 (모든 컬럼에 자료를 입력하는 경우,테이블 생성할 때 컬럼 순서 그대로 입력됨)

​

-하나의 테이블에 복수행 삽입 : 서브쿼리 사용, 기존 테이블에 있던 여러 행을 복사해서 다른 테이블에 삽입

INSERT INTO 테이블명 (*복수행 삽입할 땐, VALUES 쓰지 않는다!!!)

SELECT ()

FROM 테이블 명

WHERE 조건;

​

ex)

INSERT INTO t_student

SELECT *

FROM student;

​

-여러 테이블에 복수행 삽입

INSERT ALL

INTO 테이블명 VALUES(컬럼명,컬럼명~)

INTO 테이블명 VALUES(컬럼명,컬럼명~)

SELECT 두개의 테이블 컬럼명 합해서 작성

FROM EMP

WHERE DEPTNO = 20;

​

-조건(WHEN) 추가하여 여러 테이블에 복수행 삽입

INSERT ALL

WHEN 조건 ~~~ THEN

INTO 테이블명 VALUES(컬럼명,컬럼명~)

WHEN 조건 ~~~ THEN

INTO 테이블명 VALUES(컬럼명,컬럼명~)

SELECT 두개의 테이블 컬럼명 합해서 작성

FROM EMP;

​

문1 T_EMP 테이블을 만들고, 'SMITH'를 제외한 모든 사원의 정보를 입력하시오.

​

CREATE TABLE T_EMP AS

SELECT *

FROM EMP

WHERE ENAME != 'SMITH';

​

문2 T_EMP 테이블에 아래 정보를 입력하시오.

​

이름 : SON

월급 : 20000

보너스 : 20%

사원번호 : 777

​

INSERT INTO T_EMP(ENAME,SAL,COMM,EMPNO)

VALUES('SON',20000,4000,777);

​

문3 T_EMP 테이블에 사원번호 9999, 사원이름 SIRENA를 입력하시오.

​

INSERT INTO T_EMP(EMPNO, ENAME)

VALUES(9999,'SIRENA');

​

(2) UPDATE : 테이블의 기존 내용을 수정하기 위해 사용

​

UPDATE 테이블명

SET 컬럼이름 = 변경값

WHERE 조건; (WHERE절을 사용하지 않을 경우 테이블에 있는 모든 행이 수정되기 때문에 신중히 판단할 것)

​

ex) SON의 SAL를 30000으로 변경하시오

​

UPDATE T_EMP

SET SAL = 30000

WHERE ENAME = 'SON';

​

문4 SON의 보너스를 10% 인상하시오. 그리고, 손흥민의 입사일을 오늘 날짜로 변경하시오.

​

UPDATE T_EMP

SET COMM = COMM * 1.1, HIREDATE = SYSDATE

WHERE EMPNO = 777;

​

문5 SON보다 적게 월급을 받는 사원들의 월급을 10% 인상하시오.

​

UPDATE T_EMP

SET SAL = SAL * 1.1

WHERE SAL < (SELECT SAL

FROM T_EMP

WHERE ENAME = 'SON');

​

문6 COMM이 NULL 값인 모든 사원의 COMM을 0으로 변경하시오.

​

UPDATE T_EMP

SET COMM = 0

WHERE COMM IS NULL;

​

(??)문7 월급 등급이 3인 직원의 월급을 15% 인상하여 저장하시오.

UPDATE T_EMP

SET SAL = SAL * 1.15

WHERE EMPNO IN (SELECT EMPNO

FROM SALGRADE, T_EMP

WHERE T_EMP.SAL BETWEEN LOSAL AND HISAL

AND GRADE = 3);

​

SELECT EMPNO

FROM SALGRADE, T_EMP

WHERE T_EMP.SAL BETWEEN LOSAL AND HISAL

AND GRADE = 3

​

​

문8 DALLAS에서 일하는 직원들의 COMM을 100씩 추가하시오.

UPDATE T_EMP

SET COMM = NVL(COMM,0) + 100

WHERE DEPTNO = (DALLS에서 일하는 부서번호);

​

SELECT DEPTNO

FROM DEPT

WHERE LOC = 'DALLS'

​

​

**문9 SCOTT의 상급자를 BLAKE로 변경하시오.

​

UPDATE T_EMP

SET MGR = (SELECT EMPNO

FROM T_EMP

WHERE ENAME = 'BLAKE')

WHERE EMPNO = (SELECT EMPNO

FROM T_EMP

WHERE ENAME = 'SCOTT');

​

SELECT EMPNO

FROM T_EMP

WHERE ENAME = 'BLAKE'

​

SELECT EMPNO

FROM T_EMP

WHERE ENAME = 'SCOTT'

​

(3) DELETE : 테이블에 저장되어 있는 데이터를 삭제할 때 사용

​

DELETE

FROM 테이블명

WHERE ~

​

ex)

DELETE

FROM T_EMP

WHERE EMPNO = 777;

​

문10 사원번호 7902번 사원 정보를 모두 삭제하시오.

​

DELETE

FROM T_EMP

WHERE EMPNO = 7902;

​

문11 평균급여보다 적게 받는 사원을 모두 삭제하시오.

​

DELETE

FROM T_EMP

WHERE SAL < (SELECT AVG(SAL)

FROM T_EMP);

 

​

문12 T_EMP 테이블의 데이터를 모두 삭제하시오.

​

DELETE FROM T_EMP;

​

*트랜잭션과 TCL* (데이터의 일관성을 유지하면서 안정적으로 데이터를 복구시키기 위한 목적)

트랜잭션 : 사용자에 의해 실행된 SQL문의 집합 (DML 명령어 = SELECT, INSERT,UPDATE,DELETE)

여러개의 명령어의 집합이 정상적으로 처리되면 정상 종료하고 (COMMIT),

여러개의 명력어의 집합이 하나라도 잘못되었다면 전체를 취소한다 (ROLLBACK).

​

(4)COMMIT : 모든 작업들을 정상적으로 처리하겠다고 확정하는 명령어

: 변경된 내용 모두 영구저장

: COMMIT 명령어를 수행하게 되면 하나의 트랜잭션 과정을 종료하는 것

​

(5)ROLLBACK : DELETE로 삭제했던 데이터 되돌리기

: ROLLBACK 명령어를 수행하게 되면 하나의 트랜잭션 과정을 종료하는 것

: 이전 COMMIT한 곳까지 복구

​

(6)MERGE : 데이터 삽입, 수정, 삭제 작업을 한번에 할 수 있다.

데이터를 겹치거나 안겹치는 경우 사용~

USING 절 테이블에 해당 행이 존재하는 경우 UPDATE(DELETE포함 할 수 있다)를 수행하고,

해당 행이 존재하지 않을 경우 INSERT를 수행한다.

대상 테이블에 대한 UPDATE / INSERT 조건은 ON절에 의해 결정된다.

​

(T_EMP 데이터 중 EMP 테이블 데이터가 있는 경우 사원번호 7369 사원의 이름을 KIM으로 변경하고,

데이터가 없는 경우 아래 값이 입력된다.)

MERGE INTO T_EMP

USING EMP

ON (EMP.EMPNO = T_EMP.EMPNO)

WHEN MATCHED THEN

UPDATE SET T_EMP.ENAME = 'KIM'

WHERE EMPNO = 7369

WHEN NOT MATCHED THEN

INSERT VALUES ('777','SON','','','','','','');

​

MERGE INTO T_EMP

USING EMP

ON (EMP.EMPNO = T_EMP.EMPNO)

WHEN MATCHED THEN

UPDATE SET T_EMP.COMM = 100

WHERE ENAME = 'CLERK'

WHEN NOT MATCHED THEN

INSERT VALUES ('777','SON','','','','','','');

​

문13 T_EMP에서 다음 정보를 입력하시오.

사원번호 : 1112

이름 : PARK

월급 : 2000

데이터가 존재하면 UPDATE하고 데이터가 존재하지 않으면 INSERT하시오.

CREATE TABLE S_EMP

AS SELECT *

FROM EMP

WHERE EMPNO = 7902;

​

MERGE INTO T_EMP

USING S_EMP

ON (S_EMP.EMPNO = T_EMP.EMPNO)

WHEN MATCHED THEN

UPDATE SET ENAME = 'PARK', SAL = 2000

WHEN NOT MATCHED THEN

INSERT VALUES (1112,'PARK','','','',2000,'','');

​

***문14 STUDENT 테이블에서 컴퓨터정보 1,2학년이면 몸무게를 10KG 씩 증가시키고,

컴퓨터정보 3학년 학생이면 몸무게 20K을 증가시켜 T_STUDENT 테이블에 저장하시오.

CREATE TABLE T_STUDENT

AS SELECT * FROM STUDENT;

​

MERGE INTO T_STUDENT A

USING (SELECT * FROM STUDENT WHERE STU_DEPT = '컴퓨터정보') B

ON (A.STU_NO = B.STU_NO)

WHEN MATCHED THEN

UPDATE SET A.STU_WEIGHT = B. STU_WEIGHT +

DECODE(B.STU_GRADE,

1,10,

2,10,

3,20)

WHEN NOT MATCHED THEN

INSERT (A.STU_NAME, A.STU_DEPT, A.STU_GRADE,

A.STU_CLASS, A.STU_GENDER, A.STU_HEIGHT,

A.STU_WEIGHT)

VALUES (B.STU_NAME, B.STU_DEPT, B.STU_GRADE,

B.STU_CLASS, B.STU_GENDER, STU_HEIGHT,

B.STU_WEIGHT + DECODE(B.STU_GRADE,

1,10,

2,10,

3,20));

​

​

문15 외국인 학생 테이블(FORE_STUDENT) 작성시에 오류가 발생하였다.

외국인 학생 MARK를 STUDENT 테이블에 추가합니다.

외국인 학생들의 클래스는 모두 A반으로 변경하고 추가된 MARK를 FORE_STUDENT에 저장하시오.

​

<오류 발생 명령문>

CREATE TABLE fore_student

as select * from student

where stu_grade = 1;

<MARK 정보> -> STUDENT 테이블에 추가 (INSERT)

STU_NO STU_NAME STU_GRADE

1111 MARK 1학년

​

create table fore_student

as select * from student;

​

merge into fore_student f

using (select * from student where stu_grade = 1) s

on (f.stu_no = s.stu_no)

when matched then

update set stu_class = 'A'

when not matched then

insert (f.stu_no,f.stu_name,f.stu_dept,f.stu_grade,f.stu_class,

f.stu_gender,f.stu_height,f.stu_weight)

values (s.stu_no,s.stu_name,s.stu_dept,s.stu_grade,s.stu_class,

s.stu_gender,s.stu_height,s.stu_weight);

​

insert into fore_student (stu_no, stu_name,stu_grade)

values(1111, 'MARK', 1);

​

(1) Sub-Query : ( )로 꼭 묶을 것!!!

SELECT~

FROM~

WHERE ~~~~ (SELECT~

FROM~

WHERE);

​

(*서브쿼리는 메인 쿼리가 실행되기 이전에 실행된다)

​

* Sub Query를 사용할 수 있는 절들(ORDER BY절은 불가능!!)

1.WHERE절

2.HAVING절

3.FROM절

​

(2) 단일행 Sub-Query : 서브쿼리의 결과가 하나만 나오는 경우

​

문1 DALLS에서 근무하는 사원의 이름, 부서번호를 출력하시오.

​

SELECT ENAME, DEPTNO

FROM EMP

WHERE DEPTNO = (SELECT DEPTNO

FROM DEPT

WHERE LOC = 'DALLAS');

=============

(JOIN 사용)

SELECT ENAME, DEPTNO

FROM EMP NATURAL JOIN DEPT

WHERE LOC = (SELECT LOC

FROM DEPT

WHERE LOC = 'DALLAS');

​

**문2 직속상관이 KING인 사원의 이름과 급여를 출력하시오.

(틀림... 너무 복잡하게 생각...)

SELECT ENAME,SAL

FROM EMP

WHERE ???? = (SELECT E1.ENAME,E2.ENAME

FROM EMP E1, EMP E2

WHERE E1.MGR = E2.EMPNO AND E2.ENAME = 'KING');

​

(정답 : KING의 사원번호를 찾아서 MGR 번호와 맞춰보면 답이 나온다...)

SELECT ENAME,SAL

FROM EMP

WHERE MGR = (SELECT EMPNO

FROM EMP

WHERE ENAME = 'KING');

​

문3 평균 급여보다 높은 사원의 이름, 급여를 구하라. (평균급여를 구하기 위해서 서브쿼리 사용)

​

SELECT ENAME, SAL

FROM EMP

WHERE SAL >= (SELECT AVG(SAL)

FROM EMP);

​

문4 진현무 학생보다 키가 큰 학생들에 이름, 학번, 키를 출력하시오.

​

SELECT STU_NAME, STU_NO, STU_HEIGHT

FROM STUDENT

WHERE STU_HEIGHT > (SELECT STU_HEIGHT

FROM STUDENT

WHERE STU_NAME = '진현무');

​

SELECT STU_HEIGHT

FROM STUDENT

WHERE STU_NAME = '진현무'

​

**=SELF JOIN으로 풀기

​

SELECT S1.STU_NAME, S1.STU_NO, S1.STU_HEIGHT

FROM STUDENT S1, STUDENT S2

WHERE S1.STU_HEIGHT > S2. STU_HEIGHT

AND S2.STU_NAME = '진현무';

​

**문5 학과별 평균 신장 중 가장 높은 평균 신장, 학과를 출력하시오.

(틀림)

SELECT MAX(STU_HEIGHT),STU_DEPT

FROM STUDENT

GROUP BY STU_DEPT

HAVING AVG(STU_HEIGHT);

​

(정답)

SELECT STU_DEPT,AVG(STU_HEIGHT)

FROM STUDENT

GROUP BY STU_DEPT

HAVING AVG(STU_HEIGHT) = (SELECT MAX(AVG(STU_HEIGHT))

FROM STUDENT

GROUP BY STU_DEPT);

​

SELECT MAX(AVG(STU_HEIGHT))

FROM STUDENT

GROUP BY STU_DEPT;

​

​

문6 부서 평균 급여가 전체 직원 평균 급여보다 많은 부서의 이름, 부서 평균 급여를 출력하시오.

​

SELECT DNAME, ROUND(AVG(SAL))

FROM EMP NATURAL JOIN DEPT

GROUP BY DNAME

HAVING AVG(SAL) > (SELECT AVG(SAL)

FROM EMP;)

​

SELECT AVG(SAL)

FROM EMP;

​

문7 SCOTT 사원보다 많은 급여를 받는 사원 정보를 출력하시오.

​

SELECT *

FROM EMP

WHERE SAL > (SELECT SAL

FROM EMP

WHERE ENAME = 'SCOTT');

​

SELECT SAL

FROM EMP

WHERE ENAME = 'SCOTT';

​

문8 기계과 학생들 중 학년별 평균 신장이 160이상인 학년과 평균 신장을 출력하시오.

​

SELECT STU_GRADE, AVG(STU_HEIGHT)

FROM STUDENT

WHERE STU_DEPT = '기계'

GROUP BY STU_GRADE

HAVING AVG(STU_HEIGHT) > = 160;

​

** =다른방법 (FROM에 서브쿼리 적용)

​

SELECT STU_GRADE, AVG(STU_HEIGHT)

FROM (기계과 학년별 평균 신장)

WHERE AVG (STU_HEIGHT)>= 160

​

SELECT STU_GRADE, AVG(STU_HEIGHT)

FROM (SELECT STU_GRADE, AVG(STU_HEIGHT) AS AVG

FROM STUDENT

WHERE STU_DEPT = '기계'

GROUP BY STU_GRADE)

WHERE AVG(STU_HEIGHT) >= 160;

​

문9 CHICAGO에서 근무하지 않은 모든 사원의 정보를 출력하시오.

​

SELECT *

FROM EMP

WHERE DEPTNO != (SELECT DEPTNO

FROM DEPT

WHERE LOC = 'CHICAGO');

​

SELECT DEPTNO

FROM DEPT

WHERE LOC = 'CHICAGO');

​

**문10 20번 부서의 최대급여보다 최대급여가 많은 부서의 부서번호, 부서이름, 최대급여를 검색하시오.

(ORA-00934: 그룹 함수는 허가되지 않습니다) ===> GROUP BY 위치가 틀렸고, HAVING절로 풀어야 됨

SELECT DEPTNO, DNAME, MAX(SAL)

FROM EMP NATURAL JOIN DEPT

WHERE MAX(SAL) > (SELECT MAX(SAL)

FROM EMP

WHERE DEPTNO = 20)

GROUP BY DEPTNO, DNAME;

​

(정답)

SELECT DEPTNO, DNAME, MAX(SAL)

FROM EMP NATURAL JOIN DEPT

GROUP BY DEPTNO, DNAME

HAVING MAX(SAL) > (SELECT MAX(SAL)

FROM EMP

WHERE DEPTNO = 20);

​

SELECT MAX(SAL)

FROM EMP

WHERE DEPTNO= 20

​

문11 SALESMAN의 커미션 평균을 출력하시오.

​

SELECT AVG(COMM)

FROM EMP

WHERE JOB = 'SALESMAN';

​

** =SUB-QUERY

​

SELECT AVG(COMM)

FROM (SELECT NVL(COMM,0) AS COM

FROM EMP

WHERE JOB = 'SALESMAN');

​

(3) Multi Row Sub Query 다중형 서브쿼리 : 서브쿼리의 결과가 여러 행이 나오는 경우

​

-종류

1. IN : 메인쿼리의 비교조건이 서브쿼리의 결과 중에서 하나라도 일치하면 참 ('='과 같다)

2. ANY,SOME : 메인쿼리의 비교조건이 서브쿼리의 검색결과 하나 이상 일치하면 참 ( > ANY,SOME = 'MIN')

3.ALL : 메인쿼리의 비교조건이 서브쿼리의 검색결과 모든 값이 일치하면 참( > ALL = 'MAX')

​

문12 3000 이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원의 이름과 급여, 부서번호를 출력하라

(ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.) ===> 다중형 서브쿼리이므로 IN 사용!

①SELECT ENAME, SAL, DEPTNO

FROM EMP

WHERE DEPTNO = (SELECT DEPTNO

FROM EMP

WHERE SAL >= 3000);

​

(정답)SELECT ENAME, SAL, DEPTNO

FROM EMP

WHERE DEPTNO IN (SELECT DEPTNO

FROM EMP

WHERE SAL >= 3000);

​

문13 10번 부서에 근무하는 사원의 이름과 10번 부서의 부서명을 출력하라

​

(1) JOIN 이용

SELECT E.ENAME, D.DNAME

FROM EMP NATURAL JOIN DEPT

WHERE DEPTNO = 10;

​

(2) 다중형 서브쿼리, JOIN 이용

SELECT E.ENMAE, D.DNAME

FROM EMP E, (SELECT DEPTNO, DNAME

FROM DEPT

WHERE DEPTNO = 10) D

WHERE E.DEPTNO = D.DEPTNO;

​

문14 부서별로 가장 급여를 많이 받는 사원의 사원번호, 사원이름, 급여, 부서번호를 출력하라.

(ORA-00934: 그룹 함수는 허가되지 않습니다) ===> WHERE절 그룹함수를 SAL로 고침

①SELECT EMPNO, ENAME, SAL, DEPTNO

FROM EMP

WHERE MAX(SAL) IN (SELECT DEPTNO, MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

​

(ORA-00913: 값의 수가 너무 많습니다) ===> 서브쿼리 SELECT문에서 DEPTNO 삭제

②SELECT EMPNO, ENAME, SAL, DEPTNO

FROM EMP

WHERE SAL IN (SELECT DEPTNO, MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

​

(정답)SELECT EMPNO, ENAME, SAL, DEPTNO

FROM EMP

WHERE SAL IN (SELECT MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

​

문15 각 부서별 최소 급여를 받는 사원의 이름과 급여를 구하라

​

SELECT ENAME, SAL

FROM EMP

WHERE SAL IN (SELECT MIN(SAL)

FROM EMP

GROUP BY DEPTNO);

​

SELECT MIN(SAL)

FROM EMP

GROUP BY DEPTNO;

​

문16 20번 부서 사원의 사원 직무와 같은 사원직무인 다른 부서의 사원 정보를 검색하라.

​

SELECT *

FROM EMP

WHERE DEPTNO != 20

AND JOB IN (SELECT DISTINCT JOB

FROM EMP

WHERE DEPTNO = 20);

​

SELECT JOB

FROM EMP

WHERE DEPTNO = 20

​

문17 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하시오

​

SELECT *

FROM EMP

WHERE SAL IN (SELECT MAX(SAL)

FROM EMP

GROUP BY DEPTNO);

​

문18 직급이 매니저인 사람이 속한 부서명, 부서번호, 지역을 출력하시오

​

SELECT DISTINCT DNAME, DEPTNO, LOC

FROM DEPT NATURAL JOIN EMP

WHERE DEPTNO IN (SELECT DEPTNO

FROM EMP

WHERE JOB = 'MANAGER');

​

SELECT DEPTNO

FROM EMP

WHERE JOB = 'MANAGER';

​

문19 영업 사원이 받은 최소 급여보다 많이 받는 사원들의 이름,급여를 출력하시오

(부서번호 20은 제외할 것)

​

SELECT ENAME, SAL

FROM EMP

WHERE SAL > ANY(SELECT SAL

FROM EMP

WHERE JOB = 'SALESMAN')

AND DEPTNO != 20;

===

SELECT ENAME, SAL

FROM EMP

WHERE SAL > (SELECT MIN(SAL)

FROM EMP

WHERE JOB = 'SALESMAN')

AND DEPTNO != 20;

​

SELECT MIN(SAL)

FROM EMP

WHERE JOB = 'SALESMAN';

​

문20 학과별 평균몸무게보다 몸무게가 많은 학과별 학생들의 학번, 이름, 학과명, 몸무게를 출력하시오.

(ORA-00913: 값의 수가 너무 많습니다) ==== > 서브쿼리 SELECT문에서 STU_DEPT 삭제

①SELECT STU_NO, STU_NAME,STU_DEPT,STU_WEIGHT

FROM STUDENT

GROUP BY STU_DEPT

HAVING STU_WEIGHT > (SELECT STU_DEPT​, AVG(STU_WEIGHT)

FROM STUDENT

GROUP BY STU_DEPT);

​

(정답)SELECT STU_NO, STU_NAME, STU_DEPT,STU_WEIGHT

FROM STUDENT

WHERE STU_WEIGHT > ALL(SELECT STU_DEPT,AVG(STU_WEIGHT)

FROM STUDENT

GROUP BY STU_DEPT);

​

(SELF JOIN으로 풂)

SELECT STUDENT.STU_NO, STUDENT.STU_NAME, STUDENT.STU_DEPT, STU_WEIGHT

FROM STUDENT, (SELECT STU_DEPT, AVG(STU_WEIGHT) AVG_WEIGHT

FROM STUDENT

GROUP BY STU_DEPT) STUDENT_B

WHERE STUDENT.STU_DEPT = STUDENT_B.STU_DEPT

AND STUDENT.STU_WEIGHT > AVG_WEIGHT;

​

SELECT STU_DEPT, AVG(STU_WEIGHT)

FROM STUDENT

GROUP BY STU_DEPT

​

문21 급여가 모든 부서들의 평균 급여보다 많은 사원 정보를 검색하라. (ALL)

​

SELECT *

FROM EMP

WHERE SAL > ALL(SELECT AVG(SAL)

FROM EMP

GROUP BY DEPTNO);

​

문22 영업 사원들 보다 급여를 많이 받는 사원의 사원이름, 급여를 출력하시오. (ALL)

​

SELECT ENAME, SAL

FROM EMP

WHERE SAL > ALL(SELECT SAL

FROM EMP

WHERE JOB = 'SALESMAN');

​

​

문23 컴퓨터 정보과 최소 신장보다 큰 학과의 학과명과 최소 신장을 출력하시오.

(ORA-00934: 그룹 함수는 허가되지 않습니다) ===> WHERE절에 그룹함수를 HAVING절로 !!

①SELECT STU_DEPT, MIN(STU_HEIGHT)

FROM STUDENT

WHERE MIN(STU_HEIGHT) > (SELECT MIN(STU_HEIGHT)

FROM STUDENT

WHERE STU_DEPT = '컴퓨터정보');

​

(정답)

SELECT STU_DEPT, MIN(STU_HEIGHT)

FROM STUDENT

GROUP BY STU_DEPT

HAVING MIN(STU_HEIGHT) > (SELECT MIN(STU_HEIGHT)

FROM STUDENT

WHERE STU_DEPT = '컴퓨터정보');

​

​

문24 101번 과목을 수강한 학생들의 정보를 구하라.

(부질의를 조인 질의로 표현 가능하다)

​

SELECT *

FROM STUDENT

WHERE STU_NO IN (SELECT STU_NO

FROM ENROL

WHERE SUB_NO = 101);

===============

(NATURAL JOIN을 사용하면 자동으로 중복되는 STU_NO를 잡아주니, 찾고자 하는 조건만 입력하면 OK)

SELECT *

FROM STUDENT NATURAL JOIN ENROL

WHERE SUB_NO = 101;

​

(4) SET연산자 : SELECT문에 작성

: 독립적인 여러 개의 쿼리를 하나로 묶는 역할

: 최종 결과는 하나의 쿼리가 수행된 것과 같다.

: 수학의 집합과 같은 개념

: SELECT 리스트의 컬럼의 개수, 순서, 타입이 같아야 함

​

1.UNION : QUERY 결과값의 합이며 중복 제거 (합집합)

​

SELECT * FROM EMP WHERE DEPTNO = 20

UNION

SELECT * FROM EMP WHERE DEPTNO = 10;

===

SELECT * FROM EMP WHERE DEPNTO IN (10,20)

​

2.UNION ALL : 중복 포함 (합집합)

​

SELECT * FROM EMP WHERE DEPTNO = 20

UNION ALL

SELECT * FROM EMP WHERE DEPTNO = 10;

​

3.INTERSECT :중복 값(교집합)

​

SELECT * FROM EMP WHERE DEPTNO = 20

INTERSECT

SELECT * FROM EMP WHERE DEPTNO = 10;

​

4.MINUS : 첫번째 결과값에서 두번째 결과값을 뺀 값

​

SELECT * FROM EMP WHERE DEPTNO = 20

MINUS

SELECT * FROM EMP WHERE DEPTNO = 10;

​

문25 전기전자과인 학생들과 컴퓨터 정보과인 학생들의 전체정보를 나타내시오.

​

SELECT *

FROM STUDNET

WHERE STU_DEPT = '전기전자'

UNION

SELECT *

FROM STUDNET

WHERE STU_DEPT = '컴퓨터정보';

​

문26 전체 학생 정보에서 1학년 학생 정보를 제외하고 전체정보를 출력하시오.

​

SELECT *

FROM STUDENT

MINUS

SELECT *

FROM STUDENT

WHERE STU_GRADE = 1;

​

문27 전체 학생 정보에서 기계과이면서 1학년생인 정보를 출력하시오.

​

SELECT *

FROM STUDENT

INTERSECT

SELECT *

FROM STUDENT

WHERE STU_DEPT = '기계'

AND STU_GRADE = 1;

​

문28 SALESMAN 이지만, 보너스를 받지 않는 사원의 정보를 모두 출력하시오 (SET)

​

SELECT *

FROM EMP

WHERE JOB = 'SALESMAN'

MINUS

SELECT *

FROM EMP

WHERE COMM IS NOT NULL;

====

SELECT *

FROM EMP

WHERE JOB = 'SALESMAN'

INTERSECT

SELECT *

FROM EMP

WHERE COMM IS NULL;

​

(5) FROM 절의 부질의 : 부질의를 하나의 테이블로 생각하여 FROM절에 입력

: 테이블 명이 없으므로 별칭을 붙여 사용

: join 방식을 사용하는 것과 같다.

​

**문29 학생들의 학과별 평균 신장보다 큰 신장의 학생들 정보를 출력하라.

​

select stu_no, stu_name, a.stu_dept, stu_height, avg_height

from student a, (select stu_dept, round(avg(stu_height)) avg_height

from student

group by stu_dept) b

where a.stu_dept = b.stu_dept and stu_height > avg_height;

​

select sut_dept, avg(stu_height)

from student

group by stu_dept

​

**문30 점수가 각 학과 학생들의 평균 점수보다 높은 학생의 학번을 검색하라.

​

select a.stu_no,a.stu_dept

from student a, (select stu_dept,avg(enr_grade) avg_enr_grade

from student natural join enrol

group by stu_dept) b, enrol e

where a.stu_dept = b.stu_dept and a.stu_no=e.stu_no and

e.enr_grade > b.avg_enr_grade;

​

​

문31 컴퓨터정보과 학생들의 평균 점수를 구해 학생들의 학번과 이름, 평균 점수를 성적 순으로 검색하라.

​

select stu_no,stu_name,avg(enr_grade)

from student natural join enrol

where stu_dept = '컴퓨터정보'

group by stu_no,stu_name

order by 3;

​

(서브쿼리로 풀면?)

​

select stu_no, stu_name, avg(enr_grade)

from student natural join enrol

group by stu_no, stu_name

having avg(enr_grade) in (select avg(enr_grade)

from student natural join enrol

where stu_dept = '컴퓨터정보'

group by stu_no)

order by 3;

​

문32 사원번호, 사원이름을 부하직원수가 많은 순으로 검색하라.

​

select b.empno,b.ename,count(*)

from emp a join emp b on a.mgr = b.empno

group by b.empno, b.ename

order by 3 desc;

* 윈도우 환경에서는 sql developer를 사용 가능하지만, 리눅스 환경에서는 sqlplus밖에 사용되지 않기 때문에 sqlplus도 자주 사용하며 연습하기!!!!

​

-oracle 사이트에서 download 클릭 sql develop 다운로드

https://www.oracle.com/tools/downloads/sqldev-v192-downloads.html

​

(1) SQL Developer 실행 - '+'누르기 - Name : 임의설정 - 사용자 이름(연결할 계정)과 비번 입력, SID : orcl

테스트 누르면 옆에 '성공'뜸 - 위쪽 '+' 테이블 클릭 - 위에 사람모양 SQL 클릭 후 확인

​

- 새접속을 하고 싶다면? 기존 워크시트에 create user 사용자 이름 identified by 비밀번호;

권한 부여? grant create session to 사용자 이름;

위에 사람모양 SQL 클릭 후 확인

​

(SQL Developer에서 ctrl키를 누르고 sql문 작성한 것에 테이블명을 클릭하면 해당 테이블로 이동 가능)

​

(2) JOIN : 동일한 열의 이름을 데이터베이스끼리 연결해주는 것

: 두 개 이상의 테이블을 결합해야만 원하는 결과를 얻을 수 있을 때 한 번의 질의만으로 원하는 결과를 얻을 수 있도록 하기 위해 조인을 사용한다.

: 일반적으로 Primnary Key(PK)나 Foreign Key(FK)의 연관에 의해 성립됨

​

* 식별자은 중복값에만 붙여줘도 OK~~

ORA-00918: 열의 정의가 애매합니다 ---> 식별자(테이블 명 or 별칭) 붙여주기

​

1. oracle join = equi join (where절 '=' 쓰는것)

​

select emp.empno, emp.ename, dept.dname

from emp, dept

where emp.deptno = dept.deptno;

​

select empno, ename, dname

from emp, dept

where emp.deptno = dept.deptno;

​

select e.empno, e.ename, d.dname

from emp e, dept d (별칭 - from에서는 as 쓰면 안됨!, select에서는 가능)

where e.deptno = d.deptno;

​

select s.stu_no,s.stu_name,e.sub_no,e.enr_grade

from student s, enrol e

where s.stu_no = e.stu_no;

​

(* 3개 테이블 이용할 경우 WHERE절 AND로 연결)

​

2. natural join (from절에 쓰는 것)

​

select empno, ename, dname

from emp natural join dept;

(*보통 NATURAL JOIN에는 식별자를 사용하지 않는다.)

(*컬럼 이름이 같은 경우가 2개 이상 있을 경우 사용할 수 없다)

(* 3개 테이블 이용할 경우 테이블1 natural join 테이블2 natural join 테이블3;)

​

3. join ~ using (from절에 쓰는 것)

​

select emp.empno, emp.ename, dept.dname

from emp join dept using(deptno);

(*보통 JOIN ~ USING에서는 식별자를 사용하지 않는다.)

(* 3개 테이블 이용할 경우 테이블1 join 테이블2 using( ) join 테이블3 using( );)

​

4. join ~on (from절에 쓰는 것)

​

select emp.empno, emp.ename, dept.dname

from emp join dept on emp.deptno = dept. deptno;

(* 3개 테이블 이용할 경우 테이블1 join 테이블2 on subject.sub_no = enrol.sub_no join 테이블3 on enrol. stu_no = student.stu_no;)

​

5. inner join ~ on (from절에 쓰는 것) : A 테이블과 B 테이블의 교집합 부분 구할 때

(양쪽 테이블에 모두 있는 정보만 출력한다. 즉, 한쪽 테이블에 NULL값이 있으면 출력이 되지 않음)

​

select emp.empno, emp.ename, dept.dname

from emp inner join dept on emp.deptno = dept.deptno;

​

문1 학생들이 듣고 있는 과목 번호, 점수를 확인하시오.

학번, 학생명, 과목번호, 점수를 출력하시오.

​

1.select student.stu_no,student.stu_name,enrol.sub_no,enrol.enr_grade

from student, enrol

where student.stu_no = enrol.stu_no;

​

2.select stu_no, stu_name, sub_no, enr_grade

from student natural join enrol;

​

3. select stu_no, stu_name, sub_no, enr_grade

from student join enrol using(stu_no);

​

4.select student.stu_no,student.stu_name,enrol.sub_no,enrol.enr_grade

from student join enrol on student.stu_no = enrol.stu_no;

​

5.select student.stu_no,student.stu_name,enrol.sub_no,enrol.enr_grade

from student inner join enrol on student.stu_no = enrol.stu_no;

​

문2 부서번호가 30이고, 급여가 1500이상인 사원의 이름, 부서명, 급여를 출력하시오.

​

1.SELECT e.ename, d.dname, e.sal

FROM emp e, dept d

WHERE e.deptno = d.deptno AND (d.deptno = 30 AND e.sal >= 1500);

​

2.SELECT ename, dname, sal

FROM emp Natural Join dept

WHERE deptno = 30 AND emp.sal >= 1500;

​

3.SELECT ename, dname, sal

FROM emp JOIN dept USING (deptno)

WHERE deptno = 30 AND sal >= 1500;

​

4.SELECT e.ename, d.dname, e.sal

FROM emp e JOIN dept d ON e.deptno = d.deptno

WHERE e.deptno = 30 AND e.sal >= 1500;

​

5.SELECT emp.ename, dept.dname, emp.sal

FROM emp INNER JOIN dept ON emp.deptno = dept.deptno

WHERE emp.deptno = 30 AND emp.sal >= 1500;

​

문3 사원수가 5명이 넘는 부서의 부서명과 사원수를 출력하시오. (사원수는 *)

​

1.select dept.dname, count(*)

from dept, emp

where dept.deptno = emp.deptno

group by dept.dname

having count(emp.empno) > 5;

​

2.select dept.dname, count(*)

from dept natural join emp

group by dept.dname

having count(emp.empno) > 5;

​

3.select dept.dname, count(*)

from dept join emp using(deptno)

group by dept.dname

having count(emp.empno) > 5;

​

4.select dept.dname, count(*)

from dept join emp on dept.deptno = emp.deptno

group by dept.dname

having count(emp.empno) > 5;

​

5.select dept.dname, count(*)

from dept inner join emp on dept.deptno = emp.deptno

group by dept.dname

having count(emp.empno) > 5;

​

문4 ADAMS 사원이 근무하는 부서이름과 지역이름을 출력하시오.

​

select dname, loc

from dept natural join emp

where ename = 'ADAMS';

​

문5 NEW YORK이나 DALLAS 지역에 근무하는 사원들의 사원번호, 사원이름을 사원번호 순으로 검색하시오.

​

select empno, ename, loc

from emp join dept using(deptno)

where loc = 'NEW YORK' or loc = 'DALLAS'

order by 1;

​

​

문6 부서이름이 ACCOUNTING이거나, 지역이름이 CHICAGO인 사원의 사원번호와 사원이름을 검색하시오.

​

select empno, ename, dname, loc

from emp join dept using(deptno)

where dname = 'ACCOUNTING' or loc = 'CHICAGO';

​

​

(3) Non-Equi Join : 조인 조건에 "=" 외의 비교연산자를 사용하는 조인

(급여 등급 구할 때, BETWEEN ~ AND 사용하는 것처럼~~)

​

문7 사원번호, 사원이름, 급여, 급여등급을 급여등급별 사원번호 순으로 검색하시오.

​

select empno, ename, sal, grade

from emp, salgrade

where emp.sal between salgrade.losal

and salgrade.hisal

order by 1;

​

(4) SELF JOIN

​

select my.empno, my.ename, mgr.empno, mgr.ename

from emp my, emp mgr

where my.mgr= mgr.empno;

​

ex) 매니저가 KING인 사원들의 이름과 직급을 출력하시오.

​

SELECT E1.ENAME, E1.JOB

FROM EMP E1, EMP E2

WHERE E1.MGR = E2.EMPNO AND E1.JOB = 'MANAGER'

AND E2.ENAME = 'KING';

​

**ex) SCOTT과 동일한 근무지에서 근무하는 사원의 이름을 출력하시오.

​

(DEPTNO가 같은 사원)

SELECT E1.ENAME, E2.ENAME

FROM EMP E1, EMP E2

WHERE E1.DEPTNO = E2.DEPTNO

AND E1.ENAME = 'SCOTT';

​

문10 각 부서의 이름과 가장 많은 월급의 크기를 출력하시오.

​

select dname, max(sal)

from dept, emp

where dept.deptno = emp. deptno

group by dname;

​

(5) OUTER JOIN : 2개 이상의 테이블을 조인할 때, 어느 한쪽의 테이블에는 해당하는 데이터가 있으나,

다른 테이블에는 없는 경우, 테이블의 모든 정보를 출력하고자 할 때 사용

(만족하는 행이 없는 행 = NULL 값인 행도 포함시켜 출력)

​

1.FULL : 두개의 테이블 모두 표시 (왼쪽에만 있는 값 출력, 오른쪽에만 있는 값도 출력)

​

SELECT ENAME, DNAME, LOC

FROM EMP FULL OUTER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO;

​

2.LEFT : 왼쪽 테이블 모두 표시 (NULL값이 존재해도 출력)

​

(EMP 테이블과와 DEPT 테이블에서 DEPTNO값이 같으니까 하나의 표로 만드는 것)

SELECT ENAME, DNAME, LOC

FROM EMP LEFT OUTER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO;

​

SELECT ENAME, DNAME, LOC

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO(+);

​

​

3.RIGHT : 오른쪽 테이블 모두 표시 (NULL값이 존재해도 출력)

​

SELECT ENAME, DNAME, LOC

FROM EMP RIGHT OUTER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO;

​

SELECT ENAME, DNAME, LOC

FROM EMP, DEPT

WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;

​

문11 사원번호, 사원이름, 상사번호, 상사이름을 모두 출력하는 쿼리를 작성하시오.

(단, 사장님도 포함하여 출력할 수 있도록 작성하시오.) * SELF JOIN,LEFT 사용

​

SELECT A.EMPNO, A.ENAME, B.EMPNO, B.ENAME

FROM EMP A LEFT OUTER JOIN EMP B

ON A.MGR = B. EMPNO;

​

SELECT A.EMPNO, A.ENAME, B.EMPNO, B.ENAME

FROM EMP A, EMP B

WHERE A.MGR = B.EMPNO(+);

​

**문12 사원번호, 사원이름, 직책, 관리자이름, 근무위치를 출력하시오.

(단, 사장님도 포함하여 출력할 수 있도록 작성하시오.)

​

SELECT E1.EMPNO, E1.ENAME, E1.JOB, E2.ENAME, D.LOC

FROM EMP E1 LEFT OUTER JOIN EMP E2 ON E1.MGR = E2.EMPNO

JOIN DEPT D ON E1.DEPTNO = D.DEPTNO;

​

select e1.empno, e1.ename, e1.job, e2.ename, d.loc

from emp e1, emp e2, dept d

where e1.mgr = e2.empno(+)

and e1.deptno = d.deptno;

​

​

***문13 사원 JAMES의 사원번호, 이름, 급여, 급여등급, 부서명과 그의 상사 이름, 상사의 부서명을 출력하시오.

​

SELECT E1.EMPNO, E1.ENAME, E1.SAL,GRADE, D1.DNAME,

E2.ENAME, D2.DNAME

FROM EMP E1, EMP E2, SALGRADE, DEPT D1, DEPT D2

WHERE E1.DEPTNO = D1.DEPTNO

AND E1.SAL BETWEEN LOSAL AND HISAL

AND E1.MGR = E2.EMPNO

AND E2.DEPTNO = D2.DEPTNO

AND E1.ENAME = 'JAMES';

​

(1)

-NVL (NULL 값 부분에 문자 입력 가능)

SELECT ENAME, NVL(MGR,1111) FROM EMP;

SELECT ENAME, NVL(COMM,0) FROM EMP;

​

-NVL2 (COMM 확인 후 NULL이 아니면=값이 있으면 2번째, NULL이면=값이 없으면 3번째만 출력)

(COMM 확인 후 NULL이 아니면 급여와 커미션을 더한 값, NULL이면 급여만 출력)

SELECT ENAME, NVL2(COMM,SAL+COMM, SAL) AS RESULT FROM EMP;

​

문1 학생들의 키가 측정되어 있지 않을 경우, '재검'이라고 출력하고, 키 데이터가 있으면 '필요없음'이라고 검사사항을 출력하시오.

출력항목은 학생이름, 키, 검사사항 입니다.

​

SELECT STU_NAME, STU_HEIGHT, NVL2(STU_HEIGHT, '필요없음', '재검') AS 검사사항 FROM STUDENT;

​

**문2 모든 사원의 커미션을 200씩 추가하여 출력하시오. NVL, NVL2 모두 이용할 것.

​

SELECT ENAME, NVL(COMM,0) + 200 FROM EMP;

=SELECT ENAME, NVL2(COMM,COMM+200,200) AS COMM FROM EMP;

​

문3 MGR이 없는 사원의 경우 '사장님'으로 나타나도록 출력하시오. 단, 사원 이름, 상급자로 출력할 것

​

SELECT ENAME AS 사원이름, MGR AS 상급자, NVL(TO_CHAR(MGR), '사장님') FROM EMP;

=SELECT ENAME AS 사원이름, MGR AS 상급자, NVL2(TO_CHAR(MGR),TO_CHAR(MGR), '사장님') FROM EMP;

​

(2) NULLIF(인수1, 인수2) : 값이 같으면 NULL 이 나옴

SELECT NVL(NULLIF('A','A'), '널값') FROM DUAL;

= 널값

SELECT STU_NAME, NVL(NULLIF(TO_CHAR(STU_WEIGHT),'90'), '몸무게 90KG') AS 몸무게 FROM STUDENT;

​

(3) CASE(DECODE와 차이점은 비교연산이 가능하다는 점~)

JOB을 비교해서 SALESMAN일 경우, 점원일경우, 매니저일 경우~ 마지막에 END 꼭 붙이기!!, ( ) 쓰면 오류 발생함)

SELECT EMPNO, ENAME, SAL, CASE JOB

WHEN 'SALESMAN' THEN SAL*1.1

WHEN 'CLERK' THEN SAL*1.15

WHEN 'MANAGER' THEN SAL * 1.2

END AS 급여인상 FROM EMP;

​

문4 학생들 중에 기계과 학생의 등록금 1000만원 전기전자 학생의 등록금 500만원 컴퓨터정보 학생의 등록금 300만원이라고 하자.

학번, 이름, 과, 등록금을 출력하시오. 단, 2013학번만 출력하시오.

​

SELECT STU_NO, STU_NAME, STU_DEPT, CASE STU_DEPT

WHEN '기계' THEN '1000만원'

WHEN '전기전자' THEN '500만원'

WHEN '컴퓨터정보' THEN '300만원'

END AS 등록금 FROM STUDENT WHERE STU_NO LIKE '2013%';

​

(4) DECODE (CASE랑 같지만, 함수형태니깐 ,로 구분)

DECODE(컬럼명,조건1,결과1,조건2,결과2,기본결과)

​

ex) 직무에 해당되는 급여를 구해보자. 직급이 'ANALYST'인 사원은 5%, 'SALESMAN'인 사원은 10%, 'CLERK'인 사원은 15%, 'MANAGER'인 사원은 20%를 인상한다.

​

SELECT ENAME, JOB, SAL,

DECODE (JOB, 'ANALYST',SAL*1.05,'SALESMAN', SAL*1.1, 'CLERK', SAL*1.15, 'MANAGER', SAL*1.2, SAL) AS 급여인상

FROM EMP;

​

문4이랑 같은 문제 BUT, DECODE로 풀기

​

SELECT STU_NO, STU_NAME, STU_DEPT, DECODE (STU_DEPT,

'기계', '1000만원',

'전기전자', '500만원',

'컴퓨터정보', '300만원')

FROM STUDENT WHERE STU_NO LIKE '2013%';

​

**문5 사장님의 급여를 2배 인상하여 출력하시오. 이름, 사원번호, 급여

​

SELECT ENAME, EMPNO, DECODE (MGR, NULL, SAL*2, SAL) AS SAL

FROM EMP;

=====

SELECT ENAME, EMPNO, DECODE (JOB,'PRESIDENT',SAL*2, SAL) AS SAL

FROM EMP;

​

(5)그룹함수 : NULL값을 제외하고 계산, NVL사용 안해도 됨 (그룹함수를 사용할 때 보통 alias를 지정한다.)

​

COUNT : 개수

SELECT COUNT(EMPNO) FROM EMP;

​

직업 종류의 개수를 구하시오. 단, 중복은 제외

​

SELECT COUNT(DISTINCT JOB)

FROM EMP;

(* SELECT DISTINCT COUNT(JOB)은 틀린 것!! DISTINCT 위치 중요!!)

​

SUM : 총합

SELECT SUM(SAL) FROM EMP;

​

AVG : 평균

SELECT AVG(SAL) FROM EMP WHERE JOB='SALESMAN';

​

STDDEV : 표준편차

​

VARIANCE : 분산

​

GROUP BY : ~별 (SELECT에 있는 컬럼으로 묶어주면 보기 편하다, SELECT문에 작성하지 않아도 결과는 나옴)

SELECT JOB, ROUND(AVG(SAL),0) AS AVG FROM EMP GROUP BY JOB ORDER BY AVG DESC;

​

ex)

SELECT ENAME, MAX(SAL)

FROM EMP;

(ORA-00937 : 단일 그룹의 함수가 아닙니다)

이유는 ENAME 값은 14개고, MAX(SAL) 값은 1개밖에 없기 때문에 오류가 발생한다.

해결법 : GROUP BY로 묶어야 됨!!

SELECT ENAME, MAX(SAL)

FROM EMP

GROUP BY ENAME;

​

문6 SALESMAN의 커미션 평균을 출력하시오.

​

SELECT JOB, AVG(COMM)

FROM EMP

WHERE JOB = 'SALESMAN'

GROUP BY JOB;

​

문7 직무별 최대 임금을 출력하시오

​

SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB;

​

문8 직무별 평균 급여(SAL+COMM)를 출력하시오

​

(*SAL이랑 합치는 거니까 COMM에 NVL 적용 해줘야 함!)

SELECT JOB, ROUND(AVG(SAL+NVL(COMM,0)))

FROM EMP

GROUP BY JOB;

​

(6) 출력 순서

5 SELECT

1 FROM

2 WHERE

3 GROUP BY

4 HAVING : 그룹함수에 적용한 결과에 다시 조건 부여할 때

6 ORDER BY : 5 select 후에 출력되는 것이기 때문에 alias 사용 가능

​

(7)HAVING 조건(그룹:부서별) vs WHERE 조건(단일행:각각)

​

문9 직무별, 직무별 사원들의 인원수를 출력하시오.

단, 직무인원이 4명이상인 직무만 출력하시오.

​

SELECT JOB, COUNT(*)

FROM EMP

GROUP BY JOB

HAVING COUNT(EMPNO) >= 4;

​

​

문10 사원수가 5명 이상인 부서의 부서번호와 사원수를 출력하시오.

​

SELECT DEPTNO, COUNT(ENAME) AS 사원수

FROM EMP

GROUP BY DEPTNO

HAVING COUNT(EMPNO) >=5;

​

**문11 부서번호, 부서별 평균 임금을 출력하시오.

단, 평균 임금이 2000이하인 부서는 제외 (2000이상인 부서 찾으면 됨!!!)

​

SELECT DEPTNO, AVG(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING AVG(SAL) >= 2000;

​

문12 직무별 사원 평균 급여, 분산, 표준편차를 검색하시오.

​

SELECT JOB, AVG(SAL), VARIANCE(SAL), STDDEV(SAL)

FROM EMP

GROUP BY JOB;

​

​

**문13 직무별, 부서별 사원의 급여 합이 많은 순으로 검색하시오.

​

SELECT JOB, DEPTNO, SUM(SAL+NVL(COMM,0)) AS 급여합

FROM EMP

GROUP BY JOB, DEPTNO

ORDER BY 3 DESC;

​

​

문14 부서별, 직무별 사원들의 급여 편차가 적은 순으로 검색하시오.

​

SELECT DEPTNO, JOB, ROUND(STDDEV(SAL+NVL(COMM,0))) AS 급여편차

FROM EMP

GROUP BY DEPTNO, JOB

ORDER BY 3;

(1) where 조건 검색에 논리 연산자, 범위조건 사용

​

X Y =

AND T T T

T F F

F T F

F F F

​

X Y =

OR T T T

T F T

F T T

F F F

​

X Y =

NOR T T F

T F T

F T T

F F F

​

같지않다.

<>

!= (main)

^=

​

(연습문제 ex)

문1 부서번호 10번인 사원의 사원번호, 사원이름, 월급을 출력하시오

​

select empno, ename, sal

from emp

where deptno = 10;

​

문2 사원번호 7369보다 큰 사원의 이름, 입사일, 부서번호를 출력하시오

​

select ename, hiredate, deptno

from emp

where empno > 7369;

​

문3 사원번호 7300보다 크고, 7400보다 작은 사원의 이름, 입사일, 부서번호를 출력하시오

​

select ename, hiredate, deptno

from emp

where empno > 7300 and empno < 7400;

​

문4 사원이름이 smith인 사원의 사원이름, 입사일, 부서번호를 출력하시오.

행의 제목을 사원이름, 입사일. 부서번호

​

select ename as 사원이름 , hiredate as 입사일, deptno as 부서번호

from emp

where ename = 'SMITH'; (*데이터에 입력한 그대로 대문자로 적어야 함!!!!)

​

(2) LIKE

​

% : 0개 이상의 문자

_ : 1개의 문자

​

select ename as 사원이름 , hiredate as 입사일, deptno as 부서번호

from emp

where ename LIKE 'S%';

​

문5 이름이 K로 시작하는 사원의 사원이름, 사원번호, 직책을 출력하시오.

​

SELECT ENAME, EMPNO, JOB

FROM EMP

WHERE ENAME LIKE 'K%';

​

문6 부서번호가 10인 사원 중 직급이 CLERK이 아닌 사원의 이름, 직책, 부서번호를 출력하시오.

​

SELECT ENAME, JOB, DEPTNO

FROM EMP

WHERE DEPTNO = 10 AND JOB != 'CLERK';

​

​

문7 이름이 W로 시작하고 D로 끝나는 사람의 사원명, 직책, 부서번호를 출력하시오.

​

SELECT ENAME, JOB, DEPTNO

FROM EMP

WHERE ENAME LIKE 'W%' AND ENAME LIKE '%D';

OR

LIKE 'W%D';

​

문8 이름에 A가 포함되어 있는 사원의 이름과 사원번호를 출력하시오.

​

SELECT ENAME,EMPNO

FROM EMP

WHERE ENAME LIKE '%A%';

​

(3) NULL : 값이 없는 것, IS로 표현

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

WHERE STU_HEIGHT IS NOT NULL; (NULL이 아닌 값만 출력 : 키가 입력 된 사람)

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

WHERE STU_HEIGHT IS NULL; (NULL인 경우에만 출력 : 키가 입력 안 된 사람=공란인 사람)

​

(4) IN = OR (1학년 이거나 2학년인 학생이름)

​

SELECT STU_NAME

FROM STUDENT

WHERE STU_GRADE = 1 OR STU_GRADE = 2;

​

SELECT STU_NAME

FROM STUDENT

WHERE STU_GRADE IN (1,2);

​

문9 사원중에 CLERK, SALESMAN, MANAGER인 사원이름, 월급, 사원번호를 출력하시오.

​

SELECT ENAME, SAL, EMPNO

FROM EMP

WHERE JOB IN ('CLERK','SALESMAN','MANAGER');

​

문10 사원중에 COMM을 받고, SALESMAN인 사원의 사원이름, 사원번호, 총급여(SAL+COMM)을 출력하시오.

​

SELECT ENAME, EMPNO, (SAL+COMM) AS 총급여

FROM EMP

WHERE COMM IS NOT NULL AND JOB = 'SALESMAN';

​

(5) ORDER BY - 정렬 (ASC; 오름차순은 생략가능, BUT DESC; 내림차순은 반드시 입력)

(* 가장 최근 날짜는 desc / 알파벳, ㄱㄴㄷㄹ순은 asc)

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

ORDER BY STU_HEIGHT ASC;

=

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

ORDER BY STU_HEIGHT;

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

ORDER BY STU_HEIGHT DESC;

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

ORDER BY STU_NAME;

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

ORDER BY 1; (이름)

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

ORDER BY 2; (키)

​

SELECT STU_NAME AS 이름, STU_HEIGHT AS 키

FROM STUDENT

ORDER BY 이름;

​

SELECT STU_DEPT AS 과, STU_HEIGHT AS 키

FROM STUDENT

ORDER BY 과;

​

SELECT STU_DEPT, STU_HEIGHT

FROM STUDENT

ORDER BY 1,2 DESC; (1 부서명정렬 2 키정렬) 내림차순;

​

문11 매니저가 아닌 사원들 중에 COMM을 받지 않는 사원들의 정보를 모두 출력하시오.

​

SELECT * (모든 정보 출력)

FROM EMP

WHERE JOB != 'MANAGER' AND COMM IS NULL;

​

​

문12 사원들의 정보를 사원 직무별, 급여순으로 검색하시오. 단, 급여는 1000만원 이상만 검색.

​

SELECT JOB, SAL

FROM EMP

WHERE SAL >= 1000

ORDER BY 1,2;

​

​

문13 부서번호가 30번인 사원들 중 사원번호, 이름, 입사일을 출력하시오. 사원번호를 오름차순으로 정렬할 것.

​

SELECT EMPNO, ENAME, HIREDATE

FROM EMP

WHERE DEPTNO = 30

ORDER BY EMPNO ASC;

​

(6) Oracle Data Type

​

CHAR : 고정길이 문자열 (입력한 데이터와 최대 크기를 비교하여 편차가 클 경우 저장공간의 낭비가 심하다)

VARCHAR2 : 가변길이 문자열 (입력한 데이터에 따라 저장공간이 할당되므로 낭비를 줄일 수 있다)

​

(7) 함수

​

-숫자함수

​

1.ROUND(반올림)

SELECT ROUND(345.678,1) 소수 첫째자리에서 반올림

FROM DUAL; (존재하지 않지만 임시)

​

SELECT STU_NAME, ROUND(STU_HEIGHT,-1) 일의 자리에서 반올림(정수)

FROM STUDENT;

​

2.ABS(절대값)

​

3.MOD(나머지) : 2를 입력하면 짝/홀 구분 가능

SELECT MOD(10,3) FROM DUAL;

SELECT MOD(STU_WEIGHT,2) FROM STUDENT;

​

ex) 사원번호가 홀수인 사람들의 사원번호와 사원이름을 출력하라.

​

SELECT EMPNO,ENAME

FROM EMP

WHERE MOD(EMPNO,2) = 1;

​

4.FLOOR(소수점 버려)

SELECT FLOOR(123.456) FROM DUAL;

​

-문자함수

​

1.LOWER(소문자로 변환)

SELECT LOWER('KOREA') FROM DUAL;

​

2.UPPER(대문자로 변환)

SELECT UPPER('korea') FROM DUAL;

​

3.INITCAP(첫번째 문자만 대문자로 변환)

SELECT INITCAP('korea') FROM DUAL;

​

SELECT INITCAP(LOWER('KOREA')) FROM DUAL;

​

4.CONCAT(두개의 문자를 연결)

SELECT CONCAT('hi','HELLO') FROM DUAL;

= SELECT 'hi' || 'HELLO' FROM DUAL;

​

**ex) 사원들의 사원이름, 사원직무를 연결하여 검색하시오.

(예: King President)

SELECT CONCAT(CONCAT(INITCAP(ENAME),' '),INITCAP(JOB)) FROM EMP;

​

5.SUBSTR(대상,시작위치,추출할 문자 개수)

* -1,1 이라면 뒤에서부터 한글자만 검색

​

('문자의 일부분'을 세번째부터 5자를 반환)

SELECT SUBSTR('ABCDEFGHIJKL',3,5) FROM DUAL;

​

ex) 이름이 E로 끝나는 사원을 검색하라. SUBSTR 함수를 사용하여 ENAME의 마지막 문자 한개만 추출해서

이름이 E로 끝나는 사원을 검색해보라.

​

SELECT *

FROM EMP

WHERE SUBSTR(ENAME,-1,1) = 'E';

​

======

​

SELECT *

FROM EMP

WHERE ENAME LIKE '%E';

​

6.LENGTH(문자열길이확인)

SELECT LENGTH('ABCDEFGHIJKL') FROM DUAL;

​

7.INSTR(대상,찾을글자,시작위치,몇번째발견)

​

('특정문자'의 첫번째자부터 C가 1번 연속 나타나는 '위치')

SELECT INSTR('ABCDEFGHIJKL','C',1,1) FROM DUAL;

​

8.LPAD(자릿수 지정함수, 빈 공간을 특정 문자로 왼쪽부터 채움)

SELECT LPAD('ABCDEFGHIJKL',15,'*') FROM DUAL;

​

9.RPAD(자릿수 지정함수, 빈 공간을 특정 문자로 오른쪽부터 채움)

SELECT RPAD('ABCDEFGHIJKL',15,'*') FROM DUAL;

​

문14 사원들의 사원이름, 사원직무를 소문자로 검색하시오

SELECT LOWER(ENAME), LOWER(JOB) FROM EMP;

​

문15 사원들의 사원이름과 사원이름의 첫 글자를 검색하여 출력하시오

SELECT ENAME, SUBSTR(ENAME,1,1) FROM EMP;

​

문16 사원이름을 10자리로 하고 뒤에 '-'를 채워 넣으시오.

SELECT RPAD(ENAME,10,'-') FROM EMP;

​

-날짜함수

​

1.SYSDATE(현재날짜)

SELECT SYSDATE FROM DUAL;

SELECT SYSDATE+1 FROM DUAL;

SELECT SYSDATE+(1/24) FROM DUAL; 1시간 뒤

SELECT SYSDATE+(1/(24*60)) FROM DUAL; 1분뒤

SELECT ENAME, HIREDATE, HIREDATE+3 FROM EMP;

SELECT EMPNO, (MONTHS_BETWEEN(SYSDATE, HIREDATE)/12) FROM EMP;

​

2.SELECT NEXT_DAY(SYSDATE,'화') FROM DUAL;

3.SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;

4.SELECT LAST_DAY(ADD_MONTHS(SYSDATE,3)) FROM DUAL;

5.SELECT ROUND(SYSDATE,'MON') FROM DUAL;

6.SELECT TRUNC(SYSDATE,'MON') FROM DUAL;

​

-변환함수

​

1.TO_CHAR(숫자, 날짜 데이터를 문자 데이터로 변환)

*숫자*

SELECT TO_CHAR(123456789, 'L999,999,999') FROM DUAL;

(L은 LOCAL 현재 있는 나라에 따른 화폐 표시)

​

(9는 자릿수를 나타내며 자릿수가 맞지 않아도 채우지 않는다.)

SELECT TO_CHAR(1234567, 'L999,999,999') FROM DUAL;

= \1,234,567

​

(0은 자릿수를 나타내며 자릿수가 맞지 않을 경우 0으로 채운다.)

SELECT TO_CHAR(1234789, 'L000,000,000') FROM DUAL;

= \001,234,789

​

*날짜*

ex) HIREDATE를 내가 원하는 형식으로 바꾸고자 할 때 사용

SELECT EMPNO, HIREDATE, TO_CHAR(HIREDATE,'MM-DD-YYYY') FROM EMP;

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD, HH24:MI:SS') FROM DUAL;

SELECT EMPNO, HIREDATE, TO_CHAR(HIREDATE,'MM-DD-YYYY DAY' ) FROM EMP;

​

2.TO_NUMBER(문자 데이터를 숫자 데이터로 변환)

SELECT TO_NUMBER(TO_CHAR(123456789)) FROM DUAL;

​

ex) 20000 - 10000

(*산술연산을 하려면 문자형식을 숫자형식으로 변환한 후 실행해야 된다)

SELECT TO_NUMBER('20000','99999') - TO_NUMBER('10000','99999') FROM DUAL;

​

3.TO_DATE(문자 데이터를 날짜 데이터로 변환)

SELECT TO_DATE('1983-08-26','YY-MM-DD') FROM DUAL;

​

ex) 올해 며칠이 지났는지 현재 날짜에서 2008/01/01을 뺀 결과를 출력하시오.

(*2008/01/01이 문자형식이기 때문에 날짜로 바꿔줘야 날짜 - 날짜가 된다)

SELECT TRUNC(SYSDATE - TO_DATE('2008/01/01','YYYY/MM/DD')) FROM DUAL;

​

문17 사원번호, 입사일, 근무 일자를 검색하여 출력하시오

SELECT EMPNO, HIREDATE, ROUND(SYSDATE-HIREDATE) FROM EMP;

​

문18 사원번호, 입사일, 입사 10년째 날짜를 검색하여 출력하시오.

SELECT EMPNO, HIREDATE, HIREDATE+(10*365) FROM EMP;

​

문19 입사일이 81/04/02 보다 늦고 82/12/09 보다 빠른 사원의 이름, 월급, 부서번호를 출력하시오.

SELECT ENAME, SAL, DEPTNO

FROM EMP

WHERE HIREDATE < TO_DATE('19821209','YYYY-MM-DD')

AND HIREDATE > TO_DATE('19810402','YYYY-MM-DD');

​

문20 모든 사원은 자신의 상관이 있다. 하지만, 유일하게 상관이 없는 로우가 있다.

상관이 없는 사원만 출력하고 MGR 값이 NULL 대신 CEO로 출력하시오.

(*ORA-01722: 수치가 부적합합니다 ---> MGR값이 숫자기 때문에 문자로 바꿔줘야 함!!)

SELECT EMPNO, ENAME, NVL(MGR,'CEO')

FROM EMP

WHERE MGR IS NULL;

​

SELECT EMPNO, ENAME, NVL(TO_CHAR(MGR),'CEO')

FROM EMP

WHERE MGR IS NULL;

 

 

(1) oracle 설치하기 (반드시 사용자계정이 영문으로 되어 있는지 확인할 것!! 한글로 되어 있는 경우, 오류 발생하여 포맷해야 됩니다ㅠㅠ 오류로 시간 많이 허비ㅠㅠ)

 

-oracle사이트 회원가입 후 아이디와 패스워드 설정

 

-데이터 저장방식 2가지

File : 저장위치를 안다면 훨씬 빠름 (동영상, 사진 URL)

DB : 중복제거, 검색기능

 

-Oracle XE 184_win 설치하기

 

-sql plus 접속 : system 입력, 오라클 비밀번호로 설정했던 1234 입력

 

-cmd 관리자로 접속 : sqlplus → /as sysdba 입력

 

-sql plus ID / Password 만들기

(아이디) create user c##______ (11g까지는 제약이 없었으나, 12c이상부터는 c##을 넣어야 함)

(비밀번호) identified by ______;

 

-ID 권한부여 : grant connect, resource to c##user ID; (dba를 주면 관리자 권한이기 때문에 db 망가질 우려)

 

(2가지 접속 방법)

- window키 + R(실행창) : cmd 입력 → sqlplus 입력 → ID/Password → show user

or

- 시작버튼 oracle 다운로드 된 곳에서 sqlplus로 들어가기 → ID/Password

 

-나갈땐? exit 입력

 

(2) 테이블 만들기

 

- Table 데이터 입력

insert into student values (~~~~)

 

- 테이블 구조 검색할 때

desc [table 이름];

 

- ~의 ~을 검색하라

SELECT ~ FROM ~;

 

-화면 Clear

clear screen 입력

 

<SQL 명령어 정리>

 

-COMMIT : 파일로 저장

-SAVE 파일명(내가 만들기) :실행한 SQL문을 파일로 저장한다.

-SPOOL 파일명/SPOOL OFF : 출력의 파일 저장을 실행/중지하며 저장 실행시에 출력 파일명을 "SPOOL 파일명"의 형식으로 지정한다.

-EXIT : SQL*PLUS를 중단한다.

-START 파일명 : 이전에 저장한 명령 파일을 실행한다.

-@파일명 : 이전에 저장한 명령 파일을 실행한다.( START와 동일 )

- COL STU_NAME FORMAT A8; (A8로 줄일때)

 

<순서> 콘솔창에서는 꼭 메모장에 입력 후 복붙하기!! 그냥 그대로 치다가 오류나면, 처음부터 다시 쳐야 함...

 

1.메모장에 테이블 데이터 입력 후 복붙

2.SELECT * FROM TAB; 테이블 검색(구지 확인 안해도 됨)

3.insert 데이터 입력 후 복붙

3.확인 SELECT * FROM 파일명;

4.COMMIT

 

<컴퓨터가 처리하는 순서>

ex)

5.select stu_no, stu_name

1.from student

2.where stu_no = 20153075;

3.group by

4.having

6.order by

 

(3) 검색문 : SELECT, 조건 검색 : WHERE

 

ex)

select stu_no, stu_name

from student (테이블명)

where stu_heingt = 177; (조건문)

 

ex) 기계과 학생들의 학번, 이름, 학과 명 출력하시오

select stu_no, stu_name, stu_dept

from student

where stu_dept = '기계';

 

(4) 중복 행 제거 : DISTINCT

 

ex) 여학생들의 학과명 출력하시오

select distinct(중복제거) stu_dept

from student

where stu_gender = 'F';

 

(5) 수식 ex)

 

select stu_name, stu_weight*10

from student;

 

(6) 별칭 붙이기 as

 

select stu_name as 학생이름, stu_weight as 학생키

from student;

 

select stu_name as 학생이름, stu_weight as 학생몸무게

from student;

 

(7) 연결연산자 : || (shift + \)

 

옥한빛 학생은 몸무게가 80kg입니다.

 

select stu_name, ||'학생은 몸무게가'|| stu_weight || 'kg입니다.' AS 몸무게

from studnet;