QUERYING MICROSOFT SQL SERVER 2014

FECHA INICIO:
03/10/2016
FECHA FIN:
07/10/2016
HORA INICIO:
09:00 h
HORA FIN:
15:00 h
LUGAR:
Barcelona
REFERENCIA:
MOC 20461
  • PERFIL

    Este curso está dirigido a administradores y desarrolladores de bases de datos. Se valorará que los interesados sean usuarios avanzados de SQL, analistas de negocios y/o desarrolladores de aplicaciones.

  • OBJETIVOS

    • Describir la arquitectura básica y los conceptos de Microsoft SQL Server 2014.

    • Entender las similitudes y diferencias entre Transact-SQL y otros lenguajes de programación.

    • Escribir consultas SELECT

    • Consulta de varias tablas

    • Ordenar y filtrar los datos

    • Describir el uso de tipos de datos en SQL Server

    • Modificar datos mediante Transact-SQL

    • Use las funciones incorporadas

    • Grupo y los datos agregados

    • Utilizar subconsultas

    • Usar expresiones de mesa

    • Usar el conjunto de los operadores

    • Uso clasificación ventana, offset y funciones de agregado

    • Implementar pivotante y conjuntos de agrupación

    • Ejecutar procedimientos almacenados

    • Programa con T-SQL

    • Implementar el control de errores

    • Implementar transacciones

  • TEMARIO

    Module 1: Introduction to Microsoft SQL Server 2014

    ·     The Basic Architecture of SQL Server

    ·     SQL Server Editions and Versions

    ·     Getting Started with SQL Server Management Studio

     

    Module 2: Introduction to T-SQL Querying

    ·     Introducing T-SQL

    ·     Understanding Sets

    ·     Understanding Predicate Logic

    ·     Understanding the Logical Order of Operations in SELECT statements

     

    Module 3: Writing SELECT Queries

    ·     Writing Simple SELECT Statements

    ·     Eliminate Duplicates with DISTINCT

    ·     Using Column and Table Aliases

    ·     Write Simple CASE Expressions

     

    Module 4: Querying Multiple Tables

    ·     Understanding Joins

    ·     Querying with Inner Joins

    ·     Querying with Outer Joins

    ·     Querying with Cross Joins and Self Joins

     

    Module 5: Sorting and Filtering Data

    ·     Sorting Data

    ·     Filtering Data with Predicates

    ·     Filtering with the TOP and OFFSET-FETCH

    ·     Working with Unknown Values


    Module 6: Working with SQL Server 2014 Data Types

    ·     Introducing SQL Server 2014 Data Types

    ·     Working with Character Data

    ·     Working with Date and Time Data

     

    Module 7: Using DML to Modify Data

    ·     Inserting Data

    ·     Modifying and Deleting Data

     

    Module 8: Using Built-In Functions

    ·     Writing Queries with Built-In Functions

    ·     Using Conversion Functions

    ·     Using Logical Functions

    ·     Using Functions to Work with NULL

     

    Module 9: Grouping and Aggregating Data

    ·     Using Aggregate Functions

    ·     Using the GROUP BY Clause

    ·     Filtering Groups with HAVING

     

    Module 10: Using Subqueries

    ·     Writing Self-Contained Subqueries

    ·     Writing Correlated Subqueries

    ·     Using the EXISTS Predicate with Subqueries

     

    Module 11: Using Table Expressions

    ·     Using Views

    ·     Using Inline Table-Valued Functions

    ·     Using Derived Tables

    ·     Using Common Table Expressions

     

    Module 12: Using Set Operators

    ·     Writing Queries with the UNION Operator

    ·     Using EXCEPT and INTERSECT

    ·     Using APPLY

     

    Module 13: Using Window Ranking, Offset, and Aggregate Functions

    ·     Creating Windows with OVER

    ·     Exploring Window Functions

     

    Module 14: Pivoting and Grouping Sets

    ·     Writing Queries with PIVOT and UNPIVOT

    ·     Working with Grouping Sets

     

    Module 15: Querying data with Stored Procedures

    ·     Writing Queries with PIVOT and UNPIVOT

    ·     Passing Parameters to Stored Procedures

    ·     Creating Simple Stored Procedures

    ·     Working with Dynamic SQL

     

    Module 16: Programming with T-SQL

    ·     T-SQL Programming Elements

    ·     Controlling Program Flow

     

    Module 17: Implementing Error Handling

    ·     Using TRY / CATCH Blocks

    ·     Working with Error Information

     

    Module 18: Implementing Transactions

    ·     Transactions and the Database Engine

    ·     Controlling Transactions

     

    Module 19: Appendix 1: Improving Query Performance

    ·     Factors in Query Performance

    ·     Displaying Query Performance Data

     

    Module 20: Appendix 2: Querying SQL Server Metadata

    ·     Querying System Catalog Views and Functions

    ·     Executing System Stored Procedures

    ·     Querying Dynamic Management Objects