数据库查询基础

–1．查询有直接先行课的课程的课号，课名和先行课号。
select 课程号,课程名,先行课号
from kc
where 先行课号 is not null
–2．查询先行课号是“J001”号课程的课号和课名
select 课程号,课程名
from kc
where 先行课号=‘J001’
–3．查询所有的网络工程系姓李，张，王的同学的学号和姓名
select 学号,姓名
from xs
where 姓名 like '李%‘or 姓名 like’张%’ or 姓名 like’王%'and 专业=‘网络工程’
–4．查询不在网络工程和信息管理专业学习的学生的学号和姓名，系别，并对查询结果按照专业的升序和学号的降序排序
select 学号,姓名
from xs
where 专业 not in
(select 专业
from xs
where 专业='网络工程’or 专业=‘信息管理’
)
order by 专业
–5．查询每门课不及格的学生的人数，显示课号和人数
select 课程号,count(*)不及格人数
from cj
where 成绩<60
group by 课程号
–6．查询年龄不在30-35之间的网络工程系的学生的学号，姓名和年龄
select 学号,姓名,Datediff(year,出生时间,getdate())年龄
from xs
where Datediff(year,出生时间,getdate())<=35 and Datediff(year,出生时间,getdate())>=30
–7．查询没有选修‘J001’号课程的学生的学号（注意去掉重复的元组）
select distinct 学号
from cj
where 学号 not in
(select 学号
from cj
where 课程号=‘J001’
)

–8．查询每个学生的学号，姓名，出生年份，并给出生年份起别名为chusheng
select 学号,姓名,year(出生时间)chusheng
from xs
–9. 查询每个学生的学号，姓名和出生日期（出生日期根据身份证号码查询）
select 学号,姓名,substring(id,7,8)出生年月
from xs
where 姓名=‘王林’
–10.查询选修J001课程成绩排名第一的同学的学号和成绩
select top 1 成绩,xs.学号,姓名,cj.课程号
from xs,cj
where 课程号='J001’and xs.学号=cj.学号
order by 成绩 desc
–11. 显示学号第五位或者第六位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及专业；
select 学号,姓名,性别,datediff(yy,出生时间,getdate()) 年龄,专业
from xs
where substring(学号,5,1)=1 or substring(学号,5,1)=2 or substring(学号,5,1)=3 or substring(学号,5,1)=4 or substring(学号,5,1)=9
or substring(学号,6,1)=1 or substring(学号,6,1)=2 or substring(学号,6,1)=3 or substring(学号,6,1)=4 or substring(学号,6,1)=9
–12. 查询信息管理专业年龄超过20岁的学生的人数
select count(*) 总数
from xs
where 专业='信息管理’and datediff(yy,出生时间,getdate())>20
–13. 查询平均成绩超过80分的课程的课程号和平均成绩

select 课程号,avg(成绩)平均成绩
from cj
group by 课程号
having avg(成绩)>80
–14. 查询每个专业所有姓张的人数
select 专业,count(*)总人数
from xs
where 姓名 like ‘张%’
group by 专业
–15. 查询各种姓氏的人数（假设没有复姓）
select left(姓名,1)姓氏,count(left(姓名,1))数量
from xs
group by left(姓名,1)
–16.查询选修课程超过5门的学生的学号和选课门数，以及平均成绩
select 学号,count(课程号)选课门数,avg(成绩)平均成绩
from cj
group by 学号
having count(课程号)>=5
–17. 查询选修‘J001’课程的成绩排名前五的学生的学号和成绩
select top 5 学号,成绩
from cj
where 课程号=‘J001’
order by 成绩 desc
–18.查询每个学生的最低分和选课门数
select 学号,count(课程号)选课门数,min(成绩)最低分
from cj
group by 学号

–19. 查询各个专业各种性别的人数
select 专业,性别,count(*)
from xs
group by 专业,性别

–20.查询各个专业男生的人数
select 专业,count(性别)男生人数
from xs
where 性别=‘男’
group by 专业
–21. 列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

select 学号,count(),avg(成绩)
from cj
where 学号 in
(select 学号
from cj
where 成绩<60
group by 学号
having count()>=2)
group by 学号

–22.查询和程明的英语成绩相同的学生的学号，姓名，课程号，成绩
select xs.学号,姓名,课程号,成绩
from xs,cj
where xs.学号=cj.学号 and 成绩=
(select 成绩
from cj
where 学号=
(select 学号
from xs
where 姓名=‘程明’
)and 课程号=
(select 课程号
from kc
where 课程名=‘英语’
)
)and 课程号=
(select 课程号
from kc
where 课程名=‘英语’
)and 姓名<> ‘程明’

–23. 显示选修课程数最多的学号及选修课程数最少的学号；
select 学号
from cj
group by 学号
having count(课程号)>= all
(select count(课程号)
from cj
group by 学号)

select 学号
from cj
group by 学号
having count(课程号)<=all
(select count(课程号)
from cj
group by 学号
)
–24. 查询选修了A001或者A002或者J001或者J002课程的学生的学号和课程号
select 学号,课程号
from cj
where 课程号=‘A001’ or 课程号 ='A002’or 课程号=‘J001’ or 课程号= ‘J002’
–25. 查询姓名为两个字的不同姓氏的人数，输出姓氏，人数。
select substring(姓名,1,1)姓氏,count()人数
from xs
where len(姓名)=2
group by substring(姓名,1,1)
–26.查询选修了高数和英语的学生的学号和姓名
select 学号,姓名
from xs
where xs.学号 in
(select cj.学号
from cj
where 课程号=
(select 课程号
from kc
where 课程名=‘数学’)
)and xs.学号 in(select cj.学号
from cj
where 课程号=
(select 课程号
from kc
where 课程名=‘英语’))
–27. 查询没有选课的学生的学号和姓名
select 学号,姓名
from xs
where 学号 not in
(select 学号
from cj)
–28. 查询每个学生的学号，姓名，选课门数和平均分
select xs.学号,姓名,count()选课总数,avg(成绩)平均分
from xs,cj
where xs.学号=cj.学号
group by xs.学号,姓名
–29. 查询成绩高于英语的平均成绩的学生的学号，姓名，课程号，课程名和成绩
select xs.学号,姓名,cj.课程号,课程名,成绩
from xs,kc,cj
where xs.学号=cj.学号 and kc.课程号=cj.课程号 and 成绩>
(select avg(成绩)
from cj
where 课程号=(
select 课程号
from kc
where 课程名=‘英语’))
–30. 查询选课人数最多的课程的课程号，课程名
select cj.课程号,课程名
from cj,kc
where cj.课程号=kc.课程号
group by cj.课程号,课程名
having count()>=all(select count() from cj group by cj.课程号)

–31.查询计算机系选修英语的最高分（使用嵌套和连接两种方法实现）
–嵌套
select max(成绩)最高分
from cj
where 学号 in(select 学号 from xs where 专业=‘计算机’)
and
课程号=(select 课程号 from kc where 课程名=‘英语’)
–连接
select max(成绩)最高分
from cj,kc,xs
where cj.学号=xs.学号 and cj.课程号=kc.课程号 and xs.专业='计算机’and 课程名=‘英语’
–32.查询计算机系选修英语取得最高分的学生的学号，姓名和分数
select top 1 cj.学号,姓名,成绩 分数
from cj,xs
where cj.学号 in(select 学号 from xs where 专业=‘计算机’)
and
课程号=(select 课程号 from kc where 课程名=‘英语’)
and
cj.学号=xs.学号
–33. 查询和程明不在同一专业的学生的学号，姓名和专业
select 学号,姓名,专业
from xs
where 专业 <>(
select 专业
from xs
where 姓名=‘程明’
)
–34.查询没有选修高数和英语的学生的学号和姓名

select 学号,姓名
from xs
where 学号 not in(
select 学号
from cj
where 课程号 in(
select 课程号
from kc
where 课程名=‘数学’ or 课程名=‘英语’
)
)