Microsoft 70-461: Querying SQL Server 2012

Explore an introduction to SQL Server 2012 and learn about the tools available for working with and querying SQL Server 2012.

Explore SQL Server 2012, including what comes with SQL Server and what it does. Examine instances of SQL Server, the available editions, and the objects contained within SQL Server.

Explore SQL Server 2012 editions and licensing, from Enterprise and Standard to Developer and Express, including Business Intelligence, Web, Express Local, and Parallel Data Warehouse, plus cloud offerings.

Explore Object Explorer, a hierarchical view in SQL Server Data Tools to connect to an instance, browse databases, tables, and stored procedures, and configure draggable tabs on the design surface.

Explore SQL Server data tools object scripting by right-clicking objects in Object Explorer to create, drop, insert, or alter them, with options to save to file or copy to clipboard.

Navigate the template explorer in SQL Server 2012 data tools to customize and reuse templates by specifying values for template parameters, including handling existing databases and predefined or user-created scripts.

Explore how SQL projects and solutions in SQL Server Management Studio organize text SQL scripts and connections as containers, replacing ad hoc folders by linking related objects.

Demonstrates debugging T-SQL in SQL Server Data Tools by stepping through a loop with a @i variable set to 100, inspecting locals and call stack, and noting no intermediate results.

Prepare for the exam with hands-on practice using SQL Server Data Tools and the Adventure Works 2012 sample database; explore tools and options, then use the results to text icon.

Review the module on SQL Server basics, including default and named instances, access methods by server name or IP address, and editions, licensing, objects, SSDT, and SQL command line interface.

Begin with a T-SQL overview and learn how to write the basic select statement to query data in SQL Server 2012.

Explore structured query language concepts and the role of SQL Server, highlighting T-SQL as a declarative, scripted language with parsing, binding, optimization, and execution-time compilation.

Learn to use single-line comments with two hyphens and multi-line comments with /* */ in T-SQL to document ad hoc queries and stored procedures; comments are ignored during execution.

Explore batch separators in SQL Server, using the go statement as a batch separator; ensure go is on its own line, and go with a number repeats a batch (2005).

Explore batch directives in SQL scripts, learning how batched execution isolates errors and lets parts of a script succeed or fail without aborting the entire run.

Demonstrates using a line terminator with a common table expression in SQL Server 2012. Shows adding a semicolon after use Adventure Works to avoid syntax errors.

Declare an integer variable with a declare statement, demonstrate a while loop counting from ten to zero, and explain how the go batch directive limits the scope of variables.

Explore how to control sql query execution using if else, while, break, and begin transaction. Learn about commit, rollback, and try catch for structured error handling and transaction management.

Explore control flow with a while loop that iterates business_entity_id values and returns names from the person table. Break and begin tran, commit, rollback with structured error handling.

Explore SQL expressions that return a single scalar value, used in select or where clauses. Ensure numeric data type compatibility and avoid non-numeric inputs, with examples.

Learn to create expressions that produce a derived column by concatenating first name, middle name, and last name with spaces from the person table, while ensuring compatible data types.

Explore system and user defined functions, including rowset and scalar valued types, open query with a provider, and how aggregate and ranking functions like row_number return values per row.

Explore aggregate functions to compute average total due and tax sum from the sales order header, and demonstrate date and time scalar functions and row_number ranking by reorder point.

Explore predicates and operators as boolean expressions in SQL Server, focusing on three-valued logic (true, false, unknown) and combining predicates with and or in where and check constraints.

Explore predicates and operator precedence in SQL queries, grouping conditions with parentheses to combine last name Abercromby, title and middle name with and/or logic.

Master a basic select statement that requires at least one value, selecting first name, middle name, and last name from the person schema person table, and ending with a semicolon.

Explore writing simple select statements, using system date/time and version functions, or selecting from a table or view with an asterisk or specific column names, and include a from clause.

Explore selecting with from, where, group by, having, and order by; learn when from is required for tables or views, and when a scalar valued function can omit extras.

Practice column aliasing in select queries by aliasing a date and time scalar function as design and concatenating first and last names as name for readability and easier ordering.

Master the exam by understanding the order of execution and function types. Practice aliasing columns with or without as, and use from, where, group by, order by.

Explore the basic select statement and learn how to specify columns, create derived columns, and alias columns in SQL Server 2012.

Select only the specific columns you need by listing them after the select keyword, separated by commas such as first name, middle name, and last name, with a from clause.

Demonstrates selecting columns in sql server 2012 query, using intellisense to pick first name, middle name, and last name, and managing commas and syntax errors to shape the result set.

Learn how derived columns, created from calculations or derived from different queries, produce a single unnamed column by concatenating first name, a space, middle name, a space, and last name.

Explore creating derived columns in SQL Server by concatenating first, middle, and last names with spaces, handling nulls, and using static and dynamic values with aliases.

Create calculated columns that return scalar values from other columns or static values, using unit price and line total to compute dynamic quantities and alias a 15 percent discount.

Explore calculated columns by aliasing expressions, such as discounting total due by 0.85 percent and computing unit price from line total divided by order quantity.

Apply the distinct clause to remove duplicates across selected columns, as shown with names and states; adding more columns reduces duplicates, while including a primary key yields unique rows.

Explore filtering data in SQL Server 2012 by using where, top, and offset, highlighting how these techniques refine results in this module.

Explore SQL filtering techniques by applying where clauses, using between and in, filtering null values, applying the like operator, and performing data type comparisons.

Explain the where syntax and predicates in the from clause, including true, false, and unknown. Emphasize evaluating the overall workload and rewriting queries before dropping indexes for performance.

Use comparison operators in the where clause to build predicates that evaluate true, false, or unknown. Filter by order date or freight totals in the sales schema, using quoted dates.

Explore how the where clause uses in to filter data by multiple values, with examples of last names Abercromby, Russell, and Ryan, and highlight dynamic subqueries.

Discover how to handle null values in SQL Server using is null, is not, and coalesce to replace nulls in concatenations and calculations, and understand how nulls affect comparisons.

Filter data with the where clause using predicates, between, and in to apply comparisons such as greater than or equal to and less than or equal to.

Learn to handle null values in SQL, where ANSI states null is unknown. Use is null and is not null, coalesce, nullif, and like patterns for derived columns.

Explore filtering data with where clauses using wildcards and like patterns, and cast and convert date and time values to extract year, month, day, and hour for precise predicates.

Learn how offset fetch in SQL Server 2012 enables efficient paging with an order by clause, returning 50 rows at a time using offset and fetch next.

Master top and top with ties using order by to return the top rows or top percent, including how ties are resolved and how statistics guide optimization.

Master exam-ready data filtering in SQL Server 2012 by using where clause syntax, like, top and top with ties, cast/convert, and offset fetch paging.

Filter data with the where clause and predicates, and use between, like, and top to return key results. Learn offset fetch for paging in SQL Server 2012.

Explore the fundamentals of joins by examining inner, outer, cross, and self joins. Learn the categories of joins and how they connect data in SQL Server 2012.

Explore how joins combine normalized relational tables to return data for reports, using the join keyword and on clause, with primary keys, foreign keys, and data type compatibility.

Learn how to use inner join with the on clause to define join predicates, alias tables, and fully qualify columns to avoid ambiguous names.

Explore how inner, left, and right outer joins, plus cross joins, connect tables using the on clause, compare equi joins, and illustrate Cartesian products with practical SQL examples.

Learn to use the union operator to combine query results from employees and customers with three columns, ensuring matching data types and distinct vs union all.

Learn to use set operators—union, union all, intersect, and except—to combine, filter, and compare query results with sample tables in SQL Server 2012.

Review key SQL join types: inner, outer, cross, and self joins, and learn how to use union, union all, intersect, and except to compare and combine result sets.

Explore subqueries and table expressions in SQL Server 2012, including exists and some queries, common table expressions, temporary tables, and table variables.

Explore an introduction to sub queries, including self-contained sub queries and correlated sub queries, to understand how nested queries operate in SQL Server 2012.

Understand correlated subqueries in SQL Server 2012, where the inner query relies on the outer query, and explore troubleshooting and rewriting with a join for each outer row.

Explore how exists and not exists operate in a where clause, using a correlated subquery to check product subcategory IDs for names like wheels in the production schema.

Explore common table expressions and learn how to write them. Meet temporary tables and table variables and learn how to use them.

Compare table variables and temporary tables in SQL Server 2012: temp tables maintain statistics, table variables do not, affecting performance on large set based operations; declare table variable syntax included.

Explore system functions in SQL Server, review an overview, examine aggregate functions, and study conversion functions.

Examine aggregate functions, scalar function categories, Rosset functions, and window functions in SQL Server 2012, and learn how aggregation, group by clause, and null handling affect results.

Explore system scalar function categories and the deterministic versus non-deterministic nature of scalar functions, with examples like newid() and object_id, plus hands-on practice in SQL Server tools.

Explore system functions in SQL Server 2012, covering scalar functions for date and time, convert and substring, and window functions like row_number with partition by and order by.

Explore aggregate functions in SQL Server 2012, including distinct with aggregates, group by basics, grouping sets, rollup and cube, and having examples.

Aggregate functions perform calculations on a set of values, returning a single scalar and ignoring nulls; they're deterministic, with max, avg, and sum, used in select lists and having clauses.

Explore how distinct with aggregates counts unique values in a column, ignores nulls, and contrasts with select distinct that eliminates duplicate rows; use coalesce to replace nulls for accurate results.

Demonstrates grouping sets, roll up, and cube in SQL Server 2012 to produce multi-level aggregations (max, average, sum) by year, order date, and product subcategory, with alias rules in group by.

Learn how implicit and explicit data type conversions work in SQL Server 2012, compare cast, convert, and parse, and apply optional format arguments to format dates and strings.

Explore conversion functions in SQL Server 2012, including parse, try parse, cast, and convert, with optional culture parameters for date and time and numeric conversions.

Master exam-ready topics in sql server 2012, including system functions, scalar, aggregate, and window functions, plus grouping sets, roll up, and conversion functions.

Explore system functions and their types, master aggregations and grouping sets, and apply casting, converting, and culture-aware parsing with having and where filters.

Learn how iif, choose, and case expressions work in SQL Server 2012, evaluating conditions to return true or false, selecting by index, or yielding a value from multiple cases.

Explore logical functions, including is date, is numeric, choose, and the case statement, and apply predicates to categorize line totals as large or small and determine profitability.

This lecture explains views as virtual tables defined by a query, stored in a database to hide complexity and enable security, including updatable views and ddl operations.

Create and alter views with a derived full name column and encryption, then compare view limitations to stored procedures, functions, and triggers, while showing parameterized procedures with temporary tables.

Explore scalar and table-valued functions, including inline and multi-line variants, with parameters and schema qualification, compare to stored procedures and views, and learn about triggers and transaction rollback.

Explore demonstration of views, stored procedures, functions, and triggers in SQL Server, showing how inserted and deleted virtual tables support insert, update, and delete operations, security, and hiding query complexity.

Learn structured error handling with try catch in SQL Server 2012, introducing try catch concepts and practical ways to implement error handling.

Explore structured error handling in SQL Server 2012 with begin try and begin catch, using the throw statement to raise errors and access error information.

Review key SQL concepts: compare values with select operators and results. Explore views, inline table-valued and scalar-valued functions, stored procedures, triggers, and try catch and throw in SQL Server 2012.

Explore the fundamentals of transactions and learn how insert, update, delete, truncate, and merge statements participate in transaction processing.

Master inserting data with insert statements using values or select, specify column order, and apply top options, then update tables with set clauses and where conditions to target rows.

Explore transactions with insert, update, delete, and merge, using the output clause to audit inserted rows in a table variable and demonstrate rollback with a unique constraint.

Demonstrates querying and modifying the people table with updates, joins, deletes, and truncation; shows affected rows, refreshes via Intellisense, and dropping the table.

SQL Server manages transactions with implicit commits and rollbacks; explicitly begin tran, commit tran, or rollback. Track transaction count per connection, use save points, and know disconnections auto roll back.

Explore transaction management in SQL Server by performing begin transaction, updating and rolling back changes, and examining locks, dirty reads, and phantom reads with and without lock hints.

Review dml statements such as insert, update, delete, and merge, and examine how SQL Server logs transactions and implicitly commits or rolls back, while explicitly managing transaction control.