25일차 오라클 명령어 퀴즈1,2

오라클 DB 문제1.txt

0.00MB

오라클 DB 문제2.txt

0.00MB

1. 부서번호가 10번인 부서의 사람중 사원번호, 이름,월급,부서번호를 출력하여라.
SQL>
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM EMP
WHERE DEPTNO=10;

     EMPNO ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
      7782 CLARK                      2450         10
      7839 KING                       5000         10
      7934 MILLER                     1300         10

2. 사원번호가 7369인 사람중 이름,입사일,부서번호를 출력하라.
SQL>
SELECT ENAME,HIREDATE,DEPTNO
FROM EMP
WHERE EMPNO=7369;

ENAME                HIREDATE     DEPTNO
-------------------- -------- ----------
SMITH                80/12/17         20

3. 이름이 ALLEN 인 사람의 모든 정보를 출력하라.
SQL>

SELECT *
FROM EMP
WHERE ENAME='ALLEN'; 

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      7499 ALLEN                SALESMAN                 7698 81/02/20       1600        300         30


4. 입사일이 '81/05/01'인 사원의 이름,부서번호,월급을 출력하라.
SQL>

SELECT ENAME,DEPTNO,SAL
FROM EMP
WHERE HIREDATE=81/05/01;  

5. 직업이 MANAGER 가 아닌 사람의 모든 정보를 출력하라.
SQL>

SELECT * 
FROM EMP
WHERE JOB!='MANAGER';

     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
      7654 MARTIN               SALESMAN                 7698 81/09/28       1250       1400         30
      7839 KING                 PRESIDENT                     81/11/17       5000                    10
      7844 TURNER               SALESMAN                 7698 81/09/08       1500          0         30
      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

6. 급여가 800 이상인 사람의 이름,급여,부서번호를 출력하라.

SELECT ENAME,SAL,DEPTNO
FROM EMP
WHERE SAL>800; 

ENAME                       SAL     DEPTNO
-------------------- ---------- ----------
ALLEN                      1600         30
WARD                       1250         30
JONES                      2975         20
MARTIN                     1250         30
BLAKE                      2850         30
CLARK                      2450         10
KING                       5000         10
TURNER                     1500         30
JAMES                       950         30
FORD                       3000         20
MILLER                     1300         10

7. 부서번호가 20번 이상인 사원의 모든 정보를 이름순으로 정렬해서 출력하라.

SELECT *
FROM EMP
WHERE DEPTNO>=20 
ORDER BY ENAME;

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      7499 ALLEN                SALESMAN                 7698 81/02/20       1600        300         30
      7698 BLAKE                MANAGER                  7839 81/05/01       2850                    30
      7902 FORD                 ANALYST                  7566 81/12/03       3000                    20
      7900 JAMES                CLERK                    7698 81/12/03        950                    30
      7566 JONES                MANAGER                  7839 81/04/02       2975                    20
      7654 MARTIN               SALESMAN                 7698 81/09/28       1250       1400         30
      7369 SMITH                CLERK                    7902 80/12/17        800                    20
      7844 TURNER               SALESMAN                 7698 81/09/08       1500          0         30
      7521 WARD                 SALESMAN                 7698 81/02/22       1250        500         30

8. 10번 부서의 모든 사람들에게 급여의 13%를 보너스로 지불하기로 하였다. 이름,급여,보너스금액,부서번호를 출력하라
SQL>

SELECT ENAME,SAL,SAL*0.13 보너스,DEPTNO
FROM EMP
WHERE DEPTNO=10;

ENAME                       SAL     보너스     DEPTNO
-------------------- ---------- ---------- ----------
CLARK                      2450      318.5         10
KING                       5000        650         10
MILLER                     1300        169         10

 

 

Q1) 직업이 'MANAGER'이거나 또는 'SALESMAN'인 사원의 이름과 직업을 출력(IN사용)
SELECT ENAME,JOB
FROM EMP
WHERE JOB='MANAGER' OR JOB='SALESMAN';

ENAME                JOB
-------------------- ---------
ALLEN                SALESMAN
WARD                 SALESMAN
JONES                MANAGER
MARTIN               SALESMAN
BLAKE                MANAGER
CLARK                MANAGER
TURNER               SALESMAN

Q2) 이름이 'S'로 시작하는 사원의 번호,이름,입사일,부서번호 출력

SELECT EMPNO,ENAME,HIREDATE,DEPTNO
FROM EMP
WHERE ENAME LIKE 'S%';

     EMPNO ENAME                HIREDATE     DEPTNO
---------- -------------------- -------- ----------
      7369 SMITH                80/12/17         20

Q3) 입사일이 81년도인 사원의 입사일,이름 출력

SELECT HIREDATE,ENAME
FROM EMP
WHERE HIREDATE LIKE '81%';

HIREDATE ENAME
-------- --------------------
81/02/20 ALLEN
81/02/22 WARD
81/04/02 JONES
81/09/28 MARTIN
81/05/01 BLAKE
81/06/09 CLARK
81/11/17 KING
81/09/08 TURNER
81/12/03 JAMES
81/12/03 FORD

Q4) 이름이 'A'로 시작하고 마지막글자가 'N'인 사원의 모든 정보를 출력

SELECT * 
FROM EMP
WHERE ENAME LIKE 'A%N';

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      7499 ALLEN                SALESMAN                 7698 81/02/20       1600        300         30

Q5) 이름의 두번째 문자가 'A'인 사원의 이름을 출력하시오.
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '_A%';

ENAME
--------------------
WARD
MARTIN
JAMES

Q6) 이름의 첫글자가 'A'로 시작하거나 부서번호가 30인 사원의 사원번호,이름,
    부서번호를 출력
SELECT EMPNO,ENAME,DEPTNO
FROM EMP
WHERE ENAME LIKE 'A%' OR DEPTNO=30;

     EMPNO ENAME                    DEPTNO
---------- -------------------- ----------
      7499 ALLEN                        30
      7521 WARD                         30
      7654 MARTIN                       30
      7698 BLAKE                        30
      7844 TURNER                       30
      7900 JAMES                        30

Q7) 입사일이 81년도가 아닌 사원들의 입사일과 이름을 출력

SELECT HIREDATE,ENAME
FROM EMP
WHERE HIREDATE NOT LIKE '81%';

SQL> SELECT HIREDATE,ENAME
  2  FROM EMP
  3  WHERE HIREDATE NOT LIKE '81%';