ch08 query.sql

SQL+Server实用简明教程电子教案和实例源文件

 


USE ElecTravelCom
CREATE TABLE books(
ISBN NVARCHAR(128) NOT NULL,
title NVARCHAR(128) NOT NULL,
pressName NVARCHAR(32) NULL,
page INT NULL,
price DECIMAL(10, 2) NULL,
publishDate DATETIME)




DROP TABLE books
 



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




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




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





USE ElecTravelCom
INSERT INTO books DEFAULT VALUES



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




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

USE ETravelCom
DROP TABLE books




USE ElecTravelCom
SELECT *
FROM books




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





USE ElecTravelCom
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 ElecTravelCom
DELETE FROM books
WHERE DATEDIFF(YEAR, publishDate, 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


USE ElecTravelCom
BULK INSERT ElecTravelCom.dbo.books
FROM 'D:\SQLServerDBData\bookdata.txt'
WITH (
	FIELDTERMINATOR =',',
    ROWTERMINATOR ='\n')
GO






USE AdventureWorks
SELECT 产品编码 = ProductNumber, Name AS 产品名称, SafetyStockLevel SSL
FROM Production.Product





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





SELECT '圆周率值' = PI(), 
       'PI/2的正弦值' = SIN(PI()/2.0), 
       'PI/4的余弦值' = COS(PI()/4.0), 
       'PI/4正切值' = 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 Name, ProductNumber, StandardCost, ListPrice, SafetyStockLevel, ReorderPoint
FROM Production.Product
ORDER BY StandardCost DESC


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 '%Yellow%'








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(*) > 25




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, Amount = SUM(Quantity)
FROM Inventory
GROUP BY ItemName, Color





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







USE AdventureWorks
SELECT ItemName, Color, Amount = 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 ElecTravelCom
CREATE MASTER KEY ENCRYPTION 
BY PASSWORD = '7587987hxJ94467GRafdsaf5y01478dDkjdah####adfafs7r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE BookTitleInfo WITH SUBJECT = 'The book title information'
GO
CREATE SYMMETRIC KEY Book_Title_info WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE BookTitleInfo
GO
ALTER TABLE Books
ADD EncryptedTitle VARBINARY(128) NULL
GO 
OPEN SYMMETRIC KEY Book_Title_info DECRYPTION BY CERTIFICATE BookTitleInfo
GO
UPDATE Books
SET EncryptedTitle = EncryptByKey(Key_GUID('Book_Title_info'), title)
GO
SELECT 未加密前的书名 = title, 
       加密后的书名 = EncryptedTitle, 
       解密后的书名 = CONVERT(NVARCHAR, DecryptByKey(EncryptedTitle)) 
FROM books