浙江万里学院2022/20232学年第一学期中 《数据库技术》上机测试

浙江万里学院2022/20232学年第一学期中
《数据库技术》上机测试
题目：

1. 创建数据库，名称为考生的学号 （5分）
   字段名 类型 描述 主键 外键 非空 唯一 自增
   Student_No INT（10） 学号 是 否 是 是 否
   name VARCHAR(20) 姓名 否 否 是 否 否
   sex VARCHAR(4) 性别 否 否 是 否 否
   Age INT 年龄 否 否 否 否 否
   depart VARCHAR(15) 院系 否 否 是 否 否
   addr VARCHAR(20) 家庭地址 否 否 否 否 否
2. 创建student_info(学生信息表), fenshu_info(课程成绩表)，表的结构如下。（10分）

student_info 的结构：

字段名 类型 描述 主键 外键 非空 唯一 自增
Id INT（10） 编号 是 否 是 是 是
Student_id INT（10） 学号 否 否 是 否 否
C_name VARCHAR(8) 课程名 否 否 否 否 否
fenshu FLOAT 分数 否 否 否 否 否
fenshu_info的结构

3. 录入数据如下 （10分）
   student_info的数据如下：最后一条记录用你的学号和你的姓名代替
   Student_No name sex age depart addr
   2015663456 李四 男 20 计算机系 浙江舟山海港村
   2010013345 王五 女 19 软件工程系 浙江象山枇杷村
   2012345790 阿尔法 女 24 信息工程系 浙江慈溪杨梅村
   2012030842 西门人 男 22 英语系 浙江宁海大一村
   2015690623 马云三 男 21 英语系 浙江三门青蟹村
   2014672901 刘夫 男 26 信息工程系 浙江三门里外村
   你的学号 你的姓名 你的性别 你的年龄 你所在的系 你的家庭地址

向fenshu_info录入数据如下

Id Student_id C_name fenshu
2015663456 计算机组成 95
2015663456 数据库 90
2010013345 软件工程 55
2010013345 数据库 86
2012345790 英语 81
2012030842 英语 72
2012030842 编译原理 68
2015690623 英语 52
你的学号 数据库 96

4. 查询student_info表的所有记录

5. 查询student_info表的第3条到5条记录

6. 从student_info表查询所有学生的学号（Student_No）、姓名（name）和院系（depart）的信息

7. 从student_info表中查询计算机系和英语系的学生的信息

8. 从student_info表中查询年龄19~22岁且性别为男的学生信息

9. 查询地址在“三门”的学生信息

10. 统计每个系的男女生人数

11. 统计人数在2人以上的系

12. 从fenshu_info表中查询每个科目的最高分

13. 查询每个科目的最高分的学生姓名及成绩

14. 查询西门人的考试科目和成绩

15. 统计每个学生（包括学生姓名）的总成绩

16. 计算每个考试科目的平均成绩

17. 查询同时参加数据库和英语考试的学生的信息

18. 查询姓李同学的姓名、院系和考试科目及成绩

19.将数据库考试成绩按从高到低进行排序

20．请导出实验用数据库

答案上传：请以你的学号+姓名 命名此文件， 并和导出的数据库一起分别提交至moodle。

1.	CREATE DATABASE 2022031160_

2.	   
CREATE TABLE student_info (
	Student_No INT(10) PRIMARY key NOT null unique  COMMENT '学号',
	name VARCHAR(20) NOT NULL COMMENT '姓名',
	sex VARCHAR(4) NOT NULL COMMENT '性别',
	Age INT  COMMENT '年龄',
	depart VARCHAR(15) NOT NULL COMMENT '院系',
	addr VARCHAR(20)  COMMENT '家庭住址'
)


	CREATE TABLE fenshu_info (
	Id INT(10) PRIMARY key NOT null unique auto_increment  COMMENT '编号',
	Student_id int(10) NOT NULL COMMENT '学号',
	C_name VARCHAR(8)  COMMENT '课程名',
	fenshu FLOAT  COMMENT '分数'
)

3.
insert into student_info(Student_No,name,sex,Age,depart,addr)values
(2015663456,'李四','男',20,'计算机系','浙江舟山海港村'),
(2010013345,'王五','女',19,'软件工程系','浙江象山枇杷村'),
(2012345790,'阿尔法','女',24,'信息工程系','浙江慈溪杨梅村'),
(2012030842,'西门人','男',22,'英语系','浙江宁海大一村'),
(2015690623,'马云三','男',21,'英语系','浙江三门青蟹村'),
(2014672901,'刘夫','男',26,'信息工程系','浙江三门里外村'),
(2022031160,'陈鹏','男',21,'大数据与软件工程系','浙江宁波鄞州区');

 

INSERT INTO fenshu_info(student_id,C_name,fenshu)VALUES
(2015663456,'计算机组成',95),
(2015663456,'数据库',90),
(2010013345,'软件工程',55),
(2010013345,'数据库',86),
(2012345790,'英语',81),
(2012030842,'英语',72),
(2012030842,'编译原理',68),
(2015690623,'英语',52),
(2022031160,'数据库',96);

4.
SELECT * FROM student_info

5.
SELECT * FROM student_info
LIMIT 3,5

6.
SELECT Student_No,name,depart FROM student_info

7.
SELECT * FROM student_info
WHERE depart = '计算机系' or depart ='英语系'

8.
SELECT * FROM student_info
WHERE sex ='男' and Age BETWEEN 19 and 22
group by age

9.
SELECT * FROM student_info
WHERE addr like'%三门%'

10.
select depart,sex,count(Student_No)  from student_info 
group by depart,sex 
order by depart desc

11.

select depart ,count(depart) as 人数 from student_info
group by depart
having count(depart)>=2

12.
SELECT MAX(fenshu) as '最高分',C_name FROM fenshu_info
GROUP BY  C_name

13.
select C_name as 课程名,name as 姓名,max(fenshu) as 最高分 from  fenshu_info,student_info WHERE student_info.Student_No=fenshu_info.Student_id GROUP BY C_name;

14.
SELECT fenshu_info.C_name ,fenshu_info.fenshu FROM fenshu_info,student_info
WHERE student_info.Student_No = fenshu_info.Student_id and student_info.`name`='西门人'

15.
SELECT student_id AS '学号' , name AS '姓名',SUM(fenshu) AS '总成绩'
FROM fenshu_info,student_info
WHERE fenshu_info.student_id = student_info.Student_No
GROUP BY student_id

16.
SELECT C_name AS '考试科目',AVG(fenshu) AS '平均成绩'
FROM fenshu_info
GROUP BY C_name

17.
select * from student_info ,fenshu_info  
where student_info.Student_No=fenshu_info.Student_id and fenshu_info.C_name='数据库' and student_info.Student_No=fenshu_info.Student_id and fenshu_info.C_name='英语';

18.
SELECT student_info.`name`,student_info.depart, fenshu_info.C_name , fenshu_info.fenshu FROM student_info,fenshu_info
WHERE student_info.`name` LIKE'李%' and fenshu_info.student_id = student_info.Student_No

19.
SELECT fenshu FROM fenshu_info
WHERE C_name='数据库'
ORDER BY fenshu DESC