admin管理员组文章数量:1597898
2024年4月26日发(作者:)
11、针对以上四个表,用SQL语言完成以下各项操作:
① 给学生表增加一属性Nation(民族),数据类型为Varchar(20);
Alter table student
add Nation Varchar(20);
② 删除学生表中新增的属性Nation;
Alter table student
drop column Nation;
③ 向成绩表中插入记录(”2001110”,”3”,80);
insert into Grade
values('2001103','3',80);
④ 修改学号为”2001103”的学生的成绩为70分;
update Grade
set Gmark=70
where Sno='2001103';
⑤ 删除学号为”2001110”的学生的成绩记录;
delete Grade
where Sno='2001110';
⑥ 为学生表创建一个名为,以班级号的升序排序;
create index IX_Class on student(Clno ASC);
⑦删除IX_Class索引
Drop index IX_Class
12、针对以上四个表,用SQL语言完成以下各项查询:
① 找出所有被学生选修了的课程号;
select distinct cno
from grade;
② 找出01311班女学生的个人信息;
select *
from student
where clno=01311 and ssex='女';
③ 找出01311班、01312班的学生姓名、性别、出生年份;
Select sname,ssex, year(getdata())-sage as ‘出生年份’
from student
where clno in('01311','01312');
④ 找出所有姓李的学生的个人信息;
Select *
from student
where sname like ’李%’;
⑤ 找出学生李勇所在班级的学生人数;
Select count(*)
from student
where clno in (select clno
from student
where sname= '李勇');
⑥ 找出课程名为操作系统的平均成绩、最高分、最低分;
Select avg(gmark),max(gmark),min(gmark)
from grade
where cno =(select cno
from course
where cname='操作系统');
⑦ 找出选修了课程的学生人数;
Select count(distinct sno)
from grade;
⑧ 找出选修了课程操作系统的学生人数;
Select count(sno)
from grade
where cno =(select cno
from course
where cname='操作系统');
⑨ 找出2000级计算机软件班的成绩为空的学生姓名。
Select sname
from student
where clno in (select clno
from class
where inyear=’2000’ and speciality=’计算机软件’
from grade
) and sno in (select sno
where gmark is null);
13、针对以上四个表,用SELECT的嵌套查询完成以下各项查询:
① 找出与李勇在同一个班级的学生信息;
Select *
from student
where clno = (select clno
from student
where sname=’李勇’);
② 找出所有与学生李勇有相同选修课程的学生信息;
Select *
from student
where sno in (select distinct sno
from grade
where cno in ( select cno
from grade
where sno=(select sno
from student
where sname='李勇')))
③ 找出年龄介于学生李勇和25岁之间的学生信息;
Select *
from student
where sage between (select sage
from student
where sname=’李勇’) and 25
④ 找出选修了课程操作系统的学生学号和姓名;
Select sno,sname
from student
where sno in (select sno
from grade
where cno = (select cno
from course
where cname=’操作系统’ ))
⑤ 找出所有没有选修1号课程的学生姓名;
Select sname
from student
where not exists (select *
from grade
where sno= and cno='1')
OR:
Select sname
from student
Where sno not in (Select sno
from grade
Where cno=’1’);
⑥ 找出选修了全部课程的学生姓名。
(提示:可找出这样的学生,没有一门课程是他不选修的。)
Select sname
from student
where not exists (select * from course
where not exists (select * from grade
where sno=
and cno=))
版权声明:本文标题:(完整版)第三章SQL练习题参考答案 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://www.elefans.com/dianzi/1714095567a387176.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论