SQL第八章用SQL操作数据上机题1-7

//修改记录 USE MySchool
--修改学号为S1201302004学生的住址为“山东省济南市文化路1号院”。
UPDATE Student SET  Address=山东省济南市文化路1号院 WHERE StudentNo=S1201302004

--修改学号为S1201302005学生的所属年级为2。
UPDATE Student SET  GradeId=2 WHERE StudentNo=S1201302005

--将2013年9月13日考试的“走进Java编程世界”课程分数低于60分的学员全部提高5分。
UPDATE Result SET  StudentResult=StudentResult+5 
WHERE ExamDate=2013-9-13 AND SubjectId=7 AND StudentResult<60

--修改“走进Java编程世界”课程的学时数为55。
UPDATE Subject SET  ClassHour=55 WHERE SubjectName=走进Java编程世界

--将学号为S1201302002的学生2013年10月18日的“HTML和CSS网页技术”课程的分数修改为55。
UPDATE Result SET  StudentResult=55
WHERE ExamDate=2013-10-18 AND SubjectId=8 AND StudentNo=S1201302002

--将电子邮件为空的学生电子邮件统一修改为“未知”。
UPDATE Student SET  Email=未知@ WHERE Email IS NULL

 //删除记录	 --由于入学限制，学校要求不允许1997年7月1日后出生的学生入学
DELETE FROM Student WHERE BornDate>=1997-7-1 
 
//增加科目记录
 
 INSERT INTO Subject(SubjectName,ClassHour,GradeId) VALUES(走进Java编程世界,40,1)
INSERT INTO Subject(SubjectName,ClassHour,GradeId) VALUES(HTML和CSS网页技术,60,1)
INSERT INTO Subject(SubjectName,ClassHour,GradeId) VALUES(C#语言和数据库技术,70,1) //增加成绩记录 INSERT INTO Result(StudentNo,SubjectId,StudentResult,ExamDate)
VALUES(S1201302001,1,80,2013-9-13)
INSERT INTO Result(StudentNo,SubjectId,StudentResult,ExamDate)
VALUES(S1201302002,1,45,2013-9-13)
INSERT INTO Result(StudentNo,SubjectId,StudentResult,ExamDate)
VALUES(S1201302001,2,90,2013-10-18)
INSERT INTO Result(StudentNo,SubjectId,StudentResult,ExamDate)
VALUES(S1201302002,2,60,2013-10-18)
 //创建通讯录
 
 INSERT INTO Address_IS  (SName,Phone,Address,Email) 
SELECT StudentName,Phone,Address,Email
FROM Student

SELECT StudentName,Phone,Address,Email
INTO Address_SI 
FROM Student 
//增加学生记录
 
 INSERT  INTO Student (StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate,Email)
 VALUES (S1201302001,zhangsan,张三, 男,1,01062768866,解放路, 1994-1-1,zhangsan@126.com)
INSERT  INTO Student (StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate)
 VALUES (S1201302002,lisi89,李四, 男,1,13812345678,长江路, 1992-2-1)
INSERT  INTO Student (StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate)
 VALUES (S1201302003,DEFAULT,王五, 男,1,13912345678,DEFAULT, 1992-5-3)
INSERT  INTO Student (StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate,Email)
 VALUES (S1201302004,wanglili,王丽丽, 女,1,13112345678,DEFAULT, 1991-6-2,wangll@sohu.com)
INSERT  INTO Student (StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate)
 VALUES (S1201302005,DEFAULT,张峰, 男,1,13212345678,DEFAULT, 1994-8-8)