--1-1 创建辅助表dbo.nums
set nocount on;
use TSQLFundamentals2008;
go

if OBJECT_ID('dbo.nums','u') is not null
drop table dbo.nums
create table dbo.nums(n int not null, constraint PK_N primary key(n))

declare @i as int =1;
begin tran
	while @i<=100000
	begin
		insert into dbo.nums values(@i);
		set @i=@i+1;
	end
commit tran
set nocount off;

--1-2 写一条查询语句把所有雇员记录复制5次
select empid, firstname, lastname, n 
from HR.Employees cross join dbo.nums 
where n < 6

--1-3 写一个查询,为每个雇员和从2009年6月12日至2009年6月16日范围内的每天返回一行.
select empid, DATEADD(day, n-1, N'20090612') as dt
from HR.Employees cross join dbo.nums
where n <= DATEDIFF(day, N'20090612', N'20090616') + 1

--2 返回来自美国的客户,并为每个客户返回其订单总数和商品交易总数量
select c.custid, Count(distinct o.orderid) as numorders,
Sum(od.qty)
from Sales.Customers c join Sales.Orders o
on c.custid=o.custid
join Sales.OrderDetails od
on o.orderid = od.orderid
where c.country = N'USA'
group by c.custid

--3 返回客户及其订单信息,包括没有下过任何订单的客户.
select c.custid, c.companyname, o.orderid, o.orderdate
from Sales.Customers c
left outer join Sales.Orders o
on c.custid = o.custid

--4 返回没有下过订单的客户
select c.custid, c.companyname
from Sales.Customers c
left outer join Sales.Orders o
on c.custid = o.custid
where o.orderid is null

--5 返回在2007年2月12日下过订单的客户,以及他们的订单
select c.custid, c.companyname, o.orderid, o.orderdate
from Sales.Customers c join Sales.Orders o
on c.custid = o.custid and o.orderdate >= N'20070212' and o.orderdate < N'20070213'

--6 返回在2007年2月12日下过订单的客户,以及他们的订单.同时返回2007年2月12日没有下过订单的客户.
select c.custid, c.companyname, o.orderid, o.orderdate
from Sales.Customers c
left join Sales.Orders o
on c.custid = o.custid and o.orderdate >= N'20070212' and o.orderdate < N'20070213'

--7 返回所有的客户的信息,并根据客户是否在2007年2月12日下过订单,再为每个客户返回一列Yes/No值。
select distinct c.custid, c.companyname, 
case 
when o.orderid is null then 'NO'
else 'YES'
end as HasOrderOn20070212
from Sales.Customers c left outer join Sales.Orders o
on c.custid = o.custid and o.orderdate >= N'20070212' and o.orderdate < N'20070213'