Backend Web/SQL · DB 기초

[SQL] Subquery / SET / DML

sofiaaa 2020. 11. 25. 10:09
반응형

 

 7장 Subquery

 

 

--sub query--

--where에서 비교값 써야되는데 비교값을 내가 작성하는 시점에 모른다.

--그래서 비교값을 추출하는 query를 포함시켜야한다. 



select last_name, salary

from employees

where salary > 11000;



select last_name, salary

from employees

where last_name = 'Abel';



--위의 두 문장을 합치는 것

select last_name, salary                    --main query 

from employees

where salary > (select salary

                from employees

                where last_name = 'Abel');  --sub query 중 single sub querry



select last_name, job_id, salary

from employees

where job_id = (select job_id

                from employees

                where last_name = 'Ernst')

and salary > (select salary

                from employees

                where last_name = 'Ernst');

                

select last_name, job_id, salary

from employees

where salary = (select min(salary) --최소 월급 2100

                from employees);

                

select department_id, min(salary)

from employees

group by department_id

having min(salary) > ( select min(salary)

                        from employees

                        where department_id = 50);

                        

select job_id, avg(salary)

from employees

group by job_id

having avg(salary) = (select min(avg(salary)) --19개 그룹에 대한 평균값이 하나의 값으로

                        from employees

                        group by job_id); 

                        

select job_id, avg(salary) --19개 그룹에 대한 평균값 

from employees

group by job_id;



--sub에서 결과가 19개 나오기 때문에 error

select employee_id, last_name

from employees

where salary = (select min(salary) 

                from employees

                group by department_id); --error --multi sub query

                

--sub query 값이 여러개 일때 in, any, all 사용 가능                

--in 연산자 --> 결과값 여러개 받기

select employee_id, last_name

from employees

where salary in (select min(salary) 

                from employees

                group by department_id);

       

--any 연산자 --> sub에서 나오는 것중에 equal이면 true

select employee_id, last_name

from employees

where salary =any(select min(salary) 

                from employees

                group by department_id);



--any

select employee_id, last_name, job_id, salary

from employees

where salary <any (select salary

                    from employees

                    where job_id = 'IT_PROG') --5명 9000보다 작은 거 찾기

and job_id <> 'IT_PROG'; --programmer 제외



--all

select employee_id, last_name, job_id, salary

from employees

where salary <all (select salary

                    from employees

                    where job_id = 'IT_PROG') --5명 4200보다 작은 거 찾기

and job_id <> 'IT_PROG'; --programmer 제외



--any ~하거나 //9000보다 덜 받는 걸 찾겠다. 

--all ~이고 //4200보다 덜 받는 걸 찾겠다.




--any 

--sub이 null이면 결과는 null

select last_name, salary, department_id

from employees

where salary > any(select salary

                    from employees

                    where employee_id = 1000); --sub가 null

--all            

--sub이 null이면 where 절 전부 가져온다

select last_name, salary, department_id

from employees

where salary > all(select salary   -- =all, <all 어떤 연산자가 와도 결과 동일

                    from employees

                    where employee_id = 1000); --sub가 null



--in

--in 또한 sub가 null이면 결과는 null

select last_name, salary, department_id

from employees

where salary in (select salary --어떤 연산자가 와도 동일

                    from employees

                    where employee_id = 1000); --sub가 null

                    

--all 제외하고 sub에 null사용하면 sub가 null이면 return 되는 것이 없다.



---------------------------------------------------------------------                    

--exists--

select count (*) 

from departments;  --27개 부서



--exsits랑 쓰게 되면 from 절에 명시하지 않은 칼럼을 쓰지 않을 수 있다.

--하지만 main querry에는 있어야 한다. 

--가방과 신발 중에 팔린게 뭐지? 등을 찾고자 할 때 쓰는 것



--11개

--요구사항 - 사원이 배치된 부서가 어디에요?

select department_id, department_name

from departments d

where exists (select *

                from employees e

                where e.department_id = d.department_id); --sub가 단독으로 실행이 x

--그래서 여기엔 120번 부서 x , null은 버려진다.



--11개

--요구사항 - 사원이 배치된 부서가 어디에요?

select count(distinct department_id) --distinct : 중복 제거

from employees

where department_id is not null; --11개




select last_name, department_id, department_name

from employees right outer join departments

using (department_id); --120번 이후부터 사원이 없는 부서



-----------------------------------------------------------------

--table 중 하나가 null인 경우 



--요구사항 : manager 찾기

--어느 하나라도 비교했을 때 true이면 true 

select emp.last_name, employee_id

from employees emp

where emp.employee_id in (select mgr.manager_id

                            from employees mgr); --18개 -- sub에 null이 하나 존재

        

select emp.last_name, employee_id

from employees emp

where emp.employee_id not in (select mgr.manager_id

                            from employees mgr

                            where manager_id is not null); --89 --위에오 합쳐서 107로 전체



--요구사항 : manager가 아닌 평사원 찾기

--not은 in의 여집합     

--평사원이 안나옴,-> null때문에 

select emp.last_name

from employees emp

where emp.employee_id not in (select mgr.manager_id

                                from employees mgr); -- sub에 null이 하나 존재



-- =any == in  //  어떤것 하나라도 같으면 같다

select emp.last_name, employee_id

from employees emp

where emp.employee_id =any (select mgr.manager_id

                           from employees mgr);

-- <>all == not in    // 모두와 달라야한다

select emp.last_name, employee_id

from employees emp

where emp.employee_id <>all (select mgr.manager_id

                            from employees mgr); --18개

                            

--이거 다시 복습하기 **                          

--null and true nll

--nall and null null

--null or 




x = any (1, 2, 3): 1, 3

x <> all(1, 2, 3) : 5 

--x가 5이면 true x가 3이면 false



--x값 1일때 true or flse or false or null =true

--x값 5일때 false or false or false or null =null

--x값 3일때 false or false or true or null =true

x = any (1, 2, 3, null): 1, 3

--x값 1일때 false and true and true and null = false 1 버려짐

--x값 5일때 true and true and true and null = null 5버려짐

--x값 3일때 true and true and false and null = false 3버려짐

x <> all(1, 2, 3, null) : 



--애초에 sub에 null을 넣지 않는게 좋다

select emp.last_name

from employees emp

where emp.employee_id in(

        select mgr.manager_id

        from employees mgr

        where manager_id is not null); --서브커리에 null이 없도록 하는게 좋다

 

 8장 SET

 

 

--집합 연산자--

--union 합집합 //  모두 합치는 것     union all 중복 제거 안하는 것

--intersect 교집합 // 중복만 내 테이블에 

--minus 차집합 // 특정 테이블 선택하고, 그 테이블에서 다른 테이블과 중복된거 빼고 나머지만 얻는 것



--직업을 두번 바꾼것 programmer, 영업



--집합 연산자--

--칼럼의 갯수가 아래 위 같아야 한다.

--순서대로 데이터 타입도 같아야 한다.



--115

select employee_id, job_id -- 내 테이블의 칼럼명

from employees

union 

select employee_id, job_id

from job_history;



--중복된 레코드까지 포함 117

select employee_id, job_id

from employees

union all

select employee_id, job_id

from job_history;



--교집합

--레코드 두개 과거의 가졌던 직업을 현재도 가지고 있는 직원 두명

--176번의 현재 직업 SA_REP, 처음에 가졌다가 다른데 갔다가 다시 온 것

--200번의 현재 직업 AD_ASST

select employee_id, job_id

from employees

intersect

select employee_id, job_id

from job_history;



select employee_id, job_id

from employees

where employee_id = 200;



select employee_id, job_id, end_date

from job_history

where employee_id = 200;



--100개 

--job_history에 사원번호가 없는 애들만 가져온 것

--입사한 이후에 직업을 바꾸지 않은 사원들

select employee_id

from employees

minus

select employee_id

from job_history;




--두 테이블의 구조가 다를 때는? null or 0 사용

select location_id, department_name

from departments;



select location_id, state_province

from locations;



select location_id, department_name, null satate_province

from departments

union

select location_id, null, state_province

from locations; --업무 관점에서는 쓰레기



select employee_id, job_id, salary

from employees;



select employee_id, job_id, 0

from job_history;



select employee_id, job_id, salary

from employees

union

select employee_id, job_id, 0

from job_history

order by 2;

 

 9장 DML

 

--DML 데이터 조회 --> select, insert, update, delete

--DCL -> commit rollback

--DDL -> drop, create 자동으로 생성

--insert 레코드에서 필드값 바꾸기

--update

--delete

--transaction 논리적인 작업 단위, commit이라는 것이 나오면 끝 



--query공부할 때는 따로 준비하지 않았었다.

--여기서는 table을 따로 준비한다.

----------------------------------------------------------

--table 삭제

drop table dept; 

drop table emp;



--table 생성

create table dept(

department_id number(4), --숫자 단위

department_name varchar2(30), --30byte 문자 단위

manager_id number(6),

location_id number(4));



create table emp(

employee_id number(6),

first_name varchar2(20),

last_name varchar2(25),

email varchar2(25),

phone_number varchar2(20),

hire_date date,

job_id varchar2(10),

salary number(8,2), --8자리 중에 두자리는 소수점 이하

commission_pct number(2,2), --2자리 중에 2자리 모두 소수점 이하

manager_id number(6),

department_id number(4));



--transaction 시작------------------------------------------------------------

--insert

insert into dept (department_id, department_name, manager_id, location_id)

values (300, 'Public Relation', 100, 1700);



select * from dept;



--1. field value 자체를 넣지 않기

insert into dept (department_id, department_name) --여기에 기입하지 않은 두 개 필드의 값은 null

values (310, 'Purchasing');



--2. filed value에 null 명시

insert into dept 

values ( 320, 'Finance', null, null);



commit; --커밋 완료. 

--transaction 끝------------------------------------------------------



--transaction 시작

insert into emp(employee_id, first_name, last_name,

    email, phone_number, hire_date,

    job_id, salary, commission_pct, 

    manager_id, department_id)

values (300, 'Louis', 'Pop',

    'Pop@gmail.com','010-378-1278', sysdate,

    'AC_ACCOUNT', 6900, null, 

    205, 110);

    

select * from emp;



------------------------------------------------------------------

insert into emp

values (310, 'Jark', 'Klein',

        'Klein@gmail.com', '010-753-4625',

        to_date('FEB 8,2020','MON DD, YYYY'),                        --문자를 날짜 타입으로

        'IT_PROG', 8000, null, 120, 90);

        

insert into emp

values (320, 'Terry', 'Benard',

        'Benard@gmail.com', '010-721-0972', '02-NOV-23',

        'AD_PRES', 5000, .2, 100, 30);

        

commit;

-----------------------------------------------------------------

create table sa_reps(

id number(6),

name varchar2(25),

salary number(8,2),

commission_pct number(2, 2));



select * from sa_reps;



insert into sa_reps(id, name, salary, commission_pct)    --필드 value로 null이 아닌 값을 넣을 필드를 나열 하는 것

    select employee_id, last_name, salary, commission_pct

    from employees

    where job_id like '%REP%';

    

rollback; --insert 내용 없애기



insert into sa_reps

    select employee_id, last_name, salary, commission_pct

    from employees

    where job_id like '%REP%';

    

select * from sa_reps;

commit;

select * from sa_reps;




--------------------------------------------------

--PL/SQL

--sql에 논리적인 문법을 가미한 것

declare

    base number(6) := 400;  --변수 선언

begin

    for i in 1..10 loop --반복 횟수 10

        insert into sa_reps(id, name, salary, commission_pct)

        values(base + i, 'n'||(base + i), base * i, i *0.01); --자동 생성

    end loop;

end;



select * from sa_reps

where id >= 400;



rollback;



select * from sa_reps

where id >= 400;



commit;



select * from sa_reps

where id >= 400;



---------------------------------------------

--update-- 

--필드 값을 수정하는 것, (=레코드를 수정)

select employee_id, salary, job_id

from emp

where employee_id =300;



update emp 

set salary = 9000, job_id = null

where employee_id = 300;



commit;



--sub query 이용해서 update

update emp

set job_id = (select job_id

                from employees

                where employee_id = 205),

    salary = (select salary

                from employees

                where employee_id = 205)

where employee_id = 300;



rollback;



update emp

set (job_id, salary) = 

        (select job_id, salary

            from employees

            where employee_id = 205)

where employee_id = 300;



select employee_id, job_id, salary

from emp

where employee_id = 300;



commit;



---------------------------------------------------------------------

--delete

--from 은 생략 가능

select * from dept;



delete from dept;



delete from dept

where department_id = 300;



rollback;



commit;



select employee_id, department_id

from emp

where department_id = 90;



delete emp

where department_id = ( 

        select department_id

        from departments

        where department_name = 'Executive');

        

commit;



--9장 you에서 더,,,

--제약 조건 객체들까지 삭제 해준다. 

drop table depts cascade constraints;

drop table emps cascade constraints;



create table depts(

department_id number(3) constraint dept_deptid_pk primary key,

department_name varchar2(20));



create table emps(

employee_id number(3) primary key,

emp_name varchar2(10) constraint emps_empname_nn not null,

email varchar2(20),

salary number(6) constraint emps_sal_ck check (salary > 1000),

department_id number(3),

constraint emps_email_uk unique(email),

constraint emps_deptid_fk foreign key(department_id)

    references depts (department_id)); --foreign key에서 refernec하는 속성은 unique 여야 한다.




--제약조건 확인

select * from user_constraints;




--index

--department_id 칼럼에 잇는 필드 값들을 index가 가지고 잇다. ??



insert into depts 

values (100, 'Development');



insert into emps

values (500, 'musk', 'musk@gmail.com', 5000, 100);

commit;



--integrity constraint 

--YOU.EMPS_DEPTID_FK 위반

--emps가 child , depts 부모 

delete depts; 



--unique constraint 

--YOU.DEPT_DEPTID_PK 위반

--100이란 레코드 있는데, 또 100 넣으면 unique 위반

insert into depts values (100, 'Marketing');

insert into depts values (null, 'Marketing');

insert into epms values (501, null, 'good@gmail.com', 600, 100);

insert into emps values (501, 'abel', 'musk@gmail.com', 6000,100);

insert into emps values (501, 'abel', 'good@gmail.com', 900, 100);

insert into emps values (501, 'abel', 'good@gmail.com', 6000,200);



select * from depts;

select * from emps;



create table gu (

gu_id number(3) primary key,

gu_name char(9) not null);



create table dong (

dong_id number(4) primary key,

dong_name varchar2(12) not null,

gu_id number(3) references gu(gu_id) on delete cascade); -- 부모가 살아지면 자식도 연달아 



create table dong2 (

dong_id number(4) primary key,

dong_name varchar2(12) not null,

gu_id number(3) references gu(gu_id) on delete set null);




insert into gu values (100, '강남구');

insert into gu values (200, '노원구');



insert into dong values (5000, '압구정동', null);

insert into dong values (5001, '역삼동', 100);

insert into dong values (5002, '삼성동', 100);

insert into dong values (6001, '상계동', 200);

insert into dong values (6002, '중계동', 200);



insert into dong2

select * from dong;



--강남구가 사라지면, 역삼동, 삼성동도 사라져야 된다.

delete gu

where gu_id =100;



commit;



----------------------------------------------------------

--foreing key 활성화



create table a (

aid number(1) constraint a_aid_pk primary key);



create table b (

bid number(2),

aid number(1), --foreign key 

constraint b_aid_fk foreign key (aid) references a(aid));

--a 가 부모 테이블 



insert into a values(1);



insert into b values (31, 1);

insert into b values (31, 9); --실패



alter table b disable constraint b_aid_fk; --foreing 키를 잠시 수정 못하게 



insert into b values (31, 9);



alter table a enable constraint b_aid_fk; --실패, 9에 해당하는 parent key 발견 x 



alter table b enable novalidate constraint  b_aid_fk; --앞으로 들어오는 foreign key만 작동하겠다



insert into b values (31, 8); --실패, 제약조건 걸린다.





--create table에 sub query 사용



create table sub_departments as

    select department_id dept_id, department_name dept_name

    from hr.departments;

    

select * from sub_departments;



---------------------------------

--alter--



create table users (

user_id number(3));



desc users



alter table users add    --추가 

(user_name varchar2(10));



desc users --user name 칼럼 추가된 것 확인



alter table users modify

(user_name number(7));



desc users --유형 변경 확인



alter table users drop

column user_name;



desc users




insert into users values(1);



alter table users read only; --읽기 전용으로 만들어버림



insert into users values(2); --실패 , update는 허용 x



select * from users; --성공 , 읽기는 가능



alter table users read write;

insert into users values(2);



commit; --transition 종료 --



-------------------------------------------------------------------

--meta table --

--data dictionary 

select constraint_name, constraint_type, table_name 

from user_constraints --제약조건들,, 

where constraint_name not like 'BIN%';

                               --BIN은 쓰레기통에 들어간 것



--table 목록

select tname, tabtype

from tab

where tname not like 'BIN%';
반응형

'Backend Web > SQL · DB 기초' 카테고리의 다른 글

[SQL] SQLD - ORDER BY 1, 2, 3  (0) 2022.03.04
[SQL] SQLD - 정규화  (0) 2022.03.01
[SQL] TABLE / View / Sequence  (0) 2020.11.26
[SQL] Single Function / Group Function / Join  (0) 2020.11.24
[SQL] SELECT / WHERE / ORDER BY / Single Function  (0) 2020.11.24

'Backend Web/SQL · DB 기초'의 다른글

  • 현재글[SQL] Subquery / SET / DML

관련글

댓글

티스토리툴바