|
osqlide |
Oracle SQL Intermediate - Data Extraction |
14 heures |
Limiting results
The WHERE clause
Comparison operators
LIKE Condition
Prerequisite BETWEEN ... AND
IS NULL condition
Condition IN
Boolean operators AND, OR and NOT
Many of the conditions in the WHERE clause
The order of the operators.
DISTINCT clause
SQL functions
The differences between the functions of one and multilines
Features text, numeric, date,
Explicit and implicit conversion
Conversion functions
Nesting functions
Viewing the performance of the functions - dual table
Getting the current date function SYSDATE
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
Limiting the function result grouping - the HAVING clause
Subqueries
Place subqueries in the SELECT command
Subqueries single and multi-lineage
Operators Subqueries single-line
Features grouping in subquery
Operators Subqueries multi-IN, ALL, ANY
How NULL values are treated in subqueries
Operators collective
UNION operator
UNION ALL operator
INTERSECT operator
MINUS operator
Further Usage Of Joins
Revisit Joins
Combining Inner and Outer Joins
Partitioned Outer Joins
Hierarchical Queries
Further Usage Of Sub-Queries
Revisit sub-queries
Use of sub-queries as virtual tables/inline views and columns
Use of the WITH construction
Combining sub-queries and joins
Analytics functions
OVER clause
Partition Clause
Windowing Clause
Rank, Lead, Lag, First, Last functions
Retrieving data from multiple tables (if time at end)
Types of connectors
The use NATURAL JOIN
Aliases tables
Joins in the WHERE clause
INNER JOIN Inner join
External Merge LEFT, RIGHT, FULL OUTER JOIN
Cartesian product
Aggregate Functions (if time at end)
Revisit Group By function and Having clause
Group and Rollup
Group and Cube
|
|
mariadbadmin |
MariaDB Database Administration |
14 heures |
MariaDB Administration training course is for anyone who wants to administrate the MariaDB database server. It is a comprehensive course covering all administrator duties.
The course explains how MariaDB Database works, what tools are available, how we can use them, how we can secure the MariaDB Database Server and configure it. During the training course you will learn how to manage user accounts and how the MariaDB Access Privilege System works. You also will learn how to maintain your database, backup and recover your databases and perform crash recovery.
Installing MariaDB server
Installing in Ubuntu/Debian
Installing in other Linux Distributions
Installation on Windows
MariaDB Server Files and Scripts
MariaDB Programs
MariaDB Server
MariaDB Client
GUI Tools
MariaDB Server Configuration
Server Options
The Server SQL Mode
Server System Variables
Dynamic System Variables
Server Status Variables
Shutdown Process
MariaDB Security Issues
Securing MariaDB Against Attacks
Security-Related Options
Security Issues with LOAD DATA LOCAL
MariaDB Access Privilege System
MariaDB Privilege System Overview
Privileges Provided by MariaDB
Connecting to the MariaDB Server - Stages
Access Control, Stage 1: Connection Verification
Access Control, Stage 2: Request Verification
Access Denied Errors
MariaDB User Account Management
Users and Passwords
Creating New Users
Deleting User Accounts
Limiting User Resources
Changing Passwords
MariaDB Database Maintenance
Backup and Recovery
Point-in-Time Recovery
Maintenance and Crash Recovery
myisamchk Syntax and Options
Getting Table Information
MariaDB Local Setting
National Characters and Sorting
MariaDB Server Time Zone
MariaDB Log Files
Error Log
General Query Log
Update Log
Binary Log
Slow Query Log
Log File Maintenance and Rotation
Running Multiple MariaDB Servers on the Same Machine
Running Multiple Servers in Windows
Running Multiple Servers in Windows as Services
Running Multiple Servers in Unix and Linux
Using Client Tools in a Multi-Server Environment
MariaDB Query Cache
The Concept of Query Cache
Testing Query Cache with SELECT
Configuring Query Cache
Checking Query Cache Status and Maintenance
The CONNECT Storage Engine
Installing the CONNECT storage engine
Creating and dropping CONNECT tables
Reading and writing CSV data using CONNECT
Reading and writing XML data using CONNECT
Accessing MariaDB tables using CONNECT
Using the XCOL table type
Using the PIVOT table type
Using the OCCUR table type
Exploring Dynamic and Virtual Columns in MariaDB
Creating tables with dynamic columns
Inserting, updating, and deleting dynamic column data
Reading data from a dynamic column
Using virtual columns
Performance and Usage Statistics
Installing the Audit Plugin
Using the Audit Plugin
Using engine-independent table statistics
Using extended statistics
Enabling the performance schema
Using the performance schema
Optimizing and Tuning MariaDB
Using SHOW STATUS
Controlling MariaDB optimizer strategies
Using extended Keys with InnoDB and XtraDB
Configuring the MyISAM segmented key cache
Configuring threadpool
Configuring the Aria pagecache
Optimizing queries with the subquery cache
Optimizing semijoin subqueries
Using microseconds in DATETIME columns
Updating the DATETIME and TIMESTAMP columns automatically
|
|
cpd200 |
CPD200: Developing Solutions on Google Cloud Platform |
24 heures |
This 3 day instructor-led class introduces participants to Solution Development for Google Cloud Platform. Through a combination of instructor-led presentations, demonstrations, and hands-on labs, students learn how to develop cloud-based applications using Google App Engine, Google Cloud Datastore, and Google Cloud Endpoints.
This class is intended for experienced application developers who want to learn how to develop solutions using Google Cloud Platform to create highly scalable backends for both web and mobile applications.
At the end of this oneday course, participants will be able to:
Manage Google Cloud Source Repositories using the Google Cloud Platform Console
Test an App Engine application using the App Engine SDK
Access the App Engine Development Server Console
Create an API using Google Cloud Endpoints
Test a Cloud Endpoint API using the API Explorer
Deploy an application to App Engine using the App Engine SDK
Design, structure and configure an App Engine application using multiple services
Create Client IDs using the Google Cloud Platform Console
Secure App Engine services and Cloud Endpoints APIs using authentication
Configure and upload new versions of App Engine services
Integrate Google Cloud Logging into App Engine applications
Review quota usage in a Google Cloud Platform project
Integrate different types of storage with App Engine applications
Create and implement a data model for use with Google Cloud Datastore
Implement a variety of queries in Google Cloud Datastore
Update the index configuration in Google Cloud Datastore
Implement transactions using Google Cloud Datastore
Review Google Cloud Trace reports in the Google Cloud Platform Console
Integrate the Memcache API into an App Engine application to increase performance
Configure, run and review the output of Google Cloud Security Scanner
Configure the scaling behaviour of individual App Engine Services
Create App Engine handlers for Push Task Queues
Send email from an App Engine application using the Mail API
Schedule Tasks in App Engine using the Cron Service
Update the configuration of the Cron Service
Secure Task Push, and Cron Service handlers
Export Google Cloud Platform data from a project
Delete Google Cloud Platform projects and resources
Module 1: Developing Solutions for Google Cloud Platform
Identify the advantages of Google Cloud Platform for solution development
Identify services and tools available for solution development using Google Cloud Platform
Compare examples of Google Cloud Platform architectures for solution development
Lab: Google Cloud Source Repositories
Create a project for the course
Use Google Cloud Shell to develop and test an application using the App Engine SDK
Configure Google Cloud Source Repositories to remotely host code in Google Cloud Platform
Module 2: Google Cloud Endpoints
Identify Cloud Endpoints features
Explain how to develop APIs using Cloud Endpoints
Compare development of Cloud Endpoints APIs using Java and Python
Lab: Google Cloud Endpoints
Review and edit Cloud Endpoints source code
Deploy an application to App Engine
Test a Cloud Endpoints API in the APIs Explorer
Module 3: App Engine Services
Explain the use cases for App Engine Services and how to use them in structuring an application
Identify how to deploy and access individual App Engine services
Explain how to route requests to individual services
Lab: Google App Engine Services
Review the code for a sample application used throughout the remainder of the course
Deploy multiple App Engine services to a single project
Module 4: User Authentication and Credentials
Compare authentication and authorization
Identify options for securing App Engine applications
Explain the use cases for Application Default Credentials
Lab: User Authentication
Configure the OAuth consent screen and create a client ID
Modify Conference Central to use your client ID
Test Conference Central authentication
Modify your admin service to require administrator rights
Module 5: Managing App Engine Applications
Explain the use cases for App Engine versions
Identify how to access App Engine monitoring and logging services
Explain the use of resource quotas and how to troubleshoot related errors
Lab: Managing Google App Engine Applications
Review App Engine settings, quotas, instances, and logs
Update App Engine services to log to Cloud Logging
Deploy new versions of your default and admin services
Route all traffic to a new version of the default service
Module 6: Storage for Solution Developers
Compare storage options for App Engine Solutions Developers
Explain the purpose of, and use cases for, Google Cloud Storage
Compare Cloud SQL integration with App Engine and Compute Engine
Explain basic Cloud Datastore terminology and concepts, including Entity Groups
Lab: Google Cloud Datastore
Update an existing application to save data persistently
Test saving application data to Cloud Datastore
List and view Cloud Datastore entities in the Google Cloud Platform Console
Module 7: Queries and Indexes
Identify available query filters for Cloud Datastore
Compare singleproperty, and composite indexes in Cloud Datastore
Configure and optimize indexes for Cloud Datastore
Lab: Google Cloud Datastore Queries and Indexes
Add support for querying entities by kind and ancestor
Add query filters to Cloud Datastore searches
Update an index configuration to support composite indexes
Module 8: Entity Groups, Consistency, and Transactions
Identify the steps of a Cloud Datastore write
Compare strong and eventual consistency in Cloud Datastore
Identify how to achieve strongly consistent queries
Identify best practises for Cloud Datastore transactions
Lab: Google Cloud Datastore Transactions
Add support for using Cloud Datastore transactions to an application
Add a Cloud Endpoint API method to view data from a different service
Module 9: App Engine Performance and Optimization
Identify Memcache types, use cases, and implementation patterns
Compare available scaling behaviours for application services
Configure application scaling for individual services
Lab: Google App Engine Performance and Optimization
Review Cloud Trace reports for an application
Configure and run a security scan for an application
Update an application to make use of memcache
Configure and test application scaling for application services
Module 10: Task Queues
Compare Push and Pull Queues
Explain how to schedule tasks with the Cron Service
Configure and securing Push and Pull Queues, as well as the Cron Service
Lab: Task Queue API
Add a task handler to send an email using the Mail API
Add a Cron Service handler and configuration to an existing application
Lab: Deleting Google Cloud Platform Projects and Resources
Export Google Cloud Platform data from a project
Delete Google Cloud Platform resources
Shut down a Google Cloud Platform project
|
|
hivehiveql |
Data Analysis with Hive/HiveQL |
7 heures |
This course covers how to use Hive SQL language (AKA: Hive HQL, SQL on Hive, HiveQL) for people who extract data from Hive
Hive Overview
Architecture and design
Aata types
SQL support in Hive
Creating Hive tables and querying
Partitions
Joins
Text processing
labs : various labs on processing data with Hive
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
|
|
mariadbdev |
MariaDB 10 Developer Course |
28 heures |
Created DBAs, Administrators and developers who are interested with getting involved in MariaDB 10 based on Linux system.
Even beginners, who have the basic skill and knowledge on Linux, can catch up with this course just if you follow the instructor's lab and explanation in detail.
This course is intended to practice enough Database Concept and SQL and to show it is very easy to understand how to use SQL and manage MariaDB on Linux system.
This course will be delivered to audience with 40% lectures, 50% labs and 10% Q&A. This five-day course strongly emphasizes lab-based activities
After this course, you can apply the knowledge, which you obtained through this course, to the other database systems such as MySQL, Oracle Database, MSSQL Server and PostgreSQL as well.
It can be deliver on any distribution (Ubuntu, CentOS are commonly used)
This course covers these kinds of topics:
Chapter 00 MariaDB 10 Developer Course Introduction
Chapter 01 MariaDB 10 Introduction
Chapter 02 Startup MariaDB 10
Chapter 03 MariaDB Tools - Command & GUI
Chapter 04 Retrieving Data using SQL
Chapter 05 Filtering Data using SQL
Chapter 06 Summarizing, Grouping & Combining
Chapter 07 Database, Table & Indexes
Chapter 08 Inserting, Updating & Deleting Data
Chapter 09 Table Joins
Chapter 10 Subqueries
Chapter 11 Views
Chapter 12 Stored Procedures
Chapter 13 Triggers
Chapter 14 MariaDB Datatypes
Chapter 15 Transaction Processing
Chapter 16 MariaDB User Management
Chapter 17 MariaDB Client Tools
|
|
dnswebmaildb |
Top 4 Linux/Unix Servers - DNS,Web,Mail and Database |
35 heures |
Created Linux/Unix Administrators and developers who are interested with getting involved in LInux/Unix Servers
Even beginners, who have the basic skill and knowledge on Linux, can catch up with this course just if you follow the instructor's lab and explanation in detail.
This course is intended to practice enough Managing Linux Servers and to show it is very easy to understand Linux/Unix servers.
This course will be delivered to audience with 40% lectures, 50% labs and 10% Q&A. This five-day course strongly emphasizes lab-based activities.
You'll learn how to deploy and manage Top 4 Linux Servers that provide highly useful network services to a mission-critical enterprise environment.
It can be deliver on any distribution (Fedora, CentOS are commonly used)
This course covers these kinds of topics:
Bind as a ;DNS server
Apache as a Web Server
Postfix as a Mail Server
MariaDB as a Database Server
Through this course, you will learn from the installation to High level features of each server. |
|
osqlint |
Oracle SQL Intermediate |
14 heures |
Audience
All who want to improve their basic skills in Oracle SQL and also systematize already gained knowledge.
Format of the course
25% lectures, 75% labs
Create complex queries to databases
Use available operators
Queries with multiple conditions
Creating tables and references
DDL commands (create, alter, and drop)
Create referential integrity
Normalization of tables (up to 3 normal form)
anomalies and ways to avoid them
Changes in the structure of existing tables
ALTER clause
Manipulation of data
DML commands (insert, update, delete)
Creating new users and granting permissions
DCL commands (grant, revoke)
Linking Tables
Internal and external joins
Data Aggregation
Features of grouping functions
Use the GROUP BY clause and HAVING
Grouping multiple column
Subqueries
multi-column
correlated
WITH clause
|
|
sqlfun |
SQL Fundamentals |
14 heures |
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.
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...)
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
SQL Dialects Overview
MySQL
Microsoft Access and SQL Server
Oracle and PostgreSQL
|
|
mariadbgc |
MariaDB Galera Cluster Administration |
21 heures |
This course is intended for database administrators.
The course presents options for High-Availability solutions using Galera Cluster.
You will learn the basics of Galera technology, as well as more advanced topics and practical knowledge related to configuring, optimizing and administering a Galera Cluster.
Topic overview
Why I need them and what are High-Availability solutions?
Cluster concepts
What is MariaDB Galera Cluster and what it offers to my organization?
Galera Cluster Management
How to start with Galera - what should I now before installation?
Architecture and functionality
First steps - Installation
Going deeper - Configuration and Set-up
Almost like a pro - Administration
Performance
Operations and operation modes
Upgrade Galera
Backups and restoring
Controlling state transfer between nodes
Load balancing
Monitoring
How to deal with Galera multi- master configuration
Advanced features
Security
Scalability
Replication
Advanced configuration
|
|
sqlmysqladv |
SQL Advanced in MySQL |
7 heures |
This course has been created for people already acquainted with SQL. The course introduces you into secrets common to all SQL databases as well as MySQL specific syntax, functions and features.
DQL (Data Query Language)
Correlation in FROM, WHERE, SELECT and HAVING clauses
Correlation and performance
Using CASE, IF, COALESCE functions
Using variables
Casting and converting
Dealing with NULL, NULL-safe operators
Using regular expression with REGEXP operator
Useful MySQL specific group by functions (GROUP_CONCAT, etc.)
GROUP BY WITH ROLLUP
EXISTS, ALL, ANY
Multitable OUTER JOIN
Rewriting subqueries as joins
DML (Data Modification Language)
Multi-row inserts
INSERT by SELECT
Using subqueries in DML statements
Using variables in DML queries
Locking tables and rows
Updating data in many tables
IGNORE clause
REPLACE clause
DELETE versus TRUNCATE
DDL (Data Definition Language)
Creating tables with select
Temporary tables
Stored Procedures
Short introduction to MySQL stored procedures
|
|
3358 |
Oracle 11g - SQL language for developers - Workshop |
35 heures |
For who
Workshops are dedicated as a first step for developers and designers of applications based on Oracle databases. Participants do not need to have any prior knowledge of the Oracle database, or other relational database systems, even though such knowledge may be useful.
Exams and Certificates
The plan covers the training material required to pass the exam 1Z0-047 Oracle Database SQL Expert and obtain the title of Oracle Database SQL Certified Expert
Purpose of training
The workshop aims to familiarize participants with the Oracle database techniques to build database structures and data manipulation. Particular emphasis is placed on the participant to see across the board, which offers opportunities to design and build applications Relational Database Management System, Oracle Database, and to be able to independently work with her.
The content of the training
Introduction to database technology and the organization of the work environment
Acquisition and modification of data
Construction of the repository application
Safety and concurrency runtime
Notes
The workshops are based on the software version 11g XE
Introduction to the Oracle database
Database Architecture
Relational model database
Users diagrams sessions
Tools
Introduction to the SELECT statement
Screening and selection (WHERE clause)
Sorting
Data types, operators, and service NULL
Built-in scalar functions
Actions to date
National and regional settings in SQL
The analysis of aggregated data
Funkcje grupujące
Klauzula distinct
Klauzule GROUP BY and having
Retrieving data from multiple tables
Inner and outer joins (INNER JOIN, OUTER JOIN)
ANSI SQL syntax, and other methods connectors (SELF JOIN, NATURAL JOIN)
Collective operators (UNION, UNION ALL, INTERSECT, MINUS)
Subqueries
Subqueries simple
Correlated subqueries
Operators EXISTS and NOT EXISTS
Other types of subqueries
Inquiries hierarchical and samples
Construction of the tree (CONNECT BY PRIOR clause and START WITH)
The SYS_CONNECT_BY_PATH
Data samples (SAMPLE clause)
Data manipulation (DML)
"INSTRUCCIONES INSERT, UPDATE, DELETE
Operacja na dużych zbiorach (INSERT FIRST, ALL INSERT, MERGE)
Dictionary system
Concurrent users work
Transactions
Locks
FLASHBACK
Users and Permissions
Creating and modifying user patterns
Permissions and roles
Managing data storage - logical layer
Tables, temporary tables, index-organized tables
Limitations
Indexes
The views, sequences, synonyms, materialized views
Units stored PL / SQL
Modeling and restore the data model using Oracle SQL Modeler
Moving Data
A logical copy of the data - datapump import and export
Loading data - sqlLoader
External tables
Links database
Automating tasks
dbms_jobs, dbms_scheduler
|
|
3044 |
Using and managing the database in MySQL |
14 heures |
Participant will learn:
Can I use MySQL for free?
What do I offer a commercial license?
How to install a MySQL database?
How to perform basic operations on the installation?
What are the available tools for managing and programming in a MySQL database?
Legal aspect MySQL
MySQL's dual license policies
Commercial License
Open Source License
Installing MySQL
Standard installation of MySQL (binary files)
Installing MySQL on Windows or Unix-like (GNU / Linux, FreeBSD)
Tuning the server after installation, testing
Upgrading MySQL
Connecting to the server
Making queries
Creating and using databases
Creating and selecting a database
Creating tables
Importing data into the table
Obtaining information about the database and tables
Using the mysql command in batch (Batch Mode)
Discussion of the utilities
Ways to call the program
Setting program options (command line, the configuration file, environment variables)
Setting the program variables via command line
GUI tools for managing and programming the database
MySQL Administrator
MySQL Query Browser
Toad for MySQL
|
|
3092 |
langage SQL avec MSSQL |
14 heures |
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?
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 |
|
3331 |
Oracle 11g - Advanced data analysis - workshops |
35 heures |
For who
These workshops are a continuation and complement the training of Oracle 11g data-analysis - a workshop dedicated to end users, data analysts and software testers for execution of professional duties which require more advanced techniques for working with the database. As in the case of the training of Oracle 11g data-analysis - workshops, participants do not need to be computer scientists, but the people who need efficient, and above all efficiently use the Oracle database, processing and analyzing stored in the large amounts of data
Purpose of training
The workshop aims to broaden the knowledge of participants about programming using PL / SQL and issues related to the optimization commands. Particular emphasis in this training is the performance of the data collection to ensure a smooth operation for very large amounts of data. In addition, workshops supplement an understanding of the elements necessary to any advanced user of Oracle in their daily work, such as copying and downloading large amounts of information, data modeling, modification of an existing data model and reverse engineering techniques using Oracle tools.
The content of the training
Moving and loading
Procedural language PL / SQL allows you to expand the analytical capabilities of a SELECT statement
Improving the performance of SQL queries
Data modeling and acquisition and modification of the existing data model based on Oracle SQL Modeler
Notes
The workshops are based on the software version 11g XE
Managing the data repository
Control repository using the system dictionary, SQL script generation
Creating tables and relationships with SQL Modeler
Play schemes tables and relationships using reverse engineering and SQL Modeler
Other repository objects: views, sequences, synonyms, temporary tables, stored routines
System privileges and object-oriented database roles
The programming language PL / SQL
Basic information about the language, data types, variables,
Deciding loops
Embedding SQL statements in the code PL / SQL
Stored subprograms: procedures and functions
Handling errors and exceptions
Query processing (cursors)
The mass data operations (processing array)
Generators data streaming functions
Permissions in PL / SQL
depending on model
Triggers
Running tasks
The task scheduler
Powerful scheduling mechanism (SCHEDULER)
Methods for data transfer and charging
Links database
Loading data from text files
external Tables
Import / Export Data
Database performance and tuning SQL statements
Architecture database and instance
Analysis of the plan to run a command to read the estimated costs and the actual
Operation and control of Oracle optimizer with hints (hintów)
The use of statistics and histograms
Indexing data
Optimize index readings
Optimizing joins, sorts, and aggregation
|
|
3033 |
Administering in Microsoft SQL Server |
21 heures |
The course is designed for administrators, developers and database developers.
The objectives of the training:
acquire and strengthen the skills to create and manage databases
knowledge of the syntax and use SQL to retrieve and modify data
apply safety rules in the database
the use of advanced elements (replication, automation, BI)
the use of Microsoft SQL Server capabilities to create complex reports and solutions for developers
Basic information about databases
Database files, Database Client / Server
Relational database management systems (RDBMS)
SQL Server versions and the differences between them
Express, Standard, Enterprise
Workgroup, Mobile , Developer
SQL Server Tools
SQL Server Management Studio
SQL Server Agent
Services in SQL Server
Database Services
Analysis Services
Reporting Services
Integration Services
Base system
Master
Model
Msdb
Tempdb
Distribution
Resource
Create a database
The database files and their location
File size and its growth
Partitioning tables
Data Modification Language (DML)
INSERT
UPDATE
DELETE
Data Definition Language (DDL)
Designing Tables
Columns and Attributes
Determine the type of data
Indexes and Keys
Indexes
Clustered Indexes
Non-Clustered Indexes
REBUILD vs. REORGANIZE
Creating a database application
Server-side programming
Procedures
Views
Functions
Triggers
Models play
Simple
Full
Bulk logged
Backup
Full
Differential
Transaction Log
Backup Strategies
Strategy full copy of the database
Strategy of full backups and transaction log
The strategy of incremental database backup
Strategy copy of the database files
Basic security and administration of SQL Server
Automation
Maintenance Plan
Jobs
Basic security and administration of SQL Server
Server Roles
Database Roles
SA Account
Creating Accounts
Schemes
High Availability
Log Shipping
Database Mirroring
Server Clustering
Replication
Snapshot Replication
Transactional Replication
Merge Replication
Activity Monitor SQL Server
SQL Server Profiler
Upgrade
In-Place vs. Side-By-Side
Service-Pack
Cluster
Best Practices |
|
advsqlpt |
Advanced SQL, Stored Procedures and Triggers for Microsoft SQL Server |
14 heures |
The aim of this course is to provide a clear understanding of the advanced use of (SQL) for Microsoft SQL Server and the advanced use of Transact-SQL. For more in depth coverage of the topics this course can be run as a three day course.
Review of Structured Query Language
DQL, DML, DDL
The GROUP BY, HAVING Clause
Sub-queries and Correlated Sub-queries
Advanced Update & Delete Statements
Sub-queries
Correlated Sub-queries
Procedural Programming
Variables
Control-Of-Flow Statements
IF, WHILE, CASE, GOTO, RETURN
Managing Errors
Responding To Errors
RAISERROR
PRINT
Using Transactions
Introduction To Transactions
Transaction Isolation Levels
Deadlocks
Transactional Error Handling
Implementing Cursors
Declaring Cursors
OPEN, FETCH, CLOSE
DEALLOCATE
CURRENT OF
Stored Procedures
Creating Stored Procedures
Passing values into a Stored Procedure
Returning Information From Stored Procedures
Altering Stored Procedures
Triggers
Creating Triggers
Transactional Error Handling
Using Inserted and Deleted Tables
|
|
3319 |
Data Analysis with Oracle 11g - workshop |
35 heures |
For who
Workshops are dedicated to end users, data analysts and software testers. Workshop participants do not need to be computer scientists, but employees who need efficiently and effectively use the Oracle database, processing and analyzing information contained in it
Exams and Certificates
The training plan includes, among others material required to pass the exam: 1Z0-051 Oracle Database 11g: SQL Fundamentals I, the first step to getting most certifications Oracle's Database
Purpose of training
The training is designed to familiarize participants with the Oracle database, the basic tools and techniques of data collection. Particular emphasis in this training is the acquisition of substantially correct and reliable data.
The content of the training
The organization of the working environment
Introduction to the relational model of data storage
Techniques for the collection, analysis and synthesis of information stored in the database
Modify the information stored in the database
Note
The workshops are based on the software version 11g XE
Introduction to the Oracle database
Database Architecture
Relational model database
Users diagrams sessions
Tools
Introduction to the SELECT statement
Screening and selection (WHERE clause)
sorting
Data types, operators, and service NULL
Built-in scalar functions
Actions to date
National and regional settings in SQL
Regular expressions
The analysis of aggregated data
grouping Functions
DISTINCT clause
The GROUP BY clause and HAVING.
Summary (clauses ROLLUP, CUBE, GROUPING)
Retrieving data from multiple tables
Inner and outer joins (INNER JOIN, OUTER JOIN)
ANSI SQL syntax, and other methods connectors (SELF JOIN, NATURAL JOIN)
Collective operators (UNION, UNION ALL, INTERSECT, MINUS)
Subqueries
Subqueries simple
Correlated subqueries
Operators EXISTS and NOT EXISTS
Other types of subqueries
Inquiries hierarchical and samples
Construction of the tree (CONNECT BY PRIOR clause and START WITH)
The SYS_CONNECT_BY_PATH
Data samples (SAMPLE clause)
Analytic Functions
Generating summaries
Definition window
Statistical Functions
New features in Oracle 11g
Inquiries Pivot (PIVOT, UNPIVOT)
Tables and referential integrity
Managing tables (CREATE, ALTER, DROP, RENAME)
Referential integrity (constraints)
Data manipulation (DML)
INSTRUCCIONES INSERT, UPDATE, DELETE
Operacja na dużych zbiorach (INSERT FIRST, ALL INSERT, MERGE)
Concurrent users work
Transactions
Locks
FLASHBACK
Brief overview of schema objects
vistas
sequences
indexes
|
|
sqlmysql |
SQL in MySQL |
14 heures |
How to build a query?
What opportunities has the SQL in a MySQL database?
What is a relational database?
What is the structure and SQL commands?
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
The command COMMIT, ROLLBACK, SAVEPOINT
|
|
sqlsqlsvr |
SQL in SQL Server |
14 heures |
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.
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)
Handling Date
|
|
3308 |
Oracle 11g - SQL language for administrators - workshops |
21 heures |
For who
The workshop is intended for beginners, starting to work with the Oracle database, future administrators and users of systems based on this database need able to use the SQL language for the extraction and modification of the information contained in the systems.
Exams and Certificates
The training plan coincides with the material required to pass the exam: 1Z0-051 Oracle Database 11g: SQL Fundamentals I, the first step to getting most certifications Oracle's Database
Purpose of training
The workshop aims to introduce participants to work with the Oracle database, making them familiar with the SQL language to the extent required for the efficient operation of the system and to take in the future obligations of database applications and Oracle databases.
The content of the training
The organization of the working environment
Introduction to relational databases
Extraction of the data using the SELECT statement
Modifying data using INSERT, UPDATE, DELETE
Overview of schema objects
Remarks
The workshops are based on 11g XE software
Introduction to Oracle
Database Architecture
Relational model database
Users diagrams sessions
Tools
Introduction to the SELECT statement
Screening and selection (WHERE clause)
sorting
Data types, operators, and service NULL
Built-in scalar functions
Actions to date
National and regional settings in SQL
Regular expressions
The analysis of aggregated data
Grouping functions
DISTINCT clause
Clauses GROUP BY and HAVING
Retrieving data from multiple tables
Inner and outer joins (INNER JOIN, OUTER JOIN)
ANSI SQL syntax, and other methods connectors (SELF JOIN, NATURAL JOIN)
Collective operators (UNION, UNION ALL, INTERSECT, MINUS)
Subqueries
Subqueries simple
Correlated subqueries
Operators EXISTS and NOT EXISTS
Other types of subqueries
Inquiries hierarchical and samples
Construction of the tree (CONNECT BY PRIOR clause and START WITH)
The SYS_CONNECT_BY_PATH
Data samples (SAMPLE clause)
Data manipulation (DML)
INSERT, UPDATE, DELETE
Operations on a large set of (INSERT FIRST INSERT ALL, MERGE)
Concurrent users work
Transactions
Locks
FLASHBACK
Overview of schema objects
Vistas
Sequences
Synonyms private and public
Indexes
|
|
sqlmsa |
SQL in Microsoft Access |
14 heures |
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.
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
SQL Dialects Overview
MySQL
Microsoft Access and SQL Server
Oracle and PostgreSQL
|
|
68403 |
Introduction to SQL Server 2012 Integration Services (SSIS) |
28 heures |
ETL and SSIS
Packages
Control Flow
Workflow
Constraints
Data Flows
Variables
Containers
Transactions
Errors and Debugging
Logging
Slowly Changing Dimensions
Deploying a Package
Security
Scripting
Best Practices
|
|
oplsqlfun |
ORACLE PL/SQL Fundamentals |
21 heures |
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.
Introduction
Aims and Objectives
Course Schedule
Introductions
Pre-requisites
Responsibilities
SQL Tools
Objectives
SQL Developer
SQL Developer - Connection
Viewing Table Information
Using SQL, SQL Developer - Query
SQL*Plus Login
Direct Connection
Using SQL*Plus
Ending the Session
SQL*Plus Commands
SQL*Plus Environment
SQL*Plus Prompt
Finding Information about Tables
Getting Help
Using SQL Files
iSQL*Plus, Entity Models
The ORDERS Tables
The FILM Tables
Course Tables Handout
SQL Statement Syntax
SQL*Plus Commands
What is PL/SQL?
What is PL/SQL?
Why Use PL/SQL?
Block Structure
Displaying a Message
Sample Code
Setting SERVEROUTPUT
Update Example, Style Guide
Variables
Variables
Datatypes
Setting Variables
Constants
Local and Global Variables
%Type Variables
Substitution Variables
Comments with &
Verify Option
&& Variables
Define and Undefine
SELECT Statement
SELECT Statement
Populating Variables
%Rowtype Variables
CHR Function
Self Study
PL/SQL Records
Example Declarations
Conditional Statement
IF Statement
SELECT Statement
Self Study
Case Statement
Trapping Errors
Exception
Internal Errors
Error Code and Message
Using No Data Found
User Exceptions
Raise Application Error
Trapping Non-defined Errors
Using PRAGMA EXCEPTION_INIT
Commit and Rollback
Self Study
Nested Blocks
Workshop
Iteration - Looping
Loop Statement
While Statement
For Statement
Goto Statement and Labels
Cursors
Cursors
Cursor Attributes
Explicit Cursors
Explicit Cursor Example
Declaring the Cursor
Declaring the Variable
Open, Fetching the First Row
Fetching the Next Row
Exit When %Notfound
Close
For Loop I
For Loop II
Update Example
FOR UPDATE
FOR UPDATE OF
WHERE CURRENT OF
Commit with Cursors
Validation Example I
Validation Example II
Cursor Parameters,
Workshop
Workshop Solution
Procedures, Functions and Packages
Create Statement
Parameters
Procedure Body
Showing Errors
Describe a Procedure
Calling Procedures
Calling Procedures in SQL*Plus
Using Output Parameters
Calling with Output Parameters
Creating Functions
Example Function
Showing Errors
Describe a Function
Calling Functions
Calling Functions in SQL*Plus
Modular Programming
Example Procedure
Calling Functions
Calling Functions In An IF Statement
Creating Packages
Package Example
Reasons for Packages
Public and Private Sub-programs
Showing Errors
Describe a Package
Calling Packages in SQL*Plus
Calling Packages From Sub-Programs
Dropping a Sub-Program
Finding Sub-programs
Creating a Debug Package
Calling the Debug Package
Positional and Named Notation
Parameter Default Values
Recompiling Procedures and Functions
Workshop
Triggers
Creating Triggers
Statement Triggers
Row Level Triggers
WHEN Restriction
Selective Triggers - IF
Showing Errors
Commit in Triggers
Restrictions
Mutating Triggers
Finding Triggers
Dropping a Trigger
Generating an Auto-number
Disabling Triggers
Enabling Triggers
Trigger Names
Sample Data
ORDER Tables
FILM Tables
EMPLOYEE Tables
Dynamic SQL
SQL in PL/SQL
Binding
Dynamic SQL
Native Dynamic SQL
DDL and DML
DBMS_SQL Package
Dynamic SQL - SELECT
Dynamic SQL - SELECT Procedure
Using Files
Using Text Files
UTL_FILE Package
Write/Append Example
Read Example
Trigger Example
DBMS_ALERT Packages
DBMS_JOB Package
COLLECTIONS
%Type Variables
Record Variables
Collection Types
Index-By Tables
Setting Values
Nonexistent Elements
Nested Tables
Nested Table Initialisation
Using the Constructor
Adding to a Nested Table
Varrays
Varray Initialization
Adding Elements to a Varray
Multilevel Collections
Bulk Bind
Bulk Bind Example
Transactional Issues
BULK COLLECT Clause
RETURNING INTO
Ref Cursors
Cursor Variables
Defining REF CURSOR Types
Declaring Cursor Variables
Constrained and Unconstrained
Using Cursor Variables
Cursor Variable Examples
|
|
67592 |
SQL Server 2008 Administration |
28 heures |
This SQL Server Administration training course teaches students how to administer a SQL Server 2008.
Objectives
Install and configure Microsoft SQL Server
Create databases and tables
Implement indexes and partitions
Take database snapshots
Implement service broker for asynchronous processing of database requests
Create and use full-text indexes
Secure SQL Server and implement policy-based management
Recover data
Automate administrative tasks with the SQL Server Agent
Use Dynamic Management Views to monitor the database and troubleshoot problems
Configure a SQL Server for high availability using failover clustering, database mirroring, log shipping, and replication
Overview of Microsoft SQL Server 2008
Database Engine
Business Intelligence
Installing and Configuring SQL Server 2008
Editions of SQL Server
Infrastructure Requirements
Service Accounts
Collation Sequences
Authentication Modes
SQL Server Instances
Upgrading to SQL Server
Installing SQL Server
Using the Tools in SQL Server 2008
SQL Server Documentation
Management Tools in SQL Server
Performance Management Tools
Business Intelligence Tools
Creating Databases
SQL Server System Databases
SQL Server Database Structure
Creating a Database
Moving Databases
Designing Tables
Naming Objects
Schemas
Data Types
Column Properties
Creating Tables
Computed Columns
Sparse Columns
Constraints
Database Diagrams
Indexes
Index Structure
Clustered Indexes
Nonclustered Indexes
Included Columns
Filtered Indexes
Online Index Creation
Index Management and Maintenance
XML Indexes
Spatial Indexes
Partitioning
Partition Functions
Partition Schemes
Partitioning Tables and Indexes
Managing Partitions
Database Snapshots
Creating a Database Snapshot
Reverting Data Using a Database Snapshot
Service Broker
Service Broker Architecture
Message Types and Contracts
Queues and Services
Conversations
Sending and Receiving Messages
Queue Activation
Prioritization
Full-Text Indexing
Full-Text Catalogs
Full-Text Indexes
Querying Full-Text Data
Security
Configuring the Attack Surface
Endpoints
Principals, Securables, and Permissions
CLR Security
Data Encryption
Policy-Based Management
Overview of Policy-Based Management
Facets
Conditions
Policy Targets
Policies
Policy Categories
Policy Compliance
Data Recovery
Database Backups
Recovery Models
Database Restores
SQL Server Agent
Creating Jobs
Creating Maintenance Plans
Creating Alerts
Dynamic Management Views
Overview of DMVs
Retrieving Object Metadata
Database Diagnostics
High Availability
Failover Clustering
Database Mirroring
Log Shipping
Replication
|
|
osqlfun |
ORACLE SQL Fundamentals |
21 heures |
This 3 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.
Introduction
Overview
Aims and Objectives
Sample Data
Schedule
Introductions
Pre-requisites
Responsibilities
Relational Databases
The Database
The Relational Database
Tables
Rows and Columns
Sample Database
Selecting Rows
Supplier Table
Saleord Table
Primary Key Index
Secondary Indexes
Relationships
Analogy
Foreign Key
Foreign Key
Joining Tables
Referential Integrity
Types of Relationship
Many to Many Relationship
Resolving a Many-to-Many Relationship
One to One Relationship
Completing the Design
Resolving Relationships
Microsoft Access - Relationships
Entity Relationship Diagram
Data Modelling
CASE Tools
Sample Diagram
The RDBMS
Advantages of an RDBMS
Structured Query Language
DDL - Data Definition Language
DML - Data Manipulation Language
DCL - Data Control Language
Why Use SQL?
Course Tables Handout
SQL*Plus
SQL*Plus Login
Easy Connect
Using /NOLOG
Using SQL*Plus
Ending the Session
SQL*Plus Commands
SQL*Plus Environment
SQL*Plus Prompt
LOGIN.SQL File
Changing the Password
Finding Information about Tables
Getting Help
Where Clause
Using SQL Files
iSQL*Plus
SQL*Plus Commands
Data Retrieval
SQL Developer
SQL Developer - Connection
Viewing Table Information
Using SQL, Where Clause
Using Comments
Character Data
Users and Schemas
AND and OR Clause
Using Brackets
Date Fields
Using Dates
Formatting Dates
Date Formats
TO_DATE
TRUNC
Date Display
Order By Clause
DUAL Table
Concatenation
Selecting Text
IN Operator
BETWEEN Operator
LIKE Operator
Common Errors
UPPER Function
Single Quotes
Finding Metacharacters
Regular Expressions
REGEXP_LIKE Operator
Null Values
IS NULL Operator
NVL
Accepting User Input
Data Definition
Creating a Table
Datatypes
Simple Create Example
Naming Tables
Constraints
Not Null
Primary Key
Foreign Key
Check
Unique
Altering Constraints
Full Create Example
Data Dictionary
Alter Table
Secondary Indexes
B-tree Index
Bitmap Index
Create Index
Explain Plan
Using Indexes
Clusters
Partitioned Tables
Creating a Partitioned Table
Rename
Drop Statement
Flashback Table
Managing the Recycle Bin
Data Update
Insert
Some Values
Insert
All Values
Insert
Date Values
Insert
TO_DATE
Default Values
Using Substitution Variables
Transactions
Commit
Rollback
Using Constraints
Update
Date Arithmetic
Update
TO_DATE
TRUNC
Delete
Truncate
Sequences
Grant
Create Synonym
Create Public Synonym
Locking
Revoke
Savepoint
Auto Commit
Multi-Table Retrieval
Calculations
Precedence
ROUND Function
Column Alias
Date Arithmetic
Using Aliases
CEIL and FLOOR
Cartesian Product
Table Join
Table Alias
Selecting the Join Column
Joining without Selecting
Views
Dropping Views
Finding Views
Derived Columns
With Check Option
Snapshot Views
Flashback Query
Using Functions
TO_CHAR
TO_NUMBER
LPAD
RPAD
NVL
NVL2 Function
DISTINCT Option
SUBSTR
INSTR
Date Functions
Aggregate Functions
COUNT
Group By Clause
Rollup and Cube Modifiers
Having Clause
Grouping By Functions
DECODE
CASE
Workshop
Sub-Query & Union
Single Row Sub-queries
Union
Union - All
Intersect and Minus
Multiple Row Sub-queries
Union – Checking Data
Outer Join
More On Joins
Joins
Cross Join or Cartesian Product
Inner Join
Implicit Join Notation
Explicit Join Notation
Natural Join
Equi-Join
Cross Join
Outer Joins
Left Outer Join
Right Outer Join
Full Outer Join
Using UNION
Join Algorithms
Nested Loop
Merge Join
Hash Join
Reflexive or Self Join
Single Table Join
Workshop
Advanced Queries
ROWNUM and ROWID
Top N Analysis
Inline View
Exists and Not Exists
Correlated Sub-queries
Correlated Sub-queries with Functions
Correlated Update
Snapshot Recovery
Flashback Recovery
All
Any and Some Operators
Insert ALL
Merge
Sample Data
ORDER Tables
FILM Tables
EMPLOYEE Tables
The ORDER Tables
The FILM Tables
PL/SQL
What is PL/SQL?
Why Use PL/SQL?
Block Structure
Sample Code
SELECT Statement
Using Variables
Accepting User Input
Exceptions
Other DML Statements
Creating Procedures
Showing Errors
Describe a Procedure
Calling Procedures
Creating and Running Functions
Showing Errors
Describe a Function
Calling Functions
Creating Triggers
Showing Errors
Query Optimisation
Query Optimisation
Creating The Tables
Timing SQL Statements
Other Timing Statements
Explain Plan
Creating the PLAN_TABLE Table
Using SET AUTOTRACE
Collecting Statistics
Primary Key
Secondary Indexes
The Query Optimizer
Rule Based Optimization
Cost Based Optimization
Choose Keyword
Gathering Statistics
Optimizer Hints
How to Specify Hints
Using Indexes
Index Types
B*tree Indexes
Bitmap Indexes
Index-organized table
When to Create Indexes
Choosing Composite Indexes
Using Objects
Object-oriented Database
Object-relational Database
Creating Objects
Creating Tables with Objects
Using Objects in Tables
Large Object Support
LOB Datatypes
Creating Tables with LOBs
Inserting an Empty LOB
Creating Tables with BFILEs
Creating Directories for BFILEs
Inserting a BFILE
SQL*PLUS REPORTS
Objectives
ACCEPT and PROMPT
Define and Undefine
Creating an SQL*Plus Report
Break Command
Compute Command
Saving the Output in a File
Utilities
What is a Utility?
Export Utility
Using Parameters
Using a Parameter file
Import Utility
Using Parameters
Using a Parameter file
Unloading Data
Batch Runs
SQL*Loader Utility
Running the Utility
Appending Data
|
|
66437 |
Microsoft SQL Server 2008/2012 (MSSQL) |
14 heures |
This course has been created for delegates already acquainted with SQL in Microsoft SQL Server Environment 2008/2012. The course focuses on set-based querying and query tuning, working with indexes and analyzing execution plans.
The training also covers table expressions, ranking functions and how to deal with partitioned tables.
Module 1. Query Tuning
Tools for Query Tuning
Cached Query Execution Plans
Clearing the Cache
Analyzing Execution Plans
Hints
Using the Database Engine Tuning Advisor
Index Tuning
Table and Index Structures
Index Access Methods
Indexing Strategies
Module 2. Subqueries, Table Expression, and Ranking Functions
Writing Subqueries
Using Table Expressions
Using Ranking Functions
Module 3. Optimizing Joins and Set Operations
Fundamental Join Types
Join Algorithm
Set Operations
Using INTO with Set Operation
Module 4. Aggregating and Pivoting Data
Using the OVER Clause
Different types of aggregations (Cumulative, Sliding and Year-To-Date)
Pivoting and Unpivoting
Setup Custom Aggregations
Using GROUPING SETS Subclause
CUBE and RULLUP Subclauses
How to materialize Grouping Sets
Module 5. Using TOP and APPLY
SELECT TOP
Using the APPLY table operator
TOP n at the Group Level
Implementing Paging
Module 6. Optimizing Data Transformation
Inserting data with Enhanced VALUES Clause
Using the BULK Rowset Provider
Using INSERT EXEC
The Sequence Mechanisms
DELETE with joins
UPDATE with joins
MERGE statement
The OUTPUT Clause with INSERT
The OUTPUT Clause with DELETE
The OUTPUT Clause with UPDATE
The OUTPUT Clause with MERGE
Module 7. Querying Partitioned Tables
Partitioning in SQL Server
How to write queries on partitioned tables
How to write queries on partitioned views
|
|
ddavsvbsqls |
Developing Desktop Applications with Visual Studio 2012, VB.NET and SQL Server 2012 |
21 heures |
This course is divided into 3 main sections and is made up of a mixture of presentations and practical exercises.
VB.NET Language in Visual Studio 2012
VB.NET Object Orientation
VB.NET and Sql Server 2012
Part I. VB.NET Language in Visual Studio 2012
Module 1. Introduction to Visual Basic 2012
The Object-Oriented Programming
The Visual Studio 2012 IDE
Creating a new Application
Using the Help System
Module 2. The Microsoft .NET Framework
The .NET Framework Classes
Executing the Code
Common Language Runtime
Code Loading and Execution
Application Isolation
Security
Interoperability
Exception Handling
Module 3. The Visual Basic 2012 Language
Data Types
Storing Variables
Using Methods
Making Decisions
Working with Data Structures
Using Arrays, Enumerations and Collections
Module 4. Building Windows Applications
Responding to Events
Creating the Toolbar
Creating the Status Bar
Using Multiple Forms
OpenFileDialog and SaveDialog controls
PrintDialog and FolderBrowserDialog controls
Understanding Menu Features
Creating Menus
Context Menus
Part II. VB.NET Object Orientation
Module 5. Building Objects
Understanding Objects
Encapsulation
Methods and Properties
Managing Events
Building Classes
Using Constructors
Managing Inheritance
Module 6. Advanced Language Constructs
Using Lambda Expressions
Using Async and Wait
Using Iterators
Module 7. Exception Handling and Debugging
Handling Exceptions
Try, Catch, Finally
The Throw Keyword
The Exit Try Statement
Using Exit Try Statement
Using Exception Properties
Logging Errors
Module 8. Parallel Programming Using Tasks and Threads
Launching Parallel Tasks
Transforming Sequential Code to Parallel Code
Parallelizing Loops
Specifying the Desired Degree of Parallelism
Creating and Managing Tasks
Part III. VB.NET and Sql Server 2012
Module 9. Database Programming with Sql Server 20012 and ADO.NET
The ADO.NET architecture
The Connection class
The Command and DataReader Classes
The ExecuteReader(), ExecuteScalar(), ExecuteNonQuery() methods
Using Parameterized Commands
Calling Stored Procedure
Managing Transactions
Module 10. Data Components and the DataSet
Building a Data Access Component
Managing Disconnected Data
The DataSet Class
The DataAdapter Class: Filling a DataSet, working with Multiple Tables and Relationships
The DataView Class
Module 11. Using Data Binding
Basic Data Binding
Data Source Controls
The SqlDataSource
Inserting, Updating, Deleting and Selecting records
|
|
3082 |
Microsoft Access - download the data |
14 heures |
The course is designed for persons with pre-information databases using SQL queries or queries.
Queries
The types of queries
Query Wizard
Query Design View
query Properties
Grouping in Queries
Create a simple select query
Crosstab queries
Query the search duplicates
Not matching the search query data
Parameter queries
Queries (forming a table, delete, append, updating)
Create a table with a query
Archiving data using an append query
Troubleshoot queries
Expressions in Queries
Examples of the use of expressions
Calculations in queries
Functions
SQL in Access
Download the data
Restrict results
Sorting Data
SQL Functions
Aggregating data using the grouping
Retrieving data from multiple tables
subqueries
Operators collective
|
|
transsqlbas |
Transact SQL Basic |
14 heures |
Delegates will gain an understanding of the basic principles of Structured Query Language as well as being able to do each of the following:
Construct queries to extract and filter data from a SQL Server database
Create summarised results
Change, derive and format data to suit the required output
Change data and maintain database components and definitions
This course is for anybody who needs information from a Microsoft SQL Server database. It is suitable for either system developers or people from other areas who need to use SQL to extract and analyse data.
Basics
Selection of all columns/fields
Selection of certain columns/fields
Use of distinct/unique
Selection of certain rows/records
Selection of values in a range
Selection of values matching a pattern mask
Selection of values within a list
Treatment of null values
How to sort and order data
Selection of calculated and derived values
How to control column headings in query results
How to send query results to external files
Joining Tables
Principles of joining tables:
Use of cartesian join
Use of inner join
Use of non-equi join
Use of outer join
Joining Queries
Union operator
Intersect operator
Except operator
Simple Functions
Conversion functions
Date functions
Number functions
Text functions
Group/summary/aggregate functions
Sub-Queries
Principles of sub-queries
How to filter rows from main query
Use of nested sub-query
Use of multi-column sub-query
Use of correlated sub-query
Use of sub-query as an inline view and common table expression
Use of sub-query as a column in main query
Case Statements
Principles of case statements
Use of case statement to derive column values
Use of nested case statements
Use of case statements to produce pivot tables
Use of case statement with sub-queries
Data Manipulation
How to insert values into a table
How to copy values between tables
How to update values
How to delete records
How to change data via views
Use of transactions
How to lock rows and tables
Data Definition
Principles of a relational database and data normalisation
Use of primary key and foreign key relationships and constraints
How to create tables
How to alter tables
How to create views
Use of synonyms
How to remove tables and views
|
|
SQL100 |
T-SQL Fundamentals with SQL Server Training Course |
16 heures |
This SQL training course is for people who want to acquire basic skills to deal with SQL Server Databases. Course will help the members to learn:
How to work with SQL Server and SQL Server Management Studio.
Meaning of Databases, SQL and RDBMS etc.
How to create tables, use DDL, DML and DAL.
Which are the various RDBMS Packages in the market and how they compare with each other.
An Introduction to NoSQL and how they are organizations are changing into hybrid databases.
Course Outline:
What is the meaning of Databases. Comparison of RDBMS and DBMS
Different RDBMS available in the market.
What is SQL Server? Working with SQL Server Management Studio
Working with sublanguages like DDL, DML, DAL.
Creation of Tables, data types, Constraints and their definition.
Using Insert, Delete and Update statements.
Using Select Query and its various operators.
Use of Null, Not Null, And, OR, Between, Exists, Order by, Group by, Having clause
What are inbuilt functions? Math, String and Datetime functions.
Working with Views. How they are used for data access.
Working with Joins. Joins types and getting data from multiple tables.
Working with Sub Queries. What is a correlated subquery. Difference between Sub Query and Joins.
What are Common Table Expressions. Using Recursive Common Table expressions.
|
|
transsqladv |
Transact SQL Advanced |
7 heures |
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.
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
|
|
osqldevdm |
Oracle SQL for development and database management |
35 heures |
Database Development
Recapping the basic principles behind relational databases
Concepts and terminology
Retrieving data using the SELECT statement
Using simple and more complex JOINS to retrieve data from multiple tables
SELF, INNER and OUTER joins
Restricting and sorting data, conditional expressions
Single row functions: string, date and time manipulation
IF-THEN-ELSE statements
Conversion of data between types
Creating aggregated reports
Using correlated and uncorrelated subqueries in SELECT statements
Retrieving and manipulating data using subqueries
Running Data Manipulation Statements in Oracle to manage database transactions
Query optimisation and efficiency
Database Management
The Oracle Data Dictionary: introduction and usage
Creating views, indexes, constraints and synonyms
Controlling and revoking user access to schema objects (tables, views)
Managing indexes and constraints
|
|
pgsqladm |
PostgreSQL Administration and Development |
28 heures |
This course handles the administration and performance tuning of PostgreSQL databases. Attendees will learn the use of specialised PostgreSQL (AKA Postgres) modules such as replication, connection pooling and full text searching.
What is PostgreSQL?
A Brief History of PostgreSQL
Conventions
Further Information
Bug Reporting Guidelines
Introduction to PostgreSQL
Installation and Creating Database
The SQL Language
Advanced Features
The SQL Language
SQL Syntax
Data Definition
Data Manipulation
Queries
Data Types
Functions and Operators
Type Conversion
Indexes
Full Text Search
Concurrency Control
Performance Tips
Server Administration
Installation from Source Code
Installation from Source Code on Windows
Server Setup and Operation
Server Configuration
Client Authentication
Database Roles
Managing Databases
Localization
Routine Database Maintenance Tasks
Backup and Restore
High Availability, Load Balancing, and Replication
Recovery Configuration
Monitoring Database Activity
Monitoring Disk Usage
Reliability and the Write-Ahead Log
Regression Tests
Client Interfaces
libpq - C Library
Large Objects
ECPG - Embedded SQL in C
The Information Schema
Server Programming
Extending SQL
Triggers
The Rule System
Procedural Languages
PL/pgSQL - SQL Procedural Language
PL/Tcl - Tcl Procedural Language
PL/Perl - Perl Procedural Language
PL/Python - Python Procedural Language
Server Programming Interface
Internals
Overview of PostgreSQL Internals
System Catalogs
Frontend/Backend Protocol
PostgreSQL Coding Conventions
Native Language Support
Writing A Procedural Language Handler
Writing A Foreign Data Wrapper
Genetic Query Optimizer
Index Access Method Interface Definition
GiST Indexes
GIN Indexes
Database Physical Storage
BKI Backend Interface
How the Planner Uses Statistics
|
|
mssql2016 |
MS SQL Server 2016 |
14 heures |
Performanace and Management
Enhanced Database Caching
Query data store
In-Memory OLTP in SQL Server 2016
Development
Temporal Database
Temporary Table and Variable Table in-memory
Native JSON
High Availability and Security
Enhanced AlwaysOn
Always Enrypted
Row-level Security
Dynamic Data Masking
Data Insight and Business Intelligence
Operational Analytics
New functionality Columnstore Index
Direct Query in SSAS Tabular
R Integration (language R in SQL Server)
Enhanced SSIS
Enhanced MDS
Reporting Services
New Report Server
Mobil Reports
SQL Server Mobile Report Publisher
Cloud and Hybrid
Stretch Database
Enhanced backup to Azure
Migration SQL Server to Azure
SSIS and Data Factory
|
|
416 |
Introduction to Firebird |
14 heures |
Classic, SuperClassic or Superserver?
Installation packages
Embedded Server for Windows
What is in the kit?
Default disk locations
Linux
Windows
Installing Firebird
Installing the Firebird server
Installing multiple servers
Testing your installation
Performing a client-only install
Server configuration and management
User management: gsec
Security
Windows Control Panel applets
Administration tools
Working with databases
Connection strings
Connecting to an existing database
Creating a database using isql
Firebird SQL
Protecting your data
Backup
How to corrupt a database
|
|
osqlbgn |
Oracle SQL for beginners |
21 heures |
Listeners
This training is addressed for people starting to work with the SQL language in Oracle database
The course answer for questions:
How to build a query?
What possibilities have SQL?
What is a relational database?
What is the structure and SQL commands
Relational database models
The structure of a relational database
Connection types of tables
The normalization and denormalization 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 operator
Limiting results
The WHERE clause
Comparison operators
LIKE Condition
Prerequisite BETWEEN ... AND
IS NULL condition
Condition IN
Boolean operators AND, OR and NOT
Many of the conditions in the WHERE clause
The order of the operators.
DISTINCT clause
Sorting Data
The ORDER BY clause
Sorting by multiple columns or expressions
SQL functions
The differences between the functions of one and multilines
Features text, numeric, date,
Explicit and implicit conversion
Conversion functions
Nesting functions
Viewing the performance of the functions - dual table
Getting the current date function SYSDATE
Handling of NULL values
Aggregating data using the grouping
Grouping functions
How grouping functions treat NULL values
Create groups of data - the GROUP BY clause
Grouping multiple columns
Limiting the function result grouping - the HAVING clause
Retrieving data from multiple tables
Types of connectors
The use NATURAL JOIN
Aliases tables
Joins in the WHERE clause
INNER JOIN Inner join
External Merge LEFT, RIGHT, FULL OUTER JOIN
Cartesian product
Subqueries
Place subqueries in the SELECT command
Subqueries single and multi-lineage
Operators Subqueries single-line
Features grouping in subquery
Operators Subqueries multi-IN, ALL, ANY
How NULL values are treated in subqueries
Operators collective
UNION operator
UNION ALL operator
INTERSECT operator
MINUS operator
Insert, update, and delete data
INSERT command
Copy data from another table
UPDATE command
DELETE command
TRUNCATE command
Transactions
Commands COMMIT, ROLLBACK, and SAVEPOINT
DDL commands
The main database objects
Rules for naming objects
Creating tables
The data types available for columns
DEFAULT option
Option NULL and NOT NULL
Managing tables
Referential integrity CHECK, PRIMARY KEY, FOREIGN KEY, UNIQUE
Create a table by the query
Delete a table DROP TABLE
DESCRIBE command
Other schema objects
Sequences
Synonyms
Views
|
|
osqladv |
Oracle SQL Advanced |
14 heures |
Listeners
This course is designed for people who want to use the advanced features of SQL in Oracle
The course answers the questions
How to build advanced queries?
How to create advanced reports?
Control user access
User Management
System permissions and object
Granting
Receiving permission
Roles
Using the links
Managing schema objects
ALTER TABLE command
Adding, modifying, and deleting columns
Add, remove, turn off constraintów
Create indexes
Flashback operations
External tables
Operations on large data sets
MERGE command
DML operations of podzapytaniami
DML operations with RETURNING clause
INSERT command multi tables
Conditional expressions
CASE expression
DECODE expression
Generate reports by grouping related data
The GROUP BY clause
The HAVING clause
Aggregating data - ROLLUP and CUBE operators
Identification summaries - GROUPING function
Aggregating data - GROUPING SETS operator
Managing data in different time zones
Time zones
Variations TIMESTAMP
Differences between DATE and TIMESTAMP
Conversion operations
Advanced subqueries
Subqueries
Multi-column subqueries
The subquery in the FROM clause
Correlated subqueries
WITH clause - re-use query blocks
Join tables
Inequality in the WHERE clause and the FROM clause
Semijoin
Antijoin
The processing of hierarchical data
The tree structure
hierarchical Queries
Pseudo column
Sort data in a hierarchical query
Useful functions
Regular expressions
Simple and complex patterns
|
|
3057 |
Oracle SQL Language |
14 heures |
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
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)
|
