数据库练习
建表
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=‘数学系’;