数据更新和视图
1) 插入如下学生记录(学号:08030,姓名:李莉,年龄:18)
INSERT
INTO Student(Sno,Sname,Sage)
VALUES('08030','李莉',18);
2) 插入如下选课记录(08030,1)
INSERT
INTO SC(Sno,Cno)
VALUES('08030','1');
3) 计算机系学生年龄改成20
UPDATE Student
SET Sage=20
WHERE Sdept='CS';
4) 数学系所有学生成绩改成0
UPDATE SC
SET Grade=0
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept='MA');
5) 把低于总平均成绩的女同学成绩提高5分
UPDATE SC
SET Grade=Grade+5
FROM SC inner join Student on Student.Sno=SC.Sno
WHERE Ssex='女' AND SC.Grade <ALL(SELECT AVG(Grade) From SC);
6) 修改2号课程的成绩,若成绩小于75分提高5%,成绩大于75时提高4%(两个语句实现,注意顺序)
UPDATE SC
SET Grade=Grade*(1+0.05)
WHERE Cno='002' AND Grade>75;
UPDATE SC
SET Grade=Grade*(1+0.04)
WHERE Cno='002' AND Grade>75;
7) 删除08030学生信息
DELETE
FROM Student
WHERE Sno='08030';
8) 删除SC表中无成绩的记录
DELETE
FROM SC
WHERE Grade=0;
9) 删除张娜的选课记录
DELETE
FROM SC
WHERE Sno IN(
SELECT Sno
FROM Student
WHERE Sname='张娜');
10) 删除数学系所有学生选课记录
DELETE
FROM SC
WHERE Sno IN(
SELECT Sno
FROM Student
WHERE Sdept='MA');
11) 删除不及格的学生选课记录
DELETE
FROM SC
WHERE Grade<60;
12) 查询每一门课程成绩都大于等于80分的学生学号、姓名和性别,把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中
CREATE TABLE STU
(SNO CHAR(9),
SNAME CHAR(20),
SSEX CHAR(2));
INSERT
INTO STU(SNO,SNAME,SSEX)
SELECT Student.Sno,Sname,Ssex
FROM Student
WHERE Sno IN(SELECT Sno
FROM SC
GROUP BY Sno
HAVING MIN(SC.Grade)>80);
13) 把所有学生学号和课程号连接追加到新表中
CREATE TABLE STU1
(SNO CHAR(9),
CNO CHAR(4));
INSERT
INTO STU1(SNO,CNO)
SELECT Sno,Cno
FROM SC;
14) 所有学生年龄增1
UPDATE Student
SET Sage=Sage+1;
15) 统计3门以上课程不及格的学生把相应的学生姓名、系别追加到另外一个表中
CREATE TABLE STU2
(sname char(8),
sdept char(20));
insert into stu2(sname,sdept)
select sname,sdept
from student
where sno in
(
select distinct sno
from sc
where grade<60
group by sno
having count(grade)>=3
);
16) 创建视图v_stuinfo,视图查询所有学生选修课程的信息,包括学生姓名,课程名称,成绩等详细信息
CREATE VIEW v_stuinfo(Sno,Sname,Ssex,Sage,Sdept,Cno,Cname,Ccredit,Cpno,Grade)
AS
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,SC.Cno,Cname,Ccredit,Cpno,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;
17) 从上题视图中查询选修“数据库原理”的学生信息
SELECT *
FROM v_stuinfo
WHERE Cname='数据库';
18) 创建视图(用with check option),视图查询计算机系所有学生的信息;并尝试修改视图中系别信息,能否修改,为什么?
CREATE VIEW v_stuinfo_1
AS
SELECT *
FROM v_stuinfo
WHERE Sdept='CS'
with check option;
不能再修改,因为with check option 用于强制视图上执行的所有修改语句,必须符合由select语句where中的条件。