10774: Querying Microsoft SQL Server 2012,Manila-Philippines

10774: Querying Microsoft SQL Server 2012

10774: Querying Microsoft SQL Server 2012 Course Description

Duration: 5.00 days (40 hours), 

This 5-day instructor led course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. Many of the exercises in this course are SQL Azure enabled.

Intended Audience For This 10774: Querying Microsoft SQL Server 2012 Course

  • » This course is intended for Database Administrators, Database Developers, and Business Intelligence professionals. The course will very likely be well attended by SQL power users who aren’t necessarily database-focused or plan on taking the exam; namely, report writers, business analysts and client application developers.
  • 10774: Querying Microsoft SQL Server 2012 Course Objectives

    • » Write SELECT Queries
    • » Query Multiple Tables
    • » Use Built-In Functions
    • » Use Subqueries
    • » Execute Stored Procedures
    • » Use Set Operators
    • » Implement Error Handling
    • » Implementing Transactions
    • » Use Table Expressions
    • » Sort and Filter Data
    • » Use Window Ranking, Offset and Aggregate Functions
    • » Query SQL Server Metadata
    • » Program with T-SQL
    • » Improve Query Performance
    • 10774: Querying Microsoft SQL Server 2012 Course Outline

        1. Module 1: Introduction to Microsoft SQL Server 2012
          1. Describe SQL Server components
          2. Create and save T-SQL scripts in SQL Server Management Studio
          3. Browse the objects in a database
          4. Search T-SQL syntax definitions in Books Online
        2. Module 2: Introduction to Transact-SQL Querying
          1. Describe the elements of T-SQL and its role in writing queries
          2. Describe the use of sets in SQL Server
          3. Describe the use of predicate logic in SQL Server
          4. Define the logical order of operations in SELECT statements
        3. Module 3: Writing SELECT Queries
          1. Write Simple SELECT statements
          2. Eliminate duplicates using the DISTINCT clause
          3. Use column and table aliases
          4. Write simple CASE expressions
        4. Module 4: Querying Multiple Tables
          1. Describe how multiple tables may be queried in a SELECT statement using joins
          2. Write queries which use inner joins
          3. Write queries which use outer joins
          4. Write queries which use self-joins and cross joins
        5. Module 5: Sorting and Filtering Data
          1. Filter data with predicates in the WHERE clause
          2. Sort data using ORDER BY
          3. Filter data in the SELECT clause with TOP
          4. Filter data with OFFSET and FETCH
          5. Understand and handle NULLs
        6. Module 6: Working with SQL Server Denali Data Types
          1. Describe numeric data types, type precedence and type conversions
          2. Write queries using character data types
          3. Write queries using date and time data types
        7. Module 7: Using Built-In Functions
          1. Write queries with built-in scalar functions
          2. Use conversion functions
          3. Use logical functions
          4. Use functions that work with NULL
        8. Module 8: Grouping and Aggregating Data
          1. Write queries which summarize data using built-in aggregate functions
          2. Use the GROUP BY clause to arrange rows into groups
          3. Use the HAVING clause to filter out groups based on a search condition
        9. Module 9: Using Subqueries
          1. Use self-contained subqueries, correlated subqueries, scalar subqueries, and multi-valued subqueries
          2. Use the IN and EXISTS predicates
        10. Module 10: Using Table Expressions
          1. Write queries against views
          2. Use derived tables
          3. Use common table expressions
          4. Use inline table-valued expressions
        11. Module 11: Use Set Operators
          1. Describe the difference between a set and a multi-set
          2. Use the UNION and UNION ALL operators
          4. Use EXCEPT and INTERSECT operators
        12. Module 12: Using Window Ranking, Offset and Aggregate Functions
          1. Use window ranking functions ROW_NUMBER, RANK, DENSE_RANK, NTILE
          2. Use offset functions LAG, LEAD, FIRST_VALUE, LAST_VALUE
          3. Use window aggregate functions
          4. Use the basic forms of window partitioning, window ordering and window framing
        13. Module 13: Pivoting and Grouping Sets
          1. Use the basic form of PIVOT and UNPIVOT operators
          2. Use the fundamental form of GROUPING SETS clause
        14. Module 14: Querying SQL Server Metadata
          1. Write queries against system catalog views and functions
          2. Execute system stored procedures
          3. Identify and query system dynamic management objects
          4. Describe security permissions necessary to query system objects
        15. Module 15: Executing Stored Procedures
          1. Invoke existing stored procedures with EXECUTE
          2. Pass input parameters and receive output parameters
          3. Generate dynamic SQL statements
        16. Module 16: Programming with T-SQL
          1. Understand where T-SQL programming objects can be used
          2. Declare variables and delimit batches
          3. Use control of flow elements such as IF, WHILE, BEGIN, END, RETURN
          4. Use synonyms
        17. Module 17: Implementing Error Handling
          1. Describe the behavior of SQL Server when errors occur in T-SQL code
          2. Use TRY/CATCH blocks to handle error flow
          3. Understand ERROR class functions
        18. Module 18: Implementing Transactions
          1. Describe the benefits of using transactions
          2. Control transactions with BEGIN, COMMIT, ROLLBACK
          3. Describe error handling behavior in transactions
        19. Module 19: Improving Query Performance
          1. Describe how SQL Server uses indexes to support query execution
          2. Enable the display of execution plans in SQL Server Management Studio
          3. Enable system IO and time information in query results
          4. Describe the differences between accessing data in sets versus via cursors