[SQL] 여러문제

 

사원정보가 중복되서 들어가있는경우

중복된 데이터를 걸러보자

--테이블 생성
create table tab01(
    id      number,
    eno     number,
    name    varchar2(20)
);

--필드 추가
insert into tab01 values(1, 10, '홍길동1');
insert into tab01 values(2, 10, '홍길동1');    --삭제대상 
insert into tab01 values(3, 20, '홍길동2');
insert into tab01 values(4, 20, '홍길동2');    --삭제대상
insert into tab01 values(5, 20, '홍길동2');    --삭제대상
insert into tab01 values(6, 30, '홍길동3');
insert into tab01 values(7, 30, '홍길동3');    --삭제대상
insert into tab01 values(8, 40, '홍길동4');
insert into tab01 values(9, 50, '홍길동5');
insert into tab01 values(10, 50, '홍길동5');   --삭제대상

tab01의 eno가 같은 것들중에서 (group by)

아이디가 작은것을 골라준다.(select MIN(id))

그리고 그것들을 뺀 나머지를(where not id in)

tab01에서 출력해준다.

select *
    from tab01
    where not id in(
        select MIN(id)
        from tab01
        group by eno
    );
        

삭제할 데이터를 출력했으니 위의 데이터를 삭제 한 후 삭제된 데이터를 출력해보자

--뽑아준 데이터들을 삭제해줄것이다.
delete
    from tab01
    where not id in(
        select MIN(id)
        from tab01
        group by eno
    );

-- 제대로 삭제 되었는지 확인
select * from tab01;

---

sum over 사용

 

 

 

 

set timing on;

시간 측정을 하는 기능

set timing off;

off를 해줘야 기능이 꺼진다. 코드를 지워도 계속 진행됨

---

홍길동의 급여보다 많은사람을 출력

우선 EMP로 안에 속성값을 넣어준다.

drop table EMP;
create table EMP(
    name    varchar2(20),
    sal     number
);

insert into EMP values('홍길동', 300);
insert into EMP values('이순신', 500);
insert into EMP values('김갑수', 250);
insert into EMP values('갑을병', 700);

-- 홍길동보다 급여가 큰사람들을 출력
select * 
    from EMP
    where sal > (
        select sal
        from EMP
        where name = '홍길동');

view01을 만들어준다음에 안에 위에서 만든 서브쿼리문인 홍길동의 급여를 넣어준다.

그리고 제대로 들어갔나 확인을 해본다.

-- 혹시 view01이 있을 수 있으니 먼저 삭제해준다.
drop view view01;

-- view01을 만들어준다.
create view view01
    as (
    select sal
        from EMP
        where name = '홍길동'
    );
    
-- 제대로 들어갔나 확인을 해준다.
select * from view01;

홍길동의 급여값이  들어갔다

view01에 들어간 값은 홍길동의 급여 조건을 대신해주는 코드가 아닌 view01라는 테이블을 만들어준 것이다.

그러므로 첫번째 방법과 같이 그냥 view01이라는 것으로 바꿔서 표현해줄수는 없다.

view01을 불러와서 비교해줘야하므로 두번째와 같은 방법으로 사용해줘야한다.

-- 이 방법은 오류가 뜬다.
select * 
    from EMP
    where sal > (view01);

--이 방법과 같이 view01을 불러와줘야한다.
select *
    from EMP
    where sal > (
        select *
            from view01);

---

무결성

 

데이터에 부여된 규칙을 무결성이라고한다.

데이터에 결점이 없어야한다.

 

 

 

 

drop table tab02;
create table tab02(
    sal     number
    );
    
insert into tab02 values(1);
insert into tab02 values(2);
insert into tab02 values(3);
insert into tab02 values(4);
insert into tab02 values(5);

select * from tab02 t1
    where 3 > (
        select count (*)
        from tab02 t2
        where t2.sal > t1.sal
    );

위의 쿼리문은 서브쿼리문이 없다.

t1에 1이 들어갔을때 t2와 교차가 일어난다.

t1이 1일때 t2가 식에 만족할경우는 4가지이다. 하지만 3보다는 작지 않으므로(where 3 >) 적용되지않는다.

t1이 2일때는 만족할 경우는 3가지이고 마찬가지로 만족되지않는다.

t1이 3일때는 2가지이고 3보다 작으므로 만족되므로 출력된다.

4와 5일때도 만족되므로 출력된다.

그러므로 3,4,5가 출력된다.

 

1등부터 3등까지 공동순위까지 출력해준다.

drop table tab02;
create table tab02(
    sal     number
    );
    
insert into tab02 values(1);
insert into tab02 values(2);
insert into tab02 values(3);
insert into tab02 values(6);
insert into tab02 values(6);
insert into tab02 values(6);
insert into tab02 values(7);
insert into tab02 values(8);

select * from tab02;

select * from tab02 t1
    where 4 > (
        select count (*)
        from tab02 t2
        where t2.sal > t1.sal
    )
    order by t1.sal desc;