use TSQLFundamentals2008

select *

from Sales.Customers as C

where c.country=N'Spain'

select *

from Sales.Customers as C

where c.country=N'Spain' and exists(select * from Sales.Orders as O where o.custid=C.custid)

select *

from Sales.Customers as C

where c.country=N'Spain' and not exists(select * from Sales.Orders as O where o.custid=C.custid)

select OBJECT_ID('Sales.OrderTotalsByYear')

if OBJECT_ID('Sales.OrderTotalsByYear') is not null  drop view Sales.OrderTotalsByYear

go

create view Sales.OrderTotalsByYear with schemabinding

as

  select

    YEAR(o.orderdate) as orderyear,

    SUM(od.qty) as qty

  from

  Sales.Orders as o

    join Sales.OrderDetails as od on o.orderid=od.orderid

  group by YEAR(o.orderdate)

go

select O1.orderyear, o1.qty,

    (select  SUM(o2.qty) from

      Sales.OrderTotalsByYear as o2

      where o2.orderyear <= o1.orderyear

    ) as runqty

from  Sales.OrderTotalsByYear as O1

order by o1.orderyear

SELECT custid, companyname

FROM Sales.Customers AS C

WHERE custid NOT IN(SELECT O.custid

                    FROM Sales.Orders AS O);

INSERT INTO Sales.Orders

  (custid, empid, orderdate, requireddate, shippeddate, shipperid,

   freight, shipname, shipaddress, shipcity, shipregion,

   shippostalcode, shipcountry)

  VALUES(NULL, 1, '', '',

         '', 1, 123.00, N'abc', N'abc', N'abc',

         N'abc', N'abc', N'abc');

SELECT custid, companyname

FROM Sales.Customers AS C

WHERE custid NOT IN(SELECT O.custid

                    FROM Sales.Orders AS O);

SELECT custid, companyname

FROM Sales.Customers AS C

WHERE  not exists (SELECT *

                    FROM Sales.Orders AS O where C.custid=O.custid);

SELECT custid, companyname

FROM Sales.Customers AS C

WHERE custid NOT IN(SELECT O.custid

                    FROM Sales.Orders AS O where O.custid is NOT null);