데이터베이스실습

테이블 만들기

 

CREATE TABLE subject
 (sub_no number(10) CONSTRAINT subject_pk_subno PRIMARY KEY,
 sub_title varchar2(30) NOT NULL,
 sub_book varchar2(30));

CREATE TABLE management
 (mana_no number(10) CONSTRAINT management_pk_manano PRIMARY KEY,
 mana_tel number(10) NOT NULL);

CREATE TABLE student
 (stu_no number(10) CONSTRAINT student_pk_stuno PRIMARY KEY,
 stu_name varchar2(10) NOT NULL,
 stu_major varchar2(20) ,
 stu_tel number(10) ,
 stu_address varchar2(50));

CREATE TABLE professor
 (pro_no number(10) CONSTRAINT professor_pk_prono PRIMARY KEY,
 pro_name varchar2(10) NOT NULL,
 pro_major varchar2(20),
 pro_tel number(10) ,
 pro_address varchar2(50));

 

데이터 삽입

 

insert into student values(0039085, '정용학', '정보통신', 0165008582, '대전문화동');
insert into student values(0039015, '김영훈', '정보통신', 01192399406, '대전월평동');
insert into student values(0039063, '윤두섭', '정보통신', 0164078643, '대전가양1동');
insert into student values(0039108, '홍성덕', '정보통신', 01193380130, '대전용계동');

insert into subject values(001,'통신시스템설계실습','LabVolt');
insert into subject values(002,'리눅스시스템프로그래밍','리눅스마스터2급');
insert into subject values(003,'DB','DB개론과실습');
insert into subject values(004,'디지털통신','LATHI');
insert into subject values(005,'TCP/IP네트워킹','TCP/IP');
insert into subject values(006,'안테나공학','PPT');
insert into subject values(007,'공학설계프로젝트','DSP');
insert into subject values(008,'VHDL회로설계','VHDL');

insert into professor values(101, '박덕규', '정보통신', 01100000000, 'D501');
insert into professor values(102, '오용선', '정보통신', 01600000000, 'D502');
insert into professor values(103, '오상훈', '정보통신', 01700000000, 'D503');
insert into professor values(104, '조용희', '정보통신', 01900000000, 'D504');
insert into professor values(105, '최성자', '정보통신', 01000000000, 'D505');

insert into management values('정통',1111111);
insert into management values('전자',2222222);
insert into management values('컴멀',3333333);
insert into management values('컴교',4444444);
insert into management values('건축',5555555);
insert into management values('응화',6666666);
insert into management values('도시',7777777);

 

시나리오

 

select pro_ma,e as NAME from professor where pro_name like '오%';
select stu_no as 학번, stu_name, stu_address from student order by stu_no;
select sub_no, sub_title, sub_book from subject where sub_no<=5;
select * from student where stu_major='정통' and stu_address='대전문화동';
select * from subject where sub_title='&제목';
select * from professor where pro_address between 'D501' and 'D503';
select stu_name, stu_major, stu_address from student s, management m where s.stu_major=m.mana_no;
select * from professor where pro_no in (102,105);
select stu_name, stu_major, stu_address from student where stu_major=(select pro_major from professor where pro_name='박덕규');
set feedback off ttitle 'student' btitle '0039085 jung yong hak' select stu_no,stu_name,stu_major from student