数据库上机作业2

--1. 向各个数据表中插入如下记录：
CREATE DATABASE db_students
ON PRIMARY(
NAME = 'stu',
FILENAME = 'd:\stu_data.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 10%)
LOG ON(
NAME = 'stu_log',
FILENAME = 'd:\stu_log.ldf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB)


USE db_students
GO


CREATE TABLE T_Student(
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20),
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);


CREATE TABLE T_Course(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40) NOT NULL,
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY(Cpno)REFERENCES T_Course(Cno)
);


CREATE TABLE T_SC(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Sno)REFERENCES T_Student(Sno),
FOREIGN KEY(Cno)REFERENCES T_Course(Cno)
);


INSERT T_Student
select'200515001', '赵菁菁','女',23,'CS' union
select'200515002', '李勇',  '男',20,'CS' union
select'200515003', '张力',  '男',19,'CS' union
select'200515004', '张衡',  '男',18,'IS' union
select'200515005', '张向东','男',20,'IS' union
select'200515006', '张向丽','女',20,'IS' union
select'200515007', '王芳',  '女',20,'CS' union
select'200515008', '王民生','男',25,'MA' union
select'200515009', '王小民','女',18,'MA' union
select'200515010', '李晨',  '女',22,'MA' union
select'200515011', '张毅',  '男',20,'WM' union
select'200515012', '杨磊',  '女',20,'EN' union
select'200515013', '李晨',  '女',19,'MA' union
select'200515014', '张丰毅','男',22,'CS' union
select'200515015', '李蕾',  '女',21,'EN' union
select'200515016', '刘社',  '男',21,'CM' union
select'200515017', '刘星耀','男',18,'CM' union
select'200515018', '李贵',  '男',19,'EN' union
select'200515019', '林自许','男',20,'WM' union
select'200515020', '马翔',  '男',21, NULL  union
select'200515021', '刘峰',  '男',25,'CS' union
select'200515022', '牛站强','男',22 ,NULL union
select'200515023', '李婷婷','女',18 ,NULL union
select'200515024', '严丽',  '女',20, NULL union
select'200515025', '朱小鸥','女',30, 'WM';




INSERT T_Course
select '2', '数学' ,Null ,   2union
select'6',  '数据处理'  ,Null,  2 union
select'4', '操作系统' ,'6', 3 union
select'7', 'PASCAL语言','6', 4 union
select'5', '数据结构' ,'7', 4 union
select'1', '数据库' ,'5', 4 union
select'3', '信息系统' ,'1', 4 union
select'8', '大学英语' ,Null, 4 union
select'9', '计算机网络',Null, 4 union
select'10', '人工智能' ,Null, 2;


INSERT T_SC
select'200515001', '1', 75   union
select'200515002', '1', 85   union
select'200515002', '3', 53   union
select'200515003', '1', 86   union
select'200515004', '1', 74   union
select'200515005', '1', 58   union
select'200515006', '1', 84   union
select'200515004', '2', 46   union
select'200515005', '2', 89   union
select'200515006', '2', 65   union
select'200515008', '2', 72   union
select'200515009', '2', 76   union
select'200515010', '2', 96   union
select'200515010', '8', 86   union
select'200515011', '8', 62   union
select'200515015', '8', 0    union
select'200515018', '8', 58   union
select'200515001', '4', 62   union
select'200515002', '4', 85   union
select'200515021', '9', 54   union
select'200515001', '5', 58   union
select'200515021', '6', 58   union
select'200515001', '7', 70   union
select'200515005', '10', 65   union
select'200515016', '8' ,   Null union
select'200515017', '8' ,   Null;


--2. 修改CS系姓名为“李勇”的学生姓名为“李咏”；


UPDATE T_Student
SET Sname = '李咏'
WHERE Sname = '李勇'AND Sdept = 'CS'


--3. 修改课程“数据处理”的学分为3学分；


UPDATE T_Course
SET Ccredit = 3
WHERE Cname = '数据处理'


--4. 将选修课程“1”的同学成绩加5分；


UPDATE T_SC
SET Grade = Grade + 5
WHERE Cno = '1'


--5. 将选修课程“大学英语”的同学成绩加5分；


UPDATE T_SC
SET Grade = Grade + 5
WHERE Cno IN (SELECT Cno FROM T_Course WHERE Cname = '大学英语')


--6. 将学号为“200515010”的学生信息重新设置为“王丹丹、女、20、MA”；


UPDATE T_Student
SET Sname = '王丹丹',Ssex = '女',Sage = 20,Sdept = 'MA'
WHERE Sno = '200515010'


--7. 删除数据表student中无专业的学生记录；


DELETE FROM T_Student
WHERE Sdept = NULL


--8. 删除数据表student中计算机系年龄大于25的男同学的记录；


DELETE FROM T_Student
WHERE Sage>25 AND Sdept = 'CS' AND Ssex = '男'


--9. 删除数据表course中学分低于1学分的课程信息；


DELETE FROM T_Course
WHERE Ccredit<1