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

Administering Microsoft SQL Server 2014 Databases

In this course, you will learn the skills required to maintain a Microsoft SQL Server 2012 or 2014 database by focusing on data storage, backup and restoration, importing and exporting data, security, database maintenance and the SQL Server Agent.

This course incorporates material from the Official Microsoft Learning Product 20462: Administering Microsoft SQL Server 2014 Databases. It covers the skills and knowledge measured by Exam 70-462 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 2008 R2 for Administration

This instructor-led course provides you with the knowledge and skills to maintain a Microsoft SQL Server 2008 R2 database. You will learn to use SQL Server 2008 R2 product features and tools to install and configure a database, manage database files, and create and tune indexes.

This course incorporates materials from the Official Microsoft Learning Product 6231: Maintaining a Microsoft SQL Server 2008 R2 Database.

Database Training
SQL

Implementing Data Models and Reports with Microsoft SQL Server

In this course, you will learn the tools needed to create managed enterprise business intelligence (BI) solutions. You will learn about data modeling by creating multidimensional databases, cubes and dimensions, measures and measure groups, MDX fundamentals, analysis services, and Data Analysis Expressions (DAX). You will then learn how to create and publish reports using reporting services, report execution and delivery, SharePoint PerformancePoint Services, and data mining.

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

Database Training
SQL

Designing Business Intelligence Solutions with Microsoft SQL Server 2012

In this course, you will learn how to design, install, and maintain business intelligence (BI) infrastructures, including data warehouse, an extract, transform, and load (ETL), Analysis Services, Reporting Services, and SharePoint Server.

This course incorporates material from the Official Microsoft Learning Product M20467B: Designing Business Intelligence Solutions for Microsoft SQL Server 2012.