This course is the foundation of all disciplines related to SQL Server; ie database management data, database development and Business Intelligence. This course helps you prepare for the 70-461 exam.
Note: This course is designed for customers who are interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features of SQL Server 2014, but also the important capabilities through SQL Server data platform.
This course is for administrators of databases, database developers and professionals in Business Intelligence. The course is likely to be well attended by SQL advanced users who are not necessarily oriented database or plan to take the examination, ie, report writers, business analysts and developers of client applications.
Módulo 1: Introducción a Microsoft SQL Server 2014
This module introduces the main tools and SQL Server platform. editions, versions, tools used for the logical structure of databases, information and consultation are analyzed.
• The basic architecture of SQL Server
• Versions and editions of SQL Server
• Getting Started with SQL Server Management Studio
Module 2: Introduction to T-SQL queries
This module introduces the main Transact SQL as query language SQL Server. The basic structure of the T-SQL queries, the logical flow of a SELECT statement is analyzed and concepts as predicates and set based operations are introduced.
• Introduction of T-SQL
• Sets understanding
• Logical understanding
• Understand the logical order of operations in SELECT statements
Module 3: Write queries SELECT
This module introduces the fundamentals of the SELECT statement, focusing on query against a single table.
• Write simple SELECT statements
• Delete duplicates with different
• With column and table aliases
• Write expressions cases Simple
Module 4: Querying Multiple Tables
This module explains how to write queries that combine data from multiple sources into SQL Server. The module introduces the use of combinations in T-SQL queries as a mechanism to retrieve data from multiple tables.
• Joins understanding
• Consultations with inner joins
• Consultations with outer joins
• Consultations with Cruz joins and joins oneself
• Write queries that use self-joins and joins Cruz.
Module 5: Sorting and filtering data
This module explains how to improve consultation to limit the rows returned and to control the order in which rows appear. The module also describes how to resolve unknown and missing results.
• Sort Data
• Filter data with a WHERE clause
• Filtering with the main options and OFFSET-FETCH
• Working with unknown values and missing
Module 6: Working with SQL Server 2014 data types
This module explains the data types that SQL Server uses to store data. Introduces many types of numeric data types and special use. It also explains conversions between data types and the importance of the type precedence.
• Introduction to Data Types SQL Server 2014
• Working with character data
• Working with Date and Time Data
Module 7: Using DML modify data
This module describes the use of data manipulation language Transact-SQL to perform inserts, updates and deletes your data.
• Insert Data
• Modification and deletion of data
Module 8: Using the integrated functions
This module introduces the use of the functions that are built into SQL Server Denali and discuss some common uses including conversion of data types, testing nullability and logical results.
• Write queries with integrated functions
• Using conversion functions
• Using logic functions
• Use functions to work with NULL
Module 9: Grouping and aggregation
This module introduces methods to group data in a query, pooled data aggregation and filtering HAVING groups. The module is designed to help students understand why a SELECT clause has restrictions on column names in clause GROUP BY and columns that can be listed in the SELECT clause.
• Using aggregate functions
• Through the group by clause
• Filtration groups have
Module 10: Using subqueries
This module introduces the use of subqueries in various parts of a SELECT statement. It will include the use of scalar subqueries and multiple outcomes and the use of operators and there.
• Write autonomous subqueries
• Writing correlated subqueries
• Using the predicate exists with subqueries
Module 11: Using table expressions
This module introduces T-SQL expressions that return a valid relational table, typically for subsequent use in the query. The module examines views, derived tables, common table expressions and table-valued functions online.
• Using derived tables
• Using common table expressions
• Using views
• Using table-valued functions online
Module 12: Using the system operators
This module introduces Microsoft SharePoint Server as a platform for BI and then focuses on the creation of BI dashboards and scorecards with PerformancePoint Services.
• Write queries with the operator of Union
• Using less and intersect
• Apply with
Module 13: Using the window graduation, compensate and add features
This module introduces window functions including sorting functions, aggregate and compensation.Much of this functionality is new in SQL Server 2012. It will cover the use of functions of T-SQL as ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, lead , LAST_VALUE FIRST_VALUE and to perform calculations with a series or window of the rows.
• Create windows over
• Functions of the browser window
Module 14: Pivoting and group sets
This module describes techniques for data in T-SQL to introduce the fundamentals of clause rotating group sets. It will also cover the use of group ROLLUP and syntax group cube in SQL Server.
• Write queries with PIVOT and UNPIVOT
• Working with grouping sets
Module 15: Executing Stored Procedures
This module introduces the use of existing stored procedures in an environment of T-SQL queries. the use of run is analyzed, how to take input and output parameters to a procedure and how to invoke the system stored procedures.
• Consult data with stored procedures
• Passing parameters to stored procedures
• Creating Stored Procedures Simple
• Working with Dynamic SQL
Module 16: T-SQL Programming
This module provides a basic introduction to programming concepts and objects T-SQL introduction.lots, variables, flow control elements such as loops and conditionals, how to create and execute dynamic SQL statements and how to use synonyms are analyzed.
• Elements T-SQL programming
• Flow control program
Module 17: Implementing Error Handling
This module introduces the use of error handlers in the T-SQL code. The difference between compilation errors and runtime errors will be introduced and will cover how errors affect lots. The module will also cover how control error handling using blocks TRY / CATCH, the use of class ERROR functions and use the new instruction THROW.
• Using TRY / CATCH blocks
• Working with information Error
Module 18: Implementing Transactions
This module introduces the concepts of transaction management in SQL Server. It will provide a high level summary of property transaction, cover the basics of transactions with BEGIN, COMMIT and ROLLBACK brand.
• Transactions and the database engine
• Transaction control
• Insulation levels
Module 19: Improve query performance
This module has several keys to writing queries good performance guidelines and ways to control the execution of queries and their impact on Microsoft SQL Server.
• Factors in query performance
• Data visualization query performance
Module 20: SQL Server Query metadata
SQL Server provides access to structured using a variety of mechanisms, such as system catalog views, system functions, dynamic management objects and metadata system stored procedures. In this module you will learn how to write queries to return metadata system using these mechanisms.
• Consultation system catalog views and functions
• System stored procedures execution
• View dynamic management objects