Duritz's Fun Life

--- ○ View
create or replace view v_sawonexpire
as
select Y.*,
       add_months(to_char(sysdate,'yyyy') || '-02-05', 12*(60-Y.현재나이)) "정년일",
       trunc(add_months(to_char(sysdate,'yyyy') || '-02-05', 12*(60-Y.현재나이)) - sysdate,0) "남은일수",
       trunc(sysdate - to_date(Y.입사일,'yyyy-mm-dd'),0) "근무일"
from (select case when substr(minbun,7,1) in ('1','2')
       then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1899)
          when substr(minbun,7,1) in ('3','4')
    then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1999)
       end "현재나이",
             to_char(hiredate,'yyyy-mm-dd') "입사일"
      from SAWON) Y;
--- create or replace view 뷰명 as (문장) : 해당문장을 지정한 뷰명으로 만들되
--                                          해당 뷰명이 있으면 replace(교체) 하고 없으면
--                                          새롭게 생성한다.
select * from v_sawonexpire;

select *
from v_sawonexpire
where extract(year from to_date(정년일,'yyyy-mm-dd')) >= 2050; -- 뷰 사용한 예제

create or replace view v_emp10
as
select empno, ename, sal
from emp
where deptno = 10;

select * from v_emp10;

create or replace view v_emp10
as
select empno, ename, sal, comm, coalesce(sal*12+comm, sal*12, 0) "YearPay"
    -- 원래 테이블에 있는 순수한 컬럼이 아니고 수식을 컬럼처럼 출력할때는 별칭을 해주어야한다.
from emp
where deptno = 10;

create or replace view v_emp10(사원번호, 사원명, 연봉) -- 뷰명 옆에 직접 별칭을 줄수 있다.
as
select empno, ename, coalesce(sal*12+comm, sal*12, 0)
from emp
where deptno = 10;

select 사원명  --- view를 사용한 예시문
from v_emp10
where 연봉 >= 2000
--// view와 view, 테이블과 view 모두 join이 되어진다.
--------------------------------------------------------------------------------------------------
-- view의 원본소스를 보자.
select * from tab; -- 해당유저의 테이블과 뷰를 모두 보여준다.

select *
from user_views
where view_name = 'V_SAWONEXPIRE'; -- 해당 뷰의 정보를 나타낸다.

select text
from user_views
where view_name='V_SAWONEXPIRE'; -- 해당 뷰의 텍스트 정보를 나타낸다.

----- 컬럼의 추가 및 제거하기 -----
--1. 컬럼추가
alter table sawon
add email varchar2(20);

select * from sawon;
--2. 컬럼삭제
alter table sawon
drop column email;

alter table sawon
add email varchar2(20) default ' ' constraint sawon_email_nn not null;
  -- 빈테이블이라면 상관이 없지만 데이타가 있는 상태에서 컬럼추가시에는 값이 null이기 때문에
  -- not null 조건을 주게되면 실행되어지지 않는다.
  -- 그래도 not null로 하고 싶으면 dafault값을 넣어서 실행하면된다.
--3. 컬럼의 데이타타입 변경
alter table sawon
modify (sano number(4));
 
----- 테이블명 변경하기 -----
rename sawon to jikwon; -- 테이블명을 변경한다.

select * from sawon; -- 기존에 쓰던 이름은 사용이 안된다.
select * from jikwon;

----- 컬럼명 변경하기 -----
alter table sawon
rename column jik to job;

alter table sawon
rename column job to jik;

----- 제약조건명 바꾸기 -----
alter table sawon
rename constraint sawon_jik_ck to sawon_jik_check; -- 제약조건명 변경 완료

----- 테이블에 주석문 달기 -----
comment on table sawon
is '우리회사 사원정보테이블';

select * from user_tab_comments; -- 해당 유저가 사용하는 테이블의 comment(주석문)을 확인할수 있다.

----- 컬럼명에 주석문 달기 ------
comment on column sawon.mgr is '관리자 사원번호';

select *
from user_col_comments
where table_name='SAWON' -- 유저가 사용하는 테이블중 원하는 테이블의 컬럼 내용확인

----- 행 삭제하기 -----
select * from buseo;

delete buseo where buno=104; -- 참조되어진 자식테이블에 해당하는 fk값이 없으므로 삭제가 된다.
delete buseo where buno=101; -- 그러나 101은 자식테이블에서 fk에 값이 있으므로 삭제가 안된다.
                             -- 삭제를 하려면 제약조건을 삭제 또는 비활성화 해야한다.

/*//// 생성한 salesman으로 접속하여 실행 ////*/
-- 테이블 생성하기 --

--- 1 : 다 관계의 테이블에서 먼저 1(부모) 테이블을 생성해야한다.
drop table buseo;

create table buseo
(buno number(3),
buname varchar2(20) constraint buseo_buname_nn not null
-- 컬럼레벨 constraint : 컬럼안에 주어지는 제약조건을 말한다.
-- 그냥 not null 이라해도 되지만 차후 유지 관리를 위해서 이름을 지어서 not null을 해준다.
,addr varchar2(100)
,constraint buseo_buno_pk primary key(buno)); -- constraint 제약 조건
-- Table level Constraint : 컬럼다음에 오는 제약조건을 말한다.

-- constraint(제약조건)의 종류 --
-- 1. primary key
-- 2. unique
-- 3. foreign key
-- 4. check
-- 5. not null

----- 제약조건을 조회한다. -----
select *
from user_constraints;

----- buseo 테이블에 데이터입력 -----
insert into buseo values(101,'총무부','서울');
insert into buseo values(102,'연구부','서울');
insert into buseo values(103,'영업부','수원');
insert into buseo values(104,'생산부','인천');

select *
from buseo;
commit;
insert into buseo values(104,'생산부','인천'); -- buno는 이미 pk로 지정했기때문에 같은 번호가 올 수 없다.
insert into buseo values(105,null,'인천'); -- buname은 not null로 지정했기 때문에 null값이 될수가 없다.
insert into buseo values(105,'인사부',null); -- addr는 null 값이 가능하다.
rollback;
insert into buseo values(105,'인사부'); -- 컬럼수가 맞지않아서 자료추가가 안된다.
insert into buseo(buno,buname) values(105,'인사부');
      -- 이와같이 두개의 컬럼만 지정하면 두개컬럼값만 지정할 수 있다.
rollback;

--- 사원 테이블을 생성하자 (자식테이블)
create table sawon
(
sano number(4) constraint sawon_sano_nn not null,
      -- pk로 지정하면 굳이 not null을 안해도 되지만 해주는것을 권장한다.
      -- 테이블을 복사하면 not null제약만을 제외하고
                -- 나머지 모든 제약조건과 default 값은 복사가 안 되어진다.
                -- 그래서 not null을 붙여주는것을 권장한다.
saname varchar2(10) constraint sawon_saname_nn not null,
jubun varchar2(13) constraint sawon_jubun_nn not null,
jik varchar2(10) default '사원', -- default 값을 넣어주면 값이 지정되지 않으면 해당 default값이 된다.
sal number(4),
comm number(4),
hiredate varchar2(10) default to_char(sysdate,'yyyy-mm-dd'),
buno number(3), -- 부모테이블에서의 fk로 작용하기 때문에 부모테이블의 pk와 동일(number(4))해야한다.
mgr number(4), -- sawon 테이블의 sano의 fk로 작용하기때문에 sano와 동일하게 해야한다.
constraint sawon_sano_pk primary key(sano),
constraint sawon_jubun_uk unique(jubun),
       -- unique key는 자동 not null이 되지 않기 때문에 컬럼생성시 not null 조건을 넣어야한다.
constraint sawon_jubun_ck check(to_number(jubun) > 0 and
                 substr(jubun,7,1) in ('1','2','3','4') and length(jubun)=13),
constraint sawon_jik_ck check(jik in ('사장','부장','과장','대리','사원')),
constraint sawon_sal_ck check(sal between 1000 and 5000 and sal > comm),
constraint sawon_comm_ck check(comm between 0 and 2000),
constraint sawon_hiredate_ck check(extract(year from to_date(hiredate,'yyyy-mm-dd')) >= 1980),
constraint sawon_buno_fk foreign key(buno) references buseo(buno),
       -- references에서 정해준 테이블의 컬럼값을 현재테이블 컬럼에 fk로 지정한다.
constraint sawon_mgr_fk foreign key(mgr) references sawon(sano)
);

select *
from sawon

insert into sawon values
(1001,'백승만','7910121234567','사장',5000,1000,'1980-01-05',101,null);

insert into sawon values
(1002,'윤봉길','8102201234567',default,3000,default,default,103,1001);

insert into sawon(sano,saname,jubun,sal,buno,mgr) values
(1003,'엄정화','7410102234567',2000,103,1001);
--/// 테이블네임다음에 컬럼명을 명시해 주지 않으면 모든 컬럼값을 넣어줘야 하는데
--    해당 컬럼값에 default를 명시해 주었으면 default값이 들어오고
--    모든 컬럼값을 넣기 힘들면 원하는 컬럼만 정해주고 넣어도 된다.

--|| default로 정의되어진 값을 변경하고자 한다.
select column_name, data_default
from user_tab_cols
where table_name='SAWON';  -- 사원테이블의 각 컬럼별 default값을 보여준다.
 
alter table sawon       -- alter table 테이블명 modify 컬럼명 default 원하는값;
modify comm default 0;  -- 위와같이 실행하면 해당컬럼의 default값이 변경된다.

---- 생성되어진 sawon테이블의 재약조건을 조회 ----
select *
from user_constraints
where table_name='SAWON'

select *
from user_cons_columns
where table_name='SAWON' -- 컬럼의 제약조건을 보여준다.

select B.constraint_name, A.column_name,B.constraint_type, B.search_condition, B.status
from user_cons_columns A join user_constraints B
on A.constraint_name = B.constraint_name
where A.table_name = 'SAWON';  -- 해당 테이블의 제약조건 리스트를 보여준다.

---- 제약조건을 제거하거나 수정하기 ----
--//. 제약조건을 수정하려면 먼저 해당 제약조건을 제거하고 새로이 추가해야한다.
alter table sawon
drop constraint sawon_jik_ck; -- 원하는 제약조건 삭제

alter table sawon
add constraint sawon_jik_ck check(jik in('사장','부장','과장','대리','주임','사원')); --제약조건추가

---- not null 제약 제거 및 추가하기 ----
alter table sawon
drop constraint sawon_saname_nn; -- not null 제약을 삭제함으로 제약조건 삭제
---- 또는
alter table sawon
modify saname null; -- null 값을 허용해서 not null 제약을 없앤다.
--- 다시 추가하기
alter table sawon
add constraint sawon_saname_nn saname not null;  -- 오류 문법이다.

alter table sawon
modify saname constraint sawon_saname_nn not null; -- not null 제약은 항시 이렇게 추가해야한다.

----- 제약조건의 활성화/비활성화 -----
--1. 비활성화
alter table sawon
disable constraint sawon_saname_nn; -- 해당 제약조건을 비활성화 한다. (존재는 하나 사용 안하게된다.)
--2. 활성화
alter table sawon
enable constraint sawon_saname_nn; -- 해당 제약조건을 다시 활성화 시킨다.

alter table buseo
disable constraint buseo_buno_pk; -- 해당 문법은 실행이 되지 않는다.

select B.constraint_name, A.column_name,B.constraint_type, B.search_condition, B.status
from user_cons_columns A join user_constraints B
on A.constraint_name = B.constraint_name
where A.table_name = 'BUSEO';

select *
from user_constraints
where table_name = 'SAWON' and constraint_type='R';

select *
from user_cons_columns
where constraint_name = 'BUSEO_BUNO_PK'
------ PK 제약조건은 FK와 참조되어지기 때문에 그냥은 지워지지 않는다.
alter table buseo
disable constraint buseo_buno_pk cascade; -- cascade를 해주면 PK,FK둘다 비활성화가 되어진다.

alter table buseo
enable constraint buseo_buno_pk; -- cascade를 넣지않고 enable 하면 다시 활성화 되어진다.
alter table sawon
enable constraint sawon_buno_fk; -- 부모테이블에 pk를 먼저 활성화 한뒤 자식테이블의 fk를 활성화 한다.

/* SYS로 접속하여 실행 */
--- TABLE CREATE ---
-- 1. 테이블 스페이스 생성
create tablespace develtbs
datafile 'c:\data\develtbs01.dbf' size 2048k -- size는 64kb의 배수로 하는것이 좋다.
extent management local
segment space management auto;
--|| 생성되어진 tablespace 조회
select *
from dba_data_files;

-- 2. user create
create user orauser1 identified by pass123 --user만들기
default tablespace develtbs -- 위에서 생성한 tablespace사용
temporary tablespace temp; -- temp는 유저가 oder by를 할때 사용할 공간을 설정해주는 것이다.
--|| 연결권한을 줘야 접속이 가능하다.
grant create session to orauser1;
--|| 새로만들어진 계정으로 접속하기
create table testtbl
(no number, name varchar2(10)); -- 권한이 없어서 테이블 생성이 안된다.
--|| orauser1 자신에게 부여되어진 system 권한을 조회
select *
from user_sys_privs;
--|| sys로 다시 접속하여 테이블 생성 권한을 준다.
grant create table to orauser1;
--|| 새로운 유저로 접속하여 권한을 조회하면 권한이 되있다
--|| 그리고 테이블을 생성하면 할당량이 주어지지 않아서 또다시 테이블 생성이 안된다.
select *
from user_users; -- 테이블 저장하는 테이블 스페이스 조회

select *
from user_ts_quotas; -- ts(tablespace)의 공간조회
--|| sys로 할당량을 준다.
alter user orauser1
quota 1024k on develtbs
quota 1024k on temp;
--|| 다시 새로운 유저로 공간조회를 하면 공간이 주어지게된다. 그리고 테이블도 생성이 된다.
create table testtbl
(no number, name varchar2(10));  -- 이것은 아무이상없이 생성이 된다.

insert into testtbl values(1, '홍길동');
select * from testtbl; -- 자료를 넣어주고 select하면 테이블이 생성된것을 확인할수 있다.

create table testtbl2
(name varchar2(10)) tablespace develtbs;
  
create table testtbl3
(name varchar2(10)) tablespace users; -- 이것은 users에 테이블 생성이 가능하도록 주어지지 않았기때문에
                                      -- 생성이 되지 않는다. sys가 권한을 주어야 한다.
                                      -- tablespace users라고 입력을 하지 않게되면 해당 테이블은
           -- 처음유저 생성시 sys가 정해준

테이블스페이스에 저장이 되게 된다.
--|| sys로 users테이블스페이스에 무제한 할당량을 주어본다.        

  
alter user orauser1
quota unlimited on users;
--|| 다시 새로 생성한 유저로 와서 테이블을 생성해보자 
create table testtbl3
(name varchar2(10)) tablespace users;

select * from user_tables; -- 사용하는 테이블을 검색해보면 사용하는 테이블 수와
                           -- 테이블 스페이스도 확인 할 수 있다.
-------------------------------------------------------------------------------------------
=============================  개 발 자 용  U S E R 생 성

=================================
-------------------------------------------------------------------------------------------
/* sys로 접속하여 다음과 같이 생성을 한다. */
create user javauser1 identified by pass123
default tablespace develtbs
temporary tablespace temp;

grant connect, resource to javauser1;

select * from dba_roles;  -- 권한 그룹을 표시해 준다.

select *
from dba_sys_privs
where grantee in ('CONNECT','RESOURCE'); -- 해당 권한 그룹(ROLE)에서 가지고 있는 권한(PRIVS)을 보여준다.

/* 생성한 유저로 접속하여 다음과 같이 해보자 */
select * from user_sys_privs; -- 유저가 가진 권한을 보여준다.

create table mytab1
(name varchar2(10));  

create table mytab2
(addr varchar2(20)) tablespace users;   -- 위 두개의 테이블은 별도의 권한을 주지 않았는데도 생성이 된다.

select * from user_tables;

select * from user_role_privs;  -- 유저에게 부여된 role(권한그룹)을 보여준다.

select *
from role_sys_privs
where role in ('CONNECT','RESOURCE')  -- role안에 포함되어진 권한(privs)들을 보여준다.
--// 아무 무리 없이 테이블이 생성된다. 이러한 이유는 sys에서 유저 생성시 connect, resource라는
--   권한 그룹(ROLE)에 대하여 권한을 주었기 때문에 그 권한 그룹안에 포함되어진 권한(privs)들이 자동적으로
--   적용이 되어 일일이 하나씩 권한을 주지 않아도 된다.

/*/////// sys로 접속하여 실행 ///////*/
--- salesman이라는 사용자를 제거 ---
drop user salesman; -- 삭제가 안된다.  cascade를 하라고 나온다.
drop user salesman cascade; -- cascade를 넣어주면 유저 소유의 object를 삭제후 유저를 삭제한다.
             -- 단, 삭제할시엔 해당 유저를 접속종료한뒤 삭제한다.
             
-- Tablespace를 제거한다. --
select *
from dba_data_files; -- 현재 tablespace로 사용하는 파일들을 보여준다.

drop tablespace salestbs
including contents and datafiles; -- 원하는 tablespace 파일을 삭제한다. (절대 탐색기에서 임의로 지우지 말것)

--- ○ 데이터 조작어
---- insert, update, delete commit, rollback
---- merge 명령어 : A서버에서 B원격서버에 A서버의 내용을 동기화할때 사용한다.
update emp set ename='백승익'
where ename='KING'

select * from emp
where deptno = 10;
--// 다른 오라클 서버에 연결할 수 있도록 datebase link를 생성한다.
--- 1. 접속할 서버의 net service 명을 생성한다.
--- 2. database link를 생성한다.
create database link boaeoracle
connect to scott identified by lion
using 'FIREWALLBOAE' -- FIREWALLBOAE라는 넷서비스로 연결된 오라클서버에 대해 boaeoracle이라는 이름으로
                     -- 해당 서버에 scott계정에 접속하는 database link를 생성한다.
--/// boaeoracle이 database link 명이다.
select * from emp
where deptno = 10; -- 자신의 로칼 데이타베이스

select * from emp@boaeoracle -- 원격 오라클 서버이다.
where deptno = 10; -- 생성한 다른 오라클 서버 데이타베이스 자료 출력
------ 새로운 테이블 생성 ------
create table reservation
(reservno number, name varchar2(10)
,tel varchar2(13)
,constraint reservation_reservno_pk primary key(reservno)) tablespace users

select *from reservation;

select *from reservation@boaeoracle;
--- merge 사용하기
insert into reservation values(1,'백승익','010-6275-2735');

merge into reservation@boaeoracle R
using reservation L
on (L.reservno = R.reservno)  -- 로컬서버 자료와 원격서버 자료와 동일한게 있는지 여부 묻기
               -- 반드시 고유한 컬럼으로 정해야 한다.
when matched then                           -- 동일하다면 다음 자료로 업데이트 해라
  update set R.name=L.name, R.tel=L.tel
when not matched then                       -- 동일하지 않다면 다음 값을 넣어라
  insert(reservno, name, tel)
  values(L.reservno, L.name, L.tel);
--/// on 조건에 match되면 matched then 다음을 실행하고 (업데이트)
--              match가 안되면 not matched then 다음을 실행하게 된다. (새로운값 입력)
 
insert into reservation values(2,'이영애','011-6275-8699'); -- merge 실행

update reservation set name='이미자', tel='018-777-8974'
where reservno=2; -- merge 실행
-----------------------------------------------------------------------------------------
create table empcopy
tablespace users
as select * from emp;

select * from empcopy;
---- delete : 해당 부분을 삭제 한다.
delete empcopy where deptno=30;
---- rollback : commit한 이후로 실행되어진 DML(insert, update, delete, merge)의 명령을 취소한다.
---- commit : DML문장의 명령을 적용시킨다.
---- merge : DML문을 실행시키면 실행되어진 DML문의 해당행은 자동으로 Exclusive lock(배타적잠금)이 걸린다.
update empcopy set comm=120 where deptno=20;
    -- 현문장은 아직까지  commit이나 rollback을 하지 않은 상태이다. 그러므로 deptno가 20번인 행들은
    -- 배타적 잠금이 걸린다. 그래서 다른 세션에서는 deptno20번인 행들을 update하기전의 값으로
    -- select는 가능하지만 deptno 20번인 행들은 DML문을 할 수가 없다. 대기상태에 빠진다.
    -- 그러나 원래세션에서 update한 문장(모든DML)을 commit이나 rollback하면 대기상태에 빠진 다른
    -- 세션에서 실행한 DML문장은 대기상태에서 빠져나와 자동으로 실행되어진다.

 --// 그래서 잠금(lock)의 종류는 2가지가 있다. 첫번째가 방금해본 exclusive lock(배타적 잠금)이고
 --// 두번재가 share lock(공유잠금)이 있다.
 --// exclusive lock(배타적 잠금)는 DML문장을 했을때 DML문장을 실ㄹ행한 세션에서 Commit이나
 --// Rollback문을 해야만 다른 세션에서 잠금이 풀리어 또 다른 DMl문장을 할 수 있는 것이고
 --// share lock(공유잠금)은 select 문을 했을때 결과물 데이터가 완료되어 추출되기전까지
 --// 자동적으로 해당 테이블이 잠기는 것을 말한다. 하지만 select 결과물이 나오기만 하면
 --// 자동적으로 share lock(공유잠금)은 풀립니다.
 
 --// 그렇다면 select 되어진 데이터를 계속해서 잠금유지상태를 원하고자 한다면 어떻게 할까?
 --// 예를들어 은행에서 돈을 인출할때 조회후 인출과 같은 경우이다.
create table saving
(geno number, jango number, constraint saving_geno_pk primary key(geno)) tablespace users;

insert into saving values(1,2000);
insert into saving values(2,3000);

select * from saving;
----- 잔고가 얼마 있는지 조회후 인출을 하려고 한다.
----- 즉, 먼저 select를 한다음에 update를 하려고 한다.
select jango
from saving
where geno=1;

update saving set jango=jango-500     /* 다른곳에서 update saving set jango=jango-2000 where geno=1; */
where geno=1                          /* 을 했다면 돈이 빠지지 않게된다. */
------- 그럼 조회하면서도 잠금을 유지 하려면 다음과 같이 한다.
select jango
from saving
where geno=1
for update;   /* 이렇게 하게되면 commit이나 rollback을 하기전까진 다른곳에서 자료 변경이 안된다. */

select *
from BALJEON;
--|| 발전일별로 누적량을 보여라 ||--
select to_char(balday,'yyyy-mm-dd') "발전일", balyang "발전량",
    sum(balyang) over(order by balday) "누적량"
from BALJEON;
--|| 서브상관쿼리로 발전일별 누적량 출력 ||--
select to_char(balday,'yyyy-mm-dd') "발전일",   -- 그룹지어진 컬럼 갯수만큼 출력을 해야한다.
    balyang "발전량",                        -- 컬럼수가 초과되면 오류가 난다.
    (select sum(balyang) from BALJEON T2 where T2.BALDAY <= T1.balday) "누적량"
from BALJEON T1
order by 1;
----// 위쿼리문에서 바깥쪽에 있는부분을 외부쿼리, 안쪽에 있는부분을 내부쿼리라 하고
--     외부쿼리문의 컬럼명이 내부쿼리의 조건절에 사용되어질때 이것을 상관서브쿼리(서브상관쿼리)라 한다.
------------------------------------------------------------------------------------------------------
select *
from OLDSALESINFO;

select *
from SALESINFO;
--|| 제품별로 판매량이 최고이였던 날짜와 총판매량을 나타내시오. ||--
select jpname "제품명", to_char(saleday,'yyyy-mm-dd') "판매일자", sum(su) "판매량"
from
(
select *
from OLDSALESINFO
union
select *
from SALESINFO
) T1
group by jpname, to_char(saleday,'yyyy-mm-dd')
having sum(su) = (
             select max(sum(su))  -- max만 하게되면 제품전체에 대한 max값만 나오기때문에
      from                 -- 하단에 where로 조건을 넣어서 제품별 max값이 나오게 해야한다.
      (
        select *
           from OLDSALESINFO
        union
        select *
        from SALESINFO
      ) T2
      where T2.jpname = T1.jpname -- 제품별로 최대치가 나오게 하기위한 조건부
      -- 외부쿼리에 컬럼이 내부쿼리 조건에 포함
      group by jpname, to_char(saleday,'yyyy-mm-dd')
     );
--// 외부쿼리 컬럼이 내부쿼리 조건절에 포함된 경우도 상관서브쿼리라 할 수 있다.
--------------------------------------------------------------------------------------------
---||| 연습문제 |||---
--문제 13. Emp테이블에서 부서별로 월급이 평균 월급보다 높은사원을 부서번호, 이름, 급여 출력.
select deptno, empno, ename, sal
from EMP E1
where sal > (
        select avg(sal)
    from EMP E2
        where E2.deptno = E1.deptno
       )
order by deptno
--문제 14. Emp테이블에서 업무별로 월급이 평균 월급보다 높은사원을 부서번호, 이름, 급여 출력.
select job, empno, ename, sal
from EMP E1
where sal < (
        select avg(sal)
    from EMP E2
        where E2.job = E1.job
       )
order by job
--★★ 교제 165PAGE EXISTS ★★-- 중요
--// exists : 조건에 만족하는 행이 존재하면 참이고, 존재하지 않으면 거짓
select *
from EMP
where exists(select * from dept where deptno = 10); -- 참인 예제

select *
from EMP
where exists(select * from dept where deptno = 50); -- 거짓 예제

--// not exists : 조건에 만족하는 행이 존재하면 거짓, 존재하지 않으면 참
select *
from EMP
where not exists(select * from dept where deptno = 10); -- 거짓 예제

select *
from EMP
where not exists(select * from dept where deptno = 50); -- 참 예제
---||| 주소지가 DALLAS 와 CHICAGO에 근무하는 사원의 부서번호, 사원번호, 사원명, 급여를 출력.
------// 서브쿼리에 in이 나오면 쿼리 처리 속도를 위해 exists를 써주는게 좋다.
select deptno, empno, ename, sal
from emp
where deptno in (select deptno
        from DEPT
        where loc in('DALLAS','CHICAGO') );
       
select deptno, empno, ename, sal
from emp E
where exists(select deptno
      from DEPT D
      where loc in('DALLAS','CHICAGO')
      and D.deptno = E.deptno);
--|=| 참고사항 : 서브쿼리보다는 join이 처리속도가 더 빠르다.
select D.dname E.empno, E.ename, E.sal
from emp E, dept D
where E.deptno = D.deptno and D.loc in ('DALLAS','CHICAGO');

select D.dname, E.empno, E.ename, E.sal
from
(select empno, ename, sal, deptno from EMP) E,
(select dname, deptno from DEPT where loc in ('DALLAS','CHICAGO')) D
where E.deptno = D.deptno;
-- 위 두개의 쿼리문은 출력값은 같으나 위쪽쿼리는 emp,dept테이블의 column, row의 모든값을
-- 표시하기때문에 아래쿼리문처럼 미리 필요한 column, row만(필요없는 부분 출력안함) 뽑아서
-- 쿼리문을 작성하면 처리 속도가 빨라진다. (대용량 DB에 매우 유용하다.)

--// 서브쿼리는 where절에 또하나의 select 문이 들어가는것을 말한다.
---|| 30번 부서중에서 sal가 가장 최고인 사원의 모든정보를 추출하시오
select *
from emp
order by sal desc;

update emp set sal=2850  -- 원래는 2975
where empno = 7566;

select *
from emp
where sal = (select max(sal) from emp -- 쿼리문에서 실행된값이 다른 부서에도 있으면 출력이 된다.
        where deptno = 30);      -- 이유는 전체문장에서 30번 부서만뽑는 조건이 없기때문이다.
       
select *
from emp
where deptno = 30 and
   sal = (select max(sal) from emp
          where deptno = 30);
-----------------------------------------------------------------------------------------------
---|| adams 가 근무하는 부서에서 그 부서의 평균급여보다 많은 사원의 모든정보를 추출
select *
from emp
where deptno = (select deptno from emp where ename='ADAMS') and
   sal > (select avg(sal) from emp
         where deptno = (select deptno from emp where ename='ADAMS'));
---|| JONES와 CLARK이 근무하는 부서와 동일한 부서에 근무하는 사원들의 모든정보를 추출.
select *
from emp
where deptno = any (select deptno from emp where ename in ('JONES','CLARK'));
---|| 각 부서별로 급여가 최저인 사원들의 부서번호, 사원명, 급여를 추출하여라.
----/// 아래 문장은 부서번호가 정의되지 않았기 때문에 잘못된 표현이다.
select deptno, ename, sal, empno
from emp
where sal in (select min(sal) from emp group by deptno)
order by deptno;

update emp set sal=1300  -- 원래는 2975
where empno = 7566;
update emp set sal=1300  -- 원래는 1250
where empno = 7654;
---/// 두개의 조건을 동시에 비교해서 해야 정확한 답이 나온다.
---/// 이런경우를 다중열 서브쿼리라 하여 Pairwise되었다고 한다.
select deptno, ename, sal
from emp
where (deptno, sal) in (select deptno, min(sal) from emp group by deptno)
order by deptno;
--|| EMP 테이블에서 관리자로 등록되어진 사원의 정보를 추출하시오.
select *
from emp
where empno in (select mgr from emp);

select *
from emp
where empno = any (select mgr from emp);
--|| EMP 테이블에서 관리자로 등록되어지지 않은 사원의 정보를 추출하시오.
select *
from emp
where empno not in (select mgr from emp); -- 이렇게하면 서브쿼리에 null값때문에 값이 나오질 않는다.
--/// 다음과 같이 풀이가 된다.
select *
from emp
where empno ^= 7566 and empno ^= 7698 and empno ^= 7782 and
   empno ^= 7788 and empno ^= 7839 and empno ^= 7902 and empno = null
   -- null은 존재하지 않는 값이기때문에 부정을 해도 null이 된다. 그래서 null부분을 빼야한다.
select *
from emp
where empno not in (select mgr from emp
          where mgr is not null); -- 다음과 같이 null부분을 없애줘야 정확한 값이 나온다.
select *
from emp
where empno ^= all (select mgr from emp     -- not in 은 ^= all 과 같다.
          where mgr is not null);

--/// 누적 예제 ///--
create table baljeon
(balday date, balyang number) tablespace users;

insert into baljeon values('2007-02-01',30);
insert into baljeon values('2007-02-02',25);
insert into baljeon values('2007-02-03',20);
insert into baljeon values('2007-02-04',15);
insert into baljeon values('2007-02-05',10);

--/// 날짜별 순차적인 누적량을 구하기 위해선 더해야할 행수는 해당 테이블의 총행수(count값)을 넣어주면된다.
--/// 또는 rows 부터 그이하를 생략하면 된다.
select count(*) from baljeon;

select balday "발전일자", balyang "발전량",
    sum(balyang) over(order by balday) "누적량"
from baljeon;
-----------------------------------------------------------------------------------------
insert into baljeon values('2007-03-01',10);
insert into baljeon values('2007-03-02',20);
insert into baljeon values('2007-03-03',30);
insert into baljeon values('2007-03-04',40);
insert into baljeon values('2007-03-05',50);
-----------------------------------------------------------------------------------------
select * from baljeon;

select balday "발전일자", balyang "발전량",
    sum(balyang) over(partition by to_char(balday,'yyyy-mm') order by balday) "누적량"
    -- partition by to_char(balday,'yyyy-mm') 원하는 날짜별로 누계를 구한다.
from baljeon;

---- cross join
select *
from emp, dept;

select *
from emp cross join dept; -- SQL 1999 CODE

---- natural join (inner join과 같고, Oracle에만 존재한다.)
---- natural join 테이블에 allias(별칭) 를 주면 안된다. (A,B 두테이블에 공통 컬럼이 한개만 존재할때 가능하다.)
select deptno, dname, ename, job
from emp natural join dept;

---- using 절 : 테이블에 Allias를 쓰면 안된다. (Oracle에만 존대한다.)
select deptno, dname, ename, job
from emp join dept
using (deptno);  -- 공통 컬럼을 지정해준다. // using(공통컬럼) //