学生课程数据库综合操作（SQL）

1.学生，课程，选课关系表

2.SQL语句实现表

//数据库试题练习sct
CREATE TABLE Student//创建学生表
(
	Sno CHAR(8) PRIMARY KEY,
	Sname CHAR(12) UNIQUE NOT NULL,
	Ssex CHAR(2) CHECK(Ssex in('男', '女')) DEFAULT '男',//在男和女中选一个
	Sage INT CHECK(Sage >= 15 AND Sage <= 36) DEFAULT 22,
	Sdept CHAR(20) DEFAULT "CS"
);


//数据库试题sct
CREATE TABLE Course//创建课程表
(
	Cno CHAR(8) PRIMARY KEY,
	Cname CHAR(8) NOT NULL,
	Teacher CHAR(10) UNIQUE
);



//创建学生选课表
CREATE TABLE SC(
	Sno CHAR(8),
	Cno CHAR(8),
	PRIMARY KEY(Sno, Cno),//学生号与课程号一起作为主码
	Grade SMALLINT CHECK(Grade >= 0 AND Grade <= 100),
	FOREIGN KEY(Sno) REFERENCES Student(Sno) ON UPDATE CASCADE ON DELETE RESTRICT,//级联更新与删除
	FOREIGN KEY(Cno) REFERENCES Course(Cno) ON UPDATE CASCADE ON DELETE NO ACTION//级联更新，拒绝删除
);

3.插入学生，课程，选课信息

//插入学生表信息
INSERT INTO Student(Sno, Sname, Ssex, Sage, Sdept)
VALUES
("20241101", "李勇", "男", 20, "CS"),
("20241102", "刘晨", "女", 19, "CS"),
("20241103", "王敏", "女", 18, "MA"),
("20241104", "张立", "男", 19, "IS");




INSERT INTO Course(Cno, Cname, Teacher)//插入课程表的信息
VALUES
("1", "数据库", "张一"),//注意老师姓名唯一，不可重复
("2", "数学", "张九"),
("3", "信息系统", "张三"),
("4", "操作系统", "张八"),
("5", "数据结构", "张四"),
("6", "数据处理", "张二"),
("7", "PASCAL语言", "张六"),
("8","DB_MATH","李一");


//插入学生选课信息
INSERT INTO Sc(Sno, Cno, Grade)
VALUES
("20241101", "1", 92),//注意学生号相同不可选多门课程
("20241103", "2", 85),
("20241104", "3", 88),
("20241103", "4", 90),
("20241102", "3", 80);

4.检索课程名以"DB_"开头，且倒数第3个字符为 k的课程的详细情况

//课程名以"DB_"开头，且倒数第3个字符为 k的课程的详细情况
SELECT *
FROM Course
WHERE Cname LIKE "DB_%A__" ESCAPE "/";

5.删除选课成绩在90分以下的学生记录

DELETE FROM Student
WHERE SNO IN
(
	SELECT SNO
	FROM SC
	WHERE GRADE < 90
);

显示不可删除 

6.把“数学”课程成绩提高8％

UPDATE SC
SET Grade = Grade * 1.08
WHERE Cno IN
(
	SELECT Cno
	FROM Course
	WHERE Cname = "数学"
);

课程号2为数学，85*1.08=91.8~~92 

后续会补充，希望大家点赞关注我！