SQL的子查询

SQL的子查询

(1)查询全体学生的学号和姓名
select sno,sname
from student
(2)查询全体学生的详细记录
select *
from student
(3)查询所有选修过课程的学生号
 select distinct sno
from sc
(4)查询考试有不及格的学生学号
 select distinct sno
from sc where grade<60
(5)查询不是信息系的(is),计算机系(cs)的学生性别，年龄，系别
 select ssex ,sage,sdept
from student where sdept not in(‘is‘,‘cs‘)
(6)查询选修了4号课程的学生学号和成绩，结果按成绩降序排列
 select sno,grade
from sc where cno=‘004‘ order by grade desc
(7)查询每个课程号和相应的选课人数
 select cno,count(sno)
from sc group by cno
(8)查询计算机系(cs)的学生姓名，年龄，系别
 select sname,sage,sdept
from student where sdept=‘cs‘
(9)查询年龄18~20岁的学生学号，姓名，系别，年龄。
select sno,sname,sdept,sage
from student where sage between 18 and 20
(10)查询姓刘的学生情况
select * from student
where sname like ‘刘*‘
(11)查询即选修4号课程又选修2好课程的学生学号
select sno
from sc where sno in(
select sno from sc where cno=‘001‘and sno in(
select sno from sc where cno=‘002‘))
(12)查询学生姓名和出生年份(今年2008)
select sname,2008-sage from student
(13)查询没有成绩的学生学号和课程
 select sno,cno
from sc where grade is null
(14)查询总成绩大于200分的学生学号
select sno
from sc where grade>200
(15)查询每门课程不及格学生人数
select sno,count(sno) from sc
where grade<60 group by sno
(16)查询不及格超过3们的学生学号
select sno,count(sno) from sc
where grade<60 group by sno having count(cno)>3
(17)查询年龄为10~19岁的学生人数
select *from student where sage between 10 and 19
(18)查询全体学生情况，按所在系升序排列，同一个系的学生按年龄降序排列
 select * from student order by sdept asc ,sage desc
(19)查询选了1号课程的学生平均成绩
  select avg(grade) from sc where cno=‘001‘
(20)查询选了3号课程的学生的最高分
  select max(grade) from sc where cno=‘003‘
(21)查询每个学生的总成绩
  select sno  ,sum(grade) from sc group by sno

SQL的子查询,古老的榕树,5-wow.com