SQL数据库 【嵌套查询】

1
2
3
4
select 出版社名称
from 出版社表
where 出版社ID =(select 出版社 from 图书信息表
				where 图书名称='C#程序设计案例教程')

1
2
3
4
5
select *
from 图书信息表
where 价格=(select MIN(价格)from 图书信息表)
and 出版日期=(select MAX(出版日期)from 图书信息表
where 价格=(select MIN(价格)from 图书信息表)) 

1
2
3
select *
from 借书证表
where 借书证编号 in (select 借书证编号 from 图书借阅表)

1
2
3
4
5
6
select *
from 图书信息表
where 出版社 =(select 出版社ID
from 出版社表 where 出版社名称='高等教育出版社')
and ISBN编号 in (select ISBN编号 from 藏书信息表
where 图书编号 in (select 图书编号 from 图书借阅表))

1
2
3
4
5
select degree
from sc
where sno =(select sno
				from student
				where sname='李勇')

1
2
3
4
5
select *
from student
where sbirthday <(select MIN (sbirthday)
				from student
				where sdept='信息系')

1
2
3
select sno 学号, B.degree 成绩
from course A, sc B
where A.cno=B.cno and A.cname like '数据库'and degree between 80 and 90

1
2
3
4
select sno,sname
from student
where sno not in(select sno
			from sc)

1
2
3
select AVG (2022-year(sbirthday))平均年龄
from student,sc
where student.sno=sc.sno and cno='C04' 

1
2
3
4
select  cno 课程号, cname 课程名
from course
where cno in(select cno
			from sc)

1
2
3
4
5
6
7
select sname
from student
where sdept=(
		select  sdept
		from student
		where sname='李勇'
	) and sname<>'李勇'

1
2
3
4
5
6
7
select sno 学号,sname 学生姓名, sdept 系别
into student22
from student
where (sbirthday>(select sbirthday
					from student 
					where sname='刘晨'))
					

1
2
3
4
5
6
select sname,sdept
from student
where (sbirthday <all(
		select sbirthday 
		from student
		where ssex='女'))and ssex='男'

1
2
3
4
5
select degree
from sc
where degree>=(select AVG(degree)
				from sc
				where sc.cno=sc.cno)