Module 1: Introduction to Transact-SQL

Best Practice of Transact-SQL

- Avoid using SELECT *, since this will return all columns on a table, if the application layer is not change it could return more columns then desire and produce an error in the system. - Always place a semicolon at the end of your statement ' ; ' - When assigning aliases always used the word [ AS ] to avoid other columns from becoming column headers

SELECT Statement order of execution

1 - FROM, 2 - WHERE, 3 - GROUP BY, 4 - HAVING, 5 - SELECT and 6 - ORDER BY

Review Question 1 You write a query that returns the Name and Price columns from a table named Product in the Production schema. In the resulting rowset, you want the Name column to be named ProductName. Which of the following Transact-SQL statements should you use? 1- SELECT * FROM Product; 2- SELECT ProductName, Price FROM Production.Product; 3- SELECT Name AS ProductName, Price FROM Production.Product; 4- SELECT Name, Price FROM dbo.Product;

3- SELECT Name AS ProductName, Price FROM Production.Product;

The ISNULL function returns?

A specified alternative value for NULL columns and variables. Syntax: ISNULL(column/variable. value)

You can use the AS keyword to specify?

Aliases for columns in the rowset returned by the SELECT statement.

Data Type Conversion Implicit Conversion

Compatible data types can be automatically converted

Some data types are compatible, and values can be implicitly converted between them, However...

Conversion between other data types requires the use of explicit conversion functions.

Data Stored

Data is stored in tables, which may be related to one another through common key fields.

SELECT Statement Structure

Element | Expression | Role SELECT <select list> Defines which columns to return FROM <table source> Defines table(s) to query WHERE <search condition> Filters rows using a predicate GROUP BY <group by list> Arranges rows by groups HAVING <search conditions> Filters groups using a predicate ORDER BY <order by list> Sorts the output

Transact-SQL Data Types

Exact Numeric Approximate Numeric Character Date/Time Binary Other

SELECT statements are written with the following clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. However, the query engine processes the clauses in what order:

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY

What I am looking in the code below; SELECT Name AS Product, ListPrice * 0.9 AS SalePrice FROM Production.Product;

I am looking at Expressions and Aliases

If you need to compare a value to NULL, use the?

IS operator instead of the = operator.

NULL is used to indicate an unknown or missing value. What is NULL not equivalent to?

NULL is not equivalent to zero or an empty string.

The NULLIF function returns?

NULL when a column or variable contains a specified value. Syntax: NULLIF(column/variable, value)

Arithmetic or string concatenation operations involving one or more NULL operands return?

NULL. For example, 12 + NULL = NULL.

Objects in a database are organized?

Objects in a database are organized into schemas.

Data Type Conversion Explicit Conversion

Requires an explicit conversion function - CAST / TRY_CAST - CONVERT / TRY_CONVERT - PARSE / TRY_PARSE - STR

You can specify expressions in the SELECT clause to?

Return the results of calculations.

What is the SELECT statement use for

The SELECT statement is use to retrieve a rowset of data from tables and views in a database.

The COALESCE function returns?

The first non-NULL value in a specified list of columns or variables. Syntax: COALESCE(column/variable1, column/variable2,...)

Fully qualified naming syntax

The fully qualified naming syntax for an object is server_name.database_name.schema_name.object_name, but in most cases you can abbreviate this to schema_name.object_name.

Transact-SQL supports a wide range of data types, which can be broadly categorized as?

They can be categorized as exact numeric, approximate numeric, character, date/time, binary, and other (which includes specialized data types for handling data such as XML and spatial data).

Review Question 3 You write a Transact-SQL query that returns the Cellphone column from the Sales.Customer table. Cellphone is a varchar column that permits NULL values. For rows where the Cellphone value is NULL, your query should return the text 'None'. Select the correct function to complete the following query: SELECT FirstName, LastName, _____(Cellphone, 'None') AS Cellphone FROM Sales.Customer; a) ISNULL b) IFNULL c) IS NULL

a) ISNULL

Review Question 2 You need to retrieve data from a column that is defined as char(1). If the value in the column is a digit between 0 and 9, the query should return it as an integer value. Otherwise, the query should return NULL. Which two functions can you use to accomplish this? a) TRY_CAST b) CAST c) STR d) CONVERT e) TRY_CONVERT

a) TRY_CAST e) TRY_CONVERT

Binary Data Types

binary varbinary image

Character Data Types

char varchar text nchar nvarchar ntext

Other Data Types

cursor hierarchyid sql_variant table timestamp uniqueidentifier xml geography geometry

Date/Time Data Types

date time datetime datetime2 smalldatetime datetimeoffset

Approximate Numeric Data Types

float real

Transact-SQL

is the language used to query data in Microsoft SQL Server and Azure SQL Database.

In the SELECT clause, you can use * to return all columns, but generally you should ______________________.

specify explicit columns

Exact Numeric Data Types

tinyint smallint int bigint bit decimal / numeric numeric money smallmoney