1. 아래의 SELECT 문장이 성공적으로 수행 될까요? ( 참 / 거짓 )
| SQL> SELECT ename 이름, job 업무, sal 급여 2 FROM emp; |
-------------------------------------------------------------------
답 - 참
SQL> select ename 이름, job 업무, sal 급여
2 from emp;
이름 업무 급여
-------------------- ------------------ ----------
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
이름 업무 급여
-------------------- ------------------ ----------
JAMES CLERK 950
FORD ANALYST 3000
MILLER CLERK 1300
14 개의 행이 선택되었습니다.
SQL>
2. 아래의 SELECT 문장이 성공적으로 수행 될까요? ( 참 / 거짓 )
| SQL> SELECT * 2 FROM salgrade; |
-------------------------------------------------------------------
답 - 참
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL>
3. 이 문장에 에러가 있습니다. 올바르게 작성하시오.
| SQL> SELECT empno, ename, salX12 년봉 2 FROM emp; |
-------------------------------------------------------------------
SQL> select empno, ename, salX12 년봉
2 from emp;
select empno, ename, salX12 년봉
*
1행에 오류:
ORA-00904: "SALX12": 부적합한 식별자
SQL> select empno, ename, sal*12 년봉
2 from emp;
EMPNO ENAME 년봉
---------- -------------------- ----------
7369 SMITH 9600
7499 ALLEN 19200
7521 WARD 15000
7566 JONES 35700
7654 MARTIN 15000
7698 BLAKE 34200
7782 CLARK 29400
7788 SCOTT 36000
7839 KING 60000
7844 TURNER 18000
7876 ADAMS 13200
EMPNO ENAME 년봉
---------- -------------------- ----------
7900 JAMES 11400
7902 FORD 36000
7934 MILLER 15600
14 개의 행이 선택되었습니다.
SQL>
4. 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
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------
SAL COMM DEPTNO
---------- ---------- ----------
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
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------
SAL COMM DEPTNO
---------- ---------- ----------
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
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------
SAL COMM DEPTNO
---------- ---------- ----------
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
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------
SAL COMM DEPTNO
---------- ---------- ----------
7902 FORD ANALYST 7566 81/12/03
3000 20
7934 MILLER CLERK 7782 82/01/23
1300 10
14 개의 행이 선택되었습니다.
SQL>
5. EMP 테이블에서 중복되지 않는 부서번호를 출력하시오.
-------------------------------------------------------------------
SQL> select distinct deptno from emp;
DEPTNO
----------
30
20
10
SQL>
6. EMP 테이블의 이름과 업무를 연결하여 출력하여라.
-------------------------------------------------------------------
SQL> select ename || ' ' || job as "Employees" from emp;
Employees
----------------------------------------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
Employees
----------------------------------------
JAMES CLERK
FORD ANALYST
MILLER CLERK
14 개의 행이 선택되었습니다.
SQL>
7. DEPT 테이블의 부서명과 위치를 연결하여 출력하여라.
-------------------------------------------------------------------
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select dname || ' ' || loc as "DEPT TABLE" from dept;
DEPT TABLE
--------------------------------------------------------
ACCOUNTING NEW YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON
SQL>
8. EMP 테이블의 업무와 급여를 연결하여 출력하여라.
-------------------------------------------------------------------
SQL> select job || ' ' || sal as SAL from emp;
SAL
--------------------------------------------------------------------------------
CLERK 800
SALESMAN 1600
SALESMAN 1250
MANAGER 2975
SALESMAN 1250
MANAGER 2850
MANAGER 2450
ANALYST 3000
PRESIDENT 5000
SALESMAN 1500
CLERK 1100
SAL
--------------------------------------------------------------------------------
CLERK 950
ANALYST 3000
CLERK 1300
14 개의 행이 선택되었습니다.
SQL>
'ORACLE > Excercise' 카테고리의 다른 글
| Oracle - 연습문제 #7 (0) | 2008.12.29 |
|---|---|
| Oracle - 연습문제 #6 (0) | 2008.12.29 |
| Oracle - 연습문제 #5 (0) | 2008.12.27 |
| Oracle - 연습문제 #4 (0) | 2008.12.27 |
| Oracle - 연습문제 #3 (0) | 2008.12.26 |
| Oracle - 연습문제 #2 (0) | 2008.12.26 |