《SQL Server 2008编程入门经典》部分示例程序
前些日子在学这个数据库，顺便把书上的例程输入运行了一些。不全，只有中间几章的。放在这里以供参考。
SELECT *
FROM Person.Person
INNER JOIN HumanResources.Employee
ON Person.Person.BusinessEntityID =
HumanResources.Employee.BusinessEntityID
SELECT Person.BusinessEntity.*, JobTitle
FROM Person.BusinessEntity
INNER JOIN HumanResources.Employee
ON Person.BusinessEntity.BusinessEntityID =
HumanResources.Employee.BusinessEntityID
SELECT pbe.*, hre.BusinessEntityID
FROM Person.BusinessEntity pbe
INNER JOIN HumanResources.Employee hre
ON pbe.BusinessEntityID = hre.BusinessEntityID
SELECT Description
FROM Sales.SpecialOfferProduct ssop
RIGHT OUTER JOIN Sales.SpecialOffer sso
ON ssop.SpecialOfferID = sso.SpecialOfferID
WHERE sso.SpecialOfferID != 1
AND ssop.SpecialOfferID IS NULL
SELECT FirstName + ' ' + LastName AS Name, pe.EmailAddress EmailAddress
FROM Person.Person pp
JOIN Person.EmailAddress pe
ON pp.BusinessEntityID = pe.BusinessEntityID
JOIN Sales.Customer sc
ON pp.BusinessEntityID = sc.CustomerID
UNION
SELECT FirstName + ' ' + LastName AS Name, pe.EmailAddress EmailAddress
FROM Person.Person pp
JOIN Person.EmailAddress pe
ON pp.BusinessEntityID = pe.BusinessEntityID
JOIN Purchasing.Vendor pv
ON pp.BusinessEntityID = pv.BusinessEntityID
SELECT FirstName + ' ' + LastName AS Name, LastName
FROM Person.Person pp
INNER JOIN HumanResources.Employee he
ON pp.BusinessEntityID = he.BusinessEntityID
WHERE he.NationalIDNumber = 112457891;
CREATE DATABASE Accounting
ON
(NAME = 'Accounting',
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AccountingData.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5)
LOG ON
(NAME = 'AccountingLog',
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AccountingLog.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB);
GO
EXEC sp_helpdb 'Accounting'
USE Accounting
CREATE TABLE Customers
(
CustomerNo int IDENTITY NOT NULL,
CustomerName varchar(30) NOT NULL,
Address1 varchar(30) NOT NULL,
Address2 varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
Zip varchar(10) NOT NULL,
Contact varchar(25) NOT NULL,
Phone char(15) NOT NULL,
FedIDNo varchar(9) NOT NULL,
DateInSystem smalldatetime NOT NULL
)
EXEC sp_help Customers
CREATE TABLE Employees
(
EmployeeID int IDENTITY NOT NULL,
FirstName varchar(25) NOT NULL,
MiddleInitial char(1) NULL,
LastName varchar(25) NOT NULL,
Title varchar(25) NOT NULL,
SSN varchar(11) NOT NULL,
Salary money NOT NULL,
PriorSalary money NOT NULL,
LastRaise AS Salary - PriorSalary,
HireData date NOT NULL,
TerminationDate date NULL,
ManagerEmpID int NOT NULL,
Department varchar(25) NOT NULL
)
EXEC sp_help Employees
ALTER DATABASE Accounting
MODIFY FILE
(NAME = Accounting,
SIZE = 100MB)
ALTER TABLE Employees
ADD
PreviousEmployer varchar(30) NULL,
DateOfBirth datetime NULL,
LastRaiseDate datetime NOT NULL DEFAULT '2008-01-01'
USE Accounting
DROP TABLE Customers, Employees
USE master
DROP DATABASE Accounting
USE Accounting
ALTER TABLE Employees
ADD CONSTRAINT PK_EmployeeID
PRIMARY KEY (EmployeeID)
USE Accounting
CREATE TABLE Orders
(
OrderID int IDENTITY NOT NULL
PRIMARY KEY,
CustomerNo int NOT NULL
FOREIGN KEY REFERENCES Customers(CustomerNo),
OrderDate date NOT NULL,
EmployeeID int NOT NULL
);
EXEC sp_helpconstraint Orders
ALTER TABLE Orders
ADD CONSTRAINT FK_EmployeeCreatesOrder
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
INSERT INTO Employees
(
FirstName,
LastName,
Title,
SSN,
Salary,
PriorSalary,
HireData,
ManagerEmpID,
Department
)
VALUES
(
'Billy Bob',
'Boson',
'Head Cook & Bottle Washer',
'123-45-6789',
100000,
80000,
'1990-01-01',
1,
'Cooking and Bottling'
);
ALTER TABLE Employees
ADD CONSTRAINT FK_EmployeeHasManager
FOREIGN KEY (ManagerEmpID) REFERENCES Employees (EmployeeID)
CREATE TABLE OrderDetails
(
OrderID int NOT NULL,
PartNo varchar(10) NOT NULL,
Description varchar(25) NOT NULL,
UnitPrice money NOT NULL,
Qty int NOT NULL,
CONSTRAINT PKOrderDetails
PRIMARY KEY (OrderID, PartNo),
CONSTRAINT FKOrderContainsDetails
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
ON UPDATE NO ACTION
ON DELETE CASCADE
);
INSERT INTO Customers
VALUES
(
'billy Bob''s Shoes',
'123 Main St.',
' ',
'Vancouver',
'WA',
'98685',
'Billy Bob',
'(360) 555-1234',
'931234567',
GETDATE()
);
INSERT INTO Orders
(CustomerNo, OrderDate, EmployeeID)
VALUES
(1, GETDATE(), 1)
INSERT INTO OrderDetails
VALUES
(1, '4X4525', 'This is a part', 25.00, 2),
(1, '0R2400', 'This is another part', 50.00, 2);
DELETE Orders
WHERE OrderID = 1
ALTER TABLE Employees
ADD CONSTRAINT AK_EmployeeSSN
UNIQUE (SSN);
ALTER TABLE Customers
ADD CONSTRAINT CN_CustomerDateInSystem
CHECK
(DateInSystem <= GETDATE() );
INSERT INTO Customers
(CustomerName, Address1, Address2, City, State,
Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
('Customer1', 'Address1', 'Add2', 'MyCity', 'NY',
'55555', 'No Contact', '553-1212', '930984954', '12-31-2049')
CREATE TABLE Shippers
(
ShipperID int IDENTITY NOT NULL
PRIMARY KEY,
ShipperName varchar(30) NOT NULL,
DateInSystem smalldatetime NOT NULL
DEFAULT GETDATE()
);
INSERT INTO Shippers
(ShipperName)
VALUES
('United Parcel Service');
SELECT * FROM Shippers
ALTER TABLE Customers
ADD CONSTRAINT CN_CustomerDefaultDateInSystem
DEFAULT GETDATE() FOR DateInSystem
ALTER TABLE Customers
ADD CONSTRAINT CN_CustomerAddress
DEFAULT 'UNKNOWN' FOR Address1
INSERT INTO Customers
(CustomerName, Address1, Address2, City, State,
Zip, Contact, Phone, FedIDNo, DateInSystem)
VALUES
('MyCust', '123 Anywhere', '', 'Reno', 'NV', 80808,
'Joe Bob', '555-1212', '931234567', GETDATE())
ALTER TABLE Customers
WITH NOCHECK
ADD CONSTRAINT CN_CustomerPhoneNo
CHECK
(Phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
ALTER TABLE Customers
NOCHECK
CONSTRAINT CN_CustomerPhoneNo
EXEC sp_helpconstraint Customers
ALTER TABLE Customers
CHECK
CONSTRAINT CN_CustomerPhoneNo
SELECT DISTINCT sod.ProductID
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE OrderDate = (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader);
SELECT Description
FROM Sales.SpecialOffer sso
WHERE sso.SpecialOfferID != 1
AND sso.SpecialOfferID NOT IN
(SELECT SpecialOfferID FROM Sales.SpecialOfferProduct);
SELECT soh.CustomerID, MIN(soh.OrderDate) AS OrderDate
INTO #MinOrderDates
FROM Sales.SalesOrderHeader soh
GROUP BY soh.CustomerID;
SELECT soh.CustomerID, soh.SalesOrderID, soh.OrderDate
FROM Sales.SalesOrderHeader soh
JOIN #MinOrderDates t
ON soh.CustomerID = t.CustomerID
AND soh.OrderDate = t.OrderDate
ORDER BY soh.CustomerID;
DROP TABLE #MinOrderDates;
SELECT soh1.CustomerID, soh1.SalesOrderID, soh1.OrderDate
FROM Sales.SalesOrderHeader soh1
WHERE soh1.OrderDate = (SELECT MIN(soh2.OrderDate)
FROM Sales.SalesOrderHeader soh2
WHERE soh2.CustomerID = soh1.CustomerID)
ORDER BY CustomerID;
SELECT sc.AccountNumber,
(SELECT MIN(OrderDate)
FROM Sales.SalesOrderHeader soh
WHERE soh.CustomerID = sc.CustomerID)
AS OrderDate
FROM Sales.Customer sc;
SELECT sc.AccountNumber,
ISNULL(CAST((SELECT MIN(OrderDate)
FROM Sales.SalesOrderHeader soh
WHERE soh.CustomerID = sc.CustomerID) AS varchar), 'NEVER ORDERED')
AS OrderDate
FROM Sales.Customer sc;
SELECT DISTINCT sc.AccountNumber, sst.Name
FROM Sales.Customer AS sc
JOIN Sales.SalesTerritory sst
ON sc.TerritoryID = sst.TerritoryID
JOIN
(SELECT CustomerID
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product pp
ON sod.ProductID = pp.ProductID
WHERE pp.Name = 'HL Mountain Rear Wheel') AS dt1
ON sc.CustomerID = dt1.CustomerID
JOIN
(SELECT CustomerID
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product pp
ON sod.ProductID = pp.ProductID
WHERE pp.Name = 'HL Mountain Front Wheel') AS dt2
ON sc.CustomerID = dt2.CustomerID;
SELECT BusinessEntityID, LastName + ', ' + FirstName AS Name
FROM Person.Person pp
WHERE EXISTS
(SELECT BusinessEntityID
FROM HumanResources.Employee hre
WHERE hre.BusinessEntityID = pp.BusinessEntityID);
MERGE 命令
CREATE TABLE Sales.MonthlyRollup
(
Year smallint NOT NULL,
Month tinyint NOT NULL,
ProductID int NOT NULL
FOREIGN KEY
REFERENCES Production.Product(ProductID),
QtySold int NOT NULL,
CONSTRAINT PKYearMonthProductID
PRIMARY KEY
(Year, Month, ProductID)
);
SELECT soh.OrderDate, sod.ProductID, SUM(sod.OrderQty) AS QtySold
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate >= '2007-08-01'
AND soh.OrderDate < '2007-08-02'
GROUP BY soh.OrderDate, sod.ProductID;
MERGE Sales.MonthlyRollup AS smr
USING
(
SELECT soh.OrderDate, sod.ProductID, SUM(sod.OrderQty) AS QtySold
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate >= '2007-08-01' AND soh.OrderDate < '2007-08-02'
GROUP BY soh.OrderDate, sod.ProductID
) AS s
ON (s.ProductID = smr.ProductID)
WHEN MATCHED THEN
UPDATE SET smr.QtySold = smr.QtySold + s.QtySold
WHEN NOT MATCHED THEN
INSERT (Year, Month, ProductID, QtySold)
VALUES (DATEPART(yy, s.OrderDate),
DATEPART(m, s.OrderDate),
s.ProductID,
s.QtySold)
OUTPUT $action,
inserted.Year,
inserted.Month,
inserted.ProductID,
inserted.QtySold,
deleted.Year,
deleted.Month,
deleted.ProductID,
deleted.QtySold;
SELECT *
FROM Sales.MonthlyRollup;
