根据要求书写SQL语句

Question

1．建表、定义主关键字、唯一索引
2．统计选课人数和最高分
3．统计选课门数和考试总成绩，并按选课门数升序或降序排序
4．创建视图
5．删除记录
6．更新数据

Answer 1

-- 创建表，指定主关键字为id
CREATE TABLE scores (
  id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  score FLOAT,
  UNIQUE (student_id, course_id) -- 定义唯一索引
);
-- 统计选课人数和最高分
SELECT course_id, COUNT(DISTINCT student_id) AS student_count, MAX(score) AS max_score
FROM scores
GROUP BY course_id;
-- 统计选课门数和考试总成绩，并按选课门数升序排序
SELECT student_id, COUNT(DISTINCT course_id) AS course_count, SUM(score) AS total_score
FROM scores
GROUP BY student_id
ORDER BY course_count ASC; -- 升序

-- 统计选课门数和考试总成绩，并按选课门数降序排序
SELECT student_id, COUNT(DISTINCT course_id) AS course_count, SUM(score) AS total_score
FROM scores
GROUP BY student_id
ORDER BY course_count DESC; -- 降序
-- 创建视图，查询各门课程的平均成绩
CREATE VIEW course_avg_score AS
SELECT course_id, AVG(score) AS avg_score
FROM scores
GROUP BY course_id;
-- 删除选修课程号为 101 的学生选课记录
DELETE FROM scores WHERE course_id = 101;
-- 将学号为 1001 的学生在课程 102 中的成绩更新为 85
UPDATE scores SET score = 85 WHERE student_id = 1001 AND course_id = 102;