数据库实践操作(三)

1:

select top 3 with ties GoodsName,GoodsClassName,SaleUnitPrice
   From Table_Goods a join Table_GoodsClass b
      on a.GoodsClsassID = b.GoodsClassID
           order by SalePrice DESC

2:

 select top 3 Goods,COUNT(*) from table_SaleBillDetail
       group by GoodID
          order by COUNT(*) desc

3:

    select GoodsName,SaleUnitPrice, Type = 
       case
            when SaleNameUnitPrice >3000 then '高档商品'
            when SaleNameUnitPriice between 1000 and 3000 then'中档产品'
            when SaleNameUnitPrice <1000 then'低档产品'
      end
      from Table_Goods a join Table_GoodsClass b
         on a.GoodsClassID = b.GoodsClassID
             where GoodsClassName = '家用电器'

4：

select a.GoodsID, 商品销售类别 =
   case 
       where count(b.GoodsID) >10 then'热销商品'
       where count(b.GoodsID) between 5 and 10 then'一般商品'
       where count(b.GoodsID)<5 then'难销产品'
       else'滞销产品'
   end
   from Table_Goods a left join Table_SaleBillDetail b
       on a.GoodsID = b.GoodsID
            group by a.GoodsID

5

select CName, Address, SaleDate, Quantity, UnitPrice
    into 家用电器销售表
    from Table_Customer a join Table_SaleBill b on a.CardID = b.CardID  
    join Table_SaleBillDetail c on c.SaleBillID = b.SaleBillID
    join Table_Goods d on d.GoodsID = c.GoodsID
    join Table_GoodsClass e on e.GoodsClassID = d.GoodsClassID
    where GoodsClassName = '家用电器'

6

select Cname, Address from Table_Customer
    where Address in( 
                                   select Address from Table_Customer where = '王晓'
                                )
    and Cname != '王晓'

7

select GoodsName, SalePrice from Table_Goods a
   where SaleUnitPrice = (select max(SaleUnitPrice) from Table_Goods)

8

select GoodsName, SaleUnitPrice, from Table_Goods a
    join Table_GoodsClass b on a.GoodsClassID = b.GoodsClassID
   where SaleUnitPrice>(
                                         select AVG(SaleUnitPrice) from Table_Goods c
                                             join Table_GoodsClass d on c.GoodsClassID = d.GoodsClassID
                                             where GoodsClassName = '家用电器'                     
                                               )
                                     and GoodsClassName = '家用电器'

9

select distinct CardID from Table_SaleBill
   where exists(   
                          select * from Table_SaleBillDetail
                             where SaleBillID = Table_SaleBill.SaleBillID
                                  and UnitPrice > 2000
                          )

10

select distinct CardID from Table_SaleBill
    where not exists(
                           select * from Table_SaleBillDetail
                               where SailBillID = Table_SaleBillDetail.SaleBillID
                                    and UnitPrice >2000
                           )

11

select distinct CardID from Table_SaleBill
   where SaleBillID not in(   
                                           select SaleBillID from Table_SaleBillDetail
                                           where UnitPrice >2000
                                          )

12

select CustomerID, Cname
    from (
                select * from Table_SaleBill a
                    join Table_SaleBillDetail b
                   on a.SaleBillID = b.SaleBillID
                   where GoodsID = 'G001'
               ) as T1
     join(
               select * from Table_SaleBill a
                   join Table_SaleBillDetail b
                  on a.SaleBillID = b.SaleBillID
                  where GoodsID = 'G002'
              ) as T2
    on T1.CardID = T2.CardID
    join Table_Customer c on c.CardID = T1.CardID

13

select Cno, CName, Semester, Credit,
    SUM(Credit) over(partition by Semester) as'total',
    AVG(Credit) over(partition by Semester)  as'Avg',
    MIN(Credit) over(partition by Semester) as'Min',
    MAX(Credit) over(partition by Semester) as'Max',
from Course

14

select OrderID 订单号, Producer 产品号, OrderQty 订购数量,
     SUM (OrderQty) over (partition by OrderID) as 总计,
     CAST(
                  1.0*OrderQty/SUM(OrderQty) over (partition by OrderID)
                  *100 as DECIMAL(5,2)
                                         ) as 所占百分比

15

select OrderID, ProuductID, OrderQty,
         RANK( ) over
         (partition by OrderID order by OrderQty DESC) as RANK
         from OrderDetail
         order by OrdderID