ch 06 - retreiving data with select.sql

《SQLServer2000宝典》—包括本书中的所有代码

  FROM Customer
  ORDER BY LastName, FirstName

-- Coalesce()
SELECT COALESCE(NULL, 1+NULL, 1+2, 'abc')

-- NullIf()

UPDATE Customer
  SET NickName = ''
  WHERE LastName = 'Adams'

SELECT LastName, FirstName, 
    CASE NickName
      WHEN '' THEN 'blank'
      ELSE Nickname
    END AS NickName,
  NULLIF(NickName,'') AS NickNameNullIf
  FROM Customer
  WHERE LastName IN ('Adams', 'Anderson', 'Andrews')
  ORDER BY LastName, FirstName

-----------------------------------------------------
-- Non-Default Null Behavior 

-- Null Concatenation

-- set database option
EXEC SP_DBOPTION 'CHA2',  CONCAT_NULL_YIELDS_NULL, 'false'
SELECT DATABASEPROPERTYEX('CHA2', 'IsNullConcat')
-- set connection setting
SET CONCAT_NULL_YIELDS_NULL OFF 
-- test
SELECT NULL + 'abc'

-- ANSI SQL 92 Nulls Comparisions

-- set database option
EXEC SP_DBOPTION 'CHA2',  ANSI_NULLS, 'false'
SELECT DATABASEPROPERTYEX('CHA2','IsAnsiNullsEnabled')

-- set connection setting
SET ANSI_NULLS OFF
-- test
SELECT 'true' WHERE (NULL = NULL)

-----------------------------------------------------------
-- Scalar Functions

-- Server Environment Functions
SELECT GETDATE()

SELECT GETDATE() AS 'Date',
  DB_NAME() AS 'Database'

SELECT @@SERVERNAME  

SELECT 
  SERVERPROPERTY ('ServerName') AS ServerName,
  SERVERPROPERTY ('Edition') AS Edition,
  SERVERPROPERTY ('EngineEdition') AS EngineEdition, 
  SERVERPROPERTY ('ProductLevel') AS ProductLevel

-- User Information Functions
SELECT 
  USER_NAME() AS 'User',
  SUSER_SNAME() AS 'Login',
  HOST_NAME() AS 'Workstation',
  APP_NAME() AS 'Application'

-- Date/Time Functions

SELECT DATENAME(YEAR, GETDATE()) AS YEAR

UPDATE Guide 
  SET DateOfBirth = '9/4/58'
  WHERE lastName = 'Frank'

SELECT LastName, 
    DATENAME(yy,DateOfBirth) AS [Year],
    DATENAME(mm,DateOfBirth) AS [Month],
    DATENAME(dd,DateOfBirth) AS [Day],
    DATENAME(WEEKDAY, DateOfBirth) AS BirthDay
  FROM Guide
  WHERE DateOfBirth IS NOT NULL

SELECT DATEPART(DayofYear, GETDATE()) AS DayCount
SELECT DATEPART(dw, GETDATE()) AS DayWeek

-- Date Math

SELECT DATEDIFF(yy,'1984/5/20', GETDATE()) AS MarriedYears,
  DATEDIFF(dd,'1984/5/20', GETDATE()) AS MarriedDays

SELECT DATEADD(hh,100, GETDATE()) AS [100HoursFromNow]

USE Family

SELECT Person.FirstName + ' ' + Person.LastName AS Mother, 
    DATEDIFF(yy, Person.DateOfBirth, 
    Child.DateOfBirth) AS Age,Child.FirstName
  FROM Person
    JOIN Person Child 
      ON Person.PersonID = Child.MotherID 
  ORDER By Age DESC

-- String Functions

SELECT SUBSTRING('abcdefg', 3, 2)

SELECT STUFF('abcdefg', 3, 2, '123')

SELECT STUFF('123456789', 4, 0, '-')
SELECT STUFF(STUFF('123456789', 4, 0, '-'), 7, 0, '-')

SELECT CHARINDEX('c', 'abcdefg', 1) 

SELECT PATINDEX('%[cd]%', 'abdcdefg') 

SELECT RIGHT('Nielsen',2) AS [Right], LEFT('Nielsen',2) AS 'Left'

SELECT LEN('Supercalifragilisticexpialidocious') AS LEN

SELECT RTRIM('   middle earth   ') AS [RTrim], LTRIM('   middle earth   ') AS [LTrim]

Select UPPER('one TWO tHrEe') AS [UpperCase], LOWER('one TWO tHrEe') AS [LowerCase]

-- TitleCase
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'TitleCase')
  DROP FUNCTION TitleCase

go
CREATE FUNCTION TitleCase (
  @StrIn NVARCHAR(1024))
RETURNS NVARCHAR(1024)
AS
  BEGIN
    DECLARE 
      @StrOut NVARCHAR(1024),
      @CurrentPosition INT,
      @NextSpace INT,
      @CurrentWord NVARCHAR(1024),
      @StrLen INT,
      @LastWord BIT

    SET @NextSpace = 1
    SET @CurrentPosition = 1
    SET @StrOut = ''
    SET @StrLen = LEN(@StrIn)
    SET @LastWord = 0

    WHILE @LastWord = 0
      BEGIN 
        SET @NextSpace = CHARINDEX(' ',@StrIn, @CurrentPosition+ 1)
        IF  @NextSpace = 0 -- no more spaces found
          BEGIN
            SET @NextSpace = @StrLen
            SET @LastWord = 1
          END
        SET @CurrentWord = UPPER(SUBSTRING(@StrIn, @CurrentPosition, 1)) 
        SET @CurrentWord = @CurrentWord + LOWER(SUBSTRING(@StrIn, @CurrentPosition+1, @NextSpace - @CurrentPosition))
        SET @StrOut = @StrOut +@CurrentWord
        SET @CurrentPosition = @NextSpace + 1
    END
    RETURN @StrOut
  END
go

SELECT dbo.TitleCase('one TWO tHrEe') AS [TitleCase]

-- Soundex()

USE CHA2
SELECT DISTINCT LastName, SOUNDEX(LastName)
  FROM Customer
  ORDER BY LastName

SELECT SOUNDEX('Nielsen') AS Nielsen, SOUNDEX('Nelson') AS NELSON, SOUNDEX('Neilson') AS NEILSON

USE CHA2

Set statistics time on 
SELECT LastName, FirstName 
  FROM Customer
  WHERE SOUNDEX('Nikolsen') = SOUNDEX(LastName)
    AND LastName LIKE 'N%'

USE OBXKites
SELECT SOUNDEX('Smith')

SELECT LastName, FirstName, SoundexCode 
  FROM Contact
  WHERE SoundexCode = 'S530'

Set statistics time off

USE CHA2
SELECT LastName, DIFFERENCE ('Smith', LastName) AS NameSearch
  FROM Customer
  ORDER BY DIFFERENCE ('Smyth', LastName) DESC

-- Data Type Conversions

SELECT CAST('Away' AS NVARCHAR(5)) AS 'Tom Hanks'

SELECT CAST(123 AS NVARCHAR(15)) AS Int2String

SELECT  GETDATE() AS RawDate,
  CONVERT (NVARCHAR(25), GETDATE(), 100) AS Date100,
  CONVERT (NVARCHAR(25), GETDATE(), 1) AS Date1

SELECT STR(123,5,2) AS [Str]

-- Alternate Date Conversions

DECLARE @MyDate CHAR(8)
SET @MyDate = '102801'
SET @MyDate = '20' + SUBSTRING(@MyDate,5,2) + SUBSTRING(@MyDate,1,2)+ SUBSTRING(@MyDate,3,2) 
PRINT @MyDate
DECLARE @NewDate DATETIME
SET @NewDate = CAST(@MyDate AS DATETIME)
PRINT @NewDate

-----------------------------------------------------------
-- Aggregate Functions

USE OBXKites
SELECT Count(*) 
  FROM dbo.Contact

-- This Query should produce an error...
SELECT LastName, Count(*) 
  FROM dbo.Contact

SELECT SUM(Quantity) AS QuantitySold,
    SUM(Quantity*UnitPrice) AS DollarSold
  FROM dbo.OrderDetail
    JOIN dbo.[Order] 
      ON [Order].OrderID = OrderDetail.OrderID
  WHERE OrderDate Between '1/1/2001' AND '12/31/2001 11:59.998PM'

SELECT DatePart(yy,OrderDate) AS [Year], SUM(Quantity) AS QuantitySold,
    SUM(Quantity*UnitPrice) AS DollarSold
  FROM dbo.OrderDetail
    JOIN dbo.[Order] 
      ON [Order].OrderID = OrderDetail.OrderID
  GROUP BY DatePart(yy,OrderDate)

-- multiple group by columns
SELECT DatePart(yy,OrderDate)  AS [Year], 
    ProductCategoryName, SUM(Quantity) AS QuantitySold,
    SUM(Quantity*UnitPrice) AS DollarSold
  FROM dbo.OrderDetail
    JOIN dbo.[Order] 
      ON [Order].OrderID = OrderDetail.OrderID
    JOIN dbo.Product
      ON OrderDetail.ProductID = Product.ProductID
    JOIN dbo.ProductCategory
      ON Product.ProductCategoryID =
           ProductCategory.ProductCategoryID
  GROUP BY DatePart(yy,OrderDate),ProductCategoryName

-- Polished
SELECT DatePart(yy,OrderDate)  AS [Year], 
    ProductCategoryName, SUM(Quantity) AS QuantitySold,
    SUM(Quantity*UnitPrice) AS DollarSold
  FROM dbo.OrderDetail
    JOIN dbo.[Order] 
      ON [Order].OrderID = OrderDetail.OrderID
    JOIN dbo.Product
      ON OrderDetail.ProductID = Product.ProductID
    JOIN dbo.ProductCategory
      ON Product.ProductCategoryID =
           ProductCategory.ProductCategoryID
    JOIN dbo.Contact
      ON [Order].ContactID = Contact.ContactID
  WHERE Contact.IsEmployee = 0 
  GROUP BY DatePart(yy,OrderDate),ProductCategoryName
  HAVING SUM(Quantity) > 2
  ORDER BY SUM(Quantity*UnitPrice) DESC

-- Vic card example
go
USE Tempdb

IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'Vic')
  DROP TABLE VIC

go
CREATE TABLE Vic (
   VICNumber INT,
   PurchaseDate SMALLDATETIME,
   Amount MONEY
   )
go
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '1/3/2003', 55.24)
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '1/12/2003', 74.24)
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '1/18/2003', 102.24)
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '1/23/2003', 47.24)
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '1/29/2003', 55.24)

INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '2/3/2003', 55.24)
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '2/12/2003', 74.24)
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '2/18/2003', 102.24)
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '2/23/2003', 47.24)
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '2/28/2003', 55.24)

INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '3/3/2003', 75.24)
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '3/12/2003', 64.24)
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '3/18/2003', 62.24)
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '3/23/2003', 67.24)
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '3/29/2003', 65.24)

INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '4/3/2003', 55.24)
INSERT Vic (VICNumber, PurchaseDate, Amount)
  VALUES (123, '4/12/2003', 74.24)

SELECT DISTINCT VICNumber, DatePart(ww,PurchaseDate) AS [Week]
  FROM Vic
  WHERE Amount >=40

SELECT VICNumber AS Winner
     FROM Vic
     WHERE Amount >=40
     GROUP BY VICNumber
     HAVING Count(Distinct DatePart(ww,PurchaseDate)) >= 14

-- Rollup

USE OBXKites
SELECT  
    CASE Grouping(ProductCategoryName) 
      WHEN 0 THEN ProductCategoryName
      WHEN 1 THEN 'All Products' 
    END AS ProductCategory, 
    SUM(Quantity) AS QuantitySold,
    SUM(Quantity*UnitPrice) AS DollarSold
  FROM dbo.OrderDetail
    JOIN dbo.[Order] 
      ON [Order].OrderID = OrderDetail.OrderID
    JOIN dbo.Product
      ON OrderDetail.ProductID = Product.ProductID
    JOIN dbo.ProductCategory
      ON Product.ProductCategoryID =
           ProductCategory.ProductCategoryID
  GROUP BY ProductCategoryName 
    WITH ROLLUP
  ORDER BY ISNULL(ProductCategoryName, 'zzz')