ORACLE training. Oracle Training Required Prerequisites

And you could get acquainted with the advantages of these systems, why they are needed and which courses are better to choose for them. In today's article, I decided to talk more about another powerful DBMS that is considered advanced in the IT field (and quite rightly) - this is Oracle SQL.

Oracle Database is an object-relational type DBMS manufactured by the largest software company Oracle. In the distant 70s, it became one of the first systems to manage data in the database and use the SEQUEL language, which was new at that time, which became the progenitor of SQL. Among embedded DBMS vendors, Oracle is the undisputed leader, ahead of IBM Microsoft, and occupies more than a quarter of the market (26.3%).

In many respects, such popularity of the system is provided due to its unique features:
- Real Application Cluster technology improves system performance and resiliency
- Real Application Testing technology reduces the cost of performing planned hardware and software configuration changes
- Active Data Guard creates a standby server that comes on when it fails (low cost)
— Total Recall provides the ability to unload the database from unused information, allowing you to send a request to receive it at any time

This is not the whole list of Oracle SQL pluses, but the main thing is that by choosing Oracle SQL courses, you are treading your way to a successful career that will bring you not only interesting projects, but also pleasantly surprise you from a financial point of view.

Oracle SQL courses to become an administrator in 40 days

The Complete Oracle SQL Certification Course

Instructor: Imtiaz Ahmad (experienced software developer)
Cost: $195
Number of students: 8 303+
Program volume: 32 lectures; 9.5 hours
Level of preparation (requirements for the course): no programming skills are needed

What will you learn?
- Understand how the base works Oracle data
— Program in SQL to solve various problems in databases
— Prepare for official certification from Oracle
- Prepare yourself to be a database developer

SQL knowledge is in demand both by large companies and in web development projects where databases are required. All training in this course takes place strictly from scratch, so even beginners without any knowledge and skills are welcome. At the same time, you will learn not only theory, but also gradually, as you master the material, perform various practical tasks. In addition to the tasks themselves, the program also provides solutions to them.

In just one or two months (you choose the learning rate yourself), you will easily become a professional in Oracle SQL, the most popular relational database in the world. Feel free to press the treasured button and go through the training!

Oracle SQL - Learn SQL step by step

Instructor: Amarnath Reddy (Data Warehouse Architect with over 15 years experience in IT)
Cost: 200$
Number of students: 10 196+
Program volume: 126 lectures; 6.5 hours
Level of preparation (requirements for the course): basic knowledge of PC

What will you learn?
- Understand database terminology
— Basic concepts about SQL
— Filtering, selecting and grouping data from the database
— Creating tables and changing their structure
- Binding to tables
— User access control
- Productivity increase
— Creation of views and other database objects using SQL commands
- Joining data from different tables

In just seven weeks, the teacher will teach you the theoretical foundations of working with databases and SQL. With real practical examples, you will learn how to create tables and create your own databases. All the basic operations for working with the Oracle DBMS and their deep understanding will help you confidently apply your knowledge in practice, and not just stuff you with boring theory.

As in the previous course, you do not need any programming skills or knowledge to pass. The course is ideal for both beginners and those who want to brush up on their knowledge of SQL.

Oracle SQL - Become a Certified SQL Developer from Scratch!

Lecturer: Omer Dagasan (Oracle Architect and Software Engineer, SQL-PL/SQL, Java, C# and PHP Specialist)
Cost: 200$
Number of students: 4 855+
Program volume: 105 lectures; 12.5 hours
Proficiency level (course requirements): Oracle Database Software and Oracle SQL Developer installed

What will you learn?
- Easy to create SQL queries
— Create solutions for any SQL tasks
- Understand database terminology
- Read, group, filter and restrict access to data
- Insert, update and delete data
- Create and modify tables
- Connectors, operators and subqueries
- Oracle SQL Functions
- Oracle Data Types

The 12.5-hour course program contains more than a hundred lectures, clearly organized and divided into 16 sections. It includes weekly surveys so you can monitor your progress.

The content of the course will be available to you even after the completion of the training, so you can always return to the material covered. And one more good news for those who are not very good with the perception of English by ear - there are subtitles in the course.

If you are undecided, check out the entire list of courses on this topic. What are you waiting for - while others are considering whether they should start programming, you should act and not waste precious time. Sign up for your favorite Oracle SQL course today!

Alexander is the founder of the Web Lab of Success website project, created to support beginners and continuing Internet entrepreneurs. He is a convinced workaholic with professional experience in managing the editorial office of an online magazine, creating and managing his own online store. Main occupation: promotion of businesses (including online stores) through Facebook and Google Adwords. Main passion: website monetization through affiliate marketing tools and Google Adsense. Personal verified records: 3 million blog visitors per month.

Course name in Russian:
Oracle Database: SQL Basics

This course introduces students to the basic concepts of a relational database. It provides students with a basic knowledge of SQL, allowing a developer to write queries against one or more tables, modify table data, and create database objects. Students will also gain an understanding of system and object privileges. The course covers creating indexes and constraints, as well as modifying existing schema objects. Students will learn how to create and query external tables. In addition, students will learn the advanced features of SQL in writing complex queries and manipulating data, as well as using data dictionary views to retrieve metadata and report on these schema objects. Students will become familiar with some of the built-in date and time functions available in Oracle Database. The course also discusses the use of SQL-supported regular expressions. The main development tool used in the course is Oracle SQL Developer; SQL*Plus is used as an additional one.

Students learn:

Data Modification Language (DML) Commands
System and object privileges
Create reports that include a selection of sorted data
Creating and viewing external tables
Creating Indexes and Integrity Constraints
Schema Object Management
Manage access to individual database objects

Circle of listeners:

Data warehouse administrators
Developers
Application Development
PL/SQL developers
Form Developers
System analysts
Business Intelligence

Course Objectives:

Overview of the main structural components Oracle 12c
Create reports from aggregated data
Using Subqueries
Extract rows and columns from tables
Using DML Commands to Manipulate Data in Oracle 12c
Creating tables to store data
Using Views to Display Data
Access control to individual objects
Schema Object Management
Displaying data from multiple tables using ANSI SQL 99 join syntax
Managing Objects with Data Dictionary Views
Creating multi-column subqueries
Using SQL Functions to Get the Desired Output
Using Scalar and Correlated Subqueries
Create reports from sorted and restricted data

Necessary preliminary preparation:

Understanding the concepts and techniques of data processing

Recommended pre-treatment:

Oracle Database: SQL and PL/SQL Fundamentals NEW
Oracle Database: Program with PL/SQL NEW

Course program:

Introduction to Oracle
Introduction to SQL
Variables when executing commands
Built-in Functions
Selecting data from multiple tables
Subqueries
Using SQL*Plus Commands
Creating tables
Data definition language
Oracle Data Dictionary
Modifying tables and integrity rules
Indices
Sequences
Representation
Data Manipulation Language
Competition and blocking
Users and data protection

After completing these courses, you will receive:

Oracle certificate at the end of the course
The Oracle certificate, unified all over the world, is a guarantee of quality: during the training, all Oracle requirements for the teacher, the curriculum, the organization of the educational process and the equipment used were met.
High-quality educational process, proven over the years
The training center was authorized by Oracle back in 1995, and i2 training has been going on since 2003. We have trained several thousand DBAs for large companies and small and medium businesses.
Teachers-practitioners of the highest level
We strive to ensure that the teachers of the Training Center are practitioners who simultaneously participate in the implementation of real projects for the development of information systems and technical support. Two of our teachers have the highest status - Oracle Certified Master.
Education in Moscow or another place convenient for you
Training is conducted in Moscow. At the request of the client, courses can be taught in classrooms near Moscow, as well as on the territory of customers and partners. Teachers have experience in teaching courses in regions from Moscow to Sakhalin, as well as abroad.
Individual approach to the customer
We always adhere to a flexible approach when working with clients, providing maximum convenience in terms of training payment schemes and document flow, as well as taking into account the wishes of customers when drawing up contracts.
Inclusion of training in expenses when accounting for taxation
The license of the Department of the Moscow Region for educational activities, issued by the CA, gives customers the right to include training costs in expenses when accounting for taxation. Organizations that train their employees are exempt from value added tax (VAT).

Why do I need Oracle Authorized Training?

It is impossible to become a qualified Oracle specialist in a short time. Training in an authorized Training Center is an absolutely necessary stage that allows you to guarantee the success of mastering complex materials and significantly reduce the time for training a specialist.

Oracle courses for beginners are the only way to quickly master a large amount of information at a level sufficient to start independent work. In turn, for specialists with experience working with Oracle, authorized training provides a complete picture of the technology being studied and the systematization of knowledge. Seminars for experts will be interesting and useful to a wide audience: database administrators, support service specialists, application developers.

Oracle's specialist training methodology takes into account the fact that a specialist needs practice. It is recommended to listen to a number of courses not immediately after completing the basic courses, but after a few months and subject to active practical activity during this period.

Oracle Authorized Training at the Learning Center provides the following benefits:

Programs, methodology, scope of training and tutorials are developed and supplied by the Oracle Corporation
Uniformity of all aspects of training and certificates issued to students around the world.
Oracle courses are taught only by instructors certified by Oracle.
The course program is fully consistent with the Oracle Certified Professional (OCP) certification test program. An OCP certification can only be obtained by completing Oracle Authorized Training.
Oracle's control over the quality of reading each course through mandatory questioning of all students.

An online store site that presents Oracle courses from one of the main Oracle authorized training centers in Russia.

Why is it profitable to place an order for training through an online store site?

The main thing is saving your time.

Using our catalog of training courses, you will be able to:

Get acquainted with the programs of courses of the most famous Educational centers of Russia.
Choose a convenient date for the course.
Get qualified advice from a specialist online store.
Pay for tuition in 20 ways.
Get discounts.

What needs to be done for this?:

1. Select the course you are interested in in the catalog of courses.

2. Checkout.

course date set: if the date of the course is scheduled, when placing an order, select a date that is convenient for you. All dates of the course can be found in the course description, if you wish to register for a later date or choose a convenient time for you, please indicate this information when placing an order. Attention! If the date of the course is scheduled, then for all students the reservation of a place is made only after the payment for the courses. Please pay in advance as groups fill up quickly.

Hello my readers. The article is intended Oracle database newbies. For those who are just going to study this subject area and become Oracle Database Administrators. So where to start??? I will note right away that there is no ideal starting level for becoming an Oracle DBA, but it is highly desirable that there be a genuine interest in database hardware, as well as a decent amount of knowledge in operating systems, UNIX and NT servers, as well as issues related to disks and memory.

It also helps a lot to have programming or development skills, because you will often have to work with developers. The most commonly used operating system for Oracle databases is UNIX, namely the UNIX versions manufactured by Hewlett-Packard (HP) and Sun Microsystems (Sun). IBM supplies the AIX variant of the UNIX operating system, but offers its own proprietary database product called DB2 Universal Database to accompany it.

If you want to train as a professional database administrator Oracle Database 11g, you need to take the following two training courses from Oracle or some other vendor: Oracle Database 11g:

Administration Workshop I (Oracle Database 11g: Administration Workshop I);
Oracle Database 11g: Administration Workshop II (Oracle Database 11g: Administration Workshop II).

Total exist three levels of certification for Oracle DBAs. The first is the Oracle Certified Associate (OCA) certification, the Oracle Certified Professional (Oracle Certified Professional) certification most commonly sought after by Oracle database professionals, and the last, the Oracle Certified Master (Oracle Certified Master) certification, which requires a lengthy two-day practice exam.

All Oracle Database 11g Database Administrator Certification candidates are required to take one of the approved physical or online courses in order to meet the new practice course requirement. Those who use Real Application Clusters (RAC) clusters or distributed Oracle databases in the company must take additional, specialized training courses. For those with a firm that uses the UNIX operating system and no prior experience with it, it may be best to start with a basic UNIX (or Linux) course from HP, Sun, Red Hat, or another vendor.

This course is not required to obtain an Oracle DBA certificate, but it will certainly be useful for those who are new to UNIX or Linux environments. Oracle itself also offers several administration courses. Linux systems, and even the possibility of obtaining a certificate in managing Oracle on Linux within Oracle programs Certified Expert Program. Of course, those who plan to use Oracle databases in Windows environment, may well do without taking a long and formal course on managing Windows, provided that they are quite familiar with the operating system Windows system, or even work as a Windows system administrator.

On a note! It should not be forgotten that Oracle Corporation is not the only company that provides Oracle training. While Oracle University is a big institution with great courses, other private providers offer just as great, and in some cases even better courses. As is the case with all courses, the quality of teaching directly depends on the experience and communication skills of the teacher. And also, we should not forget that going somewhere to seminars is not at all obligatory: it is quite possible to purchase self-learning CDs and study on your own, and it is several (about 15) times cheaper than at physical training with an instructor. Even more effective is to subscribe to Oracle's online training program called Oracle iLearning (http://ilearning.oracle.com). It's cheaper than buying DVDs and gives you access to hundreds of Oracle University courses. If you decide to take these courses, you must make sure that you have access to the server with the actual database. Oracle provides very well-designed sample schemas that you can use to hone your SQL skills either with its own database under development on a UNIX server or with a free download of Oracle Database 11g Enterprise Edition for Windows on your desktop. With this approach, training will be much faster.

As you begin your role as an Oracle Database 11g DBA, you will find that the real world of Oracle databases is much broader and more complex than what has been covered in the various courses you have attended. As each new facet of the database is discovered, it will become clearer and clearer how the software works, why it works, and why sometimes it doesn't work. Only then can you learn more about databases and the software used to manage them. After reading really all the materials that Oracle and others offer, you should still not worry, because there are always new versions, with new functionality and new mechanisms of work, which practically guarantees the endless flow of new information.

After working as a database administrator for one or two years, you will already know enough to competently administer databases and troubleshoot common database problems. Those who will also continue to improve their programming skills during this time (mainly by writing scripts for the UNIX shell and working with PL/SQL) can even begin to create complex scripts to monitor and tune database performance. After that, those who move on will be able to learn much more about their database software and thereby increase their knowledge and their contribution to the work of the organization. Oracle is constantly releasing new tools that you can also learn to improve the performance of your production databases. Although developers, testers and system administrators are also doing their best for the benefit of the organization, it is the DBA who will ultimately pave the way for new and effective ways applying new database features.

This article is intended primarily for Oracle DBMS administrators (and those who want to become them), it will be very useful for applied developers, as well as for users of this complex system. We are all waiting for an exciting journey in the world of Oracle, and I will help you not to get lost in it.

We will start from the very basics, from the simplest, and gradually we will move on and on. I'll warn you right now - it won't be easy. But are you ready to share the difficulties with me? If so, then go ahead!

Why will it be difficult for you? Because one must not just skim through the text, not just enjoy the process of reading, not just be satisfied with reading abstruse material, but WORK above the text. Exactly WORK. I will often describe complex relationships, if you do not draw them on paper, do not study every point, then there will be a gap. Gaps will accumulate until huge voids are formed. In the end, you will ask yourself: "What have I learned?". I think you already guessed what the answer will be.

Set a goal - to "understand EVERYTHING", if, nevertheless, something turned out to be incomprehensible - most likely I am to blame. Write to me, and in the next issues we will consider incomprehensible issues in much more detail.

Why will it be difficult for me? The fact is that I will also study with my subscribers. And since I am responsible for your advancement, since I will have to answer questions, collect material, I simply have to go one step forward. And while you are studying the current issue, I will have to prepare the next one.

As I promised, we will start with the simplest.

Brief history of ORACLE.

In 1977 Larry Ellison, Bob Miner, and Ed Owets organized their business under the name Relational Software Incorporated (RSI). It was this company that laid the foundation for the Oracle relational database management system (RDBMS). Ellison, Miner, and Owets decided to develop a RDBMS using C and a SQL interface. And soon the first version (prototype) was released. Buyers in 1979 Oracle RDBMS version 2 was introduced, which ran on Digital PDP-11, running OS RSX-11. Then it was ported to the DEC VAX system.

1983 became the herald of the release of version 3, which brought changes to the SQL language, increased system performance and added some other improvements. Unlike the previous versions, the third version was written entirely in C. Since then, RSI has changed its name to Oracle Corporation.

Oracle version 4 was introduced in 1984. This version supported both the VAX OS and the IBM VM. This version provided the ability to multi-user stable data reading. Version 5 appeared in 1985. and marked a turning point in the DBMS market as it pioneered client-server technology using SQL*Net. The fifth version was also one of the first MS DOS programs to break the 640Kb barrier.

In 1988 Oracle introduced version 6. This version introduced low-level locking and many other performance and functionality improvements (including sequence generation and lazy writes). Oracle is already running on multiple platforms and operating systems. In 1991 Oracle RDBMS Parallel Processing Server version 6.1 for DEC VAX system was released. Soon this version began to support other platforms.

Oracle 7 was released in 1992, and there were many architectural changes in memory and I/O. Oracle 7 is already a full-fledged RDBMS product that users are accustomed to and has been using for many years.

In 1997 version 8 was released, which brought object model, new properties and administration tools.

In 1999 version 8i (Oracle 8.1.5) with built-in Java was released.

In 2001 Released version 9i. According to the developers, more than 400 changes have been made compared to previous version. Characteristic changes - "intellectualization" automated systems and enhanced analytics capabilities.

As you can see, the Oracle product is already 25 years old, and we have to make up for all these "lost" years in a much shorter period. latest version product includes 75 different server products, but most of them are beyond the scope of our course.

Basic concepts and conditional abbreviations

Before we begin our study of Oracle, it is necessary that everyone be clear about the terms that will be encountered in the text. Each issue of the mailing list will have a "Basic Concepts" section so that readers do not waste their time searching for definitions for unfamiliar words.

DB (DB)- Database. A collection of data specially organized for ease of retrieval. The database is the actual data.

DBMS (DBMS)- Database Management System. Oracle software is a DBMS.

RDBMS (RDBMS)- Relational Database Management System. Internal access to data is carried out in a relational way. Oracle is a RDBMS.

Buffer is some volume random access memory The used to store data. The buffer contains data that is expected to be used, or that has been used most recently. In most cases, a buffer is a copy of a block of data that is stored on the hard drive. The data in the buffer can be modified and written to disk, and a buffer can also be created to store data temporarily. In relation to Oracle - buffers contain those blocks of data that were recently accessed. The collection of buffers makes up the database buffer cache. The buffer also stores temporary entries in the transaction log, which are then written to disk (the transaction log buffer).

Cache- memory area for quick access to data. From a hardware point of view, this is a small (in terms of RAM) amount of memory that is much faster than main memory. This amount of memory is used to reduce the time it takes to frequently download data or instructions to the central processing unit (CPU). The CPU itself contains a built-in cache. In Oracle, a set of buffers (block buffers - that is, blocks of data in RAM) and a shared pool are considered a cache, since they serve to store data and instructions that facilitate fast access. Caching is a very useful mechanism that greatly increases the speed of data access. Since it is usually not possible to fit all the buffers in RAM, special algorithms are used (the most common is the storage of the most frequently used blocks).

Block- the smallest unit of data storage in the Oracle DBMS. Contains header information and the block itself (data or PL/SQL code). The block size is configurable from 2 to 16Kb.

Bottleneck- Components that limit the performance or efficiency of the system.

Data Dictionary- a set of tables used to maintain information about the database.

Checkpoint- an operation that causes all changed data (data blocks in memory) to be written to disk. This is a key factor in the problem of quickly recovering a database after a crash.

Schema- collection of database objects.

SGA (System Global Area) A shared memory area used to store data and control information for an Oracle instance. The SGA is allocated in memory when an Oracle instance starts up and is freed when it shuts down. SGAs are made up of data buffers, a changelog buffer, and a shared pool. This is one of the most important concepts and we will cover it in more detail later.

ORACLE Configurations

There are many types of configurations. Let's look at the main ones, analyze and define the characteristics.

OLTP (Online Transaction Processing)- operational processing of transactions. This is the most common configuration. An OLTP system consists of users who interact with the system. These systems are usually used for the prompt entry of primary information (filling out contracts, checking numbers credit cards, asynchronous transactions, etc.).
Characteristic features of OLTP systems: typically supports a large number of users working with the RDBMS. Since users wait for data to be returned to requests, response time is of great importance. OLTP systems are associated with intensive read-write processes. Depending on the application, the read-write rating may vary.

DSS (Decision Support System)- Decision support systems are used in decision-making processes. These decisions can be based on information such as sales volume in certain regions, a sample of buyers of a certain product, a sorted list email addresses and etc.
Features of DSS: Long-running queries versus large amounts of data. Users of DSS systems are forced to wait minutes, hours, and sometimes several days for a response to a request. Data is usually collected from various sources and then processed. The DSS system is associated with an intensive reading process (writing processes
occur much less frequently).

Data Warehouse is a large scale system that consists of both OLTP and DSS. These systems typically work with hundreds of gigabytes of data and serve a huge number of users.
Features of a data warehouse: has some attributes of DSS systems, i.e. long-running queries, as well as real-time components. These components are often used as data sources for DSS queries.

Information Store (Data Mart)- this is a reduced version of the data warehouse (focused on solving highly specialized tasks), while retaining many of the features of the Data Warehouse.
Characteristic features of the information shop: typically 100 gigabytes of data or less. Just like the data store supports a large number of users and allows you to generate complex solutions.

Video Server: Allows you to support a large number of video streams. These video streams can be used on demand, as entertainment, and as educational courses.
Characteristic features of the video server: Must have a wide bandwidth to support multiple video streams. Also, it must be able to handle a large I/O load. When reading from devices, large blocks of data are loaded at once, which are not very fragmented.

Web server: designed to work with static and dynamic web pages. These pages can be very simple or complex, generated from a database. The Oracle Web Server is typically used for commercial web applications. Such
Apps allow shoppers to browse catalogs that include product images and even video illustrations. The buyer can purchase the product he likes.
Features of the Oracle Web Server: usually supports a significant number of users, contains a large amount of data that is accessed frequently, and at the same time, data that is not accessed very often. Server performance can be improved by a large amount of RAM.

OLAP(Online Analytical Processing)- analytical processing in real time. Typically used in conjunction with multidimensional data. OLAP users are financial analysts or marketing personnel working with data on a global level.
Characteristic features of OLAP systems: require a large amount of disk space and powerful computing systems. An OLAP system can only support a small number of users. However, the number of users depends on the specific configuration.

Conclusion

So, we got acquainted with the history of Oracle Corporation, learned a few important concepts and learned the most important configurations. You can congratulate yourself - you have begun your dive into the wonderful world of Oracle.

To use Oracle properly, you need to have a good understanding of the SQL language. Well " Oracle. Programming in SQL and PL/SQL" reveals the full range of possibilities of the SQL language in Oracle and a number of aspects of non-obvious features of building typical database structures.

PL/SQL - procedural language, developed by Oracle for writing subroutines stored in the database. PL/SQL provides a common framework for procedural programming in both client-side and server-side applications, including server-stored routines, packages, and database triggers.

Knowledge is given for Oracle versions 8i, 9i, 10g, 11g and 12c. The course is accompanied by practical exercises that allow you to consolidate your understanding of the basic concepts and master the basic programming techniques in SQL and PL/SQL.

At the end of the course students get the opportunity to self-program Oracle in these languages to solve the problems of developing applications in the client-server architecture, as well as the tasks of database administration.

The main difference of this course from a number of others with similar topics is that the goal is to teach specific students how to actually work with Oracle in these languages, and not to formally read a program prepared by a third party.

The course is designed for developers, programmers and database administrators. Students must have a good level of computer literacy and programming experience.

Program of the course "Oracle. Programming in SQL and PL/SQL"

Introduction to Oracle SQL

1. Basic concepts

Databases and relational model
Database
Relational approach to data modeling
Relational DBMS Implementation
Other approaches to data modeling and other types of DBMS
What is SQL?
History and standards
Oracle dialect of SQL
PL/SQL

2. SQL*Plus and entering sentences in SQL

3. Database "schema" example

4. Creating, deleting tables and changing the structure

CREATE TABLE clause
Data types in columns
Refinements in column descriptions
NOT NULL
Default values
Checking CHECK values coming into the table
Creating tables based on the results of a database query
Naming tables and columns
Virtual columns
Deleting tables
Changing the structure of tables
Logical and technical features of deleting a column
Using synonyms for naming tables
Renaming
Reference information about tables in the database

5. Basic elements of DML sentences: expressions

Immediate data values (literals)
Numeric values
Lines of text
Moments and time intervals
"System Variables"
Numeric expressions
Expressions over lines of text
Expressions over the type "point in time"
Functions
Scalar functions
CASE expressions
Scalar query
Conditional expressions
Some notes about the absence of a value in expressions

6. Data sampling

SELECT clause phrases
General rules for constructing a SELECT clause
SELECT clause processing order
SELECT clause example 1
SELECT clause example 2
Logical integrity of SELECT clause processing
The FROM clause of the SELECT clause
Column Specification Options
Columns from different tables
Using aliases in a query
Subquery as data source
Special case for request-connection
The WHERE clause of the SELECT clause
General algorithm for processing the WHERE clause
Comparison operators for obtaining a conditional expression
AND, OR, and NOT bindings for combining conditional expressions
Conditional operator IS
Conditional LIKE operator
Conditional operator BETWEEN
Conditional IN operator with explicitly enumerable set
Conditional IN operator with a set obtained from the database
Comparison conditions with subquery
Specifying ANY and ALL to Compare with Value Set Elements
Conditional operator EXISTS
The SELECT clause and functions in the SELECT clause
Shorthand for Bulk Column Selection
Expressions in the SELECT clause
Subqueries in the SELECT clause
DISTINCT specification
Features of the behavior of standard aggregate functions in the SELECT clause
Naming columns in a query result
System function ("variable") ROWNUM and features of its use
Analytic Functions
Cursor reference type expression
The ORDER BY clause of the SELECT clause
The simplest sort
Order by expression value
Specifying a column number
Binary and "language" string ordering
Features of handling missing values (NULL)
The GROUP BY and HAVING clauses of the SELECT clause
An example of working out the phrase GROUP BY ... HAVING
Missing value in grouping expression
Other examples
Specifying ROLLUP, CUBE, and GROUPING SETS in a GROUP BY clause
The CONNECT BY clause of the SELECT clause
Special system functions in clauses with CONNECT BY
Ordering the result
The WITH clause of subquery preformulation
Combining SELECT clauses
Combining with the UNION operator
Combining with the INTERSECT operator
Combination with the MINUS operator
Subqueries
Join operation in SELECT clause
Connection types
New syntax in version 9
Features of the connection operation

7. Update data in tables

Adding new lines
Explicitly adding a line
Adding Rows Received by Subquery
Adding to multiple tables with one statement
Changing existing field values
Using default values in INSERT and UPDATE
Removing rows from a table
Selective deletion
Complete removal option
Combining UPDATE, INSERT and DELETE in one statement
Logical integrity of table data update operators and response to errors
Reaction to errors during execution
Committing changes to the database
Row system revision number data
Speed up COMMIT execution

8. Quick reference to past data values

Reading old table row values
Restoring tables and data from previously deleted tables

9. Circuit integrity constraints

Varieties of circuit integrity constraints
NOT NULL constraint
Primary Keys
Uniqueness of values in columns
Foreign keys
Additional condition for value in string field
Additional condition linking values in multiple fields of a row
Adding a constraint when there are violations
Suspending Schema Constraint Checks Within a Transaction
Disabling and enabling schema integrity constraints
Technology for enabling and disabling circuit integrity constraints
More complex integrity rules

10. Virtual tables (derived, displayed: views)

Core ("base") and virtual tables
Updating virtual tables
Limitations of direct data modification through virtual tables
Disable direct updates
Narrowing the possibility of immediate updates
Virtual tables with data storage
Features of named virtual tables
Unnamed virtual tables without data storage

11. Non-scalar types for "complex" data in Oracle

Stored objects
Simple example
Using Object Properties and Methods
Using Object References
Collections
Nested tables
Arrays
XMLTYPE
Simple example
XMLTYPE data tables
Converting Table Data to XMLTYPE
Type ANYDATA

12. Auxiliary types of stored objects

Unique number generator
Operating system directory
Tables with temporary storage data
Link to another database
subroutines
Indices
Indexes for Checking Schema Integrity Constraints
Tables with external data storage

13. Some notes on optimizing SQL statements

14. Transactions and locks

Transactions in Oracle
Examples of data locks by transactions
Types of locks
Implicit locks in DML operations
Influence of Foreign Keys
Explicit table lock (TM type) with the LOCK command
Explicit locking of individual table rows
An undocumented form of group blocking
DDL Statement Locks

15. Users (schemes) and additional means of data access control

16. Tables of the system catalog (dictionary-reference)

18. Embedded SQL

Some examples of querying

19. Issue employees in accordance with the higher (lower) salary

Question to DB
"Obvious" but wrong solution
Right Decisions
Top-N solution (since version 8.1.5)
Solution Using Analytic Ranking Functions

20. Rewording the query with HAVING

22. NOT condition trap

23. Trap in NOT IN (S)

Introduction to PL/SQL

1. Basic concepts

Place of PL/SQL in Oracle architecture
General Structure of a PL/SQL Program

2. Basic types and data structures

Scalar variables
Numeric types
String types
Time points and intervals
Boolean variables
LOB types
Declaring Variables and Constants
Entries
Declaring entries in a program
Assignments
Referencing already existing data types
Custom subtypes

3. Expressions

4. Main control structures

Program branching
IF-THEN offer
IF-THEN-ELSE proposal
IF-THEN-ELSIF proposal
Case proposals
Unconditional transfer of control
Cycles
simple cycle
Counting cycle (FOR)
Cursor Loop (FOR)
WHILE loop
Simulate a REPEAT UNTIL loop
Labels in loops and blocks

5. Routines

Local routines
Redefining "external" names
Forward declarations
Repeating names at the same level (overloading)
6. Database Interaction: Static SQL
Using records instead of (list of) scalars

7. Regulation of changes in the database

Transaction management
Locks
Autonomous transactions

8. Built-in Dynamic SQL

Embedded dynamic SQL statements
Comparative example of two ways of working with dynamic SQL

9. Using Cursors

Explicit Cursors
Declaring Explicit Cursors
Opening Explicit Cursors
Retrieving a result through an explicit cursor
Closing an Explicit Cursor
No prohibition of table changes when the cursor is open
Attributes for Explicit Cursors
Some examples of using loops and cursors
Table Row Locking Cursors
SELECT … FOR UPDATE clause
Precautions for Using Locked Cursors
Ability to change the lines selected by the cursor
Cursor Links
General information
Usage example for structuring a program
Implicit Cursors

10. Exception Handling

Declaring Exceptions
Processing examples
Throwing Exceptions
Scope and distribution
"Internal" Block Exceptions
Using the SQLCODE and SQLERRM Functions

11. Stored procedures and functions

General Syntax
Options
Parameter type
Parameter use mode
Default values
Ways to Specify Actual Values for Parameters
Accessing Parameters and Local Variables in the Body of a Subroutine
Compiler Instructions When Creating Subroutines
Stored routines and database access privileges
Two logics for implementing database access privileges
Features of transferring privileges through roles

12. Trigger procedures

Creating a trigger procedure
Disabling trigger procedures
Trigger Procedures for DML Category Events
Combined trigger procedures
Managing transactions in the body of a trigger procedure
Sequence of firing trigger procedures when there are several of them
INSTEAD OF trigger procedures for output tables
Trigger Procedures for Events of the DDL Category
Trigger procedures for schema and database level events

13. Packages in PL/SQL

General package structure
Accessing Package Elements
(Global) package data
Mutual calls and repetition of names
Package initialization
SERIALLY_REUSABLE pragma

14. Calling PL/SQL Functions in SQL Statements

Requirements and restrictions on the use of user functions in SQL
Accessing functions from packages in SQL
Resolving column and function name conflicts
Table Functions in SQL

15. More complex data types: collections

Collection type declaration syntax
Working with associative arrays
Creating a nested table and VARRAY in a program
Adding and Removing Items in Collections
Multiple Actions on Collections
Collection transformations
Methods for working with collections in the program
Examples of using collections in a program
Privilege
Serial execution and binding queries to arrays
Serial execution of operations of the same type: the FORALL construct
Array binding: BULK COLLECT INTO construct
Example for SCOTT Schema
Using Collections in Table Functions (Threading Implementation)
Simple example
Use for data transformation

16. Debugging Procedures in PL/SQL

Dictionary tables
Routine dependencies
System packages
Package DBMS_PROFILER
DBMS_TRACE package
DBMS_UTILITY package functions
Package DBMS_DEBUG
An example of building a program work profile

17. Programming systems for PL/SQL

18. PL/SQL system packages

STANDARD and DBMS_STANDARD packages
Other system packages
Writing data from a program to a file and vice versa
Data encryption
Automatic job start in Oracle
Managing the dynamic placement of objects in the library buffer
Manipulating Large Unstructured NULL Objects
Accessing Old Data Values
Sending messages from a PL/SQL program
Ability to work in PL/SQL with COM Automation
additional information

A simple example of separating cursor opening and processing
A more complex example of division of work

20. Attributes of trigger procedures at the level of the database schema and events in the DBMS

IN modern world it is difficult to do without information technologies and their derivatives - computers, mobile phones, the Internet, etc., especially in large companies and government organizations working with big amount people, and not just with a couple of VIP clients, as may be the case for a small company. And where there are a large number of contractors, applicants, etc. - you can not do without the databases necessary for information processing. Naturally, the times of ledgers and cards, remembered by many in libraries, are long gone, today personal computers and electronic databases are used.

Today it is impossible to imagine the work of the largest companies, banks or government organizations without the use of databases and tools business intelligence. Databases allow us to store and access large amounts of information and database management system(DBMS) - manage available information storages.

IN Training center "Interface" You will learn how to effectively use database management systems: quickly find the information you need, navigate the database schema, create queries, develop and create databases.

Training will allow you not only to gain knowledge and skills, but also to confirm them by passing the appropriate exams for the status of a certified specialist. Experienced specialists in DBMS Microsoft SQL Server or Oracle may be interested in learning business intelligence systems. These tasks are quite complex, using a cumbersome mathematical apparatus, but they allow not only to analyze ongoing processes, but also to make forecasts for the future, which is in demand by large companies. That is why business analysts are in demand in the market, and the level of remuneration for their work is very, very worthy, although qualified database specialists, administrators and developers cannot complain about the low level of income either. Come to our courses and get a demanded and highly paid profession. We are waiting for you!

At the end of the course, a final certification is carried out in the form of a test or by assigning a grade to the teacher for the entire course of study based on the grades received by the student when checking the assimilation of the studied material based on grades for practical work performed in the learning process.

Training center "Interface" provides consulting services for building business process models, designing information systems, developing database structures, etc.

Need help finding a course?
Our goal is to provide training to professionals when and where they need it. It is possible to adjust the course programs at the request of customers! We will tell you about what interests you, and not just about what is rigidly fixed in the course program. If you need a course that you do not see on the chart or on our website, or if you want to take the course at a different time and place, please let us know at