IT Expert System MS SQL Server Training

MS SQL SERVER

Time: 40 Hours

Prerequisite:

No prior SQL training or relational database experience is assumed.

Course overview:

The MS SQL Server language is a flexible procedural extension to SQL and increases productivity, performance, scalability, portability and security. In this course, you will gain the practical knowledge to write PL/SQL programs. You will learn to build stored procedures, design and execute modular applications, and increase the efficiency of data movement.

Course Content:

Introducing SQL Server

Relational Databases and SQL: An Overview
•   The Role of the Database Server
•   Interacting with a Database Server: The Client
•   SQL: Its Role and Purpose
•   Using an Integrated development Environment (IDE)
•   Databases, Schemas, Tables, Rows and Columns

Introducing SQL
•   Creating and Editing SQL
•   About Statements, Batches and Scripts
•   Executing and Parsing SQL Scripts
•   Using Comments
•   SQL Syntax and The Rules of SQL
•   About Keywords, Identifiers, Operators, Whitespace and Case
•   About the Semi Colon
•   SQL Conventions and Good Practice
•   Working to Minimize, Trap and Find Errors

Retrieving Data with SQL: First Steps
•   Introducing Queries: The SELECT Statement
•   The Clauses of the SELECT Statement
•   About Optional Clauses and Mandatory Clauses
•   Using FROM to Specify the Source Table(s)
•   Retrieving Entire Tables
•   Retrieving Specific Columns
•   The Importance of Clause Order
•   How to Build Successful Queries
•   Types of Output: About the Result Set
•   Using Column Aliases to Rename Columns
•   Performing Calculations
•   Using Numeric and String Operators to Create Derived Output
•   Ways of Limiting the Output
•   Using ORDER BY to Sort the Output
•   Ways of Working: Some Tips
Using WHERE to Filter Results
•   Working with Comparison Operators (=, >= etc)
•   Numeric and String Based Filtering
•   Filtering Based on Calculations
•   Eliminating Duplicate Results with DISTINCT
•   The Execution Order and its Traps
•   Column Aliases: Where You Can and Cannot Use Them
•   Extending Filters with AND and OR
•   Solving AND/OR Difficulties with Brackets
•   Excluding Results with NOT: Some Tips
•   Range Filtering using BETWEEN and IN
•   NULL and its Implications Explained
•   Catering for NULL
•   Matching Patterns with LIKE
Joining: Getting Results from Multiple Tables
•   Qualifying Column Names
•   Joins Explained
•   The Different Types of Joins
•   Mastering the Inner Join: WHERE Syntax
•   Mastering the Inner Join: INNER JOIN Syntax
•   Table Aliases: Why We Need Them
•   Joining Two Tables
•   Joining Multiple tables
•   How to Simplify Joins: An Approach
Getting Confident with SQL Joins
•   The Importance of the Database Diagram
•   What if there is No Database Diagram?
•   Primary Keys and Foreign Keys Explained
•   Locating Primary Keys and Foreign Keys
•   Do we Need Primary Keys and Foreign Keys?
•   Approaching Joins the Right Way
•   Avoiding Common Join Errors
Using Standard SQL Functions
•   How to Use Standard SQL Functions to Modify Results
•   How to Find the Right Function
•   Mathematical, String and Conversion Functions
•   Functions for Modifying and Calculating Dates
•   Formatting Numbers to Two Decimal Places
•   Replacing NULL with a Specific Value
•   Using Standard Functions in WHERE
•   Using CASE to Specify Output Conditions
Working with Dates
•   Understanding How Dates are Stored
•   Introducing Date Functions
•   Converting Text Dates To Date Format
•   Establishing Today's Date
•   Extracting Parts of a Date (Day, Month, Year)
•   Displaying Dates in Specific Date Formats
•   Filtering with Dates
Grouping and Summarizing Results
•   The difference Between Tabular and Scalar Results
•   Using Aggregate Functions (MAX(), SUM(), AVG(), COUNT() etc)
•   The Way Aggregate Functions Work
•   Where to Use and Where Not to Use Aggregate Functions
•   Using GROUP BY to Group Results
•   Get GROUP BY Right Every Time!
•   The Need for HAVING: Filtering the Result Table
Inserting, Updating and Deleting Data
•   Inserting Single Rows
•   Inserting Multiple Rows
•   Inserting Rows by Column Position
•   Inserting Rows by Column Name
•   Dealing with Auto-Incrementing Values
•   Dealing with Nulls when Inserting
•   Inserting Data from one Table into Another
•   Updating Data
•   Deleting Data

Defining tables
•   Specifying appropriate data types
•   Indexing tables with Management Studio
•   Adding, modifying or removing columns
Safeguarding data with constraints
•   Enforcing uniqueness through primary key constraints
•   Validating conditions with check constraints
•   Protecting relationships with foreign keys
Leveraging Server Programmability
Retrieving data with Transact-SQL
•   Employing Query Designer to construct select statements
•   Analyzing data for aggregate functions
•   Retrieving data as an XML document
Administering SQL Server Databases
Executing the Maintenance Plan Wizard
•   Defragmenting data and checking database integrity
•   Improving performance by rebuilding indexes
•   Automating administrative tasks
Scheduling repeating jobs
•   Implementing a backup strategy
Monitoring SQL Server resources
•   Displaying current activity and expensive queries
•   Running reports with Management Studio
Securing the database infrastructure
•   Authenticating SQL Server and Windows logins
•   Assigning users to database and server roles
•   Granting or revoking permissions
Integrating External Applications
•   Pulling data from SQL Server into Application
•   Creating a front-end interface with VB or C# Application
Transferring business information
•   Importing and exporting data from text files
•   Migrating an Access database to SQL Server