大学软件学院-实验报告数据查询.docx

大学软件学院实验报告
课程：数据库原理与实用技术实验 学期：_任课教师：_专业：软件工程 学号：—姓名：成绩：
实验4数据查询
一、实验目的
（1）理解T-SQL语言的使用。
（2）熟练掌握数据查询语句。
（3）掌握合计函数的使用。
二、实验内容 1、CAP数据库的查询(记录每个查询的SQL语句和查询结果)
(1)建立CAP数据库，输入C、A、P、O四张表；Create DataBase CAP
ON Primary
(Name = cap_data, FileName=, c:\CAP\CAP. mdf', Size=10MB.
MaxSize=50MB, FileGrowth=10%) Log ONName=cap_log,
FileName='c:\CAP\CAP. Idf',
Size二5MB, MaxSize=25MB, FileGrowth=lMB)|CREATE DATABASE进程正在磁盘’cap_data'上分配MB的空间。 :REATE DATABASE进程正在磁盘’cap_l%'上分配5.00 MB的空间。
Create Table Products
Pid Char (3) Not NULL Unique.
Pname Varchar (10; Not NULL, City Varchar (15) Not NULL, Quantity int Not NULL, Price money Not NULL, Primary Key Pid ,
)
Create Table Customer^
< Cid Char 4) Mot Ml/LL Unique,
Not NULL,
Cname Varchar (15.)
City Varchar 15) Mot pn.n.L,
disent money Primary Key'Cid ,
NULL,
命令己成功联。I

命令己成功联。I
徐令已成功完慕

select ♦ frow salary!
<

Pno Base
Bonus Fact
Month
6
000002 3100
400
3500
3
7
000003 2800
450
3250
1
8
000003 2860
350
3210
2
9
000003 2800
650
3450
3
10
000004 2500
340
2840
1
11
000004 2540
320
2860
2
12
000004 2500
540
3040
3
13」
000005 3200
490
3690
1
14
000005 3200
480
3680
2
15
000005 3200
400
3600
3
16
000006 2700
600
3300
1
17
000006 2700
300
3000
2
18
000006 3000
800
3800
3
19
000007 4000
200
4200
1
20 000007 4000
-r- -1
250
4250
2
21
000007 4100
400
4500
3
22
000008 2800
350
3150
1
23
・■—・・—
000008 2800
300
3100
2
24
000008 2000
330
2330
3
（4）简单条件查询/ 查询person表中所有不重复的职称。

select distinct prof froa person
/查询具有高级职称的女员工信息
select ・ froa person
where sex ='玄’and prof ='高级'/ 查询职工姓名为黎明的员工数据
<

Pno Pname
Sex BirthdayProf Deptno
000007李艳
女 1960-05-14 00:00:00.000 高级 001
select ♦ from person where pnaine ='禁明’"I000009 黎明 男 1973-10-09 00:00:00.000 中级 002

Pname
Sex Birthday
Prof Deptno
/ 查询各部门的实发工资总数

dnane SuinFact
1
财务部39240
2
人事部18680
生
营销部31550
select dnamees fact as SumFact from department d. salary s} person pwhere p. pno-s. pno an， p. deptno-d. deptno group by dname
(5)复杂条件查询/ 查询平均工资高于3000的部门名和对应的平均工资。
F act) as平均工资 from person p. salary s. department d where p. pno=s. pno and p dept no d. deptno group by Dnajne Having -vg Fact) >3000

部门平均工资|
1
财务部 3270. 000000
2
人事部 3113. 333333
3
营销部 3505. 555555
, 查询1月份实发工资比平均实发工资高的员工姓名和实发工资额。

use employeeselect pno.pname into #person from person where pno in ( select pno from salary
where fact in (select fact from salary where month =1) and fact ( select avg (fact) from salary：*) select ♦ from ^person
<

pnopnane
1
000002郭达
2
000005罗大佑
3
000007卒艳
use employeeselect pname , fact from ^person p,salary s where p.pno=s.pno and month二1
<_ _£;

pnaroe
fact

郭达 3600罗大佑3690
李艳 4200
/ 查询2月份实发工资比一月高的员工姓名。

select pno . fact into #jansalary fro» salary where select pno .fact into #febsalary from salary where select ♦ froB #jansalary select ♦ from #febsalary
*onth=l
month=2
<

pno f act

1
000001 2600

2 J 000002 3600

3
000003 3250

000004 2840

5
L
000005 3690

6
1000006 3300

7000007 4200

8000008 3150

9
000009 3200

pno fact
1
000001 3000

叵
000002 3200

3
—
000003 3210

4
000004 2860

5
I—
000005 3680

6
000006 3000

7
000007 4250

8
000008 3100

9
1
000009 3240

select pname from personwhere pno m(select j.pno from tjansalary j join #febsalary f on j. pno-f. pno
where j. fact f fact
prime王辉
2 率辎/利用sql语句将1, 2, 3月累积的员工的实发工资按降序排序
use employeeselect pno,rum(fact) as SUMFACT from salary group by pno order by sun fact) desc
<
pno
SUMFACT

1
000007 12950
2
000005 10970
3
000002 10300
4
000006 10100
5
000003 9910
6
000009 9620
|；7~
000004 8740
8
000008 8580
9
000001 8300

Create Table Agents
(Aid Char (3) Not NULL Unique.
Aname Varchar (15) Not NULL, City Varchar 15) Not NULL, Percents »oney Not I RILL. Primary Key(Aid?,
)
命令己成功宪成。
create table Orders
Ordno int not null unique, inonth char (3) not null,
Cid char (4) not null,
Aid char (3) not null,
Pid char (3) not null, qty int not null,
Dollars money not null.
priaary key (Ordno),
foreign key (Cid) references oustoaers* 1cid), foreign key (Aid references agents aid . foreign key iPid references products pid),
命令已成功完成。

(2)完成课后习题[3.2]b、[3.5]、[3.8]a,b、[3.11]b,fj,l[3.2]b：检索获得了最高佣金百分率的代理商的aid值。
方法1：
use capselect aid from agents where percents =( select max percents) from agents)
<

aid
1
a03
方法2：
select top 1 aid from agents order by percents descselect cid. aid from customers c3 agents a

where cid not in select cid from orders x

where x. cid = c. cid ind x. aid a. aid1
<

cid ]aid
1
c002 aOl
2_
c002 a02
3
c002 aO4
4
c002 a06
5
c003 aOl
6
c003 a02
7
c003 aO4
8
c003 a05

c003 a06
10
c004 aOl
11
c004 a02
12
c004 a03
13
c004 aO4
14
cOO4 a05
15
c006 a02
16
c006 aO4
17
c006 a05
形式2：
select cid, aid fron custoners c. agents a=一一
d d
aiai

=一一
d d
aiai
vhere ( cid not ir. (select cid from orders x where x cid = c.cid anc k, and aid not _r (select sid from orders v where x cid - c.cid and x.
cid

01234567
12 34 RV67CVG^ 11111111

use capselect cid.-si dollars as MAXSPENT from orders group by cid;
cid MAXSPENT1
2
3
4
5
6
7
8
9
10
T1
12

1
2
3
4
5
6
7
8
9
10
T1
12
cOOl cOOl cOOl cOOl
900.0000 180.0000 540. 0000 540. 0000
cOOl 450. 0000 cOOl cOOl c002
720.0000
720.0000
1584.0000
c003 2208. 0000 c004 460. 0000 c006 900. 0000 c006 600. 0000
b：写一个查询来检索查询(A)中的MAXSPENT的平均值AVERAGE (对所有的顾客而言)。
select cid , avg dollars) as AVERAGE from orders group by cid
<

cid
AVERAGE

2
cOOl 506 c002 792
3
4
c003 1104 c004 460
5
c006 500
[3.11]
b:如果A是orders表中所有满足cid=x且pid=y的行的avg (qty),那我们称顾客x以平均量A订 购了一个产品y。可以用一个SQL语句求出以至少300的平均量订购了他们所收到的每样产品的顾 客的cid值吗？
一个不可以，至少两个。
select cid .pid, g dollars as avgdollars into #orders from orders
group by pid. cid select distinct cid from #orders where cid not m(select cid from borders
where avgdollars -3001
f：求出被所有住在Dallas的顾客都订购了的产品的pid值。

select cid. pid into borders from orders where cid m (select cid from customers where city like 'Dallas*) select ♦ from #orders
j:用一个Update语句把存放在Duluth或Dallas的所有产品的价格提高1()%。然后，重新运行最初 用于创立products表并导入数据的过程以恢复表中的原值。
复制一份作为临时表：
use capselect ♦ into ^products from products
(所影响的行数为7行)I
把存放在Duluth或Dallas的所有产品的价格提高10%：
use cap update products
set price = price + price ♦ 0.1
where city ' Duluth' or city Dallas'〔所影响的行数为5行)|use cap select ♦ from products
<

pid pname
city
quantity price
1
p01 comb
Dallas
111400
0. 5500...
2
p02 brush
Newark
203000
0.5
3_
p03 razor
Duluth
150600
1. 1000...
4
p04 pen
Duluth
125300
1. 1000...
5
p05 pencil
Dallas
221400
1. 1000...
6
p06 folder
Dallas
123100
2. 2000...
1
p07 case
Newark
100500
1.0
重新创立空表products,然后从临时表导入：

insert into products select ♦ ftor #products< （所影响的行数为7行）
1:写,个SQL查询来求出为住在Duluth的所有顾客订货的代理商的aid和percent值。按照percent 值从小到大的顺序排列结果中的aid值。
select cid.aid into #orders
fror orders
where cid in ,select cid from customers
where city like 'Duluth'）（所影响的行数为9行）

select aid, percents from agentswhere aid m （select distinct p. aid from #orders o. #orders p where o. aid p. aid and o. cid p. cid
order by percents, aid desc
aid percents1a06 5
2、Employee数据库的查询（记录每个查询的SQL语句和查询结果）（1）向表中插入数据。
Insert into person
values C 00000'田林’，’男’，’1982-4-7', , 初叙,'00「）（所影响的行数为1行）
Insert into salary
values（f 00000r . 2100. 500. 2600. 1）（所影响的行数为1行）
Insert into department values （* 004•采购部'）

（所影响的行数为1行）

001
002
003
004
营销部 财务部 人事部 采购部
£jii_

Pno
Pname
Sex Birthday
Prof Deptno
select * from person
12
34
56
000001田林000002郭达
000003王琳000004王烽
000005罗大佑000006蔡琴
7 000007 李艳000008刘德华
8 000009 黎明男 男 女 男 男 女 女 男 男
1982-04-07 1953-02-14 1984-08-25 1984-05-11 1968-12-10 1978-04-20 1960-05-14 1971-02-19 1973-10-0900:00:00.000 00:00:00.000 00:00:00.000 00:00:00. 000 00:00:00.000 00:00:00.000 00:00:00.000 00:00:00.000 00:00:00.000
初级 高级 初级 初级 高级 中级 高级 高级 中级001
001002
002002
003001
003002

select ♦ from salary

Pno Base
Bonus
Fact
Month
1
000001 2100
500
2600
1
2
000001 2400
600
3000
2
3
000001 2200
500
2700
3
4
000002 3000
600
3600
1
5
000002 2800
400
3200
2
6
000002 3100
400
3500
3
7
000003 2800
450
3250
1
8
000003 2860
350
3210
2
9000003 2800
650
3450
3
10
000004 2500
340
2840
1
11
000004 2540
320
2860
2
12
000004 2500
540
3040
3
13
000005 3200
490
3690
1
14
000005 3200
480
3680
2
15
000005 3200
400
3600
3
16
000006 2700
600
3300
1
17
000006 2700
300
3000
2
18
000006 3000
800
3800
3
19
000007 4000
200
4200
1
20
000007 4000
250
4250
2
21
000007 4100
400
4500
3
22
000008 2800
350
3150
1
23
000008 2800
300
3100
2
24
000008 2000
330
2330
3
25
000009 2760
440
3200
1
26
000009 2760
480
3240
2
21
000009 2760 420
3180
3
（2）将职工编号为000006的员工3月份基本工资增加为3000,奖金增加到800。
Update salary
Set Base 3000. Bonus 800. fact 3800
Where Pno-000006 3000 8003800 3
（3）员工000009已经离开公司，将该员工的数据删除
000006' and f1onth=3〔所影响的行数为1行）
select ♦ from salary where pno = '000006' and month = 3
Base Bonus Fact Month
<
Pno Pname Sex Birthday
Prof Dept no

Delete From person where Pno-* 000009^ select from person
2
3
q
5
6
7
8
000001田林
男
1982-04-07
00:00:00.000
初级
001
000002郭达
男
1953-02-14
00:00:00.000
高级
001
000003王琳
女
1984-08-25
00:00:00.000
初级
002
000004王样
男
1984-05-11
00:00:00.000
初级
002
000005罗大佑
男
1968-12-10
00:00:00.000
高级
002
000006蔡琴
女
1978-04-20
00:00:00.000
中级
003
000007李艳
女
1960-05-14
00:00:00.000
高级
001
000008刘德华
男
1971-02-19
00:00:00.000
高级
003

Pno
Delete From salary
Where Pno=‘000009*
（所影响的行数为3行）
aid1 "a03
[3.51：求出满足条件的所有(cid.aid)对，其中的顾客cid没有通过代理商aid订货，这可以由下面的 Select语句实现：
Select cid, aid from customers c, agents a
Where not exists ( select * from orders x
Where x.cid = c.cid and x.aid = a.aid
用NOTIN谓词和一个查洵连用的形式来代替NOT EXISTS谓词形式可以实现此查询吗？使用NOT IN谓词和多个子查询的形式可以吗？解释答案并通过运行来展示所有的等价形式。
形式1：
c002 a01
c002 a02
c002 a04
c302 a06
c003 aOl
c003 a02
c003 a04
c003 a05
c003 a05
c004 aOl
cO04 a02
c004 a03
c004 aOl
c004 a05
c006 a02
c006 a04
c006 a05 [3.8]a：写一个不含WHERE子句的Select语句来检索所有顾客的cid值以及每个顾客在每样产品上 的最大花费。将结果表中的列记为：cid, MAXSPENTo