SQL Scripting: Level 2 – Querying & Manipulating Data

SQL Scripting: Level 2 - Querying & Manipulating Data

COURSE OUTLINE:

Description In 'SQL Scripting: Level 1 - Fundamentals of Querying', you examined the fundamentals of SQL querying. Because of various business conditions and requirements, in addition to querying the database, you may need to restructure and index tables. You may also need to manipulate the data in the tables. In this instructor-led course, you will create advanced SQL queries, and manipulate and index tables.

Audience
Students should have basic computer skills, SQL skills, and be familiar with concepts related to database structure and terminology.

Prerequisites
Completion of SQL Scripting: Level 1 or equivalent knowledge recommended.

Learning Objectives
Upon successful completion of this course, students will be able to:

use subqueries to generate query output.
manipulate table data by inserting and updating records in a table and deleting records from a table.
manipulate the table structure.
create views, manipulate data through views, modify the view structure, and drop views.
create indices on table columns and drop inefficient indices.
mark the beginning of a transaction, create a savepoint within a transaction, rollback a transaction, and commit a transaction.

Course Content

Lesson 1: Using Subqueries to Perform Advanced Querying

Topic 1A: Search Based on Unknown Values
Topic 1B: Compare a Value with Unknown Values
Topic 1C: Search Based on the Existence of Records
Topic 1D: Generate Output Using Correlated Subqueries
Topic 1E: Filter Grouped Data Within Subqueries
Topic 1F: Perform Multiple-Level Subqueries

Lesson 2: Manipulating Table Data

Topic 2A: Insert Data
Topic 2B: Modify and Delete Data

Lesson 3: Manipulating the Table Structure

Topic 3A: Create a Table
Topic 3B: Create a Table with Constraints
Topic 3C: Add and Drop Table Columns
Topic 3D: Add and Drop Constraints
Topic 3E: Modify the Column Definition
Topic 3F: Back Up Tables
Topic 3G: Delete Tables

Lesson 4: Working with Views

Topic 4A: Create a View
Topic 4B: Manipulate Data in Views
Topic 4C: Create Aliases
Topic 4D: Modify and Drop Views

Lesson 5: Indexing Data

Topic 5A: Create Indices
Topic 5B: Drop Indices

Lesson 6: Managing Transactions

Topic 6A: Create Transactions
Topic 6B: Commit Transactions

RELATED COURSES

Database Training
SQL

Implementing a Data Warehouse with Microsoft SQL Server 2014

In this course, you will learn how to implement a data warehouse platform to support a business intelligence (BI) solution. You will discover how to create a data warehouse, implement extract, transform, and load (ETL) with SQL Server Integration Services (SSIS), and validate and cleanse data with SQL Server Data Quality Services (DQS) and SQL Server Master Data Services.

This course is designed for customers interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features of SQL Server 2014 as well as the important capabilities across the SQL Server data platform.

This course incorporates material from the Official Microsoft Learning Product 20463: Implementing a Data Warehouse with Microsoft SQL Server 2014. It covers the skills and knowledge measured by Exam 70-463 and along with on-the-job experience, helps you prepare for the exam.

Note: To reflect the inclusion of SQL Server 2014 content in exams 461, 462, and 463, Microsoft Learning has rebranded the MCSA certification to MCSA: SQL Server 2012/2014. As changes to each individual exam are modest in size and SQL Server 2012 skills remain relevant, all current MCSA: SQL Server 2012 certification holders will have their transcripts updated accordingly to reflect the new credential name. Please seehttps://www.microsoft.com/en-us/learning/mcsa-sql-certification.aspx for certification details.

Database Training
SQL

SQL Server Performance Tuning and Optimization

In this course, you will learn practical tuning and optimization techniques for SQL Server 2014 that include indexing and statistics strategies, transaction log operations, tempdb and data file configuration, transaction and isolation levels, locking and blocking, and the new In-Memory OLTP engine. You will learn how to create baselines and benchmark SQL Server performance as well as analyze and fix workload for performance issues.

This course incorporates materials from the Official Microsoft Learning Product 55144: SQL Server Performance Tuning and Optimization.

Database Training
SQL

SQL Scripting: Level 1 – Fundamentals of Querying

If you have access to company databases and your job requires you to retrieve data from those databases, retrieval of information from a database is dependent on precisely ordered logic and specific information. This instructor-led course will help you to use Structured Query Language (SQL) as a tool to implement that logic and define instructions.

Database Training
SQL

Developing Microsoft SQL Server 2014 Databases

In this course, you will be introduced to SQL Server, logical table design, indexing, query plans, and data and domain integrity. You will focus on creating database objects, including views, stored procedures, parameters, and functions. You will also learn procedure coding, such as indexes, concurrency, error handling, triggers, and SQL Common Language Runtime (CLR).

This course incorporates material from the Official Microsoft Learning Product 20464: Developing Microsoft SQL Server 2014 Databases. It covers the skills and knowledge measured by Exam 70-464 and along with on-the-job experience, helps you prepare for the exam.