Daseul's IT Blog

Q1) 'MARTIN'과 같은 급여를 받는 사원의 이름,사원번호,급여를 출력해 보세요.

SELECT ENAME, EMPNO, SAL
FROM EMP
WHERE SAL=(SELECT SAL FROM EMP WHERE ENAME='MARTIN');

ENAME                     EMPNO        SAL
-------------------- ---------- ----------
WARD                       7521       1250
MARTIN                     7654       1250

Q2) 최고급여를 받는 사원의 모든 정보를 출력하세요.

SELECT *
FROM EMP
WHERE SAL=(SELECT MAX(SAL) FROM EMP);

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      7839 KING                 PRESIDENT                     81/11/17       5000                    10

Q3) 사원번호가 7369인 사원과 같은 직업을 갖고, 7369인 사원보다 많은 급여를 받는 사원을 
모든정보를 조회하세요.(단일행)

SELECT *
FROM EMP
WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7369)
AND SAL>(SELECT SAL FROM EMP WHERE EMPNO=7369);

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      7900 JAMES                CLERK                    7698 81/12/03        950                    30
      7934 MILLER               CLERK                    7782 82/01/23       1300                    10

Q4) 부서별 최소급여가 30번부서의 최소급여보다 많은 부서번호와 해당 부서의 최소급여를 출력하세요.(복수행)

SELECT DEPTNO, MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL)>(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);

    DEPTNO   MIN(SAL)
---------- ----------
        10       1300

Q5) 'JONES'와 같은 부서에 근무하는 모든 사원의 부서번호,사원번호,사원이름,급여를 출력하시오.

SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='JONES');

    DEPTNO      EMPNO ENAME              SAL
---------- ---------- -------------------- ----------
        20       7369 SMITH                       800
        20       7566 JONES                      2975
        20       7902 FORD                       3000


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

1. 'ALLEN'의 직무와 같은 사람의 이름,급여,직무를 출력하라.

SELECT JOB FROM EMP WHERE ENAME='ALLEN';

JOB
------------------
SALESMAN

SELECT ENAME, SAL, JOB
FROM EMP
WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='ALLEN');

ENAME                       SAL JOB
-------------------- ---------- ------------------
ALLEN                      1600 SALESMAN
WARD                       1250 SALESMAN
MARTIN                     1250 SALESMAN
TURNER                     1500 SALESMAN

2. 전체 사원의 평균임금보다 많은 사원의 사원번호, 이름,입사일,급여를 출력하라.

SELECT EMPNO, HIREDATE, SAL
FROM EMP
WHERE SAL>ALL(SELECT AVG(SAL) FROM EMP);

     EMPNO HIREDATE        SAL
---------- -------- ----------
      7566 81/04/02       2975
      7698 81/05/01       2850
      7782 81/06/09       2450
      7839 81/11/17       5000
      7902 81/12/03       3000

3. 10번 부서 사람들중에 20번 부서의 사원과 같은 업무를 하는 사원의 사원번호,업무,이름,
입사일을 출력하시오.
 
SELECT EMPNO, JOB, ENAME
FROM EMP
WHERE DEPTNO=10 AND JOB IN(SELECT JOB FROM EMP WHERE DEPTNO=20);

     EMPNO JOB                ENAME
---------- ------------------ --------------------
      7934 CLERK              MILLER
      7782 MANAGER            CLARK

4. 10번 부서중에서 30번 부서에는 없는 업무를 하는 사원의 사원번호,업무,이름,입사일을 출력하시오.
(단일행 결과 어떻게 아는지)
SELECT EMPNO, JOB, ENAME, HIREDATE
FROM EMP
WHERE DEPTNO=10 AND JOB IN(SELECT JOB FROM EMP WHERE DEPTNO!=30);

SELECT EMPNO, JOB, ENAME, HIREDATE
FROM EMP
WHERE DEPTNO=10 AND JOB!=ALL(SELECT JOB FROM EMP WHERE DEPTNO=30);

     EMPNO JOB                ENAME                HIREDATE
---------- ------------------ -------------------- --------
      7839 PRESIDENT          KING                 81/11/17

5. 10번 부서와 같은 일을 하는 사원의 사원번호,업무,이름,부서번호,급여를 급여가 많은 순으로 출력하라.

SELECT EMPNO, JOB, ENAME, DEPTNO, SAL
FROM EMP
WHERE JOB=ANY(SELECT JOB FROM EMP WHERE DEPTNO=10) 
ORDER BY SAL DESC;

(ALL이 참이되는 경우는 DEPTNO=10 의 인자들 직업이 모두 같을 경우만 성립)

     EMPNO JOB                ENAME                    DEPTNO        SAL
---------- ------------------ -------------------- ---------- ----------
      7839 PRESIDENT          KING                         10       5000
      7566 MANAGER            JONES                        20       2975
      7698 MANAGER            BLAKE                        30       2850
      7782 MANAGER            CLARK                        10       2450
      7934 CLERK              MILLER                       10       1300
      7900 CLERK              JAMES                        30        950
      7369 CLERK              SMITH                        20        800

6.'MARTIN' 이나 'SCOTT'의 급여와 같은 사원의 사원번호,이름,급여를 출력하라.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL=ANY(SELECT SAL FROM EMP WHERE ENAME='MARTIN' OR ENAME='SCOTT');

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7654 MARTIN                     1250
      7521 WARD                       1250

7. 급여가 30번 부서의 최고 급여보다 높은 사원의 사원번호,이름,급여를 출력하라.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL>(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30);

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7566 JONES                      2975
      7839 KING                       5000
      7902 FORD                       3000

8. 급여가 30번 부서의 최저 급여보다 낮은 사원의 사원번호,이름,급여를 출력하라.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL<(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7369 SMITH                       800

9. 업무별로 최소 급여를 받는 사원의 정보를 사원번호,이름,업무,부서번호를 출력하시오. 
단 업무별로 정렬하시오.

복수행서브쿼리
SELECT MIN(SAL) FROM EMP GROUP BY JOB;

  MIN(SAL)
----------
       800
      1250
      5000
      2450
      3000

SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE (JOB,SAL) IN(SELECT JOB,MIN(SAL) FROM EMP GROUP BY JOB)
ORDER BY JOB;

     EMPNO ENAME                JOB                    DEPTNO
---------- -------------------- ------------------ ----------
      7902 FORD                 ANALYST                    20
      7369 SMITH                CLERK                      20
      7782 CLARK                MANAGER                    10
      7839 KING                 PRESIDENT                  10
      7521 WARD                 SALESMAN                   30
      7654 MARTIN               SALESMAN                   30

10. 부서별로 최소 급여를 받는 사원의 사원번호,이름,부서번호,급여를 출력하시오.

복수행서브쿼리
SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO;

  MIN(SAL)
----------
       950
       800
      1300

SELECT EMPNO, ENAME, DEPTNO, SAL
FROM EMP
WHERE (DEPTNO,SAL)=ANY(SELECT DEPTNO,MIN(SAL) FROM EMP GROUP BY DEPTNO);

     EMPNO ENAME                    DEPTNO        SAL
---------- -------------------- ---------- ----------
      7369 SMITH                        20         800
      7900 JAMES                        30         950
      7934 MILLER                       10       1300

퀴즈3 해답

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%';

과제

*과제 가이드

<약속문자를준다 >

예를 들어,

클라이언트가 입장을 했다 -> 메세지 앞에 '1'을 붙인다
'1:길동이'

일반 채팅메세지 인경우;
'2: 안녕'

나간 경우
'3: 안녕'

앞에 숫자별로 split으로 쪼갠다
2인경우 왼쪽/ 3인경우 오른쪽/

1. 디렉토리 복사하는 프로그램을 작성해 보세요.(조금 어려워요..)
   예)
   복사할 폴더
   c:\java
   복사본 폴더
   c:\java_copy
   java폴더가 java_copy폴더에 복사됨 


2. JTextField로 학생번호 이름 국어점수 영어점수를 입력받아 
총점,평균을 계산해서 student.txt파일로 저장되도록 해보세요.