MySql实验嵌套查询_数据库实验：SQL嵌套查询
自測(cè)題：
1、查詢哪些課程沒(méi)有人選修列出課程號(hào)和課程名；
[code]select cno,cname
from course
where cno not in(
select distinct cno
from sc)[/code]
2、用子查詢實(shí)現(xiàn)如下查詢：
(1)查詢選修了1號(hào)課程的學(xué)生姓名和所在系；
[code]select sname,sno
from student
where sno in(
select sno
from sc
where cno=1)[/code]
(2)查詢“數(shù)據(jù)庫(kù)”成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名；
[code]Select sno,sname
From student
Where sno in(
select sno
from course,sc
where course.cno=sc.cno and course.cname='數(shù)據(jù)庫(kù)' and grade>=80)[/code](3)查詢計(jì)算機(jī)系最高成績(jī)。
[code]select top 1 grade
from student,sc
where student.sno=sc.sno and sdept='CS'
order by grade desc[/code]
3、查詢同時(shí)選修了1號(hào)和2號(hào)課程的學(xué)生學(xué)號(hào)
[code]select sno
from sc
where cno=1 and sno in(
select sno
from sc
where cno=2)[/code]
4、查詢選修了“離散數(shù)學(xué)”的學(xué)生姓名(連接查詢)
[code]select sname
from student
where sno in(
select sno
from course,sc
where course.cno=sc.cno and course.cname='離散數(shù)學(xué)')[/code]
5、查詢選修課程名為“數(shù)據(jù)庫(kù)”的學(xué)生姓名(子查詢)
[code]select sname
from student
where sno in(
select sno
from course,sc
where course.cno=sc.cno and course.cname='數(shù)據(jù)庫(kù)')[/code]
6、查詢與張?zhí)旌蛷堢髟谕粋€(gè)系的學(xué)生
[code]select *
from student
where sdept in(
select sdept
from student
where sname='張?zhí)?#39; or sname='張琪')[/code]
查詢與張?zhí)旎驈堢鞑辉谕粋€(gè)系的學(xué)生
[code]select *
from student
where sdept not in(
select sdept
from student
where sname='張?zhí)?#39; or sname='張琪')[/code]
7、查詢比信息系所有學(xué)生年齡大的學(xué)生姓名
[code]select sname
from student s1
where s1.sage>all(
select sage
from student s2
where s2.sdept='CS')[/code]
8、查詢比張?zhí)炱骄煽?jī)高的學(xué)生姓名
[code]select sname
from student
where student.sno in(
select sno
from sc
group by sno
having avg(grade) >(
select avg(grade) as avg_grade2
from sc sc2,student
where student.sno=sc2.sno and sname='劉晨'
group by sc2.sno)
)[/code]9、查詢比學(xué)號(hào)為200215121學(xué)生年齡大的學(xué)生
[code]select *
from student s1
where s1.sage>(
select sage
from student s2
where s2.sno='200215121')[/code]
10、查詢各系總分最高的學(xué)生學(xué)號(hào)
[code]Select sdept,student.sno
from student,sc
where student.sno=sc.sno
group by sdept,student.sno
having sum(grade)>=all(
select sum(grade)
from student,sc
where student.sno=sc.sno and sdept=student.sdept
group by student.sno)[/code]
11、查詢選修了以6號(hào)課程為先行課的所有課程的學(xué)生學(xué)號(hào)。
[code]select distinct sno
from sc
where sc.cno in(
select cno
from course
where cpno=6)[/code]
總結(jié)
以上是生活随笔為你收集整理的MySql实验嵌套查询_数据库实验：SQL嵌套查询的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： 突然报错package name doe
- 下一篇： 怎样用CMD命令强行删除目录