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‘)

SQL语言实现复杂查询,古老的榕树,5-wow.com