Querying With T-SQL - 01

Querying with Transact-SQL
Graeme Malcolm | Senior Content Developer,

Microsoft
Geoff Allix | Principal Technologist, Content Master
Meet Your Instructors

Graeme Malcolm | @graeme_malcolm
Senior content developer at Microsoft
Consultant, trainer, and author since SQL Server 4.2
Geoff Allix | @GeoffAllix

Principal Technologist at Content Master
SQL Server specialist consultant, author,
and trainer
Course Topics
Querying with Transact-SQL
01 | Introduction to Transact-SQL
07 | Using Table Expressions
02 | Querying Tables with SELECT
08 | Grouping Sets and Pivoting

Data
03 | Querying Multiple Tables with

Joins
09 | Modifying Data
04 | Using Set Operators
10 | Programming with Transact-SQL
05 | Using Functions and

Aggregating Data
11 | Error Handling and Transactions
06 | Using Subqueries and APPLY
Setting Expectations
Target Audience
Aspiring database professionals
Application developers
Anyone preparing for SQL Server certification exams
Course Materials
Online video presentations
Downloadable labs
Suggested Approach
Complete each module and lab in turn
Engage with fellow students at Born To Learn
Course Lab Environment

Labs are based on the AdventureWorksLT sample
database in Azure SQL Database
Setup instructions are in the Getting Started guide
There is a lab for each module, consisting of:

Challenges based on the techniques discussed in the
module
References to relevant documentation
Suggested solution scripts
DEMO
Using Azure SQL Database
SQL Server Training and Certification

Microsoft Virtual Academy
www.microsoftvirtualacademy.com
Microsoft Official Curriculum

www.microsoft.com/learning
Microsoft Press
www.microsoftpressstore.com
Microsoft Certified Professional Program

www.microsoft.com/learning
Born to Learn
borntolearn.mslearn.net
Click to edit
Master subtitle
style
01 | Introduction to Transact-SQL
Graeme Malcolm | Senior Content Developer,
Microsoft
Geoff Allix | Principal Technologist, Content Master
Module Overview
What is Transact-SQL?
Relational Databases
Schemas and Object Names
SQL Statement Types
The SELECT Statement
Working with Data Types
Working with NULLs
Structured Query Language (SQL)
Developed by IBM in 1970s
Adopted as a standard by ANSI and ISO standards bodies
Widely used in industry
Microsofts implementation is Transact-SQL

Referred to as T-SQL
Query language for SQL Server and Azure SQL Database
SQL is declarative, not procedural

Describe what you want, dont specify steps
Entities are represented as relations (tables), in
which their attributes are represented as domains
(columns)
Most relational databases are normalized, with
relationships defined between tables
through primary
SalesOrderDetail
Customer
OrderI LineItem
ProductI Quantit
and
foreign
keys
D
No
D
y
Customer FirstNam LastNa
ID
me
Dan
Drayton
Aisha
Witt
Rosie
Reeves
SalesOrderHeader
Product
OrderI
D
OrderDa
te
Customer
ID
ProductI
D
Name
ListPrice
1/1/2015
Widget
2.99
1/1/2015
Gizmo
1.79
1/2/2015
Thingyb
3.49

Schemas are namespaces for database objects
Fully-qualified names:
[server_name.][database_name.]
[schema_name.]object_name
Within database context, best practice is to include

schema name:
schema_name.object_name
Sales
Order
Production
Product
Sales.Order
Sales.Customer
Custome
r
Order
Production.Product
Production.Order
SQL Statement Types

Data Manipulation
Language (DML)
Data Definition Language

(DDL)
Data Control Language

(DCL)
Statements for querying

and modifying data:
SELECT
INSERT
UPDATE
DELETE
Statements for defining

database objects:
CREATE
ALTER
DROP
Statements for
assigning security
permissions:
GRANT
REVOKE
DENY
Focus of this
course

Element
5 SELECT
1
2 FROM
3 WHERE
4
GROUP BY
6
HAVING
ORDER BY
Expression
<select list>
Role
Defines which columns to
return
Defines table(s) to query
Filters rows using a predicate
<table source>
<search
condition>
<group by list>
Arranges rows by groups
<search
Filters groups using a
condition>
predicate
SELECT
OrderDate, COUNT(OrderID)
FROM
Sales.SalesOrder
<order
by list>
Sorts the output
WHERE Status = 'Shipped'
GROUP BY OrderDate
HAVING COUNT(OrderID) > 1
ORDER BY OrderDate DESC;
Basic SELECT Query Examples

All columns
SELECT * FROM Production.Product;
Specific columns
SELECT Name, ListPrice
FROM Production.Product;
Expressions and Aliases

SELECT Name AS Product, ListPrice * 0.9 AS SalePrice
FROM Production.Product;
DEMO
Basic SELECT Queries

Transact-SQL Data Types
Exact
Numeric
Approximate
Numeric
Character
Date/Time
Binary
Other
tinyint
float
char
date
binary
cursor
smallint
real
varchar
time
varbinary
hierarchyid
int
text
datetime
image
sql_variant
bigint
nchar
datetime2
table
bit
nvarchar
smalldatetim
e
timestamp
decimal/nume
ric
ntext
datetimeoffs
et
uniqueidenti
fier
numeric
xml
money
geography
smallmoney
geometry

Data Type Conversion
Implicit Conversion
Compatible data types can be automatically converted
Explicit Conversion
Requires an explicit conversion function
CAST / TRY_CAST
CONVERT / TRY_CONVERT
PARSE / TRY_PARSE
STR
DEMO
Converting Data Types
Working with NULLs

NULL Values
NULL represents a missing or unknown value

ANSI behaviour for NULL values:
The result of any expression containing a NULL value is
NULL
2 + NULL = NULL
MyString: + NULL = NULL
Equality comparisons always return false for NULL values

NULL = NULL returns false
NULL IS NULL returns true
Working with NULLs

NULL Functions
ISNULL(column/variable, value)
Returns value if the column or variable is NULL
NULLIF(column/variable, value)
Returns NULL if the column or variable is value
COALESCE (column/variable1, column/variable2,)

Returns the value of the first non-NULL column or variable
in the list
DEMO
Working with NULLs
Introduction to Transact-SQL
SQL Statement Types
Working with NULLs
Lab: Introduction to Transact-SQL
2014 Microsoft Corporation. All rights reserved. Microsoft, Windows, Office, Azure, System Center, Dynamics and other product names are or may be registered
trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of
Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a
commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT
MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

Querying With T-SQL - 01

Uploaded by

Copyright:

Available Formats

Querying With T-SQL - 01

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Querying With T-SQL - 01

Uploaded by

Copyright:

Available Formats

Querying with Transact-SQL

Graeme Malcolm | Senior Content Developer,

Meet Your Instructors

Geoff Allix | @GeoffAllix

07 | Using Table Expressions

02 | Querying Tables with SELECT

08 | Grouping Sets and Pivoting

03 | Querying Multiple Tables with

04 | Using Set Operators

10 | Programming with Transact-SQL

05 | Using Functions and

11 | Error Handling and Transactions

06 | Using Subqueries and APPLY

Course Lab Environment

There is a lab for each module, consisting of:

SQL Server Training and Certification

Microsoft Official Curriculum

Microsoft Certified Professional Program

Microsofts implementation is Transact-SQL

SQL is declarative, not procedural

Schemas and Object Names

Within database context, best practice is to include

SQL Statement Types

Data Definition Language

Data Control Language

Statements for querying

Statements for defining

The SELECT Statement

Basic SELECT Query Examples

Expressions and Aliases

Working with Data Types

Working with Data Types

Working with NULLs

NULL represents a missing or unknown value

Equality comparisons always return false for NULL values

Working with NULLs

COALESCE (column/variable1, column/variable2,)

You might also like