SQL基础练习一

drop table book_tab;
drop table book_type;
create table book_type(
       bt_id varchar2(8) not null primary key, bt_name varchar(20));
commit;
create table book_tab(
       b_id  varchar2(8) not null primary key,
       b_name varchar2(20),
       book_price number,
       book_num number,
       book_type varchar2(20),
       foreign key(book_type) references book_type(bt_id));
commit;


insert into book_type(bt_id,bt_name) values('a001','历史类');
insert into book_type(bt_id,bt_name) values('a002','文学类');
insert into book_type(bt_id,bt_name) values('a003','地理类');
insert into book_type(bt_id,bt_name) values('a004','计算机類');
insert into book_type(bt_id,bt_name) values('a005','ERP');
insert into book_tab(b_id,b_name,book_price,book_num,book_type)values('01-1','中國上下五千年',34.45,45,'a001');
insert into book_tab(b_id,b_name,book_price,book_num,book_type)values('01-2','兰亭集序',34.45,45,'a001');
insert into book_tab(b_id,b_name,book_price,book_num,book_type)values('01-3','世界地理旅游',30,45,'a003');
insert into book_tab(b_id,b_name,book_price,book_num,book_type)values('01-4','数据结构',34.45,1,'a004');
insert into book_tab(b_id,b_name,book_price,book_num,book_type)values('01-5','计算机组成原理',34.45,20,'a004');
insert into book_tab(b_id,b_name,book_price,book_num,book_type)values('01-6','计算机网络',15,70,'a004');
insert into book_tab(b_id,b_name,book_price,book_num,book_type)values('01-7','C语言',34.45,7,'a004');
insert into book_tab(b_id,b_name,book_price,book_num,book_type)values('01-8','web前端',10,70,'a004');
commit;
select * from book_tab;
select *from book_type;
update book_tab set b_name='C语言修改的版本',book_price=100 where b_id='01-7';
commit;
select *from book_tab;


select * from book_tab where book_price>20;
select * from book_tab where book_price<30;
update book_tab set book_num=100 where book_type='a001';
select b_name,book_num from book_tab;


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


create table sp_type(
       id varchar2(20) not null primary key, 
       name varchar(30),
       p_id varchar(20));
create table user_tab(
       id varchar2(20) not null primary key, 
       name varchar(30),
       pwd varchar(20),
       type varchar(20),
       creat_time varchar(20));
create table sp_tab(
       id varchar2(20) not null primary key, 
       name varchar(30),
       model varchar(20),
       sp_text varchar(20),
       spcie varchar(20),
       c_number varchar(20),
       sp_type_id varchar(20),
       foreign key(sp_type_id) references sp_type(id));
create table order_tab(
       id varchar2(20) not null primary key, 
       creat_time varchar(30),
       type varchar(20),
       user_id varchar(20),
       foreign key(user_id) references user_tab(id));
create table order_detail_tab(
       id varchar2(20) not null primary key, 
       count varchar(20),
       order_id varchar(20),
      foreign key(order_id) references order_tab(id),
      sp_id varchar(20),
      foreign key(sp_id) references sp_tab(id));
commit;
select * from sp_type;
select * from order_detail_tab;
----------------------------------------------------------------
---------------------------------------------------------------
-------------------------------------------------------------
drop table grade_tab;
drop table student_tab;
drop table course_tab;
drop table grade_tab;
drop table teacher_tab;
create table teacher_tab(
       id varchar(20) not null primary key,
       name varchar(20),
       age number(3),
       Edu varchar2(20),
       Tage varchar2(20)       
        );
insert into teacher_tab(id,name,age,Edu,Tage) values('T-001','赵雅芝','57','本科','二十年');
insert into teacher_tab(id,name,age,Edu,Tage) values('T-002','刘若英','37','本科','十年');
insert into teacher_tab(id,name,age,Edu,Tage) values('T-003','谭晶','27','研究生','五年');
insert into teacher_tab(id,name,age,Edu,Tage) values('T-004','郎波','44','本科','十六年');
insert into teacher_tab(id,name,age,Edu,Tage) values('T-005','储久量','50','本科','二十年');
insert into teacher_tab(id,name,age,Edu,Tage) values('T-006','海尔','37','本科','十年');
insert into teacher_tab(id,name,age,Edu,Tage) values('T-007','谭晶','27','研究生','五年');
insert into teacher_tab(id,name,age,Edu,Tage) values('T-008','湛强','44','本科','六年');
commit;
select * from teacher_tab;
-------------------------------------------------------------------
create table class_tab(
       id varchar(20) not null primary key,
       cname varchar(20),
       pnum number(3),
       T_id varchar(20),
       foreign key(T_id) references teacher_tab(id)
       ); 
insert into class_tab(id,cname,pnum,T_id) values('C-001','计算机科学与技术',20,'T-001');
insert into class_tab(id,cname,pnum,T_id) values('C-002','通信工程',10,'T-003');
insert into class_tab(id,cname,pnum,T_id) values('C-003','VR技术设计',40,'T-007');
insert into class_tab(id,cname,pnum,T_id) values('C-004','游戏媒体设计',70,'T-006');
insert into class_tab(id,cname,pnum,T_id) values('C-005','前端工程',20,'T-005');
insert into class_tab(id,cname,pnum,T_id) values('C-006','运维工程',20,'T-008');
commit;
select * from  class_tab;
----------------------------------------------------------------------
create table course_tab(
        id varchar(20) not null primary key,
        name varchar(20),
        c_time varchar(20),
        credit number(3,1),
        T_id varchar(20),
       foreign key(T_id) references teacher_tab(id)
       );
insert into course_tab(id,name, c_time,credit,T_id) values('Co-001','数据库','20',9,'T-001');
insert into course_tab(id,name, c_time,credit,T_id) values('Cou-0002','计算机组成原理','20',7,'T-003');
insert into course_tab(id,name, c_time,credit,T_id) values('Cou-0003','计算机网络','20',5,'T-004');
insert into course_tab(id,name, c_time,credit,T_id) values('Cou-0004','数据结构','20',9.5,'T-005');
insert into course_tab(id,name, c_time,credit,T_id) values('Cou-0005','操作系统','20',6.5,'T-006');
insert into course_tab(id,name, c_time,credit,T_id) values('Cou-0006','java编程语言','20',8.5,'T-007');
insert into course_tab(id,name, c_time,credit,T_id) values('Cou-0007','大数据','20',10,'T-008');
commit;
select * from  course_tab;
---------------------------------------------------------------------
create table student_tab(
        id varchar(20) not null primary key,
        name varchar(20),
        age number(3),
        major varchar(20),
        inTime date,
        class_id varchar(20),
       foreign key(class_id) references class_tab(id)
       ); 
insert into student_tab( id , name,age,major,inTime,class_id) values('20101001','刘可',25,'通信工程',to_date('2010-09-14','yyyy-mm-dd'),'C-001');
insert into student_tab( id , name,age,major,inTime,class_id) values('20101002','张可',20,'计算机科学与技术',to_date('2010-09-14','yyyy-mm-dd'),'C-002');
insert into student_tab( id , name,age,major,inTime,class_id) values('20101003','赵可',23,'VR技术设计',to_date('2010-09-14','yyyy-mm-dd'),'C-003');
insert into student_tab( id , name,age,major,inTime,class_id) values('20101004','田可',21,'游戏媒体设计',to_date('2010-09-14','yyyy-mm-dd'),'C-004');
insert into student_tab( id , name,age,major,inTime,class_id)values('20101005','郝可',24,'前端工程',to_date('2010-09-14','yyyy-mm-dd'),'C-005');
insert into student_tab( id , name,age,major,inTime,class_id) values('20101006','钱可',27,'通信工程',to_date('2010-09-14','yyyy-mm-dd'),'C-006');
insert into student_tab( id , name,age,major,inTime,class_id) values('20101007','穆可',28,'游戏媒体设计',to_date('2010-09-14','yyyy-mm-dd'),'C-001');
insert into student_tab( id , name,age,major,inTime,class_id) values('20101008','韩可',20,'前端工程',to_date('2010-09-14','yyyy-mm-dd'),'C-003');
insert into student_tab( id , name,age,major,inTime,class_id) values('20101009','王可',29,'VR技术设计',to_date('2010-09-14','yyyy-mm-dd'),'C-004');
insert into student_tab( id , name,age,major,inTime,class_id) values('20101010','段可',22,'计算机科学与技术',to_date('2010-09-14','yyyy-mm-dd'),'C-004');
commit;
select * from  student_tab;
--------------------------------------------------------------  
create table grade_tab(
       id varchar(20) not null primary key,
       grade number(3),
       con_id varchar(20),
       foreign key(con_id) references course_tab(id),
       stu_id varchar(20),
       foreign key(stu_id) references student_tab(id)
       );
insert into grade_tab(id, grade, con_id,stu_id)values('G01',99,'Co-001','20101001');
insert into grade_tab(id, grade, con_id,stu_id)values('G02',99,'Cou-0002','20101002');
insert into grade_tab(id, grade, con_id,stu_id)values('G03',99,'Cou-0003','20101003');
insert into grade_tab(id, grade, con_id,stu_id)values('G04',99,'Cou-0004','20101004');
insert into grade_tab(id, grade, con_id,stu_id)values('G05',99,'Cou-0005','20101005');
insert into grade_tab(id, grade, con_id,stu_id)values('G06',99,'Cou-0006','20101006');
insert into grade_tab(id, grade, con_id,stu_id)values('G07',99,'Cou-0007','20101007');
insert into grade_tab(id, grade, con_id,stu_id)values('G08',99,'Cou-0003','20101008');
insert into grade_tab(id, grade, con_id,stu_id)values('G09',99,'Cou-0003','20101009');
insert into grade_tab(id, grade, con_id,stu_id)values('G010',99,'Cou-0003','20101010');


commit;
select * from grade_tab;