SQL语句典型例子

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