07월 30일 화 | SQL활용 09 - SQL 연습 Ⅲ 과 트랜잭션

 

 

 

 

 

문제 1) 학번별 수강신청 총 학점을 구하고 총 학점 순으로 정렬 후 위에서부터 3건만 조회하시오.
--(학번, 이름, 총 학점)

 

 

-- 과목코드별 학점 가져오기

select SU.hakno, SU.gcode, SUB.ghakjum from tb_sugang Su join tb_subject SUB on SU.gcode = SUB.gcode;

 

 

-- 학번별로 총 학점 구하기

select SU.hakno, sum(ghakjum) from tb_sugang SU join tb_subject SUB on SU.gcode = SUB.gcode group by SU.hakno order by SU.hakno;

 

-- 총 학점 순으로 정렬하기

select SU.hakno, sum(ghakjum) from tb_sugang SU join tb_subject SUB on SU.gcode = SUB.gcode group by SU.hakno order by sum(ghakjum);

 

-- 학생이름 가져오고, 행번호 출력하기

select AA.hakno, AA.총학점, STU.uname, rownum from(select SU.hakno, sum(SUB.ghakjum) as 총학점 from tb_sugang SU join tb_subject SUB on SU.gcode = SUB.gcode group by SU.hakno order by sum(ghakjum)) AA join tb_student STU on AA.hakno = STU.hakno where rownum <= 3;

 

-- 최종 정리

rownum 은 모조칼럼이므로 alias 값을 주어 실제 칼럼화 시켜서 사용하는 것이 좋다.

select hakno, 총학점, uname, rnum from (select AA.hakno, AA.총학점, STU.uname, rownum as rnum from(select SU.hakno, sum(SUB.ghakjum) as 총학점 from tb_sugang SU join tb_subject SUB on SU.gcode = SUB.gcode group by SU.hakno order by sum(ghakjum)) AA join tb_student STU on AA.hakno = STU.hakno) where rnum <= 3;

 

 

문제 2) 학번별 수강신청한 총학점을 조회하시오
--(단, 수강신청하지 않은 학생의 총학점도 0으로 표시)

 

-- 수강신청을 한 학생의 총학점

select tb_sugang.gcode, sum(tb_subject.ghakjum) as 총학점 from tb_sugang inner join tb_subject on tb_sugang.gcode = tb_subject.gcode group by tb_sugang.gcode;

 

 

-- 수강신청 한 학생의 총학점과 안한학생들 학점 0을 모두 출력

select STU.hakno, nvl(AA.총학점, 0) from tb_student STU left join (             select tb_sugang.gcode,tb_sugang.hakno, sum(tb_subject.ghakjum) as 총학점             from tb_sugang inner join tb_subject             on tb_sugang.gcode = tb_subject.gcode             group by tb_sugang.gcode,tb_sugang.hakno) AA             on STU.hakno = AA.hakno;

 

 

 

문제 3) 학생테이블에서 학번순으로 정렬 후 행번호를 아래와 같이 붙여서 조회하시오.
-- ex) 8 g1001 홍길동 ddd@email.com 서울 111-5555

 

 

-- 학번을 내림차순 조회

select hakno, uname from tb_student order by hakno desc;

 

-- 행 번호추가

select hakno, uname, rownum rnum from (select hakno, uname from tb_student order by hakno desc);

 

 

-- 행 번호 내림차순 정렬

select hakno, uname, rownum rnum from (select hakno, uname from tb_student order by hakno desc) order by rnum desc;

 

 

 

트랜잭션

[ 트랜잭션 Transaction ]

- 동시에 다수의 직업을 독립적으로 안전하게 처리하기 위한 상호 작용 단위

- 일 처리 단위

- 분할할 수 없는 최소 단위

- 목적: 안전한 거래 보장

 

[ TCL 명령어 ]

- commit : 거래 내역을 확정함

- rollback: 거래 내역을 취소함

- checkpoint : rollback할 위치를 지정함

 

 

CMD로 실습하기

 

cmd에서 sqlplus로 로그인 후

테이블 생성하고 구조 조회하였다.

--테이블 생성 create table dep ( id  varchar2(10) primary key ,name varchar2(15) not null ,location varchar(50) ); -- 테이블 구조확인 desc dep;

 

그리고 데이터를 추가하였다.

 

select count(*) from dep;

 

Rollback 명령어를 입력하면

 

그리고 다시 행 갯수를 조회하면

오잉 한개의 행도 선택되지 않았다.

Rollback 명령어는 그 전의 명령어를 철회하는 명령어기 때문이다.

그렇기 때문에 데이터 삽입했던 명령들이 모두 취소되었다.

 

 

다시 데이터를 넣었다.

 

그리고 CMD를 종료하고

다시 로그인해서 테이블을 조회하니

또 행이 없다 그 전의 내역이 저장되지 않았기 때문이다.

 

 

 

-- 세이브포인트 + 행추가 insert into dep (id,name,location)values('40','영업부','서울 종로구'); savepoint a; insert into dep (id,name,location)values('50','개발부','서울 중구'); savepoint b; insert into dep (id,name,location)values('60','관리부','서울 마포구');

 

 

세이브포인트는 롤백할 지점을 정하는 것이다.

 

 

우선 지금까지 넣은 데이터를 조회해보면

 

rollback to a를 하면

savepoint a 이전 지점까지만 보존되고 그 후는 철회됨.

 

 

[ 커밋한 자료의 복구방법 ]

SQL>col name for a20;

SQL>col type for a10;

SQL>col value for a10;

 

SQL>show parameter undo;

--undo_retention

delete, update 후에 커밋을 했을 경우

속성값의 시간(초)까지는 오라클에서 임시로 저장한 데이터로 복구할 수 있음

default 속성값은 900초(15분)

alter _retention 기능을 이용한 데이터 복구 방법

select * from dep

as of timestamp(systimestamp-interval '15' minute);

 

 

delete from dep;

commit;

 

 

 

--커밋한 데이터 확인

select * from dep
as of timestamp(systimestamp-interval '15' minute);

 

 

--삭제하고 커밋한지 15분안의 데이터를 복구
insert into dep select * from dep
as of timestamp(systimestamp-interval '15' minute);

 

 

 

---

NCS 학습모듈

---

기본 SQL

DDL 활용

DDL(Data Definition Language)은 `데이터를 정의하는 언어'로서, 보다 엄밀하게 말하면
`데이터를 담는 그릇을 정의하는 언어'이며, 이러한 그릇을 DBMS에서는 오브젝트라고 한다. 

DDL을 통해 정의할 수 있는 대상, 오브젝트 유형은 다음과 같다.

 

생성 CREATE 데이터베이스 오브젝트 생성
변경 ALTER 데이터베이스 오브젝트 변경
삭제 DROP 데이터베이스 오브젝트 삭제
       TRUNCATE 데이터베이스 오브젝트 내용 삭제

 

* 다음 중 DDL명령어가 아닌 것은? 

 

 

1. 테이블 생성

 

- 제약 조건 알아두기

 

 

데이터 확인 -> 데이터 타입결정 -> 제약조건확인 -> 테이블 생성 -> 데이터 삽입

 

 

DML활용

데이터를 조작하는 명령어

 

insert 삽입

INSERT INTO table_name (column1, column2, ..) VALUES (value1, value2, ...);

 

select 조회

SELECT [OPTION] columns FROM table [WHERE 절] ;

update 변경

UPDATE table SET column1 = value1, column2 = value2, ... [WHERE 절] ;

 

 

delete 삭제

DELETE FROM table [WHERE 절] ;

 

 

--직원테이블 CREATE TABLE employees ( eid int primary key ,name varchar(50) not null ,password varchar(20) not null ,position varchar(20) not null ,work_place varchar(50) not null ,address varchar(255) not null ,birthday date not null ,joindate date not null ,weddingdate date not null ); --급여테이블 CREATE TABLE salaries ( -- 급여 테이블 emp_no INT NOT NULL, salary INT NOT NULL, from_date DATE NOT NULL, to_date DATE, FOREIGN KEY (emp_no) REFERENCES employees (eid) ON DELETE CASCADE, PRIMARY KEY (emp_no, from_date) );