'Oracle Database SQL' 카테고리의 글 목록

Oracle Database SQL

0410 필기 2019.06.17
190418 2019.06.07
190417 2019.06.07
190416(2) 2019.06.07
190416 2019.06.07
190415 2019.06.07
190411 2019.06.07
190410 2019.06.07
190409 2019.06.07
190408 2019.06.07
190405 2019.06.07
190404 2019.06.07
190403 2019.06.07
190402 2019.06.07
090401 2019.06.07

0410 필기

monkey-k777 2019. 6. 17. 20:15

2019. 6. 17. 20:15

VO/DAO 패턴
VO(Value Object)
- 데이터 베이스의 한 레코드를 담는 객체
- 필드, 생성자, Getter/Setter 메소드 구성
- 레코드(행)의 개수만큼 생성해서 사용한다.
* VO객체, Domain객체, DTO객체, 자바 빈즈라고 부른다.

DAO(Database Access Object)
- Database Access 작업을 담당하는 객체
- 추가/삭제/변경/조회 작업과 관련된 메소드들로 구성
* CRUD 기능을 구현한다.
* Create, Retrieve(조회), Update, Delete
- 업무 로직이 존재하지 않는다.
* 단순한 JDBC 코딩만 존재한다.
* ibatis/mybatis/hibernate/jpa 등의 DB Access 작업을 단순화시켜주는 프레임워크를 활용해서 주로 개발된다.

Service
- 업무 로직을 관련된 작업을 담당하는 객체
- 사용자가 입력한 값을 전달받아서 적절한 업무로직을 수행하고 업무 로직의 수행 결과를 DAO를 활용해서 DB에 영구적으로 보관한다.
* 업무 로직은 보통 1번 이상의 DB Access 작업을 필요로 한다.
* Service 객체의 메소드들은 DAO에 구현된 DB Access 작업을 실행해서 업무 로직에 필요한 정보를 획득하거나, 업무 로직의 수행 결과를 DB에 반영한다.

무결성 제약 조건
    - 테이블에 유효하지 않은 데이터가 저장되는 것을 방지한다.
    - 테이블에 행이 추가, 갱신, 삭제될 때마다 제약 규칙이 적용된다.
    - 제약 조건의 종류
not null         제약 조건
해당 컬럼이 null값을 포함하지 못하도록 제약한다(이름).
unique          제약 조건(고유키 제약 조건)
해당 컬럼의 값은 테이블의 모든 행에서 고유한 값을 가지는 컬럼 혹은 컬럼의 조합이어야 한다(이메일, 주민번호, 아이디)
primary key 제약 조건(기본키 제약 조건)
해당 컬럼이 행을 대표하는 컬럼이 되도록 한다.
* null 값을 허용하지 않는다.
* 모든 행에 대해서 같이 고유해야 한다.
foreign key    제약 조건(외래키 제약 조건)
해당 컬럼의 값이 동일한 테이블이나 다른 테이블의 기본키 혹은 고유키로 지정된 컬럼의 값을 참조하도록 한다
* 참조하는 컬럼의 값들만 저장되게 한다.
* not null 제약 조건이 지정되어 있지 않으면 null값도 허용한다.
check 제약 조건
* 해당 컬럼의 값이 제시된 조건을 만족하는 값만 저장되도록 지정한다.

0415
트랜잭션
    - 데이터의 일관성을 보장하기 위해서 사용한다.
    - insert, update, delete 구문만 트랜잭션 처리의 대상이다.
    - 한 개 이상의 단일 작업(DB Access 작업)을 논리적 작업 그룹으로 묶어서 처리한다.

트랜잭션의 시작과 종료
    - 트랜잭션의 시작
        - 첫 번째 DML(INSERT, UPDATE, DELETE 구문) SQL이 실행될 때 자동으로 시작된다.
    - 트랜잭션의 종료
        - commit 또는 rollback 구문이 실행되면 자동으로 종료된다.
        - 시스템이 고장난 경우

commit과 rollback
    - commit은 해당 트랜잭션 내에서 실행된 dml 작업(데이터 변경 작업)을 영구 저장한다.
    - rollback은 해당 트랜잭션 내에서 실행된 dml 작업의 db 반영을 전부 취소한다.

트랜잭션 특징(ACID)
    - 원자성: 트랜잭션 내의 작업이 부분적으로 성공하거나 실패하는 것을 허용하지 않는 것. All or Nothing으로 동작
    - 일관성: 트랜잭션 내의 작업이 성공적으로 완료되었다면 언제나 일관성 있는 데이터 상태로 유지되는 것.
                  무결성 제약 조건을 위반하는 (일관성 있는 데이터 상태가 아닌 것) 트랜잭션은 실행될 수 없다.
    - 고립성: 트랜잭션 수행 시 다른 트랜잭션의 연산이 영향을 미치지 못하도록 하는 것
                  * 트랜잭션이 종료(완료)되기 전의 중간 상태의 데이터를 외부 트랜잭션이 볼 수 없음
    - 지속성: 성공적으로 수행된 트랜잭션의 결과는 데이터베이스에 영구적으로 반영된다.

오라클 데이터베이스 객체
    시퀀스: 일련번호 생성기
    테이블: 데이터의 저장소
    뷰: 가상의 테이블이다.
         하나 이상의 테이블을 기반으로 하는 가상의 테이블이고, 자체적인 데이터를 포함하고 있지는 않지만, 테이블과 마찬가지로 뷰를 대상으로 CRUD가 가능하다.
         * 대부분 뷰를 대상으로는 SELECT만 사용한다.
         - 뷰를 사용하는 이유: 복잡한 SQL문을 쉽게 작성할 수 있다.
                                        데이터 엑세스를 제한할 수 있다.
                                        동일한 데이터로부터 다양한 결과를 얻을 수 있다.
    인덱스: 데이터의 색인 정보를 가지고 있다.

인라인 뷰(Inline View)
select ~
from ~
where 컬럼 = (select ~
                   from ~
                   where ~) <-- 서브 쿼리

select ~
from (select ~
       from ~
       where ~) <-- 인라인 뷰
where ~

select ~, (select ~ from ~ where ~) <-- 스칼라 서브 쿼리
from ~
where ~

인라인 뷰
    - 메인 쿼리의 from 절에 작성된 서브 쿼리를 말한다.
       * select문의 실행 결과가 가상의 테이블로 취급된다.
       * view는 오라클의 데이터베이스 객체이지만, 인라인 뷰는 오라클의 데이터베이스 객체가 아니다.
       * 메인 쿼리가 실행되는 동안 잠깐 만들어지는 가상의 테이블이다.
       - Top-N 분석 쿼리에 자주 사용된다.
- 인라인 뷰도 조인에 참여할 수 있다.

Top-N 분석 쿼리
    - 테이블에서 조건에 맞는 최상위 데이터 n개 또는 최하위 데이터 n개를 조회할 때 사용된다.
        - 회사에 급여를 가장 많이 받는 직원 3명
        - 가장 최근에 입사한 직원 5명
        - 한 달 동안 가장 많이 팔린 제품 10가지
    - select 컬럼명, 컬럼명
from (select 컬럼명, 컬럼명
from 테이블
order by Top-N 분석 대상_컬럼명)
        where rownum <= N
        * rownum은 서브쿼리(인라인뷰)에서 반환되는 각 행에 1부터 시작하는 순번을 할당한다.

분석 함수
    - 분석 대상 컬럼의 값을 기준으로 순번 혹은 순위를 부여하는 함수다.
       * ROW_NUMBER(): 순번을 부여한다.
       * RANK(): 순위를 부여한다.
    - Top-N 분석을 수행할 수 있다.
    - 특정 범위에 속한 행을 찾을 때 유리하다(6~10등).
    - 페이징 처리에 유용하게 사용되는 함수다.
    - 사용 방법: SELECT 분석함수() OVER (ODER BY 정렬대상컬럼명 정렬방향) 별칭, 컬럼명, 컬럼명
FROM 테이블명
* 정렬대상 컬럼의 값을 기준으로 행을 정렬한 다음 순번 혹은 순위를 부여한다.

0416
인덱스
- 오라클 데이터베이스 테이블에서 원하는 데이터를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조
- 테이블의 데이터에 대한 색인 정보를 보관하고 있는 객체다.
* 인덱스에 보관된 정보 = 데이터 + ROWID(데이터가 저장된 위치 정보)
- 인덱스의 생성
자동 생성 인덱스
Primary key, unique 제약 조건이 정의된 컬럼은 자동으로 인덱스가 생성된다.
수동 생성 인덱스
create index 인덱스명
on 테이블명(컬럼명, 컬럼명, ...)

- 인덱스의 삭제
drop index 인덱스명

- 인덱스가 필요한 경우
where절에 검색 조건으로 자주 사용되는 경우
  조인 조건으로 자주 사용되는 경우
  해당 컬럼에 null값이 많이 포함된 경우
  해당 컬럼에 다양한 값이 분포되어 있는 경우
  큰 테이블에서 대부분의 조회 조건으로 검색되는 행이 2%~4% 미만인 경우

- rowid
테이블에 저장된 데이터의 위치를 나타내는 주소값이다.
  사용자가 임의로 변경할 수 없고, 조회만 할 수 있다.
  예시: AAAEAW AAE AAAACt AAA
  AAAEAW: 데이터베이스 식별을 위한 Data Object Number
  AAE: 각각의 데이터파일에 할당된 File Number
  AAAACt: 데이터 블록의 위치를 나타내는 Block Number
  AAA: 블록 내에 데이터가 저장된 행의 번호 Row Number

사용자 및 권한 관리
사용자
- 데이터베이스 시스템에 접속하고, 데이터베이스 객체를 생성/삭제, 데이터 조작을 할 때 필요한 사용자 계정
- 시스템 권한을 가진 사용자만 사용자를 등록/삭제할 수 있다.
권한(privileges)
- 특정 SQL문을 실행할 수 있는 권리
- 시스템 권한
- 데이터베이스 시스템을 사용(액세스)할 수 있는 권한
- 시스템 권한을 가진 사용자만 부여/박탈이 가능하다.
- 객체 권한
- 데이터베이스 객체의 내용을 조작할 수 있는 권한
- 해당 객체의 주인(소유자)가 부여/박탈이 가능하다.
롤(role)
- 사용자에게 부여할 수 있는 권한들의 집합이다.
- 여러 개의 권한을 묶어서 하나의 롤을 정의할 수 있다.
* 권한의 부여/박탈이 쉬워진다.
- 주로 사용되는 롤
- Connect Role
- 오라클에 접속할 수 있는 세션을 생성할 수 있는 권한
- 일반적인 데이터베이스 객체를 생성하거나 조회할 수 있는 권한
- Resource Role
- PL/SQL을 사용할 수 있는 권한
* 일반 사용자에게는 Connect, Resource Role을 부여한다.
- DBA Role
- 모든 시스템 권한이 부여된 Role이다.
- 데이터베이스 관리자에게 부여된다.

0417
set 연산자
    - 두 개 이상의 조회 결과를 하나로 결합한다.
    - 연산자
        - UNION
A UNION B 1 2 3 4 5 6 7
A의 조회 결과와 B의 조회 결과에 포함된 모든 행을 반환한다.
단 A와 B 둘 다에 포함되어 있는 행은 한 번만 선택된다.
(A와 B의 합집합을 반환한다. 단 중복된 행은 한 번만)
        - UNION ALL
A UNION ALL B 1 2 3 4 3 4 5 6 7
A의 조회 결과와 B의 조회 결과에 포함된 모든 행을 반환한다.
A와 B 모두에 포함된 중복된 행도 모두 반환한다.
(A와 B의 합집합을 반환한다. 중복된 행 포함)
        - INTERSECT
A INTERSECT B 3 4
A의 조회 결과와 B의 조회 결과에 공통으로 포함된 행만 반환한다.
(A와 B의 교집합을 반환한다.)
        - MINUS
A MINUS B 1 2
A의 조회 결과에서 B의 조회 결과를 뺀 행을 반환한다.
(A와 B의 차집합을 반환한다.)
    - 쿼리 작성 시 주의 사항
        - A 쿼리문과 B 쿼리문의 선택된 컬럼의 개수가 동일해야 한다.
SELECT 컬럼 1, 컬럼 2
FROM A
UNION
SELECT 컬럼3, 컬럼7
FROM B
        - A 쿼리문과 B 쿼리문의 선택된 컬럼의 데이터 타입이 동일해야 한다.
        - A 쿼리문과 B 쿼리문에서 선택된 컬럼의 이름은 동일하지 않아도 된다.
        - 정렬을 위한 ORDER BY 절은 맨 마지막 SELECT문에 붙인다.

다중컬럼 서브쿼리
    - 서브쿼리가 두 개 이상의 컬럼을 반환하는 경우 사용된다.
    - SELECT ~
FROM ~
WHERE (컬럼1, 컬럼2) IN (SELECT 컬럼1, 컬럼2
FROM ~
WHERE ~ )
* 컬럼 간의 실제 컬럼명은 다를 수 있다.

스칼라 서브쿼리
- 하나의 행에서 하나의 값(1행 1열)만 반환하는 서브쿼리다.
    - 오직 하나의 값을 반환하는 서브 쿼리다.
    - 주로 SELECT절에서 사용된다.
    * DECODE 및 CASE의 조건 및 표현식 부분
    * SELECT문의 모든 절에서 사용 가능하다(GROUP BY 절은 제외)
    * UPDATE 문의 SET절

상호연관 서브쿼리
- SELECT COLUMN1, COLUMN2
(SELECT COLUMN1
FROM TABLE INNER
WHERE INNER.COLUMN3 = OUTER.COLUMN3)
      FROM TABLE OUTER

    - SELECT COLUMN1, COLUMN2, ...
       FROM TABLE OUTER
       WHERE COLUMN3 = (SELECT COLUMN1
                                 FROM INNER
                                 WHERE INNER.COLUMN4 = OUTER.COLUMN4)
    - 상호연관 서브쿼리는 OUTER 쿼리의 행이 조회될 때마다 서브쿼리가 실행된다.

데이터 모델링
    - 정보 시스템을 구축하기 위한 테이블 관점에서의 업무 분석 기법
    - 약속된 표기법으로 표현하는 과정
    - 데이터베이스 구축을 위한 분석/설계 과정

데이터 모델링의 모델 종류
    - 개념적 데이터 모델: 핵심 엔티티를 선정하고, 앤티티간의 관계를 생성한다. 각 앤티티의 애트리뷰트를 선정한다.
    - 논리적 데이터 모델: 구축하려는 정보 시스템에 맞게 기본키, 애트리뷰트를 정확하게 표현하는 것
    - 물리적 데이터 모델: 실제 데이터베이스에 적용할 수 있도록 테이블, 컬럼, 데이터 타입 등을 고려해서 설계한 것

앤티티
    - 업무에 필요한 정보를 저장하는 것
    - 영구적으로 존재하는 데이터인 경우
    - 누가 봐도 정확하게 구분되는 데이터 집합인 경우
앤티티의 종류
    - Key entity, Main entity, Action entity, Child entity
속성(Attributes)
    - 앤티티가 가지는 특징
    - 앤티티를 구성하는 값
    - 앤티티에서 관리하고자 하는, 더 이상 분리되지 않는 데이터의 최소 단위
관계(Relation)
    - 두 개의 앤티티 사이의 논리적인 관계
    - 관계는 분포도, 존재 유무, 방향으로 구성된다.
    * 관계 = 분포도(O, <) + 존재유무(|) + 방향(─)
브릿지 테이블

0418
계층형 쿼리
    - 테이블에 저장된 데이터가 상위 계층과 하위 계층의 관계를 가지고 있을 때 오라클에서는 start with와 connect by를 이용해서 상위->하위, 하위->상위로 데이터를 조회할 수 있다.
    - 형식
      select level, 컬럼명, 컬럼명, ...
      from table
      where 조건식
      start with 계층의 시작점(시작행)을 지정합니다.
      connect by 검색 방향을 지정
      * connect by prior 부모행의 키 = 자식행의 키 <-- 부모에서 자식으로 트리 구성
      * connect by prior 자식행의 키 = 부모행의 키 <-- 자식에서 부모로 트리 구성
      * level은 계층형 쿼리에서 수행 결과의 depth를 표현하는 의사 컬럼이다.
      - 계층형 쿼리의 실행 순서
      select level, 컬럼명, 컬럼명, ...                           5
      from table 1
      where 조건식 4
      start with 계층의 시작점(시작행)을 지정합니다. 2
      connect by 검색 방향 3

저작자표시

'Oracle Database SQL' 카테고리의 다른 글

190418 (0)	2019.06.07
190417 (0)	2019.06.07
190416(2) (0)	2019.06.07
190416 (0)	2019.06.07
190415 (0)	2019.06.07

190418

monkey-k777 2019. 6. 7. 17:04

2019. 6. 7. 17:04

-- 전체 직원의 평균 급여보다 적은 급여를 받은 사원들의 급여 - 평균 급여를 구하기
SELECT EMPLOYEE_ID, FIRST_NAME, (SELECT avg(salary) from EMPLOYEES) - salary
from EMPLOYEES
where salary < (select avg(salary)
                   from EMPLOYEES);
 
-- with 절
-- SELECT문에서만 사용 가능하다.
-- SQL문에서 여러 번 사용되는 같은 형태의 쿼리를 with절을 사용해서 결과를 검색하고 임시로 저장한다.
-- select 문에서 with절의 결과를 이용할 수 있다.
-- 쿼리의 실행 성능이 향상된다.
-- with
-- 임시 테이블명 as (
--        select ~
--        from ~
--        where ~
-- )
-- SELECT
-- FROM 임시테이블명
-- where ~
with 
temp_avg as (
    select avg(salary) avg_salary
    from EMPLOYEES
)
select EMPLOYEE_ID, FIRST_NAME, TRUNC((SELECT avg_salary from temp_avg) - salary) salary_gap
from EMPLOYEES
WHERE SALARY < (SELECT avg_salary
                        from temp_avg);
                        
-- 부서별 총 급여를 계산했을 때, 부서별 총 급여의 평균보다 총급여를 많이 받는 부서의 이름, 총급여 조회
SELECT DEPARTMENT_NAME, TOTAL_DEPT_SALARY
FROM (SELECT B.DEPARTMENT_NAME, SUM(A.SALARY) TOTAL_DEPT_SALARY
         FROM EMPLOYEES A, DEPARTMENTS B
         WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
         GROUP BY B.DEPARTMENT_NAME) A
WHERE A.TOTAL_DEPT_SALARY > (SELECT SUM(TOTAL_DEPT_SALARY)/COUNT(*)
                                           FROM (SELECT B.DEPARTMENT_NAME, SUM(A.SALARY) TOTAL_DEPT_SALARY
                                                    FROM EMPLOYEES A, DEPARTMENTS B
                                                    WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
                                                    GROUP BY B.DEPARTMENT_NAME));
 
-- WITH절을 사용해서 부서별 총 급여의 평균보다 총급여를 많이 받는 부서의 이름, 총 급여 조회하기
WITH
    DEPT_SALARY AS (
        SELECT B.DEPARTMENT_NAME, SUM(A.SALARY) TOTAL_DEPT_SALARY
        FROM EMPLOYEES A, DEPARTMENTS B
        WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
        GROUP BY B.DEPARTMENT_NAME),
    DEPT_AVG AS (
        SELECT SUM(TOTAL_DEPT_SALARY)/COUNT(*) AVG_DEPT_SALARY
        FROM DEPT_SALARY
    )
SELECT *
FROM DEPT_SALARY
WHERE TOTAL_DEPT_SALARY > (SELECT AVG_DEPT_SALARY
                                         FROM DEPT_AVG);
                                         
-- 계층형 쿼리
-- employee에서 Neena를 시작점으로 지정하고, 상위에서 하위로 검색하기
SELECT level, employee_id, first_name, manager_id
FROM EMPLOYEES
start with first_name = 'Neena'
-- 검색방향: 상위에서 하위로
-- connect by prior 부모키 = 자식키
CONNECT BY PRIOR employee_id = manager_id;
 
-- employee에서 206번 사원을 시작점으로 지정하고, 하위에서 상위로 검색하기
SELECT EMPLOYEE_ID, FIRST_NAME, MANAGER_ID
FROM EMPLOYEES
start With  EMPLOYEE_id= 206
-- connect by prior 자식행 키 = 부모행의 키
connect by prior manager_id = EMPLOYEE_id;
 
-- employees 테이블의 계층 구조를 level과 LPAD를 사용해서 표현하기.
SELECT level, lpad(' ', (level * 5), ' ') || FIRST_NAME
FROM EMPLOYEES
START WITH EMPLOYEE_id = 100
CONNECT by PRIOR EMPLOYEE_id = MANAGER_id;
 
-- employee에서 Neena를 시작점으로 지정하고, 상위에서 하위로 검색하기
SELECT level, lpad(' ', (level * 5)-5, ' ') || FIRST_NAME
from EMPLOYEES
-- where FIRST_NAME != 'Nancy' -- Nancy만 안 나옴
start with first_name = 'Neena'
connect by EMPLOYEE_id = manager_id    -- 상위 -> 하위: prior 부모행의 키 = 자식행의 키
and FIRST_NAME != 'Nancy';                  -- Nanacy와 Nancy의 하위 행이 모두 나오지 않음
cs

'Oracle Database SQL' 카테고리의 다른 글

0410 필기 (0)	2019.06.17
190417 (0)	2019.06.07
190416(2) (0)	2019.06.07
190416 (0)	2019.06.07
190415 (0)	2019.06.07

190417

monkey-k777 2019. 6. 7. 17:03

2019. 6. 7. 17:03

-- 모든 직원의 현재 직종 및 이전 직종을 사원 아이디, 직종 아이디로 표현하기
-- 각 사원에 대해 동일한 직종은 한 번만 조회
SELECT EMPLOYEE_ID, JOB_ID, 'C'
FROM EMPLOYEES
UNION
SELECT EMPLOYEE_ID, JOB_ID, 'P'
FROM JOB_HISTORY;
 
-- 모든 직원의 현재 직종 및 이전 직종을 사원 아이디, 직종 아이디로 표현하기
SELECT EMPLOYEE_ID, JOB_ID
FROM EMPLOYEES
UNION ALL
SELECT EMPLOYEE_ID, JOB_ID
FROM JOB_HISTORY
ORDER BY EMPLOYEE_ID;
 
-- 50번 부서에서 근무중이거나 근무했던 사원의 아이디, 직종을 조회하기
SELECT EMPLOYEE_ID, JOB_ID, HIRE_DATE AS START_DATE, NULL AS END_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50
UNION ALL
SELECT EMPLOYEE_ID, JOB_ID, START_DATE, END_DATE
FROM JOB_HISTORY
WHERE DEPARTMENT_ID = 50;
 
-- 현재 50번 부서에 근무 중인 직원 중에서 이전에 50번 부서에 근무 경험이 있는 직원 조회하기
SELECT A.EMPLOYEE_ID, B.FIRST_NAME, B.SALARY
FROM (SELECT EMPLOYEE_ID
         FROM EMPLOYEES
         WHERE DEPARTMENT_ID = 50
         INTERSECT 
         SELECT EMPLOYEE_ID
         FROM JOB_HISTORY
         WHERE DEPARTMENT_ID = 50) A, EMPLOYEES B
WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID;
 
-- 90번 부서에 근무 중인 사원 중에서 업무가 변경된 적이 한 번도 없는 사원의 아이디를 조회하기
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
MINUS
SELECT EMPLOYEE_ID
FROM JOB_HISTORY;
 
-- 90번 부서에 근무 중인 사원 중에서 업무가 변경된 적이 한 번도 없는 사원의 정보를 조회하기
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID
                                FROM EMPLOYEES
                                WHERE DEPARTMENT_ID = 90
                                MINUS
                                SELECT EMPLOYEE_ID
                                FROM JOB_HISTORY);
 
-- 직원 아이디가 174 사원과 관리자 및 부서가 동일한 사원을 조회하기
SELECT *
FROM EMPLOYEES
WHERE (MANAGER_ID, DEPARTMENT_ID) IN (SELECT MANAGER_ID, DEPARTMENT_ID -- 앞부분 괄호 필수, 순서 지켜야
                                                        FROM EMPLOYEES
                                                        WHERE EMPLOYEE_ID = 174);
 
-- 부서별 최고 급여를 조회했을 때
SELECT EMPLOYEE_ID, FIRST_NAME, NVL(TO_CHAR(DEPARTMENT_ID), '없음'), SALARY
FROM EMPLOYEES
WHERE (NVL(DEPARTMENT_ID, 0), SALARY) IN (SELECT NVL(DEPARTMENT_ID, 0), MAX(SALARY)
                                                 FROM EMPLOYEES
                                                 GROUP BY DEPARTMENT_ID);
 
-- 스칼라 서브쿼리 사용하기
-- 아이디, 이름, 급여, 평균 급여와의 차
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY - (SELECT TRUNC(AVG(SALARY)) FROM EMPLOYEES) AVG_SALARY
FROM EMPLOYEES;
 
-- 전체 평균 급여보다 10000달러 이상 많이 받는 사원 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY 
FROM EMPLOYEES
WHERE SALARY - (SELECT TRUNC(AVG(SALARY)) FROM EMPLOYEES) >= 10000;
 
-- 모든 사원의 급여를 인상시키기
-- 평균 급여보다 적은 급여를 받는 사원의 급여 인상시키기
-- (평균급여액 - 급여)/4
UPDATE EMPLOYEES
SET
    SALARY = SALARY + TRUNC(((SELECT AVG(SALARY) FROM EMPLOYEES) - SALARY)/4)
WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID
                                FROM EMPLOYEES
                                WHERE SALARY < (SELECT AVG(SALARY)
                                FROM EMPLOYEES));
 
SELECT * FROM EMPLOYEES;
ROLLBACK;
 
-- 급여별 급여 인상액 추정해보기
-- 15000달러 이상 10% 인상, 10000달러 이상 15% 인상, 10000달러 미만 20% 인상
-- 평균 급여 대비 인상률임
SELECT
    EMPLOYEE_ID,
    FIRST_NAME,
    SALARY,
    CASE
        WHEN SALARY >= 15000 THEN TRUNC((SELECT AVG(SALARY) FROM EMPLOYEES) * 0.1)
        WHEN SALARY >= 10000 THEN TRUNC((SELECT AVG(SALARY) FROM EMPLOYEES) * 0.15)
        ELSE TRUNC((SELECT AVG(SALARY) FROM EMPLOYEES) * 0.2)
    END INCREMENT_SALARY
FROM EMPLOYEES;
 
-- 부서 아이디, 부서 이름, 부서별 사원수 계산하기(상호연관 서브쿼리)
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME,
          (SELECT COUNT(*) FROM EMPLOYEES X WHERE X.DEPARTMENT_ID = A.DEPARTMENT_ID)
FROM DEPARTMENTS A
WHERE A.MANAGER_ID IS NOT NULL;
 
-- 일반 서브쿼리와 상호 연관 서브쿼리
-- 일반 서브쿼리
-- 전체 사원의 평균급여보다 급여를 많이 받는 사원 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT TRUNC(AVG(SALARY))
                        FROM EMPLOYEES);  -- 서브쿼리는 단 한 번 실행되고, 실행 결과가 조건식에 사용된다.
 
-- 상호연관 서브쿼리
-- 소속부서의 평균급여보다 급여를 많이 받는 사원 조회하기
SELECT A.FIRST_NAME, A.DEPARTMENT_ID, A.SALARY
FROM EMPLOYEES A
WHERE A.SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES B WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID);
 
 
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'yyyy') >= '2003';
AND TO_CHAR(HIRE_DATE, 'mm') in ('01','02','03') 
UNION
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
where TO_CHAR(HIRE_DATE, 'yyyymm') >= 200501
AND TO_CHAR(HIRE_DATE, 'yyyymm') <= 200506;
 
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'yyyy') = 2005;
 
SELECT a.department_id, a.department_name, (SELECT count(*) FROM EMPLOYEES x where x.DEPARTMENT_ID = a.department_id) as 사원수
FROM DEPARTMENTS a;
Colored by Color Scripter
cs

'Oracle Database SQL' 카테고리의 다른 글

0410 필기 (0)	2019.06.17
190418 (0)	2019.06.07
190416(2) (0)	2019.06.07
190416 (0)	2019.06.07
190415 (0)	2019.06.07

190416(2)

monkey-k777 2019. 6. 7. 17:02

2019. 6. 7. 17:02

-- 인덱스
-- rowid 조회하기
SELECT department_id, rowid
from EMPLOYEES;
 
-- 인덱스 생성하기
create index sample_pro_name_ix
on sample_products (product_name);
 
insert into sample_products ()
values ();
 
SELECT *
FROM SAMPLE_PRODUCTS
where PRODUCT_NAME = 'tv';
 
SELECT *
FROM SAMPLE_PRODUCTS
where PRODUCT_MAKER = '애플';
 
-- 함수 기반 인덱스 생성하기
create index emp_hire_year_ix
on employees (to_char(hire_date, 'yyyy'));
 
select *
from EMPLOYEES
where to_char(HIRE_DATE, 'yyyy') = '2008';
 
 
 
SELECT first_name
FROM EMPLOYEES
where TO_CHAR(HIRE_DATE, 'yyyy') = (SELECT TO_CHAR(HIRE_DATE, 'yyyy')
                                                 FROM EMPLOYEES
                                                 GROUP by TO_CHAR(hire_date, 'yyyy')
                                                 having count(*) = (SELECT max(개수)
                                                                         from (SELECT TO_CHAR(hire_date, 'yyyy'), count(*) 개수
                                                                                 FROM EMPLOYEES
                                                                                 GROUP by TO_CHAR(hire_date, 'yyyy'))));
 
create SEQUENCE name;
Colored by Color Scripter
cs

'Oracle Database SQL' 카테고리의 다른 글

190418 (0)	2019.06.07
190417 (0)	2019.06.07
190416 (0)	2019.06.07
190415 (0)	2019.06.07
190411 (0)	2019.06.07

190416

monkey-k777 2019. 6. 7. 17:01

2019. 6. 7. 17:01

CREATE TABLE TB_CATEGORIES (
    CATEGORY_NO NUMBER(3, 0) PRIMARY KEY,
    CATEGORY_NAME VARCHAR2(100) NOT NULL,
    PARENT_CATEGORY_NO NUMBER(3, 0) REFERENCES TB_CATEGORIES(CATEGORY_NO)
);
 
CREATE TABLE TB_COMPANIES (
    COMPANY_NO NUMBER(3) PRIMARY KEY,
    COMPANY_NAME VARCHAR2(100),
    COMPANY_TEL VARCHAR2(20),
    COMPANY_CREATE_DATE DATE DEFAULT SYSDATE
);
    
CREATE TABLE TB_GOODS (
    PRODUCT_NO NUMBER(5, 0) PRIMARY KEY,
    PRODUCT_NAME VARCHAR2(200) NOT NULL,
    COMPANY_NO NUMBER(3) REFERENCES TB_COMPANIES(COMPANY_NO),
    PRODUCT_PRICE NUMBER(7, 0),
    PRODUCT_STOCK NUMBER(4, 0),
    PRODUCT_IMAGE_NAME VARCHAR2(100),
    PRODUCT_SELL_YN CHAR(1) DEFAULT 'Y' CHECK (PRODUCT_SELL_YN IN ('Y', 'N')),
    PRODUCT_LIKES NUMBER(5, 0) DEFAULT 0,
    PRODUCT_CREATE_DATE DATE DEFAULT SYSDATE,
    CATEGORY_NO NUMBER(3, 0) REFERENCES TB_CATEGORIES(CATEGORY_NO)
);
    
CREATE TABLE TB_CUSTOMERS (
    CUSTOMSER_NO NUMBER(5,0) NOT NULL PRIMARY KEY,
    CUSTOMSER_NAME VARCHAR2(50) NOT NULL,
    CUSTOMSER_ID VARCHAR2(20) UNIQUE,
    CUSTOMSER_PASSWORD VARCHAR2(20) NOT NULL,
    CUSTOMSER_GENDER CHAR(1) CHECK (CUSTOMSER_GENDER IN ('F', 'M')),
    CUSTOMSER_EMAIL VARCHAR2(256) UNIQUE,
    CUSTOMSER_PHONE VARCHAR2(20),
    CUSTOMSER_ZIPCODE CHAR(5),
    CUSTOMSER_ADDRESS VARCHAR2(200),
    CUSTOMSER_CREATE_DATE DATE
);
 
CREATE TABLE TB_ORDERS (
    ORDER_NO NUMBER(5, 0) PRIMARY KEY,
    CUSTOMER_NO NUMBER(5, 0) REFERENCES TB_CUSTOMERS(CUSTOMSER_NO),
    ORDER_STATE VARCHAR2(20) DEFAULT '주문완료' CHECK(ORDER_STATE IN ('주문완료', '결제완료', '배송중', '배송완료')),
    ORDER_CREATE_DATE DATE DEFAULT SYSDATE
);
 
CREATE TABLE TB_ORDER_ITEMS (
    ORDER_ITEM_NO NUMBER(7, 0) PRIMARY KEY,
    PRODUCT_NO NUMBER(5, 0) REFERENCES TB_GOODS(PRODUCT_NO),
    ORDER_ITEM_AMOUNT NUMBER(3, 0),
    ORDER_NO NUMBER(5, 0) REFERENCES TB_ORDERS(ORDER_NO)
);
Colored by Color Scripter
cs

'Oracle Database SQL' 카테고리의 다른 글

190417 (0)	2019.06.07
190416(2) (0)	2019.06.07
190415 (0)	2019.06.07
190411 (0)	2019.06.07
190410 (0)	2019.06.07

190415

monkey-k777 2019. 6. 7. 17:00

2019. 6. 7. 17:00

------------------------------------------------------------ 첫 번째 트랜잭션이 시작됨
 
-- 첫 번째 트랜잭션 내의 DML 작업 1
insert into store_users
values ('kim', '김유신', 'zxcv1234', '010-2222-2222', 'kimg@naver.com', 10, sysdate);
 
-- 첫 번째 트랜잭션 내의 DML 작업 2
update STORE_USERS
set USER_POINT = 500
where user_id = '1';
 
-- 첫 번째 트랜잭션 내의 DML 작업 3
update STORE_USERS
set USER_POINT = 0
where user_id = 'kim';
 
SELECT * FROM STORE_USERS;
 
-- 첫 번째 트랜잭션 내의 DML 작업 1, DML 작업 2, DML 작업 3을 영구적으로 DB에 반영한다.
commit;
--------------------------------------------------------- 첫 번째 트랜잭션 종료
--------------------------------------------------------- 두 번째 트랜잭션 시작
-- 두 번째 트랜잭션의 DML 작업 1
DELETE  FROM STORE_USERS WHERE USER_ID = '2';
 
UPDATE STORE_USERS SET USER_PHONE = '010-1234-5678';
 
INSERT into store_users VALUES('hong', '홍길동', 'zxcv1234', '010-3333-2222', 'hong@naver.com', 0, SYSDATE);
 
-- 두 번째 트랜잭션 내에서 실행한 DML 작업 1, DML 작업 2의 DB 반영을 취소시킨다.
ROLLBACK;
 
--------------------------------------------------------- 두 번째 트랜잭션 종료
--------------------------------------------------------- 세 번째 트랜잭션 시작
 
-- 뷰 생성하기
-- CREATE VIEW 뷰의 이름
-- AS SELECT ~
--     FROM ~
--     WHERE ~
 
CREATE OR REPLACE VIEW emp_info_view
AS
    SELECT A.EMPLOYEE_ID AS ID, 
    A.FIRST_NAME || ', ' || A.LAST_NAME AS FULL_NAME, 
    A.SALARY, 
    A.SALARY * 12 AS ANNUAL_SALARY, 
    A.DEPARTMENT_ID AS DEPT_ID, 
    B.DEPARTMENT_NAME AS DEPT_NAME, 
    C.GRA AS SALARY_GRADE
    FROM EMPLOYEES A, DEPARTMENTS B, JOB_GRADES C -- 복합 뷰
    WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
    AND SALARY >= C.LOWEST_SALARY
    AND SALARY <= C.HIGHEST_SALARY;
    WITH READ ONLY; -- SELECT만 가능하도록
    
SELECT * FROM EMP_INFO_VIEW;
 
SELECT *
FROM EMP_INFO_VIEW
WHERE DEPT_ID = 60;
 
-- 뷰의 삭제
-- drop view 뷰 이름;
-- 뷰는 데이터를 포함하고 있찌 않기 때문에 뷰를 삭제하더라도 데이터는 지워지지 않는다.
DROP VIEW EMP_INFO_VIEW;
 
SELECT * FROM EMP_INFO_VIEW;
 
SELECT * FROM EMPLOYEES;
 
-- 인라인 뷰 작성하기
-- 부서별 사원수를 조회하기
SELECT DEPARTMENT_ID, COUNT(*) cnt
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
 
SELECT a.dept_id, b.department_name, a.cnt
from (select DEPARTMENT_id as dept_id, count(*) cnt -- dept_id // 연산식은 꼭 별칭을 주는 습관을 들이도록
       from EMPLOYEES
       GROUP BY DEPARTMENT_id) A, DEPARTMENTS B
where a.dept_id = b.department_id; -- 밖에서도 별칭으로 활용해야
 
 
--rownum
select rownum, department_id, department_name
from departments;
 
-- Top-N 분석하기
-- 급여를 가장 많이 받는 직원 3명 조회하기
SELECT ROWNUM, ID, NAME, SALARY -- 별칭 그대로 써야
FROM (SELECT employee_id as id, first_name as name, salary
         FROM EMPLOYEES
         ORDER by salary desc)
WHERE ROWNUM <= 3;
 
-- cf
 SELECT ROWNUM, employee_id, first_name, salary
 FROM EMPLOYEES
 where rownum <= 3
 ORDER BY salary DESC; -- 정렬이 마지막으로 되므로
 
 -- 가장 최근에 입사한 직원 5명 조회하기
 -- 사원 아이디, 이름, 부서명, 입사일 조회
 SELECT ROWNUM, X.EMPLOYEE_ID, X.FIRST_NAME, X.DEPARTMENT_NAME, X.HIRE_DATE
 FROM (SELECT E.EMPLOYEE_ID, E.FIRST_NAME, D.DEPARTMENT_NAME, E.HIRE_DATE
          FROM EMPLOYEES E, DEPARTMENTS D
          WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
          ORDER BY E.HIRE_DATE DESC) X
 WHERE ROWNUM <= 5;
 
 -- 80번 부서에서 급여를 가장 많이 받는 직원
 SELECT EMPLOYEE_ID
 FROM (SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 80)
 WHERE ROWNUM <= 1;
 
 -- Sundita와 같은 해에 입사한 사원 중에서 급여를 가장 많이 받는 직원 3명 구하기
SELECT *
FROM (SELECT * 
         FROM EMPLOYEES
         WHERE TO_CHAR(HIRE_DATE, 'YYYY') = (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
                                                  FROM EMPLOYEES
                                                  WHERE FIRST_NAME = 'Sundita')
ORDER BY SALARY DESC)
WHERE ROWNUM <= 3;
 
-- Steven King에게 보고하는 사원 중에서 가장 최근에 입사한 사원 3명 구하기
SELECT
FROM
WHERE
 
SELECT * 
FROM EMPLOYEES WORKER, EMPLOYEES MANAGER
WHERE WORKER.MANAGER_ID = MANAGER.EMPLOYEE_ID
AND MANAGER.EMPLOYEE_ID = 'Steven King'
 
MANAGER.FIRST_NAME || MANAGER.LAST_NAME = 
 
ORDER BY WORKER.HIRE_DATE DESC
 
WHERE ROWNUM <= 3;
 
-- 위의 쿼리는.. 조인으로 하려다 망한 코드
 
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM (SELECT * 
         FROM EMPLOYEES
         WHERE MANAGER_ID = (SELECT EMPLOYEE_ID
                                        FROM EMPLOYEES
                                        WHERE FIRST_NAME = 'Steven' AND LAST_NAME = 'King')
         ORDER BY HIRE_DATE DESC)
         WHERE ROWNUM <=3 ;
 
-- 분석함수 사용하기
-- ROW_NUMBER()는 행마다 순번을 부여한다.
-- 같은 순번을 가진 행이 존재하지 않는다. 표시되지 않는 순위가 있다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, RANK() OVER (ORDER BY SALARY DESC)
FROM EMPLOYEES; 
-- DENSE: 1, 2, 2, 3
-- DENSE 없을 때: 1, 2, 2, 4
-- DENSE_RANK()는 행마다 순위를 부여한다.
-- 같은 순위를 가진 행이 존재한다. 표시되지 않는 순위가 없다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, RANK() OVER (ORDER BY SALARY DESC)
FROM EMPLOYEES;
 
-- 급여순으로 정렬했을 때 5위~10위 사이에 속하는 직원 조회하기
SELECT RN, EMPLOYEE_ID, FIRST_NAME, SALARY
FROM (SELECT ROWNUM AS RN, EMPLOYEE_ID, FIRST_NAME, SALARY   -- 급여순으로 정렬된 결과에 순번 부여
         FROM (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY                 -- 급여순으로 내림차순 정렬
                  FROM EMPLOYEES
                  ORDER BY SALARY DESC))
WHERE RN >= 5 AND RN <= 10;
 
-- 급여순으로 정렬했을 때 11위~15위 사이에 속하는 직원 조회하기 (위의 것보다 추천함)
SELECT RN, EMPLOYEE_ID, FIRST_NAME, SALARY
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC) RN, EMPLOYEE_ID, FIRST_NAME, SALARY
         FROM EMPLOYEES)
         WHERE RN >= (3-1)*5+1 AND RN <= 3*5; -- !!중요!!
 
 
-- 뉴스정보를 저장하는 테이블을 작성하기
-- 뉴스번호, 뉴스제목, 뉴스카테고리, 작성자, 내용, 사진파일명, 언론사이름, 작성일자, 최종수정일자
-- 뉴스번호는 행을 대표하는 컬럼이다.
-- 뉴스제목, 카테고리, 작성자, 내용, 언론사이름은 null을 허용하지 않는다.
-- 작성일자와 최종수정일자는 기본값으로 현재날짜가 저장된다.
CREATE TABLE sample_news(
    NEWS_NO             NUMBER(8,0) PRIMARY KEY,
    NEWS_TITLE           VARCHAR2(100) NOT NULL,
    NEWS_CATEGORY    VARCHAR2(200) NOT NULL,
    NEWS_WRITER        VARCHAR2(100) NOT NULL,
    NEWS_CONTENTS    VARCHAR2(1000) NOT NULL,
    NEWS_PICNAME      VARCHAR2(200),
    NEWS_PRESS          VARCHAR2(200) NOT NULL,
    NEWS_CREATE_DATE DATE DEFAULT SYSDATE,
    NEWS_EDIT_DATE DATE DEFAULT SYSDATE
);
 
-- 뉴스정보에 대한 댓글을 저장하는 테이블을 작성하기
-- 댓글번호, 작성자, 내용, 작성일자, 추천수, 뉴스번호
-- 댓글번호는 행을 대표하는 컬럼이다.
-- 작성자, 내용, 뉴스번호는 null을 허용하지 않는다.
-- 작성일자는 기본값으로 현재날짜가 저장된다.
-- 추천수는 기본값으로 0이 저장된다.
-- 뉴스번호는 위에서 생성한 뉴스정보 테이블의 뉴스번호를 참조한다.
CREATE TABLE sample_replies (
    replies_NO                        NUMBER(8,0) PRIMARY KEY,
    replies_WRITER                   VARCHAR2(300) NOT NULL,
    replies_CONTENTS               VARCHAR2(4000) NOT NULL,
    replies_CREATE_DATE DATE    DEFAULT SYSDATE,
    replies_RECOMMAND           NUMBER(8,0) DEFAULT 0,
    NEWS_NO                         NUMBER(8,0) REFERENCES sample_news(NEWS_NO)
);
 
-- 뉴스정보테이블의 뉴스번호에 사용할 일련번호용 시퀀스를 생성하시오
-- 시작값을 100000으로 지정하고, 1씩 증가시키는 시퀀스
CREATE SEQUENCE sample_news_seq START WITH 100000 INCREMENT by 1;
 
-- 뉴스댓글테이블의 댓글번호에 사용할 일련번호용 시퀀스를 생성하시오.
CREATE SEQUENCE sample_replies_seq ;
 
-- 뉴스정보 테이블에 임의의 뉴스 정보를 데이터를 추가하는 쿼리를 작성하고, 뉴스 정보를 저장하시오.
INSERT INTO sample_news 
VALUES(0, '제목', '카테고리', '작자', '내용', '사진명', '언론사', SYSDATE, SYSDATE);
 
INSERT INTO sample_news (news_no, news_title, news_category, news_writer, news_contents, news_press)
VALUES(sample_news_seq.nextval, '제목제목', '카테고리고리', '작자작자', '내용내용', '언론사언론');
 
COMMIT;
 
SELECT * from sample_news;
 
-- 위에서 저장한 뉴스 정보에 대한 댓글 정보를 추가하는 쿼리를 작성하고, 댓글 정보를 저장하시오.
INSERT into SAMPLE_replies
VALUES (1, '작자', '내용', SYSDATE, 1, 0);
 
INSERT into SAMPLE_replies (replies_no, replies_writer, replies_contents, news_no)
VALUES (sample_replies_seq.nextval, '작자예용', '내용내용!', 100000);
 
COMMIT;
 
SELECT * FROM sample_replies;
 
-- 1800번 위치에 근무하는 사원의 아이디, 이름, 직종아이디, 부서아이디, 부서이름을 조회하기
-- join
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM LOCATIONS L, EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID
AND L.LOCATION_ID = 1800;
 
-- 이름이 'A'나 'a'로  시작하는 사원의 수를 조회하기
-- like
SELECT COUNT(*)
FROM EMPLOYEES
WHERE FIRST_NAME LIKE UPPER('A%');
 
-- 모든 부서의 아이디, 부서이름, 부서별 사원수를 조회하기
-- group by, inline view
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, COUNT(*)
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID, D.DEPARTMENT_NAME;
 
SELECT a.department_id, b.department_name, a.cnt
FROM (SELECT DEPARTMENT_id, count(*) cnt
         FROM EMPLOYEES
         group by DEPARTMENt_id) A, DEPARTMENTS B
where a.DEPartment_id=b.department_id
order by a.department_id asc;
 
-- 10번부서와 20번 부서에서 근무하는 사원들의 업무를 조회하기
-- distinct
SELECT DISTINCT(JS.JOB_TITLE)
FROM JOBS JS, EMPLOYEES E
WHERE JS.JOB_ID = E.JOB_ID
AND DEPARTMENT_ID IN (10, 20);
 
-- Administration  및 Executive 부서에 근무하는 직종 아이디별 사원수를 조회하기
-- subquery, group by
SELECT job_id, count(*)
FROM (SELECT js.job_id
         FROM DEPARTMENTS D, JOBS JS, EMPLOYEES E
         WHERE D.DEPARTMENT_NAME IN ('Administration', 'Executive')
         AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
         AND E.JOB_ID = JS.JOB_ID)
GROUP BY JOB_Id;
 
SELECT job_id, count(*)
FROM EMPLOYEES
where DEPARTMENT_ID in (SELECT DEPARTMENT_ID
                                  from DEPARTMENTS
                                  where DEPARTMENT_NAME in ('Administration', 'Executive'))
group by job_id;
 
-- 입사연도별 사원수를 조회했을 때 입사한 사원수가 가장 많은 년도 3개와 입사한 사원수 조회하기
-- group by, Top-N 분석
SELECT ROWNUM, hire_year, cnt
from (SELECT TO_CHAR(HIRE_DATE, 'yyyy') hire_year, count(*) cnt
FROM EMPLOYEES
group by to_char(hire_date, 'yyyy')
ORDER by cnt desc)
where rownum <= 3;
 
-- 관리자별 사원수를 조회했을 때 관리자의 아이디, 관리자의 이름, 사원수를 조회하기
-- group by, self join, inline view
SELECT worker.manager_id, manager.first_name, count(*)
FROM EMPLOYEES worker, EMPLOYEES manager
WHERE worker.MANAGER_ID = MANAGER.employee_id
GROUP BY worker.MANAGER_ID, manager.first_name
order by worker.manager_id;
 
-- 급여등급별 사원수를 조회해서 사원수가 많은 순으로 조회하기
-- group by, order by
SELECT b.gra, count(*) cnt
from EMPLOYEES a, JOB_GRADES B
where a.SALARY >= B.LOWEST_SALARY and A.SALARY <= B.HIGHEST_SALARY
group by b.gra
order by cnt desc;
 
-- 'Allan'과 같은 부서에 근무하는 사원들의 아이디, 이름, 급여를 조회하기
-- subquery
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
where department_id = (SELECT department_id
                              from EMPLOYEES
                              where FIRST_NAME = 'Allan');
                              
-- 'Allan'과 같은 부서에 근무하는 사원들의 아이디, 이름, 급여, 급여등급을 조회하기
-- subquery, 비등가 조인
 
SELECT * from JOB_GRADES;
-- 'Allan'과 같은 부서에 근무하는 사원들의 아이디, 이름, 급여, 부서 아이디, 부서이름을 조회하기
-- subquery, join
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, b.gra
FROM EMPLOYEES A, JOB_GRADES B
where DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                  FROM EMPLOYEES
                                  WHERE FIRST_NAME = 'Allan')
and A.salary >= b.lowest_salary and a.salary <= b.highest_salary;
                                  
-- 'Allan'과 같은 부서에 근무하는 사원들의 아이디, 이름, 급여를 조회하고, 급여를 가장 많이 받는 사원
-- 3명을 조회하기
-- subquery, inline view, top-n 분석
SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, SALARY
from EMPLOYEES
where DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                  FROM EMPLOYEES
                                  WHERE FIRST_NAME = 'Allan')
and ROWNUM <= 3;
 
-- 'Allan'과 같은 부서에 근무하는 사원들을 급여등급별로 사원수를 조회하기
-- subquery, 비등가조인, group by
SELECT JG.GRA, COUNT(*)
FROM EMPLOYEES E, JOB_GRADES JG
where E.SALARY >= JG.LOWEST_SALARY AND E.SALARY <= JG.HIGHEST_SALARY
AND DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                  FROM EMPLOYEES
                                  WHERE FIRST_NAME = 'Allan')
GROUP BY JG.GRA;
                                  
 
-- 'Allan'과 같은 부서에 근무하는 사원들의 급여를 10%인상시키기
-- subquery, update
UPDATE (SELECT *
            FROM EMPLOYEES
            WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                               FROM EMPLOYEES
                                               WHERE FIRST_NAME = 'Allan'))
SET SALARY = SALARY * 1.1;
 
Colored by Color Scripter
cs

'Oracle Database SQL' 카테고리의 다른 글

190416(2) (0)	2019.06.07
190416 (0)	2019.06.07
190411 (0)	2019.06.07
190410 (0)	2019.06.07
190409 (0)	2019.06.07

190411

monkey-k777 2019. 6. 7. 17:00

2019. 6. 7. 17:00

-- DDL
-- 데이터베이스 정의어
-- 데이터베이스 객체의 생성, 삭제, 수정과 관련된 SQL
-- CREATE문, DROP문, ALTER문
-- COMMIT, ROLLBACK의 적용대상이 아니다.
 
-- 오라클의 데이터베이스 객체
-- 테이블: 가장 기본적인 데이터 저장소, 행과 열로 구분되어 있다.
-- 뷰: 하나 이상의 테이블에 있는 데이터의 부분 집합으로 구성된 가상의 테이블이다.
-- 시퀀스: 일련번호 생성기
-- 인덱스: 쿼리의 실행 속도를 향상시키기 위해서 테이블의 데이터에 대한 색인을 갖고 있는 객체다.
 
SELECT ROWID, EMPLOYEE_ID
FROM EMPLOYEES;
 
-- 시퀀스
-- 일련번호 생성한다.
-- 여러 사용자가 공유할 수 있다.
-- 행의 고유한 기본키 값을 생성하는데 주로 사용된다.
-- 시퀀스는 테이블과는 별도로 저장, 관리되기 때문에 여러 테이블에서 동일한 시퀀스를 사용할 수 있다.
 
-- 시퀀스 생성하기
-- CREATE SEQUENCE 시퀀스명
CREATE SEQUENCE SAMPLE_SEQ;
 
CREATE SEQUENCE MY_SEQ;
 
-- 새로운 일련 번호 발급 받기
-- NEXTVAL을 사용하면 새로운 일련 번호를 발급받을 수 있다.
 
INSERT INTO BOOKS
(BOOK_NO, BOOK_TITLE, BOOK_AUTHOR, BOOK_PUBLISHER, BOOK_PRICE, BOOK_PUBDATE, BOOK_CREATE_DATE)
VALUES
(MY_SEQ.NEXTVAL, '이것이 자바다', '신용권', '한빛미디어', 30000, '2015-01-01', SYSDATE);
 
-- CURRVAL을 사용하면 현재 사용자가 방금 생성한 일련번호를 조회할 수 있다.
SELECT MY_SEQ.NEXTVAL FROM DUAL;
SELECT MY_SEQ.CURRVAL FROM DUAL;
 
-- 시퀀스 삭제하기
-- DROP SEQUENCE 시퀀스명
DROP SEQUENCE MY_SEQ;
 
 
-- 시퀀스 생성하기
CREATE SEQUENCE 시퀀스명
    INCREMENT BY 숫자값         번호 사이의 간격을 지정(기본값은 1)
    START WITH 숫자값            첫번째 일련번호 지정(기본값은 1)
    MAXVALUE 숫자값             시퀀스의 최댓값을 지정(기본값은 10^27)
    MINVALUE 숫자값              시퀀스의 최솟값을 지정(기본값은 1)
    CYCLE | NOCYCLE               최댓값 도달 이후에도 시퀀스를 계속 생성할지 여부(기본값은
    CACHE 숫자값 | NOCACHE;   일련번호를 미리 생성해서 메모리에 저장할 개수를 지정(기본값은 CACHE 20);
    
-- 시퀀스의 NEXTVAL, CURRVAL 사용처
-- 1. 서브쿼리에 속하지 않는 SELECT문의 SELECT절에서 사용 가능
-- 2. INSERT문의 VALUES 절
-- 3. UPDATE문의 SET절
 
-- 테이블 정의하기
-- 테이블의 데이터를 저장하는 객체
-- 테이블의 이름, 컬럼의 이름은 30자까지만 가능
-- A-Z, a-z, 0-9, _, $, #를 사용할 수 있다.
 
-- 테이블 생성하기
-- CREATE TABLE 테이블명 (
--     컬럼 이름, 데이터 타입(크기)
--     컬럼 이름, 데이터 타입(크기)
--     컬럼 이름, 데이터 타입(크기), DEFAULT 값
-- );
 
-- 오라클의 데이터 타입
-- VARCHAR2(SIZE)       가변 길이 문자 데이터(최대 4000 바이트)
--                             (이름, 주소, 블로그의 내용, 책의 제목, ...)
-- CHAR(SIZE)              고정 길이 문자 데이터(최대 2000 바이트)
--                             (우편 번호, 군번, 주민등록번호, 사업자등록번호,...)
-- NUMBER(P, S)          가변 길이 숫자 데이터(P는 십진 자릿수, S는 소수점 이하 자릿수)      
-- DATE                     날짜 및 시간
-- LONG                    최대 2GB의 가변 길이 문자 데이터
--                            테이블 당 하나만 정의할 수 있다. 제약 조건을 정의할 수 없다.
--                            GROUP BY, ORDER BY 절에 포함시킬 수 없다.
-- CLOB                     최대 4GB의 가변 길이 문자 데이터 CHARACTER LARGE OBJECT
-- BLOB                     최대 4GB의 이진(바이너리) 데이터 BINARY ~
-- ROWID                  테이블에서 행의 고유 주소를 나타내는 64진수
 
CREATE TABLE SAMPLE_CATEGORY (
    CATEGORY_CODE CHAR(2) PRIMARY KEY,        -- 고정 길이 문자 데이터
    CATEGORY_NAME VARCHAR2(200)                -- 가변 길이 문자 데이터
);
 
INSERT INTO SAMPLE_CATEGORY VALUES('FU', '가구');
INSERT INTO SAMPLE_CATEGORY VALUES('ED', '가전기기');
INSERT INTO SAMPLE_CATEGORY VALUES('KH', '주방기기');
INSERT INTO SAMPLE_CATEGORY VALUES('LG', '생활용품');
 
INSERT INTO SAMPLE_CATEGORY VALUES('E', '기타');
COMMIT;
 
DELETE SAMPLE_CATEGORY;
 
SELECT * FROM SAMPLE_CATEGORY
WHERE CATEGORY_CODE = 'E ';
 
-- 컬럼 레벨 제약 조건 설정
CREATE TABLE SAMPLE_PRODUCTS (
    PRODUCT_NO                    NUMBER(4)                     CONSTRAINT PRODUCTS_NO_PK                 PRIMARY KEY,                                                                                       -- 숫자 데이터, 기본키 제약 조건
    PRODUCT_NAME                VARCHAR2(500)               CONSTRAINT PRODUCTS_NAME_NN             NOT NULL,                                                                                           -- 가변 길이 문자 데이터, not null 제약 조건
    PRODUCT_MAKER               VARCHAR2(200),                                                                                                                                                                                 -- 가변 길이 문자 데이터, 
    PRODUCT_PRICE                 NUMBER(8, 0)                  CONSTRAINT PRODUCTS_PRICE_CK               CHECK (PRODUCT_PRICE > 0),                                                                    -- 숫자 데이터, CHECK 제약 조건
    PRODUCT_STOCK                NUMBER(4, 0)                  CONSTRAINT PRODUCTS_STOCK_CK              CHECK (PRODUCT_STOCK > 0),                                                                  -- 숫자 데이터, CHECK 제약 조건
    PRODUCT_SELL                   CHAR(1)                         CONSTRAINT PRODUCTS_SELL_CK                CHECK(PRODUCT_SELL IN ('Y', 'N')),
    PRODUCT_CATEGORY           CHAR(2)                         CONSTRAINT PRODUCT_CATEGORY_FK           REFERENCES SAMPLE_CATEGORY(CATEGORY_CODE),
    PRODUCT_CREATE_DATE DATE DEFAULT SYSDATE                                                                                                                       
);
 
 
-- 테이블 레벨 제약 조건 설정
CREATE TABLE SAMPLE_PRODUCTS (
    PRODUCT_NO                    NUMBER(4),
    PRODUCT_NAME                VARCHAR2(500)               CONSTRAINT PRODUCTS_NAME_NN             NOT NULL,                                                         -- 가변 길이 문자 데이터, not null 제약 조건은 테이블 레벨로 못 내림
    PRODUCT_MAKER               VARCHAR2(200),                                                                                                                                                                                 -- 가변 길이 문자 데이터, 
    PRODUCT_PRICE                 NUMBER(7)                  CONSTRAINT PRODUCTS_PRICE_NN               NOT NULL,                                                                    -- 숫자 데이터, CHECK 제약 조건
    PRODUCT_STOCK                NUMBER(3)                  CONSTRAINT PRODUCTS_STOCK_NN              NOT NULL,                                                                  -- 숫자 데이터, CHECK 제약 조건
    PRODUCT_SELL                   CHAR(1)                     DEFAULT 'Y' CONSTRAINT PRODUCTS_SELL_NN NOT NULL,
    PRODUCT_CATEGORY           CHAR(2)                         CONSTRAINT PRODUCT_CATEGORY_NN       NOT NULL,
    PRODUCT_CREATE_DATE DATE DEFAULT SYSDATE,    
 
    CONSTRAINT PRODUCTS_NO_PK PRIMARY KEY (PRODUCT_NO),
    CONSTRAINT PRODUCTS_PRICE_CK CHECK (PRODUCT_PRICE > 0),
    CONSTRAINT PRODUCTS_STOCK_CK CHECK (PRODUCT_STOCK > 0),
    CONSTRAINT PRODUCTS_SELL_CK CHECK (PRODUCT_SELL IN ('Y', 'N')),
    CONSTRAINT PRODUCTS_CATEGORY_FK FOREIGN KEY (PRODUCT_CATEGORY) REFERENCES SAMPLE_CATEGORY (CATEGORY_CODE)
);
 
 
 
CREATE SEQUENCE PRODUCTS_SEQ START WITH 1000;
 
INSERT INTO SAMPLE_PRODUCTS
(PRODUCT_NO, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_STOCK, PRODUCT_SELL, PRODUCT_CATEGORY)
VALUES
(PRODUCTS_SEQ.NEXTVAL, '양문형 냉장고', 1000000, 10, 'Y', 'FU');
 
INSERT INTO SAMPLE_PRODUCTS(PRODUCT_NO, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_SELL)
VALUES (PRODUCTS_SEQ.NEXTVAL, '사계절 김치 냉장고', 10000000);
 
INSERT INTO SAMPLE_PRODUCTS(PRODUCT_NO, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_STOCK, PRODUCT_DISCOUNT_RATE, CATEGORY_CODE)
VALUES
(PRODUCTS_SEQ.NEXTVAL, '가죽 소파', 1000000, 10, 0.00, 'FU');
 
 
SELECT * FROM SAMPLE_PRODUCTS();
DROP TABLE SAMPLE_PRODUCTS;
DROP_TABLE SAMPLE_CATEGORY;
Colored by Color Scripter
cs

'Oracle Database SQL' 카테고리의 다른 글

190416 (0)	2019.06.07
190415 (0)	2019.06.07
190410 (0)	2019.06.07
190409 (0)	2019.06.07
190408 (0)	2019.06.07

190410

monkey-k777 2019. 6. 7. 16:59

2019. 6. 7. 16:59

CREATE TABLE STORE_USERS (
    USER_ID VARCHAR2(20) PRIMARY KEY,
    USER_NAME VARCHAR2(100),
    USER_PWD VARCHAR2(20),
    USER_PHONE VARCHAR2(200),
    USER_EMAIL VARCHAR2(200),
    USER_POINT NUMBER(7),
    USER_CREATE_DATE DATE DEFAULT SYSDATE
);
 
SELECT * FROM store_users;
cs

'Oracle Database SQL' 카테고리의 다른 글

190415 (0)	2019.06.07
190411 (0)	2019.06.07
190409 (0)	2019.06.07
190408 (0)	2019.06.07
190405 (0)	2019.06.07

190409

monkey-k777 2019. 6. 7. 16:58

2019. 6. 7. 16:58

create table user_contacts(
                user_no number(3) primary key,
                user_name varchar2(20),
                user_phone varchar2(20),
                user_create_date date
                );
                
drop table user_contacts;
 
SELECT * FROM USER_CONTACTS;
 
DELETE FROM USER_CONTACTS;
 
CREATE TABLE books (
    book_no            number(4) PRIMARY KEY,
    book_title          VARCHAR2(500),
    book_author       VARCHAR2(200),
    book_publisher    VARCHAR2(200),
    book_price         number(7),
    book_pubdate     VARCHAR2(10),     -- '2016-12-31'
    book_create_date date DEFAULT SYSDATE
);
select * from books;
 
--    1. 사원 아이디를 입력받아서 사원 아이디, 이름, 직종 아이디, 급여, 커미션, 부서 아이디, 부서 이름을 조회하는 기능
SELECT  E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.SALARY, E.COMMISSION_PCT, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
AND E.EMPLOYEE_ID = ;
 
 
--    2. 입사년도를 입력받아서 사원 아이디, 이름, 입사일, 부서 이름을 조회하는 기능
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.HIRE_DATE, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.EMPLOYEE_ID = D.DEPARTMENT_ID
AND TO_CHAR(E.HIRE_DATE, 'YYYY') = ? ;
 
--    3. 급여 등급을 입력받아서 사원 아이디, 이름, 급여, 급여 등급을 조회하는 기능
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, JG.GRA
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
AND JG.GRA = ? ;
 
--    4. 부서 아이디를 입력받아서 사원 아이디, 이름, 부서 아이디, 부서 이름을 조회하는 기능
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.EMPLOYEE_ID = D.DEPARTMENT_ID
AND E.DEPARTMENT_ID = ? ;
 
--    5. 관리자 이름을 입력받아서 해당 관리자에게 보고하는 직원의 아이디, 이름, 입사일을 조회하는 기능
SELECT WORKER.EMPLOYEE_ID, WORKER.FIRST_NAME, WORKER.HIRE_DATE
FROM EMPLOYEES WORKER, EMPLOYEES MANAGER
WHERE WORKER.MANAGER_ID = MANAGER.EMPLOYEE_ID;
Colored by Color Scripter
cs

'Oracle Database SQL' 카테고리의 다른 글

190411 (0)	2019.06.07
190410 (0)	2019.06.07
190408 (0)	2019.06.07
190405 (0)	2019.06.07
190404 (0)	2019.06.07

190408

monkey-k777 2019. 6. 7. 16:58

2019. 6. 7. 16:58

-- 단일행 서브쿼리 연산자
-- >, <, >=, <=, =, <>
 
-- 'Hermann'보다 급여를 많이 받는 사원의 이름과 급여를 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT SALARY
                        FROM EMPLOYEES
                        WHERE FIRST_NAME = 'Hermann');
 
-- 전체 사원들의 평균 급여보다 급여를 적게 받는 사원의 이름과 급여를 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
                        FROM EMPLOYEES);
                        
-- 부서별로 평균 급여를 계산했을 때 평균 급여가 가장 낮은 부서의 아이디와 평균 급여 조회하기
SELECT DEPARTMENT_ID, TRUNC(AVG(SALARY))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY) = (SELECT MIN(AVG(SALARY))
                                 FROM EMPLOYEES
                                 GROUP BY DEPARTMENT_ID);
                                 
-- 다중행 서브쿼리 연산자
-- IN: 서브쿼리가 반환하는 값 목록 중 어느 하나와 일치하면 TRUE가 반환된다(해당 행은 선택된다).
-- ANY: 서브쿼리가 반환하는 값 목록 중 어느 하나만 제시된 조건이 만족하면 TRUE 반환
-- ALL: 서브쿼리가 반환하는 값 조건 목록 모두 만족하면 TRUE 반환
-- SALARY > ANY (서브 쿼리)       SALARY > ALL (서브 쿼리)          SALARY = ANY (서브 쿼리)
-- SALARY > 최솟값                   SALARY > 최댓값                     SALARY IN (서브 쿼리)
 
-- 직종별 평균 급여를 계산했을 때 급여 평균이 3000미만인 직종에 근무하는 사원의 이름, 직종, 급여 조회하기
SELECT FIRST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE JOB_ID IN (SELECT JOB_ID
                       FROM EMPLOYEES
                       GROUP BY JOB_ID
                       HAVING AVG(SALARY) < 3000);
                       
                      
-- 직종이 IT_PROG인 사원들보다 급여를 많이 받는 사원들의 이름, 직종, 급여 조회하기
SELECT *
FROM EMPLOYEES
WHERE SALARY > ANY (6000, 5500, 8000); -- ANY는 최솟값과 같은 의미.
 
SELECT *
FROM EMPLOYEES
WHERE SALARY > ALL (6000, 5500, 8000); -- ALL은 최댓값과 같은 의미.
 
-- 상사가 없는 직원 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE MANAGER_ID IN (SELECT MANAGER_ID
                                FROM EMPLOYEES
                                WHERE MANAGER_ID IS NULL);
-- 널이면 같다, 작다 비교가 안 됨.
 
-- 직종이 IT_PROG인 사원들보다 급여를 많이 받는 사원들의 이름, 직종, 급여를 조회하기
-- IT_PROG 직종의 최고 급여보다 급여를 많이 받는 사원을 조회
SELECT FIRST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE SALARY > ALL (SELECT SALARY
                              FROM EMPLOYEES
                              WHERE JOB_ID = 'IT_PROG')
AND JOB_ID <> 'IT_PROG';
 
-- IT_PROG 직종의 최소 급여보다 급여를 많이 받는 사원을 조회
SELECT FIRST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE SALARY > ANY (SELECT SALARY
                              FROM EMPLOYEES
                              WHERE JOB_ID = 'IT_PROG')
AND JOB_ID <> 'IT_PROG';
                              
-- 사용자 정의 데이터베이스 객체(테이블) 생성하기
CREATE TABLE COURSES (
     COURSE_NO NUMBER(3) PRIMARY KEY,  -- 과정 번호
     COURSE_NAME VARCHAR2(200),          -- 과정명
     COURSE_PRICE NUMBER(7),                 -- 수강료
     COURSE_QUOTA NUMBER(3),              -- 모집 인원
     COURSE_TEACHER VARCHAR2(100),      -- 강사
     COURSE_START_DATE DATE,               -- 시작일
     COURSE_END_DATE DATE,                  -- 종료일
     COURSE_ROOM VARCHAR2(20),          -- 강의장
     COURSE_CREATE_DATE DATE              -- 데이터 생성일
);
 
-- 테이블에 새로운 행을 추가
-- INSERT INTO 구문을 사용한다.
-- 1. 컬럼명을 명시해서 INSERT 구문을 작성한다.
-- (테이블의 전체 컬럼명 혹은 값이 결정된 컬럼명만 적을 수도 있다.)
-- INSERT INTO 테이블명 (컬럼명1, 컬럼명2, ...)
-- VALUES                   (값1,       값2,      ...)
-- 2. 컬럼명을 생략하고 INSERT 구문을 작성한다.
-- (테이블의 모든 컬럼에 해당하는 값이 VALUES절에 명시되어야 한다(NULL값도 포함).)
-- INSERT INTO 테이블명
-- VALUES (값1, 값2, 값3, ...)
-- 한 번에 한 행만 추가한다.
-- 값을 표기할 때 문자 및 날짜는 작은 따옴표로 묶는다.
 
-- 새 과정 추가하기
INSERT INTO COURSES (COURSE_NO, COURSE_NAME, COURSE_PRICE, COURSE_QUOTA, COURSE_TEACHER, COURSE_START_DATE, COURSE_END_DATE, COURSE_ROOM, COURSE_CREATE_DATE)
VALUES(100, '자바 개발자 과정', 1000000, 20, '홍길동', '2019/05/01', '2019/12/31', 'L2', SYSDATE);
COMMIT;
 
-- 새 과정 추가하기
INSERT INTO COURSES (COURSE_NO, COURSE_NAME, COURSE_PRICE, COURSE_QUOTA, COURSE_CREATE_DATE)
VALUES(200, '안드로이드 앱 개발자 과정', 1200000, 15, SYSDATE);
 
INSERT INTO COURSES (COURSE_NO, COURSE_NAME, COURSE_PRICE, COURSE_QUOTA, COURSE_TEACHER, COURSE_START_DATE, COURSE_END_DATE, COURSE_ROOM, COURSE_CREATE_DATE)
VALUES (400, '데이터분석가 과정', 2500000, NULL, NULL, NULL, NULL, 'L3');
 
 
SELECT * FROM COURSES;
 
-- 테이블의 데이터 변경하기
-- UPDATE 구문을 사용하면 하나 혹은 여러 행의 데이터를 변경할 수 있다.
-- UPDATE 테이블명
-- SET 컬럼명1 = 값1, 컬럼명2 = 값2, 컬럼명3 = 값3, ...
-- [WHERE 조건식]
-- WHERE 절은 생략할 수 있다. 생략하면 모든 행에서 지정된 컬럼의 값을 갱신한다.
 
-- 100번 과정의 수강료를 50만 원으로 변경하기
UPDATE COURSES
SET COURSE_PRICE = 500000
WHERE COURSE_NO = 100;
 
-- 200번 과정의 강사명, 시작일, 종료일, 강의실 변경하기
UPDATE COURSES
SET COURSE_TEACHER = '이순신',
     COURSE_START_DATE = '2019/05/25',
     COURSE_END_DATE = '2019/08/25',
     COURSE_ROOM = 'L6'
WHERE COURSE_NO = 200;
 
-- 모든 직원들의 급여를 1000달러 인상하기
UPDATE EMPLOYEES
SET SALARY = SALARY + 1000;
 
SELECT * FROM EMPLOYEES;
 
-- 2007년 입사자의 평균 급여
SELECT TRUNC(AVG(SALARY))
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2007';
 
-- 132번 TJ의 급여를 2007년 입사들의 평균 급여의 70% 수준으로 연장
UPDATE EMPLOYEES
SET SALARY = (SELECT TRUNC(AVG(SALARY) * 0.7)
                   FROM EMPLOYEES
                   WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2007')
WHERE EMPLOYEE_ID = 132;
 
-- 100번 직원의 부서 번호를 300번으로 변경하기
UPDATE EMPLOYEES
SET DEPARTMENT_ID = 300 -- 300번에 해당하는 부서번호가 존재하지 않으므로 부서번호를 300번으로 바꿀 수 없음
WHERE EMPLOYEE_ID = 100;
 
-- 100번 과정의 수강료를 변경하기
UPDATE COURSES
SET COURSE_PRICE = 12000000
WHERE COURSE_NO = 100;
 
UPDATE DEPARTMENTS
SET DEPARTMENT_ID = 300
WHERE DEPARTMENT_ID = 270;
 
-- 제약 조건 2가지
-- 부모 테이블에 없는 값으로 자식 레코드 임의 변경 불가
-- 자식 테이블에서 쓰고 있는 값을 임의로 변경 불가
 
-- 테이블에서 행 삭제하기
-- DELETE 구문을 사용하면 하나 혹은 여러 개의 행을 삭제할 수 있다.
-- DELETE FROM 테이블명
-- [WHERE 조건식] 조건식을 생략하면 테이블 내의 모든 행이 삭제된다.
 
-- 과정 번호가 100번인 행 삭제하기
DELETE FROM COURSES
WHERE COURSE_NO = 100;
 
-- 홍길동이 강의하는 모든 과정 삭제하기
DELETE FROM COURSES
WHERE COURSE_TEACHER = '홍길동';
 
SELECT * FROM COURSES;
 
-- 모든 과정 삭제하기
DELETE FROM COURSES; -- 데이터만 삭제(아직 메모리 용량은 그대로 차지), 롤백 가능
 
ROLLBACK;
 
-- 모든 과정 삭제하기
TRUNCATE TABLE COURSES; -- 최초의 데이터 생성 시점으로, 롤백 불가, 트랜잭션 로그 기록 안 함.
-- DROP TABLE EMPLOYEES : 롤백 불가, 테이블이 없어짐
 
 
CREATE TABLE STORES (
     STORE_NO NUMBER(3) PRIMARY KEY,      -- 상점 번호
     STORE_NAME VARCHAR2(200),              -- 상점 이름
     STORE_OWNER VARCHAR2(100),            -- 점주명
     STORE_TEL VARCHAR2(20),                   -- 상점 전화 번호
     STORE_EMP_COUNT NUMBER(2),             -- 직원수
     STORE_LOCATION VARCHAR2(100),         -- 상점 소재지
     STORE_OPEN_DATE DATE,                     -- 상점 오픈 예정일
     STORE_CREATE_DATE DEFAULT SYSDATE   -- 데이터 생성일
);
 
-- 상점 3개 저장하기
INSERT INTO STORES
VALUES (001, '가', '가가가', '111-1111', 01, '서울', '2019/04/01', '2019/04/08');
 
INSERT INTO STORES
VALUES (002, '나', '나나나', '222-2222', 02, '경기', '2019/04/02', '2019/04/08');
 
INSERT INTO STORES
VALUES (003, '다', '다다다', '333-3333', 03, '강원', '2019/04/03', '2019/04/08');
 
SELECT * FROM STORES;
 
-- 최초 저장된 3개의 상점 중에서 하나를 선택해서 직원 수를 변경하기
UPDATE STORES
SET STORE_EMP_COUNT = 99
WHERE STORE_NO = 1;
 
-- 최초 저장된 3개의 상점 중에서 하나를 선택해서 연락처, 직원수, 오픈 예정일을 변경하기
UPDATE STORES
SET STORE_TEL = '999-9999',
      STORE_EMP_COUNT = 99,
      STORE_OPEN_DATE = '1111-11-11'
WHERE STORE_NO = 2;
 
-- 최초 저장된 3개의 상점 중에서 하나를 선택해서 지우기
DELETE FROM STORES
WHERE STORE_NO = 3;
 
-- 모든 상점 정보를 지우기
TRUNCATE TABLE STORES;
 
SELECT A.FIRST_NAME, A.DEPARTMENT_ID || '(' || B.DEPARTMENT_NAME || ')'
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;
 
-- 부서이름별 사원수, 평균 급여를 조회하기
SELECT DEPARTMENTS.DEPARTMENT_NAME, COUNT(*), AVG(EMPLOYEES.SALARY)
FROM DEPARTMENTS, EMPLOYEES
WHERE DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME;
 
-- 과제
-- 최고 급여와 최저급여의 차액을 조회하기
SELECT MAX(SALARY) - MIN(SALARY)
FROM EMPLOYEES;
 
-- 부서 소재지(city)별 직원수를 조회하기
SELECT C.CITY, COUNT(*)
FROM EMPLOYEES A, DEPARTMENTS B, LOCATIONS C
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
AND B.LOCATION_ID = C.LOCATION_ID
GROUP BY C.CITY;
 
-- 'Hermann'과 같은 해에 입사한 사원들의 이름과 입사일 조회하기 --> 서브 쿼리
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
                                                  FROM EMPLOYEES
                                                  WHERE FIRST_NAME = 'Hermann')
AND FIRST_NAME <> 'Hermann';
 
-- 50번 부서의 평균급여보다 적은 급여를 받는 다른 부서 직원의 이름과 급여를 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
                        FROM EMPLOYEES
                        WHERE DEPARTMENT_ID = 50)
AND DEPARTMENT_ID <> 50;
 
-- 1700번 지역에 소재지를 두고 있는 새로운 부서를 추가하기
-- (부서 아이디, 부서명, 관리자 아이디, 지역번호 입력)
INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (280, 'SI', 104, 1700);
 
-- 위에서 생성한 부서에 새로운 사원을 추가하기
-- (사원아이디, 이름1, 이름2, 이메일, 입사일(오늘), 직종아이디, 급여, 부서아이디 )
INSERT INTO EMPLOYEES
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID)
VALUES
(207, '길동', '홍', 'hong@gmail.com', SYSDATE, 'IT_PROG', 5000, 280);
Colored by Color Scripter
cs

'Oracle Database SQL' 카테고리의 다른 글

190410 (0)	2019.06.07
190409 (0)	2019.06.07
190405 (0)	2019.06.07
190404 (0)	2019.06.07
190403 (0)	2019.06.07

190405

monkey-k777 2019. 6. 7. 16:57

2019. 6. 7. 16:57

-- 80번 부서에 근무하는 사원 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID = 80;
 
-- 커미션을 받는 사원 중에서 급여가 10000달러 이상인 사원 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES E
WHERE E.COMMISSION_PCT IS NOT NULL
AND E.SALARY >= 10000;
 
-- 사원들 중에서 급여 등급이 B등급인 사원 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
AND JG.GRA = 'B';
 
-- 부서별 사원 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
 
-- 오류 DEPARTMENT_ID는 107개, COUNT는 1개 나오므로.
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES;
 
-- 그룹 바이 절에 등장한 컬럼은 가능
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
 
-- SELECT 5
-- FROM 1
-- WHERE 2
-- GROUP BY 3
-- HAVING 4 그룹함수가 적용된 행에 제한을 가함. 따라서 그룹 바이가 있어야 함.
-- ORDER BY 6
-- GROUP BY 절에서는 SELECT의 별칭을 사용할 수 없다.
-- GROUP 함수는 한 번만 중첩이 가능하다.
-- SUM(COUNT(*)) 가능
-- AVG(SUM(COUNT(*))) 불가능
 
-- 부서별 사원 수 조회하기
SELECT DEPARTMENT_ID, COUNT(*) AS CNT
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) >= 30;
 
-- 부서별 평균 급여를 조회하기(10000달러 미만)
SELECT E.DEPARTMENT_ID, TRUNC(AVG(E.SALARY))
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID IS NOT NULL
GROUP BY E.DEPARTMENT_ID
HAVING TRUNC(AVG(E.SALARY)) < 10000;
 
-- 급여 등급별 사원 수를 조회하기
SELECT JG.GRA, COUNT(*)
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
GROUP BY JG.GRA
ORDER BY 1;
 
-- 급여별 사원 수를 조회하기
-- 2000    10    2000~2999
-- 3000    14    3000~3999
-- 4000     7    4000~4999
-- 5000    11    5000~5999
SELECT TRUNC(SALARY, -3), COUNT(*)
FROM EMPLOYEES
GROUP BY TRUNC(SALARY, -3)
ORDER BY 1;
 
SELECT SUBSTR(FIRST_NAME, 1, 1), COUNT(*)
FROM EMPLOYEES
GROUP BY SUBSTR(FIRST_NAME, 1, 1)
ORDER BY 1;
 
-- 부서별, 직종별 사원수 계산하기
SELECT DEPARTMENT_ID, JOB_ID, COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY 1, 2;
 
-- 부서 소재지(LOCATION의 CITY)별 사원 수 조회하기
SELECT L.CITY, COUNT(L.CITY)
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND E.DEPARTMENT_ID IS NOT NULL
GROUP BY L.CITY
ORDER BY L.CITY;
 
-- 각 부서별 최고/최저/평균 급여를 조회하기
SELECT D.DEPARTMENT_NAME, MAX(E.SALARY), MIN(E.SALARY), TRUNC(AVG(E.SALARY))
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.DEPARTMENT_ID IS NOT NULL -- 있어도 그만, 없어도 그만
GROUP BY D.DEPARTMENT_NAME
HAVING MAX(E.SALARY) > 10000;
 
-- 서브쿼리
-- 다른 SQL문의 WHERE절에서 사용되는 쿼리
-- SELECT
-- FROM
-- WHERE 컬럼 연산자 (SELECT 컬럼 FROM WHERE)
 
-- 전체 사원의 평균급여보다 급여를 적게 받는 사원의 아이디, 이름, 급여를 조회하기
-- 1. 평균 급여를 계산한다
 
 
-- 2. 평균 급여보다 급여를 적게 받는 사원
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
                        FROM EMPLOYEES);
                        
-- Neena와 같은 해에 입사한 사원의 아이디, 이름, 입사일 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') IN (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
                                                  FROM EMPLOYEES
                                                  WHERE FIRST_NAME = 'Steven');         -- 다중행 서브쿼리일 땐 IN으로 해야 안전, 가능하면 IN으로 할 것.
 
 
-- Sundita 직원과 같은 부서에 일하고, Sundita보다 급여를 많이 받는 사원 조회하기
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                   FROM EMPLOYEES
                                   WHERE FIRST_NAME = 'Sundita')
AND SALARY > (SELECT SALARY
                     FROM EMPLOYEES
                     WHERE FIRST_NAME = 'Sundita');
 
-- 급여를 가장 적게 받는 사원과 같은 해에 입사한 사원의 이름, 입사일, 급여를 조회하기
 
SELECT FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
                                                  FROM EMPLOYEES
                                                  WHERE SALARY = (SELECT MIN(SALARY)
                                                                          FROM EMPLOYEES) );
                                                                          
-- 오라클 에러
-- 테이블 이름이 틀린 경우: TABLE OR VIEW DOES NOT EXISTS
-- SELECT *
-- FROM EMPLOYEE;
 
-- 컬럼의 이름이 틀린 경우: INVALID IDENTIFIER. 식별자가 잘못되었다네.
-- SELECT NAMED
-- FROM EMPLOYEES;
 
-- 콤마가 누락된 경우: FROM keyword not found where expected
-- SELECT FIRST_NAME LAST_NAME SALARY -- 두 개만 쓰면 AS 별칭으로 인식해서 실행은 됨
-- FROM EMPLOYEES; 
 
-- 콤마가 너무 많은 경우: missing expression 뒤에 하나 더 와야 하는데 안 왔네요?
-- SELECT FIRST_NAME, LAST_NAME, SALARY,
-- FROM EMPLOYEES;
 
-- FROM 키워드에 오타가 있는 경우: FROM keyword not found where expected
-- SELECT FIRST_NAME, LAST_NAME, SALARY
-- FORM EMPLOYEES;
 
-- SELECT 키워드에 오타가 있는 경우: invalid SQL statement
-- SELET FIRST_NAME, LAST_NAME, SALARY
-- FROM EMPLOYEES;
 
-- WHERE 키워드에 오타가 있는 경우: SQL command not properly ended
-- SELECT FIRST_NAME, LAST_NAME, SALARY
-- FROM EMPLOYEES
-- WHER DEPARTMENT_ID = 10;
 
-- 모든 사원의 급여 최고액/최저액/평균을 조회하기
SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEES;
 
-- 직종별 사원수를 조회하기(직종아이디, 사원수)
SELECT E.JOB_ID, COUNT(*)
FROM EMPLOYEES E
WHERE E.JOB_ID IS NOT NULL
GROUP BY E.JOB_ID
ORDER BY E.JOB_ID;
 
-- 직종별 사원수를 조회했을 때, 사원수가 10명이 넘어가는 직종과 사원수를 조회하기
SELECT JOB_ID, COUNT(*)
FROM EMPLOYEES 
GROUP BY JOB_ID
HAVING COUNT(*) > 10;
 
-- 관리자별 사원수를 조회하기(관리자 이름, 사원수)
SELECT manager.FIRST_NAME, COUNT(*)
FROM EMPLOYEES worker, EMPLOYEES manager
WHERE worker.MANAGER_ID = manager.EMPLOYEE_ID
GROUP BY MANAGER.FIRST_name;
 
-- 입사년도별 사원수를 조회하기(연도, 사원수)
SELECT TO_CHAR(HIRE_DATE, 'YYYY'), COUNT(*)
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')
ORDER BY TO_CHAR(HIRE_DATE, 'YYYY');
 
-- 급여 등급별 사원수를 조회하기(급여등급, 사원수)
SELECT JG.GRA, COUNT(*)
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
GROUP BY JG.GRA
ORDER BY JG.GRA;
 
-- "Hermann"가 부서담당자로 지정된 부서에 소속된 사원의 아이디와 이름, 입사일을 조회하기
SELECT 직원.EMPLOYEE_ID, 직원.FIRST_NAME, 직원.HIRE_DATE
FROM EMPLOYEES 직원, EMPLOYEES 담당자
WHERE 직원.MANAGER_ID= 담당자.EMPLOYEE_ID
AND 담당자.FIRST_NAME = 'Hermann'; -- 틀렸어 ㅜㅜ
 
-- 서브 쿼리로 구하기
-- 1. 사원 번호 구하기
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE FIRST_NAME = 'Hermann';
 
-- 2. 1이 관리자로 있는 부서 찾기
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE MANAGER_ID = 204;
 
-- 3. 2 부서에 근무하는 사원 구하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 70;
 
-- 종합
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                  FROM DEPARTMENTS
                                  WHERE MANAGER_ID = (SELECT EMPLOYEE_ID
                                                                 FROM EMPLOYEES
                                                                 WHERE FIRST_NAME = 'Hermann'));
 
-- 조인으로 구하기
SELECT WORKER.EMPLOYEE_ID, WORKER.FIRST_NAME, WORKER.HIRE_DATE
FROM EMPLOYEES WORKER, EMPLOYEES MANAGER, DEPARTMENTS DEPT
WHERE MANAGER.EMPLOYEE_ID = DEPT.MANAGER_ID
AND DEPT.DEPARTMENT_ID = WORKER.DEPARTMENT_ID
AND MANAGER.FIRST_NAME = 'Hermann';
 
-- 최고 급여가 $10000를 넘는 부서별 부서의 번호 및 평균 급여를 조회하기
SELECT DEPARTMENT_ID, TRUNC(AVG(SALARY))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY) >= 10000
ORDER BY DEPARTMENT_ID;
 
-- 관리자별 번호 및 해당 관리자에 속한 사원의 최저 급여를 조회하기
SELECT MANAGER.EMPLOYEE_ID, MIN(WORKER.SALARY)
FROM EMPLOYEES WORKER, EMPLOYEES MANAGER
WHERE WORKER.MANAGER_ID = MANAGER.EMPLOYEE_ID
GROUP BY MANAGER.EMPLOYEE_ID
ORDER BY MANAGER.EMPLOYEE_ID;
 
-- 'Hermann'와 같은 급여등급에 속하는 사원들의 이름과 급여를 조회하기
 
SELECT A.FIRST_NAME, A.SALARY, B.GRA
FROM EMPLOYEES A, JOB_GRADES B
WHERE A.SALARY >= B.LOWEST_SALARY AND A.SALARY <= B.HIGHEST_SALARY
AND B.GRA = (SELECT Y.GRA
                  FROM EMPLOYEES X, JOB_GRADES Y
                  WHERE X.SALARY >= Y.LOWEST_SALARY AND X.SALARY <= Y.HIGHEST_SALARY
                  AND X.FIRST_NAME = 'Hermann');
-- 메인 쿼리와 서브 쿼리 별칭을 다르게 하라.
 
 
-- 엄청 어려운 문제. 인터넷에서 검색해보고 풀 것. 입사연도별 사원수 조회하기
-- 조회 예)
-- 2001 2002 2003 2004 2005 2006 2007 2008
-- 1     7      6      10    29    24    19   11
SELECT 
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2001', 1, 0)) "2001년", 
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2002', 1, 0)) "2002년", 
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2003', 1, 0)) "2003년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2004', 1, 0)) "2004년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2005', 1, 0)) "2005년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2006', 1, 0)) "2006년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2007', 1, 0)) "2007년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2008', 1, 0)) "2008년"
 
FROM EMPLOYEES;
Colored by Color Scripter
cs

'Oracle Database SQL' 카테고리의 다른 글

190409 (0)	2019.06.07
190408 (0)	2019.06.07
190404 (0)	2019.06.07
190403 (0)	2019.06.07
190402 (0)	2019.06.07

190404

monkey-k777 2019. 6. 7. 16:57

2019. 6. 7. 16:57

-- 비등가조인 실습을 위한 테이블 생성
CREATE TABLE JOB_GRADES (
   GRA CHAR(1) NOT NULL PRIMARY KEY,
   LOWEST_SALARY NUMBER(7),
   HIGHEST_SALARY NUMBER(7)
);
 
 
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('A', 1000,    2999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('B', 3000,    5999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('C', 6000,    9999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('D', 10000, 14999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('E', 15000, 24999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('F', 25000, 40000);
COMMIT;
 
-- 비등가 조인
-- = 연산자가 아닌 다른 연산자를 사용해서 조인 조건을 정의하는 조인
 
-- 사원의 이름, 사원의 급여, 급여 등급을 조회하기
-- 등호(=)를 사용하는 것이 아니라, 급여가 최소 급여와 최대 급여 사이에 해당하는 등급 관련 행이 조인되어야 함.
 
SELECT A.FIRST_NAME, A.SALARY, B.GRA
FROM EMPLOYEES A, JOB_GRADES B
WHERE A.SALARY >= B.LOWEST_SALARY
AND A.SALARY <= B.HIGHEST_SALARY
ORDER BY A.SALARY DESC;
 
-- 포괄조인(아우터조인:OUTER JOIN)
-- 포괄 조인을 사용하면 조인 조건을 만족하지 않는 행도 조회된다.
-- 포괄 조인 연산자는 +
 
-- 사원 이름, 부서 아이디, 부서 이름 조회하기
SELECT A.FIRST_NAME, A.DEPARTMENT_ID, B.DEPARTMENT_NAME
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID(+);
 
-- 부서 아이디, 부서 이름, 부서 담당자 아이디, 부서 담당자 이름, 부서 담당자 직종 아이디를 조회하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANAGER_ID, E.FIRST_NAME, E.JOB_ID
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.MANAGER_ID = E.EMPLOYEE_ID(+)
ORDER BY D.DEPARTMENT_ID;
 
-- 부서 아이디, 부서 이름, 부서 담당자 아이디, 부서 담당자 이름, 그 부서의 주소를 조회하기
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, A.MANAGER_ID, B.FIRST_NAME, C.CITY
FROM DEPARTMENTS A, EMPLOYEES B, LOCATIONS C
WHERE A.MANAGER_ID = B.EMPLOYEE_ID(+)
AND A.LOCATION_ID = C.LOCATION_ID
ORDER BY A.DEPARTMENT_ID;
 
-- 사원의 이름, 사원의 급여, 급여 등급, 소속 부서 이름을 조회하기(107명이 조회되어야 함)
SELECT A.FIRST_NAME, A.SALARY, B.GRA, C.DEPARTMENT_NAME
FROM EMPLOYEES A, JOB_GRADES B, DEPARTMENTS C
WHERE A.DEPARTMENT_ID = C.DEPARTMENT_ID(+)
AND A.SALARY >= B.LOWEST_SALARY
AND A.SALARY <= B.HIGHEST_SALARY
ORDER BY A.FIRST_NAME ASC;
 
-- 셀프 조인
-- 자기 자신의 테이블과 다시 조인을 해야하는 경우 사용된다.
-- 상하관계(재귀관계)가 있는 조직도, 메뉴 구성 정보, 카테고리 구성 정보 등이 셀프 조인을 통해서
-- 필요한 데이터를 조회할 수 있는 경우이다.
-- 같은 테이블을 서로 조인하기 때문에 테이블마다 적절한 역할을 부여해서 조인에 참여시켜야 한다.
 
-- 100번 부서에 근무하는 사원의 아이디, 사원의 이름, 그 사원의 상사 아이디, 그 사원의 상사 이름을 조회하기
SELECT A.EMPLOYEE_ID 직원_아이디, A.FIRST_NAME 직원_이름, B.EMPLOYEE_ID 상사_아이디, B.FIRST_NAME 상사_이름
FROM EMPLOYEES A, EMPLOYEES B
WHERE A.MANAGER_ID = B.EMPLOYEE_ID AND A.DEPARTMENT_ID = 100;
 
-- 90번 부서에 근무하는 매니저에게 보고하는 직원들의 아이디, 이름, 급여, 부서명을 조회하기
SELECT 직원.EMPLOYEE_ID, 직원.FIRST_NAME, 직원.SALARY, 부서.DEPARTMENT_NAME
FROM EMPLOYEES 매니저, EMPLOYEES 직원, DEPARTMENTS 부서
WHERE 부서.DEPARTMENT_ID = 직원.DEPARTMENT_ID
AND 매니저.EMPLOYEE_ID = 직원.MANAGER_ID
AND 매니저.DEPARTMENT_ID = 90;
 
-- 다중행 함수(그룹함수)
-- 그룹함수는 행 집합(행 그룹)에 작용해서 그룹 당 하나의 결과를 생성한다.
-- 행 집합은 테이블 전체 행일 수도 있고,
-- 테이블에서 특정 컬럼의 값이 동일한 것끼리 그룹화된 행일수도 있다.
-- 그룹함수
-- COUNT(*), COUNT(컬럼명) 행의 개수, *일 때는 모든 행의 개수, 컬럼명을 지정하면 NULL이 아닌 행의 개수
--SUM(컬럼명) 합계를 계산, NULL은 무시한다.
--AVG(컬럼명) 평균을 계산, NULL은 무시
-- MIN(컬럼명) 최솟값을 계산, NULL 무시
-- MAX(컬럼명) 최댓값을 계산, NULL 무시
-- VARIANCE(컬럼명) 분산을 계산, NULL 무시
-- STDDEV(컬럼명) 
 
-- COUNT(*)
-- 직원의 수를 조회하기
SELECT COUNT(*)
FROM EMPLOYEES;
 
-- 커미션을 받는 직원의 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
 
-- COUNT(컬럼명)은 해당 컬럼의 값이 NULL이 아닌 행의 개수만 센다.
SELECT COUNT(COMMISSION_PCT)
FROM EMPLOYEES;
 
-- 80번 부서에 소속된 사원들의 급여 총합을 조회하기
SELECT SUM(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
 
-- 커미션의 평균을 조회하기
SELECT AVG(COMMISSION_PCT)
FROM EMPLOYEES;
-- 또는
SELECT SUM(COMMISSION_PCT/35)
FROM EMPLOYEES;
-- NULL이 아닌 것만 갖고 계산이 된다.
 
-- 최저급여를 조회하기
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE SALARY = (SELECT MIN(SALARY)
                        FROM EMPLOYEES);
 
-- 커미션을 받는 직원들의 이름과 급여, 커미션 및 소속부서명을 조회하기
SELECT E.FIRST_NAME, E.SALARY, E.COMMISSION_PCT, D.DEPARTMENT_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.COMMISSION_PCT IS NOT NULL
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+);
 
-- 커미션을 받는 직원들의 이름과 급여, 커미션 및 소속부서명과 직종, 직종제목을 조회하기
SELECT E.FIRST_NAME
, E.SALARY
, E.COMMISSION_PCT
, D.DEPARTMENT_ID
, J.JOB_ID
, J.JOB_TITLE
FROM EMPLOYEES E
, DEPARTMENTS D
, JOBS J
WHERE E.COMMISSION_PCT IS NOT NULL
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
AND J.JOB_ID = E.JOB_ID;
 
-- 커미션을 받는 직원들의 이름과 급여, 커미션 및 그 직원의 매니저의 이름을 조회하기
SELECT 직원.FIRST_NAME
, 직원.SALARY
, 직원.COMMISSION_PCT
, 매니저.FIRST_NAME
FROM EMPLOYEES 직원, EMPLOYEES 매니저
WHERE 직원.COMMISSION_PCT IS NOT NULL
AND 직원.MANAGER_ID = 매니저.EMPLOYEE_ID;
 
-- 커미션을 받는 직원들의 이름과 급여, 커미션, 소속부서명 및 그 직원의 매니저의 이름과 매니저의 
-- 소속부서명을 조회하기
SELECT 직원.FIRST_NAME
, 직원.SALARY
, 직원.SALARY
, 직원_부서.DEPARTMENT_NAME
, 매니저.FIRST_NAME
, 매니저_부서.DEPARTMENT_NAME
FROM EMPLOYEES 직원, EMPLOYEES 매니저, DEPARTMENTS 직원_부서, DEPARTMENTS 매니저_부서
WHERE 직원.MANAGER_ID = 매니저.EMPLOYEE_ID
AND 매니저_부서.DEPARTMENT_ID = 매니저.DEPARTMENT_ID
AND 직원_부서.DEPARTMENT_ID = 직원.DEPARTMENT_ID
AND 직원.COMMISSION_PCT IS NOT NULL;
 
-- 급여등급이 A등급인 직원 수를 조회하기
SELECT COUNT(*)
FROM EMPLOYEES E, JOB_GRADES J
WHERE J.LOWEST_SALARY <= E.SALARY
AND J.HIGHEST_SALARY >= E.SALARY
AND J.GRA = 'A';
 
-- 급여등급이 E등급인 상사에게 보고하는 직원의 이름과 급여를 조회하기
SELECT 직원.FIRST_NAME, 직원.SALARY
FROM EMPLOYEES 직원, EMPLOYEES 상사, JOB_GRADES 상사_등급
WHERE 상사_등급.LOWEST_SALARY <= 상사.SALARY
AND 상사_등급.HIGHEST_SALARY >= 상사.SALARY
AND 직원.MANAGER_ID = 상사.EMPLOYEE_ID
AND 상사_등급.GRA = 'E';
 
-- 급여등급이 E등급인 상사에게 보고하는 직원의 이름, 급여, 소속부서명을 조회하기
SELECT 직원.FIRST_NAME, 직원.SALARY, 직원_부서.DEPARTMENT_NAME
FROM EMPLOYEES 직원, EMPLOYEES 상사, JOB_GRADES 상사_등급, DEPARTMENTS 직원_부서
WHERE 직원.MANAGER_ID = 상사.EMPLOYEE_ID
AND 상사_등급.LOWEST_SALARY <= 상사.SALARY
AND 상사_등급.HIGHEST_SALARY >= 상사.SALARY
AND 직원.DEPARTMENT_ID = 직원_부서.DEPARTMENT_ID
AND 상사_등급.GRA = 'E';
 
-- 급여등급이 E등급인 상사에게 보고하는 직원의 이름, 급여, 그 직원의 급여등급을 조회하기
SELECT 직원.FIRST_NAME, 직원.SALARY, 직원_등급.GRA
FROM EMPLOYEES 직원, EMPLOYEES 상사, JOB_GRADES 상사_등급, JOB_GRADES 직원_등급
WHERE 직원.MANAGER_ID = 상사.EMPLOYEE_ID
AND 상사_등급.LOWEST_SALARY <= 상사.SALARY
AND 상사_등급.HIGHEST_SALARY >= 상사.SALARY
AND 직원_등급.LOWEST_SALARY <= 직원.SALARY
AND 직원_등급.HIGHEST_SALARY >= 직원.SALARY
AND 상사_등급.GRA = 'E';
 
-- 급여등급에 따라서 인상된 급여를 조회하기
-- 직원이름, 급여, 급여등급, 인상된 급여
-- 인상률: A등급 1%, B등급:3%, C등급: 5%, D등급: 7%, E등급: 10%, F등급 15% 인상시킨다.
SELECT E.FIRST_NAME, E.SALARY, JG.GRA, 
CASE WHEN JG.GRA = 'A' THEN TRUNC(1.01*E.SALARY)
        WHEN JG.GRA = 'B' THEN TRUNC(1.03*E.SALARY)
        WHEN JG.GRA = 'C' THEN TRUNC(1.05*E.SALARY)
        WHEN JG.GRA = 'D' THEN TRUNC(1.07*E.SALARY)
        WHEN JG.GRA = 'E' THEN TRUNC(1.10*E.SALARY)
        WHEN JG.GRA = 'F' THEN TRUNC(1.15*E.SALARY)
        END AS 급여인상분
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY;
 
-- DECODE(JG.GRA, 'A' TRUNC(~)도 가능
 
-- 사원의 아이디, 사원이름, 급여, 급여등급, 직종아이디, 직종제목, 
-- 소속부서 아이디, 부서명, 소재지주소, 소재지의 국가를 조회하기(6개 조인)
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, JG.GRA, E.JOB_ID, J.JOB_TITLE, E.DEPARTMENT_ID, D.DEPARTMENT_NAME, L.CITY, C.COUNTRY_ID
FROM EMPLOYEES E, DEPARTMENTS D, JOB_GRADES JG, JOBS J, LOCATIONS L, COUNTRIES C
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
AND E.JOB_ID = J.JOB_ID
AND L.LOCATION_ID(+) = D.LOCATION_ID
AND L.COUNTRY_ID = C.COUNTRY_ID(+);
Colored by Color Scripter
cs

'Oracle Database SQL' 카테고리의 다른 글

190408 (0)	2019.06.07
190405 (0)	2019.06.07
190403 (0)	2019.06.07
190402 (0)	2019.06.07
090401 (0)	2019.06.07

190403

monkey-k777 2019. 6. 7. 16:56

2019. 6. 7. 16:56

-- 문자를 날짜로 변경하기
SELECT TO_DATE('2019/01/31', 'YYYY/MM/DD'),
          TO_DATE('2019-01-31', 'YYYY-MM-DD'),
          TO_DATE ('01/31/2019', 'MM/DD/YYYY')
FROM DUAL;
 
SELECT TRUNC(TO_DATE('2019/08/19', 'YYYY/MM/DD') - SYSDATE)
FROM DUAL;
 
-- 2008년 3월 1일 - 2008년 3월 15일 사이에 입사한 사원의 아이디, 이름, 입사일 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE >= TO_DATE('2008/03/01', 'YYYY/MM/DD')
AND HIRE_DATE <= TO_DATE('2008/03/15 23:59:59', 'YYYY/MM/DD HH24:MI:SS'); -- 23:59:59 안 쓰면 0:00:00시 까지라 3/15 아침 10시 입사자 데이터가 안 나옴
-- WHERE HIRE_DATE >= '2008/03/01'
-- AND HIRE_DATE < '2008/03/16';
 
-- 숫자 <-> 문자
-- 9: 소수점 이상 -> 숫자가 있으면 표현, 없으면 무시
--    소수점 이하 -> 숫자가 있으면 표현, 없으면 0을 표현
-- 0: 소수점 이상 -> 숫자가 있으면 표현, 없으면 0을 표현
--    소수점 이하 -> 숫자가 있으면 표현, 없으면 0을 표현
SELECT EMPLOYEE_ID, FIRST_NAME, TO_CHAR(SALARY, '99,999') -- 엑셀의 #과 같은 개념. 9 또는 0을 씀
        , TO_CHAR(COMMISSION_PCT, '9.99')
        , TO_CHAR(COMMISSION_PCT, '0.00')
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
 
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > '15000'; -- 숫자로 묵시적 변환해줌 WHERE SALARY > TO_NUMBER('15,000, 99,999');
 
-- 기타 함수
-- NVL(컬럼, 값)
-- 지정된 컬럼의 값이 만약 NULL이라면 지정된 값으로 변환해서 제공
-- 지정된 컬럼의 값이 NULL이 아니면 원래 값을 제공
SELECT NVL(30, 0), NVL(NULL,0) -- 널에 대한 값 제공
FORM DUAL;
 
-- 사원의 이름, 급여, 커미션을 조회하기, 커미션이 없는 사원들은 0으로 표시하기
SELECT FIRST_NAME, SALARY, NVL(COMMISSION_PCT, 0)
FROM EMPLOYEES;
 
-- 사원의 이름, 급여, 커미션, 총급여(보너스가 포함된 급여)를 조회하기
SELECT FIRST_NAME, SALARY, COMMISSION_PCT, SALARY * (1+NVL(COMMISSION_PCT, 0)) AS 총급여
FROM EMPLOYEES;
 
-- NVL2(컬럼, 값1, 값2) 값1, 2는 같은 타입이어야 한다.
--       지정된 컬럼의 값이 만약 NULL이라면 값2 대체해서 제공한다.
--       지정된 컬럼의 값이 NULL이 아니면 값1로 대체해서 제공한다.
 
-- 사원의 이름, 급여, 커미션을 받는지 여부를 "유", "무"로 조회하기
SELECT FIRST_NAME, SALARY, NVL2(COMMISSION_PCT, '유', '무') AS 유무
FROM EMPLOYEES;
 
-- CASE ~ WHEN ~ END문
-- IF ELSE IF ELSE문의 역할을 수행한다.
-- CASE
--         WHEN 비교식 THEN 값1
--         WHEN 비교식 THEN 값2
--         WHEN 비교식 THEN 값3
--         ELSE 값4
-- END      
      
-- 부서번호가 50번인 부서는 'A팀', 60, 70번인 부서는 'B팀', 80번인 부서는 'C팀'으로 조회하기
SELECT FIRST_NAME, DEPARTMENT_ID,
         CASE
            WHEN DEPARTMENT_ID = 50 THEN 'A팀'
            WHEN DEPARTMENT_ID IN (60, 70) THEN 'B팀'
            WHEN DEPARTMENT_ID = 80 THEN 'C팀'
            END AS TEAM
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (50, 60, 70, 80)
ORDER BY TEAM;
 
-- 사원들의 급여 수준을 상중하로 조회하기
-- 사원 이름, 급여, 급여 수준을 조회하고, 급여 수준 순으로 정렬하기
-- 급여 수준 상:12000달러 이상, 중: 5000달러 이상, 하:5000달러 미만 
SELECT FIRST_NAME, SALARY, 
          CASE WHEN SALARY >= 12000 THEN '상'
                 WHEN SALARY >= 5000 THEN '중'
                 ELSE '하'
          END AS 급여수준
FROM EMPLOYEES;
 
-- 사원 이름, 급여, 급여 인상분을 조회하고, 급여 인상분 순으로 정렬하기
-- 인상 비율:12000달러 이상 5%, 중:5000달러 이상 10%, 하:5000달러 미만 15%
SELECT FIRST_NAME, SALARY
        , CASE WHEN SALARY >= 12000 THEN 1.05*SALARY
                 WHEN SALARY >= 5000 THEN 1.1*SALARY
                 ELSE 1.15*SALARY
        END AS 급여인상분
FROM EMPLOYEES
ORDER BY 급여인상분 DESC;
 
-- DECODE 함수
-- IF ~ ELSE IF ~ ELSE를 간편하게 구현할 수 있는 함수
-- DECODE(컬럼 혹은 표현식, 비교값1, 결과값1
--                                  비교값2, 결과값2
--                                             결과값3) <-- ELSE와 같음
SELECT FIRST_NAME, DEPARTMENT_ID,
         DECODE(DEPARTMENT_ID, 50, 'A팀',
                                          60, 'B팀',
                                          70, 'B팀',
                                          80, 'C팀') AS TEAM
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (50, 60, 70, 80)
ORDER BY TEAM;
 
-- 테이블의 조인
-- 조회할 데이터가 2개 이상의 테이블을 사용해야지만 획득되는 경우
-- 조인 방법: FROM 절에 데이터 획득에 필요한 테이블을 나열하기만 하면 된다. 조인 조건을 제공해야 된다(N개의 테이블을 조인하면 N-1개의 조인 조건이 있어야 한다). 조인 조건은 조인된 행들 중에서 의미있게 연결된 행만 선택하게 한다.
 
-- 사원의 아이디, 사원의 이름, 부서 아이디, 부서 이름으로 조회하기
-- 단, 직종 최저 급여가 3000달러 미만인 정보만 조회하기
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.DEPARTMENT_ID, B.DEPARTMENT_NAME
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;
 
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.JOB_ID, A.SALARY, B.JOB_TITLE, B.MIN_SALARY, B.MAX_SALARY
FROM EMPLOYEES A, JOBS B  -- 조인
WHERE A.JOB_ID = B.JOB_ID AND B.MIN_SALARY < 3000;  -- 조인 조건
 
-- 사원의 아이디, 사원 이름, 부서 이름을 조회하기
-- 단, 100번 사원이 부서 담당자로 지정된 부서에 소속된 사원만 조회하기
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, B.DEPARTMENT_NAME
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID AND B.MANAGER_ID = 100;
 
-- 부서 아이디, 부서명, 그 부서가 위치한 지역의 도시명을 조회하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D, LOCATIONS L
WHERE D.LOCATION_ID = L.LOCATION_ID;
 
-- 부서 아이디, 부서명, 부서 담당자 아이디, 부서 담당자(사원)의 이름, 부서 담당자(사원)의 연락처를 조회하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANAGER_ID, E.FIRST_NAME, E.PHONE_NUMBER
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.MANAGER_ID = E.EMPLOYEE_ID;
 
-- 급여를 15000달러 이상 받는 사원들의 아이디, 사원 이름, 급여, 소속 부서 아이디, 소속 부서명을 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND E.SALARY >= 15000;
 
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE SALARY >= 15000;
 
-- 직종 최저 급여가 10000달러 이상인 직종에 근무 중인 사원의 아이디, 사원의 이름, 직종 아이디, 직종 제목 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, J.JOB_TITLE
FROM JOBS J, EMPLOYEES E
WHERE J.JOB_ID = E.JOB_ID AND J.MIN_SALARY >= 10000;
 
-- 사원의 아이디, 사원 이름, 직종 아이디, 직종 제목, 소속 부서 아이디, 소속 부서명 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, J.JOB_TITLE, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, JOBS J, DEPARTMENTS D
WHERE E.JOB_ID=J.JOB_ID AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;
 
-- 등가, 비등가, 셀프, 아우터 조인
 
-- 모든 사원의 이름, 부서 번호, 부서 이름을 조회하기
SELECT EMPLOYEE_ID, E.DEPARTMENT_ID, D.DEPARTMENT_NAME 
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
 
-- 소속 부서가 80번인 사원의 아이디, 직종 아이디, 직종 제목, 최소 급여, 최대 급여를 조회하기
SELECT E.EMPLOYEE_ID, E.JOB_ID, J.JOB_TITLE, J.MIN_SALARY, J.MAX_SALARY
FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID = J.JOB_ID AND E.DEPARTMENT_ID = 80;
 
-- 커미션을 받는 사원의 이름, 부서 이름, 도시명을 조회하기
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, L.CITY
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID AND E.COMMISSION_PCT IS NOT NULL;
 
-- Toronto에서 근무하는 사원의 이름, 부서명을 조회하기
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID AND L.CITY LIKE 'Toronto';
 
-- 145번 사원이 담당자로 지정된 부서에서 근무하는 
-- 사원의 이름, 부서명, 직종 제목, 최소급여, 최대급여를 조회하기
SELECT E.FIRST_NAME, DEPARTMENT_NAME, J.JOB_TITLE, J.MIN_SALARY, J.MAX_SALARY
FROM EMPLOYEES E, DEPARTMENTS D, JOBS J
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND J.JOB_ID = E.JOB_ID AND D.MANAGER_ID = 145;
 
-- 101번 사원의 이름, 근무했던 직종 아이디, 근무했던 부서 아이디, 근무했던 부서명을 조회하기
SELECT E.FIRST_NAME, J.JOB_ID, J.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, JOB_HISTORY J
WHERE J.DEPARTMENT_ID = D.DEPARTMENT_ID AND E.EMPLOYEE_ID = J.EMPLOYEE_ID AND E.EMPLOYEE_ID = 101;
 
-- 부서들 중에서 소재지가 Canada인 부서의 아이디, 부서명, 도시명을 조회하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D, LOCATIONS L, COUNTRIES C
WHERE D.LOCATION_ID = L.LOCATION_ID AND L.COUNTRY_ID = C.COUNTRY_ID AND C.COUNTRY_NAME LIKE 'Canada';
 
-- 2007년에 'SA_MAN'으로 근무했던 사원의 아이디, 이름, 현재 근무 중인 직종 아이디, 급여를 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.SALARY
FROM EMPLOYEES E, JOB_HISTORY J
WHERE E.EMPLOYEE_ID = J.EMPLOYEE_ID 
AND TO_CHAR(J.START_DATE, 'YYYY') <= 2007 
AND TO_CHAR(J.END_DATE, 'YYYY') >= 2007
AND J.JOB_ID = 'SA_MAN';
 
-- 110번 부서에서 근무했던 경력이 있는 사원의 아이디, 이름, 부서명을 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, JOB_HISTORY J
WHERE J.DEPARTMENT_ID = D.DEPARTMENT_ID AND E.EMPLOYEE_ID = J.EMPLOYEE_ID AND J.DEPARTMENT_ID = 110;
Colored by Color Scripter
cs

'Oracle Database SQL' 카테고리의 다른 글

190408 (0)	2019.06.07
190405 (0)	2019.06.07
190404 (0)	2019.06.07
190402 (0)	2019.06.07
090401 (0)	2019.06.07

190402

monkey-k777 2019. 6. 7. 16:55

2019. 6. 7. 16:55

-- 오라클의 논리 연산자
-- AND
-- OR
-- NOT
 
-- 80번 부서에 소속된 사원 중에서 급여를 5000달러 이상 받는 사원의 이름과 급여, 직종, 부서 아이디를 조회하기
SELECT FIRST_NAME, SALARY, JOB_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
AND SALARY >= 5000;
 
-- 90번 부서에 소속된 사원 중에서 관리자 아이디가 100인 사원의 이름, 급여, 직종을 조회하기
SELECT FIRST_NAME, SALARY, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
AND MANAGER_ID = 100;
 
-- 커미션을 받는 사원 중에서 소속 부서가 지정되지 않는 사원의 이름, 급여, 커미션을 조회하기
SELECT FIRST_NAME, SALARY, COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL 
AND DEPARTMENT_ID IS NULL;
 
-- 커미션이 0.3 이상이고, 직종이 SA_MAN이며, 이름이 'A'로 시작하는 사원의 이름, 커미션, 직종을 조회
SELECT FIRST_NAME, COMMISSION_PCT, JOB_ID
FROM EMPLOYEES
WHERE COMMISSION_PCT >= 0.3
AND JOB_ID = 'SA_MAN'
AND FIRST_NAME LIKE 'A%';
 
-- 소속 부서가 80번 부서이고 급여를 5000달러 초과 10000 미만 받는 사원의 이름, 급여 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
AND SALARY BETWEEN 5000 AND 10000;
 
-- 소속 부서가 60이거나 90인 사원의 이름, 소속 부서를 조회하기
SELECT FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (60, 90);
 
-- 급여를 3000달러 이하로 받는 사원과 급여를 15000달러 이상으로 받는 사원의 이름, 급여 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
where SALARY <=3000
OR SALARY >= 15000;
 
-- 소속 부서가 50번 부서에 소속된 사원 중에서
-- 급여를 3000달러 이하로 받거나 급여를 15000달러 이상으로 받는 사원의 이름, 급여, 부서 아이디를 조회
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50 
AND (SALARY <= 3000 OR SALARY >= 15000);
 
-- 소속 부서가 50번이거나 80번인 사원의 이름, 소속 부서를 조회하기
SELECT FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (50, 80);
 
-- 소속 부서가 50번, 80번 외의 부서에서 근무하는 사원의 이름, 소속 부서를 조회하기
SELECT FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID NOT IN (50, 80);
 
-- 조회된 데이터의 정렬
-- ORDER BY 절을 사용하면 조회된 데이터를 정렬할 수 있다.
-- ORDER BY 절은 SELECT문의 가장 끝에 위치한다.
-- SELECT
-- FROM 
-- [WHERE]
-- [ORDER BY 기준 컬럼명 [ASC <--기본값 | DESC]];
 
-- 사원 테이블에서 아이디, 이름, 급여를 조회하고, 급여를 기준으로 내림차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY 
FROM EMPLOYEES
ORDER BY SALARY DESC;
 
-- 80번 부서에 소속된 사원들의 이름, 입사일을 조회하고, 입사일을 기준으로 오름차순 정렬하기
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
ORDER BY HIRE_DATE ASC;
 
-- 오름차순: 날짜는 옛날 날짜부터, 문자는 알파벳, 가나다 순으로 표시, NULL값은 맨 마지막에 표시
 
-- 80번 부서에 소속된 사원 중에서 이름에 'e'를 포함하고 있고, 급여를 5000달러 미만으로 받는 사원들의
-- 아이디, 이름, 급여를 조회하고, 급여 순으로 내림차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80 AND (FIRST_NAME LIKE '%e%' OR FIRST_NAME LIKE '%E%') AND SALARY < 10000
ORDER BY SALARY DESC;
 
-- 커미션을 받는 사원의 아이디, 이름, 급여, 커미션, 보너스(급여*커미션)을 조회하고
-- 보너스 순으로 오름차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT, SALARY*COMMISSION_PCT AS BONUS
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY 5 ASC;
 
-- 내부 연산 순서: FROM WHERE SELECT ORDER BY 순으로 하므로, WHERE은 별칭 불가, ORDER BY는 가능.
-- 두 가지 이상의 정렬 기준 적용 가능
 
-- 80번 부서 사원의 급여, 이름을 조회하기
-- 급여와 이름 순으로 오름차순 정렬하기(급여가 동일할 때는 이름 순으로 정렬하기)
SELECT SALARY, FIRST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
ORDER BY SALARY, FIRST_NAME;
 
-- SELECT
-- FROM
-- WHERE
-- GROUP BY ~
-- HAVING ~
-- ORDER BY ~ (순서 꼭 지켜야!)
 
-- 오라클의 내장함수: 값을 입력받아서 적절한 작업을 수행하고 결과값을 반환하는 것.
--                 ㄴ구분: 단일행 함수: 단일 행에 대해서만 연산을 수행하고, 행 당 하나의 결과를 반환한다. 단일행 함수는 여러 번 중첩해서 사용할 수 있다. SELECT, WHERE, ORDER BY 절에서 사용할 수 있다.
--                                      ㄴ종류: 문자/숫자/날짜/변환 함수 - 날짜 <->문자, 숫자<->문자로 변환/기타 함수.
--                           다중행 함수(그룹 함수): 여러 행(행 그룹)을 조작해서 행 그룹 당 하나의 결과를 반환한다. 한 번만 중첩 가능. 통계치(합계, 평균, 분산, 표준편차, 최댓값, 최솟값, 개수)를 계산한다.
 
-- 문자 함수
-- 대소문자 변환 함수
-- UPPER(컬럼), LOWER(컬럼)
SELECT FIRST_NAME, UPPER(FIRST_NAME), LOWER(LAST_NAME)
FROM EMPLOYEES;
 
-- 문자열 추출
-- SUBSTR(컬럼, m [, n]): m번 째부터 n번 째까지 문자를 반환한다.
SELECT FIRST_NAME, SUBSTR(FIRST_NAME, 2)
FROM EMPLOYEES;
 
-- 지정된 문자열의 등장 위치 찾기
-- INSTR(컬럼, '문자'): 지정된 문자의 위치를 반환
SELECT INSTR('801010-1121211', '-')
FROM DUAL; -- 오라클의 빌트인 테이블, 1행 1열짜리 테이블. 특별한 테이블을 대상으로 수행하는 조회 작업이 아닐 때 사용된다.
 
-- 문자 채우기
-- LPAD(컬럼, 길이, '문자'), RPAD(컬럼, 길이, '문자')
-- 컬럼의 값이 지정된 길이보다 짧으면 부족한 길이만큼 문자를 채운다.
SELECT LPAD('ABC', 10, '*'), RPAD('ABC', 10, '*')
FROM DUAL;
 
-- 문자의 길이
-- LENGTH(컬럼)
SELECT FIRST_NAME, LENGTH(FIRST_NAME)
FROM EMPLOYEES;
 
-- 이름 4글자 이하인 사원의 이름과 글자수를 조회하기
SELECT FIRST_NAME, LENGTH(FIRST_NAME)
FROM EMPLOYEES
WHERE LENGTH(FIRST_NAME) <= 4;
 
-- 이름에 'C'나 'c'가 들어 있는 사원의 이름을 조회하기
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE LOWER(FIRST_NAME) LIKE '%c%';
 
-- 불필요한 공백 없애기
-- TRIM(컬럼): 불필요한 좌우 공백을 제거한다.
SELECT '            C B C              ', TRIM('             ABC        ') AS X
FROM DUAL;
 
-- 문자 바꾸기
-- REPLACE(문자, '찾을 문자', '변경할 문자')
SELECT REPLACE('자바 입문자를 위한 자바의 정석', '자바', '파이썬')
FROM DUAL;
 
-- 숫자 함수
-- 반올림한다
-- ROUND(숫자, 소수 자릿수) : 지정된 자리로 값을 반올림한다.
-- 버린다.
-- TRUNC(숫자, 소수 자릿수): 지정된 자리까지 남기고 값을 버린다.
-- 나머지를 반환한다.
-- MOD(숫자1, 숫자2): 숫자1을 숫자2로 나눈 나머지를 반환한다.
 
SELECT ROUND(3,2), ROUND(3,6)
FROM DUAL;
 
SELECT ROUND(3.1523, 1), ROUND(3.1523, 2)
FROM DUAL;
 
SELECT ROUND(10/3, 2) FROM DUAL;
 
SELECT ROUND(1234, 0), ROUND(1234, -1), ROUND(1234, -2), ROUND(1234,-3) FROM DUAL;
 
SELECT TRUNC(123.58, 1), ROUND(123.58, 1) FROM DUAL;
 
SELECT FIRST_NAME, SALARY, TRUNC(SALARY, -3)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
 
-- 급여 금액별 사원 수 계산하기
SELECT TRUNC(SALARY, -3), COUNT(*)
FROM EMPLOYEES
GROUP BY TRUNC(SALARY, -3)
ORDER BY 1;
 
-- 날짜 관련 함수
-- SYSDATE: 현재 날짜와 시간 정보를 반환한다. 반환값은 DATE타입의 값이다(중요!)
SELECT SYSDATE
FROM DUAL;
 
-- MONTHS_BETWEEN(날짜, 날짜): 두 날짜 사이의 개월 수 반환
-- ADD_MONTHS(날짜, 개월 수): 날짜에 개월 수를 더한 날짜를 반환
 
-- 사원 아이디, 이름, 입사일, 근무 개월 수 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS MONTHS
FROM EMPLOYEES;
 
-- 200개월 이상 근무한 사원의 사원 아이디, 이름, 입사일, 근무 개월 수 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS MONTHS
FROM EMPLOYEES
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) >= 200;
 
SELECT ADD_MONTHS(SYSDATE, 6)
FROM DUAL;
 
-- 날짜 연산
-- 날짜 + 일수: 지정된 날짜에서 더해진 숫자만큼 이후의 날짜를 반환한다.
-- 날짜 - 숫자: 지정된 날짜에서 빼진 숫자만큼 이전의 날짜를 반환한다.
-- 날짜 - 날짜: 두 날짜 사이의 일수를 반환한다.
-- 날짜 + 숫자/24: 지정된 날짜에 시간이 더해진 날짜를 반환한다.
-- !날짜 + 날짜는 없음
 
-- 최근 3일 전에 입사한 사원 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE > (SYSDATE -3);
 
-- 3일 후, 3일 전
SELECT SYSDATE + 3, SYSDATE -3
FROM DUAL;
 
-- 사원들의 이름, 입사일, 근무일수 구하기
SELECT FIRST_NAME, HIRE_DATE, TRUNC(SYSDATE - HIRE_DATE) DAYS
FROM EMPLOYEES;
 
SELECT SYSDATE, SYSDATE + 2/24
FROM DUAL;
 
-- 변환함수
SELECT TO_CHAR(SYSDATE, 'YYYY') 년, TO_CHAR(SYSDATE, 'MM') 월, TO_CHAR(SYSDATE, 'DD') 일,
          TO_CHAR(SYSDATE, 'HH24') 시, TO_CHAR(SYSDATE, 'MI') 분, TO_CHAR(SYSDATE, 'SS') 초,
          TO_CHAR(SYSDATE, 'AM') 오전오후, TO_CHAR(SYSDATE, 'DAY') 요일
FROM DUAL;
 
-- 입사연도 조회하기
SELECT FIRST_NAME, HIRE_DATE, TO_CHAR(HIRE_DATE, 'YYYY') 입사연도
FROM EMPLOYEES;
 
-- 입사일이 오늘과 동일한 날짜에 입사한 사람의 이름, 입사일 조회하기
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE (TO_CHAR(HIRE_DATE, 'MM-DD') = TO_CHAR(SYSDATE, 'MM-DD'));
 
-- 2008년에 입사한 사원의 아이디, 이름, 입사일 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2008';
 
-- 80번 부서에 소속된 사원들의 아이디, 이름, 입사일, 근무 개월 수, 근무 연수를 조회하기(소숫점 한 자리까지 표시하고 버린다).
SELECT EMPLOYEE_ID
        , FIRST_NAME
        , HIRE_DATE
        , TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS 근무개월수
        , TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) AS 근무연수
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
 
-- 급여가 12000달러를 초과하는 사원의 아이디, 이름, 급여를 조회하고, 이름순으로 오름차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 12000
ORDER BY FIRST_NAME ASC;
 
-- 100번 사원이 관리자로 지정된 사원 중에서 급여를 15000달러 이상 지급받는 사원의 아이디, 이름, 
-- 소속 부서 아이디, 급여를 조회하고, 급여순으로 내림차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE MANAGER_ID = 100 AND SALARY >= 15000
ORDER BY SALARY DESC;
 
-- 20번 부서와 50번 부서에 소속된 사원 중에서 급여를 5000달러 이상 지급받는 사원의 아이디, 이름,
-- 소속 부서 아이디, 급여를 조회하고, 이름순으로 오름차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (20, 50) AND SALARY >= 5000
ORDER BY FIRST_NAME ASC;
 
-- 급여가 5000 ~ 12000 사이이고, 부서번호가 20번 또는 50인 사원의 이름과 급여를 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 5000 AND SALARY < 12000 AND DEPARTMENT_ID IN (20, 50); -- BETWEEN은 포함됨(이상 이하 개념)
 
-- 2008년 상반기에 입사한 사원의 아이디, 이름, 입사일을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYYMM') BETWEEN 200801 AND 200806;
 
-- 커미션을 받는 사원들의 사원 아이디, 이름, 급여, 커미션, 보너스를 조회하기
-- 보너스는 소숫점 한짜리까지 반올림해서 표현하고, 보너스를 기준으로 내림차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT AS 커미션, ROUND(COMMISSION_PCT*SALARY, 1) AS 보너스
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY 보너스 DESC;
 
-- 80번부서에 근무하는 사원의 아이디, 이름, 급여, 15%인상된 급여를 조회하기
-- 인상된 급여는 소숫점 이하는 버린다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, TRUNC(SALARY*1.15, 0) AS "인상 급여"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
 
-- 1월달, 2월달, 3월달에 입사한 사원의 아이디, 이름, 입사일을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'MM') IN (1, 2, 3);
 
-- 이름이 'A', 'M', 'J'로 시작하는 사원의 이름을 알파벳순으로 정렬해서 조회하기(문자함수 사용)
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE UPPER(FIRST_NAME) LIKE 'A%' 
OR UPPER(FIRST_NAME) LIKE 'M%'
OR UPPER(FIRST_NAME) LIKE 'J%'
ORDER BY FIRST_NAME ASC;
 
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE SUBSTR(FIRST_NAME, 1, 1) IN ('A', 'M', 'J');
 
-- 사원의 이름과 급여를 '*'로 나타내는 SQL을 작성하시오, '*'하나는 1000달러를 나타낸다.
-- 결과 
-- King 24000 ********************
-- Abel 6000  ******
-- 응수 45000 **********************************************************
SELECT FIRST_NAME, SALARY, RPAD('*', SALARY/1000 ,'*') AS 별
FROM EMPLOYEES;
 
SELECT FIRST_NAME, SALARY, RPAD(SALARY/1000+LENGTH(SALARY) , LENGTH(SALARY/1000+LENGTH(SALARY)) ,'*')
FROM EMPLOYEES;
 
-- 이름에 'A'나'a'와 'C'나'c'를 모두 포함하고 있는 사원의 이름을 알파벳순으로 정렬해서 조회하기 
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE INSTR(LOWER(FIRST_NAME), 'a') > 0
AND INSTR(LOWER(FIRST_NAME), 'C') > 0;
 
--WHERE UPPER(FIRST_NAME) LIKE '%A%' 
-- OR UPPER(FIRST_NAME) LIKE '%C%'
-- ORDER BY FIRST_NAME ASC;
 
-- !문자도 크기 비교 가능
 
Colored by Color Scripter
cs

'Oracle Database SQL' 카테고리의 다른 글

190408 (0)	2019.06.07
190405 (0)	2019.06.07
190404 (0)	2019.06.07
190403 (0)	2019.06.07
090401 (0)	2019.06.07

090401

monkey-k777 2019. 6. 7. 16:53

2019. 6. 7. 16:53

-- 테이블의 모든 데이터 조회
SELECT *
FROM 테이블명;
 
-- 모든 부서 정보 조회하기
SELECT *
FROM DEPARTMENTS;
 
-- 모든 국가 정보 조회하기
SELECT *
FROM COUNTRIES;
 
-- 모든 사원 정보 조회하기
-- 오라클의 키워드와 테이블 이름, 컬럼 이름은 대소문자를 구분하지 않는다.
SELECT *
FROM EMPLOYEES;
 
-- 모든 직종 정보 조회하기
SELECT *
FROM JOBS;
 
-- 모든 직종 변경 이력 정보 조회하기
SELECT *
FROM JOB_HISTORY;
 
-- 테이블의 특정 열(컬럼)의 데이터 조회하기
SELECT 컬럼명, 컬럼명, 컬럼명
FROM 테이블명;
 
-- 부서 정보 중에서 부서 번호와 부서 이름을 조회하기
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS;
 
-- 사원 정보 중에서 사원 아이디, 이름, 급여를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES;
 
-- 사원 정보 중에서 부서 아이디, 사원 아이디, 직종 아이디, 이름, 급여를 조회하기
SELECT DEPARTMENT_ID, EMPLOYEE_ID, JOB_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES;
 
-- SELECT절에서는 산술 연산자(+, -, *, /)의 사용이 가능하다.
-- 숫자 데이터 및 날짜 데이터에 대해서 사용 가능하다.
-- SELECT 컬럼명+컬럼명, 컬럼명 - 숫자, 컬럼명 * 숫자, 컬럼명/숫자, 컬럼명/컬럼명
-- from 테이블명
 
-- 사원 아이디, 이름, 급여, 연봉을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, SALARY*12
FROM EMPLOYEES;
 
-- 사원 아이디, 이름, 급여(주급), 시급(원으로 환산한 값:1달러 1130원)
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, (SALARY/40)*1130 SALARY_PER_HOUR
FROM EMPLOYEES;
 
-- 별칭 부여하기
-- SELECT 컬럼명 AS 별칭, 컬럼명 AS 별칭, 산술식 AS 별칭
-- FROM 테이블명
 
-- SELECT 컬럼명 별칭, 컬럼명 별칭, 산술식 "별칭" <-- 띄어쓰기 하고 싶을 때
-- FROM 테이블명
 
 
-- 오라클의 예약어는 컬럼명으로 쓸 수 없음.
SELECT EMPLOYEE_ID AS ID, FIRST_NAME AS NAME, HIRE_DATE AS "DATE"
FROM EMPLOYEES;
 
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY || '달러'
FROM EMPLOYEES;
 
-- 문자 이어 붙이기 연산자 ||
-- 문자(열) ''
-- 별칭 ""
 
-- 연결 연산자
-- 연결 연산자(||)는 컬럼과 컬럼, 산술식과 컬럼, 산술식과 산술식을 연결해서 문자를 생성한다.
 
 
 
-- 사원 아이디, 이름, 직종을 조회하기
-- (단, FIRST_NAME과 LAST_NAME을 연결해서 조회하고, 별칭을 FULL_NAME으로 붙이기)
SELECT EMPLOYEE_ID, FIRST_NAME || LAST_NAME AS FULL_NAME, JOB_ID
FROM EMPLOYEES;
 
SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME, JOB_ID
FROM EMPLOYEES;
 
-- 사원들이 현재 근무하고 있는 직종을 조회하기(중복 제거)
SELECT DISTINCT EMPLOYEE_ID, JOB_ID -- 이 경우 둘 다 같은 게 있어야 중복 제거 됨.
FROM EMPLOYEES;
 
-- 선택되는 행을 제한하기
-- WHERE 절을 사용하면 조회되는(선택되는) 행을 제한할 수 있다(조건절).
-- WHERE 절은 FROM 절 다음에 온다.
-- (조건식 --> WHERE 컬럼 이름 비교 조건 값, WHERE 컬럼이름 비교조건 다른 컬럼)
-- 오라클의 비교 조건 
-- 같다 =
-- 같지 않다. != <>
 
 
-- 사원 정보에서 60번 부서에서 소속된 사원의 아이디, 이름, 직종을 조회하기
SELECT employee_id, first_name, job_id, department_id
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;
 
-- 사원 정보에서 급여를 15000달러 이상 받는 사원의 아이디, 이름, 직종, 급여를 조회하기
 
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE SALARY >= 15000;
 
-- 사원 정보에서 사원 아이디가 200번인 사원의 아이디, 이름, 전화번호, 이메일, 소속부서 아이디 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, PHONE_NUMBER, EMAIL, DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 200;
 
-- ! 국가정보에서 아시아 지역에 위치하고 있는 나라의 국가코드, 국가명 조회하기
SELECT COUNTRY_ID, COUNTRY_NAME
FROM COUNTRIES
WHERE REGION_ID = 3;
 
-- WHERE 절에서 사용 가능한 기타 비교조건
-- WHERE 컬럼명 BETWEEN 값1 AND 값2
--      지정된 컬럼이 값1 이상 값2 이하인 행을 선택한다. 문자도?
-- WHERE 컬럼명 IN (값1, 값2, 값3)
--      지정된 컬럼의 값이 값1, 값2, 값3 중의 하나와 일치하는 행을 선택한다.
-- WHERE 컬럼명 LIKE '문자 패턴'
--      지정된 컬럼의 값이 문자 패턴과 유사한 행을 선택한다.
-- WHERE 컬럼명 IS (NOT) NULL
--      지정된 컬럼의 값이 NULL인(NULL이 아닌) 행을 선택한다.
--      컬럼의 값이 NULL인 행을 선택할 때 WHERE 컬럼명 = NULL과 같이 사용하지 않는다.
 
-- 급여가 10000 ~ 15000 달러를 받는 사원의 아이디, 이름, 직종, 급여를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE SALARY BETWEEN 10000 AND 15000;
 
-- 사원 아이디가 150 ~ 170에 속하는 사원의 아이디, 이름 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID BETWEEN 150 AND 170;
 
-- 소속 부서 아이디가 30, 60, 90인 사원의 아이디, 이름, 직종, 부서 아이디를 조회하시오.
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (30, 60, 90);
 
-- 직종 아이디가 'ST_MAN'이거나 'ST_CLERK'인 직종의 아이디, 제목, 최소 급여, 최대 급여 조회하기
SELECT JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY
FROM JOBS
WHERE JOB_ID IN ('ST_MAN', 'ST_CLERK');
 
-- 오라클의 문자 패턴 형식 ( % 0 or 무한대로 이미지화 )
-- WHERE job_id LIKE 'ST%'     <-- JOB_ID가 'ST'로 시작하는 것 검색
-- WHERE job_id LIKE '%ST%'     <-- JOB_ID가 'ST'로 끝나는 것 검색
-- WHERE job_id LIKE '%ST%'     <-- JOB_ID가 'ST'를 포함하는 것 검색
 
-- WHERE NAME LIKE "이_" <-- 이름이 이*인 사람 검색(외자).
 
-- 이름에 'e'를 포함하고 있는 사원의 아이디, 이름을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE '%e%';
 
-- 이름이 'A'로 시작하는 사원의 아이디, 이름을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'A%';
 
-- 이름이 'L'로 시작하고 이름이 4글자인 사원의 아이디, 이름을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'L___';
 
-- 부서 정보에서 담당자가 지정되어 있지 않은 부서의 아이디, 부서 이름 조회하기
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE MANAGER_ID IS NULL;
 
-- 사원 정보에서 커미션을 받는 사원의 아이디, 이름, 급여, 커미션을 조회하기
SELECT  EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
 
-- 사원 정보에서 소속 부서가 지정되어 있지 않는 사원의 아이디, 이름, 직종을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NULL;
 
-- 1. 모든 사원 정보 조회하기
SELECT *
FROM EMPLOYEES;
 
-- 2. 모든 부서 정보를 조회하기
SELECT *
FROM DEPARTMENTS;
 
-- 3. 사원 정보에서 사원 아이디가 100번인 사원의 아이디, 이름, 부서 아이디, 급여를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;
 
-- 4. 사원 정보에서 소속 부서가 60번인 사원의 아이디, 이름, 부서 아이디, 직종을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;
 
-- 5. 사원 정보에서 60번 부서 혹은 80번 부서에 소속된 사원의 아이디, 이름, 부서 아이디, 직종을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (60, 80);
 
-- 6. 직장 정보에서 최소 급여가 5000달러 이상 10000 이하인 직종의 아이디, 제목, 최소 급여, 최고 급여 조회하기
SELECT JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY
FROM JOBS
WHERE MIN_SALARY BETWEEN 5000 AND 10000;
 
-- 7. 사원 테이블에서 커미션을 받는 사원의 아이디, 이름, 급여, 연봉, 시급을 조회하기
SELECT  EMPLOYEE_ID, FIRST_NAME, SALARY AS 급여, SALARY*12 AS 연봉, SALARY/208 AS 시급
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
 
-- 8. 사원 테이블에서 이름이 'Kevin'인 사원의 아이디, 이름, 이메일, 전화번호를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, EMAIL, PHONE_NUMBER
FROM EMPLOYEES
WHERE FIRST_NAME = 'Kevin';
 
-- 9. 사원테이블에서 급여가 12000달러를 넘는 사원의 아이디, 이름, 급여를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 12000;
 
-- 10. 사원테이블에서 연봉이 100000달러를 초과하는 사원의 아이디, 이름, 급여, 연봉을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY*12 > 100000;
 
-- 11. 사원테이블에서 관리자가 없는 사원의 이름과 직종을 조회하기
SELECT FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE MANAGER_ID;
 
-- 12. 사원테이블에서 커미션을 받는 사원의 아이디, 이름, 급여, 커미션, 커미션 반영 급여를 조회하기 (커미션 반영 급여 = 급여 + 급여*커미션)
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT, (1+COMMISSION_PCT)*SALARY
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
 
-- 13. 이름의 3번째 문자가 'a'인 사원의 이름을 모두 조회하기
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE '__a%';
 
Colored by Color Scripter
cs

'Oracle Database SQL' 카테고리의 다른 글

190408 (0)	2019.06.07
190405 (0)	2019.06.07
190404 (0)	2019.06.07
190403 (0)	2019.06.07
190402 (0)	2019.06.07

PREV 이전 1 NEXT 다음

N의 창고