[ORACLE]_23.02.21

‍전희주·2023년 2월 21일
oracle
0

🧡 시작

  • 윤정원 강사님 jhjhyk@naver.com
  • SQL.REF, SQL_실습 PDF 강의 중 활용 예정.
  • 자습 시간 (2시간) -> 종이 교안 연습문제 풀이 진행.
  • 11장 내용까지는 SELECT 문 활용 내용임.
  • 15장은 SQL 튜닝 관점에서 아키텍처 이해하기
  • 프로젝트 과제: 제시한 질의문을 쿼리문으로 바꾸기

🧡 1장

SQL 기본 명령어 학습

  • SQL: 관계형 데이터베이스에서 데이터의 액세스 및 관리에 사용하는 LANGUAGE
  • 관계형 데이터베이스의 기본 개념 및 용어 설명
  • SQL 및 개발 환경 소개

다양한 미디어의 데이터 저장 영역

    1. DATA: 업무상 필요한 정보 CF. INFORMATION
    • 데이터(Data, 자료)는 현실 세계에서 측정하고 수집한 사실이나 값이고, 정보(Information)는 어떠한 목적이나 의도에 맞게 데이터를 가공 처리한 것
    1. DATABASE: 업무상 필요한 정보를 저장한 것, 계층형, 네트워크형, 관계형, 객체 관계형
    1. DBMS: 요청에 따라 DATABASE 내의 DATA를 저장, 검색, 수정, 관리해주는 프로그램
    • EX) ORACLE, MYSQL, SQL SERVER

관계형 데이터베이스 모델

  • 관계형 데이터베이스의 정의:
    관계형 데이터베이스는 관계 또는 2차원 테이블의 모음

  • 오라클 데이터베이스는 데이터 보관 및 관리를 위한 여러 기능과 저장공간을 객체(OBJECT)를 통해 제공한다.

  • 관계형 데이터베이스 용어

  • SQL을 사용하여 데이터베이스 QUERY

    • 관계형 데이터베이스 작동을 위한 ANSI 표준 언어
    • 효율적이고 쉽게 배워서 사용할 수 있음
    • 완벽한 기능(SQL을 사용하여 데이터를 정의, 검색, 조작할 수 있음)
    • 조인(JOIN)외 대부분 SQL QUERY는 STANDARD SYNTAX (모든 RDBMS에서 통용)

  • 과정에 사용되는 SQL 문

  • CDB Architecture

    • 오라클 버전 발전
      10g
      11g
      12c
      18cEE
      18cXE
      : ▶ 현재 우리는 18c 버전 이용

  • connection

    • Root -> xepdb1으로 이동

      🔽 sql developer 툴 활용 connection

    • 테이블 생성 위치: xepdb1

🔽 conn ora1/oracle_4U@localhost:1521/xepdb1
이렇게 해서 db연결!

기본 SELECT 문

  • 모든 열 선택 
SELECT * 
FROM departments;
  • 특정 열 선택 
SELECT department_id, location_id
FROM departments;

  • SQL 문 작성

    • SQL 문은 대소문자를 구분하지 않음
    • SQL 문은 한 줄 또는 여러 줄에 입력할 수 있음
    • 키워드는 약어로 표기하거나 여러 줄에 걸쳐 입력할 수 없음
    • 절은 보통 별도의 줄로 나누어 입력
    • 가독성을 높이기 위해 들여쓰기 사용

  • 기본 열 머리글(heading)

    • sql developer:
      • 왼쪽 정렬
      • 대문자 표시
    • SQL*plus:
      • 문자, 날짜 열 머리글은 왼쪽 정렬
      • 숫자 열 머리글은 오른쪽 정렬
      • 대문자

  • 산술 표현식

    • 연산자 우선 순위

  • 세션(session): 로그인해서 로그아웃 하기까지의 기간

  • 날짜 형식 변경하기 (DD-MON-RR)

    • 도구 - 환경설정 - 데이터베이스(+) 클릭 -NLS (언어 환경) 선택 - AMERICAN 선택 - DD-MON-RR 입력

    🔽

  • ✨ NULL 값 정의(의미)

    • NULL은 사용할 수 없거나, 할당되지 않았거나, 알 수 없거나, 적용할 수 없는 값임.
    • NULL은 0이나 공백과는 다름.
    • NULL값이 있을 때 산술식 사용하면 NULL 반환

  • sql developer 툴 활용해 null 표시 변경 방법
    🔽 cmd

    🔽 sql developer

  • cmd 창에서 sql plus 활용해 쿼리문, 내장 기능 출력
    🔽 list 의 l, show all

  • 열 alias 정의

    • 열 머리글의 이름을 변경
    • 계산 결과를 표시할 때 유용
    • 공백이 있을 때 큰 따옴표 "" 로 묶기

  • 연결 연산자

    • 열이나 문자열을 다른 열에 연결
    • 두 개의 세로선(||)으로 표시
    • 연산 결과는 하나의 문자식을 생성 
      SELECT LAST_NAME || JOB_ID AD "EMPLOYEES" 
FROM EMPLOYEES;
      🔽 세 컬럼을 연결

  • 문자열에 NULL 값을 결하봘 경우 결과는 문자열.
    즉, LAST_NAME || NULL 의 결과는 LAST_NAME

  • 의미있는 문자열 삽입 LITERAL 

-- 의미있는 문자열 삽입 LITERAL : 
COLUMN과 같이 간주 
EX) 1이라는 값을 갖는 컬럼이다 

-- 숫자 LITERAL 
select 
 DEPARTMENT_ID,
 LOCATION_ID,
 1   
from DEPARTMENTS;

-- 문자 LITERAL 
select 
 DEPARTMENT_ID,
 LOCATION_ID, 
 'AAA'   
from DEPARTMENTS;
  • 대체 인용(q) 연산자

🔽 오류 발생 -> 대체 인용(q) 연산자 사용 필

🔽 오류 해결 (1) 일반 단일 부호로 인식_단일 따옴표 연달아 입력

🔽 오류 해결 (2) 대체 인용(q) 연산자 사용

  • q + ' + [나타내고 싶은 문자열 표현] + '

  • 중복 행 제거: DISTINCT
    중복된 값을 한 가지만 화면에 출력 (속성 값 종류별 출력 가능)

    SELECT + DISTINCT
    SELECT 키워드 바로 뒤에 DISTINCT 키워드 포함

🔽 예제

SELECT DISTINCT DEPARTMENT_ID, JOB_ID
FROM EMPLOYEES;

  • DISTINCT 수식자는 선택된 모든 열에 적용, 결과에는 고유의 열 조합 표시

🔽

  • SQL 문 작성 실습 (CMD 창 활용)
    • $ sqlplus ora1/oracle_4U@localhost:1521/xepdb1 (SQL문 입력할 수 있는 환경으로 변경)
    • SQL > SELECT * FROM tab; (CMD 에서는 약어 사용 가능)
    • SQL> DESCRIBE dept
    • SQL> SELECT * FROM emp;
    • ALTER SESSION SET NLS_LANGUAGE=american;
      ALTER SESSION SET NLS_TERRITORY=america;
    • SQL> SELECT * FROM dept;
    • SQL> SELECT deptno, dname FROM dept ;
    • SQL> SELECT empno, ename, deptno FROM emp;
    • SQL> SELECT empno, ename, 'Daou', 2023 FROM emp;
    • SQL> SELECT empno, ename, sal*12, deptno
      FROM emp;
    • SELECT empno, ename, hiredate, hiredate+1, hiredate-1 FROM emp;
    • SELECT empno, ename, sal, comm, mgr FROM emp ;
    • SQL> SELECT empno, sal, comm, sal+comm FROM emp ;
    • SQL> SELECT empno id, ename AS name, sal*12 "Annual Salary" FROM emp ;
    • SQL> SELECT ename || job FROM emp ;
    • SQL> SELECT ename || ' is a ' || job FROM emp ;
    • SQL> SELECT ename || '''s Salary:' || sal FROM emp ;
    • SQL> SELECT ename || q'['s Salary:]'|| sal FROM emp ;
    • SQL> SELECT deptno FROM emp ;
    • SQL> SELECT DISTINCT deptno FROM emp ;

🧡 3장. 데이터 제한 및 정렬

  • where 문자열, 날짜 값 비교시 '' 로 표기
  • where 문자열 비교시 대소문자 구분 주의
  • between 연산자 사용 (range condition) 
select last_name, salary
from employees
where salary BETWEEN 2500 and 3500;

cf. 2500 <= salary <= 3500
-> and 로 연산 수행하기 때문에 error 발생

  • where 절에는 select 절에서 기재한 as 사용 불가 (쿼리문 실행 순서 존재)
    🔽

  • like 연산자 사용
    🔽


-- 09년도에 입사한 사원 모두 출력 
alter session set nls_date_format = 'DD-MON-RR' ;
select last_name, salary, hire_date
from employees
where hire_date like '%09';

-- 09일에 입사한 사원 모두 출력 
alter session set nls_date_format = 'yyyy/mm/dd' ;
select last_name, salary, hire_date
from employees
where hire_date like '%09'; 
-- 9일 입사한 직원으로 의미 변경 

-- 'yyyy/mm/dd' 날짜 포맷에서 2009년에 입사한 직원 모두 출력 
alter session set nls_date_format = 'yyyy/mm/dd' ;
select last_name, salary, hire_date
from employees
where hire_date like '%2009%';
  • like연산에서 escape / 의미 
create table ltest ( col  varchar2(20) ); 
insert into ltest values ( 'aX_Yb' ); -- 문자열이 x로 시작 y로 끝난다 
insert into ltest values ( 'aXbYc' );
insert into ltest values ( 'X_Yb' );
insert into ltest values ( 'aXxY' );


select * from ltest;
select * from ltest where col like '%X_Y%'; 
-- 언더바가 와일드카드가 아님을 select 절로 지칭 필
-- '\' escape 문자로 와일드카드가 아닌 일반 문자로 언더바 인식
select * from ltest where col like '%X\_Y%' escape '\';
  • 논리 연산자(and, or, not)
  • and, or 연산자 우선 순위 규칙
    • or을 먼저 수행하고 and로 조건 부여하려면 or 행에 괄호 표기하여 우선순위 부여 (그렇지 않으면 and 조건이 먼저 수행되어, and 행에 괄호 부여되어 쿼리문 실행됨)

🔽 

select last_name, ob_id, salary
from employees
where (job_id = 'SA_REP'
or       job_id = 'AD_PRES')
and   salary > 15000;
  • not 연산자

🔽

🔽 not BETWEEN

  • order by 절 사용
    • order by 절을 사용하여 검색된 행 정렬.
    • order by 절을 사용하지 않으면 화면 출력 값이 일정하지 않게됨.
    • 쿼리문 실행 순서 마지막, 절 기입 순서 마지막
    • 기본 정렬은 asc (작은 값부터)
    • null 값은 오름차순에서 마지막 표시

🧡 자습

  • 3장 실습 order by절 까지 자습 진행

    • null 값 먼저 출력시 아래와 같은 쿼리문 작성

select employee_id, department_id, salary, commission_pct
from employees
order by commission_pct desc;


select employee_id, department_id, salary, commission_pct
from employees
order by commission_pct nulls first;

  • null 값 맨 나중에 출력시 아래와 같은 쿼리문 작성

select employee_id, department_id, salary, commission_pct
from employees
order by commission_pct nulls last;


select employee_id, department_id, salary, commission_pct
from employees
order by commission_pct asc;

+) order by 기입된 컬럼명이 select절에 존재하지 않아도 쿼리문 실행 가능~

  • 특정 컬럼 기준으로 오름차순후, 속성 값이 동일 시 두 번째 컬럼 기준으로 오름차순 진행
    🔽

    select last_name, department_id, salary
    from employees
    order by department_id, salary;

  • 2장 연습문제 풀이

2-1) DEPARTMENTS 테이블의 구조와 테이블의 전 내용을 출력하는 쿼리를 작성하시오

🔽
SELECT *
FROM DEPARTMENTS;

2-2) EMPLOYEES 테이블의 구조와 다음의 결과를 출력하는 쿼리를 작성하세요.
HIRE_DATE 열에 대한 ALIAS로 STARTDATE를 출력하시오.

🔽
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, HIRE_DATE AS STARTDATE
FROM EMPLOYEES;

2-3) EMPLOYEES 테이블의 모든 JOB_ID를 하나씩만 출력하는 쿼리를 작성하시오

🔽
SELECT DISTINCT JOB_ID
FROM EMPLOYEES;

2-4) EMPLOYEES 테이블에서 LAST_NAME과 JOB_ID를 이어서 출력하고(쉼표와 공백으로 구분) 열 이름을 EMPLOYEE AND TITLE 로 지정합니다.

🔽
SELECT LAST_NAME || ',' ||JOB_ID AS "EMPLOYEE AND TITLE"
FROM EMPLOYEES;

2-5) 성공적으로 실행되는 SELECT 문 두 개를 고르시오.

🔽 답: 2, 3번

2번:
SELECT first_name, last_name, job_id, salary*12 "yearly sal"
FROM employees;

3번:
SELECT first_name, last_name, job_id, salary AS "yearly sal"
FROM employees;

  • 3장 연습문제 풀이

3-1) EMPLOYEES 테이블에서 SALARY를 12,000보다 많이 받는 사원을 출력하는 쿼리를 작성하세요.

🔽
SELECT LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY >= 12,000;

3-2) EMPLOYEES 테이블에서 EMPLOYEE_ID가 176인 사원을 출력하는 쿼리를 작성하시오.

🔽
SELECT LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 176;

3-3) EMPLOYEES 테이블에서 SALARY가 $5000~$12000의 범위에 속하지 않는 사원을 출력하는 쿼리를 작성하시오.

🔽
SELECT LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY NOT BETWEEN 5000 AND 12000;

3-4) LAST_NAME 이 MATOS 또는 TAYLOR 인 사원을 출력하는 쿼리를 작성하시오 (입사순으로 결과 정렬)

🔽
SELECT LAST_NAME, JOB_ID, HIRE_DATE
FROM EMPLOYEES
WHERE LAST_NAME = 'MATOS'
OR LAST_NAME = 'TAYLOR'
ORDER BY HIRE_DATE;

3-5) EMPLOYEES 테이블에서 DEPARTMENT_ID가 20 또는 50에 속하는 사원 정보를 LAST_NAME 의 알파벳 순서대로(오름차순) 정렬하여 출력하는 쿼리문을 작성하시오.

🔽
SELECT LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20
OR DEPARTMENT_ID = 50
ORDER BY LAST_NAME;

3-6) EMPLOYEES 테이블에서 SALARY가 $5000~$12000 범위에 속하며 DEPARTMENT_ID가 20 또는 50인 사원의 LAST_NAME, SALARY를 다음과 같이 출력하는 쿼리를 작성하시오.

🔽
SELECT LAST_NAME "EMPLOYEE", SALARY "MONTHLY SALARY"
FROM EMPLOYEES
WHERE SALARY BETWEEN 5000 AND 12000
AND DEPARTMENT_ID IN (20, 50) ;

3-7) EMPLOYEES 테이블에서 2010년에 채용된 모든 사원을 출력하는 쿼리를 작성하시오.

🔽
SELECT LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE LIKE '%10'
;

3-8) EMPLOYEES 테이블에서 MANAGER가 없는 사원을 출력하시오

🔽
SELECT LAST_NAME, JOB_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL;

3-9) EMPLOYEES 테이블에서 커미션을 받는 사원을 출력하시오. SALARY, COMMISSION_PCT의 내림차순으로 데이터를 정렬합니다.

🔽
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY SALARY DESC, COMMISSION_PCT DESC;

3-10) EMLOYEES 테이블에서 세 번째 문자가 "a"인 모든 사원을 출력하시오.

🔽
SELECT LAST_NAME
FROM EMPLOYEES
WHERE LASTNAME LIKE '__a%';

3-11) EMPLOYEES 테이블에서 LAST_NAME에 "a"와 "e"가 포함된 모든 사원의 성과 이름을 표시하시오.

🔽
SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE "%a%"
AND LAST_NAME LIKE "%e%";

3-12) 급여를 많이 받는 사원 5명을 표시하는 쿼리를 행 제한 구문을 이용해 작성

🔽
SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC
FETCH NEXT 5 ROWS ONLY;

profile
‍전희주
heejoojeon@daou.co.kr
이전 포스트

[DB]_프로젝트 발표 요약, 빅데이터 자격증 정리

다음 포스트

[JAVA]_23.02.21

0개의 댓글