Автор: Garth Schulte CBT Nuggets Trainer
Год выпуска: 2013
Продолжительность: 11:29:50
Тип материала: Видеоурок
Язык: Английский
Стоимость: 1999 рублей
Описание:
В курсе изучаются вопросы создания и использования баз данных Microsoft SQL Server 2008 R2. Рассматривается реализация хранения данных (таблицы, индексы) и обработки данных (программирование, транзакции, оптимизация) средствами MS SQL Server 2008 R2.
Целевая аудитория курса: лица, в обязанности которых входит администрирование SQL-сервера, а также аналитики, разработчики, специалисты по системам отчётности, программисты 1С и т.п.
[wpspoiler name="Подробное описание" ]
1. Introduction to 70-461: Querying Microsoft SQL Server 2012 (00:35:20)
In this introductory Nugget we take a good look at what's in store for the course. We start with the 70-461 exam objectives from Microsoft and follow that up with a look at how this course is laid out. Next, we get familiar with the SQL Server 2012 express editions and install it on our Nuggetlab virtual machine. Finally we look at how to obtain and attach the AdventureWorks2012 sample database into our instance and show you a few tips on how to manage your queries and scripts using SQL Server projects.
2. Creating and Altering Tables with T-SQL (00:34:26)
You'll learn how to create, modify and delete tables using T-SQL in this Nugget. You'll also learn how to utilize context sensitive help, translate T-SQL syntax, generate and analyze scripts from tables, and learn some tips and tricks in SQL Server 2012 Management Studio along the way.
3. Creating and Altering Views with T-SQL (00:43:22)
This Nugget will show you how to create, modify and delete views using T-SQL. You'll learn how to modify data using views, create and use indexed views, analyze query execution plans and generate sample data for testing purposes.
4. Designing Views (00:32:22)
Understanding when and how to design views will help you create solutions to common problems and simplify data access. This Nugget will give you the know-how to do just that -- you'll start with view design 101 and learn when to use views; next we'll learn how to design views in SQL Server 2012 Management Studio as well as encrypt view definitions. Finally we'll learn how to apply security at the view level to simplify permission management and keep our base tables secure.
5. Creating and Modifying Constraints (00:42:30)
In this Nugget, you'll learn how to create constraints, which gives us a way to shape the data going into our database and ensure data integrity. You'll learn how to create column and table constraints such as defaults, checks, unique indexes, primary keys and foreign keys using T-SQL. You'll also see how to create a database diagram to get a better visual of the overall database design.
6. Creating and Modifying DML Triggers (00:46:11)
DML Triggers allow us to respond to data modifications on our tables in real-time and are a great tool for any DBA or developer to have in their skill set. This Nugget will show you when and how to create every type of DML trigger for every type of data modification. You'll also learn how to build nested triggers, understand potential performance implications and see how to send an email from inside a trigger using T-SQL.
7. Querying Data using SELECT (00:58:55)
This Nugget will take you on a tour through the SELECT statement and how we use it to return data from the database. You'll start with the basics -- learning the major components of the SELECT statement and follow it up with how to use some extremely useful T-SQL language constructs and built-in functions. You'll also learn how to rank data, build and execute dynamic SQL, and use system metadata to query the database about itself.
8. Implementing Subqueries (00:33:54)
Learning how to write subqueries gives us more ways to design a query. This Nugget will start with the subquery basics and show you where and when we can write nested queries. You'll also learn how to create Common Table Expressions (CTEs) to help us better manage T-SQL code and finish with how to build matrix style results using pivot tables.
9. Implementing Data Types (00:40:16)
Choosing the right data type for storing data, whether it be in table columns or variables, is essential for the integrity, storage and performance of our database. This Nugget gives you an understanding of every data type available in SQL Server 2012 -- you'll learn how to choose the right data type for your database structures, get some common guidelines for working with data types and see first hand the data we can store in each data type.
10. Implementing Aggregates (00:44:03)
This Nugget will show you how to group and aggregate data in a variety of ways. You'll start with the GROUP BY clause to get a handle on the basics of grouping data and incorporate grouping sets for subtotaling our query results. You'll see the new analytic and ranking functions in action while learning how to generate, work with and aggregate spatial data.
11. Querying and Managing XML Data (00:43:24)
XML is a widely adopted format for storing data and this Nugget will show you everything you need to know about XML in SQL Server. The basics cover XML itself -- from schemas to namespaces to how we transform relational data to an XML format. You'll also learn how to load and associate XSD schemas with XML columns and import/export XML using BCP, OPENXML and even SQL Server Integration Services (SSIS).
12. Creating and Altering Stored Procedures (00:33:21)
Stored Procedures allow us to create reusable components on the database server to increase managability and performance of our T-SQL code -- this Nugget will show you the basics and beyond of creating and executing Stored Procedures as well as techniques for building and designing a set of Stored Procedures for use in the data layer of an application.
13. Modifying Data with T-SQL (00:33:08)
This Nugget will show you the basics of data modification with the INSERT, UPDATE and DELETE statement. We'll analyze the syntax of each statement, learn the basic usages and see how to utilize the OUTPUT clause to discover what changes were caused by each statement.
14. Combining Datasets (00:22:49)
Understanding how to work with data from multiple queries will give you another skill to prepare you for anything and everything. This Nugget will show you how to combine, compare and merge results using operators such as UNION, EXCEPT, INTERSECT and MERGE.
15. Working with Functions (00:42:21)
Functions give us a great alternative to reusing code over stored procedures and provide more flexibility in where we can use them. This Nugget will get you familiar with everything functions in T-SQL -- you'll learn how to create scalar and table-value functions, use built-in and system functions and see examples of how to use them in the real world.
16. Optimizing Queries (00:37:24)
Performance is a top priority for databases and many performance issues come from poorly designed queries. This Nugget will show you how to find query performance issues using query execution plans, find slow performing queries in a database using DMVs and give you design guidelines needed to create blazing fast queries.
17. Managing Transactions (00:27:41)
Transactions ensure our data stays in a consistent state and is protected from concurrent processes. This Nugget will show you how to create, manage and work with transactions -- you'll also learn how locking works and see how isolation levels help us when it comes to concurrent operations.
18. Row-based vs. Set-based Operations (00:22:57)
Understanding how T-SQL statements process rows can help you make better design decisions when creating queries. This Nugget will get you familiar with row-based operations such as cursors and loops, when to use them and how to convert them to set-based operations for performance benefits.
19. Implementing Error Handling (00:15:26)
Knowing when and how to handle errors will improve end user and developer experience with the database. This Nugget will show you where, when and how to handle errors using the TRY..CATCH statement. You'll also learn how to generate, store and view custom error messages in SQL Server.
[/wpspoiler]
