use TSQLFundamentals2008;
go
-- 1.返回2007年6月生成的订单
select orderid, orderdate, custid, empid
from Sales.Orders
where orderdate > '20070601' and orderdate < '20070701'
-- 2.返回每个月的最后一天生成的订单
select orderid, orderdate, custid, empid
from Sales.Orders
where MONTH(orderdate) <> MONTH(dateadd(DAY, 1, orderdate))
select orderid, orderdate, custid, empid
from Sales.Orders
where orderdate = DATEADD(month, datediff(month, N'19000131', orderdate), N'19000131')
--3.返回姓氏中包含'a'两次或更多次的雇员
select empid, firstname, lastname
from HR.Employees
where len(lastname) - len(replace(lastname,'a', '')) > 1
--4.返回总价格(单价*数量)大于10000的所有订单,并按总价格排序
select orderid, sum(unitprice*qty) as totalvalue
from Sales.OrderDetails
group by orderid
having sum(unitprice*qty) > 10000
order by totalvalue desc
--5.返回2007年平均运费最高的三个发货国家
select top(3) shipcountry, AVG(freight) as avgfreight
from Sales.Orders
where year(orderdate) = 2007
group by shipcountry
order by avgfreight desc
--6.为每个顾客单独根据订单日期的顺序(用orderid作为附加属性)来计算其订单的行号
select custid, orderdate, orderid, ROW_NUMBER() over(partition by custid order by orderid)
from sales.orders
order by custid asc, orderid asc
--7.构造一个select语句,让它根据每个雇员的友好称谓,而返回其性别.
select empid, firstname, lastname, titleofcourtesy,
case titleofcourtesy
when N'MS.' then N'Female'
when N'Mrs.' then N'Female'
when N'Mr.' then N'Male'
else N'Unknown'
end
as gender
from HR.Employees
--8.返回每个客户的客户ID和所在区域。对输出中的行按区域排序,NULL值排在最后面(在所有非null值之后)
select custid, region
from Sales.Customers
order by
case
when region is null then 1
else 0
end, region asc
第二章 单表查询(4)
最新推荐文章于 2023-02-07 23:50:03 发布