sql语言（测试）

--使用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

--------------------------------------------------------------

转载于:https://my.oschina.net/u/586743/blog/69202