sql_day2

------------第二天-----------
-----查询被订购次数最多的产品------
select * from Products where ProductId in
(
select top 1 ProductId from [Order Details]
group by ProductId
order by count(*) desc
)

--------------销售业绩最佳的员工--------
select * from Employees
where EmployeeId in
(
select EmployeeId from Orders
where OrderID in
(
select top 1 OrderId
from [Order Details]
group by OrderId
order by sum(UnitPrice*Quantity*(1-Discount)) desc
)
)

-----销售业绩排在前三位的员工-------
select * from Employees
where EmployeeId in
(
select EmployeeId from Orders
where OrderID in
(
select top 3 OrderId
from [Order Details]
group by OrderId
order by sum(UnitPrice*Quantity*(1-Discount)) desc
)
)

---------------------------------------------------
---------------------------------------------------
-------游标的申明和使用----------------------------
----声明游标----
declare CategoryCursor cursor
for
-----为什么而申请---
    select CategoryName from Categories
   
--------打开游标-----
    open CategoryCursor
------申明变量用于提取数据
    declare @CategoryName nvarchar(15)
-------首次提取数据-提取数据烈性必须与变量类型一样----
    fetch next from CategoryCursor into @CategoryName
    while(@@fetch_status = 0) 
    begin
        print @CategoryName
        fetch next from CategoryCursor into @CategoryName   
    end
------关闭和清除游标------
    close CategoryCursor
    deallocate CategoryCursor
   
------游标应用-----游标不能乱用,十分耗资源--------------------
declare CategoryCursor cursor
for
   select CategoryId from Categories
   open CategoryCursor
   declare @CategoryId int
   fetch next from CategoryCursor into @CategoryId 
  
   while(@@fetch_status=0)
   begin
     select * from Products where CategoryId = @CategoryId
     fetch next from CategoryCursor into @CategoryId
   end
   close CategoryCursor
   deallocate CategoryCursor
  
  
  
-------可滚动-----------找出每类产品低于平均的产品--------

declare ProductCursor cursor scroll
for
   select ProductName from Products
   open ProductCursor
   declare @ProductName varchar(50)
   ----从第一个开始的第十个-------
   fetch absolute 10 from ProductCursor into @ProductName
   print @ProductName
 ------从现在开始的第十个---- 
   fetch relative 10 from ProductCursor into @ProductName
   print @ProductName
  
   close ProductCursor
   deallocate ProductCursor
  
 
 
 
---------------------------------------------------------------
declare @n int, @i int
set @n = 101
set @i =2
while(@i<= sqrt(@n))
begin
   if(@n % @i = 0)
      begin
        print '是合数'
        return
      end
   set @i = @i + 1
end

print '是质数'

------------------------------------------------------
declare @oldPrice money
select @oldPrice = UnitPrice from Products where ProductId = 2
--------------开始一个模块----以便出错以后恢复-----
begin transaction
update Products set UnitPrice = UnitPrice*(1+0.15)
where ProductId = 1

declare @newPrice money
select @newPrice = UnitPrice from Products where ProductId = 2

if ((@newPrice - @oldPrice)/@oldPrice > 0.1)
begin
  raiserror('价格调整不超过原来价格的10%%',1,16)
  ---------------撤销更改----回滚--------
  rollback transaction
end