Source Codes

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 1 - Working with Single Table Queries
--
-- Paul
-----------------------------------------------------------


-----------------------------------------------------------
-- Simple SQL statement
SELECT 'SQL Rocks';

-- Toggle Result Pane - ctrl R

-- Logical Flow (PowerPoint)
-- Syntax Flow (BOL)
-- Physical Flow (Query Execution Plan)

-- Switch Default Database
USE CHA2;

SELECT *
FROM Tour;

-- Statement Terminator ;
-- Batch Terminator GO

-----------------------------------------------------------
-- Logical Flow - FROM Clause
/*
SELECT *
FROM
tables(s)
subqueries (as derived tables) AS NamedRangeAlias
common table expressions (CTE)
views
table-valued user-defined functions
distributed data sources (OpenQuery)
XML data sources (XQuery)
*/

go

-- Fully Qualified - Four Part Name

-- SELECT *
-- FROM Server.Database.Schema.Object;

SELECT *
FROM dbo.Tour;
SELECT *
FROM dbo.Tour;


-----------------------------------------------------------
--- Where Clause

/*
SELECT *
FROM data source(s)
WHERE condition
*/

-- a boolean condition
SELECT *
FROM dbo.Tour
WHERE 1 = 1;

-- Comparison Operator
SELECT *
FROM dbo.Tour
WHERE TourID <= 3;

-- Between
SELECT *
FROM dbo.Tour
WHERE TourID BETWEEN 2 AND 4;

SELECT *
FROM dbo.Tour
WHERE TourID >= 2
AND TourID <= 4;

-- In Condition
SELECT *
FROM dbo.Tour
WHERE TourID IN (1, 3); -- (Subquery)

UPDATE dbo.BaseCamp SET Region = 'NC' WHERE Name = 'Ashville';
UPDATE dbo.BaseCamp SET Region = 'NC' WHERE Name = 'Cape Hatteras';
UPDATE dbo.BaseCamp SET Region = 'BA' WHERE Name = 'Freeport';
UPDATE dbo.BaseCamp SET Region = 'FL' WHERE Name = 'Ft Lauderdale';
UPDATE dbo.BaseCamp SET Region = 'WV' WHERE Name = 'West Virginia';

SELECT *
FROM dbo.BaseCamp
WHERE Region IN ('NC', 'WV');

-- Like Condition
/*
multiple characters %
single character _
match in range [ ]
match not in range [^ ]
*/

SELECT *
FROM dbo.BaseCamp
WHERE Name LIKE 'F%';

SELECT *
FROM dbo.Customer
WHERE LastName LIKE '%son';

SELECT *
FROM dbo.Customer
WHERE FirstName LIKE '[ABC]%';

SELECT *
FROM dbo.Customer
WHERE FirstName LIKE '[^ABC]%';

-- Multiple Conditions
SELECT *
FROM dbo.Customer
WHERE
CustomerTypeID = 1
OR
FirstName LIKE 'Dav%'
AND
CustomerID = 14;

SELECT *
FROM dbo.Customer
WHERE
(CustomerTypeID = 1
OR
FirstName LIKE 'Dav%')
AND
CustomerID = 14;

-----------------------------------------------------------
-- Columns, Expressions Scalar Functions

SELECT 'Expression' as Alias, 123 as [Order]

/* Expressions
Numeric constants 1, 2, 3
String literals 'LastName', 'Employee: ', 'Life''s Great!'
Dates '1/6/80', 'Jan 6, 1980', '19800106'
Mathematical operators *, /, % (remainder), +, -(in order of precedence)
String operator (concatenation) +
Bitwise operators and &, or |, exclusive or ^, not ~
Columns LastName, PrimaryKeyID
Case Expressions CASE Column1
WHEN 1 THEN 'on'
ELSE 'off'
END AS Status
Subqueries (Select 3)
User-defined variables @MyVariable
Global variables @@Error
Scalar functions GetDate(), SysUser()
User-defined functions dbo.MyUDF()
*/

-- Addition vs. Concatenation
SELECT 123 + 456 as Addition, '123' + 'abc' as Concatenation


-----------------------------------------------------------
-- CASE Expressions

-- Simple CASE Expression

USE OBXKites

SELECT CustomerTypeName, [Default],
CASE [Default]
WHEN 1 THEN 'default type'
WHEN 0 THEN 'alternate'
ELSE '-'
End as AssignStatus
From dbo.CustomerType

-- Boolean Case Expression
/*

CASE
WHEN BooleanCondition THEN Expression
WHEN BooleanCondition THEN Expression
...
ELSE Expression
END AS Alias
-- from KillTheCursor!
UPDATE dbo.Detail
SET AdjAmount = DWM.Multiplier * ClientType.Multiplier *
CASE
WHEN Formula = 1 AND ExecOverRide = 0
THEN BaseRate * Amount * BaseMultiplier
WHEN (Formula = 1 OR Formula = 3 )AND ExecOverRide = 1
THEN BaseRate * Amount
WHEN Formula = 2
THEN BaseRate * Amount * (SELECT Value
FROM dbo.Variable
WHERE Name = 'AccRate')
WHEN (Formula = 3 AND ExecOverRide = 0)
THEN Amount * BaseMultiplier
END
FROM Detail
JOIN ActionCode
ON Detail.ActionCode = ActionCode.ActionCode
JOIN [Order]
ON [Order].OrderID = Detail.OrderID
JOIN Client
ON [Order].ClientID = Client.ClientID
Join ClientType
ON Client.ClientTypeID = ClientType.ClientTypeID
JOIN DayOfWeekMultiplier DWM
ON CAST(DatePart(dw,[Order].TransDate) as SMALLINT) = DWM.DayOfWeek
WHERE AdjAmount IS NULL

*/

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

SELECT 1 + NULL

-- IS NULL

-- WHERE Expression IS NULL

SELECT CASE
WHEN Null = NULL THEN 'True'
ELSE 'False'
END as '=null'

SELECT CASE
WHEN Null IS NULL THEN 'True'
ELSE 'False'
END as ' IS null'

-- 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

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

-----------------------------------------------------------
-- 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,'2005/10/22', GETDATE()) AS MarriedYears,
DATEDIFF(dd,'2005/10/22', 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

-- Paul's Recommended Date/time code:
-- return datetime - date only / midnight time
SELECT DATEADD(day, DATEDIFF(day, '20040101', CURRENT_TIMESTAMP), '20040101')

-- extract date only as string
SELECT Convert(CHAR(10), CURRENT_TIMESTAMP, 101 )

-- extract time only as string (with seconds)
SELECT substring(Convert(CHAR(19), CURRENT_TIMESTAMP, 120 ),12,8)

-- extract time only as string (without seconds)
SELECT substring(Convert(CHAR(19), CURRENT_TIMESTAMP, 120 ),12,5)

-- 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]

-- 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]


-----------------------------------------------------------
-- Order By

-- Column name or alias
SELECT FirstName, LastName as Last
FROM dbo.Customer
ORDER BY Last, FirstName;

-- Column Expression
SELECT LastName + ', ' + FirstName as name
FROM dbo.Customer
ORDER BY LastName + ', ' + FirstName;

-- Ordinal Position
SELECT *
FROM dbo.Customer
ORDER BY 3, 2;

SELECT *
FROM dbo.Event
ORDER BY DateBegin DESC;

-----------------------------------------------------------
-- Predicates

-- [All] Distinct
SELECT ALL FirstName
FROM dbo.Customer
WHERE FIRSTNAME IN ('David', 'Gary', 'Melissa')
ORDER BY FirstName

SELECT DISTINCT FirstName
FROM dbo.Customer
WHERE FIRSTNAME IN ('David', 'Gary', 'Melissa')
ORDER BY FirstName

-- Top
SELECT TOP 5 *
FROM dbo.Customer
WHERE CustomerTypeID IS NOT NULL
ORDER BY CustomerTypeID
SELECT TOP 5 WITH TIES *
FROM dbo.Customer
WHERE CustomerTypeID IS NOT NULL
ORDER BY CustomerTypeID

Read Users' Comments (0)

0 Response to "Source Codes"

Post a Comment