實(shí)驗(yàn)四 表數(shù)據(jù)的操作（二）實(shí)驗(yàn)報(bào)告姓名：張娜 成績(jī)：___________【實(shí)驗(yàn)?zāi)康摹?、掌握用企業(yè)管理器完成表數(shù)據(jù)的檢索的方法。2、掌握用Transact-SQL 語(yǔ)言完成表數(shù)據(jù)的檢索的方

實(shí)驗(yàn)四 表數(shù)據(jù)的操作（二）

實(shí)驗(yàn)報(bào)告

姓名：張娜 成績(jī)：___________

【實(shí)驗(yàn)?zāi)康摹?/p>

1、掌握用企業(yè)管理器完成表數(shù)據(jù)的檢索的方法。

2、掌握用Transact-SQL 語(yǔ)言完成表數(shù)據(jù)的檢索的方法。

3、掌握視圖的定義和操作

【實(shí)驗(yàn)內(nèi)容】

2、用企業(yè)管理器完成查詢2006年以來的訂單號(hào)（OrderNo ）, 訂購(gòu)日期（OrderDate ）, 交

付日期（DeliveryDate ）和交付地址（AddressofDelivery ），并按內(nèi)部訂單號(hào)（InternalOrderNo ）降序排列。（抓圖）

3、記錄相應(yīng)的SQL 語(yǔ)句

（1）查詢表Orders 表的全部信息。

select *from orders

（2）查詢表Products 表中所有商品的名稱（Name ），許可證代碼（License Code）和單價(jià)

（Price ）。

select Name,licensecode,price from products

（3）查詢表Order Details中訂購(gòu)數(shù)量（QtyOrdered ）不超過10的不重復(fù)訂單號(hào)。 select distinct orderno from orderdetails where qtyordered<10

（4）查詢表Products 表中單價(jià)在2500到5000，類別（Category ）為A2，且名稱中含有

字符’機(jī)’的商品的全部信息。

select *from products where price between 2500 and 5000 and category='A2' and name like'機(jī)'

（5）計(jì)算訂單號(hào)為1002的訂單中各類商品的金額（單價(jià)X 數(shù)量），并顯示訂單號(hào)、商品名

稱和金額，按金額升序排列。

select orderno,name,price*qtyordered as allmoney

from orderdetails s1 join products s2 on s1.productno=s2.productno where orderno='1002' order by allmoney

（6）查詢“云南大學(xué)信息學(xué)院”2005年度訂購(gòu)的商品名稱。

1

select name from products s1 join orderdetails s2 on s1.productno=s2.productno join orders s3 on s3.orderno=s2.orderno

where addressofdelivery ='云南大學(xué)信息學(xué)院'and orderdate between '2005-1-1'and'2005-12-12'

（7）查詢統(tǒng)計(jì)表Order Details中每一訂單中訂購(gòu)商品種類數(shù)和總訂購(gòu)數(shù)量。

select count(orderno) order_elements,sum(qtyordered) order_amout from orderdetails group by orderno

（8）查詢所有訂單中累計(jì)訂購(gòu)數(shù)量超過20的商品名稱。

select name from products where productno in(select productno from orderdetails where orderno

in(select orderno from orderdetails group by orderno having sum(qtyordered)>20))

（9）查詢沒有訂購(gòu)過復(fù)印機(jī)的年份。

select distinct datepart(year,orderdate) from orders where orderno not in(select orderno from orderdetails where productno

in(select productno from products where name='復(fù)印機(jī)' ))

（10）查詢至少和1002號(hào)訂單訂購(gòu)的商品種類相同的訂單號(hào)。

select distinct orderno from orderdetails where productno in(select productno from orderdetails where orderno='1002') and orderno!='1002'

4、用企業(yè)管理器在Sales 數(shù)據(jù)庫(kù)的上創(chuàng)建視圖View_order1, 完成顯示商品名稱、訂購(gòu)日期、

交付日期和訂購(gòu)商量等信息，該視圖的定義和結(jié)果。（抓圖）

5、寫SQL 語(yǔ)句在Sales 數(shù)據(jù)庫(kù)的上創(chuàng)建視圖View_order2，實(shí)現(xiàn)統(tǒng)計(jì)每年訂購(gòu)的每種商品

的名稱，數(shù)量，單價(jià)和金額的功能。

create view product_order2(yeartime,name,amount,price,allmoney)

as

select datepart(year,orderdate),name,qtyordered,price,price*qtyordered from orders s1 join 2

orderdetails s2 on s1.orderno=s2.orderno

join products s3 on s3.productno=s2.productno

select datepart(yy,orderdate),name,qtyordered,price,price*qtyordered from orders s1 join orderdetails s2 on s1.orderno=s2.orderno

join products s3 on s3.productno=s2.productno

select *from product_order2

【思考題】

1、編寫查詢數(shù)據(jù)和創(chuàng)建視圖的SQL 語(yǔ)句

（1）顯示地址在北辰小區(qū)的旅游業(yè)務(wù)員姓名。

Select oper_nm from operator where oper_add like '北辰小區(qū)'

（2）顯示當(dāng)年7月到8月間預(yù)訂旅游的顧客姓名。（用Datepart （）函數(shù)）

Select cust_nm from customer where datepart(month,start_dt)=7 or datepart(month,start_dt)=8

（3）顯示所有旅游業(yè)務(wù)員的姓名和email 域名。

（用right （），rtrim()，len()，charindex （）函數(shù)）

Select oper_nm,right(oper_email,(len(rtrim(oper_email))-charindex('@',oper_email))) as email 域名 from operator

（4）顯示每名能導(dǎo)游的旅游線路數(shù)（顯示旅游業(yè)務(wù)員的姓名）。

Select oper_nm,count(*) as lines from operator join cruise on operator.oper_cd=cruise.oper_cd group by operator. oper_nm

（5）顯示所有預(yù)期收入低于2000000的旅行線路及其預(yù)期收入。

Select cruise_nm ,sum(price)*(sum(tot_seats)-sum(seats_avail))/count(price) total from cruise join cruise_book on

cruise.cruise_cd=cruise_book. cruise_cd group by cruise_nm having sum(price)*(sum(tot_seats)-sum(seats_avail))/count(price)<2000000

（6）顯示自9月份以來沒有一筆業(yè)務(wù)的旅游業(yè)務(wù)員名單。（用子查詢）

select oper_nm from operator where oper_cd not in (select oper_cd from cruise where cruise_cd

in(select distinct cruise_cd from cruise_book where start_dt like '9'))

（7）從即日起將目的地城市為北京的旅游線路的座位數(shù)增加10個(gè)。（用子查詢） update cruise_book set tot_seats=tot_seats 10 where cruise_cd=1

（8）創(chuàng)建名為long_cruise的視圖，它包含行程大于8天的旅游線的代碼、名稱、目的城市、 3

行程和價(jià)格。

create view long_cruise

as

select cruise_cd,cruise_nm,des_city,duration,price from cruise where duration>8

（9）通過視圖long_cruise修改表cruise 的數(shù)據(jù)，將所有線路的價(jià)格增加15。

update long_cruise set price=price*1.15

（10）刪除名為long_cruise的視圖。

drop view long_cruise

【實(shí)驗(yàn)小結(jié)】

數(shù)據(jù)庫(kù)是用于內(nèi)部設(shè)計(jì)的，查詢功能很強(qiáng)大，視圖是為了供用戶查詢的，視圖只是個(gè)虛表，它不會(huì)加快運(yùn)行速度。

4