9/12 sql

-- database 생성
create database sampledb;

 

-- database선택
use sampledb;

 

-- table생성
create table student(
id char(7) primary key,
name varchar(10) not null,
dept varchar(20) not null
);

 

-- 데이터입력
insert into student values('1091011','김철수','컴퓨터시스템');
insert into student values('0792012','최고봉','멀티미디어');
insert into student values('0494013','이기자','컴퓨터공학');

 

-- 조회
select * from student;

 

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','2018001','000001','20170713');
insert into bookRent values('2017071302','2018002','000003','20170713');
insert into bookRent values('2017071303','2018003','000003','20170713');
insert into bookRent values('2017071304','0792012','000001','20170713');
insert into bookRent values('2017071305','0792012','000003','20170713');
insert into bookRent values('2017071306','1091011','000003','20170713');

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

 
select s.id, s.name, b.title, br.rDate
from student s, books b, bookRent br
where br.id=s.id and br.bookNo=b.no
and s.dept='컴퓨터시스템'
order by b.no;