Tag: SQL

Duration

21 hours (usually 3 days including breaks)

Requirements

• NO previous SQL or database experience is required.

Overview

Microsoft SQL Server is a relational database management system (RDBMS) for storing and retrieving data.

In this instructor-led, live training (onsite or remote), participants will learn the essentials of the SQL language needed to query a Microsoft SQL Server database.

By the end of this training, participants will be able to:

• Install and configure Microsoft SQL Server
• Query a Microsoft SQL Server database using Microsoft’s Transact-SQL (T-SQL) language
• Understand the essentials of database design
• Optimize a database through normalization
• Use aggregate functions to generate results similar to those of “pivot-tables”
• Access Microsoft SQL Server through Excel

Audience

• Data analysts
• Business Intelligence professionals
• Business managers
• Excel experts who wish to expand their analysis skill set

Format of the Course

• Interactive lecture and discussion
• Lots of exercises and practice
• Hands-on implementation in a live-lab environment

Course Customization Options

• This training is based on the latest version of Microsoft SQL Server
• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

Installing and Configuring Microsoft SQL Server

Overview of Microsoft SQL Server Features

Overview of Transact-SQL Concepts

Querying a Database using SELECT

Filtering the Data with WHERE

Sorting the Data with ORDER BY

Querying Multiple Tables with Joins

Grouping Data with GROUP BY

Using Aggregate Functions to Perform Calculations on Resulting Data

Filtering Groups with HAVING

Creating a New Database

Understanding the Database Model

Optimizing the Database through Normalization

Enhancing your Queries with Procedures

Writing Functions and Sub-routines

Using Conditions to Control the Flow

Connecting to Microsoft SQL Server through Excel

Troubleshooting

Summary and Conclusion

Duration

14 hours (usually 2 days including breaks)

Requirements

• Computer literacy
• Knowledge of any operating system

Overview

• How to build a query?
• What is a relational database?
• What is the structure and SQL commands?

Course Outline

Relational database models

• Relational operators
• Characteristics of declarative SQL language
• SQL syntax
• Division language DQL, DML, DDL, DCL

Data Query Language

• SELECT queries.
• Aliases columns of tables
• Service date (DATE types, display functions, formatting)
• Group Features
• Combining internal and external tables (JOIN clause)
• UNION operator
• Nested Subqueries (the WHERE clause, the table name, column name)
• Correlated subqueries

Data Modification Language

• Inserting rows (INSERT clause)
• Inserting rows by request
• Variation of the rows (UPDATE)
• Delete rows (DELETE)

Data Definition Language

• Creating, altering and dropping objects (CREATE, ALTER, DROP)
• Creating tables using subquery (CREATE TABLE …. AS SELECT…)

CONSTRAINTS

• Options NULL and NOT NULL
• CONSTRAINT clause
• ENUM type
• type SET
• PRIMARY KEY condition
• UNIQUE condition
• FOREIGN KEY condition
• DEFAULT clause

Transactions

Duration

28 hours (usually 4 days including breaks)

Requirements

• Basic skills in any programming language, object-oriented structural or
• The free use of the basics of SQL and knowledge of Oracle database environment (preferably Oracle 11g completion of training – Native SQL for Programmers – Workshops)

Overview

For who

Workshops are dedicated to developers, end users and administrators, who until now have had no contact with the language PL / SQL, and the need to exploit its huge potential in working with a database, automate processes, and in building applications

Exams and Certificates

The plan covers the training material required to pass the exam 1Z0-144 Oracle Database 11g Program with PL / SQL and obtain the title of Oracle PL / SQL Developer Certified Associate

Purpose of training

The workshop aims to familiarize participants with the programming language PL / SQL, its capabilities and limitations. This workshop will include a full understanding of the mechanisms involved in the programming language PL / SQL needed to implement the logic of the free applications, automation of data processing and database management.

The content of the training

• Introduction to PL / SQL architecture solutions based on this language, the organization of the working environment
• Create scripts and stored program units that operate on data

Notes

The workshops are based on the software version 11g XE

Course Outline

Introduction to PL / SQL

• Runtime Environment
• Construction and types of PL / SQL blocks
• Declaring and using variables
• Control statements, decisions, loops

SQL statements in PL / SQL

• DML commands
• DDL and dynamic SQL
• TCL commands and transactional
• SELECT

Procedures and Functions

• Create and delete
• Parameterization
• Passing parameters by value and reference, nocopy

Handling errors and exceptions

Create and use cursors

• Records
• static cursors
• Parameterizing cursors
• Cursor FOR UPDATE

Associative arrays

Packages

• The structure of the package: the specification and body
• Section Initialization and global variables, memory management, an instance of the package
• Encapsulation, overloading programs, pre-declaration procedures

Triggers

• DML triggers
• The triggers Drives (INSTEAD OF)
• The triggers system
• New features in Oracle 11g triggers

Examples of application packages built

• Writing to a file through UTL_FILE
• Sending e-mails

Management code and compiler

• Encryption code (dynamic obfuscation, wrap)
• conditional Compilation
• The relationships between objects
• Warnings

Duration

14 hours (usually 2 days including breaks)

Requirements

• Computer skills
• Knowledge of any operating system

Overview

The course answers questions

How to build a query?

What possibilities have SQL?

What is a relational database?

What is the structure and SQL commands

Course Outline

Relational database models

• The structure of a relational database
• Connection types of tables
• The normalization and denormalization database
• Database Management System (RDBMS)

Relational Operators

Characteristics of declarative SQL language SQL Syntax Division language DQL, DML, DDL, DCL Language DQL (Data Query Language)

• SELECT queries
• Aliases columns, tables
• Service date (DATE types, display functions, formatting)
• Group Features
• Combining internal and external tables
• UNION operator
• Nested Subqueries (the WHERE clause, the table name, column name)
• Correlated subqueries

Data Modification Language

• Inserting rows (INSERT clause)
• Inserting via query
• Updating rows (UPDATE)
• Removing rows (DELETE)

Data Definition Language

• Create, change of ownership, remove tables (CREATE, ALTER, DROP) Creating tables by asking (CREATE TABLE …. AS SELECT …)

CONSTRAINTS

• Options NULL and NOT NULL
• CONSTRAINT clause
• Condition PRIMARY KEY
• Condition UNIQUE
• Condition FOREIGN KEY
• DEFAULT clause

Transactions

• The command COMMIT, ROLLBACK, SAVEPOINT

Language DCL

• Granting and revoking permissions (GRANT, REVOKE)
• Roles
• Creating Users
• sequences
• Synonyms
• The views (perspective)

Duration

35 hours (usually 5 days including breaks)

Requirements

• Familiarity with database management and database management systems

Audience

• Beginners
• Programmers
• Database admins

Overview

The SQL Development Bootcamp provides comprehensive lessons for SQL development. Topics covered include database design techniques, data manipulation, and simple and complex query methods.

This instructor-led, live training (online or onsite) is aimed at beginners, programmers, and database administrators who wish to learn or improve their SQL development skills and knowledge.

By the end of this training, participants will be able to install, create, manage, manipulate, and monitor databases using SQL and database management systems.

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 to SQL Development Bootcamp

Overview of SQL Development

• Data and databases
• Database management systems (DBMS)
• SQL key concepts

Getting Started with SQL Databases

• Installing DBMS software
• Initial configuration
• Connecting existing databases
• Database development methodology

Exploring SQL Statement Fundamentals

• Data definition language statements
• Transaction control statements
• Session and system control statements
• Embedded SQL statements

Working with SQL Data

• Filtering data
• Comparison and logical operators
• Sorting data
• Row limiting clause
• Substitution variables
• Define and verify commands

Using Single Row, Group, Conversion, and General Functions

• Character functions
• Numeric and date functions
• Conversion functions
• Case and decode functions
• Distinct and null values
• Group by clauses
• Having clauses

Working with SQL Join Clauses

• Inner join
• Non-equijoin
• Outer join
• Cross join

Using Subqueries and SET Operators

• Single row subqueries
• Multi row and multi column subqueries
• SET operators
• Correlated subquery
• Data manipulation with subqueries

Creating Databases and Tables

• Data types
• Primary and foreign keys
• Constraints
• Table actions (create, insert, update, delete)
• Data dictionary views
• Conditional expressions

Working with Views, Sequences, Synonyms, and Indexes

• Creating and querying views
• Creating and modifying sequences
• Creating synonyms
• Creating indexes

Managing Schema Objects

• Constraints management
• Temporary tables
• External tables

Controlling User Access

• System privileges
• Object privileges
• Roles
• Revoking privileges

Managing Data in Different Time Zones

• Interval data types
• Time functions

Troubleshooting

Summary and Conclusion

Duration

14 hours (usually 2 days including breaks)

Requirements

• Computer literacy
• Knowledge of any operating system

Overview

The course answers questions

• How to build a query?
• What opportunities have SQL?
• What is a relational database?
• What is the structure and SQL commands?

Course Outline

Relational database models

• The structure of a relational database
• Relational operators

Download the data

• Rules for writing SQL queries
• The syntax for the SELECT
• Selecting all columns
• Inquiries from arithmetic operations
• Aliases columns
• Literals
• Concatenation

Restrict results

• The WHERE clause
• The comparison operators.
• LIKE Condition
• Prerequisite BETWEEN … AND
• IS NULL condition
• IN condition.
• Logical operators
• Many of the conditions in the WHERE clause
• The order of operators
• DISTINCT clause

Sorting Data

• The ORDER BY clause
• Sorting by multiple columns or expressions

SQL Functions

• The differences between the functions of single and multi-rows
• Functions text, numeric, date,
• Conversion functions
• Nesting functions
• Handling of NULL values

Aggregating data using the grouping function

• Grouping functions
• How grouping functions treat NULL values
• Create groups of data – the GROUP BY clause
• Grouping multiple columns
• Reducing the function result grouping – the HAVING clause

Retrieving data from multiple tables

• Types of joins
• Aliases tables
• Joins in the WHERE clause
• INNER JOIN Inner join
• External Merge LEFT, RIGHT
• Cartesian product

Subqueries

• Place subqueries in the SELECT command
• Subqueries single and multi-lineage
• Operators Subqueries single-line
• Operators Subqueries multi-IN, ALL, ANY

Collective operators

• UNION operator
• INTERSECT operator
• EXCEPT operator

Insert, update, and delete data

• INSERT command
• UPDATE command
• DELETE command

Transactions

Duration

7 hours (usually 1 day including breaks)

Requirements

Though no technical background is required, understanding the examples requires some level of database theory (e.g. SQL, etc…)

Overview

This course helps customer to chose the write data storage depend on their needs. It covers almost all possible modern approaches.

Course Outline

1. File Document Storage (Cloud Storage)
   1. Features (OCR, Scalaibility, Search, etc…)
   2. Open Source examples (e.g. Next Cloud)
   3. Some commercial examples
2. Flat file storage
   1. XML databases
   2. CSV databases
3. Relational databases
   1. Normalization
   2. Dependencies and Constrants
   3. Scalability – replications, clusters
   4. Open Source and commercial software (MySQL, PostrgreSQL, DM7, Oracle, etc.)
4. NoSQL Storage
   1. Document Oriented Databases (MongoDB, CouchDB etc…)
   2. Column Orientation (Canadra, Scylla etc…)
   3. Search Orientation (Elasticsearch…
5. NewSQL
   1. CAP Theorem
   2. Opensource software (SequoiaDB, etc…)
6. Search Engines
   1. Features (text processing, relevancy, etc…)
   2. Open Source examples
   3. Scalability, High Availability, Load Balacing, etc….
7. Traditional Datawherehouses
   1. Business Inteligence, OLTP and Datawherehouse
   2. Opensource and commercial solutions
8. MapReduce and Distributed Parallel Processing
   1. Hadoop-like (Hive, HFS, Impala)
9. Distributed filesystem
   1. Overview of opensource (Ceph etc…)
10. In-memory Databases
    1. Opensource solution (e.g. ApacheIgnite)
11. Others
    1. Hypertable (Google Bigtable)
    2. BigQuery
    3. AWS solutsion (S3, etc…)
12. Beyond present – future trends

Duration

7 hours (usually 1 day including breaks)

Requirements

• Computer literacy
• Knowledge of any operating system

Overview

This live instructor-led training (onsite or remote) will guide you through the most important elements of  the SQL language. A combination of comprehensively prepared training materials and the expertise of the instructor will serve as an introduction to the world of databases.
Depending on the preferences of the participant, the training can be carried out on either MySQL, Oracle or MsSQL databases.

The training is designed for a group of 3-6 participatns.

Course Outline

Relational database models

• Relational operators
• Characteristics of declarative SQL language
• SQL syntax
• Division language DQL, DML, DDL, DCL

Data Query Language

• SELECT queries.
• Aliases columns of tables
• Service date (DATE types, display functions, formatting)
• Group Features
• Combining internal and external tables (JOIN clause)
• UNION operator
• Nested Subqueries (the WHERE clause, the table name, column name)
• Correlated subqueries

Data Modification Language

• Inserting rows (INSERT clause)
• Inserting rows by request
• Variation of the rows (UPDATE)
• Delete rows (DELETE)

Data Definition Language

• Creating, altering and dropping objects (CREATE, ALTER, DROP)
• Creating tables using subquery (CREATE TABLE …. AS SELECT…)

Duration

7 hours (usually 1 day including breaks)

Overview

Delegates will gain an understanding of some of the more advanced features of Transact SQL as well as being able to do each of the following:

• Use queries to return complex result sets
• Manage database objects to aid query performance
• Tune queries to perform more efficiently

This course is for anyone who currently uses Transact SQL to extract data from a Microsoft SQL Server database and wishes to expand their knowledge particularly in the areas of data analysis and improving query speed.

Course Outline

Analytical Functions

• Use of advanced summary functions
• Use of hierarchical queries
• Use of analytical summary functions, e.g. moving averages, running totals
• Use of ranking functions

Useful Database Objects

• Principles of using indexes
• How to create and maintain an index
• Use of clustered tables
• Use of partitioned tables
• Use of metadata in the master database

Query Performance Tracing

• Principles of query execution and optimisation
• Use of Execution Plan
• Use of table & index statistics
• Use of hints

Basic Data Warehouse Techniques

• Use of Indexed Views
• Use of Dimension & fact tables
• Use of Star & Snowflake designs

Duration

14 hours (usually 2 days including breaks)

Requirements

SQL: Fundamentals of Querying or equivalent knowledge.

Overview

Students will learn advanced queries and how to add, update, and delete data, tables, views, and indexes.

Course Outline

Lesson 1: Querying with unions and advanced joins

• Querying multiple tables with unions
• Advanced Joins
• Calculating with COMPUTE

Lesson 2: Querying with subqueries

• Subqueries

Lesson 3: Adding data

• Inserting data
• SELECT INTO

Lesson 4: Updating and removing data

• Updating records
• Deleting records
• Deleting the contents of tables

Lesson 5: Manipulating tables and views

• Creating tables
• Modifying tables
• Deleting tables
• Adding and removing views

Lesson 6: Manipulating indexes

• Adding and removing indexes

Lesson 7: Ensuring data integrity with transactions

• Transactions

Lesson 8: Creating databases

• Creating a database with SQL
• Deleting a database