info@smashgaps.com +91.7391.042.951 020 2565 4714

MS-SQL Server Developer

Microsoft SQL Server Training and Placement - Best Job Oriented IT Training Institutes In Pune, Maharashtra India
Category Name : Database
Certification Name : MCSD
Exam Id : 70-461
Number of Questions : 62
Passing Score : 60%
Time Limit : 120 Mins
Examination Format : MCQ
Count : 10 Seats
Duration: XX Classes | XX Hours | Weekdays: XX Days | Weekends: XX Days
Batch Timings: Mon-Fri: 30 Days | Sat-Sun (Weekend): 8 Weekends Mon-Fri: 9 AM to 11 AM | Sat-Sun (Weekend): 10 AM to 4 PM
Batch Schedule: Starts from 01-Sep-2107
Review

Feature:

This module will teach students how to write necessary Transact-SQL queries for MS SQL Server. This module is planned for Database Developers, Database Administrators, and Business Intelligence professionals. After completing this module, students will be able to: explain the basic architecture and concepts of MS SQL Server and comprehend the differences and similarities between Transact-SQL and other computer languages

Intended Audience:

This course is ideal for individuals interested in building their careers in the profiles such as MS SQL Server Developer and database administrators.

Prerequisites:

Basic understanding of databases

Working knowledge of RDBMS & SQL

Foundations of Querying 

  • Understanding the Foundations of T-SQL                        
  • Understanding Logical Query Processing

Getting Started with the SELECT Statement

  • Using the FROM and SELECT Clauses
  • The FROM Clause
  • The SELECT Clause
  • Delimiting Identifiers
  • Working with Data Types and Built-in Functions
  • Choosing the Appropriate Data Type
  • Choosing a Data Type for Keys
  • Date and Time Functions
  • Character Functions
  • CASE Expression and Related Functions

Filtering and Sorting Data

  • Filtering Data with Predicates
  • Predicates, Three-Valued Logic, and Search Arguments
  • Combining Predicates
  • Filtering Character Data
  • Filtering Date and Time Data
  • Sorting Data Understanding When Order Is Guaranteed
  • Using the ORDER BY Clause to Sort Data
  • Filtering Data with TOP and OFFSET-FETCH
  • Filtering Data with TOP
  • Filtering Data with OFFSET-FETCH

Combining Sets 

  • Using Joins
  • Cross Joins
  • Inner Joins
  • Outer Joins
  • Multi-Join Queries
  • Using Subqueries, Table Expressions, and the APPLY
  • Operator
  • Subqueries
  • Table Expressions
  • APPLY
  • Using Set Operators
  • UNION and UNION ALL
  • INTERSECT
  • EXCEPT

Grouping and Windowing 

  • Writing Grouped Queries
  • Working with a Single Grouping Set
  • Working with Multiple Grouping Sets
  • Pivoting and Unpivoting Data
  • Pivoting Data
  • Unpivoting Data
  • Using Window Functions
  • Window Aggregate Functions
  • Window Ranking Functions
  • Window Offset Functions

Creating Tables and Enforcing Data Integrity

  • Creating and Altering Tables
  • Introduction
  • Creating a Table
  • Altering a Table
  • Choosing Table Indexes
  • Enforcing Data Integrity
  • Using Constraints
  • Primary Key Constraints
  • Unique Constraints
  • Foreign Key Constraints
  • Check Constraints
  • Default Constraints

Designing and Creating Views, Inline Functions and Synonyms

  • Designing and Implementing Views and Inline Functions
  • Views
  • Inline Functions
  • Using Synonyms
  • Creating a Synonym
  • Comparing Synonyms with Other Database Objects

Inserting, Updating, and Deleting Data

  • Inserting Data
  • INSERT VALUES
  • INSERT SELECT
  • INSERT EXEC
  • SELECT INTO
  • Updating Data
  • UPDATE Statement
  • UPDATE Based on Join
  • Nondeterministic UPDATE
  • UPDATE and Table Expressions
  • UPDATE Based on a Variable
  • UPDATE All-at-Once
  • Deleting Data
  • Sample Data
  • DELETE Statement
  • TRUNCATE Statement
  • DELETE Based on a Join
  • DELETE Using Table Expressions

Other Data Modification Aspects

  • Using the Sequence Object and IDENTITY Column Property.
  • Using the IDENTITY Column Property
  • Using the Sequence Object
  • Merging Data
  • Using the MERGE Statement
  • Using the OUTPUT Option
  • Working with the OUTPUT Clause
  • INSERT with OUTPUT
  • DELETE with OUTPUT
  • UPDATE with OUTPUT
  • MERGE with OUTPUT
  • Composable DML

 Designing and Implementing T-SQL Routines

  • Designing and Implementing Stored Procedures
  • Understanding Stored Procedures
  • Executing Stored Procedures
  • Branching Logic
  • Developing Stored Procedures
  • Implementing Triggers
  • DML Triggers
  • AFTER Triggers
  • INSTEAD OF Triggers
  • DML Trigger Functions
  • Implementing User-Defined Functions
  • Understanding User-Defined Functions
  • Scalar UDFs
  • Table-Valued UDFs
  • Limitations on UDFs
  • UDF Options
  • UDF Performance Considerations 

Implementing Transactions, Error Handling and Dynamic SQL

  • Managing Transactions and Concurrency
  • Understanding Transactions
  • Types of Transactions
  • Basic Locking
  • Transaction Isolation Levels
  • Implementing Error Handling
  • Detecting and Raising Errors
  • Handling Errors After Detection
  • Using Dynamic SQL
  • Dynamic SQL Overview
  • SQL Injection
  • Using sp_executesql

Implementing Indexes and Statistics

  • Implementing Indexes
  • Heaps and Balanced Trees
  • Implementing Nonclustered Indexes
  • Implementing Indexed Views
  • Using Search Arguments
  • Supporting Queries with Indexes
  • Search Arguments
  • Understanding Statistics
  • Auto-Created Statistics
  • Manually Maintaining Statistics

Understanding Cursors, Sets, and Temporary Tables

  • Evaluating the Use of Cursor/Iterative Solutions vs. Set-Based Solutions
  • The Meaning of “Set-Based”
  • Iterations for Operations That Must Be Done Per Row
  • Cursor vs. Set-Based Solutions for Data Manipulation Tasks
  • Using Temporary Tables vs. Table Variables
  • Scope
  • DDL and Indexes
  • Physical Representation in tempdb
  • Transactions
  • Statistics

 Querying and Managing XML Data

  • Returning Results As XML with FOR XML
  • Introduction to XML
  • Producing XML from Relational Data
  • Shredding XML to Tables
  • Querying XML Data with XQuery
  • XQuery Basics
  • Navigation
  • FLWOR Expressions
  • Using the XML Data Type
  • When to Use the XML Data Type
  • XML Data Type Methods
  • Using the XML Data Type for Dynamic Schema

Comments:

Is there any certification available for the course?

Yes,certifications are available for almost every course offered by Microsoft

What are SQL server Developer responsibility?

The SQL Server Developer (SSD) plays a pivotal role in the development of MagNet operational applications. This individual will provide database programming, ETL and application support related issues. The individual will be involved in implementing the goals set forth as they relate to database architecture, ETL and development activities.

I have already started down the path to earn certification on one of the technologies that will updated. What should I do?

Regardless of the SQL certification level you are currently pursuing, we encourage you to continue down the path for your certification as upgrade paths become available.