2024.5.9

所学时间：3小时

代码行数：62

博客园数：1篇

所学知识：今天学习了 继续写了数据库和表的建立

-- 学生表 students
CREATE TABLE students (
sno CHAR(8) NOT NULL PRIMARY KEY,
sname VARCHAR(20),
sex CHAR(2),
sdept VARCHAR(20)
);

-- 课程表 course
CREATE TABLE course (
cno CHAR(6) NOT NULL PRIMARY KEY,
cname VARCHAR(20)
);

-- 选课表 sc
CREATE TABLE sc (
sno CHAR(8) NOT NULL,
cno CHAR(6) NOT NULL,
grade INT NOT NULL,
PRIMARY KEY (sno, cno),
FOREIGN KEY (sno) REFERENCES students(sno),
FOREIGN KEY (cno) REFERENCES course(cno)
);

DELETE FROM sc WHERE sno='20050101'; DELETE FROM sc WHERE sno='20050102'; INSERT INTO student VALUES('20050101','王飞','男','计算机系'); INSERT INTO student VALUES('20050102','李丽','女','信息系'); SELECT * FROM student; INSERT INTO course VALUES('01','数据结构'); INSERT INTO course VALUES('02','数据库原理'); SELECT * FROM course; INSERT INTO sc VALUES('20050101','01',70); INSERT INTO sc VALUES('20050102','02',90); SELECT * FROM sc;

INSERT INTO student VALUES('20050101','王飞','男','计算机系');
INSERT INTO student VALUES('20050102','李丽','女','信息系');
SELECT * FROM student;

INSERT INTO course VALUES('01','数据结构');
INSERT INTO course VALUES('02','数据库原理');
SELECT * FROM course;

为了计算每门课程的学生平均成绩并将结果存入数据库中，您可以使用以下 SQL 查询：

CREATE TABLE average_grade (
cno CHAR(6) NOT NULL PRIMARY KEY,
avg_grade DECIMAL(5,2)
);

INSERT INTO average_grade (cno, avg_grade)
SELECT cno, AVG(grade) AS avg_grade
FROM sc
GROUP BY cno;

SELECT * FROM average_grade;