Oracle_20090423(ORACLE 입문, SELECT 문 기초)

학습목표

데이터베이스 내의 데이터를 검색하기 위해 사용되는 SQL 문을 DQL(Data Query Language) 언어라고 하며 또는 SELECT문이라고 한다. 대부분의 정보시스템을 개발할 때  입력과 수정, 삭제도 중요하지만 저장된 데이터를 사용자가 원하는 형태로 검색하는 것이 무엇보다 중요하다.

 

[1]. DB에 scott 계정으로 접속하시오.

1. CONN scott/tiger

 

[2]. scott 계정으로 접속되었는지 확인하시오(자신 확인).

1. SHOW user            // --> SHOW 는 툴(현재 실습툴인 Oracle SQLPlus) 자체의 명령어

 

[3]. scott 계정 소유의 모든 테이블명을 조회하시오.

1. SELECT *
2. FROM tab;

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
CUSTOMER                       TABLE

 

[4]. customer 테이블의 컬럼구조(컬럼명, 컬럼데이터타입)을 확인하시오.

1. 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 테이블에 저장된 데이터를 모두 조회하시오.

1. SELECT *
2. 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 테이블 구조 확인

1. 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 테이블의 내용 확인

1. SELECT *
2. 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 테이블의 구조 확인

1. DESC dept

SQL> desc dept
이름                                                  널?      유형
----------------------------------------------------- -------- ------------------------------------
DEPTNO                                                NOT NULL NUMBER(2)
DNAME                                                          VARCHAR2(14)
LOC                                                            VARCHAR2(13)

※ dept 테이블의 내용 확인

1. SELECT *
2. 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에서 사원번호, 이름, 급여, 담당업무의 값을 출력하시오.

1. SELECT empno, ename, sal, job
2. 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을 더한 값을 출력하시오.

1. SELECT ename, sal, (sal +300)
2. 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 테이블에서 이름, 급여, 보너스, 연봉을 출력하시오(출력값 변환).

1. SELECT ename, sal, comm,  sal * 12 "연봉"
2. 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로 출력하시오.

1. SELECT ename "NAME", sal "SALARY", ename||'   '||sal
2. 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 를 '급  여'로 출력하시오.

1. SELECT ename "성  명", sal*12 "급  여"
2. 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" 형식으로 출력하시오( ||'  '|| : 사용방법).

1. SELECT ename||' is a '|| job
2. 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" 형식으로 출력하시오( ||'  '|| : 사용방법).

1. SELECT ename||' : 1 Year salary ='|| ( ( sal * 12 ) + nvl( comm , 0 ) ) "이름 및 연봉"
2. 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 옵션

1. SELECT DISTINCT job FROM emp;

SQL> SELECT DISTINCT job FROM emp;

JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

 

문제 9) EMP 테이블에서 중복되지 않는 부서번호를 출력하시오(DISTINCT).

1. SELECT DISTINCT deptno FROM emp;

SQL> SELECT DISTINCT deptno FROM emp;

   DEPTNO
----------
       30
       20
       10

 

문제 10) EMP테이블에서 사원이름순으로 사원번호, 이름, 업무, 부서번호를 조회하시오(ORDER BY : 정렬 출력, 기본 오름차순).

1. 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; 내림차순 정렬).

1. 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

1. SELECT empno, ename, job, sal, hiredate, deptno FROM emp ORDER BY 2;      // Attribute 값 중 2번째(ename) 값을 기준으로 출력하라.
2. SELECT empno, ename "이름", job, sal, hiredate, deptno FROM emp ORDER BY "이름";      //별칭(ALIAS)을 기준으로 출력하라.

 

문제 12) EMP 테이블에서 SMISTH 사원의 모든 정보를 조회하시오(조건식 : WHERE 절 사용방법).

1. 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 절 사용방법).

1. 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 절 사용방법).

1. 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 절 사용방법).

1. 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

1. 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

 

1. select * from nls_session_parameters;   //현재 세션(현재창에서만)에 설정된 환경파라미터의 값들을 조회
2. alter session set nls_date_format='rrrr/mm/dd:hh24:mi:ss';  //날짜 관련 출력형식을 YYYY/MM/DD:24시:분:초 로 출력한다.
3. select * from nls_session_parameters;

 

문제 16) EMP 테이블에서 급여가 1300에서 1500사이의 사원의 성명, 담당업무, 급여, 부서번호를 출력하라.

1. SELECT ename, job, sal, deptno FROM emp WHERE sal BETWEEN 1300 AND 1500;
2. 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 인 사원의 사원번호, 성명, 담당업무, 급여, 입사일자를 출력하여라.

1. SELECT empno, ename, job, sal, hiredate FROM emp WHERE empno IN('7902', '7788', '7566');
2. 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년도에 입사한 사원의 사번, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하여라.

1. SELECT empno, ename, job, sal, hiredate, deptno FROM emp WHERE hiredate LIKE '1982%';
2. 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인 사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하여라.

1. 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

 

 

이 글은 스프링노트에서 작성되었습니다.