实验七视图及SQL数据更新语句

? 将所有由职员Fuller(LastName)签订的订单运费降低10% update Orders

set Freight=Freight*0.9

where EmployeeID=(select EmployeeID from Employees where LastName='Fuller')

? 将所有美国顾客购买的订单单价调高20% update Orderdetails

set UnitPrice=UnitPrice*1.2

where OrderID in (select distinct Orderdetails.OrderID from Orderdetails,Orders,Customers

where Orderdetails.OrderID=Orders.OrderID

Orders.CustomerID=Customers.CustomerID and Customers.Country='USA')

and

? 将订货数量最多的产品的单价上调5元 update Products

set UnitPrice=UnitPrice+5

where ProductID in (select ProductID from Orderdetails group by ProductID having sum(Quantity) >=ALL

(select sum(Quantity) from Orderdetails group by ProductID))

? 删除订单个数最少的职员的信息 delete from Employees

where EmployeeID in (select EmployeeID from Orders group by EmployeeID having count(OrderID) <=ALL

(select count(OrderID) from Orders group by EmployeeID))

? 删除所有没有下订单的顾客信息 delete from Customers where not exists

(select * from Orders where Customers.CustomerID=Orders.CustomerID)

四．讨论、心得

（可写遇到的问题及解决方法，或者对技术的理解等）

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

本次实验都是对于视图的操作，自己对于SLQ语句觉得还不够熟练，还要继续复习！