If you like watching videos to learn topics, consider Udemy.com This post is about a series of videos at Udemy that are on Querying SQL Server by Phillip Burton. Here is an outline of his series of videos aiming to get you to write and pass the 70-461 exam.
Burton uses some tables as examples with these videos. The database diagram for these are at the post SQL Server Burton’s Database.
70-461 Session 1: Querying Microsoft SQL Server 2012/2014 (4.5 hours)
- Download SQL Server
- Starting SQL Server
- Creating Tables
- Number Types and Functions
- String Data Types and Functions
- Date Data Types and Functions
- Bonus – Installing 2016 Developer Edition
T-SQL (Transact SQL Server) beginners: Create tables, data types + functions. NULL, IIF, CASE WHEN, coalesce, CONCAT, data type precedence (numbers take precedence over strings in implicit conversion), format, datetime2(3), datefromparts(), datetime2fromparts(), year, month, day, hour, minute, seconds, getdate, sysdatetime, dateadd, datepart, datediff.
70-461 Session 2: Querying Microsoft SQL Server 2012/2014 (3.5 hours)
- Creating and Querying Part of a Table
- Summarizing and Ordering Data
- Adding a Second Table
- Find Missing Data, and Delete and Update Data
T-SQL tables, SELECT queries (FROM, WHERE, GROUP BY, HAVING, ORDER BY), JOIN tables, INSERT/DELETE/UPDATE. ALTER columns in the table. Create tables in a database. INSERT new data, UPDATE and DELETE existing data, and export data INTO a new table. JOIN two or more tables together, finding missing data.
70-461 Session 3: Querying Microsoft SQL Server 2012/2014 (3.5 hours)
- Create and Modify Constraints
- Views
- Create and Alter DML Triggers
We learn how to create views, which enable us to store these SELECT queries for future use, and triggers, which allow for code to be automatically run when INSERTing, DELETEing or UPDATEing data. It covers constraints, such as UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY constraints, to stop erroneous data from being added.
70-461 Session 4: Querying Microsoft SQL Server 2012/2014 (3.5 hours)
- Combine Databases
- Create and Alter Stored Procedures
- Implement Try/Catch/Throw
The topics in Combine Databases are UNION, UNION ALL, INTERSECT and EXCEPT, CASE, ISNULL, Coalesce and the mighty MERGE statement. Encapsulate routines by creating procedures, allowing us to EXECUTE parameterized commands with just one statement, and we’ll add some error handling with TRY, CATCH and THROW. It covers how to EXECUTE those procedures, and add OUTPUT parameters to obtain RESULTs. It covers error handling, and combining datasets together.
70-461 Session 5: Querying Microsoft SQL Server 2012/2014 (3.5 hours)
- Implement Aggregate Queries
- Ranking Functions
- New Analytic Functions
- Grouping Sets
- Spatial Aggregates
Implementing aggregate queries includes: OVER(), PARTITION BY and ORDER BY, (running balance, running total) RANGE, CURRENT ROW and UNBOUNDED, RANGE vs. ROWS, Omitting RANGE/ROW. Ranking Functions include: ROW_NUMBER, RANK and DENSE_RANK. New Analytic Functions includes: FIRST_VALUE and LAST_VALUE, LAG and LEAD, CUME_DIST and PERCENT_RANK, PERCENTILE_CONT and PERCENTIAL_DISC. Grouping Sets includes: Adding totals, ROLLUP, GROUPING and GROUPING_ID. Spatial Aggrtegates includes: POINT, POLYGONE and Circles, Line queries, Geography, and Spatial Aggregates.
70-461 Session 6: Querying Microsoft SQL Server 2012/2014 (3.5 hours)
- Sub-queries
- WITH statement
- PIVOTing and UNPIVOTing
- CTE Statement
- Functions
- Synonyms and Dynamics
- GUIDs and Sequences
Some of the topics in session six include: correlated subqueries, CTEs (common table expressions), Pivots, UDFs, unique identifiers and Sequences.
Philip has added section 7.
- Manage Transactions
- Indexes
- Optimize Queries
- Dynamic Management Views and Functions
- Evaluate the use of row-based operations vs. set-based operations
- Congratulations