SQL server中的表数据的操作(二)
实验四 表数据的操作（二）实验报告姓名：张娜 成绩：___________【实验目的】1、掌握用企业管理器完成表数据的检索的方法。2、掌握用Transact-SQL 语言完成表数据的检索的方
实验四 表数据的操作（二）
实验报告
姓名：张娜 成绩：___________
【实验目的】
1、掌握用企业管理器完成表数据的检索的方法。
2、掌握用Transact-SQL 语言完成表数据的检索的方法。
3、掌握视图的定义和操作
【实验内容】
2、用企业管理器完成查询2006年以来的订单号（OrderNo ）, 订购日期（OrderDate ）, 交
付日期（DeliveryDate ）和交付地址（AddressofDelivery ），并按内部订单号（InternalOrderNo ）降序排列。（抓图）
3、记录相应的SQL 语句
（1）查询表Orders 表的全部信息。
select *from orders
（2）查询表Products 表中所有商品的名称（Name ），许可证代码（License Code）和单价
（Price ）。
select Name,licensecode,price from products
（3）查询表Order Details中订购数量（QtyOrdered ）不超过10的不重复订单号。 select distinct orderno from orderdetails where qtyordered<10
（4）查询表Products 表中单价在2500到5000，类别（Category ）为A2，且名称中含有
字符’机’的商品的全部信息。
select *from products where price between 2500 and 5000 and category='A2' and name like'机'
（5）计算订单号为1002的订单中各类商品的金额（单价X 数量），并显示订单号、商品名
称和金额，按金额升序排列。
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）查询“云南大学信息学院”2005年度订购的商品名称。
1
,select name from products s1 join orderdetails s2 on s1.productno=s2.productno join orders s3 on s3.orderno=s2.orderno
where addressofdelivery ='云南大学信息学院'and orderdate between '2005-1-1'and'2005-12-12'
（7）查询统计表Order Details中每一订单中订购商品种类数和总订购数量。
select count(orderno) order_elements,sum(qtyordered) order_amout from orderdetails group by orderno
（8）查询所有订单中累计订购数量超过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）查询没有订购过复印机的年份。
select distinct datepart(year,orderdate) from orders where orderno not in(select orderno from orderdetails where productno
in(select productno from products where name='复印机' ))
（10）查询至少和1002号订单订购的商品种类相同的订单号。
select distinct orderno from orderdetails where productno in(select productno from orderdetails where orderno='1002') and orderno!='1002'
4、用企业管理器在Sales 数据库的上创建视图View_order1, 完成显示商品名称、订购日期、
交付日期和订购商量等信息，该视图的定义和结果。（抓图）
5、写SQL 语句在Sales 数据库的上创建视图View_order2，实现统计每年订购的每种商品
的名称，数量，单价和金额的功能。
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、编写查询数据和创建视图的SQL 语句
（1）显示地址在北辰小区的旅游业务员姓名。
Select oper_nm from operator where oper_add like '北辰小区'
（2）显示当年7月到8月间预订旅游的顾客姓名。（用Datepart （）函数）
Select cust_nm from customer where datepart(month,start_dt)=7 or datepart(month,start_dt)=8
（3）显示所有旅游业务员的姓名和email 域名。
（用right （），rtrim()，len()，charindex （）函数）
Select oper_nm,right(oper_email,(len(rtrim(oper_email))-charindex('@',oper_email))) as email 域名 from operator
（4）显示每名能导游的旅游线路数（显示旅游业务员的姓名）。
Select oper_nm,count(*) as lines from operator join cruise on operator.oper_cd=cruise.oper_cd group by operator. oper_nm
（5）显示所有预期收入低于2000000的旅行线路及其预期收入。
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月份以来没有一笔业务的旅游业务员名单。（用子查询）
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）从即日起将目的地城市为北京的旅游线路的座位数增加10个。（用子查询） update cruise_book set tot_seats=tot_seats 10 where cruise_cd=1
（8）创建名为long_cruise的视图，它包含行程大于8天的旅游线的代码、名称、目的城市、 3
,行程和价格。
create view long_cruise
as
select cruise_cd,cruise_nm,des_city,duration,price from cruise where duration>8
（9）通过视图long_cruise修改表cruise 的数据，将所有线路的价格增加15。
update long_cruise set price=price*1.15
（10）删除名为long_cruise的视图。
drop view long_cruise
【实验小结】
数据库是用于内部设计的，查询功能很强大，视图是为了供用户查询的，视图只是个虚表，它不会加快运行速度。
4