數(shù)據(jù)庫(kù)系統(tǒng)及應(yīng)用第二版課后上機(jī)答案

1、精選優(yōu)質(zhì)文檔-傾情為你奉上數(shù)據(jù)庫(kù)系統(tǒng)及應(yīng)用第二版課后上機(jī)答案實(shí)驗(yàn)章節(jié)：第一，二，三，四，五，七實(shí)驗(yàn)一、實(shí)驗(yàn)?zāi)康模菏煜?shù)據(jù)庫(kù)的基本操作，會(huì)運(yùn)用sql處理問(wèn)題二、實(shí)驗(yàn)內(nèi)容：1.建立數(shù)據(jù)庫(kù) ，2.建立表和數(shù)據(jù)完整性，3.SQL數(shù)據(jù)操作，4.SQL數(shù)據(jù)查詢(xún)，5視圖的定義和操作，7.存儲(chǔ)過(guò)程三.、程序源代碼：實(shí)驗(yàn)一：1. create database test1on(name=test1_dat,filename='d:lydatatest1dat.mdf',size=5MB)log on(name=test1_log,filename='d:lydatatest1log.ld

2、f')2.create database test2onprimary(name=test2_dat1,filename='d:lydatatest2dat1.mdf'),(name=test2_dat2,filename='d:lydatatest2dat2.ndf'),(name=test2_dat3,filename='d:lydatatest2dat3.ndf')log on(name=test2_log1,filename='d:lydatatest2log1.ldf'),(name=test2_log2,fil

3、ename='d:lydatatest2log2.ldf')3. create database test3onprimary(name=test3_dat,filename='d:lydatatest3dat.mdf'),filegroup w1(name=test3_dat1,filename='d:lydatatest3dat1.ndf'),(name=test3_dat2,filename='d:lydatatest3dat2.ndf'),filegroup w2(name=test3_dat3,filename='

4、;e:lydatatest3dat3.ndf'),(name=test3_dat4,filename='e:lydatatest3dat4.ndf'),filegroup w3(name=test3_dat5,filename='f:lydatatest3dat5.ndf'),(name=test3_dat6,filename='f:lydatatest3dat6.ndf')log on(name=test3_log,filename='d:lydatatest3log.ldf')4. alter database tes

5、t1add file(name=new_dat,filename='d:lydatanewdat.ndf',size=5MB)5. alter database test1modify file(name=test1_dat,size=10 MB )6. Drop database test1Drop database test2Drop database test3實(shí)驗(yàn)21建庫(kù)：CREATE DATABASE 訂單管理ON ( NAME = order_dat,FILENAME = 'd:lydataorderdat.mdf',SIZE = 10,MAXSIZ

6、E = 50,FILEGROWTH = 5 )LOG ON( NAME = order_log,FILENAME = 'd:lydataorderlog.ldf',SIZE = 5MB,MAXSIZE = 25MB,FILEGROWTH = 5MB ) 建表： 客戶號(hào) char(8) primary key check(客戶號(hào) like 'A-z%'), 客戶名稱(chēng) varchar(40) not null, 聯(lián)系人 char(8), 地址 varchar(40), 郵政編碼 char(6) check(郵政編碼 like '0-90-90-90-90-9

7、0-9'), 電話 char(12) check(電話 like '0-90-90-90-90-90-90-90-90-90-90-90-9') create table 產(chǎn)品(產(chǎn)品號(hào) char(8) primary key check(產(chǎn)品號(hào) like 'A-zA-z%'),產(chǎn)品名稱(chēng) varchar(40),規(guī)格說(shuō)明 char(40) constraint uni unique,單價(jià) smallmoney constraint dj check(單價(jià)>0)create table 訂購(gòu)單(客戶號(hào) char(8) not null foreign

8、key references 客戶,訂單號(hào) char(8) primary key,訂購(gòu)日期 datetime default getdate()create table 訂單名細(xì)(訂單號(hào) char(8) foreign key references 訂購(gòu)單,序號(hào) tinyint,產(chǎn)品號(hào) char(8) not null foreign key references 產(chǎn)品,數(shù)量 smallint constraint sl check(數(shù)量>0)primary key(訂單號(hào),序號(hào))2.1、先取消唯一性約束：alter table 產(chǎn)品 drop constraint unialter

9、table 產(chǎn)品 alter column 規(guī)格說(shuō)明 varchar(40) 2.2 alter table 訂購(gòu)單 add 完成日期 datetime null2.3 先取消約束 alter table 訂單名細(xì) drop constraint num；ALTER TABLE 訂單名細(xì) ADD CONSTRAINT num CHECK (數(shù)量 >= 0 AND 數(shù)量 <= 1000)alter table 訂單名細(xì) drop constraint num3.1 create index sup_kh_idx on 客戶(客戶名稱(chēng))3.2 create unique index c

10、p_idx on 產(chǎn)品(產(chǎn)品名稱(chēng))3.3由于create table命令中 的primary key 約束將隱式創(chuàng)建聚集索引，且在創(chuàng)建表時(shí)已經(jīng)指定了關(guān)鍵字，則不可以再創(chuàng)建聚集索引3.4create index dd_mx_idx on 訂單名細(xì)(訂單號(hào),序號(hào),數(shù)量 desc)四、實(shí)驗(yàn)數(shù)據(jù)、結(jié)果分析：實(shí)驗(yàn)3客戶表：訂購(gòu)單：訂單名細(xì)：產(chǎn)品：1、insert into 訂單名細(xì) values( 'dd16','32','cp56','150')insert 客戶(客戶號(hào),客戶名稱(chēng) )values ('E20','

11、廣西電子')訂購(gòu)單備份：select* into 訂購(gòu)單備份 from 訂購(gòu)單select * from 訂購(gòu)單備份2、delete from 客戶 where 客戶號(hào)='E10'delete from 客戶 where 客戶號(hào)='E10'3、update 訂單名細(xì)set 數(shù)量=225where 訂單號(hào)='dd13'4、update 訂購(gòu)單set 訂購(gòu)日期='2011-10-11'where 訂單號(hào) in (select 訂單號(hào) from 訂單名細(xì) where 產(chǎn)品號(hào) in (select 產(chǎn)品號(hào) from 產(chǎn)品 wher

12、e 產(chǎn)品名稱(chēng)='MP4')5delete from 訂購(gòu)單where 客戶號(hào) in( select 客戶號(hào) from 客戶 where 客戶名稱(chēng)='華中電子廠')由于語(yǔ)句與 REFERENCE 約束"FK_訂單名細(xì)_訂單號(hào)_145C0A3F"沖突。該沖突發(fā)生于數(shù)據(jù)庫(kù)"訂單管理"，表"dbo.訂單名細(xì)", column '訂單號(hào)'。語(yǔ)句已終止。實(shí)驗(yàn)41、select * from 客戶2 .select distinct 客戶號(hào) from 訂購(gòu)單 where 訂單號(hào) is not null

13、3、Select * from 產(chǎn)品 where 單價(jià)>5004Select * from 產(chǎn)品 where 單價(jià)>200 and 產(chǎn)品名稱(chēng)='MP4'5、select * from 產(chǎn)品 where (單價(jià)>500)and (產(chǎn)品名稱(chēng)='MP7' or 產(chǎn)品名稱(chēng)='HTC手機(jī)')6、select 客戶名稱(chēng) ,聯(lián)系人 ,電話 ,訂單號(hào)from 訂購(gòu)單 ,客戶where 訂購(gòu)單.客戶號(hào)=客戶.客戶號(hào) and 訂購(gòu)日期='2011-10-11 00:00:00.000'7、select 客戶名稱(chēng),聯(lián)系人,電話from

14、 客戶,訂購(gòu)單,訂單名細(xì),產(chǎn)品where (客戶.客戶號(hào)=訂購(gòu)單.客戶號(hào) and 訂購(gòu)單.訂單號(hào)=訂單名細(xì).訂單號(hào) and 訂單名細(xì).產(chǎn)品號(hào)=產(chǎn)品.產(chǎn)品號(hào)) and 產(chǎn)品名稱(chēng)='MP4'8、select * from 訂單名細(xì) where 產(chǎn)品號(hào) in (select 產(chǎn)品號(hào) from 產(chǎn)品 where 產(chǎn)品名稱(chēng)='MP3')9、select * from 訂購(gòu)單where 訂單號(hào) in (select 訂單號(hào) from 訂單名細(xì) where 數(shù)量>200)10、select * from 產(chǎn)品 where 單價(jià)=(select 單價(jià) from 產(chǎn)品 wh

15、ere 產(chǎn)品名稱(chēng)='HTC手機(jī)')11、 select* from 產(chǎn)品 where 單價(jià) between 200 and 350 12、 select * from 客戶 where 客戶名稱(chēng) like'%公司'13、 select * from 客戶 where 客戶名稱(chēng) not like'%電子廠'14、 select * from 產(chǎn)品 order by 單價(jià)15、 select * from 產(chǎn)品 order by 產(chǎn)品名稱(chēng),單價(jià)16、 select COUNT (distinct 產(chǎn)品名稱(chēng)) from 產(chǎn)品17. select sum

16、 (數(shù)量) from 訂單名細(xì) where 產(chǎn)品號(hào) in(select 產(chǎn)品號(hào) from 產(chǎn)品 where 產(chǎn)品名稱(chēng)='MP4')18、 select SUM (數(shù)量*單價(jià)) from 訂單名細(xì),產(chǎn)品 where 訂單名細(xì).產(chǎn)品號(hào) =產(chǎn)品.產(chǎn)品號(hào) and 產(chǎn)品名稱(chēng)='MP4'19、 select count(訂單號(hào)),avg(數(shù)量*單價(jià))from 產(chǎn)品,訂單名細(xì) where 訂單名細(xì).產(chǎn)品號(hào) =產(chǎn)品.產(chǎn)品號(hào)20. SELECT 訂單號(hào),COUNT(訂單名細(xì).產(chǎn)品號(hào)) AS 項(xiàng)目數(shù),SUM( 產(chǎn)品.單價(jià) * 訂單名細(xì).數(shù)量 ) AS 總金額 FROM 訂單名細(xì),

17、產(chǎn)品 WHERe 訂單名細(xì).產(chǎn)品號(hào) = 產(chǎn)品.產(chǎn)品號(hào) GROUP BY 訂單號(hào)21、select 客戶.客戶號(hào),產(chǎn)品.產(chǎn)品號(hào),數(shù)量*單價(jià) as 總金額from 客戶,訂購(gòu)單,訂單名細(xì),產(chǎn)品where 客戶.客戶號(hào)=訂購(gòu)單.客戶號(hào) and 訂購(gòu)單.訂單號(hào)=訂單名細(xì).訂單號(hào) and 訂單名細(xì).產(chǎn)品號(hào)=產(chǎn)品.產(chǎn)品號(hào) and 產(chǎn)品名稱(chēng)='MP3'order by 客戶號(hào) compute max(數(shù)量*單價(jià)),min (數(shù)量*單價(jià)) by 客戶號(hào)22、select 訂單號(hào),avg(數(shù)量*單價(jià))as 平均金額,count(產(chǎn)品.產(chǎn)品號(hào))from 訂單名細(xì),產(chǎn)品where 訂單名細(xì).產(chǎn)品號(hào)=

18、產(chǎn)品.產(chǎn)品號(hào)group by 訂單號(hào) having count(序號(hào))>=223 select 客戶名稱(chēng),聯(lián)系人,電話,訂購(gòu)單.訂單號(hào) from 客戶, 訂單名細(xì),訂購(gòu)單where(客戶.客戶號(hào)= 訂購(gòu)單.客戶號(hào) ) and 訂購(gòu)日期 is null24. select 客戶名稱(chēng),聯(lián)系人,電話,訂單號(hào),訂購(gòu)日期from 客戶,訂購(gòu)單where 客戶.客戶號(hào)=訂購(gòu)單.客戶號(hào) and 訂購(gòu)日期>'2011-10-1'25. select outa.產(chǎn)品號(hào),outa.產(chǎn)品名稱(chēng), outa.規(guī)格說(shuō)明, outa.單價(jià)from 產(chǎn)品 outawhere 單價(jià)= (SELECT

19、 max(單價(jià)) FROM 產(chǎn)品 innera WHERE outa.規(guī)格說(shuō)明= innera.規(guī)格說(shuō)明)26. select * from 客戶 where not exists ( select* from 訂購(gòu)單 where 客戶號(hào)=訂購(gòu)單.客戶號(hào) ) 27. select * from 客戶 where exists ( select* from 訂購(gòu)單 where 客戶號(hào)=訂購(gòu)單.客戶號(hào) ) 28.select * from 產(chǎn)品 where(單價(jià)>any(select 單價(jià)/2 from 產(chǎn)品 where 產(chǎn)品名稱(chēng)='HTC手機(jī)')29.select * fr

20、om 產(chǎn)品 where(單價(jià)>all(select 單價(jià) from 產(chǎn)品 where 產(chǎn)品名稱(chēng)='HTC手機(jī)')30.1. select *from 客戶 cross join 訂購(gòu)單 where 客戶.客戶號(hào)= 訂購(gòu)單.客戶號(hào)30.2 select *from 客戶 inner join 訂購(gòu)單 on 客戶.客戶號(hào)= 訂購(gòu)單.客戶號(hào)30.3 select 客戶.客戶號(hào),客戶名稱(chēng), 聯(lián)系人,地址,郵政編碼,電話 ,訂單號(hào),訂購(gòu)日期 from 客戶 left join 訂購(gòu)單 on 客戶.客戶號(hào)= 訂購(gòu)單.客戶號(hào)30.4 select 客戶.客戶號(hào),客戶名稱(chēng), 聯(lián)系人,地址

21、,郵政編碼,電話 ,訂單號(hào),訂購(gòu)日期 from 客戶 right join 訂購(gòu)單 on 客戶.客戶號(hào)= 訂購(gòu)單.客戶號(hào)30.5 select 客戶.客戶號(hào),客戶名稱(chēng), 聯(lián)系人,地址,郵政編碼,電話 ,訂單號(hào),訂購(gòu)日期 from 客戶 full join 訂購(gòu)單 on 客戶.客戶號(hào)= 訂購(gòu)單.客戶號(hào)實(shí)驗(yàn)51.create view k_hu as select * from 客戶 where 客戶名稱(chēng)='東方電子廠'查詢(xún)：select * from k_hu2.create view ke_h as select 客戶號(hào),客戶名稱(chēng) from 客戶查詢(xún)： select * fro

22、m ke_h3.create view ke_hu as select 聯(lián)系人,電話 from 客戶 where 客戶名稱(chēng)='東方電子廠'查詢(xún)： select * from ke_hu.4.給出地址在廣州有訂購(gòu)產(chǎn)品MP4的客戶名稱(chēng)create view k_kehu as select 客戶名稱(chēng) from 客戶,訂購(gòu)單,訂單名細(xì),產(chǎn)品 where 地址='廣州' and 客戶.客戶號(hào)=訂購(gòu)單.客戶號(hào) and 訂購(gòu)單.訂單號(hào)=訂單名細(xì).訂單號(hào) and 訂單名細(xì).產(chǎn)品號(hào)=產(chǎn)品.產(chǎn)品號(hào) and 產(chǎn)品名稱(chēng)='MP4'查詢(xún) ：select * from k

23、_kehu5 確定哪些客戶名稱(chēng)目前沒(méi)有訂購(gòu)單的視圖create view dd_gg as select * from 客戶 where not exists ( select* from 訂購(gòu)單 where 客戶號(hào)=訂購(gòu)單.客戶號(hào) ) 查詢(xún)： select * from dd_gg6，定義視圖 包含訂單號(hào),產(chǎn)品號(hào),數(shù)量訂單總金額create view d_ming (訂單號(hào),產(chǎn)品號(hào),數(shù)量,總金額)as select 訂單號(hào),訂單名細(xì).產(chǎn)品號(hào),數(shù)量,單價(jià)*數(shù)量 from 訂單名細(xì),產(chǎn)品 where 訂單名細(xì).產(chǎn)品號(hào)=產(chǎn)品.產(chǎn)品號(hào) 查詢(xún)：select *from d_ming2.1 刪除視圖 d

24、rop view <視圖名> 例如 drop view dd_gg插入：對(duì)第五個(gè)視圖進(jìn)行插入insert  dd_gg values ('E33','南華電子','李然','南寧','','2') 第六個(gè)視圖插入 insert d_ming values ('dd23','cp33','160','30000')更新：更新第一個(gè)視圖：update k_hu set 客戶名稱(chēng)='東方電子公司' where 客戶號(hào)='E12'實(shí)驗(yàn)7 CREATE PROCedur