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 how SQL Server 2012 evolved from a relational database to a full suite, including integration services, analysis services, and reporting services with full text indexing.

Learn how sql server supports default and named instances with separate security contexts, and how the server browser service manages ports 1433 and 1434 for remote and local connections.

Explore why multiple SQL Server instances create security boundaries and support version upgrades, then assess licensing and performance trade-offs with proper testing.

Explore SQL Server instances via configuration manager, highlighting a default and a named instance with separate services and startup settings. Connect to both on the local server using management studio.

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.

Explain how SQL Server 2012 licensing shifted to core-based pricing, replacing processor-based licensing, with Enterprise Edition using core licenses and Standard or Developer editions offering server+CAL as an option.

Explore sql server objects from physical or virtual servers and instances to databases, including data and log files, file groups, views, tables, columns, data types, and constraints.

Identify the system databases in SQL Server—master, model, MSDB, TempDB, and the resource database—and explain how the model provides templates for new databases and how the master affects startup.

Explore SQL Server Data Tools (SSD) and the SQL Server command line interface, detailing SSD as the main graphical tool for administration and development, with IntelliSense and native debugging.

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.

Use the sql server data tools object explorer to drill into Adventure Works 2012 databases, tables, and columns, and view details and diagrams.

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.

Script database schemas and tables with Object Explorer to produce DDL that recreates the structure without data, and generate a full database script or copy using the wizard.

Explore SQL Server Data Tools templates, including pre-created templates with parameters, and learn to create, store, and execute your own by stepping through parameter values via the Template Explorer.

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.

Open a sql server query tab in sql server 2012 by right-clicking the database and selecting new query, using the database query icon, or via file > new database query.

Open a new query from the database context or file menu, configure fonts and colors, enable line numbers and Intellisense, and switch query results between grid and text.

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.

Create and organize SQL Server projects and solutions in SQL Server Data Tools, manage new connections and queries, and understand how solution and project files link related scripts.

Explore how server data tools can be customized, including font, color, size, and line numbers. See query execution and results behavior and gui options customized in sequel's server data tools.

Explore how to customize SQL Server Data Tools startup and design-time behavior, including Object Explorer layouts, text editor options, batch separators, and governor cost limit settings.

Debug T-SQL with SQL Server Data Tools to walk through query execution, view variable values, and understand the execution context before the final result set is shown.

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.

Use sqlcmd to query and configure SQL Server, automate database creation with scripts and bat files, and leverage the dedicated admin connection (dac) for troubleshooting when the server locks up.

Use sqlcmd, the sql server command line interface, to run batch directives, connect to a server and instance, and pipe results from input files to a text file from AdventureWorks.

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.

Explore the three primary t-sql categories: ddl for create, alter, drop; dcl for security permissions; and dml for querying and modifying data.

Explore common T-SQL elements such as comments, batch terminators, variables, control flow, expressions, functions, predicates, and operators, and examine each in detail in this module.

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.

Learn how the line terminator is defined as a semicolon and why future SQL releases may require terminating all statements with a semicolon.

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 variables by name and data type, using a single @ for local scope or @@ for global scope. In 2008, declare and assign could be in the same statement.

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.

Learn how operator precedence determines the evaluation order in predicates, ensuring results align with intent and avoiding skewed outcomes, with guidance from Books Online, MSDN, and Technet.

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.

Learn how to alias columns in SQL Server queries using the as keyword (optional), and see how aliases appear in the result set with examples like dt and f name.

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.

Discover the non-linear execution order of a select statement in SQL Server, starting with from, then where, group by, having, and finally order by, and how it affects aliasing.

Explore the order of execution and how column aliases behave, showing why aliases can appear in order by but not in where.

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.

Gain an overview of SQL elements, including comments, batched directives with go statements, and ANSI line terminator. Review functions, control flow, and basic select statements, plus order by and where.

Explore the select statement through an introduction and practical steps to modify results of queries.

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

Explore writing a basic select statement with a single argument or scalar function, use the from clause, and choose between select * or specific columns for best practice.

Examine select syntax in SQL Server, using the use statement to switch Adventure Works 2012, and select from system functions, a scalar valued function, and from a table or view.

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.

Alias columns and tables to simplify queries and avoid rewriting, using as or just a space. Learn how derived column names affect ordering and when aliasing helps readability.

Learn to alias columns in SQL Server 2012 by creating a derived column that concatenates first and last names with a space, and aliasing it with the as keyword (optional).

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.

Master ordering results, providing distinct values, and concatenating values to present data clearly. Apply these techniques to improve query readability and data presentation.

Order results using the order by clause on last name, first name, and middle name. Choose ascending order by default, and specify descending order after a column to reverse it.

Explore ordering result sets with the order by clause in SQL Server 2012, sorting by last name, then first and middle names, ascending or descending.

Ordering by the telephone number can be very expensive. A clustered index on last name, first name, and middle name makes ordering by those columns cheaper.

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.

See how distinct returns unique rows for first, middle, and last names. Note that the business entity id, a primary key, shows that distinct applies to the entire row.

Explore the order of execution for SQL Server select statements—from the from clause to the where, group by, and order by clauses—plus aliasing, distinct usage, and when duplicates are removed.

Explore the order of execution for select statements, including from, where, group by, having, and order by, and understand column and table aliasing, distinct, and default ordering.

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.

Use the between operator to test whether two expressions fall within inclusive bounds set by two values, such as orders between May 1 and May 2, 2008.

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.

Master the SQL Server 2012 like operator for partial string matches using wildcards % and _. Filter by last name patterns, such as begins with a or contains a.

Explore data type concepts in SQL Server 2012, including character literals, numeric conversions, and casting or converting date and time values, with datepart and year functions for precise 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.

Explore the top and offset fetch features introduced in sql server 2012 and how they relate to exam preparation.

Use the top clause to return the top results from a table or view, with order by and awareness of clustered indexes or primary keys for most recent orders.

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.

Learn how to query SQL Server by joining multiple tables and applying set operators to combine results.

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.

Explore the categories of joins in SQL Server 2012, including inner joins, outer joins (left, right, full), cross joins, and self-joins, and learn how join conditions determine the resulting rows.

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 outer joins return all rows from one or both tables, including left, right, and full joins, and how nonmatching rows yield nulls.

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.

Master set operators in SQL Server 2012, including union, union all, intersect, and except, in set operators part 1.

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.

Explore SQL Server 2012 ANSI set operators, including union, union all, intersect, and except, with same column counts, compatible data types, and alternatives like not in and join rewrites.

Explore set operator precedence from intersect through union, union all, and except as they’re evaluated by order of appearance, and note how table sample is drawn from the from clause.

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.

Explore exam scenarios for SQL Server joins, aliases, and on predicates, and compare inner, left outer, right outer, and cross joins, plus set operators like union, union all, and except.

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 subqueries as queries within queries, returning scalar, multi-valued, or table results, usable in select, from, or where clauses, with alternatives like common table expressions, table variables, and temporary tables.

Explore self-contained subqueries running without the outer query, returning scalar, multi-valued, or table-valued results in select, from, and where clauses, with an average unit price example from the sales schema.

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 self-contained and correlated subqueries, including scalar valued and table-valued derived tables, with examples like average unit price and max order date. Witness multi-value predicates and derived tables.

Explore an introduction to exists and learn how to implement exists within subqueries to filter results in SQL queries.

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 exists and not exists in correlated subqueries, illustrating outer and inner queries with sales data and product subcategories, and showing true/false outcomes and how joins can replace these patterns.

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

Define a common table expression as a temporary named result set created with a select statement and use it immediately in delete, insert, update, merge, or select.

Create temporary tables as local or global to control scope and visibility, prefixing with one or two pounds and storing them in the temp database.

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.

Learn how to use common table expressions, temporary tables, and table variables, including creating a CTE named topsails, working with tempdb, and understanding scope and batch boundaries.

Explore common table expressions, temporary tables in tempdb, and table variables, focusing on creation, scope (local vs global), and how batch directives affect lifetime and statistics.

Compare correlated and self-contained subqueries, including exists in where clause, and review scalar, multi-valued, and table-valued results in select, where, and temporary objects as table variables and common table expressions.

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 rowset objects and window functions in SQL Server, tracing evolution from 2005 to 2012 with ranking functions like row_number and ntile, extensions to group by, and exam-focused 2012 features.

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 aggregate functions, max, average, sum, and count, on line totals from joined product and sales order detail tables, including distinct counts and handling null values.

Master group by usage for aggregate results and enforcing group by clauses, then learn grouping sets, cube, and rollup for multiple subtotals in one query.

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 the having clause filters groups after a group by, using aggregates like count(*) > 100 to surface products with 100 or more sales.

Discover how the having clause filters groups from group by using counts to show products with 100+ sales and salespeople with 200+ appearances, and why having requires a group by.

Explore data type conversion and learn how to use cast, convert, parse, and the try parse and try convert functions in SQL Server 2012.

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.

Explore casting and converting data in SQL Server 2012, including cast, convert, parse, try parse, and try convert, with date, time, and currency formats.

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.

Explore T-SQL programming by examining logical functions and the case statement, then creating objects: views, stored procedures, functions, and triggers, followed by error handling.

Explore logical functions in SQL Server 2012, including introductions to the choose and case functions.

Test expressions for valid numeric or date types using logical functions. Return values of 1 or 0 and integrate into select statements or case or I am statement.

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.

Demonstrate the choose function to pick an indexed element and return null when out of bounds, then illustrate a case statement using when, else, and or operators for value-based categorization.

Explore views, including creation, alteration, dropping, and partitioned views; then learn to create, alter, and drop stored procedures and functions, plus an introduction to DML triggers.

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.

Learn how SQL Server 2012 stored procedures encapsulate logic, accept input parameters, return values and statuses, use temporary tables, and support create, alter, and drop via DDL.

Explore SQL Server functions: scalar valued, inline table valued, and multi statement table valued; learn parameters, from clause usage, DDL operations, and schema qualifications for robust function design.

Master triggers in SQL Server 2012, including after and instead of triggers, to enforce business rules and referential integrity, tied to the firing transaction and potentially costly.

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.

Master exam scenarios for SQL Server 2012 by applying logical and comparison operators, including if, case, and choose, while noting views, functions, stored procedures, triggers, and their limitations.

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 SQL Server transaction concepts, including insert, update, delete, truncate, and merge, and learn how to manage transactions effectively.

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

Learn how transactions guarantee atomicity, consistency, isolation, and durability in SQL Server 2012, with commit and rollback semantics, locking, and transaction log management during checkpoints.

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.

Explain delete and truncate for removing rows; truncate is more efficient and less logged but limited by foreign keys, while merge handles inserts, updates, and deletes in one statement.

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.

Explore how the merge statement unifies insert, update, and delete within one statement and how the output clause can route affected rows to audit or temp tables.

Begin, commit, and rollback transactions to manage batch operations, handle errors, and ensure exact abort control within SQL Server 2012.

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 how XACT_ABORT governs automatic rollback of a current transaction on runtime errors, and implement begin try/catch with commit and rollback to maintain ACID properties in batches.

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.

Demonstrate managing transactions in SQL Server with begin tran and commit, enforce referential integrity via foreign keys, and handle errors via try catch and exact abort to roll back batches.

Explore exam scenarios for querying SQL Server 2012, covering transactions, inserts/updates/deletes, merge, begin tran, commit tran, rollback tran, batch and try blocks, and locking hints and session options.

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.