This SQL training course is for people who want to gain the necessary skills to extract and analyse data from any database and create reports. Course members will learn:
how to write SQL queries
what relational databases are and how we can use them
what are relations and how to create them
the structure of data
differences between T-SQL and other dialects
practical skills for writing queries
This SQL course deals with Microsoft T-SQL dialect. If you are interested in generic SQL, please see SQL Fundamentals course.
Course Outline
RDBMS (Relational DataBase Management System)
Relational Operators
SQL as Declarative Language
SQL Syntax
SQL Sublanguages DQL, DML, DDL, DCL
DQL (Data Query Language) in Detail
SELECT clause
Column aliases
Table aliases
Date types and Date functions
Group function
Table joins
JOIN clause
UNION operator
Nested queries
Correlated subqueries
DML (DataManipulationLanguage) Overview
Inserting rows (INSERT)
Inserting rows using subquery
Updating rows (UPDATE)
Deleting rows (DELETE)
DDL (Data Definition Language) Overview
Creating, altering and dropping objects (CREATE, ALTER, DROP)
Creating tables using subquery (CREATE TABLE …. AS SELECT…)
CONSTRAINTS Overview
NULL i NOT NULL
CONSTRAINT clause
ENUM type
SET type
PRIMARY KEY
UNIQUE
FOREIGN KEY
DEFAULT clause
Transactions Overview
COMMIT
ROLLBACK
SAVEPOINT
Implicit and explicit rollbacks and commits
T-SQL Dialects Overview
What is Transact-SQL
T-SQL and portability with other dialects (what to avoid)
This SQL training course is for people who want to gain the necessary skills to extract and analyse data using Microsoft Access. Course members will learn:
how to write SQL queries
what relational databases are and how we can use them
what are relations and how to create them
the structure of data
differences between SQL dialects (Oracle, T-SQL, ANSI)
practical skills for writing queries
This SQL course deals with Microsoft Access dialect of SQL.
Course Outline
RDBMS (Relational DataBase Management System)
Relational Operators
SQL as Declarative Language
SQL Syntax
SQL Sublanguages DQL, DML, DDL, DCL
DQL (Data Query Language) in Detail
SELECT clause
Column aliases
Table aliases
Date types and Date functions
Group function
Table joins
JOIN clause
UNION operator
Nested queries
Correlated subqueries
DML (Data Manipulation Language) Overview
Inserting rows (INSERT)
Inserting rows using subquery
Updating rows (UPDATE)
Deleting rows (DELETE)
DDL (Data Definition Language) Overview
Creating, altering and dropping objects (CREATE, ALTER, DROP)
Creating tables using subquery (CREATE TABLE …. AS SELECT…)
The course is only suitable for those who have some knowledge of SQL.
Previous experience with an interactive computer system is desirable but not essential.
Overview
This 3 day course gives an introduction to ORACLE PL/SQL, an application development environment that enables the writing of stored procedures, functions and triggers using both SQL and PL/SQL commands.
The course takes the format of a workshop, with a mix of lecture, working examples and practical exercises. Although the content may be customised, at least 2 days are needed to cover the core elements.
Full course notes are provided along with sample database files, example SQL files and free software tools for use in accessing an ORACLE database.
The course is suitable for those who have some knowledge of SQL as well as those who are using ORACLE for the first time.
Previous experience with an interactive computer system is desirable but not essential.
Overview
This 2 day course gives an introduction to SQL Developer, SQL*Plus and to SQL, the Structured Query Language used to access a Relational Database and includes the new features of the latest version of ORACLE. The principles learnt may also be applied to databases as diverse as Microsoft SQL Server, MySQL, Access, Informix and DB2.
The course takes the format of a workshop, with a mix of lecture, working examples and practical exercises. Although the content may be customised, at least 2 days are needed to cover the core elements.
Full course notes are provided along with sample database files, example SQL files and free software tools for use in accessing an ORACLE database.
This SQL training course is for people who want to gain the necessary skills to extract and analyse data from any database and create reports. Course members will learn:
how to write SQL queries
what relational databases are and how we can use them
what are relations and how to create them
the structure of data
differences between SQL dialects (Oracle, T-SQL, ANSI)
practical skills for writing queries
This SQL course deals with generic ANSI SQL. It can be used in any database, including Oracle, MySQL, Microsoft Access, Microsoft SQL Server, DB2, Informix, PostgreSQL any other relational databases.
Course Outline
RDBMS (Relational DataBase Management System)
Relational Operators
SQL as Declarative Language
SQL Syntax
SQL Sublanguages DQL, DML, DDL, DCL
DQL (Data Query Language) in Detail
SELECT clause
Column aliases
Table aliases
Date types and Date functions
Group function
Table joins
JOIN clause
UNION operator
Nested queries
Correlated subqueries
DML (Data Manipulation Language) Overview
Inserting rows (INSERT)
Inserting rows using subquery
Updating rows (UPDATE)
Deleting rows (DELETE)
DDL (Data Definition Language) Overview
Creating, altering and dropping objects (CREATE, ALTER, DROP)
Creating tables using subquery (CREATE TABLE …. AS SELECT…)
Spark SQL is Apache Spark’s module for working with structured and unstructured data. Spark SQL provides information about the structure of the data as well as the computation being performed. This information can be used to perform optimizations. Two common uses for Spark SQL are: – to execute SQL queries. – to read data from an existing Hive installation.
In this instructor-led, live training (onsite or remote), participants will learn how to analyze various types of data sets using Spark SQL.
By the end of this training, participants will be able to:
Install and configure Spark SQL.
Perform data analysis using Spark SQL.
Query data sets in different formats.
Visualize data and query results.
Format of the Course
Interactive lecture and discussion.
Lots of exercises and practice.
Hands-on implementation in a live-lab environment.
Course Customization Options
To request a customized training for this course, please contact us to arrange.
Course Outline
Introduction
Overview of Data Access Approaches (Hive, databases, etc.)
Overview of Spark Features and Architecture
Installing and Configuring Spark
Understanding Dataframes in Spark
Defining Tables and Importing Datasets
Querying Data Frames using SQL
Carrying out Aggregations, JOINs and Nested Queries
This instructor-led, live training (online or onsite) is aimed at software developers, managers, and business analyst who wish to use big data systems to store and retrieve large amounts of data.
By the end of this training, participants will be able to:
Query large amounts of data efficiently.
Understand how Big Data system store and retrieve data
Use the latest big data systems available
Wrangle data from data systems into reporting systems
Learn to write SQL queries in:
MySQL
Postgres
Hive Query Language (HiveQL/HQL)
Redshift
Format of the Course
Interactive lecture and discussion.
Lots of exercises and practice.
Hands-on implementation in a live-lab environment.
Course Customization Options
To request a customized training for this course, please contact us to arrange.
Python basics and real world coding framework – organizing code, logging, error handling, config file, unit testing
Requirements
Basic programming skills and SQL knowledge required
Description
Learn how to code and unit test Python applications in a real world project. Go beyond the basics by solving a practical use case step by step. This course is designed for Python beginners who want to transition for academic background to a real world developer role !
Course Project :
You will be building a Python application to read data from files and store the data into PostgreSQL database. You will be creating REST endpoints using which external users will interact with your application data. All the industry standard best practices in terms of logging, error handling, config file, code structuring will be used in the application.
Course structure :
Python (3.9) and PyCharm IDE installation
Python basics – Get started with basic Python data types including List, Tuple and Dictionary
Organizing code with Classes and Modules – Understand core concepts of classes and packages
Python logging – Implement logging using basic config and file config
Python error handling – Learn how to handle exceptions.
Python PostgreSQL database interaction – Understand how to read and write to PostgreSQL using psycopg2
Create REST API using Python – Learn to create APIs using Python Flask framework
Reading configuration from property file – Learn how to avoid hardcoding of configurable properties
Unit testing – Learn to test your application using unittest package
Unit testing – Learn to test your application using PyTest package
You will learn the above concepts by building a real world file processing application. No prior Python knowledge required.
Prerequisites :
Basic programming skills
Basic knowledge of SQL queries
Who this course is for:
Python beginners who are getting ready for real world developer role
Course content
8 sections • 31 lectures • 1h 55m total lengthExpand all sections
Introduction5 lectures • 7min
Introduction01:37
What is Python?01:02
Installing Python00:46
Installing PyCharm02:28
Creating a project in the main Python environment01:19
Python getting started3 lectures • 16min
Python basics09:27
Python dictionary04:42
Python List and Tuple02:20
Organizing code, logging and error handling6 lectures • 27min
Structuring code with classes and functions08:12
Initializing variables with a constructor04:37
Logging using basic config03:48
Logging using file config04:13
Having multiple loggers in an application03:47
Error handling with try and except blocks02:36
Reading configuration and database interaction5 lectures • 21min
Reading properties from a configuration file03:16
Installing PostgreSQL03:55
Reading from and writing to Postgres08:58
Organizing code further03:16
Handling the unique key constraint error01:50
Reading data from a JSON file and storing it in database2 lectures • 8min
Reading data from a JSON file02:34
Writing JSON file data to PostgreSQL05:39
Creating REST APIs4 lectures • 15min
What is REST?01:13
Understanding how REST will be used in the application01:01
Creating a REST API to fetch course list07:37
Creating a REST API to store course information05:24
Unit testing3 lectures • 14min
Python unittest package03:35
Data and error testing04:49
Unit testing with PyTest05:24
Where to go from here?3 lectures • 6min
Where to go from here?00:07
Preview – Data Engineering – Hadoop and Spark03:16
Preview – Machine Learning Deep Learning Model deployment introduction02:54
Python basics and real world coding framework – organizing code, logging, error handling, config file, unit testing
Requirements
Basic programming skills and SQL knowledge required
Description
Learn how to code and unit test Python applications in a real world project. Go beyond the basics by solving a practical use case step by step. This course is designed for Python beginners who want to transition for academic background to a real world developer role !
Course Project :
You will be building a Python application to read data from files and store the data into PostgreSQL database. You will be creating REST endpoints using which external users will interact with your application data. All the industry standard best practices in terms of logging, error handling, config file, code structuring will be used in the application.
Course structure :
Python (3.9) and PyCharm IDE installation
Python basics – Get started with basic Python data types including List, Tuple and Dictionary
Organizing code with Classes and Modules – Understand core concepts of classes and packages
Python logging – Implement logging using basic config and file config
Python error handling – Learn how to handle exceptions.
Python PostgreSQL database interaction – Understand how to read and write to PostgreSQL using psycopg2
Create REST API using Python – Learn to create APIs using Python Flask framework
Reading configuration from property file – Learn how to avoid hardcoding of configurable properties
Unit testing – Learn to test your application using unittest package
Unit testing – Learn to test your application using PyTest package
You will learn the above concepts by building a real world file processing application. No prior Python knowledge required.
Prerequisites :
Basic programming skills
Basic knowledge of SQL queries
Who this course is for:
Python beginners who are getting ready for real world developer role
