SELECT, 정렬

<오라클 설치 및 Scott 계정으로 로그인하기>

 
1. 오라클 설치
2. scott 계정 설정(DB 공간 대여 개념)
1) splplus system/oracle //SYSTEM 계정으로 SQL*Plus
2) @C:\oracleexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql
//교육용 내장 계정인 scott 계정을 사용하겠다고 명령
3) show user; //USER is "SCOTT"이란 말이 뜨면 성공
4) alter user scott identified by tiger; //tiger로 비밀번호 변경
5) exit
6) sqlplus scott/tiger 로 다시 접속
 

<자료형, SELECT>

 
1.  
sqlplus scott/tiger //scott 계정이 가지고 있는 tablespace로 실습
2.  
select * from tab; //mysql> show tables;
3.  
desc emp; //dept 라는 table 구조 보기
4.  
대표적인 자료형
   NUMBER(PRECISION, SCALE)
   숫자를 저장하기 위한 데이터형
   PRECISION: 소수점을 포함한 전체 자리수
   SCALE: 소수점 이하 자리수
   예) 72.5는 NUMBER(3, 1), 10은 NUMBER(2), 7788은 NUMBER(4), 어떤 숫자도 상관없다면 NUMBER
 
   DATE
   날짜 및 시간 데이터를 저장하기 위한 데이터형
 
   VARCHAR2(숫자)
   가변적인 문자열을 저장하기 위한 데이터형
   괄호 안은 바이트 수를 의미한다.
   만약 칼럼의 데이터형이 VARCHAR2(15)라면
   최대 15바이트의 문자열을 저장할 수 있다.
   (오라클 캐릭터셋이 KO16KSC5601이나
   KO16MSWIN949이면 한글은 2바이트, 영어는 1바이트이다.
   UTF8이나 AL32UTF8이면 한글은 3바이트, 영어는 1바이트이다)
 
CHAR(자바의 Char와는 다름)
고정길이 문자 데이터를 저장하기 위한 자료형
CHAR(20) 20바이트의 고정 길이를 저장 공간으로 할당(저장 공간을 아끼기 위해 VARCHAR2를 사용한다)
 
LONG
2기가 바이트의 가변 길이 문자 저장
VARCHAR2와 유사하나 다음의 제한 사항 있음.
1.  
하나의 테이블에 하나의 LONG 타입만 사용 가능
2.  
(NOT) NULL을 제외한 다른 제약 조건은 지정 불가
3.  
인덱스 못 만듬
 
5. emp 테이블 구조
 
EMPNO(사원번호)
ENAME(이름)
JOB(직책)
MGR(직속상사 사원번호)
HIREDATE(입사일)
SAL(급여)
COMM(커미션)
DEPTNO(부서 번호)
 
 
6. EMP 테이블에 담긴 모든 사원 레코드를 조회해보자
select * from emp;
 
7. SELECT 구조
SELECT 칼럼, 칼럼…
FROM 테이블명
WHERE 조건
GROUP BY 구문
UNION/UNION ALL/INTERSECT/MINUS 구문
ORDER BY 구문
 
8. 연습 문제
 
1) 이름과 입사일을 조회하시오.
select ENAME, HIREDATE from emp;
*select ENAME 이름 from emp(ENAME 칼럼을 이름으로 바꾸어 출력)
 
2) 사원번호와 이름을 조회하시오.
select EMPNO, ENAME from emp;
 
3) 총 사원수를 구하시오.
select count(*) from emp;
 
4) 부서번호가 10인 사원을 조회하시오.
힌트) 위 SQL문은 where 조건을 사용
select * from emp where DEPTNO=10;
 
5) 월 급여가 2500 이상 되는 사원을 조회하시오.
힌트) where 조건에 =,>,>=,<=,<  비교 연산자를 사용
select * from emp where SAL>=2500;
 
6) 사원들 중 이름이 S로 시작하는 사원의 사원번호와 이름을 조회하시오.
힌트) LIKE는 %와 _문자와 함께 검색시 사용
select EMPNO, ENAME from emp where ENAME LIKE ‘S%’;
 
•  
where ENAME LIKE ‘____S’;(_ 네 개)
=> JONES, JAMES만 출력됨
 
7) 사원 이름에 T가 포함된 사원의 사원번호와 이름을 조회하시오.
select * from emp where ENAME LIKE ‘%T%’;
 
8-1) 커미션이 300, 500, 1400인 사원의 사원번호, 이름, 커미션을 조회하시오.
힌트) 2가지 경우의 SQL을 만드세요. WHERE 절에 OR과 IN()을 사용
ㄱ) select EMPNO, ENAME, COMM from emp where COMM=300 or COMM=500 or COMM=1400;
ㄴ) select EMPNO, ENAME, COMM from emp where COMM IN(300, 500, 1400);
 
8-2) 커미션이 300, 500, 1400이 아닌 사원을 NOT IN()으로 조회 
select * from emp where COMM not in(300, 500, 1400);
9) 부서번호가 10번이 아닌 사원을 조회하세요.
.  
비교연산자를 이용하는 방법
select * from emp where DEPTNO <>10;
select * from emp where DEPTNO !=10;
select * from emp where DEPTNO ^=10;
.  
Not을 이용
select * from emp where DEPTNO not in(10);
 
10) 급여가 2000 이상 AND 3000 이하인 직원을 모두 조회하세요.
.  
비교연산자를 이용
   select * from emp where SAL>=2000 AND SAL<=3000;
.  
BETWEEN
   select * from emp where SAL 2000 BETWEEN 3000; //값 between 값
11)  급여가 2000이하이거나  3000 이상인 직원을 모두 조회하세요.
select * from emp where SAL not BETWEEN 2000 AND 3000;   
 
HW) 사원의 실제 급여는 SAL*10이다. 사원 이름과 컴미션,
사원의 실제급여, 사원의 실제급여+커미션을 조회하세요.
a) COMM에 NULL 때문에 실제급여 + 커미션이 되지 않는다.
NULL도 중요한 데이터로 취급되기 때문에
nvl() 내장함수로 NULL을 0으로 바꾸고 계산: nvl(null 있는 칼럼, 0)
*COMM을 NVL으로 씌워준다.
*NVL(Null Value Logic)
 
SELECT ENAME, NVL(COMM, 0), SAL*10, SAL*10+NVL(COMM, 0) from emp;
 
 
 

<시스템 변수 조작, 고급 검색, 정렬>

 
1.  
SQLPLUS 시스템 변수 조작

SET HEADING OFF //칼럼 제목 출력 끄기  
SET HEADING ON //칼럼 제목 출력  
SET LINESIZE 100 //ROW의 길이를 100으로 조정(기본80)
SET PAGESIZE 20 //PAGE 크기를 20으로 조정(기본14)
  
COLUMN ENAME FORMAT A25
//문자 형식의 칼럼이 출력하는 길이를 25자리로 변경
  
COLUMN SAL FORMAT 9,999,999
   //숫자는 길이가 자동으로 변경되므로 길이 조정이 필요 없음
   //대신 표현 방법을 바꿀 수 있다.
   //예) 1400 => 1,400
  
COLUMN COMM FORMAT 0,000,000 //예) 1400 => 0,001,400
 
2. 연습 문제
 
1.  
이름이 A를 포함하는 사원을 모두 찾으세요.
SELECT ENAME FROM EMP WHERE ENAME LIKE ‘%A%’;
2.  
이름의 두 번째 글자가 A인 사원을 찾으세요.
SELECT ENAME FROM EMP WHERE ENAME LIKE ‘_A%’;
 
3.  
*UPDATE EMP SET ENAME=’A_LLEN’ WHERE EMPNO=7499;
입력한 후,  이름에 ‘A’를 포함하는 사원을 찾으세요.
a.ESCAPE를 사용해 ‘_’를 문자로 인식하도록 하세요.
SELECT ENAME FROM EMP
WHERE ENAME LIKE ‘%A\_%’ ESCAPE ‘\’;
   //문자로 인식할 부분 앞에 \를 쓴다.
 
4) 이름에 ‘A’를 포함하지 않는 사원을 찾으세요.
SELECT ENAME FROM EMP WHERE ENAME not LIKE ‘%A%’;
 
5) 커미션이 NULL인 사원을 검색하세요.
a.NULL은 연산, 할당, 비교가 불능
SELECT ENAME FROM EMP WHERE NVL(COMM,0)=0;
SELECT ENAME FROM EMP WHERE COMM IS NULL; //두 가지 방법
 
6) 커미션이 NULL이 아닌 사원을 검색하세요.
SELECT ENAME FROM EMP WHERE NVL(COMM,0)<>0;
SELECT ENAME FROM EMP WHERE COMM IS NOT NULL;
 
*정렬을 위한 ORDER BY:ASC, DESC
 
7) 급여 칼럼을 기준으로 사원을 오름차순으로 정렬하세요.
SELECT ENAME, SAL FROM EMP ORDER BY SAL ASC;
//ASC가 default이므로 안 써도 무방
 
8) 사원의 이름을 알파벳 순으로 정렬하세요.
SELECT ENAME FROM EMP ORDER BY ENAME ASC;
 
9) 오래 입사한 순으로 사원을 정렬하세요.
SELECT HIREDATE FROM EMP ORDER BY HIREDATE ASC;
10) 급여를 많이 받는 순으로 사원을 정렬할 때 급여가 같은 사람이 존재할 경우
이름의 철자가 빠른 사람부터 정렬하세요.
SELECT SAL, ENAME FROM EMP ORDER BY SAL DESC, ENAME ASC;