Oracle Database 19c: SQL Fundamentals - course RUB 68,990. from Specialist, training 40 academic hours, date: May 7, 2023.

Practical teacher, experienced Oracle DBMS specialist. He knows how to convey his wealth of experience to every listener and do it in simple and accessible language, using many practical examples. Grateful listeners note his ability to show a wide variety of nuances of working with the Oracle DBMS.

Expert teacher of Oracle and Java courses. Oracle Certified Specialist, Candidate of Technical Sciences. He is distinguished by his diverse experience in practical and teaching activities.
In 2003, Alexey Anatolyevich graduated with honors from MIREA. In 2006, he defended his PhD thesis on the topic of building secure automated information systems.
A major specialist in the field of database security, building secure java and web applications for Oracle DBMS and SQL Server, developing stored program modules in PL/SQL and T-SQL. Automated the activities of large state-owned enterprises. Provides consulting and advisory services in the development of complex distributed web applications based on the Java EE platform.
Alexey Anatolyevich’s teaching experience in the postgraduate education system exceeds 7 years. Worked with corporate clients, trained employees of the companies “BANK PSB”, “Internet University of Information Technologies (INTUIT)”, “SINTERRA”.
Author of several educational and methodological manuals on programming and working with databases. From 2003 to 2005, Alexey Anatolyevich was engaged in the adaptation and technical translation of foreign literature on web programming and working with databases. Published over 20 scientific papers.
Grateful graduates invariably note the accessible manner of presentation of even the most complex topics, detailed answers to questions from students, and the abundance of living examples from the teacher’s professional practice.

Teacher with 27 years of experience, Oracle expert, holder of international certifications Oracle, MySQL, Microsoft, IBM. Has extensive experience in programming in SQL, PL/SQL, Java, C, C++, R, Python, Unix / Linux administration, using the Git system, creating virtual machines (VMWare, Virtualbox, HYPER-V), administering Oracle databases, IBM Informix, configuring server hardware and RAID.
Since 1991, Ilya Andreevich has been teaching IT courses at Moscow State University, including their application in business. Its graduates include employees of the largest enterprises in the Russian Federation. He has authored many works in economic publications about trends in the use of IT in business. He also regularly conducts training to improve the skills of IT specialists and speaks at seminars.
Polite, with a sense of humor, patient, responsible - this is how students characterize their teacher. He knows how to convey his experience to every student in simple and accessible language, which makes him a truly high-quality teacher.

Module 1. Introduction to the Oracle 19c database (1 ac. h.)

Overview of the main features of the Oracle 19c database
Discussion of basic concepts, as well as theoretical and physical aspects of relational
Database
Classification of SQL commands as a standard language for relational databases
Overview of data used in the course
Using SQL Developer to Establish a Database Session
Saving query results to files and using script files in SQL Developer

Module 2. Extracting data using the SQL SELECT command (1.5 ac. h.)

Overview of the SELECT Command Features
Creating a report using the basic SELECT command
Selecting all columns
Selecting Specific Columns
Default Column Headings
Arithmetic Expressions
Understanding operator priorities
Using the DESCRIBE Command to Display the Table Structure

Module 3. Limiting and sorting data (2 ac. h.)

Using a WHERE clause to select the required rows
Using comparison operators and logical operators in the WHERE clause
Description of precedence rules for comparison operators and logical operators
Using Character Literals in the WHERE Clause
Sorting Rows Using the ORDER BY Clause of the SELECT Command
Sort the result in ascending and descending order of values

Module 4. Using single-line functions in the SELECT command (3 ak. h.)

Demonstrating the differences between single-line and multi-line SQL functions
Converting Strings Using Character Functions Used in the SELECT List and WHERE Clause
Converting Numbers Using the ROUND, TRUNC, and MOD Functions
Using date arithmetic in a SELECT clause
Using functions to work with dates

Module 5. Using conversion functions and conditional expressions (3 ak. h.)

Implicit and Explicit Data Type Conversion
Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
Nested One-Line Functions
Applying the NVL, NULLIF, and COALESCE functions to dates
Using IF THEN ELSE Logical Conditions in a SELECT Command

Module 6. Data aggregation using group functions (2 ac. h.)

Using group functions in the SELECT command to create analytical reports
Creating Data Groups Using the GROUP BY Clause
Excluding groups of data using the HAVING clause

Module 7. Sampling data from several tables (2 ac. h.)

Writing a SELECT command to access data from more than one table
Viewing data from tables using an outer join
Joining a table with itself (self join)

Module 8. Using subqueries (2 ak. h.)

Types of Problems Solved with Subqueries
Defining Subqueries
Types of Subqueries
Single-row and multi-row subqueries

Module 9. Using set operators (2 ak. h.)

Description of operators for working with sets
Using set operators to combine multiple queries into one
Sorting results when using set operators

Module 10. Data manipulation (3 ac. h.)

DML Command Syntax
Adding rows to a table using the INSERT command
Using the UPDATE Command to Modify Table Rows
Deleting data from a table using the DELETE command
Using scripts to manipulate data
Saving and rolling back changes using the COMMIT and ROLLBACK commands
What is Reading Consistency

Module 11. Using DDL Commands to Create and Manage Tables (3 ac. h.)

Main database objects covered in the course
Table structure overview
Basic data types used when creating table columns
Using simple syntax to create tables
What integrity rules can be defined when creating a table?
How schema objects work: (ALTER, DROP commands)

Module 12. Creating other schematic objects (2 ak. h.)

Creating a simple and complex view
Accessing data through a view
Create, edit, and use sequences
Creation and maintenance of indexes
Creating private and public synonyms

Module 13. User access control (2 ac. h.)

Differences between system privileges and object privileges
Creating Users
Granting system privileges
Granting privileges to tables
Create a role and grant it privileges
Changing user password
Granting object privileges
Revoking privileges

Module 14. Managing schema objects (2 ac. h.)

Add, change, or remove a column
Adding and removing restrictions
Enabling and disabling constraints, deferred constraint checking
Creating and Deleting Indexes
Creating Feature-Based Indexes
Performing a FLASHBACK operation
Creating and using external tables

Module 15. Managing objects using a data dictionary (2 ak. h.)

Data Dictionary
Using Dictionary Views
USER_OBJECTS and ALL_OBJECTS views
Information about tables and columns
View restrictions information
View information about sequences, indexes, and synonyms
How to add comments to a table and columns

Module 16. Manipulation of large data sets (2 ac. h.)

Manipulating Data Using Subqueries
Description of the features of the multi-table INSERT command
Using Different Types of Multi-Table INSERT Command
Merging Rows Using the MERGE Command
Track changes in data over a period of time

Module 17. Maintenance of data from different time zones (2 ac. h.)

Using different date and time data types to store fractional fractions of a second, as well as support data from different time zones
Using interval data types to store the difference between two values ​​containing time
Practical application of a number of date and time functions for application globalization

Module 18. Data sampling using complex subqueries (2 ac. h.)

Multi-column subqueries
Using Scalar Subqueries in SQL
Range of problems solved using correlated subqueries
Modifying and deleting rows using correlated subqueries
Using the EXISTS and NOT EXISTS Operators
Using the WITH clause

Module 19. Regular expression support (1.5 ac. h.)

Benefits of using regular expressions
Using regular expressions to match and replace strings