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

 IT Expert System, Inc. is approved to operate by the Private Business and Vocational Schools Division of the Illinois Board of Higher Education.

 IT Expert System, Inc. is not accredited by a US Department of Education recognized accrediting body. IBHE Mandatory Disclosure Reporting

 IT Expert System, Inc Training programs are approved by the Illinois State Approving Agency for the enrollment of qualified veterans and/or other eligible persons to receive Montgomery GI Bill® education benefits.

‘PMP’ and 'CAPM' are registered marks of the Project Management Institute, Inc.

‘GI Bill' is a registered trademark of the U.S. Department of Veterans Affairs (VA).

Office:

951 N. Plum Grove Rd.

Suite A, C
Schaumburg, IL, 60173

Ph: 847 350 9034

Email: info@itexps.com

Branch Office:

1560 Wall Street,

Suite #111,

Naperville, IL 60563 

Ph: 847 350 9034

Email: info@itexps.com

Disclaimer: At this time, we are not offering any training programs for Nebraska residents.