Northwind数据库练习及参考答案
时间：2021-07-01 10:21:17
帮助过：7人阅读
查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、客户ID和雇员ID等字段的值
Create View Orderquery
as
Select OrderDate,OrderID,CustomerID,EmployeeID
from Orders
where OrderDate
Between ‘1996-07-01‘ and ‘1996-07-15‘
Select * from Orderquery
--查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。条件是“地区等于华北”并且“联系人头衔等于销售代表”。
Select SupplierID,CompanyName,Address,City
from suppliers
where Region
=‘华北‘ and ContactTitle
=‘销售代表‘
--查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。其中的一些供应商位于华东或华南地区，另外一些供应商所在的城市是天津
Select SupplierID,CompanyName,Region,City,Phone
from Suppliers
where Region
in (
‘东北‘,
‘华南‘)
or City
=‘天津‘
--查询“Northwind”示例数据库中位于“华东”或“华南”地区的供应商的ID、公司名称、地区、城市和电话字段的值
Select SupplierID,CompanyName,Region,City,Phone
from Suppliers
where Region
in (
‘东北‘,
‘华南‘)
多表查询
-- 查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等字段的 值，并将查询结果按雇员的“姓氏”和“名字”字段的升序排列，“姓氏”和“名字”值相同的记录按“订单 ID”的降序排列
Create procedure orderquery2
@StartOrderDate datetime=‘1998-01-02 00:00:00.000‘,
@EndOrderDate datetime=‘1998-01-31 23:59:59.997‘
with encryption
as
Select Orders.OrderDate,Orders.OrderID,Customers.CompanyName,Employees.LastName,Employees.FirstName
from Orders
join Customers
on Customers.CustomerID
=Orders.CustomerID
join Employees
on Employees.EmployeeID
=Orders.EmployeeID
Where OrderDate
between @StartOrderDate and @EndOrderDate or OrderDate
between ‘1998-01-01 00:00:00.000‘ and ‘1998-01-31 23:59:59.997‘
Order By LastName,FirstName
ASC,OrderID
DESC
execute orderquery2
‘1996-07-01 00:00:00.000‘，
‘1996-07-15 23:59:59.999‘
--查询“10248”和“10254”号订单的订单ID、运货商的公司名称、订单上所订购的产品的名称
Create view orderquery3
as
Select Orders.OrderID,Shippers.CompanyName,ProductName
From Orders
join Shippers
on Shippers.ShipperID
=Orders.ShipVia
join [Order Details]
on [Order Details].OrderID
=Orders.OrderID
join Products
on Products.ProductID
=[Order Details].ProductID
Select * from orderquery3
where OrderID
=10248 or OrderID
=10254
--查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称、数量、单价和折扣
Create view orderquery4
as
Select Orders.OrderID,ProductName,quantity,Products.unitprice
From [Order Details] join Orders
on [Order Details].orderid
=Orders.orderid
join Products
on Products.ProductID
=[Order Details].ProductID
Select * from orderquery3
where OrderID
=10248 or OrderID
=10254
--查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称及其销售金额
Create view orderquery5
as
Select Orders.OrderID,ProductName,Products.unitprice
*quantity
as ‘销售金额‘
From [Order Details] join Orders
on [Order Details].orderid
=Orders.orderid
join Products
on Products.ProductID
=[Order Details].ProductID
Select * from orderquery5
where OrderID
=10248 or OrderID
=10254
综合查询
--查询所有运货商的公司名称和电话
select companyname,phone
from Shippers
--查询所有客户的公司名称、电话、传真、地址、联系人姓名和联系人头衔
select companyname,fax,phone,address,contactname,contacttitle
from customers
--查询单价介于10至30元的所有产品的产品ID、产品名称和库存量
select productid,productname,unitsinstock
from products
where unitprice
between 10 and 30
--查询单价大于20元的所有产品的产品名称、单价以及供应商的公司名称、电话
select productname,unitprice,suppliers.companyname,suppliers.phone
from suppliers
join products
on suppliers.supplierid
=products.supplierid
where unitprice
>20
--查询上海和北京的客户在1996年订购的所有订单的订单ID、所订购的产品名称和数量
select orders.orderid,productname,quantity,city
from [order details] join products
on [order details].productid
=products.productid
join orders
on [order details].orderid
=orders.orderid
join customers
on orders.customerid
=customers.customerid
where city
in(
‘北京‘ ,
‘上海‘)
and
OrderDate between ‘1996-00-00 00:00:00‘ and ‘1996-12-31 23:59:59.999‘
--查询华北客户的每份订单的订单ID、产品名称和销售金额
select orders.orderid,productname,
[order details].unitprice
*quantity
as 销售金额
from [order details] join products
on [order details].productid
=products.productid
join orders
on [order details].orderid
=orders.orderid
join customers
on orders.customerid
=customers.customerid
where region
=‘华北‘
--按运货商公司名称，统计1997年由各个运货商承运的订单的总数量
select companyname,
count(
*)
from shippers
join orders
on shippers.shipperid
=orders.shipvia
where year(orderdate)
=1997
group by companyname
--统计1997年上半年的每份订单上所订购的产品的总数量
select orders.orderid,
sum(quantity)
from [order details] join orders
on [order details].orderid
=orders.orderid
where year(orderdate)
=1997 and month(orderdate)
>=1
and month(orderdate)
<=6
group by orders.orderid
--select * from [order details] join orders
on [order details].orderid
=orders.orderid
where orders.orderid
=10400 and year(orderdate)
=1997
--统计各类产品的平均价格
select categories.categoryname,
avg(unitprice)
from products
join categories
on products.categoryid
=categories.categoryid
group by categories.categoryname
--统计各地区客户的总数量
select count(
*)
from customers
where region
is not null
group by region
Northwind数据库练习及参考答案
标签：参考 null tar 电话 products start asc 练习 count
