1)查询被学生选修的课程有几个?(count)
select count(Cno)
from SC,C
where S.Sno = SC.Sno AND Sname = '张三'
select count(Cno)
from SC
where Sno in (
select Sno
from S
where Sname='张三')
select count(Cno)
from SC,C
where S.Sno = SC.Sno AND Sname = ‘张三’
select count(Cno)
from SC
where Sno in (
select Sno
from S
where Sname=‘张三’)
2)查询年龄在18-19之间的学生的信息?(between)
select *
from S
where age between 18 and 19
select *
from S
where age between 18 and 19
3)查询姓刘的同学的信息?(like)
select *
from S
where Sname like '刘_'
select *
from S
where Sname like '刘%'
select *
from S
where Sname like ‘刘_’
select *
from S
where Sname like ‘刘%’
_表示一个汉字,%表示字符
4)查询没有选修课的成绩?(is null)
select *
from C
where Cpno is null
select *
from C
where Cpno is null
(其中where Cpno = null格式错误)
5)求各门课学生的平均成绩,显示课程号及相应的平均成绩(AVG,group by)
select Cno,avg(grade)
from Sc
group by Cno
select Cno,avg(grade)
from Sc
group by Cno
6)查询有2个以上的学生选修的课程的课程号(having)
select Cno
from SC
group by Cno
having count(Sno)>2
select Cno
from SC
group by Cno
having count(Sno)>2
7)查询学生选修课程的基本情况,要求显示学生号,课程号,成绩(三表查询)
select Sname,Cname,grade
from S,C,SC
where S.Sno = SC.Sno AND C.Cno = SC.Cno
select Sname,Cname,grade
from S,C,SC
where S.Sno = SC.Sno AND C.Cno = SC.Cno
8)查询了选修全部课程的学生姓名(不存在没有选修课程)
select Sname from S
where not exists(
select * from C
where not exists(
select * from SC
where S.Sno = SC.Sno AND C.Cno = SC.Cno))
select Sname from S
where not exists(
select * from C
where not exists(
select * from SC
where S.Sno = SC.Sno AND C.Cno = SC.Cno))