大学软件学院-实验报告数据查询.docx-资源下载-咨信网-让知识获取变得高效

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

1、大学软件学院实验报告课程：数据库原理与实用技术实验学期：_任课教师：_专业：软件工程学号：—姓名：成绩：实验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, FileGr

2、owth=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, Qua

3、ntity 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 F

4、act 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 3

5、200 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 3

6、30 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 黎

7、明男 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的部

8、门名和对应的平均工资。 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 #perso

9、n 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.

10、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

11、 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

12、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 <

13、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

14、 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), fo

15、reign 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 f

16、rom 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

17、 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 ord

18、ers 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 c

19、OOl 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

20、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

21、 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

22、 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. 5

23、500... 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 selec

24、t ♦ 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. a

25、id 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）

26、所影响的行数为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 黎明男男女男

27、男女女男男 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

28、 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

29、 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

30、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）将职工编号为0000

31、06的员工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

32、 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

33、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

34、语句实现： 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

邮箱/手机：
温馨提示：	快捷下载时，用户名和密码都是您填写的邮箱或者手机号，方便查询和重复下载（系统自动生成）。如填写123，账号就是123，密码也是123。
特别说明：	请自助下载，系统不会自动发送文件的哦；如果您已付费，想二次下载，请登录后访问：我的下载记录
支付方式：
验证码：	换一换

账号：
密码：
验证码：	换一换
当日自动登录忘记密码？