sqlserver的增删改查小测试及答案(这对于初学者来说是一件难得的宝物)

create table student(
    sid varchar(8) primary key,
    sname varchar(16) not null,
    sex varchar(2) check (sex='男' or sex = '女')default '女',
    age int check(age > 0 and age < 120),
    tel varchar(16) unique
);

create table course(
    cid varchar(8) primary key,
    cname varchar(8) not null
);

create table sc(
    sid varchar(8) foreign key references student(sid),
    cid varchar(8) foreign key references course(cid),
    score int
);

insert into student
values('s001','丰登儿','男',18,'13527542451');
insert into student
values('s002','班克尔','女',17,'13884233134');
insert into student
values('s003','车笔刀','女',13,'15086623248');
insert into student
values('s004','趴耳朵','男',19,'15323535256');
insert into student
values('s005','直角','女',23,'15653579258');
insert into student
values('s006','扳手','女',19,'13663279788');
insert into student
values('s007','俄石板','女',21,'13656529396');

insert into course
values('c001','语文');
insert into course
values('c002','数学');
insert into course
values('c003','外语');
insert into course
values('c004','物理');
insert into course
values('c005','化学');


insert into sc
values('s001','c001',70);
insert into sc
values('s001','c002',78);
insert into sc
values('s001','c003',82);
insert into sc
values('s001','c004',63);
insert into sc
values('s001','c005',92);
insert into sc
values('s002','c001',52);
insert into sc
values('s002','c002',67);
insert into sc(sid,cid)
values('s002','c003');
insert into sc
values('s002','c004',82);
insert into sc
values('s002','c005',88);
insert into sc
values('s003','c001',52);
insert into sc(sid,cid)
values('s003','c002');
insert into sc
values('s003','c003',72);
insert into sc(sid,cid)
values('s003','c004');
insert into sc
values('s003','c005',88);
insert into sc
values('s004','c001',76);
insert into sc
values('s004','c003',89);


--二、执行一下操作
--1. 修改sc表中s001号学生的c002号课程的成绩,改为82
--(4分)
select * from sc;
update sc
set score = 82
where sid = 's001' and cid='c002'

--2. 删除sc表中学号为s004的学生成绩(4分)
delete from sc
where sid='s004'


--3. 查询年龄在18~20岁的学生信息(4分)
select *
from student
where age between 18 and 20

--4.  查询姓名以儿结尾的学生信息(4分)
select * 
from student
where sname like '%儿'

--5. 查询电话号码 倒数第3位是2的学生信息(4分)
select *
from student
where tel like '%2__'


--6. 查询男女生各多少人(4分)
select sex,COUNT(*) 人数
from student
--where sex = '女'
group by sex


--7. 查询每门课程的最高分、最低分、总分和平均分(4分)
select cname 课程名, MAX(score) 最高分, 
MIN(score) 最低分, SUM(score) 总分, AVG(score) 平均分
from student,sc,course
where student.sid = sc.sid and course.cid = sc.cid
group by cname



--8. 查询每个人的总分和平均分(4分)
select student.sname 姓名, SUM(score) 总分, AVG(score) 平均分
from student,sc
where student.sid = sc.sid
group by student.sname


--9. 查询比耙耳朵 大的学生信息(4分)
select * 
from student
where age > (
    select age 
    from student
    where sname = '趴耳朵'
);


--10. 查询每个学生的学号、姓名、性别、科目和成绩(4分)
select student.sid 学号,student.sname 姓名,
sex 性别, course.cname 课程名, sc.score 分数
from student,sc,course
where student.sid = sc.sid and course.cid = sc.cid
group by student.sid,student.sname,sex,course.cname,sc.score

--or
select student.sid,sname,sex,cname,score
from student,sc,course
where student.sid=sc.sid
and course.cid=sc.cid
--11. 查询没及格的学生的学号、姓名、性别、科目和成绩(5分)
select student.sid 学号,student.sname 姓名,
sex 性别, course.cname 课程名, sc.score 分数
from student,sc,course
where student.sid = sc.sid and course.cid = sc.cid and sc.score<60



--12. 查询学生表中第3~6个人的信息(分页查询5分)
select top 3 *
from student
where sid not in (
    select top 3 sid from student
)

--13. 查询没有参加考试的学生的学生信息(5分)
select * from student
where student.sid not in (
    select sc.sid
    from sc
)