oracle数据库小练习

数据库练习

建表

create table student(
  sno number(10) primary key,
  sname varchar(20) not null,
  ssex varchar(10) not null,
  sage number(5) not null,
  sdept varchar(20) not null 
);
insert into student(sno,sname,ssex,sage,sdept) values(9512101,'李勇','男',19,'计算机系');
insert into student(sno,sname,ssex,sage,sdept) values(9512102,'刘晨','男',20,'计算机系');
insert into student(sno,sname,ssex,sage,sdept) values(9512103,'王敏','女',20,'计算机系');
insert into student(sno,sname,ssex,sage,sdept) values(9521101,'张立','男',22,'信息系');
insert into student(sno,sname,ssex,sage,sdept) values(9521102,'吴宾','女',21,'信息系');
insert into student(sno,sname,ssex,sage,sdept) values(9521103,'张海','男',20,'信息系');
insert into student(sno,sname,ssex,sage,sdept) values(9531101,'钱小平','女',18,'数学系');
insert into student(sno,sname,ssex,sage,sdept) values(9531102,'王大力','男',19,'数学系');

create table course(
  cno varchar(10) primary key,
  cname varchar(20) not null,
  ccredit number(3) not null,
  period number(3) not null
);
insert into course(cno,cname,ccredit,period) values('C01','计算机文学',3,1);
insert into course(cno,cname,ccredit,period) values('C02','VB',2,3);
insert into course(cno,cname,ccredit,period) values('C03','计算机网络',4,7);
insert into course(cno,cname,ccredit,period) values('C04','数据库基础',6,6);
insert into course(cno,cname,ccredit,period) values('C05','高等数学',8,2);
insert into course(cno,cname,ccredit,period) values('C06','数据结构',5,4);

create table SC(
  sno number(10) not null,
  cno varchar(10) not null,
  grade number(10),
  must varchar(10) not null
);
insert into SC(sno,cno,grade,must) values(9512101,'C01',90,'必修');
insert into SC(sno,cno,grade,must) values(9512101,'C02',86,'选修');
insert into SC(sno,cno,grade,must) values(9512101,'C06',NULL,'必修');
insert into SC(sno,cno,grade,must) values(9512102,'C02',78,'选修');
insert into SC(sno,cno,grade,must) values(9512102,'C04',66,'必修');
insert into SC(sno,cno,grade,must) values(9521102,'C01',82,'选修');
insert into SC(sno,cno,grade,must) values(9521102,'C02',75,'选修');
insert into SC(sno,cno,grade,must) values(9521102,'C04',92,'必修');
insert into SC(sno,cno,grade,must) values(9521102,'C05',50,'必修');
insert into SC(sno,cno,grade,must) values(9521103,'C02',68,'选修');
insert into SC(sno,cno,grade,must) values(9521103,'C06',NULL,'必修');
insert into SC(sno,cno,grade,must) values(9531101,'C01',80,'选修');
insert into SC(sno,cno,grade,must) values(9531101,'C05',95,'必修');
insert into SC(sno,cno,grade,must) values(9531102,'C05',85,'必修');

查询学生选课表中的全部数据

select * from SC;

查询计算机系学生的姓名,年龄.

select sname,sage
from student where sdept=‘计算机系’;

查询成绩在70~80分之间的学生的学号,课程号和成绩.

select sc.sno,sc.cno,sc.grade
from student,SC where student.sno=SC.SNO and sc.grade>69 and sc.grade<81;

查询计算机系年龄在18~20之间且性别为"男"的学生的姓名和年龄.

select sname,sage
from student where student.sdept=‘计算机系’ and student.sage>=18 and student.sage<=20 and student.ssex=‘男’;

查询课程号为"C01"的课程的最高分数

select max(sc.grade)
from SC where cno=‘C01’;

查询计算机系学生的最大年龄和最小年龄.

select max(sage)，min(sage)
from student where sdept=‘计算机系’;

统计每个系的学生人数.

select sdept,count(*)
from student group by sdept;

统计每门课程的选课人数和考试最高分.

select
sc.cno,count(sc.cno),max(grade)
from SC,course where course.cno=SC.CNO
group by sc.cno;

查询每个学生的选课门数和考试总成绩,并按选课门数的升序显示结果

select
sno,count(cno),sum(grade)
from SC group by sno order by count(cno);

查询选修了"C02"的学生的姓名和所在系

select
sname,sdept
from SC h,student s where h.sno=s.sno and h.cno=‘C02’;

要求;用子查询(每题15分)
(1).查询选修了课程"C01"的学生的姓名和所在系

select
sname,sdept
from student where student.sno in(select sc.sno from sc where sc.cno=‘C01’);

(2).查询数学系成绩在80分以上的学生的学号,姓名.

select
sno,sname
from student where student.sno in(select sc.sno from sc where sc.grade>=80) and student.sdept=‘数学系’;