页眉内容
实验三 建表、修改表、删除表
1.建立数据库jxgl
在jxgl数据库中建立学生表student,课程表course,选修表sc，建表过程有如下方式 1）以图形界面操作
2）以SQL操作（以下脚本可直接在查询分析器中执行） Create Table Student
(Sno CHAR(5) NOT NULL PRIMARY KEY(Sno), Sname VARCHAR(20),
Sage SMALLINT CHECK(Sage>=15 AND Sage<=45), Ssex CHAR(2) DEFAULT '男' CHECK (Ssex='男' OR Ssex='女'), Sdept CHAR(2));
Create Table Course
(Cno CHAR(2) NOT NULL PRIMARY KEY(Cno), Cname VARCHAR(20), Cpno CHAR(2),
Ccredit SMALLINT);
Create Table SC
(Sno CHAR(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES Student(Sno),
Cno CHAR(2) NOT NULL,
Grade SMALLINT CHECK((Grade IS NULL)OR (Grade BETWEEN 0 AND 100)), PRIMARY KEY(Sno,Cno),
CONSTRAINT C_F FOREIGN KEY(Cno) REFERENCES Course(Cno));
INSERT INTO Student VALUES('98001','钱横',18,'男','CS'); INSERT INTO Student VALUES('98002','王林',19,'女','CS'); INSERT INTO Student VALUES('98003','李民',20,'男','IS'); INSERT INTO Student VALUES('98004','赵三',16,'女','MA'); INSERT INTO Course VALUES('1','数据库系统','5',4); INSERT INTO Course VALUES('2','数学分析',null,2); INSERT INTO Course VALUES('3','信息系统导论','1',3); INSERT INTO Course VALUES('4','操作系统原理','6',3); INSERT INTO Course VALUES('5','数据结构','7',4);
INSERT INTO Course VALUES('6','数据处理基础',null,4); INSERT INTO Course VALUES('7','C语言','6',3); INSERT INTO SC VALUES('98001','1',87); INSERT INTO SC VALUES('98001','2',67); INSERT INTO SC VALUES('98001','3',90); INSERT INTO SC VALUES('98002','2',95); INSERT INTO SC VALUES('98002','3',88);
页脚内容9
页眉内容
课程表Course（课程号Cno,课程名Cname,先修课号Cpno,学分Ccredit）
学生表Student（学号Sno,姓名Sname,年龄Sage,性别Ssex,所在系Sdept）
学生选课表SC（学号Sno,课程号Cno,成绩Grade)
页脚内容9
页眉内容
实验四 Select查询操作练习
（1）基于“教学管理”数据库jxgl，使用SQL的查询语句表示下列查询： ①检索年龄大于23岁的男学生的学号和姓名； select Sno,Sname from Student
where Sage>'23' AND Ssex='男';
②检索至少选修一门课程的女学生的姓名； select Sname from Student,SC
where Ssex='女' AND Student.Sno=SC.Sno group by Student.Sname having count(*)>=1;
或者
Select Sname From Student Where Ssex='女' AND Sno in (select sno from SC
group by sno
having count(*)>=1);
③检索王同学不学的课程的课程号； select Cno from Course
where Course.Cno not in (select Cno
from SC,Student
where SC.Sno=Student.Sno AND Sname LIKE '王%');
④检索至少选修两门课程的学生学号； select DISTINCT Student.Sno
from Student,SC
页脚内容9
页眉内容
WHERE Student.Sno=SC.Sno
GROUP BY Student.Sno HAVING COUNT(*)>=2;
⑤检索全部学生都选修的课程的课程号与课程名； SELECT Cno,Cname from Course where not exists (select * from student where not exists (select * from SC
where SC.sno=Student.Sno AND SC.Cno=Course.Cno) )
或者假设所有学生只有两人 SELECT Cno,Cname from Course
WHERE Course.Cno in (select Cno from SC
group by SC.Cno having count(Sno)= (select count(*) from Student));
SELECT Cno,Cname from Course
WHERE Course.Cno in (select Cno from SC
group by SC.Cno
having count(Sno)=2);
页脚内容9
页眉内容
⑥检索选修了所有3学分课程的学生学号。 select distinct Student.Sno
from Student,SC where exists (select * from Course
where Ccredit ='3' AND Student.Sno=SC.Sno AND SC.Cno=Course.Cno);
(2)基于“教学管理”数据库jxgl,试用SQL的查询语句表达下列查询： ①统计有学生选修的课程门数； select count(distinct SC.Cno) FROM SC;
②求选修4号课程的学生的平均年龄； SELECT avg(Student.Sage) from Student,SC
where Student.Sno=SC.Sno AND Cno='4';
SELECT avg(Student.Sage) as 平均年龄 from Student,SC
where Student.Sno=SC.Sno AND Cno='3';
③求学分为3的每门课程的学生平均成绩；
页脚内容9
数据库实验
