SQL的子查询

迷南。 2022-08-26 12:13 182阅读 0赞

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