oracle 数据库基本操作三 | 您所在的位置:网站首页 › 查询全体学生姓名和出生年份 › oracle 数据库基本操作三 |
例3-1: (选择表中的若干列) 求全体学生的学号、姓名、性别和年龄。 select sno,sname,ssex,sage from student;例3-2: (不选择重复行) 求选修了课程的学生学号。 select distinct sno from score;例3-3: (选择表中的所有列) 求全体学生的详细信息。 select * from student;例3-4: (使用表达式) 求全体学生的学号、姓名和出生年份。 select sno,sname,2017-sage as year_brithday from student;例3-5: (使用列的别名) 求学生的学号和出生年份,显示时使用别名“学号”和“出生年份”。 select sno as 学号,2017-sage as 出生年份 from student;例3-6: (比较大小条件) 求年龄大于19岁的学生的姓名和年龄。 select sname,sdept,sage from student where sage > 19;例3-7: (比较大小条件) 求计算机系或信息系年龄大于18岁的学生的姓名、系和年龄。 select sno,sage from student where sage > 18 and sdept = 'CS';例3-8: (确定范围条件) 求年龄在19岁与22岁(含20岁和22岁)之间的学生的学号和年龄。 select sno,sage from student where sage between 20 and 23;//或者 select sno,sage from student where sage > 19 and sage s2.sage and s2.sname = '李丽';例3-33:(外部连接查询) 求选修了课程002或003的学生的学号、课程号、课程名和成绩,要求必须将002和003课程的相关信息显示出来。 select sno,course.cno,cname,score from score,course where course.cno in ('002','003') and score.cno = course.cno;例3-34:(子查询) 求与 ‘黎明’ 年龄相同的学生的姓名和系。 select sname ,sdept from student where sage in (select sage from student where sname = '黎明');因为,有两位同学叫黎明,在子查询中返回多行,此时不能使用等号进行判断,而是要用in进行判断; 例3-35:(子查询) 求选修了课程名为 ’数据结构’ 的学生的学号和姓名。 select sno,sname from student where sno in (select sno from score where cno in (select cno from course where cname = '数据结构'));例3-36:(子查询ANY) 求比数学系中某一学生年龄大的学生的姓名和系。 select sname ,sdept from student where sage > any(select sage from student where sdept = 'MA');例3-37:(子查询ALL) 求比数学系中全体学生年龄大的学生的姓名和系。 select sname ,sdept from student where sage > all(select sage from student where sdept = 'MA');例3-38:(子查询EXISTS) 求选修了课程004的学生的姓名和系。 select sname, sdept from student where exists (select * from score where cno = '004' and sno = student.sno);例3-39:(返回多列的子查询) 求与 ‘黎明’ 同系且同龄的学生的姓名和系。 select sname, sdept, sage from student where (sdept,sage) in (select sdept ,sage from student where sname = '黎明' ) ;例3-40:(多个子查询) 求与 ‘‘黎明’ 同系,且年龄大于 ‘李丽’ 的学生的信息。 select * from student where sdept in (select sdept from student where sname = '黎明') and sage > all (select sage from student where sname = '李丽') and sname != '李丽';例3-41:(子查询中使用表连接) 求数学系中年龄相同的学生的姓名和年龄。 select sname, sage from student where sno in ( select s1.sno from student s1,student s2 where s1.sage = s2.sage and s1.sno != s2.sno and s1.sdept = s2.sdept and s1.sdept = 'MA');例3-42:(连接或嵌套查询) 检索至少选修王成刚老师所授课程中一门课程的女学生姓名。 select sname from student where sno in ( select sno from score where cno in ( select cno from teach where tname = '王成刚')) and ssex = '女';例3-43:(嵌套与分组查询) 检索选修某课程的学生人数多于3人的教师姓名。 select distinct tname ,cno from teach where cno in ( select cno from score group by cno having count(sno)>3);例3-44:(集合查询) 列出所有教师和同学的姓名和性别。 select sname ,ssex from student union select tname, tsex from teach例3-45:(相关子查询) 求未选修课程004的学生的姓名。 select sname from student where sno not in (select distinct sno from score where cno = '004');例3-46:(相关子查询) 求选修了全部课程的学生的姓名。 select sname from student where not exists ( select * from course where not exists( select * from score where sno = student.sno and cno = course.cno));例3-47:(相关子查询) 求至少选修了学生 ‘96002’ 所选修的全部课程的学生的学号。 select sno from score sc1 where not exists( select * from score sc2 where sc2.sno = '96002' and not exists( select * from score sc3 where sno = sc1.sno and cno = sc2.cno));例3-48:(相关子查询) 求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。 select sno, score.cno, score from score ,(select cno ,avg(score) as av from score group by cno)avgs where score > av and score.cno = avgs.cno ;例3-49:(相关子查询) 查询被一个以上的学生选修的课程号。 select cno from (select cno ,count(sno)as cou from score group by cno) count_c where cou > 1 order by cno asc;例3-50:(相关子查询) 查询所有未选课程的学生姓名和所在系。 select sname ,sdept from student where not exists ( select * from score where student.sno = score.sno);欢迎大家评论,共同学习,共同进步; |
CopyRight 2018-2019 实验室设备网 版权所有 |