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

Introduction No-SQL
•    Compare OLAP vs OLTP
•    Compare Relational Database vs No-SQL Database

Comparison Oracle vs MS SQl Server


 

 

Special notes:

 

  • Hands-on labs for each topic

  • Real life projects at the course completion

 

 

Additional Material [Lab, Homework]: 

 

 

For more info contact ITEXPS