--(1) 查询LIU老师所授课程的课程号和课程名。
select cno,cname
from c
where teacher='LIU'
--(2) 查询年龄大于23岁的男生的学号和姓名。
select sno,sname
from s
where sage>23 and ssex='M'
--(3) 查询学号为S3学生所学课程的课程名与任课教师名。
select cname,teacher
from sc,c
where sc.cno=c.cno and sno='S3'
--(4) 查询至少选修LIU老师所授课程中一门课程的女学生姓名。
select sname
from s
where ssex='女'
and sno in(select sno
from sc
where cno in(select cno
from c
where teacher='刘'))
--(5) 查询Wang同学不学的课程的课程号。
select cno
from c
where not exists(select * from s,sc
where s.sno=sc.sno
and sc.cno=c.cno
and sname='wang')
--(6) 查询至少选修两门课程的学生学号。
--方法一：
select distinct a.sno
from sc as a,sc as b
where a.sno=b.sno and a.cno!=b.cno
--方法二：
select sno
from sc
group by sno
having count(cno)>=2
--(7) 查询全部学生都选修的课程的课程号与课程名。
select cno,cname
from c
where not exists
(select *
from s
where not exists
(select *
from sc
where sno=s.sno
and cno=c.cno))
--(8) 查询选修课程包含LIU老师所授课程的学生学号。
select distinct sno
from sc as x
where not exists
(select *
from c
where teacher='LIU'
and not exists
(select *
from sc as y
where y.sno=x.sno and y.cno=c.cno))
--(9) 统计有学生选修的课程门数。
select count(distinct Cno) as 课程门数
from sc
--(10) 求选修C4课程的女学生的平均年龄。
select avg(sage) as avg_age
from s,sc
where s.sno=sc.sno and cno='C4' and ssex='F'
--(11) 求LIU老师所授课程的每门课程的平均成绩。
select c.cno,avg(grade) as avg_grade
from sc,c
where sc.cno=c.cno and teacher='LIU'
group by c.cno
--(12) 统计每门课程的学生选修人数（超过10人的课程才统计）。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
select cno as 课程号,count(sno) as 学生人数
from sc
group by cno
having count(*)>1
order by 2 desc,1
--(13) 检索学号比Wang同学大，而年龄比他小的学生姓名。
select sname
from s
where sno>all(select sno
from s
where sname='wang')
and sage<all(select sage
from s
where sname='wang')
--(14) 在表SC中查询成绩为空值的学生学号和课程号。
select sno,cno
from sc
where grade is null
--(15) 查询姓名以L打头的所有学生的姓名和年龄。
select sname,sage
from s
where sname like 'L%'
--(16) 求年龄大于女同学平均年龄的男学生姓名和年龄。
select sname,sage
from s
where ssex='男'
and sage>(select avg(sage)
from s
where ssex='女')
--(17) 求年龄大于所有女同学年龄的男学生姓名和年龄。
select sname,sage
from s
where ssex='M'
and sage>all(select sage
from s
where ssex='F')
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
