SQL语言实现复杂查询

小灰灰 2022-08-26 12:13 274阅读 0赞

SQL语言实现复杂查询

(1)查询每个学生及其选课情况  
select student.\*,sc.\*  
from student,sc where student.sno=sc.sno  
(2)查询每门课的间接先修课。  
select first.cno,second.cpno  
from course first left join course second  
on first.cpno=second.cno  
(3)将student,sc进行右链接  
select student.\* ,sc.\*  
from student right join sc on student.sno=sc.sno  
(4)查询不及格学生的姓名和所在系  
select sname,sdept  
from student where sno in(  
select sno from sc group by sno having min(grade)<60)  
(5)查询所有成绩为优秀(大于90分的学生信息)的学生姓名  
select sname from student where sno in(  
select sno from sc group by sno having min(grade)>90)  
(6)查询即选修了2号课程又选修了3号课程的学生姓名，学号  
select sname,A.sno from student ,sc A ,sc B where A.cno='002' and B.cno='003' and A.sno=B.sno and A.sno=student.sno  
(7)查询和刘晨同一年龄的学生  
select \* from student where sage=(  
select sage from student where sname='刘晨')  
(8) 选修了课程名为“数据库”的学生姓名和年龄  
select sname ,sage from student where sno in(  
select sno from sc where cno in(  
select cno from course where cname='数据库'))  
(9)查询其他系比is系任意学生年龄小的学生名单  
select \* from student where sage<any(  
select sage from student where sdept='is') and sdept<>'is'  
(10)查询其他系比is系所有学生年龄都小的学生名单  
select \* from student where sage<all(  
select sage from student where sdept='is') and sdept<>'is'  
(11)查询选修了全部课程的学生姓名  
SELECT sname  
from student  
where sno in  
(  
select sno  
from sc  
group by sno  
having count(cno)=7  
)  
(12)查询计算机系学生及其性别是男的学生  
select \* from student where sdept='is'and ssex='男'  
(13)查询选修课程1的学生集合和选修2号课程学生集合的差集  
select sno from sc where cno='001'and sno not in(  
select sno from sc where cno='002')  
(14)查询李丽同学不学的课程号  
select distinct cno from sc where cno not in(  
select cno from student,sc where sname='李丽'and student.sno=sc.sno)  
(15)查询选修了3号课程的学生平均年龄  
select avg(sage) from student where sno in(  
select sno from sc where cno='003')  
(16)求每门课的平均成绩  
select cno,avg(grade) from sc group by cno  
(17)统计每门课程的学生选修人数(超过3人的统计)。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列。  
select distinct cno ,count(sno)  
from sc  
group by cno  
having count(sno)>3  
order by 2 desc,cno asc  
(18)查询学号比刘晨大的，而年龄比他小的学生姓名。  
select sname from student where sno>(  
select sno from student where sname='刘晨')and sage<(  
select sage from student where sname='刘晨')  
(19) 求年龄大于女同学平均年龄的男同学姓名和年龄  
select sname ,sage from student where sage>(  
select avg(sage) from student where ssex='女')and ssex='男'  
(20)求年龄大于所有女同学年龄的男同学姓名和年龄  
select sname ,sage from student where sage>(  
select max(sage) from student where ssex='女')and ssex='男'  
(21)查询至少选修了95002选修的全部课程的学生号码  
输入的数据中没有95002的，查询了08002的  
select sno from sc where cno in(  
select cno from sc where sno='08002')  
group by sno having count(cno)=(  
select count(cno) from sc where sno='08002')  
(22)查询95001和95002两个学生都选修的课程信息  
数据中没有95001和95002的查询了08001和08002  
select \* from course where cno in (  
select cno from sc where sno='08001')  
and cno in (  
select cno from sc where sno='08002')