实验7SQL练习

1、实验7 sql练习一、实验目的：练习sql数据更新语句。二、实验内容：1、 视图2、 sql数据更新语言练习三、表结构描述3.1 suppliers(供货厂商)代码描述数据类型长度约束条件supplierid供货厂商编号int4主码companyname厂名varchar40contactname联系人名varchar30contacttitle联系人职位varchar30address地址varchar60city城市名varchar15region地区varchar15postalcode邮政编码varchar10country国家varchar15phone电话varchar24fax传

2、真varchar24homepage主页varchar163.2 region（地区）代码描述数据类型长度约束条件regionid地区编号int4主码regiondescription地区描述varchar503.3 products（产品）代码描述数据类型长度约束条件productid产品编号int4主码productname品名varchar40supplierid供货厂商编号int4categoryid所属种类号int4quantityperunit单位数量varchar20unitprice单价float8unitsinstock库存int2unitsonorder定货数int2reo

3、rderlevel修订量int2discontinued是否进行bit13.4 orders（定单）代码描述数据类型长度约束条件orderid定单编号int4主码customerid顾客编号varchar5employeeid职员编号int4orderdate定货日期datetime8requireddate交货日期datetime8shippeddate载运日期datetime8shipvia经由数int4freight运费float8shipname船名varchar40shipaddress地址varchar60shipcity城市varchar15shipregion地区varchar

4、15postalcode邮政编码varchar10shipcountry国籍varchar153.5 order details（定单详细信息）代码描述数据类型长度约束条件orderid定单编号int4主码productid产品编号int4主码unitprice单价float8quantity数量int2discount折扣float43.6 employees（职工）代码描述数据类型长度约束条件employeeid职工编号int4主码lastname姓varchar20firstname名varchar10title头衔varchar30titleofcourtesy性别varchar25b

5、irthdate生日datetime8hiredate受聘日期datetime8address地址varchar60city城市varchar15region地区varchar15postalcode邮政编码varchar10country国籍varchar15homephone住宅电话varchar24extension分机号varchar4photo照片image16notes备注varchar16reportsto直接上级号int4photopath职工照片路径varchar2553.7 customers（顾客）代码描述数据类型长度约束条件customerid顾客编号varchar5主

6、码companyname公司名varchar40contactname联系人名varchar30contacttitle联系人头衔varchar30address地址varchar60city城市varchar15region地区varchar15postalcode邮政编码varchar10country国籍varchar15phone电话varchar24fax传真varchar243.8 oldsuppliers（供应厂商备份表）结构与suppliers表相同四、实验步骤1、 运行sql server服务管理器，确认数据库服务器开始运行。2、 运行企业管理器，以图示方式点击“附加数据库”

7、，恢复db目录下的数据库文件3、 打开查询分析器，选择刚才恢复的数据库exampledb，输入sql指令，获得运行结果。4、 完成以下sql数据更新语句(1) 创建视图v_supplycount,显示供应商编号，以及该供应商供应的产品的品种数(非categoryid)，该视图包含两个字段：supplierid、productcount。create view v_supplycount(supplierid,productcount)asselect supplierid,count(productid)from productsgroup by supplierid(2) 创建视图v_ord

8、ercount，显示顾客编号，顾客所下订单的产品总金额(金额=单价数量折扣)，该视图包含两个字段：customerid，totalfee。create view v_ordercount(customerid, totalfee)asselect customerid,sum(unitprice*quantity*discount)from orders,orderdetails where orders.orderid=orderdetails.orderid group by customerid(3) 通过视图v_supplycount，查出供应产品的品种数最少的供应商编号和供应商名称。

9、select supplierid,companynamefrom supplierswhere supplierid in(select supplieridfrom v_supplycountwhere productcount=(select min(productcount)from v_supplycount) (4) 通过视图v_ordercount，查出所下订单的产品总金额最多的顾客编号和地址。select customerid,address from customers where customerid in(select customerid from v_ordercou

10、nt where totalfee=(select max(totalfee) from v_ordercount)(5) 再region表中，添加一个新的地区：地区编号为5，地区描述为centralinsertinto regionvalues (5,central)(6) 将suppliers表中的所有国家为法国的供应商信息内容添加到oldsuppliers中create table oldsuppliers( supplierid int not null primary key, companyname varchar(50) , contactname varchar(50) , c

11、ontacttitle varchar(50) , address varchar(60) , city varchar(50) , region varchar(50) , postalcode varchar(50) , country varchar(50) , phone varchar(50) , fax varchar(50) , homepage varchar(50) )insertinto oldsuppliersselect * from suppliers where country=france(7) 将所有由职员fuller(lastname)签订的订单运费降低10%

12、update ordersset freight=freight*0.9where employeeid=(select employeeid from employees where lastname=fuller)(8) 将所有美国顾客购买的订单单价调高20%update orderdetailsset unitprice=unitprice*1.2where orderid in (select distinct orderdetails.orderid from orderdetails,orders,customers where orderdetails.orderid=order

13、s.orderid and orders.customerid=customers.customerid and customers.country=usa)(9) 将订货数量最多的产品的单价上调5元update productsset unitprice=unitprice+5where productid in (select productid from orderdetails group by productid having sum(quantity) =all (select sum(quantity) from orderdetails group by productid)(10) 删除订单个数最少的职员的信息delete from employeeswhere employeeid in (select employeeid from orders group by employe