sql练习题+答案

一一 新建以下几个表新建以下几个表 student student 学生表学生表 snosnosnamesnamesexsexdeptdeptbirthbirthageage 其中约束如下 其中约束如下 1 1 学号不能存在相同的学号不能存在相同的 2 2 名字为非空名字为非空 3 3 性别的值只能是性别的值只能是 男男 或或 女女 4 4 系包括这几个 信息系 计算机科学系 数学系 管理系 中文系包括这几个 信息系 计算机科学系 数学系 管理系 中文 系 外语系 法学系系 外语系 法学系 5 5 出生日期为日期格式出生日期为日期格式 6 6 年龄为数值型 且在年龄为数值型 且在 0 1000 100 之间之间 create table student sno smallint constraint a primary key 设置学生学号为student的主键 sname varchar 10 not null sex varchar 2 constraint b check sex in 男 女 检查约束 性别的值只能是 男 或 女 dept varchar 20 constraint c check dept in 信息系 计算机科学系 数学系 管理系 中 文系 外语系 法学系 检查约束 系包括这 几个 信息系 计算机科学系 数学系 管理系 中文系 外语系 法学系 birth datetime age smallint constraint d check age between 0 and 100 检查约束 年龄为数值型 且在 100 之间 cs cs 成绩表成绩表 snosnocnocnocjcj 其中约束如下 其中约束如下 1 1 snosno 和和 cnocno 分别参照分别参照 studentstudent 和和 coursecourse 表中的表中的 sno cnosno cno 的字段的字段 2 2 cj cj 成绩成绩 只能在只能在 0 1000 100 之间 可以不输入值之间 可以不输入值 create table cs sno smallint not null references student sno 定义成外键 cno smallint not null references course cno 定义成外键 cj smallint constraint e check cj between 0 and 100 检查约束 cj 成绩 只能在 100之间 可以不输入值 constraint f primary key sno cno 定 义学生学号和课程号为sc表的主键 coursecourse 课程表 课程表 cnocnocnamecname 其约束如下 其约束如下 1 1 课程号 课程号 cnocno 不能有重复的 不能有重复的 2 2 课程名 课程名 cnamecname 非空 非空 create table course cno smallint not null constraint g primary key 设置课程号为course的主键 cname varchar 20 not null 三 针对学生课程数据库查询 三 针对学生课程数据库查询 1 1 查询全体学生的学号与姓名 查询全体学生的学号与姓名 Select sno sname from student 2 2 查询全体学生的姓名 学号 所在系 并用别名显示出结果 查询全体学生的姓名 学号 所在系 并用别名显示出结果 Select sname as 姓名 sno as 学号 dept as 所在地 from student 3 3 查询全体学生的详细记录 查询全体学生的详细记录 select from student 4 4 查全体学生的姓名及其出生年份 查全体学生的姓名及其出生年份 select sname birth from student 5 5 查询学校中有哪些系 查询学校中有哪些系 select distinct dept from student 6 6 查询选修了课程的学生学号 查询选修了课程的学生学号 select sno from cs where cno is not null 7 7 查询所有年龄在查询所有年龄在2020岁以下的学生姓名及其年龄 岁以下的学生姓名及其年龄 select sname age from student where age 20 8 8 查询年龄在查询年龄在20 2320 23岁 包括岁 包括2020岁和岁和2323岁 之间的学生的姓名 系别和年岁 之间的学生的姓名 系别和年 龄 龄 select sname dept age from student where age between 20 and 23 9 9 查询年龄不在查询年龄不在20 2320 23岁之间的学生姓名 系别和年龄 岁之间的学生姓名 系别和年龄 select sname dept age from student where age23 10 10 查询信息系 数学系和计算机科学系生的姓名和性别 查询信息系 数学系和计算机科学系生的姓名和性别 select sname sex from student where dept 信息系 or dept 数学系 or dept 计算机科学系 11 11 查询既不是信息系 数学系 也不是计算机科学系的学生的姓名和性别 查询既不是信息系 数学系 也不是计算机科学系的学生的姓名和性别 select sname sex from student where dept 信息系 and dept 数学系 and dept 计算机科学系 12 12 查询所有姓刘学生的姓名 学号和性别 查询所有姓刘学生的姓名 学号和性别 select sname sno sex from student where sname like 刘 13 13 查询学号为查询学号为20090112009011的学生的详细情况 具体的学号值根据表中数据的学生的详细情况 具体的学号值根据表中数据 确定 确定 select from student where sno 5 14 14 查询姓查询姓 欧阳欧阳 且全名为三个汉字的学生姓名且全名为三个汉字的学生姓名 select sname from student where sname like 欧阳 15 15 查询名字中第查询名字中第2 2个字为个字为 晨晨 字的学生的姓名和学号字的学生的姓名和学号 select sname sno from student where sname like 晨 16 16 查询所有不姓刘的学生姓名 查询所有不姓刘的学生姓名 select sname sno from student where sname not like 刘 17 17 查询查询sqlsql课程的课程号和学分 课程的课程号和学分 select cno from course where cname sql 18 18 查询以查询以 DB DB 开头 且倒数第开头 且倒数第3 3个字符为个字符为 i i的课程的详细情况 的课程的详细情况 select from course where cname like DB i 19 19 查询缺少成绩的学生的学号和相应的课程号 查询缺少成绩的学生的学号和相应的课程号 select sno cno from cs where cj is null 20 20 查所有有成绩的学生学号和课程号 查所有有成绩的学生学号和课程号 select sno cno from cs where cj is not null 21 21 查询计算机系年龄在查询计算机系年龄在2020岁以下的学生姓名 岁以下的学生姓名 select sname from student where age 3 32 32 查询有查询有3 3门以上课程是门以上课程是9090分以上的学生的学号及分以上的学生的学号及 9090分以上的分以上的 课程数 课程数 select sno count cno as 课程数 from cs where cj 90 group by sno having count cno 3 33 33 查询学生查询学生20060112006011选修课程的总学分 选修课程的总学分 select sum course from course cs where o cs sno and cs sno 2006011 34 34 查询每个学生选修课程的总学分 查询每个学生选修课程的总学分 select sno sum cj from cs course where o o group by sno union select sno 0 from student where sno not in select sno from cs 35 35 查询每个学生及其选修课程的情况 查询每个学生及其选修课程的情况 select cs sno course from cs course where o o 36 36 查询选修查询选修2 2号课程且成绩在号课程且成绩在9090分以上的所有学生的学号 姓名分以上的所有学生的学号 姓名 select sno sname from student where sno select sno from cs where cno 2 and cj 90 37 37 查询每个学生的学号 姓名 选修的课程名及成绩 查询每个学生的学号 姓名 选修的课程名及成绩 select student sno sname course course cs cj from student course cs where student sno cs sno and o o 38 38 查询与查询与 刘晨刘晨 在同一个系学习的学生 分别用嵌套查询和连接查询 在同一个系学习的学生 分别用嵌套查询和连接查询 嵌套查询 select from student where dept in select dept from student where sname 刘晨 连接查询 select stu1 from student as stu1 student as stu2 where stu1 dept stu2 dept and stu2 sname 刘晨 exists查询 select from student s1 where exists select from student s2 where s1 dept s2 dept and s2 sname 刘晨 39 39 查询选修了课程名为查询选修了课程名为 信息系统信息系统 的学生学号和姓名的学生学号和姓名 select sno sname from student where sno in select sno from cs where cno in select cno from course where cname 信息系统 40 40 查询其他系中比信息系任意一个查询其他系中比信息系任意一个 其中某一个其中某一个 学生年龄小的学生姓名和学生年龄小的学生姓名和 年龄年龄 select sname age from student where age any select age from student where dept 信息系 41 41 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄 分别用查询其他系中比信息系所有学生年龄都小的学生姓名及年龄 分别用 ALLALL谓词和集函数谓词和集函数 用ALL select sname age from student where age all select age from student where dept 信息 系 聚合函数 select sname age from student where age select min age from student where dept 信息系 42 42 查询所有选修了查询所有选修了1 1号课程的学生姓名 分别用嵌套查询和连查询 号课程的学生姓名 分别用嵌套查询和连查询 嵌套查询 select sname from student where sno in select sno from cs where cno 1 连接查询 select sname from student cs where student sno cs sno and o 1 43 43 查询没有选修查询没有选修1 1号课程的学生姓名 号课程的学生姓名 select sname from student where sno in select sno from cs where cno 1 44 44 查询选修了全部课程的学生姓名 查询选修了全部课程的学生姓名 select sname from student where not exists select from course where not exists select from cs where cs sno student sno and o o 45 45 查询至少选修了学生查询至少选修了学生9500295002选修的全部课程的学生号码 选修的全部课程的学生号码 select distinct sno from sc scx where not exists select from cs scy where scy sno 95002 and not exists select from sc scz where scz sno scx sno and o o 46 46 查询计算机科学系的学生及年龄不大于查询计算机科学系的学生及年龄不大于1919岁的学生的信息 岁的学生的信息 select from student where dept 计算机科学 系 or age 19 47 47 查询选修了课程查询选修了课程1 1或者选修了课程或者选修了课程2 2的学生的信息 的学生的信息 select student from student cs where student sno cs sno and o 1 or o 2 48 48 查询计算机科学系中年龄不大于查询计算机科学系中年龄不大于1919岁的学生的信息 岁的学生的信息 select from student where age19 51 51 通过查询求学号为通过查询求学号为1 1学生的总分和平均分 学生的总分和平均分 select sum cj as 总分 avg cj 平均分 from cs where sno 1 52 52 求出每个系的学生数量求出每个系的学生数量 select dept count sno as 学生个数 from student group by dept 53 53 查询平均成绩