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 )
上一篇: 你一晚最多几次?
下一篇: 三国的笑话,第7个需要智商!