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 업무별로 생성 BACKUP 위해 용량 분산하여 저장
tbs 안에는 segment 있음
SELECT*FROM dba_data_files;
데이터 물리적 위치(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
실제 물리적 위치이나 DB쓰면 위치 몰라도 사용가능 편리함
■ 리터럴 문자열(어제에 이어)
SELECT q'[My name’s]'||last_name||' '||first_name
FROM employees;
q'[리터럴문자열]'
q'<리터럴문자열>'
q'(리터럴문자열)'
q'{리터럴문자열}'
q'!리터럴문자열!'
어떤 것을 사용해도 무관
■ 중복행 제거
- 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 절
■ WHERE 절, 비교연산자 연습
desc employees; 테이블 구조 먼저 확인할 것!
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 연습 두 조건 다 만족해야 함
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;
‘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개 행이 인출됨” 더 많을수도
◆ “인출된 모든 행: 104”
■ AND, 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;
여기에서 IN 연산자 사용(괄호 안써도 돼서 편함)
SELECT *
FROM employees
WHERE department_id IN (30,50,60)
AND salary > 5000;
■ 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;
커미션 비율이 결측값 아닌 사원 중에 급여가 만과 같거나 큰 사원 출력
■ LIKE 연산자
문자패턴을 찾는 연산자이므로 기존컬럼이 문자가 아닌 다른 유형의 컬럼이면 내부적으로 문자형으로 변환하므로 문자 타입 컬럼에만 사용할 것!
% : 0개 이상의 문자를 찾을 때 사용
_ : 1개 문자를 찾을 때 사용
SELECT *
FROM employees
WHERE last_name LIKE 'K%';
K로 시작하는 성 가진 모든 사원 출력
SELECT *
FROM employees
WHERE last_name LIKE 'K___';
K로 시작하고 4글자인 성 가진 사원 출력
SELECT *
FROM employees
WHERE last_name LIKE '_i%';
두 번째 글자만 알고, 세 번째는 글자 있을수도 없을수도 있음
◆ 사용하지 말아야 할 쿼리문장 => 이렇게 쿼리문장을 작성하지 마세요!!!!!!!!!!!!!!!
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%’;
수행이 되긴 함
[부록]
des employees
오류 보고 -
알 수 없는 명령
이름 | 널? | 유형 |
---|---|---|
EMPLOYEE_ID | NOT NULL | NUMBER(6) |
FIRST_NAME | VARCHAR2(20) | |
LAST_NAME | NOT NULL | VARCHAR2(25) |
NOT NULL | VARCHAR2(25) | |
PHONE_NUMBER | VARCHAR2(20) | |
HIRE_DATE | NOT NULL | DATE 날짜 타입의 컬럼 |
JOB_ID | NOT NULL | VARCHAR2(10) |
SALARY | NUMBER(8,2) | |
COMMISSION_PCT | NUMBER(2,2) | |
MANAGER_ID | NUMBER(6) | |
DEPARTMENT_ID | NUMBER(4) |
■ LIKE 연산자에서 ESCAPE ‘\’ 활용 예시
◆ ESCAPE ‘\’ : LIKE 연산자 사용시 % 또는 _를 순수한 문자로 인식할 수 있는 방법
JOB_ID
HRREP
HR%REP 이걸 찾고 싶음
HRREP
HR_PROG
HR%PROG 이걸 찾고 싶음
HR%REP
SELECT *
FROM employees
WHERE job_id LIKE ‘HR\%%’ ESCAPE ‘\’;
JOB_ID
HRREP 이걸 찾고 싶음
HR%REP
HRREP
HR_PROG 이걸 찾고 싶음
HR%PROG
HR%REP
SELECT *
FROM employees
WHERE job_id LIKE ‘HR\_%’ ESCAPE ‘\’;
JOB_ID
HRREP
HR%REP
HRREP
HR_PROG
HR%PROG
HR%REP 이걸 찾고 싶음
SELECT *
FROM employees
WHERE job_id LIKE ‘HR\_\%%’ ESCAPE ‘\’;
[문제7] employees 테이블에 있는 데이터 중에 job_id가 SA로 시작되고 salary 값은 10000 이상 받는 사원들의 정보를 출력해주세요.
SELECT *
FROM employees
WHERE job_id LIKE 'SA%' 작따와 큰따를 잘 구분하기!!!
AND salary >= 10000;
[문제8] last_name의 세 번째 문자가 ‘a’ 또는 ‘e’ 글자가 포함된 사원들의 정보를 출력해주세요.
SELECT *
FROM employees
WHERE lastname LIKE '__a' 네 글자라고는 안 했음!!!!!!!!!
OR lastname LIKE '__e';
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; 작은 따옴표 필수!!!!!!
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) 작은 따옴표 필수!!!!!!
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(정렬)
SELECT
FROM
WHERE
GROUP BY (군집)
HAVING
START WITH (계층 검색-트리 구조)
CONNECT BY (계층 검색-트리 구조)
ORDER BY
(예시)
SELECT salary, employee_id
FROM employees
ORDER BY salary asc;
asc는 기본값이므로 생략 가능
SELECT salary, employee_id
FROM employees
ORDER BY salary desc;
SELECT employee_id, salary*12 표현식도 같이 표현해줘야 함!!
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;
표현식도 같이 표현해줘야 함!!
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;
동일함! (열 별칭 사용; 단, 큰 따옴표 사용시 표현식 그대로 order by 절에도 써야 함)
◆ 위치 표기법 이용 예시
SELECT employee_id,(salary*12) + (salary*12*nvl(commission_pct,0)) "annual_salary"
FROM employees
ORDER BY 2 desc;
◆ 여러 컬럼 정렬 예시
실무 + 프로그래밍 감각
[문제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; 위 출력 결과 한 명뿐이므로 안 써도 무방
[문제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; 위치 표기법 이용