SQL 2일차

한희수·2023년 3월 26일
0

빅데이터 분석 SQL

목록 보기
2/17

20230315 SQL

■ SQL DEVELOPER - dba
SELECT*FROM dba_users;
SELECT*FROM user_tables;
SELECT*FROM tab;
SELECT*FROM user_segments;

■ TABLESPACE_NAME : TBS(논리적 관점)

DB(논리적 관점) ----------- OS DISC(물리적 관점)
 |                                           |
TBS - -----------------------    DATA FILE
  |                                          |
SEGMENT(INDEX)                      |
  |                                          |
EXTENTS                                 |
  |                                          |
BLOCKS(ORACLE의 최소 os) --  OS BLOCK
  |                                          |
ROWS(행)

데이터 저장 위해 테이블 생성, 바로 만들 수 없고 DB에 TBS라는 덩어리 만들어야 함
OS에 data file 생성해야 함(여러 개 생김)

회사에선 TBS 업무별로 생성 \to BACKUP 위해 용량 분산하여 저장

tbs 안에는 segment 있음

SELECT*FROM dba_data_files;
\to 데이터 물리적 위치(OS DISC 안에 있는 data file임)

(데이터 예시)
users (tbs)
   |
employees (segments)

- TABLESPACE : 책장 하나가 데이터, 책장 단 하나하나가 TBS (편리한 관리성 위해 TBS 분리)
- SEGMENT : 책 한 권이 segment
- EXTENT : 책 내용의 1장,, 2장, 3장, ...
- BLOCK(PAGE, ms) : 책의 한 페이지
- ROW(행) : 책 페이지의 문장들

C:\oraclexe\app\oracle\oradata\XE 
\to 실제 물리적 위치이나 DB쓰면 위치 몰라도 사용가능 편리함

■ 리터럴 문자열(어제에 이어)
SELECT q'[My name’s]'||last_name||' '||first_name
FROM employees;

q'[리터럴문자열]'
q'<리터럴문자열>'
q'(리터럴문자열)'
q'{리터럴문자열}'
q'!리터럴문자열!'

\to 어떤 것을 사용해도 무관

■ 중복행 제거
- distinct 키워드를 사용해서 중복을 제거함
- distinct 키워드는 SELECT 행 제일 앞에 한 번만 사용함

SELECT distinct deparment_id
FROM employees;

SELECT distinct deparment_id, job_id
FROM employees;

[문제1] employees 테이블에서 employee_id, last_name과 first_name은 연결해서 표시하고(공백으로 구분) 열 별칭은 화면 예처럼 보고서 작성해 주세요.
<화면 결과>
Emp# Employee Name


100 King Steven
101 Kochhar Neena

SELECT
employee_id "Emp#", last_name || ' ' || first_name "Employee Name"
FROM employees;

[문제2] employees 테이블에서 컬럼 중에 last_name, job_id를 연결해서 표시하고(쉼표와 공백으로 구분) 열 별칭은 화면 예처럼 보고서 작성하세요.
<화면 결과>
Employee and Title


Abel, SA_REP
ANDE, SA_REP

SELECT last_name || ‘, ’ || job_id "Employee and Title"
FROM employees;

[문제3] departments 테이블에 있는 데이터에서 department_name, manager_id 칼럼을 가지고 화면 결과처럼 출력하는 쿼리 문장(select 문장)을 만드세요.
<화면 결과>
Department and Manager


Administration Department’s Manager id: 200
Marketing Department’s Manager id: 201

SELECT department_name || ' Department''s Manager id: ' || manager_id as "Departemnt and Manager"
FROM departments;

SELECT department_name ||q ' ( Department's Manager id: ) ' || manager_id as "Departemnt and Manager"
FROM departments;
SELECT 컬럼, 컬럼
FROM 테이블
WHERE 기준컬럼 비교연산자 비교값
AND 기준컬럼 비교연산자 비교값

■ WHERE 절

  • 행을 제한하는 절
  • 조건절
  • 기준컬럼이 문자열, 날짜열이면 비교값은 ‘작은 따옴표’로 묶어야 함
  • 영문자는 대소문자 구분함
  • 날짜형식은 지역에 따라 기본날짜 표시 방식이 다름(날짜 비교 힘듦!)
    -- SQL developer 운영지역 셋업지역에 따라 자동 표기
    -- 한국(RR/MM/DD), 미국(DD-MON-RR)
  • 비교연산자
    = (같다), > (크다), >= (크거가 같다), < (작다), <= (작거나 같다), !=, *=. <> (같지 않다)
  • 논리연산자
    AND (두 개 조건이 모두 참일 경우 TRUE)
    OR (두 조건 중에 하나가 참일 경우 TRUE)
    NOT (FALSE 경우 TRUE, TRUE일 경우 FALSE)

■ WHERE 절, 비교연산자 연습
desc employees; \to 테이블 구조 먼저 확인할 것!

  • 숫자열 예시

SELECT *
FROM employees
WHERE department_id = 100;

SELECT *
FROM employees
WHERE department_id ^= 50;

SELECT *
FROM employees
WHERE salary >= 10000;

  • 문자열 예시(‘’)
    SELECT * (모든 것)
    FROM employees
    WHERE last_name = ‘King’; (대소문자 구분 필요!)

  • 날짜열 예시(‘’)
    SELECT *
    FROM employees
    WHERE hire_date = '04/01/30'; (셋업지역 기준)

◆ OR 연습
SELECT *
FROM employees
WHERE employee_id = 100
OR employee_id = 101;

SELECT *
FROM employees
WHERE employee_id = 100
OR employee_id = 300; => 300은 없으므로 100만 나옴

SELECT *
FROM employees
WHERE department_id = 20
OR salary >= 15000; => 이 경우 20번 부서 아니어도 무관

◆ AND 연습 \to 두 조건 다 만족해야 함
SELECT *
FROM employees
WHERE department_id = 20
AND salary <= 15000;

[문제4] employees 테이블에서 급여(salary)가 2500 ~ 3500인 사원들의 last_name, salary를 출력해주세요.

SELECT last_name, salary
FROM employees
WHERE salary >= 2500
AND salary <= 3500;

\to ‘salary’ 반복 작성X

SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;

■ BETWEEN 하한값 AND 상한값(숫자열)
범위 조건을 사용하여 값의 범위에 따라 행을 추출할 때 사용
WHERE 기준컬럼 >= 비교값
AND 기준컬럼 <= 비교값

[문제5] employees 테이블에서 급여(salary)가 2500 ~ 3500이 아닌 사원들의 last_name, salary를 출력해주세요.

SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 2500 AND 3500;

SELECT last_name, salary
FROM employees
WHERE salary < 2500
OR salary > 3500;

◆ 문자열(알파벳 순)
SELECT *
FROM employees
WHERE last_name >= 'Abel'
AND last_name <= 'Austin';

SELECT *
FROM employees
WHERE last_name BETWEEN 'Abel' AND 'Austin’
AND department_id =80;

SELECT *
FROM employees
WHERE last_name NOT BETWEEN 'Abel' AND 'Austin';

◆ 논리연산자 순위
NOT > AND > OR

[문제6] employees 테이블에서 hire_date(입사일) 2001(01) ~ 2002(02) 년도에 입사한 사원 정보를 출력해주세요.

SELECT *
FROM employees
WHERE hire_date >= '01/01/01'
AND hire_date >= '02/12/31';

SELECT *
FROM employees
WHERE hire_date BETWEEN '01/01/01' AND '02/12/31';

=> 날짜열, 문자열 작은 따옴표 필수!
=> 날짜열 ‘세기(2000년도)’ 안 보이지만 입력되어 있음(연도 4자리 쓰는 습관 가지는 것 좋음)

SELECT *
FROM employees
WHERE hire_date BETWEEN '2001/01/01' AND '2002/12/31';

■ IN 연산자

  • 각 목록의 값과 일치하는 값을 추출할 때 사용
  • 기존 컬럼 중복

SELECT *
FROM employees
WHERE employee_id = 100
OR employee_id = 101
OR employee_id = 102;

SELECT *
FROM employees
WHERE employee_id IN (100,101,102);

SELECT *
FROM employees
WHERE employee_id ^= 100
AND employee_id ^= 101
AND employee_id ^= 102;

SELECT *
FROM employees
WHERE employee_id NOT IN (100,101,102);

■ 페이징 처리(투닝)
◆ “50개 행이 인출됨” \to 더 많을수도
◆ “인출된 모든 행: 104”

■ AND, OR 동시 실행

  • 부서ID 30, 50, 60인 사원 중 급여 5000 초과인 사원 출력 원함
  • AND가 OR보다 우선순위이나 OR가 먼저 돌아가게 할 수 있음(우선순위 제어: 괄호로 표현)

SELECT *
FROM employees
WHERE department_id = 30
OR department_id = 50
OR department_id = 60
AND salary > 5000; => 이 부분이 먼저 돌아감, 그 이후 ID = 50, 60인 사원 전부 나옴

SELECT *
FROM employees
WHERE (department_id = 30
OR department_id = 50
OR department_id = 60)
AND salary > 5000;

\to 여기에서 IN 연산자 사용(괄호 안써도 돼서 편함)

SELECT *
FROM employees
WHERE department_id IN (30,50,60)
AND salary > 5000;

■ NULL값 체크하는 연산자

  • IS NULL, IS NOT NULL

SELECT *
FROM employees
WHERE commission_pct IS NULL;

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
AND salary >= 10000;

\to 커미션 비율이 결측값 아닌 사원 중에 급여가 만과 같거나 큰 사원 출력

■ LIKE 연산자

  • 문자패턴을 찾는 연산자이므로 기존컬럼이 문자가 아닌 다른 유형의 컬럼이면 내부적으로 문자형으로 변환하므로 문자 타입 컬럼에만 사용할 것!

  • % : 0개 이상의 문자를 찾을 때 사용

  • _ : 1개 문자를 찾을 때 사용

SELECT *
FROM employees
WHERE last_name LIKE 'K%';
\to K로 시작하는 성 가진 모든 사원 출력

SELECT *
FROM employees
WHERE last_name LIKE 'K___';
\to K로 시작하고 4글자인 성 가진 사원 출력

SELECT *
FROM employees
WHERE last_name LIKE '_i%';
\to 두 번째 글자만 알고, 세 번째는 글자 있을수도 없을수도 있음

◆ 사용하지 말아야 할 쿼리문장 => 이렇게 쿼리문장을 작성하지 마세요!!!!!!!!!!!!!!!

SELECT
FROM employees
WHERE last_name LIKE 'King’;

SELECT

FROM employees
WHERE last_name = 'King’;

SELECT *
FROM employees
WHERE salary LIKE '5%’;

SELECT *
FROM employees
WHERE hire_date LIKE '02%’;
\to 수행이 되긴 함

  • oracle에서 문자 타입으로 바꾸어 버림 (INTERNAL_FUNCTION, F10 실행계획 확인 가능)
  • 악성 프로그램 됨!!! 투닝 때 배움

[부록]
des employees
오류 보고 -
알 수 없는 명령

이름널?유형
EMPLOYEE_IDNOT NULLNUMBER(6)
FIRST_NAMEVARCHAR2(20)
LAST_NAMENOT NULLVARCHAR2(25)
EMAILNOT NULLVARCHAR2(25)
PHONE_NUMBERVARCHAR2(20)
HIRE_DATENOT NULLDATE \to 날짜 타입의 컬럼
JOB_IDNOT NULLVARCHAR2(10)
SALARYNUMBER(8,2)
COMMISSION_PCTNUMBER(2,2)
MANAGER_IDNUMBER(6)
DEPARTMENT_IDNUMBER(4)

■ LIKE 연산자에서 ESCAPE ‘\’ 활용 예시
◆ ESCAPE ‘\’ : LIKE 연산자 사용시 % 또는 _를 순수한 문자로 인식할 수 있는 방법

  1. ‘%’를 문자로 인식
  • LIKE 연산자를 사용할 때 ‘%’를 문자로 표현하고 싶음
  • JOB_ID : 가상의 데이터

JOB_ID


HRREP
HR%REP \larr 이걸 찾고 싶음
HRREP
HR_PROG
HR%PROG \larr 이걸 찾고 싶음
HR
%REP


SELECT *
FROM employees
WHERE job_id LIKE ‘HR\%%’ ESCAPE ‘\’;

  1. ‘_’를 문자로 인식

JOB_ID


HRREP \larr 이걸 찾고 싶음
HR%REP
HRREP
HR_PROG \larr 이걸 찾고 싶음
HR%PROG
HR
%REP


SELECT *
FROM employees
WHERE job_id LIKE ‘HR\_%’ ESCAPE ‘\’;

  1. ‘_%’를 문자로 인식

JOB_ID


HRREP
HR%REP
HRREP
HR_PROG
HR%PROG
HR
%REP \larr 이걸 찾고 싶음


SELECT *
FROM employees
WHERE job_id LIKE ‘HR\_\%%’ ESCAPE ‘\’;

[문제7] employees 테이블에 있는 데이터 중에 job_id가 SA로 시작되고 salary 값은 10000 이상 받는 사원들의 정보를 출력해주세요.

SELECT *
FROM employees
WHERE job_id LIKE 'SA%' \to 작따와 큰따를 잘 구분하기!!!
AND salary >= 10000;

[문제8] last_name의 세 번째 문자가 ‘a’ 또는 ‘e’ 글자가 포함된 사원들의 정보를 출력해주세요.

SELECT *
FROM employees
WHERE lastname LIKE '__a'
OR lastname LIKE '__e';
\to 네 글자라고는 안 했음!!!!!!!!!

SELECT *
FROM employees
WHERE last_name LIKE 'a%'
OR last_name LIKE '
e%';

[문제9] employees 테이블에 있는 데이터 중에 job_id가 SA로 시작되고 salary 값은 10000 이상 받고 2005년도에 입사한(hire_date) 사원들의 정보를 출력해주세요.

SELECT *
FROM employees
WHERE job_id LIKE 'SA%'
AND salary >= 10000
AND hire_date BETWEEN 2005/01/01 AND 2005/12/31; \to 작은 따옴표 필수!!!!!!

SELECT *
FROM employees
WHERE job_id LIKE 'SA%'
AND salary >= 10000
AND hire_date BETWEEN ‘2005/01/01’ AND ‘2005/12/31’;

[문제10] employees 테이블에 있는 데이터 중에 job_id가 SA_REP 또는 AD_PRES인 사원들 중에 salary 값이 10000 초과한 사원들의 정보 출력해주세요.

SELECT *
FROM employees
WHERE (job_id = SA_REP
OR job_id = AD_PRES) \to 작은 따옴표 필수!!!!!!
AND salary > 10000;

SELECT *
FROM employees
WHERE (job_id = 'SA_REP'
OR job_id = 'AD_PRES')
AND salary > 10000;

SELECT *
FROM employees
WHERE job_id IN ('SA_REP', 'AD_PRES') => 작은 따옴표와 괄호 중요!!!
AND salary > 10000;

■ SORT(정렬)

  • ORDER BY 절 이용
  • SELECT문의 가장 마지막에 기술
  • 기본값으로 오름차순 정렬 ASC(ascending)
  • 내림차순 정렬: DESC(Descending)

SELECT
FROM
WHERE
GROUP BY (군집)
HAVING
START WITH (계층 검색-트리 구조)
CONNECT BY (계층 검색-트리 구조)
ORDER BY

(예시)
SELECT salary, employee_id
FROM employees
ORDER BY salary asc;
\to asc는 기본값이므로 생략 가능

SELECT salary, employee_id
FROM employees
ORDER BY salary desc;

SELECT employee_id, salary*12 \to 표현식도 같이 표현해줘야 함!!
FROM employees
ORDER BY salary*12;

SELECT employee_id, salary*12 annual_salary
FROM employees
ORDER BY salary*12 desc;

SELECT employee_id,(salary*12) + (salary*12*nvl(commission_pct,0))
FROM employees
ORDER BY (salary*12) + (salary*12*nvl(commission_pct,0)) desc;
\to 표현식도 같이 표현해줘야 함!!

SELECT employee_id,(salary*12) + (salary*12*nvl(commission_pct,0)) annual_salary
FROM employees
ORDER BY (salary*12) + (salary*12*nvl(commission_pct,0)) desc;

SELECT employee_id,(salary*12) + (salary*12*nvl(commission_pct,0)) annual_salary
FROM employees
ORDER BY annual_salary desc;

SELECT employee_id,(salary*12) + (salary*12*nvl(commission_pct,0)) "annual_salary"
FROM employees
ORDER BY "annual_salary" desc;

\to 동일함! (열 별칭 사용; 단, 큰 따옴표 사용시 표현식 그대로 order by 절에도 써야 함)

◆ 위치 표기법 이용 예시
SELECT employee_id,(salary*12) + (salary*12*nvl(commission_pct,0)) "annual_salary"
FROM employees
ORDER BY 2 desc;

◆ 여러 컬럼 정렬 예시

  • 부서 id 오름차순, 부서 내 사원들의 급여 내림차순
    SELECT employee_id, department_id, (salary*12) + (salary*12*nvl(commission_pct,0)) "annual_salary"
    FROM employees
    ORDER BY 2 asc, 3 desc;
    \to asc는 생략 가능

실무 + 프로그래밍 감각

[문제11] 2006년도 입사한 사원의 employee_id, last_name, hire_date를 출력해주세요. 단, last_name의 경우 이름을 기준으로 오름차순 정렬

SELECT employee_id, last_name, hire_date
FROM employees
WHERE hire_date BETWEEN '2006/01/01' AND '2006/12/31'
ORDER BY last_name asc;

[문제12] 80번 department_id 사원 중에 commission_pct가 0.2이고 job_id는 SA_MAN인 사원의 employee_id, last_name, salary를 출력해주세요. 단, last_name 오름차순 정렬

SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = '80'
AND commission_pct = '0.2'
AND job_id = 'SA_MAN'
ORDER BY last_name asc; \to 위 출력 결과 한 명뿐이므로 안 써도 무방

[문제13] salary가 5000 ~ 12000에 속하지 않는 모든 사원의 last_name 및 salary를 출력해주세요. 단, salary 내림차순 정렬

SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000
ORDER BY salary desc;

SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000
ORDER BY 2 desc; \to 위치 표기법 이용

0개의 댓글