--使用Northwind数据库
use Northwind
--1.检索Customers表中,CompanyName以B(b) 或者F(f)开头的记录。
select * from Customers where CompanyName like 'B%' OR CompanyName like 'F%'
------------------------------------------------------------------------------------------------------
--2.检索Orders表中订单日期order_date为1997年的记录。
select * from Orders where YEAR(OrderDate)=1997
------------------------------------------------------------------------------------------------------
--3.计算Products表每类产品(CategoryID)的平均单位价格(UnitPrice),并起别名AvgPrice。
select CategoryID , AVG(UnitPrice)as AvgPrice from Products group by CategoryID
------------------------------------------------------------------------------------------------------
--4.检索订单明细表(Order Details)表中每个订单号的产品数目、最大数量、最小价格(UnitPrice*Quantity*(1-Discont))。
select sum(Quantity)as 产品数目,MAX(Quantity) as 最大数量,min((UnitPrice*Quantity*(1-Discount)))as 最小价格
from [Order Details] group by OrderID
------------------------------------------------------------------------------------------------------
--5.查询订货日期1997年运费在50到100之间的订单信息。
select * from Orders where year(OrderDate)=1997 and Freight >50 and Freight <100
------------------------------------------------------------------------------------------------------
--6.查询每笔订单明细及其对应的产品名称。
select * , ProductName from [Order Details] s ,Products p where s.ProductID = p.ProductID
------------------------------------------------------------------------------------------------------
--7.查询1997年订单数量超过10的客户信息(有客户ID即可)
select CustomerID from Orders where YEAR(OrderDate) = 1997
group by CustomerID having COUNT(OrderID)>10
------------------------------------------------------------------------------------------------------
--8.统计出每年每个产品的销售数量之和(要求包括年度、产品ID、产品名称、销售数量)。
select YEAR(o.OrderDate),d.ProductID ,p.ProductName ,count(d.Quantity)as 销售数量
from Orders o
join
[Order Details] d
on o.OrderID = d.OrderID
join
Products p
on d.ProductID = p.ProductID
group by p.ProductName,d.ProductID ,YEAR(o.OrderDate)
order by YEAR(o.OrderDate)
-------------------------------------------------------------------------------------------------------
--9.创建一个视图,包含订单信息、订单明细信息、产品信息、客户信息。
-- 创建视图
create view All_View
as
SELECT c.*, o.OrderID, o.EmployeeID, o.OrderDate,o.RequiredDate, o.ShippedDate, o.ShipVia,
o.Freight, o.ShipName, o.ShipAddress, o.ShipCity, o.ShipRegion, o.ShipPostalCode,
o.ShipCountry, p.*, od.Quantity, od.Discount
FROM Orders o
inner join
Customers c
ON o.CustomerID = c.CustomerID
inner join
[Order Details] od
ON o.OrderID = od.OrderID
inner join
Products p
ON od.ProductID = p.ProductID
--查询这个视图
select * from All_View
--------------------------------------------------------------------------------------------------------
--10. 检索订单表(Orders)中每种ShipVia的订单数量,并按Ship名称(在Shippers表)使用以下格式输出:
-- Speedy Express United Package Federal Shipping
-- 订单数量 订单数量 订单数量
select * from
(select COUNT(o.OrderID) as [Speedy Express] from orders o where o.ShipVia = 1) as a
left join
(select COUNT(o.OrderID) as [United Package] from orders o where o.ShipVia = 2) as b
on a.[Speedy Express]!= b.[United Package]
left join
(select COUNT(o.OrderID) as [Federal Shipping] from orders o where o.ShipVia = 3) as c
on a.[Speedy Express]! = c.[Federal Shipping]
---------------------------------------------------------------------------------------------------------
--11.编写一个存储过程,实现分页数据查询,即查询出订单表中的分页数据,按订单时间倒序排列。
-- 要求输入参数为起始号、每页查询数量;
-- 如果有想法,可以考虑在参数中加入查询条件。返回数据即为分页的数据记录。
-- 如通过这个存储过程,可以查询出经倒序排列后从100行开始的10第记录。
-- (注:查询条件即为用户在使用该功能时,额外加入的一些查询条件,如运费、订单日期等)
create procedure sp_myproc(
--查询的页数 ,即查询的事第几页的数据
@page int ,
-- 每页查询数量
@quantity int,
--查询条件 订单年份
@year int
)
as
begin
select top (@quantity) * from Orders where OrderID
not in (select top((@page-1)*@quantity) OrderID
from Orders )and YEAR(OrderDate)=@year
order by OrderDate desc
end
--倒序100行后的10条记录
begin
select top (10) * from Orders where OrderID
not in (select top (100) OrderID
from Orders order by OrderID desc )
order by OrderID desc
end
--执行这个存储过程
execute sp_myproc 3,5 ,1997
--------------------------------------------------------------