SQL Advanced level for Analysts Training Course

Duration

21 hours (usually 3 days including breaks)

Requirements

There are no specific requirements needed to attend this course.

Overview

The aim of this course is to provide a clear understanding of the use of SQL for different
databases (Oracle, SQL Server, MS Access…). Understanding of analytic functions and the
way how to join different tables in a database will help delegates to move data analysis
operations to the database side, instead of doing this in MS Excel application. This can also
help in creating any IT system, which uses any relational database.

Course Outline

Selecting data from database

  • Syntax rules
  • Selecting all columns
  • Projection
  • Arithmetical operations in SQL
  • Columns aliases
  • Literals
  • Concatenation

Filtering outcome tables

  • WHERE clause
  • Comparison operators
  • Condition LIKE
  • Condition BETWEEN…AND
  • Condition IS NULL
  • Condition IN
  • AND, OR, NOT operators
  • Several conditions in WHERE clause
  • Operators order
  • DISTINCT clause

Sorting outcome tables

  • ORDER BY clause
  • Sort by multiple columns or expressions

SQL Functions

  • Differences between single-row and multi-row functions
  • Character, numeric, DateTime functions
  • Explicit and implicit conversion
  • Conversion functions
  • Nested functions
  • Dual table (Oracle vs other databases)
  • Getting current date and time with different functions

Aggregate data using aggregate functions

  • Aggregate functions
  • Aggregate functions vs NULL value
  • GROUP BY clause
  • Grouping using different columns
  • Filtering aggregated data – HAVING clause
  • Multidimensional Data Grouping – ROLLUP and CUBE operators
  • Identifying summaries – GROUPING
  • GROUPING SETS operator

Retrieving data from multiple tables

  • Different types of joints
  • NATURAL JOIN
  • Table aliases
  • Oracle syntax – join conditions in WHERE clause
  • SQL99 syntax – INNER JOIN
  • SQL99 syntax – LEFT, RIGHT, FULL OUTER JOINS
  • Cartesian product – Oracle and SQL99 syntax

subqueries

  • When and where subquery can be done
  • Single-row and multi-row subqueries
  • Single-row subquery operators
  • Aggregate functions in subqueries
  • Multi-row subquery operators – IN, ALL, ANY

Set operators

  • UNION
  • UNION ALL
  • INTERSECT
  • MINUS/EXCEPT

Transactions

  • COMMIT, ROLLBACK SAVEPOINT statements

Other schema objects

  • Sequences
  • Synonyms
  • Views

Hierarchical queries and samples

  • Tree construction (CONNECT BY PRIOR and START WITH clauses)
  • SYS_CONNECT_BY_PATH function

Conditional expressions

  • CASE expression
  • DECODE expression

Data management in different time zones

  • Time zones
  • TIMESTAMP data types
  • Differences between DATE and TIMESTAMP
  • Conversion operations

Analytic functions

  • Use of
  • Partitions
  • Windows
  • Rank functions
  • Reporting functions
  • LAG/LEAD functions
  • FIRST/LAST functions
  • Reverse percentile functions
  • hypothetical rank functions
  • WIDTH_BUCKET functions
  • Statistical functions

MongoDB for Analysts Training Course

Duration

14 hours (usually 2 days including breaks)

Requirements

There are no specific requirements needed to attend this course, but experience with any database would be useful.

Overview

After the training, you will be able to write extract and modify the data in MongoDB database. You will also learn about powerful Aggregation Pipeline that is a flexible and fast tool for data analytics, and if it’s not enough for you, how to connect your current BI tools like Tableau or Excel to data stored in Mongo.

Course Outline

MongoDB Introduction

  • Introduction into NoSQL databases
  • NoSQL vs. RDBMS (relational databases)
  • What are documents and JSON format
  • Creating MongoDB sandbox (local or cloud)
  • CRUD operations (Create, Read, Update and Delete)
  • MongoDB Shell as a basic client tool
  • MongoDB Compass and other clients

Aggregation functions

  • Single purpose aggregation functions
  • Aggregation pipelines
  • Creating views
  • Overview of map-reduce

Business Intelligence connector and data migration

  • How to use MongoDB data in Excel
  • BI connector installation and configuration 
  • Loading data from existing SQL databases
  • Built-in Mongo import/export tools

Performance tuning

  • Analysing performance using explain function
  • Profiler
  • Indexes and special collections overview
  • Optimising replica-set nodes for reporting

Understanding Machine Learning: Uses, Example

What Is Machine Learning?

Machine learning is the concept that a computer program can learn and adapt to new data without human intervention. Machine learning is a field of artificial intelligence (AI) that keeps a computer’s built-in algorithms current regardless of changes in the worldwide economy.

KEY TAKEAWAYS

  • Machine learning is an area of artificial intelligence (AI) with a concept that a computer program can learn and adapt to new data without human intervention.
  • A complex algorithm or source code is built into a computer that allows for the machine to identify data and build predictions around the data that it identifies.
  • Machine learning is useful in parsing the immense amount of information that is consistently and readily available in the world to assist in decision making.
  • Machine learning can be applied in a variety of areas, such as in investing, advertising, lending, organizing news, fraud detection, and more.

Understanding Machine Learning

Various sectors of the economy are dealing with huge amounts of data available in different formats from disparate sources. The enormous amount of data, known as big data, is becoming easily available and accessible due to the progressive use of technology, specifically advanced computing capabilities and cloud storage. Companies and governments realize the huge insights that can be gained from tapping into big data but lack the resources and time required to comb through its wealth of information. As such, artificial intelligence measures are being employed by different industries to gather, process, communicate, and share useful information from data sets. One method of AI that is increasingly utilized for big data processing is machine learning.

The various data applications of machine learning are formed through a complex algorithm or source code built into the machine or computer. This programming code creates a model that identifies the data and builds predictions around the data it identifies. The model uses parameters built in the algorithm to form patterns for its decision-making process. When new or additional data becomes available, the algorithm automatically adjusts the parameters to check for a pattern change, if any. However, the model shouldn’t change.

Uses of Machine Learning

Machine learning is used in different sectors for various reasons. Trading systems can be calibrated to identify new investment opportunities. Marketing and e-commerce platforms can be tuned to provide accurate and personalized recommendations to their users based on the users’ internet search history or previous transactions. Lending institutions can incorporate machine learning to predict bad loans and build a credit risk model. Information hubs can use machine learning to cover huge amounts of news stories from all corners of the world. Banks can create fraud detection tools from machine learning techniques. The incorporation of machine learning in the digital-savvy era is endless as businesses and governments become more aware of the opportunities that big data presents.

Application of Machine Learning

How machine learning works can be better explained by an illustration in the financial world. Traditionally, investment players in the securities market like financial researchers, analysts, asset managers, and individual investors scour through a lot of information from different companies around the world to make profitable investment decisions. However, some pertinent information may not be widely publicized by the media and may be privy to only a select few who have the advantage of being employees of the company or residents of the country where the information stems from. In addition, there’s only so much information humans can collect and process within a given time frame. This is where machine learning comes in.

An asset management firm may employ machine learning in its investment analysis and research area. Say the asset manager only invests in mining stocks. The model built into the system scans the web and collects all types of news events from businesses, industries, cities, and countries, and this information gathered makes up the data set. The asset managers and researchers of the firm would not have been able to get the information in the data set using their human powers and intellects. The parameters built alongside the model extracts only data about mining companies, regulatory policies on the exploration sector, and political events in select countries from the data set.

Example of Machine Learning

Say mining company XYZ just discovered a diamond mine in a small town in South Africa. A machine learning tool in the hands of an asset manager that focuses on mining companies would highlight this as relevant data. The model in the machine learning tool would then use an analytics tool called predictive analytics to make predictions on whether the mining industry will be profitable for a time period, or which mining stocks are likely to increase in value at a certain time, based on the recent information discovered, without any input from the asset manager. This information is relayed to the asset manager to analyze and make a decision for their portfolio. The asset manager may then make a decision to invest millions of dollars into XYZ stock.

In the wake of an unfavorable event, such as South African miners going on strike, the computer algorithm adjusts its parameters automatically to create a new pattern. This way, the computational model built into the machine stays current even with changes in world events and without needing a human to tweak its code to reflect the changes. Because the asset manager received this new data on time, they are able to limit their losses by exiting the stock.

SPONSORED

Trade on the Go. Anywhere, Anytime

One of the world’s largest crypto-asset exchanges is ready for you. Enjoy competitive fees and dedicated customer support while trading securely. You’ll also have access to Binance tools that make it easier than ever to view your trade history, manage auto-investments, view price charts, and make conversions with zero fees. Make an account for free and join millions of traders and investors on the global crypto market.