학습목표
데이터베이스 내의 데이터를 검색하기 위해 사용되는 SQL 문을 DQL(Data Query Language) 언어라고 하며 또는 SELECT문이라고 한다. 대부분의 정보시스템을 개발할 때 입력과 수정, 삭제도 중요하지만 저장된 데이터를 사용자가 원하는 형태로 검색하는 것이 무엇보다 중요하다.
[1]. DB에 scott 계정으로 접속하시오.
- CONN scott/tiger
[2]. scott 계정으로 접속되었는지 확인하시오(자신 확인).
- SHOW user // --> SHOW 는 툴(현재 실습툴인 Oracle SQLPlus) 자체의 명령어
[3]. scott 계정 소유의 모든 테이블명을 조회하시오.
- SELECT *
- FROM tab;
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
CUSTOMER TABLE
[4]. customer 테이블의 컬럼구조(컬럼명, 컬럼데이터타입)을 확인하시오.
- DESC customer // --> DESC( =DESCRIBE ) 는 툴(현재 실습툴인 Oracle SQLPlus) 자체의 명령어
SQL> desc customer;
이름 널? 유형
----------------------------------------- -------- ----------------------------
CNO NOT NULL VARCHAR2(10)
CNAME NOT NULL VARCHAR2(20)
ADDR VARCHAR2(30)
TEL VARCHAR2(20)
CREDIT_NO VARCHAR2(10)
CREDIT_COMP VARCHAR2(10)
CREDIT_DATE DATE
JUMIN_NO VARCHAR2(15)
[5]. customer 테이블에 저장된 데이터를 모두 조회하시오.
- SELECT *
- FROM customer;
SQL> desc customer;
이름 널? 유형
----------------------------------------- -------- ----------------------------
CNO NOT NULL VARCHAR2(10)
CNAME NOT NULL VARCHAR2(20)
ADDR VARCHAR2(30)
TEL VARCHAR2(20)
CREDIT_NO VARCHAR2(10)
CREDIT_COMP VARCHAR2(10)
CREDIT_DATE DATE
JUMIN_NO VARCHAR2(15)
[6]. EMP, DEPT 테이블의 구조 밑 데이터를 조회
※ emp 테이블 구조 확인
- DESC emp
SQL> DESC emp
이름 널? 유형
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
※ emp 테이블의 내용 확인
- SELECT *
- FROM emp;
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
※ dept 테이블의 구조 확인
- DESC dept
SQL> desc dept
이름 널? 유형
----------------------------------------------------- -------- ------------------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
※ dept 테이블의 내용 확인
- SELECT *
- FROM dept; //dept 테이블의 모든 속성값을 가진 데이터(내용)를 출력한다.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
기본문법
SELECT [ * || 컬럼명]
FROM [Table Name]
과제 : 09/04/23
교재 실습 : 94p
문제 1) SCOTT이 소유하고 있는 emp TABLE에서 사원번호, 이름, 급여, 담당업무의 값을 출력하시오.
- SELECT empno, ename, sal, job
- From emp;
SQL> SELECT empno, ename, sal, job From emp;
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7369 SMITH 800 CLERK
7499 ALLEN 1600 SALESMAN
7521 WARD 1250 SALESMAN
7566 JONES 2975 MANAGER
7654 MARTIN 1250 SALESMAN
7698 BLAKE 2850 MANAGER
7782 CLARK 2450 MANAGER
7788 SCOTT 3000 ANALYST
7839 KING 5000 PRESIDENT
7844 TURNER 1500 SALESMAN
7876 ADAMS 1100 CLERK
7900 JAMES 950 CLERK
7902 FORD 3000 ANALYST
7934 MILLER 1300 CLERK
문제 2) 모든 사원의 이름 및 급여, 급여에 300을 더한 값을 출력하시오.
- SELECT ename, sal, (sal +300)
- FROM emp;
SQL> SELECT ename, sal, (sal +300) FROM emp
ENAME SAL (SAL+300)
---------- ---------- ----------
SMITH 800 1100
ALLEN 1600 1900
WARD 1250 1550
JONES 2975 3275
MARTIN 1250 1550
BLAKE 2850 3150
CLARK 2450 2750
SCOTT 3000 3300
KING 5000 5300
TURNER 1500 1800
ADAMS 1100 1400
JAMES 950 1250
FORD 3000 3300
MILLER 1300 1600
문제 3) emp 테이블에서 이름, 급여, 보너스, 연봉을 출력하시오(출력값 변환).
- SELECT ename, sal, comm, sal * 12 "연봉"
- FROM emp;
SQL> select ename, sal, comm, (sal*12 + comm) "연봉" from emp;
//(sal*12 + comm) "연봉" : ALIAS 기능을 사용하여, sal*12 + comm 의 타이틀(Attribut)을 "연봉"으로 대체해서 출력한다.
// COMM 값이 빈 것은 NULL 값으로, 0과는 다르다. 조건문 이용해서 계산을 별도로 해준다.
ENAME SAL COMM 연봉 //보시다시피 COMM 값이 없는 항목들은 연봉에 NULL이 출력된다.
---------- ---------- ---------- ----------
SMITH 800
ALLEN 1600 300 19500
WARD 1250 500 15500
JONES 2975
MARTIN 1250 1400 16400
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500 0 18000
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
처리방법 :
SQL> SELECT ename, sal, comm, (sal*12 + comm), (sal*12) + nvl(comm, 0) FROM emp; //nvl 함수 : null 값이 있으면 0을 넣어줘라.
ENAME SAL COMM (SAL*12+COMM) (SAL*12)+NVL(COMM,0)
---------- ---------- ---------- ------------- --------------------
SMITH 800 9600
ALLEN 1600 300 19500 19500
WARD 1250 500 15500 15500
JONES 2975 35700
MARTIN 1250 1400 16400 16400
BLAKE 2850 34200
CLARK 2450 29400
SCOTT 3000 36000
KING 5000 60000
TURNER 1500 0 18000 18000
ADAMS 1100 13200
JAMES 950 11400
FORD 3000 36000
MILLER 1300 15600
문제 4) emp 테이블에서 ENAME을 NAME 로, SAL을 SALARY로 출력하시오.
- SELECT ename "NAME", sal "SALARY", ename||' '||sal
- FROM emp;
SQL> select ename "NAME", sal "SALARY" from emp;
NAME SALARY
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
※ || 오라클 에서는 연결연산자(= 자바 + 연산자의 기능(문자열과 관련된 기능)과 같다.)
문제 5) EMP 테이블에서 ENAME를 '성 명' 으로 sal *12 를 '급 여'로 출력하시오.
- SELECT ename "성 명", sal*12 "급 여"
- FROM emp;
SQL> SELECT ename "성 명", sal*12 "급 여" FROM emp;
성 명 급 여
---------- ----------
SMITH 9600
ALLEN 19200
WARD 15000
JONES 35700
MARTIN 15000
BLAKE 34200
CLARK 29400
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
JAMES 11400
FORD 36000
MILLER 15600
문제 6) EMP 테이블에서 이름과 업무를 "KING is a PRESIDENT" 형식으로 출력하시오( ||' '|| : 사용방법).
- SELECT ename||' is a '|| job
- FROM emp;
SQL> SELECT ename||' is a '|| job FROM emp;
ENAME||'ISA'||JOB
-------------------------
SMITH is a CLERK
ALLEN is a SALESMAN
WARD is a SALESMAN
JONES is a MANAGER
MARTIN is a SALESMAN
BLAKE is a MANAGER
CLARK is a MANAGER
SCOTT is a ANALYST
KING is a PRESIDENT
TURNER is a SALESMAN
ADAMS is a CLERK
JAMES is a CLERK
FORD is a ANALYST
MILLER is a CLERK
문제 7) EMP 테이블에서 이름과 연봉을 "KING : 1 Year salary = 60000" 형식으로 출력하시오( ||' '|| : 사용방법).
- SELECT ename||' : 1 Year salary ='|| ( ( sal * 12 ) + nvl( comm , 0 ) ) "이름 및 연봉"
- FROM emp;
SQL> SELECT ename||' : 1 Year salary = '||( (sal*12)+nvl(comm,0) ) "이름 및 연봉" FROM emp;
ENAME||':1YEARSALARY='||((SAL*12)+NVL(COMM,0))
--------------------------------------------------------------------
SMITH : 1 Year salary =9600
ALLEN : 1 Year salary =19500
WARD : 1 Year salary =15500
JONES : 1 Year salary =35700
MARTIN : 1 Year salary =16400
BLAKE : 1 Year salary =34200
CLARK : 1 Year salary =29400
SCOTT : 1 Year salary =36000
KING : 1 Year salary =60000
TURNER : 1 Year salary =18000
ADAMS : 1 Year salary =13200
JAMES : 1 Year salary =11400
FORD : 1 Year salary =36000
MILLER : 1 Year salary =15600
문제 8) EMP 테이블에서 담당하고 있는 업무의 종류를 출력하시오. // 중복항목을 제거하는 DISTINCT 옵션
- SELECT DISTINCT job FROM emp;
SQL> SELECT DISTINCT job FROM emp;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
문제 9) EMP 테이블에서 중복되지 않는 부서번호를 출력하시오(DISTINCT).
- SELECT DISTINCT deptno FROM emp;
SQL> SELECT DISTINCT deptno FROM emp;
DEPTNO
----------
30
20
10
문제 10) EMP테이블에서 사원이름순으로 사원번호, 이름, 업무, 부서번호를 조회하시오(ORDER BY : 정렬 출력, 기본 오름차순).
- SELECT empno, ename, job, deptno FROM emp ORDER BY ename;
SQL> SELECT empno, ename, job, deptno FROM emp ORDER BY ename;
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7876 ADAMS CLERK 20
7499 ALLEN SALESMAN 30
7698 BLAKE MANAGER 30
7782 CLARK MANAGER 10
7902 FORD ANALYST 20
7900 JAMES CLERK 30
7566 JONES MANAGER 20
7839 KING PRESIDENT 10
7654 MARTIN SALESMAN 30
7934 MILLER CLERK 10
7788 SCOTT ANALYST 20
7369 SMITH CLERK 20
7844 TURNER SALESMAN 30
7521 WARD SALESMAN 30
문제 11) EMP테이블에서 가장 최근에 입사한 순으로 사원번호, 이름, 업무, 급여, 입사일자, 부서번호를 출력하라(ORDER BY : 정렬 출력 + DESC; 내림차순 정렬).
- SELECT empno, ename, job, sal, hiredate, deptno FROM emp ORDER BY hiredate DESC;
SQL> SELECT empno, ename, job, sal, hiredate, deptno FROM emp ORDER BY hiredate DESC;
EMPNO ENAME JOB SAL HIREDATE DEPTNO
---------- ---------- --------- ---------- -------- ----------
7876 ADAMS CLERK 1100 87/05/23 20
7788 SCOTT ANALYST 3000 87/04/19 20
7934 MILLER CLERK 1300 82/01/23 10
7902 FORD ANALYST 3000 81/12/03 20
7900 JAMES CLERK 950 81/12/03 30
7839 KING PRESIDENT 5000 81/11/17 10
7654 MARTIN SALESMAN 1250 81/09/28 30
7844 TURNER SALESMAN 1500 81/09/08 30
7782 CLARK MANAGER 2450 81/06/09 10
7698 BLAKE MANAGER 2850 81/05/01 30
7566 JONES MANAGER 2975 81/04/02 20
7521 WARD SALESMAN 1250 81/02/22 30
7499 ALLEN SALESMAN 1600 81/02/20 30
7369 SMITH CLERK 800 80/12/17 20
- SELECT empno, ename, job, sal, hiredate, deptno FROM emp ORDER BY 2; // Attribute 값 중 2번째(ename) 값을 기준으로 출력하라.
- SELECT empno, ename "이름", job, sal, hiredate, deptno FROM emp ORDER BY "이름"; //별칭(ALIAS)을 기준으로 출력하라.
문제 12) EMP 테이블에서 SMISTH 사원의 모든 정보를 조회하시오(조건식 : WHERE 절 사용방법).
- SELECT * FROM emp WHERE ename = 'SMITH';
SQL> SELECT * FROM emp WHERE ename = 'SMITH';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
문제 13) EMP 테이블에서 급여가 3000이상인 사원의 사원번호, 이름, 담당업무, 급여를 출력하라(조건식 : WHERE 절 사용방법).
- SELECT empno, ename, job, sal FROM emp WHERE sal >= 3000;
SQL> SELECT empno, ename, job, sal FROM emp WHERE sal >= 3000;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7902 FORD ANALYST 3000
문제 14) EMP 테이블에서 담당업부가 Manager인 사원의 정보를 사원번호, 성명, 담당업무, 급여, 부서번호를 출력하라(조건식 : WHERE 절 사용방법).
- SELECT empno, ename, job, sal, deptno FROM emp WHERE job = 'MANAGER';
SQL> SELECT empno, ename, job, sal, deptno FROM emp WHERE job = 'MANAGER';
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
7566 JONES MANAGER 2975 20
7698 BLAKE MANAGER 2850 30
7782 CLARK MANAGER 2450 10
문제 15) EMP 테이블에서 1982년 1월 1일 이후에 입사한 사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하라(조건식 : WHERE 절 사용방법).
- SELECT empno, ename, sal, hiredate, deptno FROM emp WHERE hiredate > '1982/01/01';
SQL> SELECT empno, ename, sal, hiredate, deptno FROM emp WHERE hiredate > '1982/01/01';
EMPNO ENAME SAL HIREDATE DEPTNO
---------- ---------- ---------- -------- ----------
7788 SCOTT 3000 87/04/19 20
7876 ADAMS 1100 87/05/23 20
7934 MILLER 1300 82/01/23 10
- select empno, ename, hiredate, to_char(hiredate, 'rrrr/mm/dd:hh24:mi:ss') from emp;
SQL> select empno, ename, hiredate, to_char(hiredate, 'rrrr/mm/dd:hh24:mi:ss') from emp;
EMPNO ENAME HIREDATE TO_CHAR(HIREDATE,'R
---------- ---------- -------- -------------------
7369 SMITH 80/12/17 1980/12/17:00:00:00
7499 ALLEN 81/02/20 1981/02/20:00:00:00
7521 WARD 81/02/22 1981/02/22:00:00:00
7566 JONES 81/04/02 1981/04/02:00:00:00
7654 MARTIN 81/09/28 1981/09/28:00:00:00
7698 BLAKE 81/05/01 1981/05/01:00:00:00
7782 CLARK 81/06/09 1981/06/09:00:00:00
7788 SCOTT 87/04/19 1987/04/19:00:00:00
7839 KING 81/11/17 1981/11/17:00:00:00
7844 TURNER 81/09/08 1981/09/08:00:00:00
7876 ADAMS 87/05/23 1987/05/23:00:00:00
7900 JAMES 81/12/03 1981/12/03:00:00:00
7902 FORD 81/12/03 1981/12/03:00:00:00
7934 MILLER 82/01/23 1982/01/23:00:00:00
- select * from nls_session_parameters; //현재 세션(현재창에서만)에 설정된 환경파라미터의 값들을 조회
- alter session set nls_date_format='rrrr/mm/dd:hh24:mi:ss'; //날짜 관련 출력형식을 YYYY/MM/DD:24시:분:초 로 출력한다.
- select * from nls_session_parameters;
문제 16) EMP 테이블에서 급여가 1300에서 1500사이의 사원의 성명, 담당업무, 급여, 부서번호를 출력하라.
- SELECT ename, job, sal, deptno FROM emp WHERE sal BETWEEN 1300 AND 1500;
- SELECT ename, job, sal, deptno FROM emp WHERE sal >= 1300 AND sal <=1500;
SQL> SELECT ename, job, sal, deptno FROM emp WHERE sal BETWEEN 1300 AND 1500;
ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
TURNER SALESMAN 1500 30
MILLER CLERK 1300 10
문제 17) EMP 테이블에서 사원번호가 7902, 7788, 7566 인 사원의 사원번호, 성명, 담당업무, 급여, 입사일자를 출력하여라.
- SELECT empno, ename, job, sal, hiredate FROM emp WHERE empno IN('7902', '7788', '7566');
- SELECT empno, ename, job, sal, hiredate FROM emp WHERE empno=7902 OR empno=7788 OR empno=7902;
SQL> SELECT empno, ename, job, sal, hiredate FROM emp WHERE empno IN('7902', '7788', '7566');
EMPNO ENAME JOB SAL HIREDATE
---------- ---------- --------- ---------- -------------------
7566 JONES MANAGER 2975 1981/04/02:00:00:00
7788 SCOTT ANALYST 3000 1987/04/19:00:00:00
7902 FORD ANALYST 3000 1981/12/03:00:00:00
문제 18) EMP 테이블에서 입사일자가 1982년도에 입사한 사원의 사번, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하여라.
- SELECT empno, ename, job, sal, hiredate, deptno FROM emp WHERE hiredate LIKE '1982%';
- SELECT empno, ename, job, sal, hiredate, deptno FROM emp WHERE hiredate LIKE '%82%';
SQL> SELECT empno, ename, job, sal, hiredate, deptno FROM emp WHERE hiredate LIKE '1982%';
EMPNO ENAME JOB SAL HIREDATE DEPTNO
---------- ---------- --------- ---------- ------------------- ----------
7934 MILLER CLERK 1300 1982/01/23:00:00:00 10
문제 19) EMP 테이블에서 보너스가 NULL인 사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하여라.
- SELECT empno, ename, job, sal, hiredate, deptno FROM emp WHERE comm IS NULL;
SQL> SELECT empno, ename, job, sal, hiredate, deptno FROM emp WHERE comm IS NULL;
EMPNO ENAME JOB SAL HIREDATE DEPTNO
---------- ---------- --------- ---------- ------------------- ----------
7369 SMITH CLERK 800 1980/12/17:00:00:00 20
7566 JONES MANAGER 2975 1981/04/02:00:00:00 20
7698 BLAKE MANAGER 2850 1981/05/01:00:00:00 30
7782 CLARK MANAGER 2450 1981/06/09:00:00:00 10
7788 SCOTT ANALYST 3000 1987/04/19:00:00:00 20
7839 KING PRESIDENT 5000 1981/11/17:00:00:00 10
7876 ADAMS CLERK 1100 1987/05/23:00:00:00 20
7900 JAMES CLERK 950 1981/12/03:00:00:00 30
7902 FORD ANALYST 3000 1981/12/03:00:00:00 20
7934 MILLER CLERK 1300 1982/01/23:00:00:00 10
이 글은 스프링노트에서 작성되었습니다.