2022年数据库实验.pdf

数据库实验实验三建表、修改表、删除表1、建立数据库jxgl 在jxgl 数据库中建立学生表student,课程表 course,选修表 sc,建表过程有如下方式1)以图形界面操作2)以SQL操作 (以下脚本可直接在查询分析器中执行) CreateTable Student (Sno CHAR (5) NOT NULL PRIMARY KEY (Sno), Sname VARCHAR (20), Sage SMALLINTCHECK (Sage=15 AND Sage23 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 王%); 检索至少选修两门课程的学生学号; selectDISTINCT Student、Sno from Student,SC WHERE Student、Sno=SC、Sno 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 3 页，共 9 页 - - - - - - - - - - 数据库实验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); 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 4 页，共 9 页 - - - - - - - - - - 数据库实验检索选修了所有3 学分课程的学生学号。selectdistinct 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 的查询语句表达下列查询: 统计有学生选修的课程门数; selectcount(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 的每门课程的学生平均成绩; 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 5 页，共 9 页 - - - - - - - - - - 数据库实验SELECT avg(SC、Grade) from Course,SC,Student where Student、Sno=SC、Sno AND Course、Ccredit=3 group by SC、Cno 用group by 语句以课程号分组注意 ,如果程序就是这样的: SELECT avg(SC 、Grade) from Course,SC,Student where Student、Sno=SC、Sno AND Course、Ccredit=3AND Course 、Cno=SC、Cno; 只显示一门课程的成绩！统计每门课程的学生选修人数,要求超过3 人的课程才统计,要求输出课程号与选修人数,查询结果按人数降序排列,若人数相同 ,按课程号升序排列; SELECT Cno,count(Sno) from SC GROUP BY Cno HAVING Count(Sno)3 order by count(sno)DESC,Cno; SELECT Cno , count( Sno )as选修人数from SC GROUP BY Cno HAVINGCount ( Sno ) 1 orderbycount( sno ) DESC, Cno ; 检索学号比“王林”同学大而年龄比她小的学生姓名; SELECT Sname FROM Student where Sno (select Sno from Student where Sname=王林 ) 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 6 页，共 9 页 - - - - - - - - - - 数据库实验AND Sage Y、Sno AND X、Sage(select avg(Sage ) from student where Ssex=女) AND Ssex=男; 求年龄大于所有女学生年龄的男学生姓名与年龄; SELECT Sname,Sage from Student where Sage(SELECT MAX (Sage) 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 7 页，共 9 页 - - - - - - - - - - 数据库实验from Student where Ssex=女) AND Ssex=男; 检索所有比“王林”年龄大的学生姓名、年龄与性别; SELECT Sname,Sage ,Ssex from Student where Sage(select Sage from Student where Sname=王林 ) 检索选修“ 2”课程的学生中成绩最高的学生的学号; SELECT Sno,Gradefrom SC where Grade=(select MAX (Grade) from SC where Cno=2); 注意 :不能写成 Grade=MAX(Grade) 的形式 ,因为不存在！ Count,min,avg 也就是检索学生姓名以及所选修课程的课程号与成绩; select Sname,Cno,Grade from SC,Student where Student、Sno=SC、Sno; 检索选修4 门以上课程的学生总成绩(不统计不及格的课程),并要求按总成绩的降序排列出来。选择 4门以上select sno,sum(Grade) sum from SC where grade=60 AND Sno in (select Sno from SC group by Sno 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 8 页，共 9 页 - - - - - - - - - - 数据库实验having count(Sno)4) group by sno order by sum(grade) desc 注意 :选择2门课(包括两门 ) select sno,sum(Grade) from SC where grade=60 AND Sno in (select Sno from SC group by Sno having count(Sno)=2) group by sno order by sum(grade) desc 再注意 : Select sno,sum(grade) From SC Where grade=60 Group by sno having count(sno)1 Order by sum(grade) desc 因为输入的数据分数都大于60,所以结果与上面的例子相同。但就是,当分数小于60 时,有课不及格的同学的学号会被删除,统计不完整。精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 9 页，共 9 页 - - - - - - - - - -