数据库机试试题现有 3 张数据表表的关系模式及数据如下 带下划线的是主键：学生表 studentsnosnamesexbirthdaydepart学号 姓名 性别 出生日期 系别1001 李明 男 2002-3-15 计算机系1002 张然 女 2003-11-5 经管系1003 许晴 女 2002-8-11 电子系课程表 coursecnocnamecredit课程号 课程名 学分001 My

1. 创建学生表： CREATE TABLE student ( sno INT PRIMARY KEY, sname VARCHAR(50), sex VARCHAR(10), birthday DATE, depart VARCHAR(50) );

插入学生数据： INSERT INTO student (sno, sname, sex, birthday, depart) VALUES (1001, '李明', '男', '2002-03-15', '计算机系'), (1002, '张然', '女', '2003-11-05', '经管系'), (1003, '许晴', '女', '2002-08-11', '电子系');

创建课程表： CREATE TABLE course ( cno VARCHAR(10) PRIMARY KEY, cname VARCHAR(50), credit INT );

插入课程数据： INSERT INTO course (cno, cname, credit) VALUES ('001', 'MySQL数据库', 4), ('002', '线性代数', 3), ('003', '专业英语', 2);

创建成绩表： CREATE TABLE score ( sno INT, cno VARCHAR(10), grade INT, PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno) );

插入成绩数据： INSERT INTO score (sno, cno, grade) VALUES (1001, '001', 90), (1002, '001', 85), (1002, '002', 76), (1003, '003', 70);

2. (1) 查询 MySQL数据库成绩为良好（80~89分）的学生。 SELECT s.sno, s.sname FROM student s INNER JOIN score sc ON s.sno = sc.sno WHERE sc.grade BETWEEN 80 AND 89;

(2) 把 course 表中课程号为 002 的课程的学分修改为 4。 UPDATE course SET credit = 4 WHERE cno = '002';

(3) 在 student 表中查询 2002 年出生的学生的所有信息，并按学号降序排列。 SELECT * FROM student WHERE YEAR(birthday) = 2002 ORDER BY sno DESC;

(4) 查询年龄最大的 2 名学生的学号、姓名和出生日期。 SELECT sno, sname, birthday FROM student ORDER BY birthday DESC LIMIT 2;

(5) 查询选修了“专业英语”课程的学生的学号、姓名及成绩。 SELECT s.sno, s.sname, sc.grade FROM student s INNER JOIN score sc ON s.sno = sc.sno INNER JOIN course c ON sc.cno = c.cno WHERE c.cname = '专业英语';

(6) 统计每个学生的平均分。 SELECT s.sno, s.sname, AVG(sc.grade) AS average_grade FROM student s INNER JOIN score sc ON s.sno = sc.sno GROUP BY s.sno, s.sname;

3. 在score表后增加一列成绩等级grade，将90及以上列为“优秀”，80~89列为良好，70~79列为中等，60~69为及格，否则为不及格。 ALTER TABLE score ADD COLUMN grade_level VARCHAR(10); UPDATE score SET grade_level = '优秀' WHERE grade >= 90; UPDATE score SET grade_level = '良好' WHERE grade BETWEEN 80 AND 89; UPDATE score SET grade_level = '中等' WHERE grade BETWEEN 70 AND 79; UPDATE score SET grade_level = '及格' WHERE grade BETWEEN 60 AND 69; UPDATE score SET grade_level = '不及格' WHERE grade < 60