SQL SERVER 2005 基本查询（连接查询）

use AdventureWorks
GO
SELECT c.LastName from Person.Contact c;
SELECT * from HumanResources.Employee e
INNER JOIN HumanResources.Employee m
 ON e.ManagerID = m.EmployeeID; n
SELECT ProductID,Name,ProductNumber,ReorderPoint
from Production.Product
where ProductID in( select ProductID from Production.Product where ProductID in (1,2,3) )
if EXISTS( select ProductID from Production.Product where ProductID in (1,2,3) )
 PRINT 'TRUE'
ELSE
 PRINT 'FALSE'
SELECT e.EmployeeID,ce.FirstName,ce.LastName
from HumanResources.Employee e
INNER JOIN HumanResources.Employee m
 ON e.ManagerID = m.EmployeeID
INNER JOIN Person.Contact ce
 ON e.ContactID = ce.ContactID
INNER JOIN Person.Contact cm
 ON m.ContactID = cm.ContactID
WHERE cm.FirstName = 'Jo' and cm.LastName = 'Brown';
Select SalesOrderID,SUM(OrderQty) as sum
from Sales.SalesOrderDetail
where SalesOrderID between 43684 and 43686
group by SalesOrderID
having SUM(OrderQty) > 5
create table UserInfor
(
 UserID int not null,
 FirstName nvarchar(50),
 LastName nvarchar(50)
)
GO
ALTER TABLE UserInfor
 add primary key (UserID)
INSERT INTO UserInfor(UserID,FirstName,LastName)
SELECT e.EmployeeID,ce.FirstName,ce.LastName
from HumanResources.Employee e
INNER JOIN HumanResources.Employee m
 ON e.ManagerID = m.EmployeeID
INNER JOIN Person.Contact ce
 ON e.ContactID = ce.ContactID
INNER JOIN Person.Contact cm
 ON m.ContactID = cm.ContactID
WHERE cm.FirstName = 'Jo' and cm.LastName = 'Brown';
declare @MyTable table
(
 SalesOrderID int,
 CustomerID int
)
INSERT INTO @MyTable(SalesOrderID,CustomerID)
select SalesOrderID,CustomerID
from AdventureWorks.Sales.SalesOrderHeader
where SalesOrderId between 50222 and 50225
select * from @MyTable
create table Shippers
(
 ShipperID int identity not null primary key,
 ShipperName varchar(30) not null,
 Address varchar(20) not null,