数据库实验hitwh.docx

实验 1

1、 创建课程表

use shiyan

create table course

(

cno int,

cname char(20)not null,

cpno int,

(3,'信息系统',1,4),

(4,'操作系统',6,3),

(5,'数据结构',7,4),

(6,'数据处理',null,2),

(7,'Pascal 语言',6,4)

2、创建学生表

use shiyan

create table student

(

sclass int,

)

insert into student values(1,1,'李勇','男',20,'is'),

(1,2,'刘晨','女',19,'is'),

(1,3,'刘朋','男',20,'is'),

(2,1,'王敏','女',18,'ma'),

(2,2,'张锋','男',19,'ma'),

(2,3,'李敏','男',20,'ma')

3、建立选课表

use shiyan

create table sc

foreign key (sclass,sno)references student(sclass,sno),

foreign key (cno)references course(cno)

)

insert into SC values (1,1,1,92),

(1,1,2,85),

(1,1,3,88),

(1,2,2,90),

(1,2,3,80),

(2,1,1,75),

(2,1,2,92),

left join course c on(sc.cno=c.cno)

5、查询一班的学号及姓名

select sno,sname

from student

where sclass=1;

6、查询‘刘晨’的出生年

select 2014-sage brith

from student

where sname='刘晨';

7、查询姓刘的学生的详细情况

from student s ,sc

where sc.cno=1 and sc.sno=s.sno and sc.sclass=s.sclass;

9、查询没有先行课 的课程号和课程名

select cno,cname

from course

where cpno is null;

10、查询 2 班所有女生的情况

select s.sclass,s.sno,sname,ssex,sage,sdept,c.cno,cname,cpno,ccredit,greade

12、查询选修 1 或 2 号课程的学生班号、学号、姓名、课程名及成绩

select s.sclass,s.sno,sname,c.cname,greade

from student s,course c,sc

where (sc.cno=1 or sc.cno=2) and sc.cno=c.cno and s.sclass=sc.sclass and s.sno=sc.sno;

13、查询 2 班至少选修一门其先修课为 1 号课的学生的班号、学号、姓名、性别、系、课程号

及成绩

select s.sclass,s.sno,sname,ssex,sdept,c.cno,greade

from student s,course c,sc

where sc.cno=c.cno and s.sclass=sc.sclass and s.sno=sc.sno

and s.sno in

14、查询 2 号课成绩最高的学生班号、学号

select sclass,sno

from sc

where greade=

(select max(greade)

from sc

where cno=2

) and cno=2;

15、查询一班 2 号课成绩最低的学生班号、学号

select sclass,sno

where greade>

(select min(greade)

from sc

where cno=2

) and cno=2 ;

实验 2

一、数据库 SQL 语言的增、删、改、查功能

1、查询选了 1 号课且选了 2 号课的学生的班号、学号

select sc1.sclass,sc1.sno

from sc sc1

2、 查询了 1 号课但不选 2 号课的学生班号、学号

select sc1.sclass,sc1.sno

from sc sc1

where sno not in

(

select sno

from sc

where cno=2 and sc1.sclass=sc.sclass

)and cno=1;

3、 查询 1 班平均分在 85 分以上的同学的班号、学号及同学姓名

where s.sno not in

(

select sno

from sc s2

where s2.sclass=s.sclass and s2.cno=1

)

from sc

where sc1.sclass=sc.sclass and sc.cno=3

values (8,'无机化学',null,1)

go

select * from student

go