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.

Learn how to perform a SQL Server 2012 standalone installation on Windows 7, including setup, licensing, updates, instance configuration, and enabling database engine and management tools.

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.

Explore the Windows 7 interface for SQL Server 2012, compare with Windows 8 and Server 2012, and learn to open SQL Server Management Studio and start a new query.

Attach the AdventureWorks2012.mdf sample database to a non-production SQL Server to build, test queries, run DBCC CHECKDB, and assess security risks of untrusted attachments.

Attach the AdventureWorks 2012 sample database to SQL Server by placing the mdf file in the data folder and removing the log file if needed.

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.

Install SQL Books Online locally by downloading or unzipping the documentation, configuring SSMS help settings, and linking to a local or network library for offline access.

Navigate the SQL Server Management Studio interface and connect to a 2012 SQL Server using Windows authentication. Open the New Query window to write and execute queries.

Learn to connect to a SQL Server using SQL Server Management Studio with Windows authentication, connect to the Database Engine by server name or IP address, and manage multiple servers.

Learn to customize SQL Server Management Studio (SSMS) by adjusting window layout, pinning panels, managing tools, and resetting layout for a productive query environment.

Learn to enable and use database diagrams to visualize table relationships and keys in AdventureWorks 2012, create diagrams, and inspect column data types.

Save and manage database diagrams by saving changes, naming diagrams, creating new diagrams from selected tables, and refreshing to load updates from the database.

Explore sql server management studio’s text and graphical query tools to build, view, and refine select statements, and visualize joins for complex queries.

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.

Identify what database objects are, including tables, views, indexes, and stored procedures, and learn how permissions and properties govern access and management in SQL Server 2012.

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.

Understand data integrity as the correctness, consistency, and uniqueness of data in a table. Learn how primary keys, data types, and constraints prevent duplicates and guide reliable queries.

Understand data types and their impact on t-sql queries, and see how columns enforce data integrity, from exact numerics like int and big int to overflow risks.

Explore how primary keys enforce unique rows, the tradeoffs of single vs composite keys on first name, last name, and age, and how key design affects inserts, updates, and relationships.

Use constraints in Transact-SQL to enforce data integrity with primary key, foreign key, check, and unique constraints; constrain data ranges, avoid duplicates, and manage table relationships.

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.

Learn how stored procedures in Microsoft SQL Server act as reusable, permission-controlled code blocks that centralize functionality, accept parameters, and improve performance across applications.

Understand how triggers in SQL Server work as automatically fired stored procedures that run on insert, update, or delete actions to enforce rules and modify data.

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.

Learn to create a table in SQL Server 2012 using T-SQL or graphical tools, defining personID int, LastName varchar(50), FirstName varchar(25), and age tinyint.

Create a table graphically in sql server management studio, defining personid integer not null, last name varchar(50) not null, and age, then compare with t sql.

Learn to modify a table's structure using the alter table command, add a level column (tinyint null), and disable constraints or triggers via code in SQL Server Management Studio.

Explore graphically altering a table in SQL Server Management Studio, including adding and repositioning columns, the risk of table recreation, and the need for backups in production environments.

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.

Explore the four basic TSQL queries—select, insert, update, and delete—and learn how to retrieve and manipulate data across tables and views using practical query structures.

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.

Master good query structure to boost SQL Server performance in production by avoiding select asterisk, unnecessary columns and joins, and by using where clauses with schema.table naming and proper permissions.

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.

Use the USE command to set the database context for your queries, preventing invalid object name errors when switching from master to AdventureWorks2012.

Learn how the GO command marks batch boundaries in SQL Server, affecting variable scope and the order of table creation and execution.

Master the basics of the select statement to retrieve data from one or more tables, apply where conditions, format results, and understand select permissions and roles like db_datareader and sysadmin.

Learn the basic select structure, including select, from, where, group by, having, and order by, and how they return columns, filter data, and form grouped summaries.

Master the where clause to define conditions and limit rows in select, update, and delete queries using operators such as equal, not equal, greater than, and like.

Learn how to use the top command to limit rows in SQL Server queries, including top n, top n% and with ties, and why adding order by ensures predictable results.

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 to handle nulls in sql queries with isnull and isnotnull, recognizing that null is unknown and cannot equal anything, with practical filtering and ordering in AdventureWorks 2012 database.

Learn how to use T-SQL concatenation with the plus operator to join first and last names, insert spaces with literals, and create readable, real world like results such as addresses.

Learn how column aliasing renames query output columns for readability and reporting in Transact-SQL. Use the as keyword or omit it in SQL Server Management Studio queries to improve clarity.

Learn how the distinct keyword prevents duplicates in SQL Server queries, returning the first instance per selected columns and supporting average and sum, with practical demos.

Explore the sum aggregate function by totaling line totals from the sales order detail, aliasing the result, and using distinct values to refine data subsets.

Learn how to use the AVG aggregate function in SQL Server to compute averages while ignoring nulls, with examples from sales order detail and aliasing the result.

Learn how to use the count aggregate function in SQL Server 2012 to count rows, apply where filters like specialofferid, and combine with sum and average for reporting.

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.

Demonstrate the having clause as a group-by filter by summing sales ytd per country region code and showing only groups over six million.

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.

Use union to combine results from two or more queries into a result set. By default duplicates are removed; use union all to include duplicates, requiring matching columns across queries.

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.

Learn how synonyms provide abstraction in SQL Server by linking a synonym to Test100 and re-pointing it to Test200 after renaming, ensuring existing scripts remain functional.

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.

Explore ranking in Transact-SQL, a window function that assigns a rank to each row within partitions, using RANK, DENSE_RANK, NTILE, and ROW_NUMBER, with practical examples.

Explore ranking with window functions in SQL Server 2012, using rank over partition by and order by to analyze rates and quantities across locations and business entities.

Learn advanced ranking techniques in SQL Server using dense_rank, ntile, and row_number with practical examples, including handling ties, dividing results into groups, and combining all in one query.

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.

Master join query structure by building simple two-table queries with aliases, selecting first and last names alongside policy types, and applying on CID for left and right joins.

Learn how inner joins connect two tables on primary and foreign key equality to return matching rows, using aliases and selecting first name, last name, and phone number.

Learn to create inner joins by aliasing tables, and use from, inner join, and on to join Person.Person and Person.PersonPhone on BusinessEntityID.

Explore easier join creation through a graphical SQL Server tool, using design query and editor to build inner and outer joins across Person and PersonPhone.

Explore how left, right, and full outer joins return rows with nulls for unmatched columns, and see practical examples using customer and order data in SQL Server.

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 the insert statement, the main way to add data in SQL Server, including values, select, and exec forms, with syntax, constraints, and proper column order.

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.

Master insert statements in SQL Server by using insert into with a select and by calling stored procedures, and you load 17 rows into the employee_sales table.

This lecture covers how the update statement modifies data in tables and views, behind-the-scenes delete and insert, rechecking constraints and primary keys, and using a where clause to limit updates.

Learn how to use the update statement in SQL Server, including setting multiple columns and using a where clause, with tests via select to avoid mass changes.

Master the delete statement in SQL Server, including delete from schema.table where conditions to avoid unintended mass deletions; understand when to use truncate table and the role of transactions.

Learn to safely delete data in SQL Server by using a test table created from AdventureWorks 2012 and a where clause to target specific rows.

Learn a safe approach to deleting records by converting delete statements into select checks to verify matching rows before execution. Apply safeguards to prevent accidental data loss in high-risk environments.