MS SQL Server Administration

Microsoft SQL Server continues to be one of the world’s most popular and top databases. Database is a heart of IT which is needed to stored all critical data and information. This training course will provide you all the necessary skills you need to be a successful database administrator.
 

Course Objectives

After completing this course, students will be able to
  • Start from scratch by installing Microsoft SQL Server 2016 and create a practice environment for yourself.
  • You will be familiarized with some of the core concepts of RDBMS / SQL Server.
  • Ins and outs of SQL Server Management studio.
  • Create and connect SQL server colud database using Amazon AWS and Microsoft Azure.
  • Create, alter and drop database.
  • Create, alter and drop tables.
  • Insert, update and delete records from tables.
  • Constructing your SELECT statement to retrieve data from SQL Server.
  • Filter out unwanted data by using WHERE clause as well as, you would know how to order your result set by using ORDER BY clause.
  • Use UNION / UNION ALL operators
  • Group and summarize your data by using , GROUP BY clause, HAVING clause and Aggregate functions.
  • SQL Server. Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, Cross Join and Self Join.
  • String functions, date functions, and system functions.
  • Create your own function.
  • Create, alter and drop Stored Procedure and advantages of stored procedure.
  • Create, alter and drop Trigger and its advantage.
  • Create, alter and drop View and its advantage.
  • Temp tables and its advantage.
  • Handel exceptions in SQL Server.
  • Import data from excel file, flat file, another SQL server database and MySQL.
  • Export data to excel file, flat file and another SQL server database
  • Bulk Insert &  Cursors.
 

Target Audience

  • System Engineer
  • IT Professionals
 

Course Pre-Requisites

  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Working knowledge of Transact-SQL.
  • Working knowledge of relational databases.
  • Some experience with database design.

Course Summary

Course Fee

৳ 20,000

Training Method

Offline/Online

Total Modules

11

Course Duration

60 Hours

Total Session

20

Class Duration

3 Hours

Enroll Now

Details Course Outlines

Module-01

Create Transact-SQL SELECT queries

  • identify proper SELECT query structure
  • write specific queries to satisfy business requirements
  • construct results from multiple queries using set operators
  • Distinguish between UNION and UNION ALL behavior
  • identify the query that would return expected results based on provided table structure, and/or data.

Module-02

Query multiple tables by using joins

  • write queries with join statements based on provided tables, data, and requirements;
  • determine proper usage of INNER JOIN
  • LEFT/RIGHT/FULL OUTER JOIN, and CROSS JOIN
  • construct multiple JOIN operators using AND and OR
  • determine the correct results when presented with multi-table SELECT statements and source data
  • write queries with NULLs on joins

Module-03

Implement functions and aggregate data

  • Construct queries using scalar-valued and table-valued functions
  • Identify the impact of function usage to query performance and WHERE clause sargability
  • Identify the differences between deterministic and non-deterministic functions
  • Use built-in aggregate functions;
  • Use arithmetic functions
  • Date-related functions, and system functions.

Module-04

Modify data

  • Write INSERT, UPDATE, and DELETE statements
  • Determine which statements can be used to load data to a table based on its structure and constraints
  • Construct Data Manipulation Language (DML) statements using the OUTPUT statement
  • determine the results of Data Definition Language (DDL) statements onsupplied tables and dataQuery data with advanced Transact-SQL components (30–35%)

Module-05

Query data by using subqueries and APPLY

  • Determine the results of queries using subqueries and table joins
  • Evaluate performance differences between table joins and correlated subqueries based on provided data and query plans
  • Distinguish between the use of CROSS APPLY and OUTER APPLY
  • Write APPLY statements that return a given data set based on supplied data

Module-06

Query data by using table expressions

  • Identify basic components of table expressions
  • Define usage differences between table expressions and temporary tables
  • Construct recursive table expressions to meet business requirements

Module-07

Group and pivot data by using queries

  • Use windowing functions to group and rank the results of a query
  • distinguish between using windowing functions and GROUP BY
  • construct complex GROUP BY clauses using GROUPING SETS, and CUBE
  • construct PIVOT and UNPIVOT statements to return desired results based on supplied data
  • determine the impact of NULL values in PIVOT and UNPIVOT queries

Lesson-08

Query temporal data and non-relational data

  • query historic data by using temporal tables, query and output JSON data, query and output XML data

Lesson-09

Create database programmability objects by using Transact-SQL

  • create stored procedures, table-valued and scalar-valued user-defined functions, triggers, and views
  • implement input and output parameters in stored procedures
  • identify whether to use scalar-valued or table-valued functions
  • distinguish between deterministic and non-deterministic functions
  • create indexed views

Lesson-10

Implement error handling and transactions

  • determine results of Data Definition Language (DDL) statements based on transaction control statements
  • implement TRY…CATCH error handling with Transact-SQL
  • generate error messages with THROW and RAISERROR
  • implement transaction control in conjunction with error handling in stored procedures

Lesson-11

Implement data types and NULLs

  • evaluate results of data type conversions
  • determine proper data types for given data elements or table columns
  • Identify locations of implicit data type conversions in queries
  • determine the correct results of joins and functions in the presence of NULL values,
  • identify proper usage of ISNULL and COALESCE functions
Download Course Outline