数据库语言查询练习题(4-25)

单表查询：

1、查询<学生信息表>，查询学生"张三"的全部基本信息

2、查询<学生信息表>，查询学生"张三"和”李四”的基本信息

3、查询<学生信息表>，查询姓"张"学生的基本信息

4、查询<学生信息表>，查询姓名中含有"四"字的学生的基本信息

5、查询<学生信息表>，查询姓名长度为三个字，姓“李”，且最后一个字是“强”的全部学生信息。

6、查询<学生信息表>，查询姓"张"或者姓”李”的学生的基本信息。

7、查询<学生信息表>，查询姓"张"并且"所属省份"是"北京"的学生信息

8、查询<学生信息表>，查询"所属省份"是"北京"、”新疆”、”山东”或者"上海"的学生的信息

9、查询<学生信息表>，查询姓"张"，但是"所属省份"不是"北京"的学生信息Select *

10、查询<学生信息表>，查询全部学生信息，并按照“性别”排序，性别相同的情况下按照“所属省份”排序，所属省份相同的情况下再按照“班级”排序

11、查询<学生信息表>，查询现有学生都来自于哪些不同的省份

12、查询<学生选修信息表>，查询没有填写成绩的学生的学号、课程号和成绩

13、查询<学生选修信息表>，查询全部填写了成绩的学生的选修信息，并按照“成绩”从高到低进行排序

聚合查询

1、统计<学生信息表>，统计共有多少个学生

2、统计<学生信息表>，统计年龄大于20岁的学生有多少个

3、统计<学生信息表>，统计入学时间在1980年至1982年的学生人数

4、统计<学生选修信息表>，统计学号为"S001"的学生的平均成绩

5、统计<学生选修信息表>，统计学号为"S001"的学生的总成绩

6、统计<学生选修信息表>，查询课程号为”C001”的课程的最高成绩

7、统计<学生信息表>，查询所有学生中的最大年龄是多少

分组查询练习

1、统计<学生选修信息表>，统计每个课程的选修人数

2、统计<学生选修信息表>，统计每个同学的总成绩

3、统计<学生信息表>，统计每个班级中每种性别的学生人数，并按照班级排序

4、统计<学生选修信息表>，统计每门课程的平均成绩,并按照成绩降序排序

5、统计<学生选修信息表>，显示有两门以上课程不及格的学生的学号

6、统计<学生信息表>，统计每个班级中的最大年龄是多少

嵌套查询练习

1、用子查询实现，查询选修“高等数学”课的全部学生的总成绩

2、用子查询实现，统计<学生选修信息表>，显示学号为"S001"的学生在其各科成绩中，最高分成绩所对应的课程号和成绩

3、用子查询实现，查询2班选修"数据库技术"课的所有学生的成绩之和

4、用子查询实现，查询3班"张三"同学的"测试管理"成绩

一、单表查询练习

1、查询<学生信息表>，查询学生"张三"的全部基本信息

Select *

from A_studentinfo

where sname='张三'

2、查询<学生信息表>，查询学生"张三"和”李四”的基本信息

Select *

from A_studentinfo

where sname='张三'

or sname='李四'

3、查询<学生信息表>，查询姓"张"学生的基本信息

Select *

from A_studentinfo

where sname like '张%'

4、查询<学生信息表>，查询姓名中含有"四"字的学生的基本信息

Select *

from A_studentinfo

where sname like '%四%'

5、查询<学生信息表>，查询姓名长度为三个字，姓“李”，且最后一个字是“强”的全部学生信息。

select *

where sname like '李_强'

6、查询<学生信息表>，查询姓"张"或者姓”李”的学生的基本信息。

Select *

from A_studentinfo

where sname like '张%'

or sname like '李%'

7、查询<学生信息表>，查询姓"张"并且"所属省份"是"北京"的学生信息

Select *

from A_studentinfo

where sname like '张%'

and province='北京'

8、查询<学生信息表>，查询"所属省份"是"北京"、”新疆”、”山东”或者"上海"的学生的信息

Select *

from A_studentinfo

where province in ('北京','上海','新疆','山东')

9、查询<学生信息表>，查询姓"张"，但是"所属省份"不是"北京"的学生信息Select *

from A_studentinfo

where sname like '张%'

and province !='北京'

10、查询<学生信息表>，查询全部学生信息，并按照“性别”排序，性别相同的情况下按照“所属省份”排序，所属省份相同的情况下再按照“班级”排序select *

from A_studentinfo

order by sex,province,class

11、查询<学生信息表>，查询现有学生都来自于哪些不同的省份

select distinct province as 省份

12、查询<学生选修信息表>，查询没有填写成绩的学生的学号、课程号和成绩Select *

from A_studentcourse

where score is null

13、查询<学生选修信息表>，查询全部填写了成绩的学生的选修信息，并按照“成绩”从高到低进行排序

Select *

from A_studentcourse

where score is not null

order by score desc

二、聚合查询

1、统计<学生信息表>，统计共有多少个学生

Select count (*) as 学生数量

from A_studentinfo

2、统计<学生信息表>，统计年龄大于20岁的学生有多少个

Select count(*) as 学生数量

from A_studentinfo

where (2008-yearofbirth)>20

3、统计<学生信息表>，统计入学时间在1980年至1982年的学生人数

select count(*) as 学生数量

from A_studentinfo

where enrollment between '1998-01-01' and '2003-12-30'

对比以下查询方式，看看有何不同，为什么？

select count(*) as 学生数量

where enrollment between '1998' and '2003'

4、统计<学生选修信息表>，统计学号为"S001"的学生的平均成绩Select avg(score) as 平均成绩

from A_studentcourse

where sno='S001'

5、统计<学生选修信息表>，统计学号为"S001"的学生的总成绩select sum(score) as 总成绩

from A_studentcourse

where sno ='S001'

6、统计<学生选修信息表>，查询课程号为”C001”的课程的最高成绩select max(score) as 最高成绩

from A_studentcourse

where cno='C001'

7、统计<学生信息表>，查询所有学生中的最大年龄是多少

select 2008-min(yearofbirth) as 最大年龄

from A_studentinfo

三、分组查询练习

1、统计<学生选修信息表>，统计每个课程的选修人数

select cno,count(*) as 学生数量

from A_studentcourse

group by cno

2、统计<学生选修信息表>，统计每个同学的总成绩

select sno,sum(score) as 总成绩

from A_studentcourse

group by sno

3、统计<学生信息表>，统计每个班级中每种性别的学生人数，并按照班级排序select class as 班级,sex as 性别, count(*) as 人数

from A_studentinfo

group by class,sex

order by class

4、统计<学生选修信息表>，统计每门课程的平均成绩,并按照成绩降序排序Select cno,avg(score) as 平均成绩

from A_studentcourse

group by cno

order by avg(score) desc

5、统计<学生选修信息表>，显示有两门以上课程不及格的学生的学号

Select sno as 不及格学生学号

from A_studentcourse

where score<60

group by sno

having count(*)>1

6、统计<学生信息表>，统计每个班级中的最大年龄是多少

select class as 班级, 2008-min(yearofbirth) as 最大年龄

from A_studentinfo

group by class

四、嵌套查询练习

1、用子查询实现，查询选修“高等数学”课的全部学生的总成绩

select sum(考试成绩) as 高等数学总成绩

from 选课

where 课号=

(

select 课号

from 课程

where 名称='高等数学'

)

2、用子查询实现，统计<学生选修信息表>，显示学号为"S001"的学生在其各科成绩中，最高分成绩所对应的课程号和成绩

select 课号,成绩

from 选课

where 学号='S001'

and 考试成绩=

(

select max(考试成绩)

from 选课

where学号='S001'

)

3、用子查询实现，查询2班选修"数据库技术"课的所有学生的成绩之和select sum(score) as 数据库技术总成绩

from A_studentcourse

where cno =

(

select cno

from A_courseinfo

where subject='数据库技术')

and sno in

(

select sno

from A_studentinfo

where class='2'

)

4、用子查询实现，查询3班"张三"同学的"测试管理"成绩select score

from A_studentcourse

where cno=

(

select cno

from A_courseinfo

where subject='测试管理'

)

and sno in

(

select sno

from A_studentinfo

where class='3'

and sname='张三'

)

五、联接查询练习

1、查询"张三"的各科考试成绩，要求显示姓名、课程号和成绩

select sname as 姓名,cno as 课程号,score as 成绩

from A_studentinfo,A_studentcourse

where A_studentinfo.sno=A_studentcourse.sno

and sname='张三'

2、查询"张三"的各科考试成绩中，哪科没有记录考试成绩，要求显示姓名、课程号和成绩

select sname as 姓名,cno as 课程号,score as 成绩

from A_studentinfo,A_studentcourse

where A_studentinfo.sno=A_studentcourse.sno

and sname='张三'

and score is null

3、查询"张三"的各门课程成绩，要求显示姓名、课程名称和成绩

select sname as 姓名,subject as 课程名称,score as 成绩

from A_studentinfo,A_courseinfo,A_studentcourse

where A_studentcourse.sno=A_studentinfo.sno

and A_https://www.360docs.net/doc/3616211401.html,o=A_https://www.360docs.net/doc/3616211401.html,o

and A_studentinfo.sname='张三'

4、查询3班"张三"的"测试管理"成绩，要求显示姓名、成绩

select sname as 姓名,score as 成绩

from A_studentcourse,A_courseinfo,A_studentinfo

where A_https://www.360docs.net/doc/3616211401.html,o=A_https://www.360docs.net/doc/3616211401.html,o

and A_studentcourse.sno=A_studentinfo.sno

and subject='测试管理'

and class='3'

and sname='张三'

5、查询所有2000年以前入学的，各班男生的各科考试平均成绩

select class as 班级,avg(score) as 男生平均成绩

from A_studentcourse,A_courseinfo,A_studentinfo

where A_https://www.360docs.net/doc/3616211401.html,o=A_https://www.360docs.net/doc/3616211401.html,o

and A_studentcourse.sno=A_studentinfo.sno

and sex='男'

and enrollment<'2000-01-01'

group by class

六、外联接查询

查询李坚强所有课程的成绩，并显示学号、姓名、课程号和成绩，没有成绩记录的学号包括：('S009','S010','S011')

1、使用左联接

select A_studentinfo.sno as 学生表学号,sname as 姓名,A_studentcourse.sno as

成绩表学号, cno as 课程号,score as 成绩

from A_studentinfo

left join A_studentcourse

on A_studentinfo.sno=A_studentcourse.sno

where sname='李坚强'

2、使用右联接

select A_studentinfo.sno as 学生表学号,sname as 姓名,A_studentcourse.sno as

成绩表学号, cno as 课程号,score as 成绩

from A_studentcourse

right join A_studentinfo

on A_studentinfo.sno=A_studentcourse.sno

where sname='李坚强'

3、对比等值连接

select sname,score

from A_studentinfo,A_studentcourse

where A_studentinfo.sno=A_studentcourse.sno

and sname='李坚强'

七、补充提高

1、查询“张三”比“王三”入学早几年

select A.sname as 姓名,year(A.enrollment) as 入学时间,

B.sname as 姓名,year(B.enrollment) as 入学时间,

datediff(year,A.enrollment,B.enrollment) as 年差

from A_studentinfo A,A_studentinfo B

where A.sname='张三'

and B.sname='王三'

2、查询所在班级和该班内学生的年龄之和，其中每个人的年龄都大于20岁，每个班的年龄之和大于60岁

select class as 班级,sum(2008-yearofbirth) as 年龄和

from A_studentinfo

where (2008-yearofbirth) >20

group by class

having sum(2008-yearofbirth)>60

order by class

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

--方法1

--建立视图

create view A2Sum

as

select name,sum(amount) as thesum

from A2

group by name

--关联查询

select https://www.360docs.net/doc/3616211401.html,,A1.amount-A2Sum.thesum

from A1,A2Sum

where https://www.360docs.net/doc/3616211401.html,=https://www.360docs.net/doc/3616211401.html,

--方法2

select https://www.360docs.net/doc/3616211401.html,,A1.amount-A2Table.A2amount as stocks

from A1,(select name,sum(amount) as A2amount from A2 group by name) as A2Table

where https://www.360docs.net/doc/3616211401.html,=https://www.360docs.net/doc/3616211401.html,

--方法3

--定义单个产品变量

declare @aa int

set @aa=

(

select sum(amount)

from A2

where name='A'

group by name

)

--计算单个产品剩余

select name,amount-@aa as leave from A1

where name='A'