Oracle SQL, PL/SQL – practical application, database programming, and database object management

Training process

Training needs analysis

If you have specific requirements regarding the training programme, we will carry out a training needs analysis for you. This will guide us on which aspects of the programme should receive greater emphasis, so that the training programme meets your specific needs.

What will you gain?

Smooth Oracle connectivity - You will configure Oracle database access through ODBC and learn to work with data from Access and Excel, making it easier for you to connect the database with everyday business tasks.

Reliable data exchange - You will learn practical import and export methods and move data into spreadsheets and pivot analysis, so you can prepare reports and business summaries faster and with fewer manual steps.

Efficient SQL Developer work - You will build queries in both text and visual modes and create tables in Oracle SQL Developer, which will help you prepare, test, and refine database solutions more efficiently.

Confident data operations - You will retrieve, add, update, and delete records with SELECT, INSERT, UPDATE, and DELETE, and you will better match data types to real use cases in your own database work.

Stronger multi-table queries - You will master JOIN and UNION, sort result sets, and remove duplicates, allowing you to create clear, reliable queries even when you work with more complex and related datasets.

Clear analysis and calculations - You will use aggregate functions, operators, AND and OR conditions, plus text, numeric, and date functions to build analytical queries faster and reduce unnecessary manual processing.

Database object creation - You will create tables and views and modify them with CREATE, ALTER, and DROP, giving you the ability to develop and adjust database structures to fit actual project requirements.

First steps in PL/SQL - You will start writing and running stored procedures, using variables, controlling program flow, and building nested queries, opening the way to automate your everyday database tasks.

Training programme

1. The process of creating and configuring a data source in Oracle SQL

creating and configuring a data source using ODBC,
Microsoft Access as an SQL database client: data import, linked tables and management.

2. Discussion of data exchange methods between the Oracle SQL server

discussion of data import and export tools,
ODBC and the connection wizard for data analysis in a spreadsheet and pivot table in Microsoft Excel.

3. Overview of the Oracle SQL Developer tool

creating queries in text and graphical mode,
building tables using the graphical user interface.

4. Detailed discussion of the basic data types used in Oracle SQL

basic data types in SQL and their application,
selection queries and saving SQL views,
SQL statements: retrieval and modification of records SELECT, UPDATE, INSERT, DELETE, SELECT INTO.

5. Description of various table joining techniques in SQL queries

JOIN, LEFT, RIGHT, OUTER joins,
sorting and limiting the query result ORDER BY ASC/DESC, TOP, TOP PERCENT,
combining the results of many tables and queries using UNION,
removing duplicated values from the query result DISTINCT, DISTINCTROW.

6. Presentation of aggregate operations in SQL

aggregation of results in queries COUNT, MIN, MAX, SUM,
operators in SQL: =, <>, >=, <=, IN, BETWEEN AND, LIKE,
criteria in queries AND, OR, NOT.

7. Detailed discussion of functions built into Oracle SQL

string functions: LEFT, RIGHT, MID, REPLACE,
number and date functions: DATE, DATEADD, DATEDIFF.

8. Description of more advanced techniques for creating and modifying objects in Oracle SQL

creating TABLE and VIEW objects using the CREATE command,
modifying and deleting existing database objects: ALTER and DROP commands.

9. Discussion of methods for optimizing the performance of SQL queries

optimization of query performance,
use of created objects in external applications: Microsoft Excel and Access,
advanced table creation: default values, relationships, indexes.

10. Overview of stored procedures in Oracle PL/SQL and programming techniques

creating and running procedures,
controlling program flow,
using variables in procedures: local and global variables,
using DECLARE, GO in stored procedures,
temporary tables and array variables,
data conversion and modification,
nesting queries.

What are the prerequisites for participating in the training?

Basic computer skills - You should be comfortable using Windows, launching applications, saving files, and navigating folders, so you can focus on database work instead of learning the environment itself.

Understanding of tables and data - You should understand what tables, columns, and records are and be able to work with simple data in Excel or Access, because the course includes data exchange and analysis tasks.

Analytical thinking - You should be able to see relationships between data and understand simple logical conditions, because during the course you will build queries with filters, joins, and criteria.

Readiness to write queries - You should be ready to work with typed commands and test results step by step, because the training covers both SQL statements and introductory programming in PL/SQL.