2024年1月20日发(作者：)

实 验 报 告

课程名称 数据库原理与应用 实验名称 数据查询

系别 专业班级 指导教师

学号 姓名 实验日期 _

实验名称：数据查询

一、实验目的

1． 熟悉各种基本的数据查询的含义。

2． 掌握数据查询的SQL语句编写方法。

3． 能根据要求写出正确的查询语句。

4． 掌握基本的调试方法。

二、实验环境

1． 硬件环境：微机

2． 软件环境： Windows，Sql server2000或更高版本

三、实验内容及步骤

题目 根据要求编写以下SQL查询语句

第一题 简单查询

1. 查询病人表，显示所有病人的病人编号、保险公司名称、电话号码（别名：病人电话）。

2. 查询病人表，显示病人编号、病人姓名（病人姓+病人名）、保险公司名称，并在每个“电话号码”前面显示字符串“病人电话：”。

3. 查询病人表，要求显示保险公司名称，并消除重复的值。

4. 查询病人表，要求只显示前五条的全部病人信息。

5. 查询病人表，要求显示最年轻的前6位病人的病人编号，病人姓名，病人年龄

6. 给医生表取别名：doctors，并显示医生表的所有信息

7. 要求查询在“人民保险公司”投保的所有病人的信息

8. 要求查询病人年龄在20~60岁之间的所有病人信息

9. 要求查询姓“王”、姓“李”或姓“关”的所有病人的信息。（提示：用“in”或者用“or”两种方法查询。）

10. 查询电话号码为空的病人信息

11. 要求按年龄从大到小显示病人信息

12.

13.

14.

15.

要求先按病人姓的升序；如果姓一样，再按年龄的降序，来显示病人信息

要求查询电话号码的最后一个数字为6的病人编号，病人姓名，电话号码

要求查询倒数第二个数字为7的病人编号，病人姓名，电话号码。

要求查询除区号外的第二个数字为2的病人编号，病人姓名，电话号码。（注：手机号不算。）

第 1 页 共 9 页

16. 要求查询电话号码的最后一个数字为6、为3、为1的病人编号，病人姓名，电话号码。（注：至少两种方法可以实现）

17. 要求查询电话号码的最后一个数字除1、3、6外的病人编号，病人姓名，电话号码。

步骤（写出以上SQL语句）

--1.查询病人表，显示所有病人的病人编号、保险公司名称、电话号码（别名：病人电话）。

select 病人编号,保险公司名称,电话号码病人电话

from 病人表

go

--2.查询病人表，显示病人编号、病人姓名（病人姓+病人名）、保险公司名称，并在每个“电话号码”前面显示字符串“病人电话：”。

select 病人编号,病人姓+病人名病人姓名,保险公司名称,'病人电话：' 病人电话,电话号码

from 病人表

go

--3.查询病人表，要求显示保险公司名称，并消除重复的值。

select distinct 保险公司名称

from 病人表

go

--4.查询病人表，要求只显示前五条的全部病人信息。

select top 5 *

from 病人表

go

--5.查询病人表，要求显示最年轻的前位病人的病人编号，病人姓名，病人年龄

select top 5 病人编号,病人姓+病人名病人姓名,病人年龄

from 病人表

order by 病人年龄ASC

go

--6.给医生表取别名：doctors，并显示医生表的所有信息

select *

from 医生表as doctors

go

--7.要求查询在“人民保险公司”投保的所有病人的信息

select *

from 病人表

where 保险公司名称= '人民保险公司'

go

--8.要求查询病人年龄在~60岁之间的所有病人信息

select *

from 病人表

where 病人年龄between 20 and 60

go

--9.要求查询姓“王”、姓“李”或姓“关”的所有病人的信息。（提示：用“in”或者用“or”两种方法查询。）

select *

from 病人表

where 病人姓in('李','王','关')

go

--10.查询电话号码为空的病人信息

第 2 页 共 9 页

select *

from 病人表

where 电话号码is null

go

--11.要求按年龄从大到小显示病人信息

select *

from 病人表

order by 病人年龄DESC

go

--12.要求先按病人姓的升序；如果姓一样，再按年龄的降序，来显示病人信息

select *

from 病人表

order by 病人姓ASC,病人年龄DESC

go

--13.要求查询电话号码的最后一个数字为的病人编号，病人姓名，电话号码

select 病人编号,病人姓+病人名病人姓名,电话号码

from 病人表

where 电话号码like '%6'

go

--14.要求查询倒数第二个数字为的病人编号，病人姓名，电话号码。

select 病人编号,病人姓+病人名病人姓名,电话号码

from 病人表

where 电话号码like '%7_'

go

--15.要求查询除区号外的第二个数字为的病人编号，病人姓名，电话号码。（注：手机号不算。）

select 病人编号,病人姓+病人名病人姓名,电话号码

from 病人表

where 电话号码like'____-_2%'

go

--16.要求查询电话号码的最后一个数字为、为、为的病人编号，病人姓名，电话号码。（注：至少两种方法可以实现）

select 病人编号,病人姓+病人名病人姓名,电话号码

from 病人表

where 电话号码like'%6' or 电话号码like'%3' or 电话号码like'%1'

go

select 病人编号,病人姓+病人名病人姓名,电话号码from 病人表

where 电话号码like'%[6,3,1]'

go

--17.要求查询电话号码的最后一个数字除、、外的病人编号，病人姓名，电话号码。

select 病人编号,病人姓+病人名病人姓名,电话号码

from 病人表

where 电话号码not like'%[6,3,1]'

go

第二题 分组查询

1. 查询病人表，要求显示最大年龄值、最小年龄值、平均年龄值

2. 查询病人表，要求统计在“太平洋保险公司”投保的病人数

第 3 页 共 9 页

3. 查询一下病人表，要求统计有电话号码的病人数

4. 查询病人表，要求统计在各个保险公司投保的各自病人数。（两种方法：group by与compute by）

5. 查询病人表，要求统计投保人数2人以上（含2人）的保险公司名称与投保人数。

6. 查询病历表，要求统计一下病人编号为"Pat0002"的病人结算总金额。（注：结算金额相同的只算一个记入总金额中）

7. 查询病历表，要求统计一下病人编号为"Pat0002"的病人结算总金额。（注：结算金额相同的不记入总金额中）

步骤

--1.查询病人表，要求显示最大年龄值、最小年龄值、平均年龄值

select max(病人年龄) 最大年龄,min(病人年龄) 最小年龄, avg(病人年龄) 平均年龄

from 病人表

go

--2.查询病人表，要求统计在“太平洋保险公司”投保的病人数

select count(保险公司名称) 太平洋保险公司投保的病人数

from 病人表

where 保险公司名称= '太平洋保险公司'

go

--3.查询一下病人表，要求统计有电话号码的病人数

select count(电话号码) 有电话号码的病人数

from 病人表

where 电话号码is not null

go

--4.查询病人表，要求统计在各个保险公司投保的各自病人数。（两种方法：group by与compute by）

select count(保险公司名称)

from 病人表

where 保险公司名称is not null

group by 保险公司名称

go

--5.查询病人表，要求统计投保人数人以上（含人）的保险公司名称与投保人数。

select 保险公司名称,count(*) 投保人数

from 病人表

group by 保险公司名称

having count(*)>=2

go

--6.查询病历表，要求统计一下病人编号为"Pat0002"的病人结算总金额。（注：结算金额相同的只算一个记入总金额中）

select distinct sum(结算金额) 结算总金额

from 病历表

where 病人编号='Pat0002'

go

--7.查询病历表，要求统计一下病人编号为"Pat0002"的病人结算总金额。（注：结算金额相同的不记入总金额中）

select sum(结算金额) as 结算总金额

from 病历表

where 病人编号='Pat0002'

go

第 4 页 共 9 页

第三题 多表联接查询

1. 查询所有有看过病的病人编号，病人姓名，住院日期，病历。

2. 查询所有病人的病人编号，病人姓名，住院日期，病历。（注：如果有的病人暂时还没有看过病，则相应内容显示为NULL）

3.

4.

5.

6.

显示“王太山”该病人的所有的看病资料（病人编号，病人姓名，住院日期，病历）。

统计一下“柳四二”该病人的看病次数，以及结算总金额。

查询医生编号为'docek001'的医生看过的所有病人资料（病人编号，病人姓名，医生编号）。

查询'张明仁'医生看过的所有病人资料（病人编号，病人姓名，医生姓名）。

7. 显示出院日期在2008年间每个病人的最高的一笔结算金额。（注：要显示的信息有--病人编号，病人姓名，最高的结算金额）

8. 查询具有相同专业的医生。（注：显示的信息有--医生编号，医生姓名，科室）

步骤

--1.查询所有有看过病的病人编号，病人姓名，住院日期，病历。

select 病人表.病人编号,病人姓+病人名病人姓名,住院日期,病历from 病人表,病历表

where 病人表.病人编号= 病历表.病人编号

go

--2.查询所有病人的病人编号，病人姓名，住院日期，病历。(注：如果有的病人暂时还没有看过病，则相应内容显示为NULL）

select 病人表.病人编号,病人姓+病人名病人姓名,住院日期,病历

from 病人表left outer join 病历表on(病人表.病人编号= 病历表.病人编号)

go

--3.显示“王太山”该病人的所有的看病资料（病人编号，病人姓名，住院日期，病历）。

select 病人表.病人编号,病人姓+病人名病人姓名,住院日期,病历

from 病人表,病历表

where 病人表.病人编号= 病历表.病人编号and 病人姓+病人名='王太山'

go

--4.统计一下“柳四二”该病人的看病次数，以及结算总金额。

select count(病历) 看病次数,count(结算金额) 结算总金额

from 病人表,病历表

where 病人姓+病人名= '柳四二'

go

--5.查询医生编号为'docek001'的医生看过的所有病人资料（病人编号，病人姓名，医生编号）。

select 病人表.病人编号,病人姓+病人名病人姓名,医生表.医生编号

from 病人表,医生表,病历表

where 病人表.病人编号= 病历表.病人编号and 医生表.医生编号= 病历表.医生编号and 医生表.医生编号=

'docek001'

go

--6.查询'张明仁'医生看过的所有病人资料（病人编号，病人姓名，医生姓名）。

select 病人表.病人编号,病人姓+病人名病人姓名,医生姓+医生名医生姓名

from 病人表,医生表,病历表

where 病人表.病人编号= 病历表.病人编号and 医生表.医生编号= 病历表.医生编号and 医生表.医生姓+医生表.医生名= '张明仁'

go

--7.显示出院日期在年间每个病人的最高的一笔结算金额。（注：要显示的信息有--病人编号，病人姓名，最高的结算金额）

第 5 页 共 9 页

select 病人表.病人编号,病人姓+病人名病人姓名,max(结算金额) 最高的结算金额

from 病人表,病历表

where 病人表.病人编号= 病历表.病人编号and 出院日期between'2008-1-1' and '2008-12-31'

group by 病人表.病人编号,病人姓+病人名

go

--8.查询具有相同专业的医生。（注：显示的信息有--医生编号，医生姓名，科室）

select one.医生编号,one.医生姓+ one.医生名医生姓名,one.科室

from 医生表one,医生表two

where one.科室= two.科室

group by one.医生编号,one.科室,one.医生姓,one.医生名

having count(one.科室)>1

go

第四题 子查询

1. 使用两种子查询的方法。要求查询病历表，要求统计一下病人编号为"Pat0002"的病人结算总金额。（注：结算金额相同的不记入总金额中）

2. 请用“相关子查询作为表达式”方法写出SELECT语句，来统计并显示每个医生的医生编号、医生姓名以及每个医生看过的病人数。要求：如果还没看过病人的医生，则显示看过的病人数为0。

3. 请用“派生表”的子查询方法写出SELECT语句，来统计并显示每个医生的医生编号、医生姓名以及每个医生看过的病人数。要求：如果还没看过病人的医生，则显示看过的病人数为0。

4. 请用“相关子查询作为表达式”的子查询方法写出SELECT语句，来统计并显示每个医生的医生编号、医生姓名以及每个医生看过的病人数。要求：如果还没看过病人的医生，不显示信息。

5. 请用“派生表”的子查询方法写出SELECT语句，来统计并显示每个医生的医生编号、医生姓名以及每个医生看过的病人数。要求：如果还没看过病人的医生，不显示信息。

6. 查询具有相同专业的医生。（注：显示的信息有--医生编号，医生姓名，科室）(提示：可使用in或exists关键字实现。)

7. 查询看过所有医生的病人信息

8. 查询至少看过病人编号为'Pat0004'看过的全部医生的病人编号

9. 查询结算总金额最多的病人编号，病人姓名和相应的结算总金额

步骤

--1.使用两种子查询的方法。要求查询病历表，要求统计一下病人编号为"Pat0002"的病人结算总金额。（注：结算金额相同的不记入总金额中）

--第一种方法:

select sum (结算金额) as 结算总金额

from 病历表

where 结算金额not in

(

select 结算金额

from 病历表

where 病人编号='Pat0002'

group by 结算金额

having count (结算金额)>1

)and 病人编号='Pat0002'

go

--第二种方法:

select sum (结算金额) as 结算总金额

第 6 页 共 9 页

from 病历表

where 结算金额=any

(

select 结算金额

from 病历表

where 病人编号='Pat0002'

group by 结算金额

having count (结算金额)=1

)and 病人编号='Pat0002'

go

--2.请用“相关子查询作为表达式”方法写出SELECT语句，来统计并显示每个医生的医生编号、医生姓名以及每个医生看过的病人数。要求：如果还没看过病人的医生，则显示看过的病人数为。

select b.医生编号,b.医生姓+b.医生名as 医生姓名,'该医生看过的病人数'=

(select count(病人编号)from 病历表as a where a.医生编号=b.医生编号)

from 医生表as b

order by b.医生编号

go

--3.请用“派生表”的子查询方法写出SELECT语句，来统计并显示每个医生的医生编号、医生姓名以及每个医生看过的病人数。要求：如果还没看过病人的医生，则显示看过的病人数为。

select a.医生编号,a.医生姓+a.医生名as 医生姓名,isnull (b.该医生看过的病人数,0)

from 医生表as a left outer join

(

select 医生编号, count(*) as 该医生看过的病人数

from 病历表

group by 医生编号)as b

on a.医生编号=b.医生编号

go

--4.请用“相关子查询作为表达式”的子查询方法写出SELECT语句，来统计并显示每个医生的医生编号、医生姓名以及每个医生看过的病人数。要求：如果还没看过病人的医生，不显示信息。

select 病历表.医生编号,医生姓+医生名as 医生姓名,count(*)as 该医生看过的病人数

from 病历表inner join 医生表

on 病历表.医生编号=医生表.医生编号

group by 病历表.医生编号,医生姓+医生名

union

select 医生表.医生编号,医生姓+医生名as 医生姓名,病人编号=0

from 病历表right outer join 医生表

on 病历表.医生编号=医生表.医生编号

where 病人编号is null

go

--5.请用“派生表”的子查询方法写出SELECT语句，来统计并显示每个医生的医生编号、医生姓名以及每个医生看过的病人数。要求：如果还没看过病人的医生，不显示信息。

select a.医生编号,a.医生姓+a.医生名as 医生姓名, b.该医生看过的病人数

from 医生表as a inner join

(select 医生编号,count(*)as 该医生看过的病人数

from 病历表

group by 医生编号)as b

第 7 页 共 9 页

on a.医生编号=b.医生编号

go

--6.查询具有相同专业的医生。（注：显示的信息有--医生编号，医生姓名，科室）(提示：可使用in或exists关键字实现。)

select distinct a.医生编号,a.医生姓+a.医生名as 医生姓名,a.科室

from 医生表as a inner join 医生表as b

on a.科室=b.科室

where a.医生编号>=b.医生编号

and b.科室not in

(

select a.科室

from 医生表as a inner join 医生表as b

on a.科室=b.科室

where a.医生编号>=b.医生编号

group by a.科室

having count(b.科室)=1

)

go

--7.查询看过所有医生的病人信息

select 病人编号,病人姓+病人名as 病人姓名,保险公司名称,年龄,电话号码

from 病人表

where not exists

(

select *

from 医生表

where not exists

(

select *

from 病历表

where 病人编号=病人表.病人编号

and 医生编号=医生表.医生编号

)

)

go

--8.查询至少看过病人编号为'Pat0004'看过的全部医生的病人编号

select distinct 病人编号

from 病历表as C_A

where not exists

(

select *

from 病历表as C_B

where 病人编号='Pat0004' and not exists

(

select *

from 病历表as C_C

where C_C.病人编号=C_A.病人编号

第 8 页 共 9 页

and C_C.医生编号=C_B.医生编号

)

)

go

--9.查询结算总金额最多的病人编号，病人姓名和相应的结算总金额

select a.病人编号,病人姓+病人名as 病人姓名,sum (结算金额) as 结算总金额

from 病历表as a inner join 病历表as b on a.病人编号=b.病人编号

group by a.病人编号,病人姓,病人名

having sum(结算金额)=(select max (结算总金额)

from (select 病人编号,sum (结算金额) as 结算总金额

go

五、实验小结

from 病历表

group by 病人编号) as T)

第 9 页 共 9 页