오라클 조인,서브쿼리 연습

-- 학생
drop table student;

create table student(
  id char(10) primary key, -- 학번
  name varchar(20) not null, -- 이름
  departmentId char(3) not null, -- 학과id
  address varchar(50) not null -- 주소
);

insert into student
values('EL20170001','홍길동','EL','서울');
insert into student
values('CO20170002','이순신','CO','제주도');
insert into student
values('CO20170003','왕건','CO','경기도');
insert into student
values('SC20170004','보안','SC','부산');

select * from student;

-- 코드, ID등이 저장되는 테이블이 먼저 정의되어야 함.

-- 학과
create table department(
  id char(2) primary key,
  name varchar(30) not null
);
insert into department
values('EL','전자공학과');
insert into department
values('CO','컴퓨터공학과');
insert into department
values('SC','보안학과');

 

-- join
select s.name, d.name
from student s, department d
where s.departmentId=d.id;

 

-- 책
create table books(
  no char(6) primary key, -- 책번호
  title varchar(50) not null, -- 책이름
  author varchar(50) not null -- 저자
)
insert into books values('000001','오라클기본','이황');
insert into books values('000002','자바정복','율곡');
insert into books values('000003','HTML5','강감찬');

select * from books;

 

-- 책대여
drop table bookRent;

create table bookRent
( no char(10) primary key, -- 대여번호
  id char(10) not null, -- 학번
  bookNo char(6) not null, -- 책번호
  rDate char(8) not null ,-- 대여일
  constraint fk_bookrent foreign key(id)
  references student(id)
)
insert into bookRent values('2017071301','EL20170001','000001','20170713');
insert into bookRent values('2017071302','CO20170003','000003','20170713');
insert into bookRent values('2017071303','SC20170004','000003','20170713');

 

-- 참조키 테스트 ---------------------------------------------------------------------

-- Error. student에 존재하지 않는 ID 입력 불가.
insert into bookRent values('2017071303','Anonymous','000001','20170713');

-- Error. student에 존재하지 않는 ID 수정 불가.
update  bookRent set
id='Anonymous'
where no='2017071301';

-- Error. bookRent에 존재한는 ID를 student에서만 삭제 불가
delete from student where id='EL20170001';

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

select * from bookRent;

 

-- 학생이름, 책이름, 대여일
select s.name, b.title, br.rDate
from student s, books b, bookRent br
where br.id=s.id
and br.bookNo=b.no;

 

-- 모든 책의 목록이 출력된 상태에서 책을 빌린 사람을 보여주기.

select b.title,s.name,br.rDate
from books b  left outer join  bookRent br
on b.no=br.bookNo
left outer join student s
on br.id=s.id;

 

select b.title,s.name,br.rDate
from bookRent  br  right outer join  books b
on b.no=br.bookNo
left outer join student s
on br.id=s.id;

 

-- 전자공학과 학생들의 대여횟수 집계
select count(*) as 대여횟수
from bookRent b , student s
where b.id=s.id
and departmentId='EL';

 

select count(*) as 대여횟수
from bookRent b , student s
where b.id=s.id
and departmentId=(select id from department where name='전자공학과');

 

-- 전자공학과, 컴퓨터 공학과 학생들의 대여횟수 집계

select s.departmentId, count(*) as 대여횟수
from bookRent b , student s
where b.id=s.id
and (departmentId='EL' or departmentId='CO')
group by s.departmentId;

 

-- 서브쿼리의 결과값이 여러개(행의 수가 여러개)일 때 in 사용.
select s.departmentId, count(*) as 대여횟수
from bookRent b , student s
where b.id=s.id
and departmentId in (select id from department
                                      where name='전자공학과' or name='컴퓨터공학과')
group by s.departmentId;