SQL Server2012——SQL的常用数据更新操作

SQL的常用数据更新操作

插入如下学生记录（学号：95030，姓名：李莉，年龄：18）

INSERT INTO student (sno, sname, sage)
VALUES (95030, '李莉', 18)

插入如下选课记录（95030，1）

INSERT INTO sc (sno, cno)
VALUES (95030, 1)

计算机系学生年龄改成20

UPDATE student
SET sage = 20
WHERE sdept = 'CS'

把数学系所有学生成绩改成0

UPDATE sc
SET grade = 0
WHERE sno = (
		SELECT sno
		FROM student
		WHERE sdept = 'MA'
	)

把低于总平均成绩的女同学成绩提高5分

UPDATE sc
SET grade = grade + 5
WHERE grade < (
		SELECT avg(grade)
		FROM sc
			INNER JOIN student ON student.sno = sc.sno
		WHERE ssex = '女'
	)

删除95030学生信息 (注：如在SC表中存在相应选课记录，还需删除约束)

DELETE FROM student
WHERE sno = 95030

删除SC表中无成绩的记录

DELETE FROM sc
WHERE grade IS NULL

删除张娜的选课记录

DELETE FROM sc
WHERE sno = (
		SELECT sno
		FROM student
		WHERE sname = '张娜'
	)

删除不及格的学生选课记录

DELETE FROM sc
WHERE grade < 60

删除数学系所有学生选课记录

DELETE FROM sc
WHERE sno = (
		SELECT sno
		FROM student
		WHERE sdept = 'MA'
	)

删除所有未被选修的课程

DELETE FROM course
WHERE cno NOT IN (
		SELECT cno
		FROM sc
	)

查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中

CREATE TABLE STU (
	sno char(8) PRIMARY KEY,
	sname char(8) NOT NULL UNIQUE,
	ssex char(2) DEFAULT '男' CHECK (ssex = '男'
	OR ssex = '女')
)

INSERT INTO STU (sno, sname, ssex)
SELECT DISTINCT student.sno, sname, ssex
FROM student, sc
WHERE student.sno NOT IN (
		SELECT sno
		FROM sc
		WHERE grade < 80
	)
	AND student.sno = sc.sno

建立一个sdeptgrade 表，包含（sdept,avggrade）字段，对每一个系，求学生的成绩，并把结果存入sdeptgrade

CREATE TABLE sdeptgrade (
	sdept char(8) PRIMARY KEY,
	avggrade int
)

INSERT INTO sdeptgrade
SELECT student.sdept, AVG(sc.grade)
FROM student
	INNER JOIN sc ON student.sno = sc.sno
GROUP BY student.sdept