Practical SQL Queries for Microsoft SQL Server 2008 R2

Home >
Browse >
Practical SQL Queries for Microsoft SQL Server 2008 R2

Practical SQL Queries for Microsoft SQL Server 2008 R2

Authors: Art Tennick

Published: October 2010

eISBN: 9780071746885 0071746889 | ISBN: 9780071746878

Contents

Acknowledgments

Introduction

Chapter 1 Select: Single Table

Hello World

Select All Columns from a Table

Schema Name

Database Name

Switching Databases

Server Name

Variations on a Theme

Specific Column 1/2

Specific Column 2/2

Column Aliases 1/2

Column Aliases 2/2

Two or More Columns

Concatenating Columns

Adding Strings

Concatenation Failure

Cast and Convert

Date Column

Formatting Dates 1/3

Formatting Dates 2/3

Formatting Dates 3/3

System Date Function

Date Column Calculation

Numeric Column Calculation 1/2

Numeric Column Calculation 2/2

Arithmetic Calculation

Distinct Values

Distinct on Multiple Columns

Top

XML

Nulls 1/3

Nulls 2/3

Nulls 3/3

Case 1/2

Case 2/2

Chapter 2 Where

All Rows in a Table

Top

Where =

Where <>

Where And

Where <> And

Where Or 1/2

Where Or 2/2

Case Sensitivity

Where In

Where Not In

Where and Or 1/3

Where and Or 2/3

Where and Or 3/3

Where Comparing Columns

Where with Numeric Column =

Where with Numeric Column <>

Where with Numeric Column >

Where with Numeric Column >=

Where with Non-numeric Column >=

Where with Numeric Column <

Where with Numeric Column <=

Where with Numeric Column Range 1/3

Where with Numeric Column Range 2/3

Where with Numeric Column Range 3/3

Numeric with Or

Numeric with In

Null Values 1/2

Null Values 2/2

Date Criteria

Wildcards

Left()

Not Like

Unicode Characters

More on Like

Single Character Wildcard

Complex Wildcards 1/3

Complex Wildcards 2/3

Complex Wildcards 3/3

Working with Long Strings 1/2

Working with Long Strings 2/2

Like %

Like %%

Charindex()

Contains 1/3

Contains 2/3

Contains 3/3

Chapter 3 Order By

No Particular Order

Order By

Asc

Desc

Alternative Syntax

Sorting on Two Columns 1/3

Sorting on Two Columns 2/3

Sorting on Two Columns 3/3

Order By with Where

Numeric Sort

Top

Top with Ties

Bottom

Top Percent 1/2

Top Percent 2/2

Column Name

Chapter 4 Select: Multiple Tables

Single Table

How Not to Join Tables 1/3

How Not to Join Tables 2/3

How Not to Join Tables 3/3

How to Join Tables 1/2

How to Join Tables 2/2

Ambiguity Problem

Joining Three Tables

Complex Query 1/2

Complex Query 2/2

Outer Joins

Left Outer Join 1/2

Left Outer Join 2/2

Right Outer Join 1/2

Right Outer Join 2/2

Another Inner Join

Another Left Outer Join

Another Right Outer Join

Creating Mismatch 1/2

Creating Mismatch 2/2

Inner Join

Left Outer Join 1/2

Left Outer Join 2/2

Right Outer Join 1/2

Right Outer Join 2/2

Full Outer Join 1/2

Full Outer Join 2/2

Cleanup 1/2

Cleanup 2/2

Self Join 1/6

Self Join 2/6

Self Join 3/6

Self Join 4/6

Self Join 5/6

Self Join 6/6

Cross Join 1/3

Cross Join 2/3

Cross Join 3/3

Chapter 5 Aggregates

Base Query

Count(*) 1/2

Count(*) 2/2

Count(column) 1/3

Count(column) 2/3

Count(column) 3/3

Min() 1/2

Min() 2/2

Max() 1/5

Max() 2/5

Max() 3/5

Max() 4/5

Max() 5/5

Sum() 1/4

Sum() 2/4

Sum() 3/4

Sum() 4/4

Avg() 1/2

Avg() 2/2

StDev()

Some Statistics

Chapter 6 Select: New Tables

Base Query

Select Into

Testing New Table

Deleting from New Table

Dropping New Table

Creating an Empty Table

Testing New Table

Dropping New Table

Local Temporary Table

Global Temporary Table

Semipermanent Temporary Table

Chapter 7 Except/Intersect/Union

New Table 1/2

New Table 2/2

Inserting Data

Union 1/3

Union 2/3

Union 3/3

Union All

Intersect

Except 1/2

Except 2/2

Chapter 8 Group By

Base Query

Count()

Group By 1/2

Group By 2/2

Having 1/2

Having 2/2

No Aggregation

Grouping on Two Columns

Jumping a Level

Sum() 1/2

Sum() 2/2

Min()

Max()

Avg()

Two Aggregate Functions

Comparing Two Aggregate Functions

Compute

Compute By 1/2

Compute By 2/2

Chapter 9 System Functions

Base Query for String Functions

Lower()

Upper()

Left()

Right()

Charindex()

Replace()

Base Query for Mathematical Functions

Ceiling()

Floor()

Round()

Base Query for Date Functions 1/2

Base Query for Date Functions 2/2

Datepart() 1/5

Datepart() 2/5

Datepart() 3/5

Datepart() 4/5

Datepart() 5/5

Datename() 1/2

Datename() 2/2

New Base Query for Date Functions

Convert()

Datediff()

Dateadd 1/2

Dateadd 2/2

Chapter 10 Subqueries

Where Revision

Subquery In

Subquery Not In

Subquery Exists

Subquery Not Exists

Base Query

Subquery Any

Subquery All 1/2

Subquery All 2/2

Chapter 11 Delete/Insert/Update

Select Into

Truncate Table

Drop Table

Delete

Select Into

Insert Into … Select 1/3

Insert Into … Select 2/3

Insert Into … Select 3/3

Truncate Table with Identity

Delete with Identity 1/2

Delete with Identity 2/2

Delete with Where

Re-creating Base Table

Update

Update with Where 1/2

Update with Where 2/2

Re-creating Base Table

Insert … Values 1/3

Insert … Values 2/3

Insert … Values 3/3

Insert Select

Drop Table

Chapter 12 Views/User-Defined Functions

Select from Tables

Create View

Select from View

Alter View 1/2

Alter View 2/2

Select from View

Insert/Update/Delete View

Drop View

Select Calculation

Create Function

Select Function 1/2

Select Function 2/2

Drop Function

Create Function

Chapter 13 Stored Procedures/Programming

Select

Print

String Variable

Numeric Variable

System Variable 1/2

System Variable 2/2

While 1/3

While 2/3

While 3/3

Return

If … Else 1/6

If … Else 2/6

If … Else 3/6

If … Else 4/6

If … Else 5/6

If … Else 6/6

Case … When … End

Variable in Select

Base Query

Create Proc

Alter Proc

Running a Stored Procedure 1/3

Running a Stored Procedure 2/3

Running a Stored Procedure 3/3

Alter Proc

Variables

Parameters

Passing Parameters

Default Parameter Values

Output Parameter 1/4

Output Parameter 2/4

Output Parameter 3/4

Output Parameter 4/4

Return 1/4

Return 2/4

Return 3/4

Return 4/4

Two Stored Procedures

Try … Catch 1/2

Try … Catch 2/2

Your Last Stored Procedure

Chapter 14 Data Definition Language (DDL) and Data Control Language (DCL)

Create Database

Use

Create Table

Testing the Table

Insert Into … Values

Primary Key

Create Table

Foreign Key

Foreign Key Violation

Insert Into … Values

Create Index

Inner Join

Create View

Create Function

Create Proc

Create Login

Create User

Execute As

Testing Security

Grant

Revoke

Execute Permission

Drop Database

Chapter 15 After You Finish

Where to Use SQL

SSRS

SSIS

DMX

XMLA

Winforms and Webforms

Third-Party Software

Copy and Paste

Index