浙江大学城市学院第五次数据库上机作业.doc

实验5 SQL练习一、实验目的：熟悉SQL SERVER数据库的恢复技术。练习SQL查询语句。二、实验内容：1、 数据库恢复2、 ER图练习3、 SQL查询语言练习三、表结构描述3.1 Suppliers(供货厂商)代码描述数据类型长度约束条件SupplierID供货厂商编号INT4主码CompanyName厂名VARCHAR40ContactName联系人名VARCHAR30ContactTitle联系人职位VARCHAR30Address地址VARCHAR60City城市名VARCHAR15Region地区VARCHAR15PostalCode邮政编码VARCHAR10Country国家VARCHAR15Phone电话VARCHAR24Fax传真VARCHAR24HomePage主页VARCHAR163.2 Region（地区）代码描述数据类型长度约束条件RegionID地区编号INT4主码RegionDescription地区描述VARCHAR503.3 Products（产品）代码描述数据类型长度约束条件ProductID产品编号INT4主码ProductName品名VARCHAR40SupplierID供货厂商编号INT4CategoryID所属种类号INT4QuantityPerUnit单位数量VARCHAR20UnitPrice单价FLOAT8UnitsInStock库存INT2UnitsOnOrder定货数INT2ReorderLevel修订量INT2Discontinued是否进行BIT13.4 Orders（定单）代码描述数据类型长度约束条件OrderID定单编号INT4主码CustomerID顾客编号VARCHAR5EmployeeID职员编号INT4OrderDate定货日期DATETIME8RequiredDate交货日期DATETIME8ShippedDate载运日期DATETIME8ShipVia经由数INT4Freight运费FLOAT8ShipName船名VARCHAR40ShipAddress地址VARCHAR60ShipCity城市VARCHAR15ShipRegion地区VARCHAR15PostalCode邮政编码VARCHAR10ShipCountry国籍VARCHAR153.5 OrderDetails（定单详细信息）代码描述数据类型长度约束条件OrderID定单编号INT4主码ProductID产品编号INT4主码UnitPrice单价FLOAT8Quantity数量INT2Discount折扣FLOAT43.6 Employees（职工）代码描述数据类型长度约束条件EmployeeID职工编号INT4主码LastName姓VARCHAR20FirstName名VARCHAR10Title头衔VARCHAR30TitleOfCourtesy性别VARCHAR25BirthDate生日DATETIME8HireDate受聘日期DATETIME8Address地址VARCHAR60City城市VARCHAR15Region地区VARCHAR15PostalCode邮政编码VARCHAR10Country国籍VARCHAR15HomePhone住宅电话VARCHAR24Extension分机号VARCHAR4Photo照片IMAGE16Notes备注VARCHAR16ReportsTo直接上级号INT4Photopath职工照片路径VARCHAR2553.7 Customers（顾客）代码描述数据类型长度约束条件CustomerID顾客编号VARCHAR5主码CompanyName公司名VARCHAR40ContactName联系人名VARCHAR30ContactTitle联系人头衔VARCHAR30Address地址VARCHAR60City城市VARCHAR15Region地区VARCHAR15PostalCode邮政编码VARCHAR10Country国籍VARCHAR15Phone电话VARCHAR24Fax传真VARCHAR243.8 OldSuppliers（供应厂商备份表）结构与Suppliers表相同四、实验步骤1、 运行SQL SERVER服务管理器，确认数据库服务器开始运行。2、 运行企业管理器，以图示方式点击“附加数据库”，恢复db目录下的数据库文件3、 打开查询分析器，选择刚才恢复的数据库exampleDB，输入SQL指令，获得运行结果。4、 完成以下SQL查询语句n 查询顾客表(Customer)中没有设定区域的顾客编号和公司名SQL语句：select CustomerID,CompanyNamefrom Customerswhere not exists(select *from Regionwhere RegionID is NULL);运行结果：n 统计职工表(Employees)中头衔的数量SQL语句：SELECT COUNT(Title)FROM Employees;运行结果：n 查找订单表(Orders)中职员编号为VICTE和WELLI的SQL语句：select OrderID,Freightfrom Orderswhere CustomerID=VICTE or CustomerID=WELLIorder by Freight;运行结果：n 职员生成的订单号和运费，并按照运费的降序排列n 查找产品表(Products)中的平均库存总价(库存总价=单价库存数)SQL语句：select avg(UnitPrice*UnitsInStock)from Products运行结果：n 查找提供产品的各个供应商编号及其供应的产品数量SQL语句：select SupplierID,UnitsOnOrderfrom Products运行结果：n 查找提供产品的各个供应商名称及其供应的产品数量SQL语句：select CompanyName,UnitsOnOrderfrom Suppliers,Productswhere Suppliers.SupplierID=Products.SupplierID;运行结果：n 在订单详细信息表(OrderDetails)中查找每个订单号对应的产品种类超过4种的订单号和产品种类数，并按照产品种类数升序排列SQL语句：select OrderID,count(distinct ProductID)from OrderDetailsGROUP BY OrderIDhaving count(distinct ProductID)4 order by count(distinct ProductID)运行结果：n 查询客户的公司名和它所下订单的订单编号SQL语句：select CompanyName,OrderID from Orders,Customers where Orders.CustomerID=Customers.CustomerID 运行结果：n 找出所有的职员姓和名以及他的直接上级的姓和名SQL语句：select first.LastName,first.FirstName,second.LastName ReportsToLastName,second.FirstName ReportsToFirstName from Employees first,Employees second where first.ReportsTo=second.EmployeeID运行结果：n 使用外部连接，查找所有的职员的基本信息以及其直接上级的姓、名SQL语句：select first.*,second.LastName ReportsLastName,second.FirstName ReportsFirstName from Employees first LEFT OUTER JOIN Employees second ON (