用VB连接ORalce数据库的学生管理系统

创建用户：
create user he identified by he;
grant connect,resource,dba to he;
commit;

创建表空间：
create tablespace "helen"
logging
datafile 'd:\ora\helen.dbf' size 50m;

学生表:
create table student(
sno varchar2(20) NOT NULL UNIQUE,
name varchar2(20) UNIQUE,
sex varchar2(20),
age int,
dept varchar2(20),
key varchar2(20),
class varchar2(20));

向学生表添加数据：
insert into student values ('10001','mike','man',20,'computer','mike','0104');
insert into student values ('10002','joe','man',21,'computer','joe','0104');
insert into student values ('10003','jane','woman',20,'computer','jane','0104');
insert into student values ('10004','marry','woman',19,'computer','marry','0104');
insert into student values ('10005','lee','man',23,'computer','lee','0104');
insert into student values ('10006','tom','man',23,'computer','tom','0104');
insert into student values ('10007','jim','man',22,'computer','jim','0104');
insert into student values ('10008','jay','man',21,'computer','jay','0104');

班级表:
create table class(
grd varchar2(20),
class varchar2(20) NOT NULL PRIMARY KEY,
room varchar2(20) ,
year varchar2(20),
dept varchar2(20),
teacher varchar2(20));

向班级表添加数据：
insert into class values ('大四','0104','201','4','computer','lee');
insert into class values ('大一','0402','201','4','computer','joe');
insert into class values ('大一','0403','201','4','computer','zhou');

课程表：
create table course(
cno varchar2(20) NOT NULL UNIQUE,
cname varchar2(20),
teacher varchar2(20),
profession varchar2(20),
snum int,
course int);

向课程表添加数据：
insert into course values ('1001','Mathe','tian','associate professor',30,3);
insert into course values ('1002','Physic','zhang','professer',30,3);
insert into course values ('1003','English','janny','lecturer',30,3);
insert into course values ('1004','Visual C++','tong','professor',30,2);
insert into course values ('1005','Operate System','lu','lecturer',30,3);
insert into course values ('1006','Date Structure','zhou','associate professor',30,3);
insert into course values ('1007','Dlephy','zhang','professer',35,3);

选课表：
create table choose(
sno varchar2(20) NOT NULL, 
cno varchar2(20) NOT NULL,
grade int);

向选课表添加数据：
insert into choose values ('10001','1001',78);
insert into choose values ('10001','1002',70);
insert into choose values ('10001','1003',66);
insert into choose values ('10001','1004',80);
insert into choose values ('10001','1005',87);
insert into choose values ('10001','1006',60);
insert into choose values ('10002','1001',78);
insert into choose values ('10002','1002',74);
insert into choose values ('10002','1003',73);
insert into choose values ('10003','1001',78);
insert into choose values ('10003','1003',77);
insert into choose values ('10004','1001',78);
insert into choose values ('10005','1001',45);
insert into choose values ('10006','1001',57);
insert into choose values ('10007','1001',85);
insert into choose values ('10008','1001',90);

登录表：
create table use(
username varchar2(20) NOT NULL PRIMARY KEY, 
password varchar2(20) NOT NULL,
ident varchar2(20));

向登录表添加数据：
insert into use values ('he','he','manager');
insert into use values ('ting','ting','user');

create table mtemp(
sno varchar2(20),
num int); 

insert into mtemp (sno,num) select sno,count(cno) from choose group by sno;

drop table mtemp;

select count(sno) group by cno where sno = (
select sno from choose having count(*)=3 group by sno);


select cno,count(*) from choose having count(*)=(select max(count(*)) from choose group by cno)
group by cno;

select course.cname,course.teacher from course where course.cno in(
select choose.cno from choose having count(*)=(select max(count(*)) from choose group by cno)group by cno);

select course.cname,course.teacher from course where course.cno in(select choose.cno from choose having count(*)=(select max(count(sno)) from choose group by cno)group by cno);

select max(count(*)) from choose group by sno;
select choose.sno from choose having count(*)=(select max(count(*)) from choose group by sno) group by sno;
select student.sno,student.name,course.cname from student,course where student.sno in(select choose.sno from choose having count(*)=(select max(count(*)) from choose group by sno) group by sno);

select student.sno,student.name,student.sex,student.age,student.dept,student.class from student where student.sno in(select choose.sno from choose having count(*)=1 group by sno);

select * from course where cno in (select cno from choose where sno='10001');

select count(*) from course where cno in(select cno from choose where sno='10001');

select * from choose where grade < 60 and sno in (select sno from choose where grade < 60 group by sno having count(*)>1);

select * from course where cno in (select cno from choose where grade<60 group by cno having count(*)=(select max(count(*)) from choose where grade<60 group by cno));

select * from course where cno in (select cno from choose  group by cno having avg(grade)=(select max(avg(grade)) from choose  group by cno));



SQL> select grade from choose where cno in(select cno from course where cname='Mathe');

    GRADE
---------
       78
       78
       78
       78
       45
       57
       85
       90

8 rows selected.

SQL> select grade from choose where cno in(select cno from course where cname='English');

    GRADE
---------
       66
       73
       77
select grade from choose where grade in(select grade from choose where cno in(select cno from course where cname='English') and grade<70);

SQL> select grade from choose where grade in(
  2  select grade from choose where cno in(select cno from course where cname='English') and grade<7
0);

    GRADE
---------
       66

select count(*) from choose where grade in(select grade from choose where cno in(select cno from course where cname='English') and grade<70);