Brian Alderman MCT CEO Founder of Micro Tech
Brian Alderman | MCT, CEO / Founder of Micro. Tech. Point Tobias Ternstrom | Microsoft SQL Server Program Manager
Meet Brian Alderman | @brianalderman MCT, Chief Executive Office, Founder Micro. Tech. Point Industry-recognized consultant Noted author and conference speaker Brian’s expertise and designs range across Microsoft operating systems More than 25 years of industry experience Brian has been focused on helping IT Pros and Database Administrators (DBAs) better understand core Microsoft technologies for over 25 years. A frequent presenter at Share. Point Conferences around the world, he has authored or contributed to several Share. Point, SQL Server, and other technical books, and is a MCSE, MCT, and MCITP: Share. Point and SQL Server Administrator. Brian has a BS and MS in Computer Information Systems where he graduated summa cum laude from Regis University of Colorado Springs and lives in Scottsdale, AZ where he enjoys playing golf year round and traveling around the world. Linked. In /brianalderman Blog http: //brianalderman. wordpress. com
Meet Tobias Ternstrom • Principal Program Manager Lead in the SQL Server & Windows Azure SQL DB product group • His team owns the development of the T-SQL language, typesystem and server-side libraries and performance management in SQLDB • Tobias describes himself as a developer, instructor and architect who has been using SQL Server since mid-90 s
Querying Microsoft SQL Server 2012 Jump Start 01 | Introducing SQL Server 2012 SQL Server types of statements; other SQL statement elements; basic SELECT statements 02 | Advanced SELECT Statements DISTINCT, Aliases, scalar functions and CASE, using JOIN and MERGE; Filtering and sorting data, NULL values 03 | SQL Server Data Types Introduce data types, data type usage, converting data types, understanding SQL Server function types 04 | Grouping and Aggregating Data Aggregate functions, GROUP BY and HAVING clauses, subqueries; self-contained, correlated, and EXISTS; Views, inline-table valued functions, and derived tables | Lunch Break Eat, drink, and recharge for the afternoon session
Querying Microsoft SQL Server 2012 Jump Start 05 | SET Operators, Windows Functions, and Grouping SET operators, Windows functions, GROUPING sets (PIVOT, UNPIVOT, CUBE, ROLLUP) 06 | Modifying Data INSERT, UPDATE, and DELETE statements, use of defaults, constraints, and triggers, OUTPUT 07 | Programming with T-SQL Using T-SQL programming elements, implementing error handling, understanding and implementing transactions 08 | Retrieving SQL Server Metadata and Improving Query Performance Querying system catalogs and dynamic management views, creating and executing stored procedures, improving SQL Server query performance
01 | Introduction to Microsoft SQL Server 2012 Brian Alderman | MCT, CEO / Founder of Micro. Tech. Point Tobias Ternstrom | Microsoft SQL Server Program Manager
Course Topics Querying Microsoft SQL Server 2012 Jump Start 01 | Introducing SQL Server 2012 SQL Server types of statements; other SQL statement elements; basic SELECT statements 02 | Advanced SELECT Statements DISTINCT, Aliases, scalar functions and CASE, using JOIN and MERGE; Filtering and sorting data, NULL values 03 | SQL Server Data Types Introduce data types, data type usage, converting data types, understanding SQL Server function types 04 | Grouping and Aggregating Data Aggregate functions, GROUP BY and HAVING clauses, subqueries; self-contained, correlated, and EXISTS; Views, inline-table valued functions, and derived tables | Lunch Break Eat, drink, and recharge for the afternoon session
Module Overview Types of commands and statement elements Basic SELECT statements
Commands and Statement Elements
Categories of T-SQL statements Data Manipulation Language (DML*) • Statements for querying and modifying data • SELECT, INSERT, UPDATE, DELETE Data Definition Language (DDL) Data Control Language (DCL) • Statements for object definitions • Statements for security permissions • CREATE, ALTER, DROP • GRANT, REVOKE, DENY * DML with SELECT is the primary focus of this course
T-SQL language elements Predicates and Operators Batch Separators Functions Control of Flow Variables Comments Expressions
T-SQL language elements: predicates and operators Elements: Predicates and Operators: Predicates IN, BETWEEN, LIKE Comparison Operators =, >, <, >=, <>, !=, !>, !< Logical Operators AND, OR, NOT Arithmetic Operators Concatenation +, -, *, /, % + T-SQL enforces operator precedence
T-SQL language elements: functions String Functions • SUBSTRING • LEFT, RIGHT • LEN • DATALENGTH • REPLACE • REPLICATE • UPPER, LOWER • RTRIM, LTRIM Date and Time Functions • GETDATE • SYSTDATETIME • GETUTCDATE • DATEADD • DATEDIFF • YEAR • MONTH • DAY Aggregate Functions • SUM • MIN • MAX • AVG • COUNT
T-SQL language elements: variables Local variables in T-SQL temporarily store a value of a specific data type Name begins with single @ sign @@ reserved for system functions Assigned a data type Must be declared and used within the same batch In SQL Server 2008 and later, can declare and initialize in the same statement DECLARE @My. Var int = 30;
T-SQL language elements: expressions Combination of identifiers, values, and operators evaluated to obtain a single result Can be used in SELECT statements SELECT clause WHERE clause Can be single constant, single-valued function, or variable Can be combined if expressions have same the data type SELECT YEAR(Order. Date) + 1. . . SELECT Order. Qty * Unit. Price. . .
T-SQL language elements: batch separators Batches are sets of commands sent to SQL Server as a unit Batches determine variable scope, name resolution To separate statements into batches, use a separator: SQL Server tools use the GO keyword GO is not a SQL Server T-SQL command
T-SQL language elements: control of flow, errors, and transactions Allow you to control the flow of execution within code, handle errors, and maintain transactions Used in programmatic code objects Stored procedures, triggers, statement blocks Control of Flow • • • IF. . . ELSE WHILE BREAK CONTINUE BEGIN. . . END Error Handling • TRY. . . CATCH Transaction Control • BEGIN TRANSACTION • COMMIT TRANSACTION • ROLLBACK TRANSACTION
T-SQL language elements: comments Marks T-SQL code as a comment: For a block, enclose it between /* and */ characters /* This is a block of commented code For inline text, precede the comments with -*/ T-SQL Editors such as SSMS will typically color-code comments, as shown above -- This line of text will be ignored
Logical query processing The order in which a query is written is not the order in which it is evaluated by SQL Server. 5: SELECT <select list> 1: FROM <table source> 2: WHERE <search condition> 3: GROUP BY <group by list> 4: HAVING <search condition> 6: ORDER BY <order by list>
Applying the logical order of operations to writing SELECT statements USE Adventure. Works 2012; SELECT Sales. Person. ID, YEAR(Order. Date) AS Order. Year FROM Sales. Order. Header WHERE Customer. ID = 29974 GROUP BY Sales. Person. ID, YEAR(Order. Date) HAVING COUNT(*) > 1 ORDER BY Sales. Person. ID, Order. Year;
Basic SELECT Statements
Elements of the SELECT statement Clause Expression SELECT <select list> FROM <table source> WHERE <search condition> GROUP BY <group by list> ORDER BY <order by list>
Retrieving columns from a table or view Use SELECT with column list to display columns Use FROM to specify a source table or view Specify both schema and table names Delimit names if necessary End all statements with a semicolon Keyword Expression SELECT <select list> FROM <table source> SELECT Customer. ID, Store. ID FROM Sales. Customer;
Using calculations in the SELECT clause Calculations are scalar, returning one value per row Operator Description + Add or concatenate - Subtract * Multiply / Divide % Modulo Using scalar expressions in the SELECT clause SELECT unitprice, Order. Qty, (unitprice * Order. Qty) FROM salesorderdetail;
Demo Writing basic SELECT statements
Summary Three types of command used to manage SQL server objects and security include; DDL, DML, and DCL T-SQL language elements include; Predicates and operators – BETWEEN, LIKE, NOT, >=, * Functions – string, date and time, aggregate Variables – local has one @, system has two @@ Expressions – identifiers, values, and operators Batch separators – GO used to separate statements Control-of-flow – IF…ELSE, WHILE, CONTINUE Comments - /* to start */ to end; can also use --
Summary Predicate logic is a property or expression that is either true or false. Also referred to as a Boolean expression Elements of a SELECT statement and the order they are evaluated : 1. FROM 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. ORDER BY
© 2013 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.
- Slides: 29