Microsoft SQL Server 2012 Certification Training Exam 70-461

Download the free SQL Server 2012 evaluation edition from Microsoft, run it in a virtual machine for 90 days, and follow along hands-on to multiply your learning.

Master the Windows 8 / Server 2012 interface to open SQL Server Management Studio, pin the tool to the taskbar, and quickly switch between start screen and desktop.

Learn how to access and configure help in SQL Server Management Studio, choosing local or online help, installing or downloading Books Online, and staying updated with online content.

Watch how to write simple T-SQL samples, save queries, and reuse code files in the working files folder using SQL Server Management Studio with AdventureWorks 2012.

Explain the semicolon as a statement terminator, why it appears or not in SQL code, and advise adopting semicolon usage for ANSI SQL-92 alignment and future server updates.

Explore tables and core database objects like data types, primary keys, constraints, data integrity, and views, and learn how rows and columns shape data storage and query efficiency.

Explore how null values denote unknown in SQL Server, how to query using is null and is not null, and why nulls affect data design and query results.

Explore how views act as pre-defined, secure virtual tables that expose only selected columns from a base table, enabling data abstraction and permissions-based access control.

Explore synonyms in SQL Server: an alias that provides a second name for a database object, enabling abstraction and remote four-part naming to simplify cross-server queries.

Discover how to drop a table in transact sql, including what is removed and how to identify referencing views or procedures with sys.dm_sql_referencing_entities before dropping.

Explore dropping a table graphically in SQL Server Management Studio, view dependencies, and understand the difference between delete and drop while considering T-SQL re-creation.

Create a primary key during table creation or add it later, as shown in SQL Server Management Studio. Name keys clearly, and explore composite keys on PersonID and LastName.

Grasp the distinction between logical processing and physical processing in SQL, and how the query optimizer parses queries, builds plans, and caches execution plans.

Explore the many options in SQL Server, understand the query optimizer, and learn multiple ways to produce the same result; use books online and user groups to grow.

Explain how SQL Server 2012 handles comments, including two types of comments—double hyphen and forward slash asterisk pairs—inline and multi-line usage, keyboard shortcuts, and the value of documenting code.

Sort query results with the order by clause, choosing ascending or descending order across one or more columns, and understand null handling and offset fetch paging.

Learn how to use group by to group rows into summary rows and apply aggregates to non-grouped columns, pre-aggregating sales data in Adventure Works 2012 for easier reports.

Select into creates a new table from a query’s result set, letting you test results by copying data into a new table in management studio without affecting production.

Explore how like and in work in a where clause to search data with wildcards—percent, underscore, and brackets—and see last names starting with w or a through f.

Explore intersect and except in SQL Server to compare two tables, return distinct matching values, and identify nonmatching rows with practical examples and a hands-on demo.

Discover how synonyms provide alternate names for tables, views, procedures, and functions to create an abstraction layer, and see code-based creation using select into and a synonym alias.

Use offset fetch after order by to paginate results in T-SQL, skipping rows and fetching the next set of rows, offering an alternative to top.

Learn how joins retrieve data from multiple tables using normalization, primary and foreign keys, and inner, outer, and cross join types to ensure data integrity.

Demonstrate cross joins and the cartesian product by pairing five names with five numbers to produce 25 rows, and learn to use select into for test tables.

Learn to use insert into with explicit columns, literals and dates, respect primary key constraints, insert multiple rows, and use insert select to populate a new employee sales table.