Enterprise Database Systems
Microsoft SQL Server 2012: Querying
Microsoft SQL Server 2012: Creating Database Objects
md_mssq_a01_it_enus
Microsoft SQL Server 2012: Creating Functions and Triggers
md_mssq_a04_it_enus
Microsoft SQL Server 2012: Creating Programming Objects and Optimizing Queries
md_mssq_a05_it_enus
Microsoft SQL Server 2012: Managing XML Data
md_mssq_a06_it_enus
Microsoft SQL Server 2012: Manipulate Data Using Operators and Functions
md_mssq_a03_it_enus
Microsoft SQL Server 2012: Querying Basics and Modifying Data
md_mssq_a02_it_enus
Microsoft SQL Server 2012: Creating Database Objects
Lesson Objectives
Microsoft SQL Server 2012: Creating Database Objects
- choose the appropriate data type to use, in a given scenario
- create a basic table
- modify a basic table
- implement a constraint in a table
- create and modify tables and constraints
- create an index
- modify an index
- create a view
- create indexes and views
Overview/Description
SQL Server 2012 is a robust database system that provides a number of database objects used to store and view data. In order to configure a SQL Server 2012 database to meet business requirements, administrators and developers must fully understand how to design, create, and configure these database objects. These objects include tables for storing data in the database, views to allow access to the data within specific tables, and indexes to optimize the performance of the database for data retrieval. This course identifies each of these database objects and demonstrates the steps required to create them using Transact-SQL statements. This course is one of a series in the SkillSoft learning path that covers the objectives for the exam 70-461: Microsoft SQL Server 2012: Querying. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.
SQL Server 2012 is a robust database system that provides a number of database objects used to store and view data. In order to configure a SQL Server 2012 database to meet business requirements, administrators and developers must fully understand how to design, create, and configure these database objects. These objects include tables for storing data in the database, views to allow access to the data within specific tables, and indexes to optimize the performance of the database for data retrieval. This course identifies each of these database objects and demonstrates the steps required to create them using Transact-SQL statements. This course is one of a series in the SkillSoft learning path that covers the objectives for the exam 70-461: Microsoft SQL Server 2012: Querying. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.
Target Audience
Candidates for this path should have two or more years of experience using SQL Server 2012 or a previous version of SQL Server, and they are seeking to prove their proficiency in writing queries. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work as database developers.
Candidates for this path should have two or more years of experience using SQL Server 2012 or a previous version of SQL Server, and they are seeking to prove their proficiency in writing queries. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work as database developers.
Prerequisites: none
Microsoft SQL Server 2012: Creating Functions and Triggers
Lesson Objectives
Microsoft SQL Server 2012: Creating Functions and Triggers
- control the sequence of statement execution using control-of-flow language
- match the transaction modes to their descriptions
- create and modify user-defined functions
- create a DML trigger
- modify an existing DML trigger
- specify the sequence of statement execution for a given scenario
- create and modify functions and triggers in a given scenario
Overview/Description
In SQL Server 2012 you can use Transact-SQL statements to access data in the databases. Transact-SQL statements can be grouped together in a number of ways to create more complex queries when one Transact-SQL statement will not retrieve the desired results. You can group Transact-SQL statements by creating programming objects within a database. These programming objects can include user-defined functions and DML triggers. When creating programming objects Transact-SQL provides control-of-flow keywords to ensure transaction are completed in a specific sequence and transaction are completed accordingly without errors. This course demonstrates how to use the control-of-flow language keywords, transaction modes, and how to create user-defined functions, including table-valued functions and DML triggers. This course is one of a series in the SkillSoft learning path that covers the objectives for the exam 70-461: Microsoft SQL Server 2012: Querying. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.
In SQL Server 2012 you can use Transact-SQL statements to access data in the databases. Transact-SQL statements can be grouped together in a number of ways to create more complex queries when one Transact-SQL statement will not retrieve the desired results. You can group Transact-SQL statements by creating programming objects within a database. These programming objects can include user-defined functions and DML triggers. When creating programming objects Transact-SQL provides control-of-flow keywords to ensure transaction are completed in a specific sequence and transaction are completed accordingly without errors. This course demonstrates how to use the control-of-flow language keywords, transaction modes, and how to create user-defined functions, including table-valued functions and DML triggers. This course is one of a series in the SkillSoft learning path that covers the objectives for the exam 70-461: Microsoft SQL Server 2012: Querying. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.
Target Audience
Candidates for this path should have two or more years of experience using SQL Server 2012 or a previous version of SQL Server, and they are seeking to prove their proficiency in writing queries. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work as database developers.
Candidates for this path should have two or more years of experience using SQL Server 2012 or a previous version of SQL Server, and they are seeking to prove their proficiency in writing queries. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work as database developers.
Prerequisites: none
Microsoft SQL Server 2012: Creating Programming Objects and Optimizing Queries
Lesson Objectives
Microsoft SQL Server 2012: Creating Programming Objects and Optimizing Queries
- recognize the characteristics of stored procedures
- execute and modify a stored procedure in a given scenario
- employ error handling techniques
- use cursors to focus query results
- use synonyms in a given scenario
- create program objects for a given scenario
- describe the characteristics of execution plans
- creating a plan guide
- create and update statistics in a given scenario
- describe the characteristics of concurrency in SQL Server 2012
- describe the characteristics of locking in SQL Server 2012
- describe how queries can be optimized in a given scenario
- improve query performance in a given scenario
Overview/Description
SQL Server 2012 allows programming objects to be created to group multiple statements together. These objects work with data in the databases. One type of programming object is the stored procedure. Stored procedures can help reduce network traffic between clients and servers by enabling more than one statement to be set to the server at a time. They do this by executing statements as batches. They also provide greater security. Within stored procedures and other programming objects such as functions and triggers, error handling can be implemented to enable errors to be caught and corrected or returned to the user. Cursors and synonyms can also be used in SQL Server 2012. During user interactions with the database resource SQL Server implements certain mechanisms to ensure the integrity of the database, which includes concurrency control and locking. This course demonstrates how to create stored procedures, cursors, and synonyms, as well as how to implement error handling. It also discusses how to view and read execution plans, create plan guides and how concurrency control is handled in SQL Server 2012. This course is one of a series in the SkillSoft learning path that covers the objectives for the exam 70-461: Microsoft SQL Server 2012: Querying. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.
SQL Server 2012 allows programming objects to be created to group multiple statements together. These objects work with data in the databases. One type of programming object is the stored procedure. Stored procedures can help reduce network traffic between clients and servers by enabling more than one statement to be set to the server at a time. They do this by executing statements as batches. They also provide greater security. Within stored procedures and other programming objects such as functions and triggers, error handling can be implemented to enable errors to be caught and corrected or returned to the user. Cursors and synonyms can also be used in SQL Server 2012. During user interactions with the database resource SQL Server implements certain mechanisms to ensure the integrity of the database, which includes concurrency control and locking. This course demonstrates how to create stored procedures, cursors, and synonyms, as well as how to implement error handling. It also discusses how to view and read execution plans, create plan guides and how concurrency control is handled in SQL Server 2012. This course is one of a series in the SkillSoft learning path that covers the objectives for the exam 70-461: Microsoft SQL Server 2012: Querying. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.
Target Audience
Candidates for this path should have two or more years of experience using SQL Server 2012 or a previous version of SQL Server, and they are seeking to prove their proficiency in writing queries. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work as database developers.
Candidates for this path should have two or more years of experience using SQL Server 2012 or a previous version of SQL Server, and they are seeking to prove their proficiency in writing queries. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work as database developers.
Prerequisites: none
Microsoft SQL Server 2012: Managing XML Data
Lesson Objectives
Microsoft SQL Server 2012: Managing XML Data
- describe the considerations involved in storing XML data directly within SQL Server 2012
- recognize how to implement XML data storage in SQL Server 2012
- describe the characteristics of indexes
- recognize how to handle XML data in different scenarios
- recognize how XPath is used
- recognize how to query XML data using XQuery
- recognize the modes that can be specified for a FOR XML clause
- distinguish between regular and nested XML queries
- retrieve and convert data using the OPENXML statement
- use SQL Server 2012's XML integration features
- recognize how to manage XML data in SQL Server 2012
- control how XML-related queries are returned
Overview/Description
XML has been integrated into SQL Server 2012 so that XML data can be stored within databases using the xml data type. SQL Server 2012 also allows for relational data already stored in a database to be changed to XML, and for data to be loaded as XML using various methods. You can query this data using the XQuery language within the XML data type methods. SQL Server 2012 also provides support for using FOR XML to extract relational data from a database in XML format and the use of OPENXML to generate relational views from XML. This course demonstrates how to use the xml data type, XML schema collations, and how to load XML data and how to create XML indexes. It also explores how to retrieve data using FOR XML, process XML data using OPENXML, and how to query data using the XQuery language. This course is one of a series in the SkillSoft learning path that covers the objectives for the exam 70-461: Microsoft SQL Server 2012: Querying. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.
XML has been integrated into SQL Server 2012 so that XML data can be stored within databases using the xml data type. SQL Server 2012 also allows for relational data already stored in a database to be changed to XML, and for data to be loaded as XML using various methods. You can query this data using the XQuery language within the XML data type methods. SQL Server 2012 also provides support for using FOR XML to extract relational data from a database in XML format and the use of OPENXML to generate relational views from XML. This course demonstrates how to use the xml data type, XML schema collations, and how to load XML data and how to create XML indexes. It also explores how to retrieve data using FOR XML, process XML data using OPENXML, and how to query data using the XQuery language. This course is one of a series in the SkillSoft learning path that covers the objectives for the exam 70-461: Microsoft SQL Server 2012: Querying. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.
Target Audience
Candidates for this path should have two or more years of experience using SQL Server 2012 or a previous version of SQL Server, and they are seeking to prove their proficiency in writing queries. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work as database developers.
Candidates for this path should have two or more years of experience using SQL Server 2012 or a previous version of SQL Server, and they are seeking to prove their proficiency in writing queries. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work as database developers.
Microsoft SQL Server 2012: Manipulate Data Using Operators and Functions
Lesson Objectives
Microsoft SQL Server 2012: Manipulate Data Using Operators and Functions
- describe the features of functions in SQL Server 2012
- use built in functions in SQL Server 2012
- determine the kind of scalar function required in a given scenario
- manipulate data using built-in functions
- manage results sets
- return a single value by evaluating conditions
- describe the considerations involved in creating summary reports
- change a table-valued expression using the PIVOT and UNPIVOT operators in a given scenario
- use the results returned from a subquery in an outer query
- use operators and table expressions to work with data in given scenarios
- determine which expression should be used in a given scenario
Overview/Description
Microsoft SQL Server 2012 Transact-SQL offers advanced query techniques, such as built-in aggregate, analytic, scalar, and ranking functions, as well as common table expressions, or CTEs and derived tables to manipulate the result set for a query. To manipulate the result sets and generate summarization information, such as totals, the CUBE, GROUPING SETS, and ROLLUP operators can be used with the GROUP BY statement. The UNION, INTERSECT, and EXCEPT operators can be used to combined the result set of multiple queries. SQL Server 2012 also offers the use of CTEs and derived tables to obtain a result set, temporarily store it, and then pass it to a query to return another result set based on that information. This course demonstrates how to perform advanced query techniques to manipulate data using the SQL Server 2012 built-in functions, operators, and table expressions. This course is one of a series in the SkillSoft learning path that covers the objectives for the exam 70-461: Microsoft SQL Server 2012: Querying. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.
Microsoft SQL Server 2012 Transact-SQL offers advanced query techniques, such as built-in aggregate, analytic, scalar, and ranking functions, as well as common table expressions, or CTEs and derived tables to manipulate the result set for a query. To manipulate the result sets and generate summarization information, such as totals, the CUBE, GROUPING SETS, and ROLLUP operators can be used with the GROUP BY statement. The UNION, INTERSECT, and EXCEPT operators can be used to combined the result set of multiple queries. SQL Server 2012 also offers the use of CTEs and derived tables to obtain a result set, temporarily store it, and then pass it to a query to return another result set based on that information. This course demonstrates how to perform advanced query techniques to manipulate data using the SQL Server 2012 built-in functions, operators, and table expressions. This course is one of a series in the SkillSoft learning path that covers the objectives for the exam 70-461: Microsoft SQL Server 2012: Querying. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.
Target Audience
Candidates for this path should have two or more years of experience using SQL Server 2012 or a previous version of SQL Server, and they are seeking to prove their proficiency in writing queries. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work as database developers.
Candidates for this path should have two or more years of experience using SQL Server 2012 or a previous version of SQL Server, and they are seeking to prove their proficiency in writing queries. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work as database developers.
Prerequisites: none
Microsoft SQL Server 2012: Querying Basics and Modifying Data
Lesson Objectives
Microsoft SQL Server 2012: Querying Basics and Modifying Data
- describe how the SELECT statement is used to retrieve data
- describe the characteristics of subqueries
- use a join condition to define how tables are related
- retrieve data from SQL Server database in a given scenario
- alter the data in a given scenario
- use the MERGE and OUTPUT statements to change the contents of a database in a given scenario
- modify the contents of a databases in a given scenario
Overview/Description
SQL Server 2012 includes the SQL Server Management Studio which allows access to SQL Server databases and enables you to access database data. This data can be retrieved and modified through the use of Transact-SQL within the Query Edit window of the Management Studio tool. Using Transact-SQL you can select data from a database’s tables and filter the results to include only the data that you require. Transact-SQL statements also allow you to update, change, and remove data from tables. This course identifies the basic Transact-SQL statements that can be used to retrieve and modify data within SQL Server databases,which includes the DML statements SELECT, INSERT, UPDATE, DELETE, and MERGE. This course also demonstrates how to use joins in queries in combine data from multiple tables and how to use subqueries. This course is one of a series in the SkillSoft learning path that covers the objectives for the exam 70-461: Microsoft SQL Server 2012: Querying. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.
SQL Server 2012 includes the SQL Server Management Studio which allows access to SQL Server databases and enables you to access database data. This data can be retrieved and modified through the use of Transact-SQL within the Query Edit window of the Management Studio tool. Using Transact-SQL you can select data from a database’s tables and filter the results to include only the data that you require. Transact-SQL statements also allow you to update, change, and remove data from tables. This course identifies the basic Transact-SQL statements that can be used to retrieve and modify data within SQL Server databases,which includes the DML statements SELECT, INSERT, UPDATE, DELETE, and MERGE. This course also demonstrates how to use joins in queries in combine data from multiple tables and how to use subqueries. This course is one of a series in the SkillSoft learning path that covers the objectives for the exam 70-461: Microsoft SQL Server 2012: Querying. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.
Target Audience
Candidates for this path should have two or more years of experience using SQL Server 2012 or a previous version of SQL Server, and they are seeking to prove their proficiency in writing queries. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work as database developers.
Candidates for this path should have two or more years of experience using SQL Server 2012 or a previous version of SQL Server, and they are seeking to prove their proficiency in writing queries. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work as database developers.
Prerequisites: none
