ch07.sql

SQLServer2005基础教程,清华大学出版社

USE ETravelCom
CREATE TABLE books(
ISBN NVARCHAR(32) NOT NULL,
Title NVARCHAR(128) NOT NULL,
pressName NVARCHAR(32) NULL,
pageNumber INT NULL,
price DECIMAL(10, 2) NULL,
publicateDate DATETIME)

DROP TABLE books
 


USE ETravelCom
INSERT INTO books
VALUES('7-900637-65-6', N'XML编程技术大全', N'清华大学出版社', 776, 95.00, '2006-1-10')


USE ETravelCom
INSERT INTO books
VALUES('7-302-06235-8', N'Web服务高级编程', NULL, 452, 68, DEFAULT)


USE ETravelCom
INSERT INTO books(Title, pressName, ISBN)
VALUES(N'数据库设计与开发教程', N'清华大学出版社', '7-302-09350-9')


USE ETravelCom
INSERT INTO books DEFAULT VALUES


USE ETravelCom
INSERT INTO books(ISBN, Title, price)
SELECT ProductNumber, Name, ListPrice
FROM AdventureWorks.Production.Product


USE AdventureWorks
SELECT ProductNumber, Name, ListPrice
INTO new_table
FROM Production.Product

USE ETravelCom
DROP TABLE books

USE ETravelCom
SELECT *
FROM books


USE ETravelCom
UPDATE books
SET pageNumber = 720, 
    price = 81
WHERE ISBN = '7-302-09350-9'


USE ETravelCom
UPDATE books
SET price = price * 0.9


USE AdventureWorks
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson sp JOIN Sales.SalesOrderHeader so
  ON sp.SalesPersonID = so.SalesPersonID AND so.OrderDate = (SELECT MAX(OrderDate)
                                                             FROM Sales.SalesOrderHeader 
                                                             WHERE SalesPersonID = sp.SalesPersonID)




USE ETravelCom
DELETE FROM books
WHERE DATEDIFF(YEAR, publicateDate, GETDATE()) > 5

USE AdventureWorks
SELECT *
FROM Production.Product

USE AdventureWorks
SELECT ProductNumber, Name, SafetyStockLevel, ReorderPoint, SellStartDate
FROM Production.Product


USE AdventureWorks
SELECT 'The number of', Name, 'product is', ProductNumber
FROM Production.Product


USE AdventureWorks
SELECT 'The num''ber of', Name, 'prod"uct is', ProductNumber
FROM Production.Product

SELECT *
FROM books

BULK INSERT ETravelCom.dbo.books
FROM 'C:\temp\bookdata.txt'
WITH 
	(
      FIELDTERMINATOR =',',
      ROWTERMINATOR ='\n'
    )


USE AdventureWorks
SELECT 'PRODUCT NUMBER' = ProductNumber, Name AS PRODUCTNAME, SafetyStockLevel SSL
FROM Production.Product

USE AdventureWorks
SELECT ProductNumber, Name, SSL = SafetyStockLevel, minSSL = SafetyStockLevel * 0.81, maxSSL = SafetyStockLevel * 1.29
FROM Production.Product

SELECT '圆周率值' = PI(), '正弦值' = SIN(PI()/2.0), '余弦值' = COS(PI()/4.0), '正切值' = TAN(PI()/4.0)

USE AdventureWorks
SELECT 'PRODUCT and ITS NUMBER' = 'The number of ' + UPPER(Name) + ' product is ' + ProductNumber
FROM Production.Product



USE AdventureWorks
SELECT ALL Color
FROM Production.Product


USE AdventureWorks
SELECT DISTINCT Color
FROM Production.Product

USE AdventureWorks
SELECT Name, ProductNumber, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice
FROM Production.Product



USE AdventureWorks
SELECT Name, ProductNumber, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice
FROM Production.Product
ORDER BY SafetyStockLevel



USE AdventureWorks
SELECT Name, ProductNumber, StandardCost, ListPrice, SafetyStockLevel, ReorderPoint
FROM Production.Product
ORDER BY StandardCost DESC, ListPrice, SafetyStockLevel



USE AdventureWorks
SELECT TOP (1) PERCENT WITH TIES Name, ProductNumber, StandardCost, ListPrice, SafetyStockLevel, ReorderPoint
FROM Production.Product
ORDER BY StandardCost DESC


USE AdventureWorks
SELECT Name, ProductNumber, StandardCost, ListPrice, SafetyStockLevel, ReorderPoint
FROM Production.Product
WHERE StandardCost > 2000


USE AdventureWorks
SELECT Name, ProductNumber, StandardCost, ListPrice, SafetyStockLevel, ReorderPoint
FROM Production.Product
WHERE Name LIKE '%Red%'



USE AdventureWorks
SELECT Name, ProductNumber, StandardCost, ListPrice, SafetyStockLevel, ReorderPoint
FROM Production.Product
WHERE Name LIKE '%Ball%' OR StandardCost > 2000



USE AdventureWorks 
SELECT '表中数据量' = COUNT(*), 
       '表中数据量' = COUNT(StandardCost), 
       '表中数据量' = COUNT(DISTINCT StandardCost), 
       '表中数据量' = COUNT(Color), 
       '标准成本的最大值' = MAX(StandardCost), 
       '标准成本的最小值' = MIN(StandardCost), 
       '标准成本的平均值' = AVG(StandardCost), 
       '标准成本的标准偏差' = STDEV(StandardCost), 
       '标准成本的方差' = VAR(StandardCost)
FROM Production.Product



USE AdventureWorks;
SELECT CustomerID, OrderDate, SubTotal, TotalDue
FROM Sales.SalesOrderHeader
ORDER BY OrderDate 
COMPUTE SUM(SubTotal), SUM(TotalDue)



USE AdventureWorks;
SELECT SalesPersonID, CustomerID, OrderDate, SubTotal, TotalDue
FROM Sales.SalesOrderHeader
ORDER BY SalesPersonID, OrderDate 
COMPUTE SUM(SubTotal), SUM(TotalDue) BY SalesPersonID



USE AdventureWorks;
SELECT Color, '颜色相同的产品数量' = COUNT(*), '颜色相同的产品的最大安全库存量' = MAX(SafetyStockLevel)
FROM Production.Product
WHERE Color IS NOT NULL
GROUP BY Color





USE AdventureWorks
SELECT Color, '颜色相同的产品数量' = COUNT(*), '颜色相同的产品的最大安全库存量' = MAX(SafetyStockLevel)
FROM Production.Product
WHERE Color IS NOT NULL
GROUP BY Color WITH CUBE
HAVING COUNT(*) > 35


CREATE TABLE Inventory(
ItemName NCHAR(20),
Color NCHAR(10),
Quantity INT)
INSERT INTO Inventory VALUES(N'自行车', N'红色', 100)
INSERT INTO Inventory VALUES(N'自行车', N'黑色', 85)
INSERT INTO Inventory VALUES(N'摩托车', N'红色', 62)
INSERT INTO Inventory VALUES(N'摩托车', N'黑色', 170)
INSERT INTO Inventory VALUES(N'汽车', N'白色', 69)


USE AdventureWorks
SELECT ItemName, Color, '数量' = SUM(Quantity)
FROM Inventory
GROUP BY ItemName, Color


USE AdventureWorks
SELECT ItemName, Color, '数量' = SUM(Quantity)
FROM Inventory
GROUP BY ItemName, Color WITH ROLLUP



USE AdventureWorks
SELECT ItemName, Color, '数量' = SUM(Quantity)
FROM Inventory
GROUP BY ItemName, Color WITH CUBE



USE AdventureWorks
SELECT PP.ProductID, Name, ProductNumber, LocationID, Quantity
FROM Production.Product PP CROSS JOIN Production.ProductInventory PPI


USE AdventureWorks
SELECT PP.ProductID, Name, ProductNumber, SafetyStockLevel, ReorderPoint, LocationID, Quantity
FROM Production.Product PP INNER JOIN Production.ProductInventory PPI
  ON PP.ProductID = PPI.ProductID


USE AdventureWorks
SELECT PP.Name, SOD.SalesOrderID
FROM Production.Product PP LEFT OUTER JOIN Sales.SalesOrderDetail SOD
ON PP.ProductID = SOD.ProductID
ORDER BY PP.Name




USE AdventureWorks
SELECT SalesOrderID, 
       OrderDate, 
       DueDate, 
       ProductAmount = (SELECT COUNT(*) FROM Sales.SalesOrderDetail SSOD WHERE SSOD.SalesOrderID = SSOH.SalesOrderID)
FROM Sales.SalesOrderHeader SSOH
WHERE SalesOrderID IN (SELECT TOP (3) SalesOrderID
                       FROM Sales.SalesOrderDetail
                       GROUP BY SalesOrderID
                       ORDER BY COUNT(SalesOrderID) DESC)


CREATE TABLE T1(col1 INT)
CREATE TABLE T2(col1 INT)
INSERT INTO T1 VALUES(1)
INSERT INTO T1 VALUES(2)
INSERT INTO T1 VALUES(3)
INSERT INTO T2 VALUES(2)
INSERT INTO T2 VALUES(5)

SELECT * FROM T1
SELECT * FROM T2


SELECT col1 FROM T1
EXCEPT
SELECT col1 FROM T2






USE AdventureWorks;
WITH AmountOrder(SalesPersonID, AmountOrderOfPerson, MaxDate)
AS
(	SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
	FROM Sales.SalesOrderHeader
	WHERE SalesPersonID IS NOT NULL
	GROUP BY SalesPersonID)
SELECT SalesPersonID, AmountOrderOfPerson, MaxDate
FROM AmountOrder
ORDER BY SalesPersonID






USE AdventureWorks;
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(   SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e INNER JOIN DirectReports d
      ON e.ManagerID = d.EmployeeID)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports



USE AdventureWorks
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5, [261] AS Emp6
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233], [261] )
) AS pvt
ORDER BY VendorID


SELECT NationalIDNumber 
    FROM HumanResources.Employee;





USE AdventureWorks;
GO

--If there is no master key, create one now 
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
GO

-- Create a column in which to store the encrypted data
ALTER TABLE test_encrypt_data
    ADD EncryptedNationalIDNumber varbinary(128); 
GO

-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric 
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE test_encrypt_data
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the 
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber 
    AS "Encrypted ID Number",
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) 
    AS "Decrypted ID Number"
    FROM test_encrypt_data;
GO



SELECT *
FROM books

DELETE FROM books WHERE price = 95


USE ETravelCom
SELECT *
FROM books

USE ETravelCom
CREATE MASTER KEY ENCRYPTION 
BY PASSWORD = '7587987hxJ94467GRafdsaf5y01478dDkjdah####adfafs7r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE BookTitleInfo
WITH SUBJECT = 'The book title information',
     START_DATE = '12/12/2006'
GO
CREATE SYMMETRIC KEY Book_Title_info
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE BookTitleInfo
GO
ALTER TABLE Books
ADD EncryptedTitle NVARCHAR(128) NULL
GO 
OPEN SYMMETRIC KEY Book_Title_info
DECRYPTION BY CERTIFICATE BookTitleInfo
UPDATE Books
SET EncryptedTitle = EncryptByKey(Key_GUID('Book_Title_info'), Title)
GO
SELECT Title, EncryptedTitle
FROM books