ch 06 - retreiving data with select.sql

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

-----------------------------------------------------------
-- SQL Server 2000 Bible 
-- Wiley Publishing 
-- Paul Nielsen

-- Chapter 6  - Retrieving Data with Select

-----------------------------------------------------------
-----------------------------------------------------------
-- Selecting Data from a Single Table

USE CHA2 -- run CHA2_Create to generate the database

SELECT 1

-----------------------------------------------------------
-- Basic Flow of the Select Statement

-- Enable Menu - Query/ Show Execution Plan (Ctlr-K)
SELECT LastName, FirstName, Qualifications
  FROM Guide
  WHERE Qualifications LIKE '%first aid%'
  ORDER BY LastName, FirstName

-- Select Distinct
SELECT ALL Name
  FROM Event
    JOIN Tour 
      ON Event.TourID = Tour.TourID

SELECT DISTINCT Name
  FROM Event
    JOIN Tour 
      ON Event.TourID = Tour.TourID

-- Top 

USE OBXKites

SELECT TOP 3 PERCENT Code, ProductName, Price, 
  CONVERT(VARCHAR(10),EffectiveDate,1) AS PriceDate
  FROM Product
    JOIN Price ON Product.ProductID = Price.ProductID
  ORDER BY Price DESC

SELECT TOP 3 Code, ProductName, Price, 
  CONVERT(VARCHAR(10),EffectiveDate,1) AS PriceDate
  FROM Product
    JOIN Price ON Product.ProductID = Price.ProductID
  ORDER BY Price

--the raw data sorted by price
SELECT Productname, Price 
  FROM Product
    JOIN Price ON Product.ProductID = Price.ProductID
  ORDER BY Price
   
-- Top With Ties

SELECT TOP 3 WITH TIES Code, ProductName, Price, 
  CONVERT(VARCHAR(10),EffectiveDate,1) AS PriceDate
  FROM Product
    JOIN Price ON Product.ProductID = Price.ProductID
  ORDER BY Price
   
-- RowCount

SET RowCount 3

SELECT Code, ProductName, Price, 
  CONVERT(VARCHAR(10),EffectiveDate,1) AS PriceDate
  FROM Product
    JOIN Price ON Product.ProductID = Price.ProductID
  ORDER BY Price

SET RowCount 0 

-- Column aliases
SELECT ProductName AS Product, 
    'abc', 
    ActiveDate + 365 AS OneYearSalesDate
  FROM Product


-----------------------------------------------------------
-- Expressions

-- Modulo operator

SELECT 15%4 AS Modulo, 
  FLOOR(1.25) AS [Floor], 
  CEILING(1.25) AS [Ceiling]

-- String Concatenation

SELECT 123 + 456 AS Addition, 'abc' + 'defg' AS Concatenation

SELECT 'Product: ' + ProductName AS [Order]
  FROM Product

-- Bitwise Operations

-- & And 
SELECT 1 & 1
SELECT 1 & 0 

-- 3 = 011 
-- 5 = 101
-- AND --- 
-- 1 = 001

SELECT 3 & 5

-- | OR 

SELECT 1 | 1
SELECT 1 | 0 

-- 3 = 011 
-- 5 = 101
-- OR  --- 
-- 7 = 111

SELECT 3 | 5

-- Exclusive OR ^

SELECT 1^1

SELECT 1^0

-- Not  ~
DECLARE @A BIT
SET @A = 1
SELECT ~@A

-- Case Expressions

USE OBXKites
SELECT CustomerTypeName,
  CASE [Default] 
    WHEN 1 THEN 'default type'
    WHEN 0 THEN 'possible'
      ELSE '-'
  END AS AssignStatus
  FROM CustomerType 

SELECT 
  CASE
    WHEN 1<0 THEN 'reality is gone.'
    WHEN GETDATE() = '11/30/2005' 
      THEN 'David gets his driver''s license.'
    WHEN 1>0 THEN 'Life is Normal.'
  END AS RealityCheck

-- Complex Boolean Case 
DECLARE @b INT, @q INT

SET @b = 2007
SET @q = 35

Select  CASE 
   WHEN @b = 2007 AND @q BETWEEN 10 AND 30 THEN 1
   ELSE NULL
END AS Test

-----------------------------------------------------------
-- The From Clause

-- From Table [AS] Range Variable
USE CHA2
SELECT G.lastName, G.FirstName
  FROM Guide AS G


-- Object with conflicting SQL keyword name
USE OBXKites
SELECT OrderID, OrderDate
  FROM [Order]

USE Northwind
SELECT OrderID, ProductID, Quantity
  FROM [Order Details]

-- 4 part name 
USE OBXKites
SELECT OrderID, OrderDate
  FROM OBXKites.dbo.[Order]

-----------------------------------------------------------
-- Where Conditions

-- Between

USE CHA2
SELECT Code, DateBegin
  FROM Event
  WHERE DateBegin BETWEEN '07/01/01' AND '07/31/01' 
go
-- end of day 
USE Tempdb

IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'DateTest')
  Drop Table DataTest
go

CREATE TABLE DateTest(
  PK INT IDENTITY,
  OrderDate DATETIME
  )
go
INSERT DateTest(OrderDate)
  VALUES('1/1/01 00:00')
INSERT DateTest(OrderDate)
  VALUES('1/1/01 23:59')
INSERT DateTest(OrderDate)
  VALUES('1/1/01 11:59:59.995 pm')
INSERT DateTest(OrderDate)
  VALUES('1/2/01')

-- this is the last valid time for the day:
SELECT * FROM DateTest
  WHERE OrderDate BETWEEN '1/1/1' AND '1/1/1 11:59:59.998 PM'

-- this query gets adjusted to the next nearest 3 ms:
SELECT * FROM DateTest
  WHERE OrderDate BETWEEN '1/1/1' AND '1/1/1 11:59:59.999 PM'

DROP TABLE DateTest

USE OBXKites
SELECT DISTINCT OrderNumber 
  FROM [Order]
    JOIN OrderDetail
      ON [Order].OrderID = OrderDetail.OrderID
  WHERE Quantity BETWEEN 12 AND 24
   
-- IN
USE CHA2
SELECT Name 
  FROM BaseCamp
  WHERE Region IN ('NC', 'WV') -- note there are no regions in the sample data. 

USE CHA2
SELECT Name 
  FROM BaseCamp
  WHERE Region NOT IN ('NC', 'SC') -- note there are no regions in the sample data.

-- LIKE

USE OBXKites

SELECT ProductName 
  FROM Product 
  WHERE ProductName LIKE  'Air%'

SELECT ProductName 
  FROM Product 
  WHERE ProductName LIKE  '[a-d]%'

-- searching for a string that contains a wildcard character

SELECT Code, ProductName 
  FROM Product 
  WHERE ProductName LIKE '%F[-]15%'

SELECT Code, ProductName 
  FROM Product 
  WHERE ProductName LIKE '%F&-15%' ESCAPE '&'


-- Multiple where conditions

SELECT Code, ProductName 
  FROM Product 
  WHERE ProductName LIKE  'Air%' 
    OR Code BETWEEN '1018' AND '1020'
    AND ProductName LIKE '%G%'

SELECT Code, ProductName 
  FROM Product 
  WHERE ( ProductName LIKE  'Air%' 
    OR Code BETWEEN '1018' AND '1020' ) 
    AND ProductName LIKE '%G%'

-- Where Select 

SELECT 'abc'

SELECT 'abc' WHERE 1>0
go
DECLARE @test NVARCHAR(15)
SET @test = 'z'
SELECT @test = 'abc' WHERE 1<0 -- false so select is not executed
SELECT @test
go
DECLARE @test NVARCHAR(15)
SET @test = 'z'
IF 1<0
  SELECT @test = 'abc'
SELECT @test
-----------------------------------------------------------
-- Order By

USE CHA2

SELECT FirstName, LastName
  FROM Customer
  ORDER BY LastName, FirstName

SELECT LastName + ', ' + FirstName AS FullName
  FROM Customer
  ORDER BY LastName + ', ' + FirstName

SELECT LastName + ', ' + FirstName AS FullName
  FROM Customer
  ORDER BY FullName DESC

SELECT LastName + ', ' + FirstName AS FullName
  FROM Customer
  ORDER BY 1

SELECT * FROM ::fn_helpcollations()

SELECT SERVERPROPERTY('Collation')

ALTER DATABASE Family
  COLLATE SQL_Latin1_General_CP1_CS_AS

SELECT DATABASEPROPERTYEX( 'Family' , 'Collation' )

USE OBXKites
SELECT * 
  FROM Product 
  ORDER BY ProductName
    COLLATE Danish_Norwegian_CI_AI

-----------------------------------------------------------
-- Working with Nulls

SELECT 1 + NULL

IF NULL = NULL
  SELECT '='
ELSE
  SELECT '!='


IF NULL IS NULL
  SELECT 'Is'
ELSE
  SELECT 'Is Not'

-- Is Null
USE CHA2
SELECT FirstName, LastName, Nickname
  FROM Customer
  WHERE NickName IS NULL
  ORDER BY LastName, FirstName

SELECT FirstName, LastName, Nickname
  FROM Customer
  WHERE NickName IS NOT NULL
  ORDER BY LastName, FirstName

-- IsNull()
SELECT FirstName, LastName, ISNULL(Nickname,'none')