Querying microsoft sql server 2012
Download
1 / 29

Querying Microsoft SQL Server 2012 - PowerPoint PPT Presentation


  • 93 Views
  • Uploaded on

Querying Microsoft SQL Server 2012. Brian Alderman | MCT, CEO / Founder of MicroTechPoint Tobias Ternstrom | Microsoft SQL Server Program Manager. Meet Brian Alderman | ‏ @ brianalderman. MCT, Chief Executive Office, Founder MicroTechPoint Industry-recognized consultant

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Querying Microsoft SQL Server 2012' - ivy-richard


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Querying microsoft sql server 2012

Querying Microsoft SQL Server 2012

Brian Alderman | MCT, CEO / Founder of MicroTechPoint

Tobias Ternstrom | Microsoft SQL Server Program Manager


Meet brian alderman @ brianalderman
Meet Brian Alderman | ‏@brianalderman

  • MCT, Chief Executive Office, Founder MicroTechPoint

    • 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 SharePoint Conferences around the world, he has authored or contributed to several SharePoint, SQL Server, and other technical books, and is a MCSE, MCT, and MCITP: SharePoint 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.


Meet tobias ternstrom
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, type-system 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-90s


Setting expectations
Setting Expectations

Level 300 - Individuals seeking to learn the technical skills required to write Transact-SQL queries for Microsoft SQL Server 2012

Prerequisites - Working knowledge of relational databases. Understanding of SQL Server Management Studio. Basic knowledge of the Microsoft Windows operating system and its core functionality.




01 | Introduction to Microsoft SQL Server 2012

Brian Alderman | MCT, CEO / Founder of MicroTechPoint

Tobias Ternstrom | Microsoft SQL Server Program Manager



Module overview

Types of commands and statement elements

Basic SELECT statements

Module Overview


Commands and Statement Elements


Categories of t sql statements
Categories of T-SQL statements

Data Manipulation Language (DML*)

Data Definition Language (DDL)

Data Control Language (DCL)

  • Statements for querying and modifying data

  • SELECT, INSERT, UPDATE, DELETE

  • Statements for security permissions

  • GRANT, REVOKE, DENY

  • Statements for object definitions

  • CREATE, ALTER, DROP

* DML with SELECT is the primary focus of this course


T sql l anguage elements
T-SQL language elements

Batch Separators

Functions

Predicates and Operators

Variables

Comments

Control of Flow

Expressions


T sql language elements predicates and operators
T-SQL language elements: predicates and operators

T-SQL enforces operator precedence


T sql language elements functions
T-SQL language elements: functions

String Functions

Date and Time Functions

Aggregate Functions

  • SUBSTRING

  • LEFT, RIGHT

  • LEN

  • DATALENGTH

  • REPLACE

  • REPLICATE

  • UPPER, LOWER

  • RTRIM, LTRIM

  • SUM

  • MIN

  • MAX

  • AVG

  • COUNT

  • GETDATE

  • SYSTDATETIME

  • GETUTCDATE

  • DATEADD

  • DATEDIFF

  • YEAR

  • MONTH

  • DAY


T sql language elements variables
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 @MyVar int= 30;


T sql language elements expressions
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

SELECTYEAR(OrderDate)+1 ...

SELECTOrderQty*UnitPrice ...


T sql language elements batch separators
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 l anguage elements control of flow errors and transactions
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

Error Handling

Transaction Control

  • TRY...CATCH

  • BEGIN TRANSACTION

  • COMMIT TRANSACTION

  • ROLLBACK TRANSACTION

  • IF...ELSE

  • WHILE

  • BREAK

  • CONTINUE

  • BEGIN...END


T sql language elements comments
T-SQL language elements: comments

  • Marks T-SQL code as a comment:

    • For a block, enclose it between /* and */ characters

    • For inline text, precede the comments with --

  • T-SQL Editors such as SSMS will typically color-code comments, as shown above

/*

This is a block

of commented code

*/

-- This line of text will be ignored


Logical query processing
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
Applying the logical order of operations to writing SELECT statements

USEAdventureWorks2012;

SELECTSalesPersonID,YEAR(OrderDate)ASOrderYear

FROMSales.SalesOrderHeader

WHERECustomerID= 29974

GROUPBYSalesPersonID,YEAR(OrderDate)

HAVINGCOUNT(*)> 1

ORDERBYSalesPersonID,OrderYear;

USEAdventureWorks2012;

SELECTSalesPersonID,YEAR(OrderDate)ASOrderYear

FROMSales.SalesOrderHeader

WHERECustomerID=29974

GROUPBYSalesPersonID,YEAR(OrderDate)

HAVINGCOUNT(*)> 1

ORDERBYSalesPersonID,OrderYear;


Basic SELECT statementsStatements



Retrieving columns from a table or view
Retrieving columns from a table or view statements

  • 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

SELECTCustomerID,StoreID

FROMSales.Customer;


Using calculations in the select clause
Using calculations statements in the SELECT clause

  • Calculations are scalar, returning one value per row

  • Using scalar expressions in the SELECT clause

SELECTunitprice,OrderQty,(unitprice *OrderQty)

FROMsales.salesorderdetail;


Writing basic select statements

Writing basic SELECT statements


Summary
Summary statements

  • 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 --


Summary1
Summary statements

  • 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


ad