Transcript Course 10175A

Version 1.0
Ahmad Adel Gad
[email protected]
•
•
•
•
•
•
•
•
•
•
•
•
MCAD
MCSD
MCPD Enterprise
SQL MCTS
MCT
Software/Web Development Consultant
Cryptography/Digital Signature Consultant
SQL Server 2005/2008R2/2012 Consultant
SharePoint 2007/2010/2013 Consultant
AJAX Consultant
Software Development Lead
Professional Scrum Master
Course Outline
1. Overview
2. Select Statement - Retrieve Data
3. Select Statement - Filtering Data
4. Working with NULL Values
5. Formatting Result Sets
6. System Functions
7. Combining and Limiting Result Sets
8. Grouping Data
9. Using Table Expressions
Querying Microsoft SQL Server 2012 - Ahmad Adel
Course Outline
10. Windowing and Paging Data (Over Clause)
11. Querying SQL Server Metadata
11. Data Manipulation Language (DML)
12. Querying Full-Text Index Search
13. Distributed Queries
15. Implementing Error Handling (Handling Exceptions)
16. Implementing Transactions (TCL)
Querying Microsoft SQL Server 2012 - Ahmad Adel
Overview
•
•
About SQL Language (T-SQL)
Introduction to Microsoft SQL Server 2012
Querying Microsoft SQL Server 2012 - Ahmad Adel
Select Statement - Retrieve Data
•
•
•
•
•
•
•
•
•
Fixed Values
Variables
Calculated Values
All Fields From Specific Table
Specific Fields From Specific Table
Concatenated Fields - Alias Name
Calculated Value On Specific Fields
Specific Columns From Table Alias Name
Value Returned From Function
Querying Microsoft SQL Server 2012 - Ahmad Adel
Select Statement - Filtering Data
•
•
Comparison operators
Logical operators
Querying Microsoft SQL Server 2012 - Ahmad Adel
Working with NULL Values
•
•
•
•
•
IS Null - Comparison
ANSI_NULLS Settings
ISNULL() Function
NullIF() Function
Coalesce() Function
Querying Microsoft SQL Server 2012 - Ahmad Adel
Formatting Result Sets
•
•
Sorting Data
Eliminating Duplicate Rows
Querying Microsoft SQL Server 2012 - Ahmad Adel
System Functions
•
•
•
Aggregate Functions
Scalar Functions
Converting Functions
Querying Microsoft SQL Server 2012 - Ahmad Adel
Combining and Limiting Result Sets
•
•
•
•
•
•
•
•
•
Join
Inner/Where Clause
Outer (Full/Left/Right)
Cross
Self Join
Data Samples (TableSample Operator)
Cube
Union/Except/Intersect
Apply (Cross/Outer)
Querying Microsoft SQL Server 2012 - Ahmad Adel
Grouping Data
•
•
•
•
Group By
RollUP
Cube
Pivot - UnPivot
Querying Microsoft SQL Server 2012 - Ahmad Adel
Using Table Expressions
•
•
•
•
•
Sub Queries
Derived Tables
Common Table Expressions (CTE)
Views
Table-Valued Functions
Querying Microsoft SQL Server 2012 - Ahmad Adel
Windowing and Paging Data (Over Clause)
•
Paging Functions
•
•
•
Offset – Fetch
Row_Number
Over Clause
•
•
•
•
Ranking Functions
Analytic Functions
Aggregate Functions
NEXT VALUE FOR
Querying Microsoft SQL Server 2012 - Ahmad Adel
Querying SQL Server Metadata
•
•
•
•
•
•
System Variable
System Stored Procedures
System Catalog Views
System Catalog Functions
Dynamic Management Views (DMV)
Dynamic Management Functions (DMF)
Querying Microsoft SQL Server 2012 - Ahmad Adel
Data Manipulation Language (DML)
•
•
•
•
Insert
Update
Delete
Output Clause
Querying Microsoft SQL Server 2012 - Ahmad Adel
Querying Full-Text Index Search
•
•
Contains
FreeText
Querying Microsoft SQL Server 2012 - Ahmad Adel
Distributed Queries
•
•
•
OpenDataSource Function
OpenRowSet Function
Configure 'Ad Hoc Distributed Queries' Settings
Querying Microsoft SQL Server 2012 - Ahmad Adel
Implementing Error Handling
(Handling Exceptions)
•
@@Error Variable
Try … Catch
Throw Exceptions
•
•
•
•
•
•
RaiseError
Throw
System Error Information Functions
Error Severities
Querying Microsoft SQL Server 2012 - Ahmad Adel
Implementing Transactions (TCL)
• Autocommit Transactions
• Implicit Transactions
• Explicit Transactions
• XACT_ABORT Setting
• Implementing Transactions with Exception Handling
• XACT_STATE() Function
• Isolation Levels
Querying Microsoft SQL Server 2012 - Ahmad Adel