SQL SERVER实验指导（第三版）实验题答案——实验一、二、三

实验一

1. 用交互式方法创建数据库mybase，要求：
数据文件存储的文件夹是：D:\mytestbase
数据存放在两个文件组中：group1、group2
2. 修改数据库mybase属性，要求：
文件最大大小：100MB
文件增长比例：20%

create database mybase
on
(name=group1,filename='D:\mytestbase\group1.mdf',
maxsize=100MB,--2.1文件最大大小：100MB
filegrowth=20%)—2.2文件增长比例为20%
log on
(name=group2,filename='D:\mytestbase\group2.ldf')
Go

3.删除数据库mybase

drop database mybase

实验二

1.创建数据库jiaoxuedb

create database jiaoxuedb

2.在数据库jiaoxuedb中创建数据库表Student、Teacher、Course、SC、TC

--创建数据库表Student
use jiaoxuedb
create table Student(
Sno char(10) NOT NULL,
Sname char(10) NOT NULL,
Sex char(10),
Age tinyint,
Dept char(10),)
--向Student中插入数据
INSERT into  Student
VALUES('001101','宋大方','男','19','计算机'),
('002102','李王','男','20','信息'),
('991101','张彬','男','18','计算机'),
('991102','王蕾','女','19','计算机'),
('991103','张建国','男','18','计算机'),
('991104','李平方','男','18','计算机'),
('991210','陈东辉','男','19','计算机'),
('991202','葛鹏','男','21','计算机'),
('991203','潘桃芝','女','19','计算机'),
('991204','姚一峰','男','18','计算机'),
('001102','许辉','女','22','计算机'),
('001201','王一山','男','20','计算机'),
('001202','牛莉','女','19','计算机'),
('002101','李丽丽','女','19','信息');

select *from Student
--创建数据库表Teacher
use jiaoxuedb
create table Teacher(
TNO char(10) NOT NULL,
TN CHAR(10) NOT NULL,
SEX CHAR(10),
AGE TINYINT,
PROF CHAR(10),
SAL SMALLINT,
COMM SMALLINT,
DEPT CHAR(10)
)
--向数据库表Teacher中插入数据
INSERT INTO Teacher
(TNO,TN,SEX,AGE,PROF,SAL,COMM,DEPT)
VALUES
('000006','许红霞','女',39,'讲师',1100,1200,'计算机'),
('000007','许永军','男',57,'教授',2000,3000,'计算机'),
('000008','李桂春','女',65,'教授',2000,3000,'计算机'),
('000009','王一凡','女',43,'讲师',1200,1200,'计算机'),
('000010','田峰','男',33,'助教',500,800,'信息'),
('000001','李英','女',39,'副教授',1500,2000,'信息'),
('000002','张雪','女',51,'教授',1900,3000,'信息'),
('000003','张朋','男',30,'讲师',1000,1200,'计算机'),
('000004','王平','女',28,'讲师',850,1200,'信息'),
('000005','李力','男',47,'教授',1800,3000,'计算机');
select *from Teacher

--创建数据库表Course
create table Course(
Cno char(10) NOT NULL,
Cname char(10) NOT NULL,
Chour tinyint,
Credit char(10),)
--向Course中插入数据
INSERT into  Course
VALUES('01001','计算机基础','60','3'),
('01002','程序设计','80','5'),
('02003','数据结构','60','6'),
('02001','数据库','80','6'),
('02002','计算机网络','60','6'),
('01003','微机原理','60','8'),
('02004','操作系统','60','6'),
('03001','软件工程','60','3'),
('03002','大型数据库','48','2'),
('03003','图像处理','48','2');

select *from Course

--创建选课表
use jiaoxuedb
create table SC(
SNO char(10) NOT NULL,
CNO char(10) NOT NULL,
SCORE TINYINT
)
--向SC表中插入数据
INSERT INTO SC VALUES
(991101,01001,88),
(991102,01001,93),
(991103,01001,90),
(991101,01002,90),
(991102,01002,98),
(991103,01002,74),
(991104,01002,85),
(991101,01002,64),
(991104,02001,33),
(991104,01001,35),
(991101,01001,76);
select *from SC
--教师任课表
use jiaoxuedb
create table TC(
Tno char(10),
Cno char(10),
)
--向TC中插入数据
INSERT INTO TC VALUES
(000001,02001),
(000008,02002),
(000003,02001),
(000011,02003),
(000001,01001),
(000002,01002),
(000002,01003),
(000004,02002),
(000005,01001),
(000006,01002),
(000003,01003);
select *from TC

3.给学生表Student增加两个字段，一个长度为2000个字符的简历字段Resume，另一个是长度为20个字符的籍贯字段Native

--给Student增加两个字段
use jiaoxuedb
alter table Student
add Resume char(2000) NULL

use jiaoxuedb
alter table Student
add Native char(20) NULL

select *from Student

4.把学生表中的籍贯列改为40个字符

--把学生表中的籍贯列改为40个字符
use jiaoxuedb
alter table Student
alter column native char(40) NULL

select *from Student

5.删除学生表中的简历的字段

--删除学生表中的简历字段
use jiaoxuedb
alter table Student
drop column Resume

select *from Student

6.删除教师授课表TC

use jiaoxuedb
drop table TC

实验三

1.交互式录入表3-1中前5给记录数据至数据库jxsk的数据库表SC中

2.用INSERT INTO语句录入表3-1中后5个记录数据至数据库jxsk的数据库表SC中

--在jxsk中创建SC表
use jxsk
create table SC(
SNO char(10) NOT NULL,
CNO char(10) NOT NULL,
SCORE char(10),
)
--向SC表中插入数据
use jxsk
INSERT into  SC
VALUES('S1','C2','85'),
('S1','C5','100'),
('S2','C5','60'),
('S2','C6','80'),
('S2','C7',' '),
('S3','C2','70'),
('S3','C4','85'),
('S4','C2','85'),
('S4','C3','83'),
('S5','C2','89');

select *from SC

4.用SQL语句把计算机系学生的年龄增加1岁

update Student set Age=Age-1 where Dept='计算机'

5.把成绩不及格的学生的学号，课号，和成绩放入一个新表makeup_s中

use jxsk

select SNO,CNO,SCORE into makeup_s from SC
where SCORE<60

select *from makeup_s

6.删除表makeup_s中成绩在20分以下的学生记录

delete from sc where SCORE<20
select *from SC