SQL查询习题
SQL查询习题
一、单表查询练习
1、查询<学生信息表>，查询学生"张三"的全部基本信息select *
from xsb
where xm='张三'
2、查询<学生信息表>，查询学生"张三"和”张四”的基本信息
select *
from xsb
where xm='张三'or xm='张四'
3、查询<学生信息表>，查询姓"张"学生的基本信息
select *
from xsb
where xm like '张%'
4、查询<学生信息表>，查询姓名中含有"四"字的学生的基本信息
select *
from xsb
where xm like '%四%'
5、查询<学生信息表>，查询姓名长度为三个字，姓“李”，且最后一个字是“强”的全部学生信息。
select *
from xsb
where xm like '李_强'
6、查询<学生信息表>，查询姓"张"或者姓”李”的学生的基本信息。
select *
from xsb
where xm like '张%'or xm='李%'
7、查询<学生信息表>，查询姓"张"并且"所属省份"是"北京"的学生信息
select *
from xsb
where xm like '张%'and jg='北京'
8、查询<学生信息表>，查询"所属省份"是"北京"、”新疆”、”山东”或者"上海"的学生的信息
select *
from xsb
where jg='北京'or jg='上海'or jg='山东'or jg='新疆' or
select *
from xsb
where jg in ('北京','上海','山东','新疆')
9、查询<学生信息表>，查询姓"张"，但是"所属省份"不是"北京"的学生信息
select *
from xsb
where xm like '张%'and jg!='北京'
or
select *
from xsb
where xm like '张%'and jg<>'北京'
or
select *
from xsb
where xm like '张%'and jg not like '%北京%'
10、查询<学生信息表>，查询全部学生信息，并按照“性别”排序，性别相同的情况下按照“所属省份”排序，所属省份相同的情况下再按照“班级”排序
select *
from xsb
order by xb,jg,bj
11、查询<学生信息表>，查询现有学生都来自于哪些不同的省份
select distinct jg
from xsb
12、查询<学生选修信息表>，查询没有填写成绩的学生的学号、课程号和成绩
select xh,kch,cj
from cjb
where cj is null
13、查询<学生选修信息表>，查询全部填写了成绩的学生的选修信息，并按照“成绩”从高到低进行排序
select *
from cjb
where cj is not null
order by cj desc
14、找出两个姓张的同学信息，只显示对应学生的姓名，性别和班级
Select top 2 xm,xb,bj
From xsb
Where xm like '张%'
二、聚合函数练习
1、统计<学生信息表>，统计共有多少个学生
select count (*)
from xsb
2、统计<学生信息表>，统计年龄大于20岁的学生有多少个select count (*)
from xsb
where nl>='20'
or
select count (*)
from xsb
where year(getdate())-year(csrq)>20
3、统计<学生信息表>，统计入学时间在1998年至2000年的学生人数
select count (*)
from xsb
where rxrq>='1998-1-1' and rxrq<='2000-12-31'
where rxrq between ‘1998-1-1’ and ‘2000-12-31’
4、统计<学生选修信息表>，统计学号为"S001"的学生的平均成绩
select AVG(cj)
from cjb
where xh='1'
OR
select CONVERT(DECIMAL(18,2),AVG(cj)) ||保留小数点两位
from cjb
where xh='1'
5、统计<学生选修信息表>，统计学号为"S001"的学生的总成绩
select sum(cj)
from cjb
where xh='1'
6、统计<学生选修信息表>，查询课程号为”C001”的课程的最高成绩
select max(cj)
from cjb
where kch='23'
7、统计<学生信息表>，查询所有学生中的最大年龄是多少select max(nl)
from xsb
三、分组查询练习
1、统计<学生选修信息表>，统计每个课程的选修人数
select kch,count(*)
from cjb
group by kch
2、统计<学生选修信息表>，统计每个同学的总成绩
select sum(cj)
from cjb
group by xh
3、统计<学生信息表>，统计每个班级中每种性别的学生人数，并按照班级排序
select bj,xb,count(*)
from xsb
group by bj,xb
4、统计<学生选修信息表>，统计每门课程的平均成绩,并按
照平均成绩降序排序
select kch,AVG(cj)
from cjb
group by kch
order by AVG(cj) desc
5、统计<学生选修信息表>，显示有两门以上课程不及格的学生的学号
select xh
from cjb
where cj<'60'
group by xh
having count(*)>='2' 分组后的条件判断
6、统计<学生信息表>，统计每个班级中的最大年龄是多少select bj,SUM(nl)
from xsb
group by bj
四、嵌套查询练习
1、用子查询实现，查询选修“高等数学”课的全部学生的总成绩
1.课程表查课程号
2.查所有高数成绩
Select Sum( cj)
from cjb
where kch=( Select kch
from kcb
where kcm='高等数学'
)
2、用子查询实现，统计<学生选修信息表>，显示学号为"S001"的学生在其各科成绩中，最高分成绩所对应的课程
思考：如果该学号学生有两个课程分数都为最高的100分，查询会有什么结果
学号一最高分
最高分对应的课程号
课程号对应课程名
Select max(cj)
From cjb
Where xh='1'
Select kch
From kcb
Where cj= (select max(cj ) from cjb ) and xh=’1’
Select kcm
From kcb
Where kch in ( Select kch
From kcb
Where cj= (select max(cj ) from cjb ) and xh=’1’)
3、用子查询实现，查询2班选修"数据库技术"课的所有学生的成绩之和
1.班级二班学生
Select xh
From xsb
Where bj=’2’
2.课程表里查课程号
Select kch
From kcb
Where kcm=’数据库技术’
Select sum(cj)
From cjb
Where xh in( Select xh
From xsb
Where bj=’2’) and kch=( Select kch
From kcb
Where kcm=’数据库技术’
)
4、用子查询实现，查询3班"张三"同学的"测试管理"成绩
1.测试管理的课程号
2.张三的学号
Select xh
From xsb
Where xm=’张三’and bj=’3’
Select kch
From kcb
Where kcm=’测试管理’
Select cj
From cjb
Where xh in ( Select xh ||in 可能有重名的多个‘张三’
From xsb
Where xm=’张三’
) and kcm=( Select kch
From kcb
Where kcm=’测试管理’
)
五、联接查询练习
1、查询"张三"的各科考试成绩，要求显示姓名、课程号和成绩
查张三学号
Select xh
From xsb
Where xm=’张三’
查学号对应成绩
Select cj,kch
From cjb
Where xh in(Select xh
From xsb
Where xm=’张三’
)
Or
select xm, kch,cj
from xsb,cjb
where xsb.xh=cjb.xh
and xm='张三'
2、查询"张三"的各科考试成绩中，哪科没有记录考试成绩，要求显示姓名、课程号和成绩
查张三学号
Select xh
From xsb
Where xm=’张三’
学号对应的没有成绩的
Select cj,kch
From cjb
Where cj is null
Select kch
From cjb
Where cj=( Select cj
From cjb
Where cj is null) and xh in (Select xh
From xsb
Where xm=’张三’)
Or
select xm, kch,cj
from xsb,cjb
where xsb.xh=cjb.xh
and xm='张三'
and cj is null
3、查询"张三"的各门课程成绩，要求显示姓名、课程名称和成绩
查张三学号
学号对应成绩
select xm,kcm,cj
from xsb,cjb,kcb
where xsb.xh=cjb.xh
and cjb.kch=kcb.kch
and xm='张三'
4、查询3班"张三"的"测试管理"成绩，要求显示姓名、成绩
查3班张三的学号
查测试管理的课程号
根据学号和课程号查成绩
select xm,cj
from xsb,cjb,kcb
where xsb.xh=cjb.xh
and cjb.kch=kcb.kch
and xm='张三'and kcm='测试管理'
and bj=’3’
5、查询所有2000年以前入学的，各班男生的各科考试平均成绩
select AVG(cj),bj,kch
from xsb,cjb
where xsb.xh=cjb.xh
and rxrq<'2000 '
and xb='男'
group by bj,kch
六、外联接查询
查询”李坚强”所有课程的成绩，并显示学号、姓名、课程
号和成绩，没有成绩记录的学号包括：('S009','S010','S011')
1、使用左联接
2、使用右联接
3、对比等值连接
七、补充提高
1、查询“张三”比“王三”入学早几年
2、查询所在班级和该班内学生的年龄之和，其中每个人的年龄都大于20岁，每个班的年龄之和大于60岁
3、计算每种产品的剩余库存量
表1，为产品进货表，产品名称name[char(10)]，产品数量amount[int]
表2，为产品出货表，产品名称name[char(10)]，产品数量amount[int]
业务逻辑：表1存储产品的总进货量，表2存储每种产品每次的出货量，如产品A进货为100，出货了3次，每次分别为10、20、30，那么A产品的库存就为40
表A1 | 表A2
-------------------|--------------------------- name amount | name amount
A 100 | A 10
B 100 | A 20
| A 30
| B 10
| B 40