《SQL Server 2008编程入门经典》部分示例程序

深碍√TFBOYSˉ_ 2021-12-04 06:03 253阅读 0赞

前些日子在学这个数据库，顺便把书上的例程输入运行了一些。不全，只有中间几章的。放在这里以供参考。

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;

转载于:https://www.cnblogs.com/cql/archive/2013/03/23/2977312.html