01|  Introduction to Microsoft SQL Server 2012


Command and Statement Elements 

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 object definitions

CREATE, ALTER, DROP
Statements for security permissions

GRANT, REVOKE, DENY

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 Language Elements: Functions

 String Functions
 Date and Time Functions  
Aggregate Functions 
SUBSTRING
LEFT, RIGHT
LEN
DATALENGTH
REPLACE
REPLICATE
UPPER, LOWER
RTRIM, LTRIM
GETDATE
SYSTDATETIME
GETUTCDATE
DATEADD
DATEDIFF
YEAR
MONTH
DAY
SUM
MIN
MAX
AVG
COUNT 

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 initialling in the same statement
DECLARE  @MyVar  int = 30;


T-SQL Language Elements: Expressions

Assign 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(OrderDate) + 1 ...

SELECT OrderQty * UnitPrice ...