[SQL 입문] DQL(DATA QUERY LANGUAGE) - 데이터 질의어 -SELECT, DISTINCT, ORDER BY, WHERE

⬛워크시트 설명문 처리

 

⚫워크시트에서 작성된 SQL 명령을 접속된 오라클 서버에 전달하여 실행하는 방법
🔘[CTRL]+[ENTER] : 커서 위치의 SQL 명령을 전달하여 실행
🔘[F5] : 워크시트에 작성된 모든 SQL 명령을 전달하여 실행
🔘마우스 또는 키보드로 범위를 지정하여 [CTRL]+[ENTER] 또는 [F5]를 사용하면 설정된 범위의 SQL 명령을 전달하여 실행
🔘워크시트에서 작성된 전달 SQL 명령의 결과는 [스크립트 출력] 또는 [질의결과] 탭에 출력
  
⚫SQL 명령은 대소문자를 구분하지 않으며 워크시트에 하나의 명령로 인식되어 처리하기 위해 ; 기호 사용
⚫현재 오라클 서버에 접속된 사용자의 이름을 확인하기 위한 오라클 시스템 명령
      SHOW USER;

⚫테이블(TABLE) : 테이타베이스에서 데이타(행)를 저장하기 위한 가장 기본적인 객체(ENTITY)

                                                                                                                                  - 데이타베이스의 기본 저장단위
⚫현재 접속 사용자가 접근 가능한 내부 스키마(사용자 스키마)에 존재하는 테이블 목록 확인
        SELECT TABLE_NAME FROM USER_TABLES;
        SELECT * FROM TAB;

⚫테이블의 구조 확인 : 테이블 속성(ATTRIBUTE) - 컬럼명과 자료형
형식) DESC 테이블명
🔘EMP 테이블 : 사원정보를 저장하기 위한 테이블
DESC EMP;
🔘DEPT 테이블 : 부서정보를 저장하기 위한 테이블
DESC DEPT;

⚫DQL(DATA QUERY LANGUAGE) : 데이타 질의어 - 테이블에 저장된 행을 검색하기 위한 SQL 명령
🔘SELECT : 하나 이상의 테이블에서 행을 검색하기 위한 SQL 명령
--형식) SELECT 검색대상,검색대상,... FROM 테이블명
--검색대상 :  *(모든 컬럼) - 다른 검색대상과 동시에 사용 불가능, 컬럼명, 연산식, 함수 등

SELECT * FROM EMP;
SELECT EMPNO,ENAME,SAL FROM EMP;
SELECT EMPNO,ENAME,SAL*12 FROM EMP;

🔘COLUMN ALIAS : 검색대상에 별칭(임시로 사용하는 컬럼명)을 부여하는 기능 
--검색대상을 명확하게 구분하기 위해 COLUMN ALIAS 기능 사용
--형식) SELECT 검색대상 AS 별칭,검색대상 AS 별칭,... FROM 테이블명

SELECT EMPNO,ENAME,SAL FROM EMP;
SELECT EMPNO AS NO,ENAME AS NAME,DEPTNO AS DNO FROM EMP;
--COLUMN ALIAS 기능에서 AS 키워드 생략 가능
SELECT EMPNO NO,ENAME NAME,DEPTNO DNO FROM EMP;
SELECT EMPNO 사원번호,ENAME 사원이름,DEPTNO 부서번호 FROM EMP;

--검색대상에 연산식 대신 COLUMN ALIAS 기능을 사용하여 검색
SELECT EMPNO,ENAME,SAL*12 FROM EMP;
SELECT EMPNO,ENAME,SAL*12 ANNUAL FROM EMP;

 

--SQL 명령은 예약어(키워드)와 사용자 정의 명칭(식별자)의 단어로 구성
--식별자(테이블명, 컬럼명, 별칭 등)은 스네이크 표기법(단어와 단어를 [_]로 구분하여 표현)을 사용하여 작성

SELECT EMPNO,ENAME,SAL*12 ANNUAL_SALARY FROM EMP;

 

 

--식별자로 사용 불가능한 단어를 사용하여 에러 발생

SELECT EMPNO 사원 번호,ENAME USER,SAL*12 ^연봉^ FROM EMP;

--식별자 작성규칙에 맞지 않은 단어(키워드 포함)를 식별자로 사용하고자 할 경우 " " 기호를 사용하여 표현 가능

SELECT EMPNO "사원 번호",ENAME "USER",SAL*12 "^연봉^" FROM EMP;

--검색대상은 , 기호를 사용하여 구분 검색

SELECT ENAME, JOB FROM EMP;

--검색대상에 || 기호를 사용하면 문자값이 결합된 결과를 제공

--SQL 명령에서 문자값은 ' ' 기호 사용햐여 표현

SELECT ENAME||JOB FROM EMP;
SELECT ENAME||'님의 업무는 '||JOB||'입니다.' FROM EMP;
--질의 결과 예시: SMITH님의 업무는 CLERK입니다.

-- EMP 테이블에 저장된 모든 사원들의 업무 검색 - 중복 컬럼값 검색

SELECT JOB FROM EMP;

--EMP 테이블에 저장된 모든 사원의 업무 검색 - 중복 컬럼값을 제외한 유일한 하나의 컬럼값만 검색
🔘 DISTINCT : 검색대상의 중복값을 제외한 유일한 하나의 컬럼값만 검색하는 기능을 제공하는 키워드
--형식) SELECT DISTINCT 검색대상,검색대상,... FROM 테이블명

SELECT DISTINCT JOB FROM EMP;

 

--오라클에서는 DISTINCT 키워드를 사용하여 검색대상을 여러개 나열하여 작성 가능 - 나열된 검색대상이 모두 중복되지 않는 행 검색

SELECT DISTINCT JOB,DEPTNO FROM EMP;

🔘WHERE : 조건식을 사용하여 조건식의 결과가 참(TRUE)인 행만 검색하는 기능을 제공하는 키워드
--형식) SELECT 검색대상,검색대상,... FROM 테이블명 WHERE 조건식
--조건식은 컬럼값을 비교연산자로 비교값과 비교하는 연산식을 작성하여 사용

--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP;

--EMP 테이블에서 사원번호가 7698인 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE EMPNO=7698;

--EMP 테이블에서 사원이름가 KING인 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME='KING';
--SQL 명령은 대소문자를 구분하지 않지만 문자값은 대소문자를 구분하여 처리
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME='king';

--EMP 테이블에서 입사일이 1981년 6월 9일인 사원의 사원번호,사원이름,업무,급여,입사일 검색
--날짜값은 기본적으로 ' ' 안에 [RR/MM/DD] 형식의 패턴으로 표현하여 사용
SELECT EMPNO,ENAME,JOB,SAL,HIREDATE FROM EMP WHERE HIREDATE='81/06/09';
--날짜값은 ' ' 안에 [YYYY-MM-DD] 형식의 패턴으로 표현하여 사용 가능
SELECT EMPNO,ENAME,JOB,SAL,HIREDATE FROM EMP WHERE HIREDATE='1981-06-09';

--EMP 테이블에서 업무가 SALESMAN이 아닌 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB<>'SALESMAN';
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB!='SALESMAN';

--EMP 테이블에서 급여가 2000이상인 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL>=2000;

--EMP 테이블에서 사원이름이 A,B,C로 시작되는 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME<'D';

--EMP 테이블에서 입사일이 1981년 5월 1일 이전에 입사한 사원의 사원번호,사원이름,업무,급여,입사일 검색
SELECT EMPNO,ENAME,JOB,SAL,HIREDATE FROM EMP WHERE HIREDATE<'81/05/01';

--EMP 테이블에서 업무가 SALESMAN인 사원 중 급여가 1500 이상인 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB='SALESMAN' AND SAL>=1500;

--EMP 테이블에서 부서번호가 10 이거나 업무가 MANAGER인 사원의 사원번호,사원이름,업무,급여,부서번호 검색
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP WHERE DEPTNO=10 OR JOB='MANAGER';

--EMP 테이블에서 급여가 1000~3000 범위의 포함되는 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL>=1000 AND SAL<=3000;

--SQL에서는 BETWEEN 키워드를 사용하여 컬럼값이 작은값부터 큰값 범위에 포함될 경우 검색하는 조건식 작성
--형식) 컬럼명 BETWEEN 작은값 AND 큰값
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL BETWEEN 1000 AND 3000;

--EMP 테이블에서 사원의 업무가 ANALYST 이거나 SALESMAN인 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB='ANALYST' OR JOB='SALESMAN';

--SQL에서는 IN 키워드를 사용하여 컬럼값이 나열된 값들 중 하나인 경우 검색하는 조건식 작성
--형식) 컬럼명 IN (값,값,...)
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB IN ('ANALYST','SALESMAN');

--EMP 테이블에서 사원이름이 BLAKE인 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME='BLAKE';

--EMP 테이블에서 사원이름이 B로 시작되는 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME>='B' AND ENAME<'C';

--조건식에는 검색패턴문자를 이용해 컬럼값을 비교하여 검색 가능
--검색패턴문자 : [%] - 전체, [_] - 임의의 문자 하나
--조건식에서 [=] 연산자를 사용하여 컬럼값을 비교할 경우 [%] 또는 [_]를 검색패턴문자가 아닌 일반문자로 처리하여 비교
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME='B%';--실행오류 : 잘못된 결과를 제공하는 SQL 명령
--SQL에서는 LIKE 키워드를 사용하여 조건식에서 검색패턴문자를 이용해 컬럼값을 비교하여 검색하는 조건식 작성
--형식) 컬럼명 LIKE '검색패턴문자가 포함된 비교값'
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE 'B%';

--검색패턴문자를 사용하지 않아도 [=] 연산자 대신 LIKE 키워드 사용
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME='BLAKE';
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE 'BLAKE';

--EMP 테이블에서 사원이름에 [A] 문자가 포함되어 있는 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE '%A%';

--EMP 테이블에서 사원이름에 두번째 문자가 [L]인 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE '_L%';

🔘EMP 테이블 새로운 사원정보 삽입 처리 - 테이블에 행 저장

--EMP 테이블에 저장된 모든 사원정보 검색
SELECT * FROM EMP;
--EMP 테이블 새로운 사원정보 삽입 처리 - 테이블에 행 저장
INSERT INTO EMP VALUES(9000,'M_BEER','CLERK',7788,'81/12/12',1300,NULL,10);
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE '%\_%' ESCAPE '\';

--EMP 테이블에서 사원이름에 [_] 문자가 포함된 사원의 사원번호,사원이름,업무,급여 검색
--문제점)LIKE 키워드로 검색하면 [_] 문자를 일반문자가 아닌 검색패턴문자로 처리하여 검색 - 실행오류
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE '%_%';
--해결법)LIKE 키워드로 검색할 때 [_] 문자를 검색패턴문자가 아닌 일반문자로 처리되도록 회피문자(ESCAPE CHARACTER)로 표현하여 검색
--ESCAPE 키워드를 사용하여 회피문자를 표현하기 위한 문자를 설정해야만 비교값에서 회피문자 사용 가능 
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE '%\_%' ESCAPE '\';

🔘 EMP 테이블 사원번호가 9000인 사원정보 삭제 처리

DELETE FROM EMP WHERE EMPNO=9000;
SELECT * FROM EMP;
COMMIT;

 

🔘SQL 명령에서는 NOT 키워드를 사용하여 조건식의 결과가 참(TRUE)가 아닌 거짓(FALSE)인 행을 검색하는 조건식 작성

--형식) NOT(조건식)

--EMP 테이블에서 업무가 MANAGER이 아닌 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB<>'MANAGER';

--SQL 명령에서는 NOT 키워드를 사용하여 조건식의 결과가 참(TRUE)가 아닌 거짓(FALSE)인 행을 검색하는 조건식 작성
--형식) NOT(조건식)
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE NOT(JOB='MANAGER');

--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,성과급 검색
--NULL : 값이 존재하지 않는 것을 표현하기 위한 키워드
SELECT EMPNO,ENAME,JOB,SAL,COMM FROM EMP;

--EMP 테이블에서 성과급이 존재하지 않는 사원의 사원번호,사원이름,업무,급여,성과급 검색
--NULL은 값이 아니므로 비교 불가능하므로 실행오류 발생
SELECT EMPNO,ENAME,JOB,SAL,COMM FROM EMP WHERE COMM=NULL;

--SQL에서는 IS 키워드를 사용하여 NULL를 구분하여 조건식 작성
--형식) 컬럼명 IS NULL 또는 컬럼명 IS NOT NULL
SELECT EMPNO,ENAME,JOB,SAL,COMM FROM EMP WHERE COMM IS NULL;

--EMP 테이블에서 성과급이 존재하는 사원의 사원번호,사원이름,업무,급여,성과급 검색
SELECT EMPNO,ENAME,JOB,SAL,COMM FROM EMP WHERE COMM IS NOT NULL;

 

 

🔘 ORDER BY : 컬럼값을 비교하여 행이 정렬되도록 검색하는 기능 제공
--형식) SELECT 검색대상,검색대상,... FROM 테이블명 [WHERE 조건식]
--      ORDER BY {컬럼명|연산식|별칭|INDEX} {ASC|DESC}, {컬럼명|연산식|함수|별칭|INDEX} {ASC|DESC}, ...
--ASC : 오름차순 정렬, DESC : 내림차순 정렬
--정렬 처리되는 컬럼값이 같은 경우 다른 컬럼값을 비교하여 정렬되도록 ORDER BY 작성 가능

--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,부서번호 검색 - 해이 삽입된 순서대로 검색
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP;

--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,부서번호를 부서번호로 오름차순 정렬하여 검색
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP ORDER BY DEPTNO ASC;
--오름차순 정렬할 때 ASC 키워드 생략 가능
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP ORDER BY DEPTNO;

--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,부서번호를 급여로 내림차순 정렬하여 검색
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP ORDER BY SAL DESC;

--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,부서번호를 사원이름으로 오름차순 정렬하여 검색
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP ORDER BY ENAME;

--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,부서번호를 입사일로 내림차순 정렬하여 검색
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO,HIREDATE FROM EMP ORDER BY HIREDATE DESC;

--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,연봉(급여*12)을 연봉으로 내림차순 정렬하여 검색
SELECT EMPNO,ENAME,SAL*12 ANNUAL FROM EMP ORDER BY SAL*12 DESC; --연산식의 결과값을 비교하여 정렬 처리
SELECT EMPNO,ENAME,SAL*12 ANNUAL FROM EMP ORDER BY ANNUAL DESC; --컬럼의 별칭으로 표현된 컬럼값을 비교하여 정렬 처리
--SELECT 명령의 검색대상에는 순차적으로 첨자(INDEX : 위치를 표현하는 숫자값)가 자동 부여 - COLUMN INDEX
--오라클에서는 첨자가 1부터 1씩 증가되는 숫자값으로 표현하여 사용
SELECT EMPNO,ENAME,SAL*12 ANNUAL FROM EMP ORDER BY 3 DESC; --검색대상의 첨자로 표현되는 컬럼값을 비교하여 정렬 처리

--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,부서번호를 부서번호로 오름차순 정렬하고 부서번호가 같은 경우 
--급여로 내림차순 정렬하여 검색
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP ORDER BY DEPTNO ASC,SAL DESC;
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP ORDER BY DEPTNO,SAL DESC;

--EMP 테이블에서 급여가 3000 이상인 사원의 사원번호,사원이름,업무,급여,부서번호를 업무로 오름차순 정렬하여 검색
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP WHERE SAL>=3000 ORDER BY JOB;