SQL语言实现复杂查询
来源:互联网 发布:天翼wifi客户端 mac 编辑:程序博客网 时间:2024/06/01 08:49
(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')
- SQL语言实现复杂查询
- 数据库系统 ##SQL语言复杂查询
- Springboot集成jdbcTemplate,实现复杂sql查询
- sql 语句实现简单查询和复杂查询
- 第7讲-SQL语言复杂查询-(NOT) in语句
- 第7讲-SQL语言复杂查询--(NOT)Exists
- 第7讲-SQL语言复杂查询之视图
- SQL Server 复杂查询
- SQL复杂查询语句
- 其他复杂SQL查询
- Sql复杂查询
- sql复杂嵌套查询
- SQL复杂的查询
- SQL-(3) --复杂查询
- sql复杂查询
- sql复杂查询
- 复杂sql查询经验
- SQL复杂查询练习
- MAC OSX 独立安装gcc编译器
- 批量下载豆瓣音乐节目单
- libsvm 使用介绍
- Win7 用户文件夹转移方法
- 《黑马程序员》 for嵌套之九九乘法表的练习
- SQL语言实现复杂查询
- 常见排序算法分析
- Java使用foreach遍历集合元素
- 奇偶剪枝
- hdu 1312
- 删除字符串中的*
- 建站前知识之一
- 寻找最小(或最大)的k个数(剑指offer30)
- 【数组去重】Remove Duplicates from Sorted Array