1 Querying with Transact-SQLGraeme Malcolm | Senior Content Developer, Microsoft Geoff Allix | Principal Technologist, Content Master
2 Meet Your Instructors Graeme Malcolm | @graeme_malcolmSenior content developer at Microsoft Consultant, trainer, and author since SQL Server 4.2 Geoff Allix Principal Technologist at Content Master SQL Server specialist consultant, author, and trainer
3 Course Topics Querying with Transact-SQL01 | 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
4 Setting Expectations Target Audience Course MaterialsAspiring 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
5 Course Lab EnvironmentLabs 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
6 Using Azure SQL Database
7 SQL Server Training and CertificationMicrosoft Virtual Academy Microsoft Official Curriculum Microsoft Press Microsoft Certified Professional Program Born to Learn borntolearn.mslearn.net
8 01 | Introduction to Transact-SQLGraeme Malcolm | Senior Content Developer, Microsoft Geoff Allix | Principal Technologist, Content Master
9 Module Overview What is Transact-SQL? Relational DatabasesSchemas and Object Names SQL Statement Types The SELECT Statement Working with Data Types Working with NULLs
10 What is Transact-SQL? Structured Query Language (SQL)Developed by IBM in 1970s Adopted as a standard by ANSI and ISO standards bodies Widely used in industry Microsoft’s 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, don’t specify steps
11 Relational Databases 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 and foreign keys SalesOrderDetail OrderID LineItemNo ProductID Quantity 1 3 2 5 Customer CustomerID FirstName LastName 1 Dan Drayton 2 Aisha Witt 3 Rosie Reeves SalesOrderHeader OrderID OrderDate CustomerID 1 1/1/2015 2 3 1/2/2015 Product ProductID Name ListPrice 1 Widget 2.99 2 Gizmo 1.79 3 Thingybob 3.49
12 Schemas and Object NamesSchemas 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 Production Order Customer Product Production.Product Sales.Order Sales.Customer Production.Order
13 SQL Statement Types Focus of this courseData 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
14 The SELECT Statement 5 1 2 3 4 6 Element Expression Role SELECT Defines which columns to return FROM 15 Basic SELECT Query ExamplesAll columns Specific columns Expressions and Aliases SELECT * FROM Production.Product; SELECT Name, ListPrice FROM Production.Product; SELECT Name AS Product, ListPrice * 0.9 AS SalePrice FROM Production.Product; 16 Basic SELECT Queries 17 Working with Data Types Transact-SQL Data TypesExact 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 smalldatetime timestamp decimal/numeric ntext datetimeoffset uniqueidentifier numeric xml money geography smallmoney geometry 18 Working with Data Types Data Type ConversionImplicit 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 19 Converting Data Types 20 Working with NULLs NULL ValuesNULL 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 21 Working with NULLs NULL FunctionsISNULL(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 22 Working with NULLs 23 Introduction to Transact-SQLWhat is Transact-SQL? Relational Databases Schemas and Object Names SQL Statement Types The SELECT Statement Working with Data Types Working with NULLs Lab: Introduction to Transact-SQL 24 Defines table(s) to query WHERE