ORACLE SQL 기초 SELECT문 SCOTT 문제 풀이

SELECT

<<1>>  부서 번호가 10번인 부서의 사람 중 사원번호, 이름, 급여를 출력하라.
 SQL>             

SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10;

<<2>>  사원번호가 7369인 사람 중 이름, 입사일자, 부서번호를 출력하라.
 SQL>

SELECT ENAME, HIREDATE, DEPTNO, EMPNO
FROM EMP
WHERE EMPNO=7369;

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

SELECT *
FROM EMP
WHERE ENAME='ALLEN';

<<4>>  입사일자가 82/01/23인 사원의 이름, 부서 번호, 급여를 출력하라.
 SQL> 

SELECT ENAME, DEPTNO, SAL, HIREDATE
FROM EMP
WHERE TO_CHAR(HIREDATE, 'YYYYMMDD') = '19820123';

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

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

<<6>>  입사일자가 81/04/02 이후에 입사한 사원의 정보를 출력하라.
 SQL> 

SELECT *
FROM EMP
WHERE TO_CHAR(HIREDATE,'YYYYMMDD') >= '19810402';

<<7>>  급여가 $800이상인 사람의 이름, 급여, 부서 번호를 출력하라.
 SQL> 

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

<<8>>  부서번호가 20번 이상인 사원의 모든 정보를 출력하라.
 SQL> 

SELECT *
FROM EMP
WHERE DEPTNO >=20;

<<9>>  성명이 K로 시작하는 사람보다 높은 이름을 가진 사람의 모든 정보를 출력하라.
 SQL> 

SELECT*
FROM EMP
WHERE ENAME LIKE 'K%'

   
<<10>>  입사일자가 81/12/09 보다 먼저 입사한 사람들의 모든 정보를 출력하라.
 SQL>

SELECT*
FROM EMP
WHERE TO_CHAR(HIREDATE, 'YYYYMMDD') < '19811209';

<<11>>  입사번호가 7698 보다 작거나 같은 사람들의 입사번호와 이름을 출력하라.
 SQL> 

SELECT EMPNO, ENAME, EMPNO
FROM EMP
WHERE EMPNO<=7698;

<<12>>  입사일자가 81/04/02 보다 늦고 82/12/09 보다 빠른 사원의 이름, 월급, 부서 번호를 출력하라.
 SQL> 

SELECT ENAME, SAL, EMPNO, HIREDATE
FROM EMP
WHERE TO_CHAR(HIREDATE, 'YYYYMMDD') > '19810402' AND TO_CHAR(HIREDATE, 'YYYYMMDD') < '19821209';

<<13>>  급여가 1,600 보다 크고 $3,000보다 작은 사람은 이름, 직무, 급여를 출력하라.
 SQL> 

SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL>1600 AND SAL <3000;

<<14>>  사원번호가 7654와 7782 사이 이외의 사원의 모든 정보를 출력하라.
 SQL> 

SELECT *
FROM EMP
WHERE EMPNO <7654 OR EMPNO>7782;

<<15>> 이름이 B와 J 사이의 모든 사원의 정보를 출력하라.
 SQL> 

SELECT *
FROM EMP
WHERE ENAME LIKE 'B%' OR ENAME LIKE 'C%' OR ENAME LIKE 'D%' OR ENAME LIKE 'E%' OR ENAME LIKE 'F%' OR ENAME LIKE 'G%' OR ENAME LIKE 'H%' OR ENAME LIKE 'I% OR ENAME LIKE 'J%';

SELECT *
FROM EMP
WHERE ENAME BETWEEN 'A' AND 'K';

-- 문자의 경우에 아스키코드에 따라서 계산된다.

'B' AND 'J'로 할 경우 'J' 의 아스키코드보다 큰 값을 가지는 JA~, JB~ 등은 검색되지 않는다. 따라서 바로 다음에 오는 문자인 'K'로 검색해야 한다.


<<16>>  입사일자가 81년 이외에 입사한 사람의 모든 정보를 출력하라.
 SQL> 

SELECT*
FROM EMP
WHERE TO_CHAR(HIREDATE, 'YYYY') != '1981' ;

 

<<17>>  직무가 MANAGER와 SALESMAN인 사람의 모든 정보를 출력하라.
 SQL> 

SELECT *
FROM EMP
WHERE JOB = 'MANAGER' OR JOB = 'SALESMAN';

<<18>>  부서 번호와 20,30번을 제외한 모든 사람의 이름, 사원번호, 부서 번호를 출력하라.
 SQL> 

SELECT ENAME, EMPNO, DEPTNO
FROM EMP
WHERE DEPTNO != 20 AND DEPTNO != 30;

-- OR (X) -> AND(O)

 

<<19>>  이름이 S로 시작하는 사원의 사원번호, 이름, 입사일자, 부서번호를 출력하라.
 SQL> 

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

<<20>>  입사일자가 81년도인 사람의 모든 정보를 출력하라.
 SQL> 

SELECT *
FROM EMP
WHERE TO_CHAR(HIREDATE, 'YYYY') = 1981;

<<21>>  이름 중 S자가 들어가 있는 사람만 모든 정보를 출력하라.
 SQL> 

SELECT*
FROM EMP
WHERE ENAME LIKE '%S%';

<<22>>  이름이 K로 시작하고 마지막 글자가 G인 사람의 모든 정보를 출력하라
 SQL>             

SELECT *
FROM EMP
WHERE ENAME LIKE 'K%' AND ENAME LIKE '%G';

<<23>>  첫 번째 문자는 관계없고 두 번째 문자가 A인 사람의 정보를 출력하라.
 SQL>

SELECT *
FROM EMP
WHERE ENAME LIKE '_A%';

<<24>>  커미션이 NULL인 사람의 정보를 출력하라.
 SQL>

SELECT *
FROM EMP
WHERE COMM IS NULL;

-- WHERE COMM=NULL (X) -> WHERE COMM IS NULL(O)


<<25>>  커미션이 NULL이 아닌 사람의 모든 정보를 출력하라.
 SQL> 

--방법1--
SELECT *
FROM EMP
WHERE COMM IS NOT NULL;

--방법2--
SELECT *
FROM EMP
WHERE COMM>=0;

<<26>>  부서가 30번 부서이고 급여가 $1,500 이상인 사람의 이름, 부서, 월급을 출력하라.
 SQL> 

SELECT ENAME, DEPTNO, SAL
FROM EMP
WHERE DEPTNO=30 AND SAL >=1500;

<<27>>  이름의 첫 글자가 K로 시작하거나 부서 번호가 30인 사람의 사원번호, 이름, 부서 번호를 출력하라.
 SQL> 

SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE ENAME LIKE 'K%' OR DEPTNO=30;

<<28>>  급여가 $1,500이상이고 부서 번호가 30번인 사워 중 직업이 MANAGER인 사람의 정보를 출력하라.
 SQL> 

SELECT *
FROM EMP
WHERE SAL>=1500 AND DEPTNO=30 AND JOB='MANAGER';

<<29>>  부서 번호가 30인 사람 중 사원번호를 SORT 하라.
 SQL> 

SELECT EMPNO
FROM EMP
WHERE DEPTNO =30
ORDER BY ASC;

 
<<30>>  급여가 많은 순으로 SORT하라.
 SQL> 

SELECT ENAME, SAL
FROM EMP
ORDER BY SAL ASC;

<<31>>  부서 번호로 ASCENDING SORT한 후 급여가 많은 사람 순으로 출력하라.
 SQL> 

SELECT ENAME, DEPTNO, SAL
FROM EMP
ORDER BY DEPTNO, SAL ASC;

<<32>>  부서 번호로 DESCENDING NSORT하고 이름 순으로 ASCENDING SORT, 급여 순으로 DESCENDING SORT하라.
 SQL> 

SELECT DEPTNO, ENAME, SAL
FROM EMP
ORDER BY DEPTNO DESC, ENAME ASC, SAL DESC;