SQL_5일차

서창민·2023년 3월 16일
sql
0

SQL

목록 보기
5/10
post-thumbnail

23.03.15 수 5일차

오늘은 예비군으로 인해서 수업을 듣지 못했다,
우선 내일 수업에서 질문을 찾기위해서 수업진도 내용을
보고 따라 작성해 보았다.,

> F.K(foreign), 외래키 제약조건 유의사항

  1. 다른 테이블의 P.K 를 참조한다.
  2. 하나의 테이블에 F.K 를 2개이상 가질 수 있다.
  3. 참조 값으로 null 를 가질수 있다.
create table enrol(
sno varchar2(3) ,
cno varchar2(5) ,
grade varchar2(3),
midterm int,
final int ,
constraint enrol_sno_fk foreign key(sno) references student(sno),
constraint enrol_cno_fk foreign key(cno) references course(cno)
);

> 트리거(trigger) 작업

트리거란
데이터베이스가 미리 정해 놓은 조건들을 만족하거나
특정 이벤트가 발생하면 자동적으로 수행되는 동작(PL/SQL 블록)
개발자가 호출하여 사용할 수 없다. (오라클에서 자동으로 실행)

가장 많이 사용되는 트리거의 유형은
insert, update, delete의 결과로 실행되는 DML트리거

기본 형식으로는

테이블 작성

Create table deleteStudent( -- 테이블명
sno varchar2(3), -- 칼럼명 데이터형
sname nvarchar2(10),
year varchar2(10),
dept nvarchar2(10) ,
deleteDate date
)

Create (or replace)TRIGGER trg_deleteStudent 
-- 트리거명, 괄호는 생략가능
after delete 
on student
for each row
begin
insert into deleteStudent 
(sno, sname, year, dept, deleteDate )
values 
(:old.sno, :old.sname, :old.year, :old.dept, sysdate());
end;
delete from student where sno='100';
student 테이블의 sno칼럼에서 100 값을 가지고 있는 레코드를 삭제.

삭제 여부 확인

select * from deleteStudent;

--update 트리거

Create table updateStudent(
sno varchar2(3) ,
sname nvarchar2(10),
year varchar2(10),
dept nvarchar2(10) ,
updateDate date
)


Create TRIGGER trg_updateStudent
after update
on student
for each row
begin
insert into updateStudent 
(sno, sname, year, dept, updateDate )
values 
(:old.sno, :old.sname, :old.year, :old.dept, sysdate());
end;
update student set dept='컴퓨터' where dept='전산과';
select * from updateStudent;

> 자동증가 적용 시퀀스(sequence)

시퀀스(Sequence)
: 자동 순번을 반환하는 데이터베이스 객체

-- 방법1
insert into student(sno,sname,year,dept)
values((select max(sno)+ 1 from student ) ,'영심이2','2','산공');
--방법2
Create sequence idx_student
start with 101 -- 초기값
increment by 1; -- 증가 크기
drop sequence idx_student;

> P.K 제약조건 만들기 , 확인하기, 삭제하기

  • 기본키(primary key)
    : unique + not null
    : 테이블 당 1개의 기본키 생성. 여러 컬럼을 하나의 기본키로 생성 가능
    : 데이터 무결성의 구현체
-- 방법1
Create table user0(
id nvarchar2(5) ,
name nvarchar2(10)
);

user0_id에 P.K 부여

alter table user0
add constraint user0_id primary key(id);

테이블을 생성하며 P.K 부여

-- 방법2
Create table user1(
id nvarchar2(5) not null primary key,
name nvarchar2(10)
);
-- 방법3 .
Create table user2(
id nvarchar2(5) not null,
name nvarchar2(10),
primary key(id)
);
-- 방법4 .
Create table user3(
id nvarchar2(5) not null,
name nvarchar2(10),
constraint pk_id primary key(id)
);

제약조건 확인

select * from ALL_constraints
where table_name='USER3';

제약조건 삭제

alter table user3
drop constraint pk_id;

제약조건 수정

insert into user2(id, name) 
values('AAA','순심이');

> F.K 제약조건 만들기 및 추가

Create table fk_user00(
id nvarchar2(5) ,
age number(3),
FOREIGN key(id) REFERENCES user00(id)
);
--부모테이블 user00 id 칼럼을 참조하여 F.K 부여
Create table fk_user01(
id nvarchar2(5) REFERENCES user00(id) ,
age number(3)
);
Create table fk_user02(
id nvarchar2(5) ,
age number(3),
constraint fk_user02_id FOREIGN key(id) REFERENCES user00(id)
);

Create table fk_user03(
id nvarchar2(5) ,
age number(3)
);
alter table fk_user03
add constraint fk_user03_id 
FOREIGN key(id) REFERENCES user00(id);
insert into user00(id, name) 
values('a001','영심이');

insert into fk_user00(id, age) values('a001',13); -- 가능

insert into fk_user00(id, age) values('a002',13); 
-- 오류 

insert into fk_user00(id, age) 
values('',13); -- null 가능

> UNIQUE 제약 조건

유니크란
고유값을 의미
즉, 유일한 값만 들어가도록 하고 싶을 때 사용하는 제약조건

Create table uk_user1 (
id nchar(10) UNIQUE ,
name varchar(20)
);
insert into uk_user1(id, name) values('100','영심이');

insert into uk_user1(id, name) values('100','둘리'); -- 에러

insert into uk_user1(id, name) 
values('','둘리'); -- 가능

insert into uk_user1(id, name) 
values('','둘리'); -- 가능

select * from uk_user1;
Create table uk_user2 (
id nchar(10) not null UNIQUE,
name varchar(20)
);
insert into uk_user2(id, name) values('100','영심이');

insert into uk_user2(id, name) values('100','둘리'); -- 에러

insert into uk_user2(id, name) 
values('','둘리'); -- 에러

select * from uk_user2;

> default 제약 조건

DEFAULT는 아무 값도 입력하지 않아도 NULL 값이 아닌,
기본 값으로 설정한 값이 자동으로 입력되도록 하는 제약조건

Create table default_user1 (
id nchar(10) ,
name varchar(20)
);
insert into default_user1(id, name) values('100','영심이');

insert into default_user1(id, name) values('','둘리'); -- null 유지

alter table default_user1
modify id default 'A001';

select * from default_user1;
insert into default_user1(id,name) values('','kkk1'); -- null 추가

insert into default_user1(name) values('kkk1'); -- A001

> check 제약조건

체크(CHECK)
입력할 수 있는 값의 범위를 설정해 주는 제약조건

특징
1.입력 값이 조건에 맞지 않으면 오류가 발생한다.
2.입력 값의 범위를 지정할 수 있다.

Create table check_user1 (
id nchar(10) ,
name varchar(20) check 
( name in ('영심이','하늘이','둘리'))
);
Create table check_user2 (
id nchar(10) ,
name varchar(20) ,
check ( name in ('영심이','하늘이','둘리'))
);
Create table check_user4 (
id nchar(10) ,
name varchar(20) ,
CHECK (id in ('A','B','C') and name in ('AAA','BBB'))
);
insert into check_user4(id, name)
values('A','AAA');

insert into check_user5(id, name)
values('A','CC');
Create table check_user6 (
id nchar(10) ,
name varchar(20)
);
alter table check_user6
add CONSTRAINT check_user6_id1 CHECK (id in ('A','B','C'));

alter table check_user6
add CONSTRAINT check_user6_name1 CHECK (name in ('AA','BB','CC'));

> 제약조건 삭제

alter table check_user3
drop CONSTRAINT check_user3_id;

============================================================================

> 연습문제 5일차

서브쿼리 연습문제

  1. 컴퓨터과 학생들의 점수를 중간, 기말 점수에서 -1씩 진행하시오.
select * from enrol;
update enrol set MIDTERM = MIDTERM-1, FINAL=FINAL-1
where sno in (
select sno from student where dept='컴퓨터'
);
  1. 서브쿼리를 이용하여 화일처리 과목의
    중간고사평균과 기말고사 평균을 나타내시오.
select avg(MIDTERM) , avg(FINAL) from enrol
where cno = (
select cno from course where cname='화일처리'
);
select avg(MIDTERM) , avg(FINAL) from enrol e join course c
on e.cno = c.cno
where cname='화일처리';
  1. 서브쿼리를 이용하여 기말고사의 평균 성적이
    85점 이상인 학생의 이름과 학과를 나타내시오.
select sname , dept from student
where sno in (
select sno from enrol
group by sno
having avg(FINAL) >= 85
);

-- 다른표현

select sname , dept from student s join enrol e
on s.sno = e.sno
group by sname , dept
having avg(FINAL) >= 85;
  1. 서브쿼리를 이용하여 등록 테이블에서 기말고사 평균을 구하여
    평균보다 성적이 좋은 학생의 이름을 구하시오.
select sname from student
where sno in (
select sno from enrol
group by sno
having avg(final) > (select avg(final) from enrol)
);

==========================================================

join 연습문제

  1. 중간고사 성적이 90점 이상인 학생의 이름과 과목코드번호, 중간고사 성적을 나타내시오.
select sname, cno, midterm
from student s join enrol e
on s.sno = e.sno
where midterm >= 90 ;
  1. 3학년 4학년 학생의 이름과 과목코드,성적을 나타내시오
select sname, cno, grade
from student s join enrol e
on s.sno = e.sno
where YEAR in( 3, 4) ;

-- 다른표현

select sname, cno, grade
from student s join enrol e
on s.sno = e.sno
where YEAR = 3 or YEAR = 4 ;
  1. 자료구조를 수강한 학생의 학번과 성적을 나타내시오.
select sno, grade from enrol e join course c
on e.cno = c.cno
where cname='자료구조';
  1. 학과별 학생수를 구하시오. ( 학과, 학생수 )
select dept, count(*) as "학생수"
from student
group by dept;
profile
서창민
Back-end Developer Preparation Students
이전 포스트

SQL_4일차

다음 포스트

SQL_6일차

0개의 댓글