《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;