SQL 数据库操作练习：学生、课程和成绩表

创建学生表并添加数据：

CREATE TABLE student (
  sno INT PRIMARY KEY,
  sname VARCHAR(20),
  sex VARCHAR(10),
  birthday DATE,
  depart VARCHAR(20)
);

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 CHAR(3) PRIMARY KEY,
  cname VARCHAR(20),
  credit INT
);

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

创建成绩表并添加数据：

CREATE TABLE score (
  sno INT,
  cno CHAR(3),
  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 student.sno, student.sname
FROM student
JOIN score ON student.sno = score.sno
WHERE score.grade >= 80 AND score.grade <= 89;

2.2 把course表中课程号为002的课程的学分修改为4：

UPDATE course
SET credit = 4
WHERE cno = '002';

2.3 在student表中查询2002年出生的学生的所有信息，并按学号降序排列：

SELECT *
FROM student
WHERE YEAR(birthday) = 2002
ORDER BY sno DESC;

2.4 查询年龄最大的2名学生的学号、姓名和出生日期：

SELECT sno, sname, birthday
FROM student
ORDER BY birthday DESC
LIMIT 2;

2.5 查询选修了“专业英语”课程的学生的学号、姓名及成绩：

SELECT student.sno, student.sname, score.grade
FROM student
JOIN score ON student.sno = score.sno
JOIN course ON score.cno = course.cno
WHERE course.cname = '专业英语';

2.6 统计每个学生的平均分：

SELECT sno, AVG(grade) AS avg_grade
FROM score
GROUP BY sno;

在score表后增加一列成绩等级grade，将90及以上列为“优秀”，80~89列为良好，70~79列为中等，60~69为及格，否则为不及格：

ALTER TABLE score
ADD COLUMN grade_level VARCHAR(10);

UPDATE score
SET grade_level = CASE
  WHEN grade >= 90 THEN '优秀'
  WHEN grade >= 80 THEN '良好'
  WHEN grade >= 70 THEN '中等'
  WHEN grade >= 60 THEN '及格'
  ELSE '不及格'
END;