Oracle 建表练习

1 建学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）

 create table student(
        sno   varchar2(3) not null,
        sname varchar2(28) not null,
        ssex  varchar2(8) not null,
        sbirthday date,
         sclass varchar2(5),
       constraint pk_student primary key(sno)
 );

 create table course(
      cno       varchar2(5) not null,
       cname     varchar2(50) not null,
       tno       varchar2(3) not null,
       constraint pk_course primary key(cno)
 );

create table score(
         sno   varchar2(3) not null,
       cno   varchar2(5) not null,
        degree   number(4,1),
         constraint pk_score primary key(sno,cno)
 );

create table teacher(
       tno   varchar2(3) not null,
     tname varchar2(28) not null,
        tsex  varchar2(8) not null,
        tbirthday date,
        prof  varchar2(50),
        depart varchar2(15) not null,
        constraint pk_teacher primary key(tno)
 );

2.添加数据

 insert into student(sno,sname,ssex,sbirthday,sclass) values(108,'zenghua','male',to_date('1977-09-01','yyyy-mm-dd'),95033);
 insert into student(sno,sname,ssex,sbirthday,sclass) values(105,'kuangming','male',to_date('1975-10-02','yyyy-mm-dd'),95031);
 insert into student(sno,sname,ssex,sbirthday,sclass) values(107,'wangli','female',to_date('1976-01-23','yyyy-mm-dd'),95033);
 insert into student(sno,sname,ssex,sbirthday,sclass) values(101,'lijun','male',to_date('1976-02-20','yyyy-mm-dd'),95033);
 insert into student(sno,sname,ssex,sbirthday,sclass) values(109,'wangfang','female',to_date('1975-02-10','yyyy-mm-dd'),95031);
 insert into student(sno,sname,ssex,sbirthday,sclass) values(103,'lujun','male',to_date('1974-06-03','yyyy-mm-dd'),95031);


insert into course(cno,cname,tno) values('3-105','Introduction to Computers',825);
insert into course(cno,cname,tno) values('3-245','Operating System',804);
insert into course(cno,cname,tno) values('6-166','Digital Circuit ',856);
insert into course(cno,cname,tno) values('9-888','Advanced Mathematics ',831);


insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(804,'licheng','male',to_date('1958-12-02','yyyy-mm-dd'),'associate professor','cs');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(856,'zhangxu','male',to_date('1969/03/12','yyyy-mm-dd'),'lecturer','ee');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(825,'wangping','female',to_date('1972/05/05','yyyy-mm-dd'),'assistant','cs');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(831,'liubing','female',to_date('1977/08/14','yyyy-mm-dd'),'assistant','ee');


 insert into score(sno,cno,degree) values(103,'3-245',86);
 insert into score(sno,cno,degree) values(105,'3-245',75);
 insert into score(sno,cno,degree) values(109,'3-245',68);
 insert into score(sno,cno,degree) values(103,'3-105',92);
 insert into score(sno,cno,degree) values(105,'3-105',88);
 insert into score(sno,cno,degree) values(109,'3-105',76);
 insert into score(sno,cno,degree) values(101,'3-105',64);
 insert into score(sno,cno,degree) values(107,'3-105',91);
 insert into score(sno,cno,degree) values(108,'3-105',78);
 insert into score(sno,cno,degree) values(101,'6-166',85);
 insert into score(sno,cno,degree) values(107,'6-166',79);
 insert into score(sno,cno,degree) values(108,'6-166',81);

3. 查询这4个表

SQL> select * from student;

SNO SNAME			 SSEX	  SBIRTHDAY SCLAS
--- ---------------------------- -------- --------- -----
108 zenghua			 male	  01-SEP-77 95033
105 kuangming			 male	  02-OCT-75 95031
107 wangli			 female   23-JAN-76 95033
101 lijun			 male	  20-FEB-76 95033
109 wangfang			 female   10-FEB-75 95031
103 lujun			 male	  03-JUN-74 95031

SQL> select * from score;

SNO CNO       DEGREE
--- ----- ----------
103 3-245	  86
105 3-245	  75
109 3-245	  68
103 3-105	  92
105 3-105	  88
109 3-105	  76
101 3-105	  64
107 3-105	  91
108 3-105	  78
101 6-166	  85
107 6-166	  79
108 6-166	  81


SQL> select * from course;

CNO   CNAME						 TNO
----- -------------------------------------------------- ---
3-105 Introduction to Computers 		         825
3-245 Operating System					 804
6-166 Digital Circuit					 856
9-888 Advanced Mathematics				 831

SQL>  select * from teacher;

TNO TNAME			 TSEX	  TBIRTHDAY PROF					       DEPART
--- ---------------------------- -------- --------- -------------------------------------------------- ---------------
804 licheng			 male	  02-DEC-58 associate professor 			       cs
856 zhangxu			 male	  12-MAR-69 lecturer					       ee
825 wangping			 female   05-MAY-72 assistant					       cs
831 liubing			 female   14-AUG-77 assistant					       ee

4.查询“95031”班的学生人数。

SQL> select sclass ,count(*) from student where sclass=95031 group by sclass;

SCLAS	COUNT(*)
----- ----------
95031	       3

 查询“计算机系“教师所教课程的成绩表。

SQL> select * from score where cno in(
select cno from course where tno in(
select tno from teacher where depart='cs'
));
  2    3    4  
SNO CNO       DEGREE
--- ----- ----------
103 3-245	  86
105 3-245	  75
109 3-245	  68
103 3-105	  92
105 3-105	  88
109 3-105	  76
101 3-105	  64
107 3-105	  91
108 3-105	  78

查询所有未讲课的教师的Tname和Depart. 

SQL> select tname, depart from teacher where tno in(
select tno from course where cno not in(
select cno from score
));  2    3    4  

TNAME			     DEPART
---------------------------- ---------------
liubing 		     ee