엔터티란(Entity)
SQL 기본2022. 10. 6. 09:35
엔티티란? Entity 란 업무의 관심 대상이 되는 정보를 갖고 있거나 그에 대한 정보를 알아야하는 유형, 무형의 사물이나 객체를 말한다.
– 엔티티의 예)
• 고객, 사원정보, 부서, 제품 • 주문서, 성적표, 입고전표, 금전출납부 • 생산계획, 공정
3장. 데이터모델링의 주요 개념
❑ 개요 ❑ 엔티티 ❑ 속성 ❑ 관계 ❑ 주식별자와 외래 식별자 ❑ ERD 표기법
'SQL 기본' 카테고리의 다른 글
| 11장 . 기타 스키마 객체 (0) | 2017.09.07 |
|---|---|
| 10장 DDL문을 사용해서 테이블을 생성 및 관리 (0) | 2017.09.07 |
| 9장 데이터 조작 언어 (DML문) (0) | 2017.09.07 |
| 8장 집합 연산자 사용 (0) | 2017.09.07 |
| 7장 Subquery 사용 (0) | 2017.09.07 |
11장 . 기타 스키마 객체
SQL 기본2017. 9. 7. 03:13
identified
■11장 . 기타 스키마 객체
- 목차
- VIEW
- INDEX
- SEQUENCE
- SYNONYM
11.1 VIEW
"테이블을 바라보는 쿼리문의 결과"
- VIEW 가 필요한 이유
- 데이터의 보안을 위해서
- 복잡한 쿼리를 단순하게 조회하기 위해서
create view emp990
as
select empno , ename, job, comm, job, deptno
from emp;
select * from emp990;
뷰를 수정하면 테이블도 수정됨 !!
update emp990
set comm= 9000
where ename = 'MARTIN';
문제331. 부서번호, 부서번호별 평균월급을 출력하시오
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO;
문제332. 이름, 월급, 부서번호,부서번호별 평균월급
SELECT ENAME, SAL, DEPTNO, SUM(SAL)
FROM EMP;
GROUP BY DEPTNO
------------------------뷰 만들어서 쓰기-------------------------------
CREATE VIEW D_AVG
AS SELECT DEPTNO , AVG(SAL) 평균월급
FROM EMP
GROUP BY DEPTNO;
SELECT ENAME, SAL, E.DEPTNO, ROUND(D.평균월급,2)
FROM EMP E, D_AVG D
WHERE E.DEPTNO = D.DEPTNO;
-------------------------------인 라인 뷰--------------------------------------
SELECT ENAME, SAL, E.DEPTNO, ROUND(D.평균월급,2)
FROM EMP E, (SELECT DEPTNO , AVG(SAL) 평균월급
FROM EMP
GROUP BY DEPTNO) D
WHERE E.DEPTNO = D.DEPTNO;
문제333.위의 결과를 다시 출력하는데 자기의 월급이 자기가 속한 부서번호의
평균월급보다 더 큰 사원만 출력하시오
SELECT ENAME, SAL, E.DEPTNO, ROUND(D.평균월급,2)
FROM EMP E, (SELECT DEPTNO , AVG(SAL) 평균월급
FROM EMP
GROUP BY DEPTNO) D
WHERE E.DEPTNO = D.DEPTNO AND E.SAL > 평균월급;
문제334.(오늘의 마지막 문제)
통신사 ,이름, 나이, 순위를 출력하는데
순위가 각 통신사별로 각각 나이가 높은 순서대로 순위를 부여하는 결과를
출력할 때에 순위가 1등,2등,3등만 출력되게 하시오
SELECT *
FROM
(SELECT TELECOM,
ENAME,
AGE,
DENSE_RANK() OVER (PARTITION BY TELECOM ORDER BY AGE DESC) RNK
FROM EMP2
)
WHERE RNK <=3 ;
- 제약(constraint) " 데이터의 품질을 높이기 위해서"
- primary key
- unique
- not null
- check
- foreign key
-
Database object 5가지
- table -->행과 열로 이루어진 데이터를 저장하는 기본 저장단위
-
Database object 5가지
- view --> 테이블을 바라보는 쿼리문의 결과
in line view 비교
- index
- sequence
- synonym
view의 장점 :? 1. 데이터 엑세스 제한
(보안상 감춰야 할 컬럼들을 감추기 위해서)
- 복잡한 쿼리를 단순화
문제335. 이름,월급, 등급을 출력하는 ntile 분석함수를 이용해서 등급을 4등급으로 나눠서 출력하세요
(월급이 높은 순서대로 등급을 나누시오!)
select ename, sal,
(4) over (order by sal desc) 등급
from emp;
문제336.위의 쿼리의 결과를view로 생성하시오 !
(view 이름 : emp336)
create view emp336
as
select ename, sal, ntile(4) over (order by sal desc) 등급
from emp;
문제337. emp336 view에서 등급이 4등급인 사원들만 출려하시오
-view
SELECT *
FROM emp336
where 등급 = 4;
-in line view
SELECT *
FROM (SELECT ename,
sal,
ntile(4) over (ORDER BY sal desc) 등급
FROM emp)
where 등급 = 4;
설명 : select 문에서 서브쿼리를 쓸 수있는 절
SELECT --- subquery (명칭: 스칼라 서브쿼리)
FROM --- subquery ( 명칭:인 라인 뷰)
WHERE --- subquery
GROUP BY
HAVING --- subquery
ORDER BY ---subquery
문제338. 이름, 월급, 사원 테이블 전체의 평균 월급을 출력하시오
select ename, sal, (select avg(sal) from emp)
from emp;
- view의 종류 2가지 (p11-7)
단순 view 복합 view
테이블의 갯수 1개 1개 초과
group 함수나 불 포함 포함
group by 절
DML 여부 가능 불가능할 수도 있다.
문제339. 이름, 월급과 부서 위치를 출력하는 VIEW를 생성하시오
(view 이름 : emP339)
CREATE VIEW EMP339
AS
SELECT ENAME,SAL,LOC
FROM EMP , DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
문제340. EMP339 VIEW를 수정하는데 이름이 KING인 사원의 부서위치를
SEOUL로 변경하시오
UPDATE EMP339
SET LOC='SEOUL'
WHERE ENAME='KING';
복합 뷰이기 때문에 에러가 난다.
뷰는 데이터를 갖고 있지 않다
저렇게 하면 실제 테이블로 가서 업데이트를 한다
그러면 다른 사람들의 LOC도 바뀐다
그러므로 복합뷰는 갱신 할 수 없다.
문제341. 부서번호, 부서번호별 평균월급을 출력하는 VIEW를 생성하시오
(VIEW 이름: EMP341)
CREATE VIEW EMP341
AS
SELECT DEPTNO, AVG(SAL) AVG
FROM EMP E
GROUP BY DEPTNO;
문제342. 부서번호가 10번인 사원들의 평균월급을 2000으로 변경해보시오!
( VIEW 이름: EMP343)
UPDATE EMP341
SET AVG= 2000
WHERE DEPTNO=10
문제343. 부서번호가 20번인 사원들의 사원번호, 이름, 월급, 부서번호를 출력하는 VIEW를 생성하시오
CREATE VIEW EMP343
AS
SELECT DEPTNO, ENAME, DEPTNO
FROM EMP
WHERE DETPNO = 20;
문제344. VIEW를 수정을 하는데
EMP343뷰를 수정하는데 JONES의 부서번호를 30번으로 변경하시오
UPDATE EMP343
SET DEPTNO = 30
WHERE ENAME = 'JONES';
■ VIEW 생성시 옵션
- WITH CHECK OPTION : VIEW 의 WHERE 절에서 기술한 조건에 위배되게끔 DATA를 갱신하지 못하게 하는 옵션
2. WITH READ ONLY : VIEW 전체를 다 수정, 삭제하지 못하게 하고 오로지 읽기만 할 수 잇게 하는 옵션
DROP VIEW EMP343;
CREATE VIEW EMP343
AS
SELECT EMPNO, ENAME, SAL DEPTNO
FROM EMP
WHERE DEPTNO = 30
WITH CHECK OPTION;
문제345. 사원 테이블의 이름과 월급을 출력하는 VIEW를 생성하는데
월급을 9600으로는 수정못하게 하는 VIEW를 생성핫이오
(VIEW 이름:EMP345)
CREATE VIEW EMP345
AS
SELECT ENAME, SAL
FROM EMP
WHERE sal between 0 and 9600
WITH CHECK OPTION;
문제346. with check옵션을 걷어내시오
CREATE or replace VIEW EMP345
AS
SELECT ENAME, SAL
FROM EMP
WHERE sal between 0 and 9600
※설명: emp345 view 가 존재하면 수정하고 없으면 새롭게 만들어라
※ 삭제 drop view emp346
문제347. (점심시간 문제) 페이스북에서 키워드 '입장권'으로 스크롤링한
csv 파일을 오라클에 table에 입력하시오
(테이블명 : facebook_ticket)
select * from facebook_ticket;
문제348. 데이터 입력한 페이스북 티켓 테이블의 데이터중 시간정보 끝에 , 를 잘라내는 쿼릴 완성하고
CTAS를 이용해서 FACEBOOK_TICKET2 라는 테이블을 생성하시오
create table FACEBOOK_TICKET2 as
select rtrim(use_time,',') use_time ,to_lob(use_word) use_word from FACEBOOK_TICKET;
select * from FACEBOOK_TICKET2;
문제349. 아래와 같이 결과를 출력하시오!
년도/월/일 : 시간 별, 건수
select substr(use_time,1,13) a, count(*)
from FACEBOOK_TICKET
group by substr(use_time,1,13)
order by a asc nulls last;
문제350. 아래와 같이 입장권에 대한 글이 facebook에 가장 많이 올라온 날짜부터 출력
select substr(use_time,1,10) a, count(*)
from FACEBOOK_TICKET
group by substr(use_time,1,10)
order by count(*) desc nulls last;
11.2 index ----> data 검색속도를 높이는 db object
- data를 엑세스 하는 방법
- full table scan : 테이블을 처음부터 끝까지 전부 스캔해서 원하는 data를 검색하는 방법
- index range scan : 인덱스( 예 : 책의 목차) 를 통해서 테이블의 data를 검색하는 방법
문제351. 이름이 SCOTT인 사원의 이름과 월급을 조회하시오
SELECT ENAME, SAL
FROM EMP
WHERE ENAME = 'SCOTT';
- 인덱스 생성 구문
CREATE INDEX EMP_ENAME <----------인덱스 이름
ON EMP(ENAME)
↑ ↑
테이블명 컬럼명
- 인덱스의 구조
- 컬럼값 + rowid (해당 로우의 주소)
예: 페이지 번호
- 컬럼값이 ascending 하게 정렬되어있다 ( 영무능 abcd 순서대로 정렬되어있다.)
select ename, rowid
from emp
where ename > ' ';
* 아래의 쿼리의 실행계획을 각각 보시오
1. table 에서 데이터를 읽은 경우
select ename, rowid
from EMP;
실행계획 확인 방법 : F7
2. index 에서 데이터를 읽은 경우
select ename, rowid
from emp
where ename > ' ';
----------------------------------------------------------------------------------------
|Operation | Object Name | Rows | Bytes | Cost | Object Node | In/Out | PStart | PStop | Access Predicates | Filter Predicates |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT Optimizer Mode=ALL_ROWS | | 14 | 266 | 2 | | | | | | |
| TABLE ACCESS FULL |SCOTT.EMP | 14 | 266 | 2 | | | | | | |
select ename, rowid
from emp
WHERE ENAME > ' ';
문제352. EMP 테이블에 월글에 INDEX를 거시오
CREATE INDEX EMP_SAL <----------인덱스 이름
ON EMP(SAL)
문제353. EMP_SAL 인덱스의 구조를 확인하시오
컬럼값 + ROWID
SELECT SAL ,
rowid
FROM emp
WHERE SAL > 0;
문제354. 월급이 1600인 사원의 이름과 월급을 출력하시오!
아래의 SQL이 인덱스를 통해서 데이터를 조회했는지
아니면 테이블을 Full table scan 을 해서 조회햇는지
알아내려면 어떻게 해야 하는가?
SELECT ENAME, SAL
FROM EMP
WHERE SAL = 1600;
문제355. 입사일에 인덱스를 걸어보세요
create index emp_hiredate
on emp(hiredate);
문제.356. 입사일에 걸린 인덱스의 구조를 확인하시오!
컬럼값 , rowid
select hiredate, rowid
from emp
where hiredate < to_date('9999/12/31','RRRR/MM/DD');
- 인덱스의 구조를 확인하기 위해서 where 절에 조건을 주는 조건
- 문자 > ' '
- 숫자 > 0
- 날짜 < to_date('9999/12/31','RRRR/MM/DD')
문제357. 아래의 SQL의 결과를 ORDER BY 절 쓰지 말고 데이터가 정렬 될 수 있도록
다시 재작성하시오! ( 튜닝하시오)
튜닝전 :
SELECT ENAME, SAL
FROM EMP
ORDER BY SAL ASC;
튜닝후 :
SELECT ENAME, SAL
FROM EMP
WHERE SAL > 0;
SELECT /*+ INDEX_ASC(EMP EMP_SAL) */ENAME,
SAL
FROM EMP
WHERE SAL > 0;
설명 : /*+ 힌트 */ 힌트? 실행계획을 제어하는 명령어
INDEX_ASC ----> 인덱스를 ASCE NDING 하게 읽어라
INDEX_ASC(EMP EMP_SAL)
↓ ↓
테이블명 인덱스 이름
문제358. 아래의 SQL을 ORDER BY 절을 이용하지 말고 인덱스를 통해서 정렬된 결과를 보시오
SELET ENAME, SAL
FROM EMP
WHERE JOB = 'SALESMAN'
ORDER BY SAL ASC;
문제359. 아래의 SQL을 ORDER BY 절 없이 수행되게끔 SQL을 고치시오
튜닝전 : SELECT ENAME, HIREDATE
FROM EMP
ORDER BY HIREDATE DESC;
튜닝후 :
SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE < TO_DATE('9999/12/31','RRRR/MM/DD');
머신러닝의 가장 기본이 되는 핵심 알고리즘중에
경사 감소법이 있는데 이 경사 감소법을 이해하려면 수렴과 발산을 알아야 한다
수열 : 수의 나열
1, 3, 5, 7, 9
퍼셉트론 네모와 세모를 구분해 내요!
ㅁㅣ분이 필요해요!
미리 공부해 놓아요
- sql이란
- sql을 배워야하는거이유
- select문 6가지값
- 단일행 함수 : 문자,숫자,날짜,변환,일반
복수행 함수 : max, min, avg, sum, count
- 조인 - 1. 1999 ansi 조인 문법
2. 오라클 조인
- 서브쿼리 1. single row subqeury
2. multiple row subquery
3. multiple column subquery
- 집합연산자 4가지 : uninon all , union, minus , intersect
-
레포팅 함
- rollup
- grouping sets
- grouping
- cube
-
DML 문장
- INSERT
- UPDATE
- DELETE
- MERGE
-
DML 문장
- DDL 문장 : CREATE, ALTER, DROP, TRUNCATE, RENAME
- 제약 : PRIMARY KEY, UNIQUE, CHECK , NOT NULL, FOREIGN KEY
-
DB OBJECT 5가지
- TABLE
- VIEW
- INDEX --------------> 데이터 검색 속도를 높이는 DB오브젝트
- SEQUENCE
- SYNONYM
■ 인덱스 (INDEX) 구조
- 컬럼값 + ROWID (ROW의 구조)
- 컬럼값이 ASCENDING 하게 정렬이 되어있다.
- ROWID( ROW의 주소)
FILE번호 + BLOCK번호 + ROW번호
SELECT ROWID, EMPNO, ENAME, SAL
FROM EMP;
SELECT * FROM EMP;
감춰져 있는 컬럼
문제360. 사원테이블에 월급에 인덱스가 있는지 조회하시오!
SELECT INDEX_NAME
FROM USER_INDEXES
WHERE TABLE_NAME='EMP';
- 어느 컬럼에 인덱스가 있는지 확인 하고자 한다면
SELET TABLE_NAME, INDEX_NAME, COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME='EMP';
문제361. emp_sal 인덱스의 구조를 출력하시오!
컬럼값 + rowid
select u.column_name , e.rowid
from user_ind_columns u, emp e
where u.rowid = e.rowid;
컬
select sal, rowid
from emp
where sal >= 0;
emp_sal 인덱스 emp 테이블
문제362. 월급이 1600인 사원의 이름과 월급을 출력하시오
( 어떻게 인덱스를 통해서 테이블의 데이터를 찾는지 그림으로 설명하시오 )
select ename , sal
from emp
where sal = 1600;
문제363. 그럼 아래의 SQL의 실행계획이 FULL TABLE SCAN을 했는지 INDEX RAGE SCAN
SELECT /*+ INDEX(EMP EMP_SAL) */ENAME, SAL
FROM EMP
WHERE SAL =1600;
문제364. 81/02/23 에 입사한 사원의 이름과 입사일을 출력하는데
실행계획이 어떻게 되는지 확인하고 인덱스 스캔이 될 수 있도록
조치하시오 !
SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE = TO_DATE('1981/02/23','RRRR/MM/DD');
문제365. 연봉이 36000인 사원의 이름과 연봉(SAL * 12) 을 출력하시오!
==> 힌트써도 풀 테이블 스캔함( TABLE ACCESS FULL )
튜닝전:
SELECT /*+ INDEX(SAL, EMP_SAL) */ ENAME, SAL* 12
FROM EMP
WHERE SAL * 12 = 36000;
↑
좌변
좌변(인덱스 컬럼) 을 가공 하면 인덱스 엑세스가 안 된다.
튜닝후 :
SELECT /*+ INDEX(EMP, EMP_SAL) */ ENAME, SAL * 12
FROM EMP
WHERE SAL = 36000 / 12;
설명: 인덱스 컬럼을 가공하게 되면 인덱스 엑세스가 안된다!
검색 속도를 높이려면 인덱스 컬럼을 가공하는 SQL을 작성하면 안된다.!!
문제366. 아래의 SQL을 튜닝하시오
CREATE INDEX EMP_JOB
ON EMP(JOB)
튜닝전 :
SELECT ENAME, JOB
FROM EMP
WHERE SUBSTR(JOB,1,5) = 'SALES';
튜닝후 :
SELECT /*+ INDEX(EMP, EMP_JOB) */ ENAME, JOB
FROM EMP
WHERE JOB LIKE 'SALES%';
설명: substr 을 걷어내고 like 연산자를 사용하는데
와일드 카드(%) 를 뒤쪽에 작성해야 한다.
문제367. 아래의 SQL을 튜닝하시오!
튜닝전 :
select ename hiredate
from emp
where to_char(hiredate, 'RR/MM/DD') = '81/02/23';
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|Operation | Object Name | Rows | Bytes | Cost | Object Node | In/Out | PStart | PStop | Access Predicates | Filter Predicates |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT Optimizer Mode=ALL_ROWS | | 1 | 14 | 2 | | | | | | |
| TABLE ACCESS FULL |SCOTT.EMP | 1 | 14 | 2 | | | | | | TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),'RR/MM/DD')='81/02/23' |
튜닝후 :
select ename hiredate
from emp
where hiredate = to_date('81/02/23','RR/MM/DD');
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|Operation | Object Name | Rows | Bytes | Cost | Object Node | In/Out | PStart | PStop | Access Predicates | Filter Predicates |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT Optimizer Mode=ALL_ROWS | | 1 | 14 | 2 | | | | | | |
| TABLE ACCESS BY INDEX ROWID |SCOTT.EMP | 1 | 14 | 2 | | | | | | |
| INDEX RANGE SCAN |SCOTT.EMP_HIREDATE | 1 | | 1 | | | | | "HIREDATE"=TO_DATE('81/02/23','RR/MM/DD') | |
문제368. 아래의 데이터를 입력하고 어제 입사한 사원의 이름과 입사일을 출력하시오
insert into emp ( empno , ename, sal , hiredate)
values (1256, 'JACK', 3500, sysdate-1);
튜닝전:
select ename, hiredate
from emp
where to_char(hiredate,'RR/MM/DD') = '17/03/12';
튜닝후
select ename, hiredate
from emp
where hiredate = to_date('17/03/12','RR/MM/DD');
select ename, hiredate
from emp
where hiredate between to_date('17/03/12 00:00:00','RR/MM/DD HH24:MI:SS') and
to_date('17/03/12 23:59:59','RR/MM/DD HH24:MI:SS');
select ename, hiredate
from emp
where hiredate between to_date('17/03/12 00:00:00','RR/MM/DD HH24:MI:SS') and
to_date('17/03/12 23:59:59','RR/MM/DD HH24:MI:SS');
select ename, hiredate
from emp
where hiredate >= to_date('17/03/12','RR/MM/DD') and
hiredate < to_date('17/03/12','RR/MM/DD') + 1
;
문제369. (점심시간문제)
아래의SQL을 order by 절 쓰지말고 인덱스를 통해서
데이터가 정려로디도록 다시 재작성하시오
튜닝전 :
select ename, sal, job
from emp
where job = 'SALESMAN'
ORDER BY SAL DESC;
튜닝후
select ename, sal, job
from emp
where SAL > 0 AND job = 'SALESMAN'
■ 인덱스 삭제
drop index emp_sal;
select index_name
from user_indexes
where table_name = 'EMP';
문제370. emp 테이블에 걸린 모든 인덱스를 전부 삭제하시오!
select ' drop index ' || index_name ||';'
from user_indexes
where table_name='EMP';
drop index EMP_JOB; drop index EMP_HIREDATE;
문제371.EMP, EMP2, SALGRADE, DEPT 테이블을 제외한 다른 테이블들을 전부 드랍하시오
SELECT 'DROP TABLE '||TABLE_NAME || ';'
FROM USER_TABLES
WHERE TABLE_NAME NOT IN ('EMP','EMP2','SALGRADE','DEPT');
- 11장 목차
- VIEW
- INDEX
- SEQUENCE
- SYNONYM
■ SEQUENCE ~> 번호를 생성하는 데이터 베이스 오브젝트
- 시퀀스 (SEQUENCE) 가 왜 필요한가?
" 특정 테이블에 번호를 순서대로 입력하기 위해서 필요하다"
예 : 특정과목에 수강신청을 하는데 30명 정원인 경우
테이블에 30명만 입력되게 하고 번호가 순서대로
수강신청번호가 생성되게 입력하고 싶을때 필요해
- 시퀀스 생성
CREATE SEQUECE SEQ1
START WITH 1 ----시작숫자
INCREMENT BY 1 ----증가치
MAXVALUE 100 ----최대숫자
MINVALUE -100 ----최소숫자 (순환시 사이클이 끝까지 가면 다시 -100)
NOCYCLE ---- 순환여부
CACHE 20 ; --- 시퀀스의 번호를 빨리 사용하기 위해 번호 20개를 미리 생성해서 메모리에 올려놓음
- 시퀀스를 사용하는 방법
SELECT SEQ1.NEXTVAL
FROM DUAL;
- 시퀀스를 삭제하는 방법
DROP SEQUENCE SEQ1;
문제372. 아래의 테이블을 생성하고 아래의 테이블에 번호를 100개를 입력하시오
(1번 ~ 100번)
CREATE TABLE EMP506
(EMPNO NUMBER(10));
INSERT INTO EMP506 VALUES (SEQ1.NEXTVAL); ----------------- 100번 반복
문제373. 아래와 같이 시퀀스를 생성하면 시퀀스의 각 파라미터들에 대해서
default가 어떻게 되는지 확인 하시오
create sequence seq373;
select *
from user_sequences;
■ 시퀀스 수정하는 방법
select *
from user_sequences;
create sequence seq5
start with 1
inCrement by 1
maxvalue 200
nocyCle;
select *
from user_sequences
where sequence_name= 'SEQ5';
- 시퀀스 seq5의 maxvalue를 1000으로 변경하겠다.
alter sequence seq5
maxvalue 1000;
문제374. 시퀀스 seq5의 increment by를 2로 변경하고
cache 200으로 변경하고 순환여부를 cycle로 바꾸시오
alter sequence seq5
increment by 2
cache 200
cycle;
※ start with 옵션은 alter sequence 명령어로 변경 못한다.(p11-32)
문제375. 지금까지 만들었던 시퀀스를 모두 삭제하시오 !
select sequene_name from user_sequences;
- 데이터 사전
- 내가 만들었던 테이블들을 확인하는 방법: user_tables
- 내가 만들었던 view들을 확인 하는 방법: user_views
- 내가 만들었던 index들을 확인하는 방법 : user_indexes
- 내가 만들었던 sequene들을 확인하는 방법 : user_sequences
- 11장 목차
- VIEW
- INDEX
- SEQUENCE
- SYNONYM -----------> 동의어
- 유저생성과 권한 부여
create user king
identified by tiger;
grant connect to king;
설명 : 패스워드를 tiger로 하는 king 유저를 생성하고 king 유저에게
접속할 수 있는 권한을 부여 함
문제376. scott유저에서 ctas를 이용해서 emp 테이블을 모두 쿼리한
결과를 테이블로 생성하시오 ( 테이블명 : emp500 )
create user king
identified by tiger;
grant connect to king;
create table emp500
as select * from EMP;
select * from emp500;
grant select on emp500 to king;
commit;
select * from emp500;
king 유저에서 emp500을 select 해보시오~~
select * from scott.EMP500;
SCOTT.EMP500에 대한 SYNONYM을 생성하시오 !
CREATE PUBLIC SYNONYM EMP
FOR SCOTT.EMP500;
문제377. SCOTT 의 DEPT 테이블을 SELECT 할 수 있는 권한을 KING에게 부여하고 KING 유저가 아래와 같이
DEPT 테이블을 SELECT 할 수 있도록 하시오
grant select on DEPT to king;
CREATE PUBLIC SYNONYM DEPT
FOR SCOTT.DEPT;
commit;
문제 378. SCOTT이 만들었던 모든 SYNONYM 을 삭제하시오
DROP PUBLIC SYNONYM DEPTOP- DROP PUBLIC SYNONYM EMP500 ;
select synonym_name
from user_synonyms;
drop public synonym
- NCS 시험 제출
- 쪽지시험 ( 오픈 북, 실습, 시간제한 )
- SQL 포트폴리오
■ 1장. 유저 엑세스 제어
- 1장 목차
- 유저생성
- 패스워드 변경하는 방법
- 시스템 권한
- 객체 권한
- 롤 사용법
SQL> alter user scott
2 account unlock;
사용자가 변경되었습니다.
SQL> alter user scott
2 identified by tiger;
사용자가 변경되었습니다.
SQL>
'SQL 기본' 카테고리의 다른 글
| 엔터티란(Entity) (0) | 2022.10.06 |
|---|---|
| 10장 DDL문을 사용해서 테이블을 생성 및 관리 (0) | 2017.09.07 |
| 9장 데이터 조작 언어 (DML문) (0) | 2017.09.07 |
| 8장 집합 연산자 사용 (0) | 2017.09.07 |
| 7장 Subquery 사용 (0) | 2017.09.07 |
10장 DDL문을 사용해서 테이블을 생성 및 관리
SQL 기본2017. 9. 7. 02:02
■ 10장. DDL문을 사용해서 테이블을 생성 및 관리
- 10장 목차
- 데이터 베이스 오브젝트 5가지
- 테이블 생성 방법
- 데이터 타입
- 제약조건
- 서브쿼리를 사용해서 테이블 생성하는 방법
- 테이블 삭제
10.1 데이터베이스 오브젝트 5가지
- TABLE -----> 데이터를 저장하는 기본 저장 단위이고 행과 열로 구성
- VIEW -----> 테이블을 바라보는 쿼리가 뷰이다
- SEQUENCE ------> 일련번호 생성기 ( 번호를 생성하는 DB 오브젝트)
- SYNONYM ------> 테이블의 또 다른 이름
- INDEX ------> 검색 속도를 향상시키기 위한 DB 오브젝트
10.2 테이블 생성 방법
CREATE TABLE EMP500
( EMPNO NUMBER(10),
ENAME VARCHR2(10),
SAL NUMBER(10),
HIREDATE DATE );
문제292. emp500 테이브렝 데이터를 2건 정도 입력하시오!
INSERT INTO EMP500 (EMPNO, ENAME, SAL, HIREDATE)
VALUES (2934,'임은총',4864,TO_DATE(SYSDATE,'RRRR-MM-DD'));
INSERT INTO EMP500 (EMPNO, ENAME, SAL, HIREDATE)
VALUES (2234,'김범수',4864,TO_DATE('1999-12-13','RRRR-MM-DD'));
INSERT INTO EMP500 (EMPNO, ENAME, SAL, HIREDATE)
VALUES (2921,'김서희',4800,TO_DATE('1989-11-21','RRRR-MM-DD'));
문제293. 여러분들의 데이터를 입력할 수 있도록 테이블을 생성하시오.
테이블명 : emp293
CREATE TABLE EMP293
(
EMPNO NUMBER(10),
ENAME VARCHAR2(10),
SAL NUMBER(10),
birth date,
major varchar2(10),
address varchar2(30)
);
사원번호
사원이름
월급
생일
전공
주소
- 테이블명이나 컬럼명을 지을 때 주의할 사항(p10-5)
- 문자로 시작해야한다.
- 30자 이내여야 한다.
- 알파벳 대문자, 소문자 , 숫자 ,특수문자(_, #, $) 를 쓸 수 있다.
- 기존에 존재하는 똑같은 이름의 테이블명을 쓸 수 없다.
- 오라클 예약어를 사용할 수 없다.
예 : create table select ( empno...
- 테이블 생성시 default값 사용하는 방법 (p 10-9)
create table emp506
( ename varchar2(20),
sal number(10) default 2500 );
insert into emp506(ename)
values('jack');
문제294. 아래의 테이브릉ㄹ 생성하고 default도 지정하시오!
테이블명 : emp294
컬럼 : empno
ename
sal
hiredate ------------------------> default 값 : 오늘날짜
job ------------------------> default 값 : SALESMAN
ALTER SESSION SET NLS_DATE_FORMAT='RR/MM/DD';
CREATE TABLE emp295
(
EMPNO NUMBER(10),
ENAME VARCHAR2(20),
SAL NUMBER(10),
HIREDATE DATE DEFAULT TO_CHAR(SYSDATE,'RR/MM/DD'),
JOB VARCHAR2(10) DEFAULT 'SALESMAN'
);
CREATE TABLE emp295
(
EMPNO NUMBER(10),
ENAME VARCHAR2(20),
SAL NUMBER(10),
HIREDATE DATE DEFAULT TRUNC(SYSDATE), --------------> 시분초 짤림!
JOB VARCHAR2(10) DEFAULT 'SALESMAN'
);
- 내가 만든 테이블 리스트를 확인하는 방법.
SELECT * FROM USER_TABLES;
- 테이블 생성 일자
SELECT object_name, created, last_ddl_time
from user_objects;
문제295. created와 last_ddl_time 서로 다른 테이블이 무엇인지 조회하시오!
SELECT object_name, created, last_ddl_time
from user_objects
where created != Last_ddl_time;
문제296. tic-tae-toe 학습데이터를 입력하기 위한 데이블을 생성하시오
create table ttt
(
t1 number(1),
t2 number(1),
t3 number(1),
t4 number(1),
t5 number(1),
t6 number(1),
t7 number(1),
t8 number(1),
t9 number(1),
result varchar(10)
);
문제297. 승리기보에서 2가 나온 횟수 각 칸별로
select sum(decode(t1,1,1,0)) t1,
sum(decode(t2,1,1,0)) t2,
sum(decode(t3,1,1,0)) t3,
sum(decode(t4,1,1,0)) t4,
sum(decode(t5,1,1,0)) t5,
sum(decode(t6,1,1,0)) t6,
sum(decode(t7,1,1,0)) t7,
sum(decode(t8,1,1,0)) t8,
sum(decode(t9,1,1,0)) t9
from ttt
where result ='positive'
-
데이터 타입 (유형)
-
데이터 타입 (유형)
문제298. 영국 옥스포드에서 영여의 거의 모든문장을 컴퓨터에 집어
넣고 통계를 내보았더니 대략 100단어 정도가 영어 문장의 거의
50%를 차지하는 것을 확인햇다고 합니다.
내가 아는 단어들을 지금 대충 세어보다도 100개는 충분히 넘는데
왜나는 영어문장을 50%도 이해 못하나 라고 질문을 하실 수도 있는데
여기에 또 다른 비밀이 숨어있습니다.
그 100단어는 대부분 전치사 입니다. (책: 애로우 잉글리쉬)
10.3 데이터 타입 (유형) (p 10-12)
- 문자형 : char, varchar2, long, clob, blob
(2000) (4000) (2G) (4G) (4G)
- 숫자형 : number
- 날짜형 : date
내가 만듬 테이블
select table_name
from user_tables
where table_name like 'WINTER%';
문제299. 아래의 데이터를 입력하게끔 테이블을 생성하고 데이터를 입력하시오
create table tab
(
lyric varchar2(4000)
);
■ char 와 varchar2의 차이점
char vs varchar2
고정형 가변형
char(10) varchar(10)
M I R A C L E M I R A C L E
■■■■■■□□□ ■■■■■■(□□□) --> 남은 공간 회수
남은 공간 공백을 그대로 유지 저장공간을 절약한다.
MIRACLE123이라고 업데이트하면 MIRACLE123이라고 업데이트하면
블락안에서 이동하지 않지만 블락이 이동해요 -> 성능이 느려짐
CREATE TABLE EMP700
(ENAME VARCHAR2(10),
DEPTNO VARCHAR2(10) );
CREATE TABEL DEPT700
( DEPTNO CHAR(10),
LOC CHAR(10) );
INSERT INTO EMP700 VALUES('SCOTT','10');
INSERT INTO EMP700 VALUES('SMITH','20');
INSERT INTO DEPT700 VALUES('10','DALLAS');
INSERT INTO DEPT700 VALUES('20','CHICAGO');
COMMIT;
문제300. 아래와 같이 조인을 하면 결과가 출력될 것인가???
SELECT E.ENAME, D.LOC
FROM EMP700 E, DEPT700 D
WHERE E.DEPTNO = D.DEPTNO;
VARCHAR2(2) CHAR(10)
결과가 안 나온다 우리 VARCHAR 쓰자!!!!!!!!!!!!!!!
문제301.(오늘의 마지막 문제) 겨울왕국에서 가장 많이 나오는
단어와 그 건수를 출력하는 SQL을 분석하고
내가 임의로 영화 스크립트를 구글에서 내려 받아서
테이블에 입력하고 그 중에 가장 많이 나오는 단어가
무엇인지
select aaa1,count(*) a
from (
select regexp_substr(lower(w_text), '[^ ]+',1,1) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,2) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,3) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,4) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,5) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,6) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,7) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,8) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,9) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,10) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,11) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,12) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,13) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,14) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,15) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,16) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,17) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,18) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,19) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,20) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,21) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,22) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,23) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,24) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,25) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,26) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,27) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,28) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,29) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,30) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,31) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,32) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,33) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,34) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,35) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,36) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,37) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,38) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,39) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,40) aaa1 from winter7
)
where aaa1 is not null
group by aaa1
order by a desc;
테이블을 따로 만들려면
create table winter
as
(
select regexp_substr(lower(w_text), '[^ ]+',1,1) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,2) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,3) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,4) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,5) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,6) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,7) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,8) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,9) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,10) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,11) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,12) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,13) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,14) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,15) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,16) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,17) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,18) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,19) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,20) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,21) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,22) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,23) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,24) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,25) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,26) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,27) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,28) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,29) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,30) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,31) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,32) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,33) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,34) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,35) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,36) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,37) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,38) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,39) aaa1 from winter7
union all
select regexp_substr(lower(w_text), '[^ ]+',1,40) aaa1 from winter7
)
■ 어제 배운 내용 복습
- DML 문장 ---> MERGE 문
DML 문 ---> INSERT,UPDATE,DELETE,MERGE,SELECT
MERGE 문 ----> INSERT, UPDATE, DELETE 를 한번에 수행하는 명령어
- DDL 문장 ----> CREATE, ALTER, DROP, TRUNCATE, RENAME
↓
테이블 생성 명령어
앞으로의 목표 파이썬을 이용한 머신러닝 프로그램 개발(틱테토)
↓
SQL, Python, 하둡
↓
Hive SQL
■ 10장. DDL문을 사용해서 테이블을 생성 및 관리
- 10장 목차
- 데이터 베이스 오브젝트 5가지
- 테이블 생성 방법
- 데이터 타입
- 제약조건
- 서브쿼리를 사용해서 테이블 생성하는 방법
- 테이블 삭제
10.4. 제약조건
- 제약( constraints) 이란?
" 데이터 품질을 높이기 위한 데이터베이스 오브젝트 "
데이터 정재 작업
중복돼서 데이터가 입력된다거나 필수항목에 null을 입력한다거나
이메일을 입력할 때 @를 반드시 넣어야하는데 아무렇게나 입력했거나 하는 데이터들을
애초부터 입력되지 않게 해야한다.
- 제약(constraints) 의 종류 ?
- primary key ---------> 중복된 data와 null 값을 입력 못하게
- unique ---------> 중복된 data를 입력 못하게
- not null ---------> null 값이 입력되지 않게
- check ---------> 지정된 data 외에는 다른 data는 입력 되지 않게
- foreign key ----------> 참조하는 컬럼에 거는 제약
- primary ket 제약을 이용해서 테이블 생성
create table emp40
( empno number(10) primary key,
ename varchar2(10) );
insert into emp40 values( 1111, 'aaa');
insert into emp40 values( 1111, 'bbb');
insert into emp40 values(null, 'ddd');
문제305. 아래의 테이블을 생성하는데 ename 에 중복된 데이터가
입력되지 않도록 제약을 걸어서 생성하시오
(중복된 데이터만 입력 안되고 null은 입력되게 하시오)
테이블명: emp305
컬럼명: empno
ename
sal
create table emp305
( empno number(10),
ename varchar(10) unique,
sal number(10) );
- 제약을 삭제하는 명령어
alter table emp305
drop constraint SYS_C007037;
(제약이름)
(제약이름)
제약 삭제 하니 잘 들어감
- 제약 이름 확인하는 방법
select table_name,
constraint_name,
constraint_type
from user_constraints <---------------- 데이터 사전
where table_name='EMP40';
↑
반드시 대문자로 입력해야한다.
문제306. emp40 테이블에 걸린 primary 제약을 삭제하시오.
alter table emp40
drop constraint SYS_C007036;
- 제약이름을 주면서 제약을 거는 방법
create table emp45
( empno number(10) constraint emp45_empno_pk primary key,
ename varchar2(10) ); ↑
제약이름
테이블명_컬럼명_제약타입
select table_name, constraint_name
from user_onstraints
where table_name= 'EMP45';
문제307. emp45 테이블에 empno에 걸린 primary key를 삭제하시오 !
select table_name, constraint_name
from user_constraints
where table_name= 'EMP45';
alter table emp45
drop constraints emp45_empno_pk;
문제308. 아래의 테이블을 unique 제약과 함께 생성하시오
create table emp308
( empno number(10),
ename varchar(10) emp308_ename_unique unique,
sal number(10) );
문제309. 위에 308 테이블에 ename에 null은 중복입력이 될까?
된다
- check 제약 ---> 지정된 data 외에는 다른 데이터는 입력 안되게 거는 제약
CREATE TABLE DEPT400
( DEPTNO NUMBER(10),
LOC VARCHAR2(20) CONSTRAINT DEPT400_LOC_CK
CHECK(LOC IN ('DALLAS','CHICAGO','BOSTON') ) );
문제310. 아래의 테이블을 생성하는데 CHECK제약을 걸어서 생성하시오
테이블명 : emp311
컬럼명 : EMPNO
ENAME
SAL <------- 월급을 0 ~ 9000 사이의 값만 입력되게
CREATE TABLE EMP311
( EMPNO NUMBER(10),
ENAME VARCHAR2(10),
SAL NUMBER(10) CONSTRAINT EMP311_SAL_CK
CHECK(SAL BETWEEN 0 AND 9000) );
문제311. 아래의 테이블을 생성하는데 이름에 성이 김씨, 이씨 최씨만
입력되게끔 CHECK제약을 걸어서 생성하시오
CREATE TABLE EMP311
(
EMPNO NUMBER(10),
ENAME VARCHAR2(10)
CONSTRAINTS EMP312_ENAME_CK
CHECK(ENAME LIKE IN ('김%', '이%', '최%') );
문제312. 이름, 월급, 이메일 컬럼을 담는 테이블을 생성하는데
이메일에 @ 가 반드시 포함되어야 이메일이 입력이 될 수 잇또록 CHECK 제약조건을 생성하시오
테이블명 : EMP313
컬럼명 : ENAME
SAL
EMAIL
CREATE TABLE EMP313
(
ENAME VARCHAR2(10),
SAL NUMBER(10),
EMAIL VARCHAR2(20) CONSTRAINTS EMP312_ENAME_CK
CHECK(EMAIL LIKE '%@%'));
문제313. 위의 문제를 다시 수행하는데 이번에는 이메일에 @와 . 이 포함되어야지만 이메일이 입력 될 수 있도록
테이블을 생성하시오
CREATE TABLE EMP313
(
ENAME VARCHAR2(10),
SAL NUMBER(10),
EMAIL VARCHAR2(20) CONSTRAINTS EMP312_ENAME_CK CHECK(EMAIL LIKE '%@%.%'));
문제314.(점심시간문제) 아래의 테이블을 생성하는데
전공이 컴퓨터 , 통계 학과는 입력되지 못하게 하시오!
테이블명 : EMP315
컬럼명 : ENAME
MAJOR <-------- 컴퓨터, 통계 학과외에 다른 학과만 입력되게 하시오
CREATE TABLE EMP315
(
ENAME VARCHAR2(10),
MAJOR VARCHAR2(20) CONSTRAINTS EMP315_MAJOR_CK
CHECK(EMAIL NOT LIKE '컴퓨터%' or EMAIL NOT LIKE '통계%'));
- 제약(constraints) 의 종류 ?
- primary key ---------> 중복된 data와 null 값을 입력 못하게
- unique ---------> 중복된 data를 입력 못하게
- not null ---------> null 값이 입력되지 않게
- check ---------> 지정된 data 외에는 다른 data는 입력 되지 않게
- foreign key ----------> 참조하는 컬럼에 거는 제약
- foreign key 제약
- dept 테이블에 deptno에 primary key 제약을 거는 방법
(이미 만들어진 테이블에 제약을 거는 방법)
alter table dept
add
constraint dept_deptno_pl primary key(deptno);
- emp 테이블에deptno 에 foreign key 제약을 걸면서
dept 테이블에 deptno를 참조해라~
alter table emp
ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY(DEPTNO)
REFERENCES DEPT(DEPTNO);
EMP(자식) ------------------------------------------------------dept(부모)
insert into emp(empno, ename, sal, deptno)
values (1232,'scott',3400,30);
insert into emp(empno, ename, sal, deptno)
values (1235,'jones',3500,70);
- 위와 같이 부모-자식관계를 설정하면
- emp 테이블에 70번과 같은 부서테이블에 속하지 않은 부서번호를 입력하지 못하게 된다.
- dept 테이블에 10,20,30,번을 삭제할 수 도 없게 된다.
결론적으로 데이터의 품질이 좋아지고
outer join을 남발하지 않아도 된다.
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno (+) ;
======================> 이렇게 조인하러가여!
크은 테이블 > 작은 테이블 조인하면 느려져여
부모자식관계를 걸어서 데이터 깨끗하게 !
문제315. dept테이블에서 10 번을 삭제해보세요
delete from emp
where deptno = 10;
안데여!
문제316. dept 테이블에서 40번을 삭제하면 삭제되겠는가?
delete from emp
where deptno = 40;
돼여 ! 자식이 없어여!
emp 테이블에 deptno가 40번인 사원이 없어서 삭제가 된다.
문제317. 자식 테이블(emp)에 걸린 foreign key 제약을 삭제 하세요
alter table emp
drop CONSTRAINT EMP_DEPTNO_FK;
문제318. 부모 테이블(dept) 에 걸린 primary key 제약을 삭제하시오
alter table dept
drop constraint dept_deptno_pk;
■ 이미 생성된 테이브레 제약을 거는 방법
문제319. dept 테이블에 deptno 에 primary key 제약을 거시오 !
alter table dept
add constraint dept_deptno_pk primary key(deptno);
문제320. 우리반 테이블에 empno에 primary key 제약을 거세요
alter table emp2
add constraint emp2_empno_pk primary key(empno);
문제321. 우리반 테이블에 ename에 unique 제약을 거세요
alter table emp2
add constraint emp2_ename_un unique(empno);
문제322. emp 테이블에 월급에 check 제약을 거는데
월급이 0 ~ 9500 사이의 데이터만 입력 또는 수정되게 제약을 거시오
alter table emp
add constraint emp_sal_ck check(sal between 0 and 9500);
문제323. 사원 테이블의 KING의 월급을 9600으로 변경하시오
UPDATE EMP
SET SAL = 9600
WHERE ENAME = 'KING';
ORA-02290: check constraint (SCOTT.EMP_SAL_CK) violated
----------------------------------------------------
UPDATE EMP
SET SAL = 9600
WHERE ENAME = 'KING'
- 제약을 중지시키는 방법
ALTER TABLE EMP
DISABLE CONSTRAINT EMP_SAL_CK;
문제324. 기존에 들어잇던 데이터 중에 제약에 위반된 데이터가
있다 하더라도 무시하고 EMP_SAL_CK제약을 다시 활성화 시키시오
ALTER TABLE EMP
ENABLE NOVALIDATE CONSTRAINT EMP_SAL_CK;
-> 기존에 들어있떤 데이터들에 대해서는 제약검사 안하고
앞으로 들어올 데이터들에 대해서만 제약검사 하겠다.
- 제약을 활성화 시키는 방법
ALTER TABLE EMP
ENABLE CONSTRAINT EMP_SAL_CK;
문제325. 우리반 테이블에 EMAIL 컬럼에 CHECK 제약을 거는데
@ 와 . 이 들어가야 데이터가 입력 될 수 있도록 CHECK제약을 거시오
ALTER TABEL EMP2
ADD CONSTRAINT EMP2_EMAIL_EMP2 CHECK ( EMAIL LIKE '%@%.%');
10.5 서브쿼리를 사용해서 테이블 생성하는 방법
CREATE TABLE EMP_BACKUP
AS
SELECT *
FROM EMP;
SELECT * FROM EMP_BACKUP;
문제326. 부서번호, 부서번호별 토탈월급을 출력하는 쿼리의 결과를 테이블로 생성하시오 !
(테이블명 : emp326)
CREATE TABLE EMP326
AS
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO ;
문제327. 부서번호, 이름, 월급, 순위를 출력하는데
순위가 부서번호 별로 각각 월급이 높은 순서대로 순위를 부여하시오.
SELECT DEPTNO, ENAME, SAL, RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RNK
FROM EMP;
문제328. 위의 결과에서 순위가 1등만 나오게 하시오
SELECT *
FROM (SELECT DEPTNO, ENAME, SAL, RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RNK
FROM EMP)
WHERE RNK =1;
문제329. 아래의 쿼리의 결과를 테이블로 생성하시오 !
(테이블명 : emp 329)
CTAS :Create Table As Subquery의 줄임!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
create table emp329
as
SELECT DEPTNO, ENAME, SAL, RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RNK
FROM EMP
;
- 오라클에 데이터를 삭제하는 명령어가 3가지가 있어요
DELETE |
TRUNCATE |
DROP |
|
DATA |
삭제 |
삭제 |
삭제 |
저장영역 |
유지 |
삭제 |
삭제 |
저장구조 |
유지 |
유지 |
삭제 |
ROLLBACK |
가능 |
불가능 |
불가능 |
TRUNCATE와 DROP은 순식간에 ~
INSERT INTO EMP
SELECT * FROM EMP_BACKUP;
■ 테이블을 읽기만 가능하도록 만드는 방법
ALTER TABLE EMP READ ONLY;
문제330. 다시 EMP 테이블을 읽기 쓰기가 가능하도록 변경하시오 !
ALTER TABLE EMP READ WRITE;
'SQL 기본' 카테고리의 다른 글
| 엔터티란(Entity) (0) | 2022.10.06 |
|---|---|
| 11장 . 기타 스키마 객체 (0) | 2017.09.07 |
| 9장 데이터 조작 언어 (DML문) (0) | 2017.09.07 |
| 8장 집합 연산자 사용 (0) | 2017.09.07 |
| 7장 Subquery 사용 (0) | 2017.09.07 |
9장 데이터 조작 언어 (DML문)
SQL 기본2017. 9. 7. 02:02
■ 9장. 데이터 조작 언어 (DML문)
SQL 문의 종류
- QUERY 문 -> SELECT 문의 6가지 절, 조인, 서브쿼리, 함수
- DML 문 (Data Manipulation Language)
insert, update, delete, merge
- DDL 문 (Data Definition Language)
create, alter , drop , truncate, rename
- TCL 문 (Transaction Control Language)
commit, rollback, savepoint
- DLC 문 (Data Control Language)
grant, revoke
- 9장 목차
- insert
- update
- delete
- merge
- commit 과 rollback
- 읽기 일관성과 lock
- select .. for update 문
9.1 insert 문
insert into emp( empno, ename, sal)
values (2914,'JACK',4000);
문제260. 아래의 DATA를 입력하시오 !
사원번호 3282
사원이름 JANE
월급 4500
입사일 오늘날짜
부서번호 20
SELECT ENAME, HIREDATE
FROM EMP
WHERE HIREDATE = SYSDATE;
설명: 위에서 입력한 시분초와 현재의 시분초가 틀려서 데이터가 검색이 안되는것이다.
문제262. 위의 DATA를 ROLLBACK 하고 오늘 날짜를 다시 입력하는데
시분초는 입력안되고 오늘 날짜만 입력되게 하시오!
insert into emp(empno, ename, sal, hiredate, deptno)
values (3282, 'JANE', 4500, TO_DATE('2017/03/07','RRRR/MM/DD'), 20);
문제263. 오늘 입사한 사원의 이름과 입사일을 출력하시오
select * from EMP
wheRE HIREDATE= to_DATE('2017/03/07', 'RRRR/MM/DD');
- NULL 을 입력하는 방법
- 명시적으로 입력하는 방법
- NULL
- ''
- 암시적으로 입력하는 방법
INSERT INTO EMP(EMPNO, ENAME, SAL)
VALUES (2911, NULL, 3400);
INSERT INTO EMP(EMPNO, ENAME, SAL)
VALUES (2945, '', 3500);
INSERT INTO EMP(EMPNO, ENAME, SAL)
VALUES (3924, ' ', 3500);
문제264. 이름이 NULL 이 아닌 사원들의 이름과 월급을 출력하는데 공백문자도 안나오게 하시오
SELECT * FROM EMP
WHERE TRIM(ENAME) IS NOT NULL;
6.2 UPDATE 문
UPDATE EMP
SET SAL = 8000
WHERE ENAME = 'SCOTT';
문제265. 직업이 SALESMAN 인 사원들의 커미션을 5000으로 변경하시오
UPDATE EMP
SET COMM = 5000
WHERE JOB = 'SALESMAN';
문제266. 월급이 3000이상인 사원들의 부서번호를 30번으로 변경하시오
UPDATE EMP
SET DEPTNO = 30
WHERE SAL >= 3000;
■ 9.3 delete 명령어
delete from emp
where ename='SCOTT';
문제267. 커미션이 null 인 사원들을 삭제 하시오
delete from emp
where comm is null;
■ commit 문
- 명시적 commit :
commit 명령어를 직접 수행
- 암시적 commit :
- 정상 종료
- DDL 명령어 수행 (CREATE TABLE)
- DCL 명령어 수행
but 비정상 종료 == rollback
■ 서브쿼리를 사용한 DML 문
- DELETE 문
문제268. SCOTT보다 많은 월급을 받는 사원들을 지우시오
DELETE FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME= 'SCOTT');
문제269. ALLEN보다 늦게 입사한 사원들을 삭제 하시오!
DELETE FROM EMP
WHERE HIREDATE > (SELECT HIREDATE
FROM EMP
WHERE ENAME = 'ALLEN');
문제270. JONES 보다 많은 월급을 받는 사원들의 직업을 SALESMAN으로 변경하시오
UPDATE EMP
SET JOB='SALESMAN'
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME='JONES');
문제271. JONES 보다 많은 월급을 받는 사원들의 직업을 KING의 직업으로 변경하시오
UPDATE EMP
SET JOB= (SELECT JOB
FROM EMP
WHERE ENAME='KING');
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME='JONES');
- INSERT 문
CREATE TABLE EMP20
AS
SELECT *
FROM EMP2
WHERE 1=2;
====> 조건이 FALSE 이기때문에 DATA는 못 가지고 오고 구조만 가져온다.
문제272. EMP2 테이블의 DATA를 EMP20테이블에 입력하시오
INSERT INTO EMP20
SELECT *
FROM EMP2;
문제273. 아까 만들었던 DBLINK를 이용해서 짝꿍의 EMP TABLE을 내 DB에 EMP7으로 생성하시오
create public database link dblink7
connect to scott
identified by tiger
using '192.168.19.26:1521/xe';
CREATE TABLE EMP7
AS SELECT * FROM EMP WHERE 1=2;
INSERT INTO EMP7
SELECT *
FROM EMP@DBLINK7;
(오늘의 마지막 문제2) 아래의 결과를 출력하시오
select deptno ,decode(grouping(job)+grouping(deptno),1,'부서토탈:',2,'전체토탈:',job) job,sum(sal)
from emp
group by rollup(deptno,job);
■어제 배웠던 내용
- 집합 연산자
- union all, union , intersect, minus
- 레포팅 함수
ROLLUP , GROUPING SETS , GROUPING , CUBE
- DML 문장
INSERT,UPDATE,DELETE, MERGE
- SUBQUERY를 사용한 DML문
DELETE,UPDATE, INSERT
문제274. 부서번호가 30번인 사원들의 커미션을
WARD의 커미션으로 변경하시오
UPDATE EMP
SET COMM = (SELECT COMM
FROM EMP
WHERE ENAME = 'WARD')
WHERE DEPTNO = 30;
문제275. EMP테이블에 SAL2라는 컬럼을 추가하시오.
ALTER TABLE EMP
ADD SAL2 NUMBER(10);
문제276. emp 테이블의 월급으로 SAL2의 값을 갱신하시오
UPDATE EMP
SET SAL2 = SAL;
문제277. EMP테이블에 LOC컬럼을 추가하시오.
ALTER TABLE EMP
ADD LOC VARCHAR2(10);
문제278. EMP 테이블에 추가한 LOC컬럼의 데이터를 해당사원의
부서위치로 값을 갱신하시오 (MERGE문을 사용해야한다.)
- MERGE 문: INSERT, UPDATE, DELETE를 한번에 해버리겠다!
MERGE INTO EMP E
USING DEPT D
ON (D.DEPTNO = D.DEPTNO)
WHEN MATCHED THEN
UPDATE SET E.LOC = D.LOC;
속도가 아주 빨라요.
UPDATE EMP E
SET LOC = (SELECT LOC
FROM DETP D
WHERE E.DEPTNO = D.DEPTNO);
문제279. 이름과 부서위치와 부서번호를 출력하시오.
SELECT E.ENAME, D.DEPTNO, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
문제280. 사원테이블에 DNAME 컬럼을 추가하고 DEPT 테이블에 DNAME 컬럼 데이터로
값을 갱신하시오 (MERGE사용)
ALTER TABLE EMP
ADD DNAME VARCHAR2(10);
MERGE INTO EMP E
USING DEPT D
ON (D.DEPTNO = E.DEPTNO)
WHEN MATCHED THEN
UPDATE SET E.DNAME = D.DNAME;
ALTER TABLE EMP
DROP COLUMN LOC;
ALTER TABLE EMP
DROP COLUMN DNAME;
ALTER TABLE EMP
ADD GRADE NUMBER(10);
문제282. 사원이름, 월급, 급여등급(GRADE)을 출력하시오 .
SELECT E.ENAME,E.SAL,S.GRADE
FROM EMP E, SALGRADE S
WHRER E.SAL BETWEEN S.LOSAL AND S.HISAL;
문제283. 방금 EMP 테이블에 추가한 GRADE 컬럼의 데이터를 해당 사원의 등급으로 값을 갱신하시오.
MERGE INTO EMP E
USING SALGRADE S
ON ( E.SAL BETWEEN LOSAL AND HISAL)
WHEN MATCHED THEN
UPDATE SET E.GRADE = S.GRADE;
문제284. 다시EMP 테이블에 GRADE컬럼을 DROP 하시오
ALTER TABLE EMP
DROP COLUMN GRADE;
문제285. 부서번호, 부서번호별 토탈 월급을 출력하시오
SELECT DEPTNO , SUM(SAL)
FROM EMP
GROUP BY DEPTNO
문제286. 부서테이블에 SUMSAL이라는 컬럼을 추가하시오
ALTER TABLE DEPT
ADD SUMSAL NUMBER(10);
문제287. 지금 추가한 SUMSAL 컬럼에 값을 갱신하는데 해당 부서번호의 토탈 월급으로 값을
갱신하시오
MERGE INTO DEPT D
USING (SELECT DEPTNO , SUM(SAL) E
FROM EMP
GROUP BY DEPTNO) E
ON ( E.DEPTNO = D.DEPTNO)
WHEN MATCHED THEN
UPDATE SET D.SUMSAL = E.E;
GROUP 함수는 컬럼 별칭을 써야한다.
설명: 1. USING 절에 테이블 명을 쓰지 않고 서브 쿼리를 사용한다.
2. SUM(SAL)과 같은 그룹 함수를 USING 절 안에 사용했으면 컬럼 별칭을 사용해야한다.
문제288. 아래의 테이블을 생성하고 AVGAGE 컬럼의 값을 해당 통신사의
평균나이로 값을 생신하시오
CREATE TABLE TELECOM_AVG
( TELECOM VARCHAR2(10),
AVGAGE NUMBER(10) ) ;
INSERT INTO TELECOM_AVG(TELECOM)
SELECT DISTINCT TELECOM
FROM EMP2;
MERGE INTO TELECOM_AVG A
USING (SELECT TELECOM, AVG(AGE) 평균나이
FROM EMP2
GROUP BY TELECOM) T
ON ( T.TELECOM = A.TELECOM)
WHEN MATCHED THEN
UPDATE SET A.AVGAGE= T.평균나이;
- 9장 목차
- insert
- update
- delete
- merge
- commit 과 rollback
- 읽기 일관성과 lock
- select .. for update 문
9.6 읽기 일관성과 lock
- 읽기 일관성이란?
: 내가 접속한 세션에서 내가 변경한 데이터는 나만이 볼 수 있고
다른 세션에서는 볼 수 없다. 내가 커밋을 해야 다른 세션에서도
내가 변경한 데이터를 볼 수 있다.
커밋해줘야 다른 세션에서도 변경된 게 보이지!
- LOCK?
: 내가 변경한 데이터를 누군가가 변경하려고 하면 그 누군가는
LOCK에 걸린다 (LOCK을 waiting 하는 상황이 발생한다)
LOCK은 ROW(행) 단위로 걸린다.
문제289. 직업이 SALESMAN인 사원들의 커미션을8000으로 수정하시오
UPDATE EMP
SET COMM = 8000
WHERE JOB = 'SALESMAN';
문제290. 위와 같은 상태에서 다른 DOS창에서 이름이 TURNER 인 사원의 월급을 8000으로 수정하면
수정이 되겠는가? 앙데여!
문제291.(점심시간문제)
개발 (나) , 운영(짝꿍) 인 상태에서
나 짝꿍
UPDATE EMP@DBLINK7
SET SAL = 0
WHERE ENAME='JONES'; UPDATE EMP
SET SAL = 8000
WHERE ENAME='JONES';
9.7. SELECT .. FOR UPDATE 문
"LOCK은 SELECT 할 때는 LOCK을 걸지 않는다 SELECT 할 때 LOCK을 거는 문장이 SELECT FOR UPDATE
==> 5초 동안 LOCK을 건다.
문제291. 책 9-46쪽을 보고 직업이 SALESMAN인 사원들의 이름과 월급을 조회하는 쿼리문을 작성하는데
10초 동안 조회하는 행에 락을 걸게 쿼리를 작성하시오
SELECT ENAME, SAL
FROM EMP
WHERE JOB = 'SALESMAN'
FOR UPDATE 10;
'SQL 기본' 카테고리의 다른 글
| 11장 . 기타 스키마 객체 (0) | 2017.09.07 |
|---|---|
| 10장 DDL문을 사용해서 테이블을 생성 및 관리 (0) | 2017.09.07 |
| 8장 집합 연산자 사용 (0) | 2017.09.07 |
| 7장 Subquery 사용 (0) | 2017.09.07 |
| 6장 조인 문법 (0) | 2017.09.07 |
8장 집합 연산자 사용
SQL 기본2017. 9. 7. 02:01
■어제까지배운내용복습
- SQL이란 무엇인가?
- SQL을 왜 배워야 하는거ㅏFULL OUTER JOIN
DATA를 다루는 기본언어를 잘 숙달하기 우해서
"머신 러닝과정에서 SQL이 왜 필요한가?"
머신러닝 알고리즘(수학 알고리즘)을 구현 가능하게
한게 빅데이터이다.
하둡이 나오면서 빅데이터를 저장하고 빅데이터를 분석할 수 있게 되었다.
빅데이터를 검색하려면 예전에는 JAVA를 잘 알야했다.
NoSQL이 java를 몰라도 빅데이터를 검색하고 분석할 수 있게해줍니다.
하둡 -> 자바 -> NoSQL(HIVE, pig...)
SQL ---> R --->python --->shell 스크립트 ---> java
SQL --> PLSQL --> PYTHON --> 리눅스 쉘 스크립트 --> 하둡 --> 텐서 플로우
- 기본 SQL의 6가지 절
select
from
where
group by
having
order by
- 함수
-
단일행 함수
- 문자 , 숫자, 날짜 , 변환 , 일반
-
단일행 함수
- 복수행 함수
- max , min , avg , sum , count
함수를 왜 알아야 하는가?
페이스 북 데이터를 스크롤링해서 하둡에 저장한 다음에
NoSQL을 날려서 이 키워드가 어느시간대에 가장 많이 글로 올라왔는지를 분석
페이스북에 올라온 글 vs emp table
빅데이터(비정형화 된 데이터 ) small data
- 조인
- 오라클 조인 문법
- equi join
- non equi join
- outer join
- self join
- 1999 ansi 문법
■ 분석함수의 종류
- listagg
- rank
- dense_rank
- ntile
- 누적 데이터 출력하는 분석함수
문제189. 사원번호, 이름, 월급, 월급의 누적치를 출력하시오
select empno, ename, sal, sum(sal) over ( order by empno asc) 누적치
from emp;
문제190. 부서번호, 이름, 월급, 월급의 누적치를 출력하는데
월급의 누적치가 부서번호 별로 각각 월급이 누적되게 하시오
select empno, ename, sal, sum(sal) over (partition by deptno order by empno asc) 누적치
from emp;
문제191. 부서번호, 이름, 월급, 월급의 누적치를 출력하는데
월급의 누적치가 부서위치 별로 각각 월급이 누적되게 하시오
select d.loc, e.empno, ename, sal,
sum(sal) over (partition BY d.loc order by sal asc) 누적치
from EMP e ,DEPT d
WHERE e.deptno = d.DEPTNO AND sal IS NOT NULL ;
문제192. 이름, 부서위치를 출력하는 outer join을 사용해서
사원 이름쪽에는 데이터가 나오는데 부서위치쪽에는 데이터가 안나오는 결과를 출력하시오
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno (+) ;
문제193. 위의 결과를 다시 출력하는데 이름도 모자란것으로 보고싶고
부서위치도 모자란것으로 보이게 결과를 출력하시오
- 오라클 조인 문법
- equi join
- non equi join
- outer join
- self join
- 1999 ansi (American National Standard Institute)조인 문법
full outer join (10g 버전부터 가능)
select e.ename, d.loc
from emp e full outer join dept d
on ( e.deptno = d.deptno );
문제 194.학생번호 이름, 나이, 나이의 누적치를 출력해보시오
SELECT empno, ename, age, SUM(age) OVER (ORDER BY empno asc)
FROM EMP2;
문제 195 . 통신사 , 이름 , 나이 , 나이의 누적치를 출력하는데 통신사 별로 각각
나이의 누적치가 출력되게 하시오
SELECT telecom, empno, ename, age, SUM(age) OVER (PARTITION BY telecom ORDER BY empno ) 누적치
FROM EMP2;
문제196. (점심시간문제)통신사, 통신사별로 해당 하는 사원들의 학생들의 출력하는데
sk 김명학(31), 길용현(30), 김경찬(28).........
lg 김진희(33)
kt ............
SELECT telecom, empno, ename, age, SUM(age) OVER (PARTITION BY telecom ORDER BY empno ) 누적치
FROM EMP2;
SELECT telecom, listagg(ename||'('||age||')', ',') within GROUP (ORDER BY age DESC) 이름
FROM EMP2
GROUP BY telecom
ORDER BY telecom desc;
문제197. 아래의 오라클 조인 문법을 1999ansi 문법으로 변경하시오
--오라클 조인 문법:
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno (+);
-- 1999 ansi 문법
select e.ename, d.loc
from emp e right outer join dept d
on (e.deptno = d.deptno);
select e.ename, d.loc
from emp e right outer join dept d
on (e.deptno = d.deptno)
-- self join
mgr 컬럼 : 자기 직속상사의 사원번호
7902는 SMITH 사원에게는 MGR이지만
FORD라는 관리자에게는 EMPNO이다.
문제 198. 사원이름, 관리자인 사원(직속상사)의 이름을 출력하시오
SELECT 사원.ENAME, 관리자.ENAME MRG
FROM EMP 사원, EMP 관리자
WHERE 사원.MGR = 관리자.EMPNO;
문제199. 사원이름, 사원월급, 관리자의 이름, 관리자의 월급을 출력하는데
관리자보다 더 많은 월급을 받는 사원들이 출력되게하시오!
문제200. 아래와 같이 결과를 출력하시오
SELECT 관리자.ENAME 관리자 , LISTAGG(사원.ENAME,',') WITHIN GROUP (ORDER BY 사원.ENAME) 사원
FROM EMP 사원, EMP 관리자
WHERE 사원.MGR = 관리자.EMPNO
GROUP BY 관리자.ENAME;
문제 201 겨울왕국 대본을 단어별로 쪼개서 결과를 출력하시오
SELECT regexp_substr(lower_substr(lower(win_text), '[!^ ]') aaa
from winter3;
문제 202. 겨울오아국 대본의 모든 단어를 테이블에 입력하시오
create table winter_kingdom
(win_text varchar2(100) );
■3개 이상의 테이블 조인
문제203. 사원이름, 부서위치, 월급, 급여등급(grade) 을 출력하시오
dept ------------------------emp -------------------------salgrade
select e.ename, d.loc, e.sal, s.grade
FROM EMP e, DEPT d, SALGRADE s
WHERE E.DEPtno = d.DEPTNO AND e.sal BETWEEN s.LOSAL AND s.HISAL;
문제204 bonus_table만드시오
CREATE TABLE bonus_table
AS
SELECT empno, sal * 1.5 AS bonus
FROM EMP;
문제 205. DALLAS 에서 근무하는 사원들의
사원이름, 월급, 보너스, 부서위치를 출력하시오!
DEPT-------------------------EMP------------------- BONUS_TABLE
SELECT E.ENAME, E.SAL, B.BONUS, D.LOC
FROM EMP E, DEPT D, BONUS_TABLE B
WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = B.EMPNO AND D.LOC = 'DALLAS';
■조인 문법 2가지
- 오라클 조인 문법
- EQUI JOIN
- NON EQUI
- OUTER
- SELF JOIN
- 1999 ANSI 문법
- FULL OUTER JOIN
- LEFT/RIGHT OUTER JOIN
- ON 절을 사용한 조인
SELECT E.ENAME, D.LOC
FROM EMP E JOIN DEPT D
ON ( E.DEPTNO = D.DEPTNO);
문제206.위의 결과를 다시 출력하는데 월급이 3000 이상인 사원들만
출력하시오!
SELECT E.ENAME, D.LOC
FROM EMP E JOIN DEPT D
ON ( E.DEPTNO = D.DEPTNO) --조인조건
WHERE E.SAL > =3000; -- 검색조건
문제207. 이름, 월급, 부서위치, 급여등급(GRADE)을 출력하는데
ON절을 사용한 조인 문법으로 수행하시오
select e.ename, d.loc, s.grade
FROM EMP e JOIN DEPT d JOIN SALGRADE s
ON (E.DEPtno = d.DEPTNO )
WHERE e.sal BETWEEN s.LOSAL AND s.HISAL;
===> 이건 안뎀!
문제208. 겨울왕국 대본의 단어, 단어별 건수를
출력하시오!
SELECT WIN_TEXT , COUNT(*)
FROM WINTER_KINGDOM
GROUP BY WIN_TEXT
;
문제 209. 위결과를 내림차순 건수로 출력하시오
SELECT WIN_TEXT , COUNT(*)
FROM WINTER_KINGDOM
GROUP BY WIN_TEXT
ORDER BY COUNT(*) DESC;
문제210. 위 결과를 다시 출력하는데 단어, 선수, 순위를 출력하시오 (건수에대한순위)
SELECT WIN_TEXT , COUNT(*) ,RANK() OVER ( ORDER BY COUNT(*) DESC) 순위
FROM WINTER_KINGDOM
GROUP BY WIN_TEXT
;
■ 조인문법
- 오라클 조인 문법
- 1999 ANSI 문법
- full outer join
- left/right outer join
- on 절을 사용한 join
- using 절을 사용한 join
- natural join
- cross join
Using
select e.ename, d.loc
from emp e join DEPT d
using (deptno);
Natural (지가 알아서!)
select e.ename, d.loc
from emp e natural join dept d;
Cross (그냥 다 함)
select e.ename, d.loc
from emp e cross join dept d;
이것과 같음
select e.ename, d.loc
from emp e , dept d;
문제211. 부서위치, 부서위치별 토탈월급을 가로로 출력하시오!
(pivot문으로)
select *
from (select d.loc, e.sal
from emp e, dept d
WHERE D.DEPtNO = E.DEPTNO )
pivot ( sum(sal) for loc in ('DALLAS' as "DALLAS",
'CHICAGO' as "CHICAGO",
'NEW YORK' as "NEW YORK",
'BOSTON' as "BOSTON"));
문제212. (오늘의 마지막 문제)
영화 겨울왕국에 나오는 단어중에 가장 긴 단어는 무엇인지 출력하시오
- SQL이란 무엇인가.
데이터를 입력 , 조작, 검색할 때 사용하는 언어
- SELECT 문의 6가지 절
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
- 함수
- 단일행 함수
- 복수행함수
- 조인
- 오라클 조인 문법
- EQUI JOIN
- NON EQUI JOIN
- OUTER JOIN
- SELF JOIN
- 1999 ANSI 문법
- FULL OUTER JOIN
- DATA 분석함수
- LISTAGG
- NTILE
- RANK
4. DENSE_RANK
- 누적데이터
- SQL을 왜 배워야하는지?
파이썬과 R을 활용한 머신러닝 전문가 과정
↓
머신이 스스로 (데이터) 학습하게끔 만드는 기능
↓
빅데이터 (원유)
인공지능 기술 연구의 선두기업들 : GOOGLE, FACEBOOK, 바이두 등
2013년에 구글은 인공지능 연구를 위해서 딥러닝의 1인자인
토론토 대학 제프리 힌트 교수를 영입.
2014년에 영국의 딥마인드 테크놀로지스사를 매수했다.
사원수가 고작 12명인 회사인데 페이스북과 경쟁하여 4억달러(4200억원)에 인수해서 세상을 놀라게 했다.
딥러닝 기술로 구현 하고자 하는 여러 사업들중 대표적인 것이
- 이미지 학습 : 이미지를 인식하게 하고 학습시키는 방법
( 구글에서 이제 고양이 사진을 인식했다.)
- 음식사진을 계속 학습 시키는 프로그램 개발
(100억 ~ 150억) 영국회사 + 우리나라회사(텐서플로우 이미지학습) + 서강대 (웹스크롤링)
파이썬 ( 프로그래밍 스킬 ) --> R (머신러닝 알고리즘)
수학
- 미분
- 선형대수학
--> 텐서플로우
- 서브 쿼리 단원 목차
- Single Row Subquery
- multiple row subquery
- 서브쿼리에서의 null 값
- exists 문
■ 7.1 Single Row Subquery
"JONES 보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하시오 "
SELECT E.ENAME, E.SAL
FROM (SELECT I.SAL FROM EMP I WHERE ENAME='JONES') A ,EMP E
WHERE E.SAL >= A.SAL;
월급이 2975보다 더 큰 사원들의 이름과 월급을 출력하라
SELECT ENAME, SAL
FROM EMP
WHERE SAL > 2975;
SELECT ENAME, SAL ---------------------- 메인 쿼리
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP ------------------서브 쿼리
WHERE ENAME ='JONES') ;
문제213.SCOTT과 같은 월급을 갖는 사원들의 이름과 월급을 출력하시오
ELECT ENAME, SAL ---------------------- 메인 쿼리
FROM EMP
WHERE SAL = ( SELECT SAL
FROM EMP ------------------서브 쿼리
WHERE ENAME ='SCOTT') ;
문제213.SCOTT과 같은 월급을 갖는 사원들의 이름과 월급을 출력하시오
ELECT ENAME, SAL ---------------------- 메인 쿼리
FROM EMP
WHERE SAL = ( SELECT SAL
FROM EMP ------------------서브 쿼리
WHERE ENAME ='SCOTT') ;
문제214. SMITH 보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하는데 월급이 높은 사원부터 출력하시ㅗㅇ
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME = 'SMITH')
ORDER BY SAL DESC;
문제215. ALLEN보다 늦게 입사한 사원들의 이름과 입사일을 출력하시오
SELECT ENAME, SAL
FROM EMP
WHERE HIREDATE > (SELECT HIREDATE
FROM EMP
WHERE ENAME = 'ALLEN');
문제216. 직업이 SALESMAN 인 사원들중에서의 최대 월급돠 더 큰 월급을 받는 사원들의
이름과 월급을 출려하시오
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT MAX(SAL)
FROM EMP
WHERE JOB ='SALESMAN');
문제217. 최대 월급을 받는 사원의 이름과 월급을 출력하시오!
SELECT ENAME, SAL
FROM EMP
WHERE SAL = (SELECT MAX(SAL)
FROM EMP
);
문제218. 등록금이 가장 비싼 대학교를 출력하라
select university, tuition
from univ
where tuition = (select max(tuition)
from univ);
문제219. 그 동안 내가 만들었던 테이블 리스트를 조회 하시오.
SELECT * FROM USER_TABLES;
select * from crime_cause2;
문제220. 살인의 가장 큰 원인이 무엇인지 알아내시오 기타를 제외하고
select term
from crime_cause2
where ( select max(
select * from user_tables;
문제221. 가정불화로 인해 생기는 가장 큰 범죄가 무엇인지 ?
select crime_type
from crime_cause2
where cnt = (select max(cnt)
from crime_cause2
where term ='가정불화')
and term = '가정불화';
문제222. 방화사건의 가장 큰 원인이 무엇인지 출력하시오 ?
select term
from crime_cause2
where cnt = (select max(cnt)
from crime_cause2
where crime_type = '방화')
and crime_type = '방화';
7.2 multiple row subquery
문제223. 직업이 SALESMAN 인 사원들과 월급이 같은 사원들의 이름과 월급을 출력하시오
SELECT ENAME, SAL
FROM EMP
WHERE SAL = ( SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
SELECT ENAME, SAL
FROM EMP
WHERE SAL IN ( SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
설명 : 서브쿼리의 종류 3가지
- single row subquery : 서브쿼리에서 메인쿼리로 하나의 로우가 리턴
연산자 : = ., >, < , >= , <= , !=, <> ,^=
- multiple row subquery : 서브쿼리에서 메인쿼리로 여러개의 로우가 리턴
연산자 : in, not in, >all, <all, >any, <any
- multiple column subquery : 서브쿼리에서 메인쿼리로 여러개의 컬럼값이 리턴되는 경우
연산자 : 위의 연산자들을 용도에 따라 사용
문제224. 월급이 1000에서 3000사이인 사원들과 같은 부서 번호에서 근무하는 사원들의
이름과 월급과 직업을 출력하시오
select ename, sal , job
from emp
where deptno in (select deptno
from emp
where sal between 1000 and 3000);
문제225. KING에게 보고하는 사원들의 이름을 출력하시오
(KING의 직속 부하사원들을 출력하시
오 )
SELECT ENAME
FROM EMP
WHERE MGR = (SELECT EMPNO
FROM EMP
WHERE ENAME = 'KING');
문제226. 관리자 인 사원들의 이름을 뽑으시오
SELECT ENAME
FROM EMP
WHERE EMPNO IN (SELECT MGR
FROM EMP);
문제227.
SELECT ENAME
FROM EMP
WHERE EMPNO noT IN (SELECT MGR
FROM EMP);
ANY
7.3 서브쿼리에서 NULL 값처리
※ 서브쿼리에서 NOT IN 사용시 주의할 점!
SELECT ENAME
FROM EMP
WHERE EMPNO IN (SELECT NVM(MGR,0)
FROM EMP);
SELECT ENAME
FROM EMP
WHERE EMPNO IN (7369, 7698,...);
=ANY (7369 OR 7698 OR NULL)
T OR T OR NULL
T/F == T
SELECT ENAME
FROM EMP
WHERE EMPNO NOT IN (7369, 7698,...);
!=ALL (7369 OR 7698 OR NULL)
T AND T AND T AND NULL
T/F == NULL
SELECT ENAME
FROM EMP
WHERE EMPNO != ALL(SELECT NVL(MGR,0)
FROM EMP);
혹은
SELECT ENAME
FROM EMP
WHERE EMPNO NOT IN (SELECT MGR
FROM EMP
WHERE MGR IS NOT NULL);
※ 서브 쿼리에서 NOT IN을 사용 할 때는 반드시 NULL 처리를 해줘야 한다.
/
문제228. 서울시 물가데이터에서 가장 비싼물건의 이름과 가격을 출력하시오
SELECT A_NAME, A_PRICE
FROM PRICE
WHERE A_PRICE = (SELECT MAX(A_PRICE)
FROM PRICE );
7.2 MULTIPLE ROW SUBQUERY의 연산자 ( P 7.17쪽)
IN, NOT IN , >ALL, <ALL, >ANY, <ANY
문제229. 직업이 SALESMAN 인 사원들에 최대 월급보다
더 많은 월급을 받는 사원들의 이름과 월급을 출력 하시오
select ENAME, SAL
FROM EMP
WHERE SAL > ( SELECT MAX(SAL)
FROM EMP
WHERE JOB = 'SALESMAN');
select ENAME, SAL
FROM EMP
WHERE SAL >ALL ( SELECT MAX(SAL)
FROM EMP
WHERE JOB = 'SALESMAN');
※오라클이 내부적으로 SQL을 다시 조인문으로 변경해서 수행 조인 방법중에서
SORT MERGE 조인을 수행함
select ENAME, SAL
FROM EMP
WHERE SAL >ALL ( SELECT MAX(SAL)
FROM EMP
WHERE JOB = 'SALESMAN');
>ALL (1250, 1600, 1500, 1250);
>ALL 은 가장 큰 값보다 크다 1600 보다 큰 값
문제230 직업이 SALESMANE 인 사원들중에서 가장 작은 월급을 받는
사원보다 더 큰 월급을 받는 사원들의 이름과 월급을 출력하시오
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ( SELECT MIN(SAL)
FROM EMP
WHERE JOB = 'SALESMAN');
SELECT ENAME, SAL
FROM EMP
WHERE SAL >ANY ( SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
>ANY( 1250, 1600, 1500, 1250)
설명 : 그 어느값보다 크기만 하면 된다.
7.4 EXISTS 문!
문제231. 방금 배웠던 IN을 이용해서 문제를 푸는데
부서 테이블에서 부서위치를 출력하는데 사원 테이블에 존재하는
부서번호에 해당하는 부서 위치를 출력하시오 !
SELECT D.LOC
FROM EMP E, DEPT D
WHERE D.DEPTNO IN E.DEPTNO
SELECT LOC
FROM DEPT
WHERE DEPTNO IN ( SELECT DEPTNO
FROM EMP );
문제232. 위의 결과를 다시출력하는데 사원 테이블에
존재하지 않는 부서번호에 해당하는 부서위치를 출력하시오
SELECT LOC
FROM DEPT
WHERE DEPTNO nOT IN ( SELECT DEPTNO
FROM EMP );
설명: 만약에 EMP 테이블이 2억건이 있었는데 2억건을 모두 조회하여 검색된 결과가 3건이면
비효율적이다.
SQL을 달리 작성하라 exists
"exists 문을 이용하면 된다. "
문제233. 아래의 SQL을 exists 문으로 변경하시오
SELECT LOC
FROM DEPT
WHERE DEPTNO nOT IN ( SELECT DEPTNO
FROM EMP );
↓
select loc
from dept d
where exists ( select 'X'
from emp e
where e.deptno = d.deptno );
설명 : exists 문은 메인 쿼리의 데이터가 서브쿼리에서 존재하면 찾는걸 멈추기 때문에 메인 쿼리의
데이터가ㅓ 많은 경우에 좋은 성능을 보인다.
문제234. 위의 SQL을 다시 수행하는데 이번에는 부서 테이블에는 존재하는데
사원 테이블에는 존재하지 않는 부서번호에 대한 부서위치를 출력하시오
select loc
from dept d
where not exists ( select 'X'
from emp e
where e.deptno = d.deptno );
■8장 집합연산자
- 8장 목사
- union all
- union
- intersect
- minus
delete from emp
where hiredate is null;
8.1 union all
"합집합 연산자로 중복을 허용하면서 집합을 합치는 연산자"
select job, sum(sal)
from emp
group by job;
문제235. 아래의 결과를 출력하시오!
select '전체토탈:' ,sum(sal)
from emp
문제236. 아래의 결과를 출력하시오 !
select job, sum(sal)
from emp
group by job
union all
select '전체토탈: ', sum(sal)
from emp;
※ 집합 연산자 사용시 주의사항
- 집합 연산자 위 아래의 컬럼의 갯수가 동일해야한다
- 집합 연산자 위 아래의 컬럼의 데이터 타입이 동일해야한다.
- 컬럼 별칭을 사용해야 데이터 정렬할 때 편하다.
문제237. 아래의 결괄르 출력하시오
select to_char(deptno) , sum(sal)
from emp
group by deptno
union all
select '전체토탈:' , sum(sal)
from emp
;
문제238. 아래와 같이 결과를 출력하시오 !
ORDER BY 절은 맨 밑에만 쓸 수 있고 컬럼명이 같아야(별명을 같게 지정) 한다. !
select to_char(deptno) deptno , sum(sal)
from emp
group by deptno
union all
select '전체토탈:' deptno , sum(sal)
from emp
order by deptno asc
;
문제239. 입사 연도별(4자리), 입사 연도별 토탈 월급 을 출력하는데 전체 토탈 월급을 출력하시오
SELECT TO_CHAR(HIREDATE,'RRRR'),SUM(SAL)
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'RRRR')
UNION ALL
SELECT '토탈월급:', SUM(SAL)
FROM EMP;
문제240. 통신사, 통신사별 인원수를 출력하는데 맨 아래쪽에
전체 인원수가 출력되게 하시오
SELECT TELECOM, COUNT(*)
FROM EMP2
GROUP BY TELECOM
UNION ALL
SELECT '전체인원 수:', COUNT(*)
FROM EMP2;
문제241. 부서위치, 부서위치별 인원수를 출력하는데
맨 아래쪽에 전체인원수도 출력하시오.
select d.loc, count(*)
from emp e, dept d
where e.deptno = d.deptno
group by d.loc
union all
select '전체 인원수:', count(*)
from EMP;
문제242. 직업, 직업별 토탈월급을 출력하는데
맨 밑에 전체 토탈월급을 출력하시오
select job as 직업 , sum(sal)
from emp
group by job
union all
select '토탈월급' 직업, sum(sal)
from emp
order by 직업 desc;
배운사람은 ! 이렇게
문제243. 아래의 결괄르 rollup으로 수행하시오
select decode(deptno,NULL,'전체토탈:',deptno) deptno,
to_char(sum(sal),'999,999') "SUM(SAL)"
from emp group by rollup(deptno);
■ 어제 배운 내용
- 서브쿼리
- single row subquery
연산자 : >, <, >=, <=, !=, <> , ^-
- multiple row subquery
연산자: in, not in, >all, <all, >any, <any
- multiple column subquery
- 집합 연산자
- 합집합 : union ( 중복제거) , union all (중복허용)
- 교집합 : intersect
- 차집합 : minus
SQL을 처음에 배워야 하는 이유?
회사가면 여러분들이 데이터 분석으로 가도 마찬가지고
많은 sql을 보게 될 것입니다 삼성 SQL
문제244. 텔레콤 , 텔레콤 별 평균 나이
select telecom, avg(age)
from emp2
group by rollup(telecom);
select telecom, round(avg(age))
from emp2
group by telecom
union all
select '전체평균나이:', round(avg(age))
from emp2;
문제245. 위 결괄르 rollup으로 수행하라 !
select nvl(telecom,'전체평균나이') as telecom , round(avg(age))
from emp2
group by rollup(telecom);
문제.246. 부서번호별, 직업별 토탈월급을 출력하시오!
select deptno, job, sum(sal)
from emp
group by rollup(deptno, job);
문제.247 위의 결과를 union all로 수행해보자
select deptno, job, sum(sal)
from EMP
group by deptno, job
union all
select deptno, null job, sum(sal)
from EMP
group by deptno
union all
select null deptno, null job, sum(sal)
from EMP
order by deptno, job;
select deptno, job, sum(sal) a
from emp
where deptno = 10
group by deptno,job
union all
select deptno, '' job, sum(sal) a
from EMP
where deptno = 10
group by deptno
union all
select deptno, job, sum(sal) a
from emp
where deptno = 20
group by deptno,job
union all
select deptno, '' job, sum(sal) a
from EMP
where deptno =20
group by deptno
union all
select deptno, job, sum(sal)
from emp
where deptno = 30
group by deptno,job
union all
select deptno, '' job, sum(sal) a
from EMP
where deptno =30
group by deptno
union all
select null deptno, null job, sum(sal) a
from EMP
order by deptno ,job, a;
SELECT TO_CHAR(deptno) deptno,job,SUM(sal) FROM EMP GROUP BY deptno,job
UNION ALL SELECT TO_CHAR(deptno) deptno,NVL(NULL,'부서별'),SUM(sal) FROM EMP GROUP BY deptno,NULL
UNION SELECT NVL(NULL,'전체 월급') deptno,NVL(NULL,'전체 월급'),SUM(sal) FROM EMP GROUP BY null,null;
■ rollup 의 결과 데이터 집합이 나오는 형태
select deptno, sum(sal)
from emp
group by rollup(deptno);
↓
rollup 괄호안에 컬럼의 갯수가 n개 이면
결과 집합의 갯수는 n + 1 개
- deptno 별 토탈월급
- 전체 토탈월급
select deptno, sum(sal)
from emp
group by rollup(deptno,job);
↓
- deptno, job
- deptno
- 전체
문제248. 아래의 SQL결과 집합이 무엇이 나오는지 마음 편안하게 예상하시오.
select mgr, deptno, job, sum(sal)
from emp
group by rollup(mgr, deptno, job);
결과 집합 4개
- mgr,deptno, job
- mgr, deptno,
- mgr
- 전체
문제249. 텔레콤, 전공별, 인원수를 출력하는데 아래의 세 가지
데이터 결과집합이 출력되게 하시오.
- 텔레콤, 전공별
- 텔레콤
- 전체
select telecom, major, count(*)
from emp2
group by rollup(telecom, major);
- 레포팅 함수 3가지
- rollup
- grouping sets
- cube
- rollup보다 사용하기가 편한 grouping sets 함수를 사용하는 방법
문제250. 문제 249번의 결과를 grouping sets로 수행하시오
select telecom, major , count(*)
from emp2
group by grouping sets ( ( telecom,major), (telecom), ());
↑
전체를 뜻함
문제251. 아래의 결과를 grouping sets로 수행하시오
select deptno, job, sum(sal)
from emp
group by grouping sets( (job), (deptno));
문제252. 전공별, 텔레콤, 주소별 인원수가 아래와 같이 출력되게 하시오 .
select major, telecom, substr(address,1,3), count(*)
from emp2
group by grouping sets( (major), (telecom), (substr(address,1,3)) );
문제253. 아래의 sql결과를 grouping sets로 수행하시오!
select detpno , sum(sal)
from emp
group by rollup(detpno);
select deptno , sum(sal)
from emp
group by grouping sets((deptno),());
문제254. (점심시간 문제) 아래의 결과를 출력하시오
select deptno ,decode(grouping(job)+grouping(deptno),1,'부서토탈:',2,'전체토탈:',job) job,sum(sal)
from emp
group by rollup(deptno,job);
■ 레포팅함수 4가지
- rollup
- grouping sets
- grouping
- cube
select deptno, sum(sal)
from emp
group by cube(deptno);
문제255. 위의 결과를 union all로 수행하시오
select null as deptno,sum(sal)
from emp
union all
select deptno, sum(sal)
from EMP
group by deptno
order by deptno nulls first;
문제256. 아래와 같이 결과를 출력하시오
select deptno, ename,sum(sal)
from EMP
group by grouping sets((deptno,ename),());
문제257. 아래의 결과를 출력하시오
select deptno, ename,sum(sal) 월급
from EMP
group by grouping sets((deptno,ename),(deptno),());
■ 집합 연산자
- 합집합 : union all, union
- 교집합: intersect
- 차집합 : minus
■ union
union all과 union의 차이는 ?
- 중복제거
- 데이터 정렬
select deptno, sum(sal)
from emp
group by deptno
select null as deptno, sum(sal)
funion all ------------------데이터 정렬이 안됨
rom emp;
select deptno, sum(sal)
from emp
group by deptno
union ---------------------데이터 정렬됨
select null as deptno, sum(sal)
from emp;
■ 2. 교집합 : intersect
select ename, major
from emp2
intersect
select ename, major
from emp3;
- 개발 서버에서 운영서버의 데이터를 읽을 수 있게
database link를 생성
운영서버 4가지 정보
- 운영서버의 ip 주소
- 운영서버의 sid 이름(xe)
- 운영서버의 port(1521)
C:\Users\stu>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 07-3월 -2017 14:22:47
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date 07-3월 -2017 08:50:34
Uptime 0 days 5 hr. 32 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\stu-PC\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stu-PC)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stu-PC)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully
아래의 작업을 개발 서버에서 한다
create public database link dblink7
connect to scott
identified by tiger
using '192.168.19.7:1521/xe';
select * from emp@dblink7; ->짝꿍의 emp table에 접속함
리스너!
운영서버 도스 창에서
C:\Users\stu> lsnrctl start
리스너를 열어준다!!!
문제258. 운영서버인 학새잉 자신의emp테이블을 변경한다
update emp
set sal = 0
where ename = 'BLAKE';
문제259. 개발서버에서 MINUS 를 사용한 쿼리를 수행하는데
운영서버의EMP 테이블과 개발서버의EMP테이들에
어떠한 데이터 차이가 있는지 조회하시오 !
SELECT *
FROM EMP
MINUS
SELECT *
FROM EMP@DBLINK7;
SELECT *
FROM EMP@DBLINK7
MINUS
SELECT *
FROM EMP;
'SQL 기본' 카테고리의 다른 글
| 10장 DDL문을 사용해서 테이블을 생성 및 관리 (0) | 2017.09.07 |
|---|---|
| 9장 데이터 조작 언어 (DML문) (0) | 2017.09.07 |
| 7장 Subquery 사용 (0) | 2017.09.07 |
| 6장 조인 문법 (0) | 2017.09.07 |
| 5장 복수행 함수 변환 함수 및 조건부 표현식 사용 (0) | 2017.09.07 |
7장 Subquery 사용
SQL 기본2017. 9. 7. 02:01
- SQL이란 무엇인가.
데이터를 입력 , 조작, 검색할 때 사용하는 언어
- SELECT 문의 6가지 절
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
- 함수
- 단일행 함수
- 복수행함수
- 조인
- 오라클 조인 문법
- EQUI JOIN
- NON EQUI JOIN
- OUTER JOIN
- SELF JOIN
- 1999 ANSI 문법
- FULL OUTER JOIN
- DATA 분석함수
- LISTAGG
- NTILE
- RANK
4. DENSE_RANK
- 누적데이터
- SQL을 왜 배워야하는지?
파이썬과 R을 활용한 머신러닝 전문가 과정
↓
머신이 스스로 (데이터) 학습하게끔 만드는 기능
↓
빅데이터 (원유)
인공지능 기술 연구의 선두기업들 : GOOGLE, FACEBOOK, 바이두 등
2013년에 구글은 인공지능 연구를 위해서 딥러닝의 1인자인
토론토 대학 제프리 힌트 교수를 영입.
2014년에 영국의 딥마인드 테크놀로지스사를 매수했다.
사원수가 고작 12명인 회사인데 페이스북과 경쟁하여 4억달러(4200억원)에 인수해서 세상을 놀라게 했다.
딥러닝 기술로 구현 하고자 하는 여러 사업들중 대표적인 것이
- 이미지 학습 : 이미지를 인식하게 하고 학습시키는 방법
( 구글에서 이제 고양이 사진을 인식했다.)
- 음식사진을 계속 학습 시키는 프로그램 개발
(100억 ~ 150억) 영국회사 + 우리나라회사(텐서플로우 이미지학습) + 서강대 (웹스크롤링)
파이썬 ( 프로그래밍 스킬 ) --> R (머신러닝 알고리즘)
수학
- 미분
- 선형대수학
--> 텐서플로우
- 서브 쿼리 단원 목차
- Single Row Subquery
- multiple row subquery
- 서브쿼리에서의 null 값
- exists 문
■ 7.1 Single Row Subquery
"JONES 보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하시오 "
SELECT E.ENAME, E.SAL
FROM (SELECT I.SAL FROM EMP I WHERE ENAME='JONES') A ,EMP E
WHERE E.SAL >= A.SAL;
월급이 2975보다 더 큰 사원들의 이름과 월급을 출력하라
SELECT ENAME, SAL
FROM EMP
WHERE SAL > 2975;
SELECT ENAME, SAL ---------------------- 메인 쿼리
FROM EMP
WHERE SAL > ( SELECT SAL
FROM EMP ------------------서브 쿼리
WHERE ENAME ='JONES') ;
문제213.SCOTT과 같은 월급을 갖는 사원들의 이름과 월급을 출력하시오
ELECT ENAME, SAL ---------------------- 메인 쿼리
FROM EMP
WHERE SAL = ( SELECT SAL
FROM EMP ------------------서브 쿼리
WHERE ENAME ='SCOTT') ;
'SQL 기본' 카테고리의 다른 글
| 9장 데이터 조작 언어 (DML문) (0) | 2017.09.07 |
|---|---|
| 8장 집합 연산자 사용 (0) | 2017.09.07 |
| 6장 조인 문법 (0) | 2017.09.07 |
| 5장 복수행 함수 변환 함수 및 조건부 표현식 사용 (0) | 2017.09.07 |
| 4장 DECODE와 PIVOT 변환 함수 및 조건부 표현식 사용 (0) | 2017.07.05 |
-
■6장 조인
- 조인 의 목차
- 오라클 조인 문법
- 1999 ansi 문법
- 6.1 오라클 조인 문법
-
■6장 조인
- 조인(join) ? 여러개의 테이블의 컬럼의 결과를 하나의결과값으로 출력할때 사용하는 SQL문법
select * from dept;
deptno : 부서번호
dname : 부서명
loc : 부서위치
문제 171. 이름, 부서위치를 출력하시오
select ename, loc
from emp, dept
where emp.deptno = dept.deptno;
-----------------------------
↑
조인 조건
문제172 위의 결과를 다시 출력하는데 부서위치가 DALLAS인 사원들만 출력하시오
select ename, loc
from emp, dept
where emp.deptno = dept.deptno and dept.loc = 'DALLAS';
----------------------------- ----------------------------
↑ ↑
조인 조건 검색조건
문제.173. 이름, 월급, 직업, 부서위치, 부서번호를 출력하시오!
select ename, sal, job, loc, deptno
from emp, dept
where emp.deptno = dept.deptno;
※ 조인문장 작성시 주의 사항
1. 컬럼명 앞에 테이블을 명시한다.
2. 테이블 별칭을 사용한다.
문제174. 월급이 3000 이상인 사원들의 이름과 월급과 부서위치를 출력하시오 !
select e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno = d.deptno and e.sal > = 3000;
------------------------------ ------------------------
↑ ↑
조인 조건 검색조건
문제175. 직업이 SALESMAN 인 사원들의 이름과 직업과 월급과 부서위치를
출력하시오
SELECT E.ENAME, E.JOB, E.SAL, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND E.JOB = 'SALESMAN';
문제176. 부서위치, 이름, 월급 , 순위를 출력하는데
순위가 월급이 높은 순서로 순위를 출력하시오
select d.loc, e.ename, e.sal, rank() over (order by sal desc nulls last)
from emp e, dept d
where e.deptno = d.deptno;
문제177. 부서위치, 이름, 월급 순위를 출력하느데
순위가 부서위치별로 각각 월급이 높은 순서로 순위를
출력하시오
select d.loc, e.ename, e.sal, rank() over (partition by d.loc order by sal desc nulls last)
from emp e, dept d
where e.deptno = d.deptno;
문제178. 부서위치, 부서위치별로 속한 사원들의 이름을 가로로 출력하시오
loc ename
new york smith,king,.....
dallas
chicago
SELECT d.loc, listagg(e.ename,',') within GROUP (ORDER BY e.ename) AS "ename"
FROM EMP e , DEPT d
WHERE e.deptno = d.deptno
GROUP BY d.loc;
문제179. 부서명, 부서명별로 속한 사원들의 이름이 아래와 같이 출력되게 하시오
dname ename
ACCOUNING
RESEARCH
SALES
SELECT D.DNAME, listagg(e.ename,',') within GROUP ( ORDER BY e.ename) AS "ename"
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno
GROUP BY d.DNAME;
- 6.1 오라클 조인 문법 4가지
- equi join ----> 조인하려는 테이블의 연결고리가 = 조인인 경우의 조인
- non equi join ---> 조인하려는 테이블의 연결고리가 = 조인조건이 아닌경우
- outer join ----> equi join 으로는 볼 수 없는 결과값을 볼때 사용하는 조인
- self join ---> 자기 자신의 테이블과 조인하는 테이블
문제 180. 부서위치, 부서위치별 토탈 월급을 출력하시오
세로로 출력
select d.loc, sum(e.sal)
from emp e, dept d
where e.deptno = d.deptno
group by d.loc;
문제181. 부서위치, 부서위치별 토탈월급을 출력하시오
select *
from emp e, dept d
WHERE e.deptno = d.deptno;
pivot ( sum(sal) for loc in ('NEW YORK', 'DALLAS', 'CHICAGO', 'BOSTON')
;
------> 안뎀 잘못쓴 쿼리문
pivot안에 e.sal 이렇게 쓰면 안뎀 칼럼명은 e.sal로 안나오잖오 !
select *
from (select d.loc, e.sal
from emp e, dept d
WHERE e.deptno = d.deptno)
pivot ( sum(sal) for loc in ('NEW YORK', 'DALLAS', 'CHICAGO', 'BOSTON')
;
문제182. 카페에서 salgrade 테이블 스크립트를 내려 받아 salgrade 테이블을 생성한다.
급여 등급 테이블
grade : 등급
losal : 등급안에서 제일 낮은 월급
hisal : 등급안에서 제일 높은 월급
문제 183. emp와 salgrade 테이블을 조인해서 이름, 월급, 급여등급을 출력하시오
문제184. 그럼 위의 결과 중에서 등급이 3등급인 사원들만 출력하시오
SELECT e.ename, e.sal, s.grade
FRoM EMP e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
AND s.grade =3;
※ 등급을 출력하는 SQL
- 코드성 테이블 (salgrade) 과 조인을 해서 출력
- Data 분석함수를 사용하는 경우
문제185번. 이름, 월급, 등급을 출력하는데 월급의 등급을 4등급으로 나눠 출력하시오
select ename, sal, ntile(4) over (order by sal desc) 등급
from emp;
문제186. 통신사, 이름, 나이, 등급을 출력하는데
통신사별로 각각 나이가 높은 순서대로 4등급으로 나눠서
등급을 출력하시오
select telecom, ename, age, ntile(4) over (partition by telecom order by age desc)
as "등급"
from emp2;
■ 오라클 조인 문법 4가지
- equi join
- non equi join
- outer join
문제187. 이름과 부서위치를 출력하는데 아래와 같이 조인이 안된 부서위치인
BOSTION도 출력하시오.
ENAME LOC
-------------------- --------------------------
KING NEW YORK
BLAKE CHICAGO
CLARK NEW YORK
JONES DALLAS
MARTIN CHICAGO
ALLEN CHICAGO
TURNER CHICAGO
JAMES CHICAGO
WARD CHICAGO
FORD DALLAS
SMITH DALLAS
: :
BOSTON
select e.ename, d.loc
from emp e, dept d
where e.deptno (+)= d.deptno ;
(+) 를 모자란쪽에 더해준다 라는 느낌으로
반대편인 경우에는
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno (+) ;
부족한게 없으니 바뀌지 않음
문제188(오늘의 마지막 문제)
직업별, 부서위치별 토탈월급을 출력하시오
(가로) (세로)
- sum 함수와 decode 이용:
select e.job, sum(decode(d.loc,'NEW YORK',e.sal,0)) as "NEW YORK",
sum(decode(d.loc,'DALLAS',e.sal,0)) as "DALLAS",
sum(decode(d.loc,'CHICAGO',e.sal,0)) as "CHICAGO",
sum(decode(d.loc,'BOSTON',e.sal,0)) as "BOSTON"
from emp e, dept d
where e.deptno = d.deptno
GROUP BY e.job;
- pivot 문 이용:
select *
from (select e.job, e.sal, d.loc
from emp e, dept d
where e.deptno = d.deptno)
pivot ( sum(sal) for loc in ('NEW YORK' AS "NEW YORK",
'DALLAS' AS "DALLAS",
'CHICAGO' AS "CHICAGO",
'BOSTON' AS "BOSTON");
'SQL 기본' 카테고리의 다른 글
| 8장 집합 연산자 사용 (0) | 2017.09.07 |
|---|---|
| 7장 Subquery 사용 (0) | 2017.09.07 |
| 5장 복수행 함수 변환 함수 및 조건부 표현식 사용 (0) | 2017.09.07 |
| 4장 DECODE와 PIVOT 변환 함수 및 조건부 표현식 사용 (0) | 2017.07.05 |
| 3장 단일 행 함수를 사용하여 출력 커스터마이즈 (0) | 2017.07.05 |
5장 복수행 함수 변환 함수 및 조건부 표현식 사용
SQL 기본2017. 9. 7. 01:59
■5장 . 복수행 함수
- 5장 목차
- max, min, avg, sum, count 함수
- 그룹함수에서의 null 값의 의미
- having 절
- 그룹함수를 중첩하는 방법
- ■max 함수
: 최대값을 출력하는 함수
문제 101. 최대 월급을 출력하시오 !
SELECT MAX(sal) FROM EMP;
문제102. 직업이 SALESMAN인 사원들중에서의 최대월급을 출력하시오
SELECT MAX(sal) FROM EMP WHERE JOB = 'SALESMAN'
문제103. SK 텔레콤 사용 학생중 최대 나이를 출력하시오
SELECT MAX(age)
FROM EMP2
WHERE TELECOM = 'sk';
3 SELECT telecom MAX(age)
1 FROM EMP2
2 WHERE TELECOM = 'sk';
telecom은 여러개 나오려고 하고 max는 하나만 나오려고하니까 에러남
4 SELECT telecom, MAX(age)
1 FROM EMP2
2 WHERE TELECOM = 'sk'
3 GROUP BY telecom;
- ※ group by 역할 : 데이터를 grouping 하는 역할
- select
- from
- where group by
문제104. 부서번호, 부서번호별 최대월급을 출력하시오!
SELECT deptno, MAX(sal)
FROM EMP
GROUP BY deptno;
문제105. 전공, 전공별 최대나이를 출력하시오
SELECT major, MAX(age)
FROM EMP2
GROUP BY major;
문제106. 성씨별 최대나이를 출력하시오
SELECT substr(ename,1,1), MAX(age)
FROM EMP2
GROUP BY substr(ename,1,1);
문제107. 위의 결과를 다시 출력하는데 나이가 높은 성부터 출력되게 하시오
SELECT substr(ename,1,1), MAX(age) 나이
FROM EMP2
GROUP BY substr(ename,1,1)
ORDER BY 나이 desc;
문제108. 서울시 물가 데이터를 오라클 database에 입력하시오 !
문제109. a_price (가격)
서울시 물가 데이터중 최대 가격을 출력하시오!!
SELECT MAX(a_price) FROM price;
- min 함수
: 최소값을 출력하는 함수
문제 110. 사원테이블에서 최소 월급을 출력하시오
SELECT MIN(sal) FROM EMP;
문제111. 직업, 직업별 최소월급을 출력하시오!
SELECT job, MIN(sal)
FROM EMP
GROUP BY job;
문제112. 위의 결과를 다시 출력하는데 직업이 null은
출력 안되게 하시오
SELECT job, MIN(sal)
FROM EMP
WHERE job IS NOT null
GROUP BY job;
문제113. 위의 결과를 다시 출력하는데 최소 월급이 높은 것부터 출력하시오
SELECT job, MIN(sal)
FROM EMP
WHERE job IS NOT null
GROUP BY job
ORDER BY min(sal) desc;
문제114. 사원 테이블에 가장 먼저 입사한 사원의 입사일을 출력하시오
select min(hiredate)
from emp;
문제115. 전공 , 전공별 최소나이를 출력하는데 컴공은 제외하고 출력하시오
select major, min(age)
from emp2
where major <> '컴퓨터공학'
group by major;
문제116. 주소, 주소별 최소나이를 출력하시오
select substr(address,1,7), min(age)
from emp2
group by substr(address,1,7);
- count 함수 : 건수를 세는 함수
문제117. 사원 테이블의 전체 건수를 출력하시오
select count(empno)
from emp;
select count(*)
from emp;
문제118. 커미션을 count하시오
select count(comm)
from emp;
문제119. 직업이 SALESMAN 인 사원들이 총 몇명이 잇는가
SELECT COUNT(*)
FROM EMP
WHERE job = 'SALESMAN';
문제120. 직업이 ANALYST 인 사원들은 총 몇명이 있는가?
SELECT COUNT(*)
FROM EMP
WHERE JOB='ANALYST';
※ GROUP 함수는 NULL 값을 무시한다!!
문제121. 직업 , 직업별 인원수를 출력하시오
SELECT JOB, COUNT(*)
FROM EMP;
문제122. 전공, 전공별 인원수를 출력하시오
SELECT MAJOR, COUNT(*)
FROM EMP2
GROUP BY MAJOR
문네123. 위의 결과를 다시 출력하는데 전공별 인원수가 높은 것 부터
SELECT MAJOR, COUNT(*)
FROM EMP2
GROUP BY MAJOR
ORDER BY COUNT(*) DESC;
문제124. 지역, 지역별 인원수를 출력하시오
SELECT SUBSTR(ADDRESS,1,7), COUNT(*)
FROM EMP2
GROUP BY SUBSTR(ADDRESS,1,7)
ORDER BY COUNT(*) DESC;
*avg 함수
: 평균값을 출력하는 함수
문제125. 사원 테이블에 월급을 평균내시오
select avg(sal)
from emp
;
문제126. 우리반 평균나이를 구하세요
select avg(age)
from emp2;
문제127. 통신사 , 통신사별 평균나이를 구하시오
SELECT telecom, AVG(age)
FROM EMP2
GROUP BY telecom;
문제128. (점심시간 문제)
이메일 도메인 명, 이메일 도메인 별 인우너수를 출력하느데
이메일 도메인별 인원수가 높은것 부터 출력하고
컬럼명을 아래 와 같이 이메일 도메인 , 인원수로 출력되게 하시오
SELECT substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1) AS "이메일 도메인" , count(*) as "인원수"
from emp2
group by substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1);
문제129. 커미션의 평균값을 출력하시오
select avg(comm)
from emp;
※그룹함수는 null 값을 무시하기 때문에 comm을 다 다해서 4로 나눈값이다.
문제130. 위의 결과를 다시 출력하는데 전체 사원수로 나눠지게 하려면 어떻게
해야하는가.
select avg(nvl(comm,0) )
from emp;
5.2 그룹함수에서 null값의 의미
" group 함수는 null 값을 무시한다."
예제1: 값이 달라지는 경우
select avg(comm) from emp;
select avg(nvl(comm,0)) from emp;
예제2:
select sum(comm) form emp;
select sum(nvl(comm,0)) from emp;
결과는 같으나 어떤 SQL이 성능이 좋을까?
널 값을 무시하지 않고 0으로 바꿔서 연산하는 밑에 연산이 비효율적이다.
- group 함수의 특징 2가지
- group 함수는 null값을 무시한다.
- group 함수는 결과를 항상 출력한다.
문제131. 이름과 월급과 직업을 출력하는데 월급이 높은 사원부터 출력하시오
select ename, sal, job
from emp
order by sal desc;
문제132. 월급을 출력하는데 decode를 이용해서 직어빙 president는 null이 나오게 하고 다른 직업이면 월급이 출력되게 하시오
select decode( job, 'PRESIDENT', ''
,sal)
from emp;
그런데 !!
SELECT MAX(decode(job,'PRESIDENT', NULL,sal))
1 2 3 4
from emp;
※ decode 문에서 세번째 인자값의 데이터 유형에 네이번째 인자값의 데이터 유형이 결정된다.
null은 데이터 유형이 문자형이다.
select to_char(sal)
from emp;
문자로 하면 9가 큰값이기 때문에
SELECT MAX(decode(job,'PRESIDENT', 0,sal))
from emp;
으로 쓰는게 좋아용
*sum 함수
: 값을 다 더하는 함수
문제 133. 우리반의 나이를 다 더하면 얼마인가?
select sum(age)
from emp2;
문제134. 직업, 직업별 토탈 월급을 출력하시오
select job, sum(sal)
from emp
group by job;
문제135. 직업과 직업별 토탈월급을 출력하는데 SALESMAN은 제외하고 출력하시오
4 select job, sum(sal)
1 from emp
2 where job <> 'SALESMAN'
3 group by job;
문제 135. 위 결과를 출력하는데 직업별 토탈월급이 6000 이상인것만 나오게 하시오
※ Group 함수로 조건을 줄 때 사용하는 절은 where 절이 아니라 having 절을 사용해야 한다.
5 select job, sum(sal)
1 from emp
2 where job <> 'SALESMAN'
3 group by job
4 HAVING SUM(sal) >= 6000;
이렇게 하세요
having 절은 group by 다음에 나와야 해요
select 문의 6가지절
5 select --- 컬럼명
1 from --- 테이블명
2 where --- 검색조건
3 group by --- grouping 할 컬럼
4 having --- group 함수로 조건을 줄 때
5 order by ---- 데이터를 정렬하는 절
문제137. 통신사, 통신사별 인원수를 출력하는데
통신사가 sk는 제외하고 출력하고
통신사별 인원수가 6명이상인 것만 출력하시오
데이터 게시판 : 6번 범죄원인
문제138. 범죄유형에는 무엇이 있는지 조회하시오 !
(중복제거해서 표기하시오 )
SELECT DISTINCT crime_type
FROM crime_cause2;
문제139. 범죄 원인은 무엇이 있는지 조회하시오 .
SELECT distinct term FROM crime_cause2;
문제140. 범죄유형, 범죄유형별 총 건수를 출력하시오!
SELECT crime_type, SUM(cnt)
from crime_cause2
GROUP BY crime_type;
문제141. 위결과에서 건수 합계가 높은것 부터출력
SELECT crime_type, SUM(cnt)
from crime_cause2
GROUP BY crime_type
ORDER BY SUM(cnt) desc;
문제142. 위의 결과에서 건 단위가 1000단위가 표시되게 하시오
SELECT crime_type, TO_CHAR(SUM(cnt),'999,999')
from crime_cause2
GROUP BY crime_type
ORDER BY SUM(cnt) desc;
5.4 그룹함수를 중첩하는 방법
문제143. 직업, 직업별 토탈월급을 출력하시오 !
select job, sum(sal)
from emp
group by job;
문제144. 위의 결과중에 최대 값을 출력하시오.
select MAX(sum(sal))
from emp
group by job;
문제145. 통신사중 가장 많은 인원수인 통신사의 인원수가 몇명인지 출력하라
SELECT MAX(COUNT(*))
FROM EMP2
GROUP BY telecom;
문제146. 직업, 직업별 토탈월급을 출력하는데 직업이 SALESMAN은 제외하고
출력하고 직업별 토탈 월급이 4000이상인 것만 출력하고
직업별 토탈 월급을 높은 것 부터 출력하는데
직업별 토탈월급에 천단위를 부여하라.
SELECT job, TO_CHAR(SUM(sal),'999,999')
FROM EMP
WHERE job <> 'SALESMAN'
GROUP BY job
HAVING SUM(sal) >= 4000
ORDER BY SUM(sal) DESC;
SELECT job, TO_CHAR(SUM(sal),'999,999')
FROM EMP
GROUP BY job
HAVING SUM(sal) >= 5000 AND job <> 'SALESMAN'
ORDER BY SUM(sal) DESC;
성능이 좋지 않음 SQL 인덱스를 사용하지 못함
SELECT job, TO_CHAR(SUM(sal),'999,999') aaa
FROM EMP
GROUP BY job
HAVING SUM(sal) >= 5000 AND job <> 'SALESMAN'
ORDER BY aaa DESC;
해도 문자 순으로 구별하는게 아니라 숫자로 정렬해줌
문제 147. 직업, 직업별 부서번호별 토탈 월급을 출력하시오
select job,deptno, sum(sal)
from emp
group by job;
문제148. 통신사별 , 전공별, 인원수를 출력하시오!
select telecom, major, count(*)
from emp2
group by telecom, major
ORDER BY telecom , major;
■ 결과를 가로로 출력하는 방법
문제 149. 부서번호, 부서번호별 토탈월급을 출력하시오
select deptno, sum(sal)
from emp
group by deptno;
이거를
-가로 :
10 |
20 |
30 |
8750 |
10875 |
9400 |
가로로 나오게 케로로하라
힌트
select ( decode(dettno, 10,sal ,0) as "10"
from emp;
정답:
select SUM(decode(deptno, 10,sal ,0)) AS "10",
SUM(decode(deptno, 20,sal ,0)) AS "20",
SUM(decode(deptno, 30,sal ,0)) AS "30"
from emp;
삼성 sql 악성 SQL입니당!
문제150 통신사, 통신사별 인원수를 세로, 가로로 출력하시오
-세로
SELECT telecom, COUNT(*)
FroM EMP2
GROUP BY telecom;
-가로
(카운트는 Null을 무시함)
SELECT COUNT(DECODE(telecom, 'sk',telecom,null)) AS "sk",
COUNT(DECODE(telecom, 'lg',telecom,null)) AS "lg",
COUNT(DECODE(telecom, 'kt',telecom,null)) AS "kt"
from EMP2;
SELECT sum(DECODE(telecom, 'sk',1,0)) AS "sk",
sum(DECODE(telecom, 'lg',1,0)) AS "lg",
sum(DECODE(telecom, 'kt',1,0)) AS "kt"
from EMP2;
SELECT sum(DECODE(telecom, 'sk',1,null)) AS "sk",
sum(DECODE(telecom, 'lg',1,null)) AS "lg",
sum(DECODE(telecom, 'kt',1,null)) AS "kt"
from EMP2;
널로 바꾸면 더 빨라짐
문제151. 나이 , 나이별 인원수를 출력하는데 세로, 가로로 출력하시오
-세로
SELECT age, COUNT(*)
FROM EMP2
GROUP BY age
ORDER BY age desc;
-가로
SELECT SUM(DECODE(age,24,1,null)) AS "24",
SUM(DECODE(age,25,1,null)) AS "25",
SUM(DECODE(age,26,1,null)) AS "26",
SUM(DECODE(age,27,1,null)) AS "27",
SUM(DECODE(age,28,1,null)) AS "28",
SUM(DECODE(age,29,1,null)) AS "29",
SUM(DECODE(age,30,1,null)) AS "30",
SUM(DECODE(age,31,1,null)) AS "31",
SUM(DECODE(age,33,1,null)) AS "33",
SUM(DECODE(age,35,1,null)) AS "35"
FROM EMP2;
문제152. 위의 결과에서 전공도 같이 출력하시오!
SELECT major, SUM(DECODE(age,24,1,null)) AS "24",
SUM(DECODE(age,25,1,null)) AS "25",
SUM(DECODE(age,26,1,null)) AS "26",
SUM(DECODE(age,27,1,null)) AS "27",
SUM(DECODE(age,28,1,null)) AS "28",
SUM(DECODE(age,29,1,null)) AS "29",
SUM(DECODE(age,30,1,null)) AS "30",
SUM(DECODE(age,31,1,null)) AS "31",
SUM(DECODE(age,33,1,null)) AS "33",
SUM(DECODE(age,35,1,null)) AS "35"
FROM EMP2
group by major;
문제153. (오늘의 마지막 문제!)메일 도메인 별, 전공별 인원수를 출력하시오
가로로 이쁘게
SELECT substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1) AS "이메일 도메인" ,
SUM(DECODE(major,'산업경영공학',1,null)) AS "산업경영공학",
SUM(DECODE(major,'미술경영학',1,null)) AS "미술경영학",
SUM(DECODE(major,'경영정보학',1,null)) AS "경영정보학",
SUM(DECODE(major,'신문방송학',1,null)) AS "신문방송학",
SUM(DECODE(major,'정보통신공학',1,null)) AS "정보통신공학",
SUM(DECODE(major,'경영학',1,null)) AS "경영학",
SUM(DECODE(major,'컴퓨터공학',1,null)) AS "컴퓨터공학",
SUM(DECODE(major,'응용수학',1,null)) AS "응용수학",
SUM(DECODE(major,'관광학',1,null)) AS "관광학",
SUM(DECODE(major,'행정학',1,null)) AS "행정학",
SUM(DECODE(major,'경제학',1,null)) AS "경제학",
SUM(DECODE(major,'산업심리학',1,null)) AS "산업심리학",
SUM(DECODE(major,'전자공학',1,null)) AS "전자공학",
SUM(DECODE(major,'글로벌금융학',1,null)) AS "글로벌금융학",
SUM(DECODE(major,'통계학',1,null)) AS "통계학",
SUM(DECODE(major,'정보통계학',1,null)) AS "정보통계학",
SUM(DECODE(major,'물리학',1,null)) AS "물리학",
from emp2
group by substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1);
은총이문제 학과별 이메일 도메인(갯수)를 나타내어라
예
컴퓨터공학과 |
naver(3), gmail(1) |
응용수학 |
gmail(1) |
select major , substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1)
from emp2
group major;
'SQL 기본' 카테고리의 다른 글
| 7장 Subquery 사용 (0) | 2017.09.07 |
|---|---|
| 6장 조인 문법 (0) | 2017.09.07 |
| 4장 DECODE와 PIVOT 변환 함수 및 조건부 표현식 사용 (0) | 2017.07.05 |
| 3장 단일 행 함수를 사용하여 출력 커스터마이즈 (0) | 2017.07.05 |
| 2장 데이터 제한미 정렬 (0) | 2017.07.05 |
4장 DECODE와 PIVOT 변환 함수 및 조건부 표현식 사용
SQL 기본2017. 7. 5. 19:48
■지난주 배운것
- SQL이란 무엇인가
- SQL을 왜 배워야 하는거ㅏ?
- 기본 select 문 6가지 절
5 select
1 from
2 where
3 group by
4 having
6 order by
-
함수
- 단일행 함수 : 문자, 숫자, 날짜 ,변환, 일반
- 복수행 함수 : max, min, avg, sum, count
-
함수
문제 154. 부서번호, 부서번호별 인원수를 출력하시오 !
(세로)
SELECT deptno, COUNT(*)
FROM EMP
GROUP BY deptno;
(가로)
문제155. 부서번호 , 부서번호별 토탈 월급을 가로로출력하시오
-sum 함수와 decode 함수를 사용한 방법:
select sum ( decode, 10, sal , 0 ) ) as "10",
sum ( decode, 20, sal , 0 ) ) as "20",
sum ( decode, 30, sal , 0 ) ) as "30"
from emp;
- pivot 문을 이용하는 방법
select *
from ( select deptno, sal from emp)
pivot ( sum (sal) for deptno in (10,20,30) );
문제156.직업, 직업별 토탈 월급을 출력하시오
세로
SELECT job, SUM(sal)
FROM EMP
GROUP BY job;
가로
SELECT *
FROM ( SELECT job,sal FROM emp)
pivot ( SUM(sal) FOR job IN ('SALESMAN', 'CLERK','PRESIDENT','MANAGER','ANALYST'));
싱글쿼테이션 없애기
SELECT *
FROM ( SELECT job,sal FROM emp)
pivot ( SUM(sal) FOR job IN ('SALESMAN' AS "SALESMAN",
'CLERK' AS "CLERK",
'PRESIDENT' AS "PRESIDENT",
'MANAGER' AS "MANAGER",
'ANALYST' AS "ANALYST"));
문제157. 통신사 , 통신사별 인원수를 가로로 출력하시오 (PIVOT으로 구현하시오)
SELECT *
FROM (SELECT TELECOM FROM EMP2)
PIVOT ( COUNT(*) FOR TELECOM IN ( 'sk' AS "SK", 'lg' AS "LG",'kt' AS "KT"));
문제158. 입사한 년도 (4자리) , 입사한 년도별 토탈월급을 출력하시오
- 세로
SELECT TO_CHAR(hiredate, 'YYYY') , SUM(sal)
FROM EMP
GROUP BY TO_CHAR(hiredate, 'YYYY');
- 가로
SELECT *
FROM ( SELECT TO_CHAR(hiredate, 'YYYY') a, sal FROM emp)
pivot( SUM(sal) FOR a IN ('1980' AS "1980",
'1981' AS "1981",
'1982' AS "1982",
'1983' AS "1983"));
설명: pivot문안에TO_CHAR(hiredate, 'YYYY')하면에러간난다.
-> 컬럼 별칭을 이용하도록 하자
문제159. 입사한 년도(4자리), 입사한 년도별 인원수를 가로로 출력하시오.
SELECT *
FROM ( SELECT TO_CHAR(hiredate, 'YYYY') A FROM EMP)
PIVOT ( COUNT(*) FOR A IN ('1980' AS "1980",
'1981' AS "1981",
'1982' AS "1982",
'1983' AS "1983"));
문제160. 부서번호, 부서번호별 토탈월급을 가로로출력하시오!
- SUM 함수와 DECODE 함수 사용
SELECT SUM(DECODE(DEPTNO,10,SAL,0)) AS "10",
SUM(DECODE(DEPTNO,20,SAL,0)) AS "20",
SUM(DECODE(DEPTNO,30,SAL,0)) AS "30"
FROM EMP ;
- PIVOT 문 이용하기
SELECT *
FROM ( SELECT DEPTNO, SAL FROM EMP)
PIVOT ( SUM(SAL) FOR DEPTNO IN (10,20,30));
문제160. 부서번호, 부서번호별 토탈월급을 가로로출력하시오! (job또한)
- SUM 함수와 DECODE 함수 사용
SELECT JOB,
SUM(DECODE(DEPTNO, 10, SAL, 0)) AS "10",
SUM(DECODE(DEPTNO, 20, SAL, 0)) AS "20",
SUM(DECODE(DEPTNO, 30, SAL, 0)) AS "30"
FROM EMP
GROUP BY JOB ;
- PIVOT 문 이용하기
SELECT *
FROM ( SELECT DEPTNO, SAL,,JOB FROM EMP)
PIVOT ( SUM(SAL) FOR DEPTNO IN (10,20,30));
문제161. 전공별, 통신사별 인원수를 출력하시오
(세로) (가로)
-SUM 함수와 DECODE함수를 활용하는방법
SELECT MAJOR ,SUM(DECODE(TELECOM,'sk',1,null)) AS "sk",
SUM(DECODE(TELECOM,'lg',1,null)) AS "lg",
SUM(DECODE(TELECOM,'kt',1,null)) AS "kt"
FROM EMP2
GROUP BY major;
-PIVOT문을 활용하는 방법
SELECT *
FROM (SELECT major, telecom FROM EMP2)
pivot ( COUNT(*) for telecom IN ( 'sk', 'lg', 'kt'));
문제162. (점심시간 문제)
전공별, 나이별 인원수를 출력하시오
(세로) (가로)
select *
from (select major, agefrom emp2)
pivot (count(*) for age in (24, 25, 26, 27, 28, 29, 30, 31, 33, 35 ));
■ 데이터 분석함수
- listagg 함수
- rank 함수
문제163. 이름 , 월급, 월급에 대한 순위를 출력하시오
select ename, sal, rank() over ( order by sal desc) 순위
from emp;
3등을 뽑고 싶으면
문제164. 이름,나이, 순위를 출력하는데 순위가 생년월일 순으로 순위를 부여하시오
SELECT telecom, ename, age,
dense_rank() over ( PARTITION BY telecom
order by birth asc) 순위
from EMP2 ;
문제166. 부서번호, 이름, 월급 ,순위를 출력하는데 부서번호별로 각각 월급이 높은 순서대로 순위를 부여하시오
select deptno , ename, sal , dense_rank() over ( partition by deptno
order by sal desc) 순위
from emp;
문제167. FORD는 월급을 3000을 받으면 emp 테이블에서 월급의 순위가
어떻게 되겠는가?
select rank(3000) within GROUP (order by sal desc) 순위 from emp;
문제168. 1991년 8월 15일 은 우리반에서 나이 순위가 어떻게 되는가
SELECT RANK(TO_DATE('1991/08/15','RRRR/MM/DD')) within GROUP (ORDER BY birth asc) 순위
FROM EMP2;
ALTER SESSION SET nls_date_format='yyyy-mm-dd';
select rank('1991/8/15') within group ( order by birth acs) 순위 from emp2;
문제169. 아래의 데이터를 입력하고 이름과 월급을 출력하는데
월급이 높은 것 부터 출력하시오!
insert into emp ( empno , ename, sal , deptno)
values (1924,'jack', null, 30);
commit;
select ename, sal FROM EMP ORDER by sal DESC;
null 이 맨 위로 올라감
select ename, sal
FROM EMP
ORDER by sal DESC nulls last;
null 이 마지막으로 빠짐
문제 170. 이름, 입사일, 순위를 출력하는데 가장 최근에 입사한 사원부터 순위를 부여하시오 (가장 최근에 입사한 사원이 1등이 되게 )
SELECT ename, hiredate, RANK() OVER (ORDER BY hiredate DESC nulls last)
FROM EMP ;
'SQL 기본' 카테고리의 다른 글
| 6장 조인 문법 (0) | 2017.09.07 |
|---|---|
| 5장 복수행 함수 변환 함수 및 조건부 표현식 사용 (0) | 2017.09.07 |
| 3장 단일 행 함수를 사용하여 출력 커스터마이즈 (0) | 2017.07.05 |
| 2장 데이터 제한미 정렬 (0) | 2017.07.05 |
| 1장 SQL SELECT 문을 사용하여 데이터 검색 (0) | 2017.07.05 |
3장 단일 행 함수를 사용하여 출력 커스터마이즈
SQL 기본2017. 7. 5. 19:48
*3장 목차
- 문자함수
- 숫자함수
- 날짜 함수
-
함수의 두가지 종류
-
단일행 함수
- 하나의 값이 입력이 되어서 하나의 값으로 출력
-
단일행 함수
-
함수의 두가지 종류
- 복수행 함수,
- 여러개의 값들이 입력되어서 하나의 값으로 출력
문 47. 서울에서 사는 학생들의 이름과 나이와 주로를 출력하는데 나이가 높은 순서로
\
문 49. 우리반에서 naver 메일을 사용하는 학생들의 이름과 나이와 이메일을 출력하시오 !
select ename, age, email from emp2 where email like '%naver%';
문 50 . 컴퓨터학과가 아닌 학생들의 이름과 전공을 출력하시오 !
select ename , major from emp2 where major not like '컴%';
문 51. 성이 김씨와 이씨인 학생들의 이름과 나이를 출력하시오
select ename, age from emp2 where ename like '김%' or ename like '이%';
3.1 문자함수
- 대소문자 변환함수: upper. lower, initcap
- 문자조작함수 : concat, instr, lpad, rpad, trim, length, replace
*upper 함수 대문자로 변환하는 함수
문제51. 우리반 테이블에서 이메일을 출력하는데 이메일을 대문자로 출력하시오
select upper(email)
from emp2;
문52. 이름이 scott 인 사원의 이름과 월급을 출력하는데 이름을 소문자로 입력해도 출력되게 하시오
select ename ,sal
FROM EMP
WHERE lower(ename) = 'scott';
- initcap 함수 : 첫버째 철자는 대문자로 출력하고
나머지 철자는 소문자로 출력하는 함수
select initcap(ename)
from emp;
- concat 함수 : 두개의 컬럼값을 연결하는 함수
select concat(ename,sal)
from emp;
select ename ||' '|| sal escape 'm'
from emp;
- substr 함수 (★★★★★★★★★)
"특정 철자열을 잘래내서 가져오는 함수"
select ename, substr(ename,0,5)
from emp;
문제53. 우리반 테이브레엇 이름의 성씨만 출력하시오
select substr(ename,1,1)
from emp2;
문제54.성이 김씨인 학생들의 이름을 출력하시오
select ename
from EMP2
WHERE substr(ename,1,1) = '김';
문55. 성씨가 김씨, 이씨 , 조씨 인 학생들의 이름을 출력하라
select ename
from EMP2
WHERE substr(ename,1,1) in ('김','이','조');
문56. 주소가 부산인 학생들의 이름과 주소를 출력하시오
(like 사용하지 말고 substr함수로 수행하라)
select ename
from EMP2
WHERE substr(address,1,2) ='부산';
문57. 통신사가 null인 학생들의 이름과 통신사를 출력하시오
select ename, telecom from EMP2 WHERE telecom IS null ;
문58. 이름과 통신사를 출력하는데 통신사를 ASCENDING 하게 정렬해서 출력하시오
SELECT ENAME , TELECOM FROM EMP2 ORDER BY TELECOM;
- instr 함수
"문자안에 특정 철자의 자릿수를 출력하는 함수"
select ename, instr(ename,'M')
from emp;
문59. 이메일, 이메일에서 @ 가 몇번째 자리에 있는지
출력하시오!
select email, instr(email,'@')
from emp2;
- trim 함수
" 특정 철자를 잘라내는 함수 "
문제60. 이름이 JACK인 사원의 이름과 월급을 출력하시오
SELECT ENAME, SAL FROM EMP WHERE TRIM(ENAME) = 'JACK';
설명: 양쪽에 공백을 잘라 버리겠다.
문제61. 우리반 테이블에서 EMAIL.을 출력하는데 이메일 끝에 .COM을 잘라내고 출력하시오
select trim(email,'.com')
from emp2;
문제62. 위의 결과에서 .net도 잘라내시오
SELECT RTRIM(RTRIM(RTRIM(email, 'com'), 'net'), '.')
FROM EMP2;
SELECT RTRIM(RTRIM(LOWER(email), 'abcdefghijklmnopqrstuvwxyz'), '.')
FROM EMP2;
SELECT ename, RTRIM(email,SUBSTR(email,instr(email,'.'),LENGTH(email))) FROM EMP2;
SELECT ename, RTRIM(email,SUBSTR(email,instr(email,'.'))) FROM EMP2;
SELECT RTRIM(email, SUBSTR(email, instr(email, '.'),4)) FROM EMP2;
select rtrim(rtrim(email,'comnet'),'.') from emp2;
SELECT substr(email,1,INSTR(email,'.')-1) FROM EMP2;
SELECT ename, RTRIM(rtrim(email,'.com'),'.net') FROM EMP2;
문제63. 위의 출력된 email 의 결과에서 @가 몇번째 자리에 잇는지 출력하시오
SELECT ename, RTRIM(email,SUBSTR(email,instr(email,'.'))) , instr( RTRIM(email,SUBSTR(email,instr(email,'.'))),'@' ) FROM EMP2;
문제64. (난이도 상 : ★★★★★★★) 우리반 이메일에서 도메인만 출력하시오
select substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1) from emp2;
*length 함수
철자의 갯수를 세는 함수
select ename, length(ename)
from emp;
문제 65. 우리반에서 email이 가장 긴 학생이 누구인지 출력하세요
select ename email, length (email)
from emp2
order by length(email) desc;
같은 표현
select ename email, length (email) as a
from emp2
order by a desc;
- replace 함수
"특정 철자를 다른 철자로 변경하는 함수"
문제 66. 이름과 월급을 출력하는데 월급을 출력할 때
0이 아니라 * 로 출력하세요 !
select ename, replace(sal,0,'*')
from emp;
문제 67. 이름과 월급을 출력하는데 월급을 출력할 때
0,1,2를 이 아니라 * 로 출력하세요 !( 맨 마지막 장에서 배우는 정규식 함수를
사용해야 한다)
select ename, sal , regexp_replace(sal,'[0-2]','*')
from emp;
문제 68. 이름과 핸드폰 번호를 출력하는데 핸드폰 번호가 암호화 되게 출력하시오
select ename, mobile, regexp_replace(mobile,'[6-9]','*')
from emp2;
int INSTR( string, substring [, start_position [, th_appearance ] ] ) |
substring과 일치하는 위치를start_position 부터 시작해서 th_appearance 번 검색해서위치를 숫자로 반환한다.
|
int instr( String[원본], Char[문자])
|
문자의 위치를 숫자로 알려준다.
|
String substr(Stirng[원본], int[에서],int[까지])
|
i번째부터 j번째까지 잘라온다.
|
String substr(Stirng, int) |
i번째 부터 끝까지 잘라온다.
|
String trim (String) |
공백제거 |
String rtrim(String,String) |
오른쪽 끝에서 두번째 파라미터에 포함된 문자열을 제거한다. 순서 상관 없음
|
String ltrim(String,String) |
왼쪽 끝에서 파라미터에 포함된 문자열을 모두 제거한다.
|
- 2.2 숫자함수
1. round
-> 반올림하는 함수
2. trunc
-> 반올림하지 않고 잘라내는 함수
3. mod
-> 나눈 나머지 값을 출력하는 함수
4.power
-> 제곱승을 구하는 함수
체스 : 10 의 50승
바둑: 10의 171승
select power(10,63) from dual;
문제69. 이름, 월급*350 출력하시오.
select ename, sal*350 from emp;
문제70. 아래의 JONES의 월급이 10의 자리에서 반올림 되도록 하시오.
select ename, sal*350, round(sal*350, -2) from emp;
1 2 3 4 . 1 2 3 4
-2 -1 0 1 2
문제71. 이름, 월급 * 0.235를 출력하는데 소숫점이하는 출력되지 않고 반올림 되게 하시오.
select ename, round(sal*0.235,0) from emp;
문제 72. 이름, 월급 * 0.235를 출력하는데 소숫점 이하는 출력되지 않게 하고 반올림하는게 아니라 소수점 이하를 버리시오
* MOD 함수
10을 3으로 나눈 나머지값이 무엇인가?
select mod (10,3) from dual;
↑
결과값을 보기 위한 가상의 테이블
- 2.3 날짜 함수
날짜 - 날짜 = 숫자
날짜 + 숫자 = 날짜
날짜 - 숫자 = 날짜
- 오늘 날짜가를 보는 키워드
select sysdate from dual;
문제 74. 이름, 입사한 날짜부터 오늘까지 총 몇일 근무했는지 출력하시오
SELECT ename, ROUND(SYSDATE - hiredate) FROM EMP;
문제 75 , 이름 , 입사한 날짜부터 오늘까지 총 몇달 근무했는지 출력하시오
- months_between 함수
: 날짜와 날짜 사이의 개월수를 출력하는 함수
select ename, months_between(sysdate, hiredate) from emp;
- add_month
: 날짜에 개월수를 더했을때의 날짜를 출력하는 함수
문제76. 오늘 날짜에서 6개월 후의 날짜를 출력하시오!
select sysdate, add_months(sysdate, 6) from dual;
문제77. 이름, 입사일, 입사일에서 200달 후의 날짜를 출력하시오
SELECT ename, hiredate ,add_months(hiredate, 200)FROM EMP;
*날짜함수
- month_between
- add_months
- next_day
: 지정된 날짜에서 앞으로 돌아올 요일의 날짜를 출력하는 함수
- last_day
:지정된 날짜의 달에 마지막 날짜를 출력
문제78. 오늘 부터 앞으로 돌아올 금요일의 날짜를 출력하시오
select next_day(sysdate, 'friday')
from daul;
문제79. (난이도 상) 오늘부터 100달뒤에 돌아오는 월요일의 날짜를 출력하시오
select next_day(add_months(sysdate,100),'monday') from dual;
** 분석함수
select deptno,
listagg(ename,',') ||'(' sal||')' within group ( order by ename)
from emp
group by deptno;
listagg('컬럼명', '구분자')
가로로 출력해라
문제80. 오늘날짜의 달에 마지막 날짜를 출력하시오 !
select last_day(sysdate)
from dual;
문제81. 오늘 부터 이번달 말일까지 총 몇일 남았는지 출력하시오
select last_day(sysdate) - sysdate
from dual;
문제82. (점심시간문제) 아래의 결과를 출력하시오
SELECT deptno,listagg(ename || '('|| sal || ')',',')
within group ( order by ename)
from emp
group by deptno;
-
■ 함수
- 문자함수
- 숫자함수
- 날짜함수
- 변환함수
-
■ 함수
-
3.4 변환함수
-
"변환을 한다는 뜻은 데이터 유형을 변환한다는 것"
- 문자형
- 숫자형
- 날짜형
-
"변환을 한다는 뜻은 데이터 유형을 변환한다는 것"
-
3.4 변환함수
SELECT ename, sal FROM EMP WHERE sal = '3000';
에러가난다.
no low select 가 나온다
결과가 나온다
정답 3번 ㅎ
※ 암시적 형변환이 발생했다.
숫자 > 문자
숫자가 우선순위 높아서 둘이 비교할 때 문자를 숫자로 변환해줌
- 형변환 2가지
1. 암시적 형변환
set autot traceonly explain
설명: 지금 부터 수행할 SQL에 대해서 실행계획만 보여달라 데이터 결과는 안봐도 되고
select ename, sal from emp where sal = '3000';
SQL> select ename, sal
from emp
where sal = '3000';
왜 암시적 형변환이 발생한 것이 별로 좋지 않다는 것인가
SQL> select ename, sal
from emp
where sal like '30%';
숫자로 형변환을 하려니 %를 숫자로 못바꾸니 sal을 문자로 바꿈
인덱스가 풀려버려서 풀스캔합니다 이렇게 하면 안돼요
오라클은 친절하게도 암시적 형변환이 발생하지만
다른 rdbms 제품에서는에러가 난다.
2.. 명시적 형변환
- to_char : 문자로 형변환하는 함수
- to_number : 숫자로 형변환하는 함수
- to_date : 날짜로 형변환하는 함수
select ename, sal, to_char(sal,'999,999') from emp;
월급을 출력한느 문자로 형변환해서 출력하겠다.
위의 형식 (format) 에 맞춰서 출력해라
설명: 9 가 의미하는것은 9 자리에 어떠한 숫자가 와도 관계 없다 . 대신 자릿수는 한개여야한다.
select ename, sal, to_char(sal,'999,999') from emp;
문제 83. 이름 , 월급 * 30203455 를 출력하는데 숫자 단위가 ( , )
가 출력되게 하시오 ( 천단위 ,백만단위 등을 표시)
select ename, sal, to_char(sal* 30203455,'999,999,999,999') from emp;
설명 : 숫자 ---------> 문자
- 날짜 ---> 문자
문제84. 오늘이 무슨요일인지 출력하시오
select sysdate, to_char( sysdate, 'day') from dual;
select sysdate, to_char( sysdate, 'day') from dual;
문제85. 내가 무슨요일에 태어났느닞 출력하시오!
select SYSDATE,ename, to_char(BIRTH, 'day') from EMP2;
- 날짜 ->> 문자로 형변환 시 사용할 수 있는 format
- 년도 : RRRR, YYYY, RR, YY
- 달 : MM ,MON
- 일 : DD
- 요일 : DAY
- 시간 : HH, HH24
- 분 :MI
- 초 : SS
- 주(WEEK) : IW,WW
\
문제 86, 수요일에 입사한 사원들의 이름과 입사일과 입사한 요일을 출력하시오
select ename,HIREDATE, to_char(HIREDATE, 'day') from EMP WHERE to_char(HIREDATE, 'day') = 'wednesday';
- 명시적 형변환 함수 ( p 4-7)
문제 87. 이름 , 입사년도 ( 4자리) 로 출력하시오
select ename, to_char(hiredate, 'RRRR') from emp;
※ : RRRR, YYYY, RR, YY
select ename, to_char(hiredate, 'RRRR//MM/DD') from emp;
※ : RRRR, YYYY, RR, YY
문88. 1981 년도에 입사한 사원들의 이름과 입사한 년도를 출력하시오 (Like, substr사용하지 말고)
select ename,to_char(hiredate, 'RRRR') from EMP where to_char(hiredate, 'RRRR') = '1981';
문89. 오늘부터 100달 뒤에 돌아오는 날짜의 요일을 출력하시오
select to_char(add_months(sysdate,100),'day') from dual;
- 년도 : RRRR, YYYY, RR, YY
- 연도 형식인 RR과 YY의 차이
문제 90. 81년 12월 11일에 입사한 사원들의 이름과 입사일을 출력하시오 !
SELECT ename,HIREDATE FROM EMP WHERE hiredate= '81/12/11';
안뎀
DD-MON-RR
SELECT ename,HIREDATE FROM EMP WHERE hiredate= '11-DEC-81';
SELECT *
FROM nls_session_parameters;
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
문제91, 81년 12월 11일에 입사한 사원들의 이름과 입사일을 출력하는데
우리나라 날짜형식으로 작성해서 검색되게 하시오
alter session set nls_date_format='RR/MM/dd';
national language support
- RR과YY 의 차이 ??
RR |
YY |
|
81 1981 2081
2017
현재연도에서 가장
가까운 연도
|
81 1981 2081
2017
현재 세기의 연도
|
튜닝전:
SELECT ename, hiredate
FROM EMP
WHERE TO_CHAR(hiredate, 'RR/MM/DD') = '81/12/11';
악성 sql입니다 성능을 떨어뜨림 index를 활용하지 못함
풀스캔
튜닝후: 좌변을 가공하면 안됨
select ename, hiredate
from emp
where hiredate = to_date('81/12/11', 'RR/MM/DD');
직업군 : 데이터 분석가, 소프트웨어 엔지니어(코딩),
DBA, 빅데이터 엔지니어, SQL 튜너
-
■ 함수
- 문자
- 숫자
- 날짜
- 변환
- to_char
- to_date
- to_number
-
■ 함수
5. 일반함수 :
함수 nvl2 함수, decode 함수, case함수
문제 92. 이름과 커미션을 출력하는데 커미션이 null 인 사람들은 0으로 출력되게 하시오
SELECT ename , nvl(comm, 0) FROM EMP;
문제93.
이름과 커미션을 출력하는데 커미션이 null 인 사람들은 'no comm'으로 출력되게 하시오
SELECT ename , nvl(TO_CHAR(comm), 'no comm') FROM EMP;
- to_char ----> 문자로 형변환하는 함수
- to_number ---> 숫자로 형변환하는 함수
- to_date ---> 날짜로 형변환하는 함수
문94. 이름, 관리자의 사원번호 (mgr)를 출력하느데 mgr이 null인 사원은 no manager 란 글 씨로 출력되게 하시오
SELECT ename, NVL(TO_CHAR(mgr), 'no manager') FROM EMP;
SELECT ename, NVL(mgr || '', 'no manager') FROM EMP;
- null은 알 수 없는 값이다 null이다
null 대신에 다른 값을 출력하는 함수가 nvl함수이다.
- nvl2 함수
select ename, sal, comm, nvl2(comm, sal+com, sal) from emp;
↑
comm 이 null 이 아니면 sal + comm 출력
comm 이 null 이 면 sal을 출력하라
- decode 함수 (중요함수 ★★★★★★★★)
문제 95. 이름, 직업, 보너스를 출력하는데
직업이 SALESMAN 이면 보너스를 5000을 출력하고
직업이 SALESMAN이 아니면 보너스를 0으로 출력하시오
SELECT ENAME, JOB, DECODE(JOB, 'SALESMAN' , 5000, 0 ) "bonus"
from emp;
설명: coding 을 길게하지 않고 간단하게 함수로
결과를 출력할 수 있다.
문96. 이름,직업, 보너스를 출력하는데
직업이 SALESMAN 이면 보너스가 5000이 출력되게 하고
직업이 ANALYST 이면 보너스가 3000이 출력되게 하고
나머지 직업은 0으로 출력되게 하시오
SELECT ENAME, JOB, DECODE(JOB, 'SALESMAN' , 5000,
'ANALYST', 3000,
0) "bonus"
from emp;
문제97. 이름, 부서번호, 월급, 보너스를 출력하는데
보너스가 부서번호가 10번이면 월급의 20%로 출력
부서번호가 20번이면 월급의 40%로 출력
나머지 부서번호는 그냥 0 이 출력되게 하시오
SELECT ENAME, JOB, DECODE(DEPNO, 10 , SAL * 0.2
, 20 , SAL * 0.4,
0 ) "bonus"
from emp;
문98. 이름, 월급 보너스를 출력하는데
보너스가 월급이 3000 이상이면 보너스를 700으로 출력하고
월급이 3000보다 작으면 보너스를 0으로 출력하시오
SELECT ENAME, JOB, DECODE(DEPNO, SAL >=3000 ,700,
0 ) "bonus"
from emp;
설명 : DECODE는 등호비교만 가능하고 부등호 비교는 불가능하다.
부등호 비교를 표현하려면 CASE 문을 작성해야 한다.
SELECT ename, deptno, sal ,
case when sal > = 3000 then 7000
else 0 end as 보너스
from emp;
문제99. 이름 , 월급, 입사년도, 보너스를 출력하는데
입사한 년도가 1980년도면 보너스를 9000을 출력
입사한 년도가 1981년도면 보너스를 2000을 출력
나머니 년도면 그냥 0 을 출력하시오 .
select ename, sal, hiredate, decode(hiredate, to_date('1980', 'RRRR'), 9000,
,to_date('1980', 'RRRR'), 2000,
0)
FROM EMP;
문제 100. (오늘의 마지막 문제)
이메일메인별로 이름이 콤마로 구분되어서 리스트에그를 써서 나오게 하라
이메일 도메인 | 이름 ,이름,이름
구굴| 이름,이름
네이버| 이름,이름
SELECT substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1) AS "이메일 도메인" ,
listagg(ename ,',') within group ( order by ename) AS "이름"
from emp2
group by substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1)
ORDER BY COUNT("이메일 도메인") desc;
SELECT substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1) as "이메일 도메인",
listagg(ename ,',')
within group ( order by ename)
from emp2
group by email;
SELECT substr(email,instr(email,'@')DLF
listagg(ename ,',')
within group ( order by ename)
from emp2
group by substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1);
우리끼리 문제 . 학과별로 사람들은 ,으로 구분하여 listagg하라
SELECT RTRIM(major,'학과') ||'학과' AS "학과" , (ename ,',')
within group ( order by ename) AS "이름"
FROM EMP2
GROUP BY RTRIM(major,'학과');
SELECT * FROM EMP2;
- SQL이란 무엇인가?
-
SQL을 왜 배워야 하는지?
- 데이터를 다룰 줄 아는 기본 스킬
- 빅데이터 엔지니어, 데이터 분석가, 머신러닝
- 우리나라가 이 부분에 취약한 상환이고 강인공지능이 인류를
- 위협한다면 국내경제가 선진국에 지배를 당하는 현상이 일어날것
-
3. 기본 SQL 문
- 3 select
- 1 from
- 2 where
- 4 order by
-
3. 기본 SQL 문
-
4. 함수
- 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반
- 복수행 함수 : max, min , avg , sum, count
-
4. 함수
'SQL 기본' 카테고리의 다른 글
| 6장 조인 문법 (0) | 2017.09.07 |
|---|---|
| 5장 복수행 함수 변환 함수 및 조건부 표현식 사용 (0) | 2017.09.07 |
| 4장 DECODE와 PIVOT 변환 함수 및 조건부 표현식 사용 (0) | 2017.07.05 |
| 2장 데이터 제한미 정렬 (0) | 2017.07.05 |
| 1장 SQL SELECT 문을 사용하여 데이터 검색 (0) | 2017.07.05 |
