欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-26 11:12:15
create table student( sid varchar(8) primary key, sname varchar(16) not null,...
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
)