数据库原理与应用（15）PTA补充题库、实验报告汇编

【实验报告1】

一、填空题

二、主观题

1. 画出学校与校长关系的E-R图。

2. 画出班级与学生关系的E-R图。

3. 某汽车运输公司数据库E-R图：

设某汽车运输公司数据库中有三个实体。

一是“车队”实体，属性有车队号、车队名等；

二是“车辆”实体，属性有牌照号、厂家、出厂日期等；

三是“司机”实体，属性有司机编号、姓名、电话等。

其中：设每个车队可聘用若干司机，但每个司机只能应聘于一个车队，车队聘用司机有聘期；每个车队可拥有若干车辆，但每辆车只能属于一个车队；每个司机可使用多辆汽车，每辆汽车可被多个司机使用，司机使用车辆需登记使用日期和公里数。

1、根据题意画出ER图，并在图上注明实体、属性、联系及联系的类型。

2、将E-R图转换为等价的关系模式，并指出每个关系的主码和外码。

4. 软件公司信息管理系统E-R图。

宁波有一软件公司为某田径锦标赛设计一个信息管理系统。经过分析该系统有如下3个实体：

运动队，属性有队号、队名、教练姓名；

运动员，属性包括运动员号、姓名、性别、年龄；

比赛项目，属性有项目名、比赛场地。

其中：一个运动队有一个教练，多个运动员；一个运动员仅属于一个运动队；一个比赛项目有多个运动员参加，一个运动员可以参加多个项目，对每个运动员的每一个参赛项目均记录名次。

根据以上情况，请完成如下任务：

1、设计田径锦标赛管理系统的E-R图；

2、将E-R图转换为等价的关系模式，并指出每个关系的主码和外码。

【实验报告2】

1. 用Navicat管理工具提供的图形界面创建表。

在JXGL数据库中创建表students表和sc表。表结构要求见图，仅建表，先不添加约束。

2. 用Navicat图形界面操作修改表结构。

(1) 向students表中增加“入学日期”列(enrollment)，其数据类型为日期型。

(2) 将students表中的sdept字段长度改为20。

(3) 将students表中的sdept字段改为不允许为空。

(4) 将students表中的speciality字段改名为spec。

(5) 将students表中的spec字段删除。

注：完成效果发来截图上传附件

3. 利用create table 命令创建course表，表结构见下图，仅建表，先不添加约束。

4. 用Navicat图形界面操作给表格添加约束。

(1) 给students表和course表添加主键。

(2) 给students表的sname字段设置非空约束。

(3) 给sc表添加外键，关联到students表和course表。

(4) 给students表的IDNum字段设置唯一性约束。

(5) 给students表中的ssex字段设置默认约束“男”。

5. 在students表、course表、sc表中录入一些数据，每个表录入3条数据，截图上传附件。

【实验报告3中（一）简单查询】

10-1 查询所有学生的学号、姓名、性别和出生日期

select sno,sname,ssex,bday from students;

 

10-2 查询前3门课程的课号及课程名称

select cno,cname from course limit 0,3;

 

10-3 查询2050年所有学生的姓名及年龄，要求结果中列名显示中文

select sname 姓名,2050-year(bday) 年龄 from students;

 

10-4 查询至2050年所有年龄小于等于55岁的女生的学号和姓名

select sno 学号,sname 姓名,ssex 性别 from students where 2050-year(bday)<=55 and ssex='女';

 

10-5 查询“信息学院”的学生姓名、性别和出生日期

select sname,ssex,bday from students where sdept='信息学院';

 

10-6 查询Students表中的所有系名，要求结果中系名不重复

select distinct sdept from students;

 

10-7 查询“0000010”课程的课名、先修课号和学分

select cname,cpno,ccredit from course where cno='0000010';

 

10-8 查询成绩在80～90分之间的选课成绩情况

select * from sc where score>=80 and score<=90;

 

10-9 查询成绩为69分、79分或89分的记录

select * from sc where score in(69,79,89);

 

10-10 查询在1970年1月1日之前出生的男教师信息

select * from teachers where tbirthday<='1970-1-1' and tsex='男';

 

10-11 输出有成绩的学生学号和课号

select sno,cno from sc where score is not null;

 

10-12 查询所有姓“刘”的学生信息

select * from students where sname like '刘%';

 

10-13 查询生源地不是“山东”省的学生信息

select * from students where bplace not like '山东%';

 

10-14 查询名字中含有“明”字的男生的学生姓名和班级

select sname,class from students where sname like '%明%';

 

10-15 查询姓名是两个字的学生信息

select * from students where sname like '__';

 

10-16 查询非信息学院和机电学院的学生信息

select * from students where sdept not like '信息学院' and sdept not like '机电学院';

 

10-17 查询学生表中没有联系电话的学生信息

select * from students where phone is null;

 

【实验报告3中（二） 简单查询（二）及聚合函数】

10-1 从学生表统计总的学生人数

select count(*) 人数 from students;

 

10-2 统计有学生选修的课程的门数,多人选修同一门只算一门

select count(distinct cno) 门数 from sc;

 

10-3 计算“0000001”课程的平均分、最高分和最低分

select avg(score) 平均分, max(score) 最高分, min(score) 最低分 from sc where

cno='0000001';

 

10-4 查询选修了“0000008”课程的学生的学号及其成绩，查询结果按分数降序排列

select sno,score from sc where cno='0000008' order by score desc;

 

10-5 查询成绩不及格的学生学号、课号和成绩，并按成绩降序排列

select sno,cno,score from sc where score<60 order by score desc;

【实验报告3中（二） 分组查询】

10-1 查询各个课程号及相应的选课人数

select cno 课程号,count(sno) 选课人数 from sc group by cno;

 

10-2 统计每门课程的选课人数和最高分

select cno 课程号, count(sno) 选课人数, max(score) 最高分 from sc group by cno;

 

10-3 查询选修了3门以上课程的学生学号

select sno 学号, count(cno) 选课门数 from sc group by sno having count(cno)>3;

 

10-4 统计输出各系学生的人数

select sdept 系, count(sno) 人数 from students group by sdept;

 

10-5 统计每个学生的选课门数和考试总成绩，并按选课门数升序排列

select sno 学号, count(cno) 选课门数, sum(score) 考试总成绩 from sc group by sno order by 选课门数 asc;

 

10-6 统计各系的男、女生人数

select sdept 系别, ssex 性别, count(sno) 人数 from students group by sdept,ssex order by sdept;

 

10-7 统计各班男、女生人数

select class 班级, ssex 性别, count(sno) 人数 from students group by class,ssex order by class;

 

10-8 统计各系的老师人数，并按人数升序排序

select tdept 系别, count(tno) 教师人数 from teachers group by tdept order by count(tno) asc;

 

10-9 统计不及格人数超过3人的课程号和人数

select cno 课程号, count(*) 不及格人数 from sc where score<60 group by cno having count(*)>3;

 

10-10 查询信息学院的男生信息，查询结果按出生日期升序排序，出生日期相同的按生源地降序排序

select * from students where sdept='信息学院' and ssex='男' order by bday, bplace desc;

 

10-11 统计选修人数最多的3门课

select cno 课程号, count(*) 选修人数 from sc group by cno order by count(*) desc limit 0,3;

【实验报告4中（一） 多表连接查询】

10-1 查询信息学院女学生的学生学号、姓名、课号及考试成绩

select students.sno,sname,cno,score from students join sc on students.sno=sc.sno where sdept='信息学院' and ssex='女';

 

10-2 查询“陈红”同学所选课程的成绩，列出课号和成绩(不考虑重名)

select cno,score from sc join students on sc.sno=students.sno where sname='陈红';

 

10-3 查询“王珊”老师所授课程的课程名称

select distinct cname from course inner join teaching on course.cno=teaching.cno inner join teachers on teaching.tno=teachers.tno where teachers.tname='王珊';

 

10-4 查询女教师所授课程的课程号和课程名称

select distinct course.cno,cname from course inner join teaching on course.cno=teaching.cno inner join teachers on teaching.tno=teachers.tno where tsex='女';

 

10-5 查询至少选修2门课程的女生姓名

select students.sname from students join sc on students.sno=sc.sno where students.ssex='女' group by sname having count(*)>2;

 

10-6 查询选修课名中含有“数据库”三个字的课程且成绩在80～90分之间的学生学号及成绩

select sno,score from sc left join course on sc.cno=course.cno where cname like '%数据库%' and score between 80 and 90;

 

10-7 查询选修“0000011”课程的学生至2050年时平均年龄

select avg(2050-year(bday)) 平均年龄 from students join sc on students.sno=sc.sno where cno='0000011';

 

10-8 查询“谭浩强”教师任课的课程号，选修其课程的学生的学号和成绩，结果中包括该老师没有被选修的课程

select teaching.cno,sno,score from teaching left join sc on teaching.cno=sc.cno left join teachers on teaching.tno=teachers.tno where tname='谭浩强';

 

10-9 列出所有学生的选课情况（包括学号，姓名，课号，成绩），结果中包括没有选课的学生

select students.sno,sname,cno,score from sc right outer join students on sc.sno=students.sno;

 

10-10 查询没有选课的学生学号和姓名

select sno,sname from students where sno not in(select sno from sc);

【实验报告4中（二） 嵌套子查询】

10-1 查询信息学院学生所选修的课程号和成绩

select cno,score from sc where sno in (select sno from students where sdept='信息学院');

 

10-2 查询与“陆毅”同一个系的同学姓名

select sname from students where sdept=(select sdept from students where sname='陆毅') and sname!='陆毅';

 

10-3 查询“19信管2”班的学生所选修的课程号

select cno from sc where sno in (select sno from students where class='19信管2');

 

10-4 查询“陈晓东”同学所选课程的课号及成绩

select cno,score from sc where sno in (select sno from students where sname='陈晓东');

 

10-5 查询选修了课号为“0000034”的学生学号和姓名

select sno,sname from students where sno in (select sno from sc where cno='0000034');

 

10-6 查询“0000008”号课程不及格的学生信息

select * from students where sno in (select sno from sc where cno='0000008' and score<60);

 

10-7 查询李小鹏同学所选课程名称

select cname from course where cno in (select cno from sc where sno in (select sno from students where sname='李小鹏'));

 

10-8 查询“王珊”老师所授课程的课程名称

select cname from course where cno in (select cno from teaching where tno in (select tno from teachers where tname='王珊'));