Course Outline for MS SQL Data Analytics using MS SQL 2019

Course Outline for MS SQL Data Analytics using MS SQL 2019

This course is designed to take the learner from their basic understanding of RDBMS and SQL to Microsoft’s SQL Server using T-SQL and various tools for analytics in a production environment.

Most courses take a very academic and generic approach to training on Data Analytics that may not be suitable in a commercial setting. This course is quite the opposite. It takes the learner through steps of implementing an MS SQL Server on a production ready server and uses the professional tools and applications needed to get the most out of their Analytics Quest!

Although this is an intermediate level course, the learners are not expected to be experts on T-SQL or know SQL in-depth.

Course Outcome

After the successful participation of this course, the learner may be expected to have skill sets that include:

• Understanding pf RDBMS
• SQL Syntax
• T-SQL Syntax
• Installing MS SQL on a production ready server
• Containers
• MSSMS
• Azure Data Studio
• Create database, tables, view, stored procedures, filter data, update and delete information and be able to create complex joins of tables.
• Machine Learning using MS SQL
• Import and Export of Data from external sources
• Functions
• Procedures
• Data Cleaning
• Visualizations
• Basic python for analytics

Please note that the above is meant as a guideline. Each learner has varying ability and as such, each learner will reflect varying levels of expertise.

Prerequisites

• Dual Screen
• High Speed Internet
• Webcam and Microphone
• Visual Studio [must be pre-installed]
• PowerShell [must be pre-installed]
• Filezilla [must be pre-installed]
• Access to external IP address
• Microsoft SQL Server Management Studio [must be pre-installed]
• Azure Data Studio [must be pre-installed]
• Basic understanding of SQL

Course Outline

This 3 day course will cover the following topics:

1. Module 1
   1. Understanding RDBMS
   2. Understanding Microsoft’s SQl Server
   3. SQL vs T-SQL
   4. Understanding Production level setup
   5. Containers
   6. Remoting
2. Module 2
   1. Remote Server for MS SQL
   2. Containers vs Virtual Box
   3. Installing Containers on Ubuntu
   4. Installing MS SQL in Ubuntu 18.0.4 in a container
   5. Accessing the MS SQL Server
3. Module 3
   1. Microsoft SQL Server Management Studio
      1. DBs
      2. Tables
      3. Stored Procedures
      4. Functions
      5. Security
      6. Importing Data
      7. Exporting Data
      8. Security
4. Module 4
   1. Azure Data Studio
      1. DBs
      2. Tables
      3. Stored Procedures
      4. Functions
5. Module 5
   1. SQL Revision
      1. Select
      2. Where
      3. Like
      4. Order
      5. Insert
      6. Update
      7. Delete
      8. IN Operator
      9. Between
      10. Aggregate
      11. Group
      12. Alter
      13. Sub queries
6. Module 6
   1. Stored Procedures
   2. Functions
   3. Assessment
7. Module 7
   1. Datetime
   2. JSON
   3. XML
   4. Python
      1. Data types
      2. Conditions
      3. Loops
      4. Functions
      5. Pandas
   5. Analytics
8. Module 8
   1. Statistics
   2. Data Transformations
   3. Data Mining Overview
   4. Data Mining Concepts
   5. Algorithms
9. Module 9
   1. Machine Learning in MS SQL for Analytics
      1. Association
      2. Clustering
      3. Decision Trees
      4. Linear Regression
      5. Logistic Regression
      6. Naive Bayes
10. Artificial Intelligence in MS SQL for Analytics
    1. Neural Network
    2. Sequence Clustering