DBMS with Oracle Training
Our PL/SQL certification training course lets you master the PL/SQL language for query of Oracle databases. We provide the best online training classes to help you learn the database tools, query language, subprogram, syntax query, basic and advanced DML, scripting. Work on real world industry projects.
Expectations and Goals
This Oracle Training Course provides you with the complete skills needed to create, implement and manage robust database applications using the Oracle database tools. Some of the topics covered are – understanding of the Basic Procedural Language/Structured Query Language, subprogram, section and syntax query, DML, advanced DML and scripting.
- 6+ GB Ram(Recommended 8 GB)
- Internet Connection
Introduction to database fundamentals, relational database management system, the various RDBMS products and the architecture of DBMS.
Introduction to Oracle – SQL
The basics of RDBMS, the various versions of Oracle, Editions of Oracle database, different Oracle products, tools for Oracle Application Development, the Oracle Server and architecture of Oracle database server.
Installation of Oracle 12c
The hardware and software requirements for installation of Oracle 12c on Windows 7/WindowsX.
Role of SQL in RDBMS
The various Query languages used in Oracle, role of SQL in RDBMS, the concepts of SQL, various features of SQL, writing SQL statements, the various SQL statements and SQL tools.
Introduction to Oracle Database
List the features of Oracle Database 12c, Log on to the database using SQL Developer environment, Save queries to files and use script files in SQL Developer
Retrieve Data using the SQL SELECT statements
List the capabilities of SQL SELECT statements, Generate a report of data from the output of a basic SELECT statement, Select All Columns, Select Specific Columns, Use Column Heading, Use Arithmetic Operators, Understand Operator Precedence, Using DESCRIBE command to display table structure
Restrict and Sort Data
Using WHERE clause, using comparison & logical operators in WHERE, rules of precedence for comparison & logical operators, using string literals in WHERE clause, using ORDER BY clause to sort output, Sort output in descending & ascending order.
Single-Row Functions to Customize Output
Differences between single row and multiple row functions, Manipulate strings with character function in the SELECT and WHERE clauses, Manipulate with numbers with MOD, TRUNC etc functions, Perform arithmetic with date data, Manipulate dates with the DATE functions
Invoke Conversion Functions & Conditional Expressions
Implicit & Explicit Data conversion, using TO_CHAR, TO_NUMBER and TO_DATA conversion functions, Nest multiple functions, Apply the NVL, NULLIF and COALESCE functions to data, Using conditional IF THEN ELSE logic in a SELECT statement
Aggregate Data Using the Group Functions
Using aggregate functions to produce meaningful reports, Divide the retrived data in groups by using GROUP BY clause, Exclude groups of data by using the HAVING clause
Accessing data from more than one table, View data that generally does not meet a join condition by using outer joins, Join a table to itself by using a self join
Describe the type of problem that sub-queries can solve, Define sub-queries, List the types of sub-queries, creating single row and multiple-row sub-queries
Using SET Operators
Describe the SET operators, Using SET operator to combine multiple queries into a single query, Control the order of rows returned by query
Data Manipulation Statements
Description of each DML statement, Insert row into a table, Change rows in a table by UPDATE statement, Delete rows from a table with the DELETE statement, Save and discard changes with the COMMIT and ROLLBACK statements
Data Definition Statements
Categorizing the main database objects, Review the table structure, Discuss the column data types, Creation of a sample table, Description of how schema objects work, Decipher constraint usage at table creation
Understanding Other Schema Objects
Creation of simple and complex views, Retrieving data from views, Create maintain and use sequences, Create and maintain indexes, Create private and public synonyms
Control User Access
Differentiate system privileges & object privileges, Create Users, Grant System Privileges, Create and Grant Privileges to a Role, Change User password, Grant object privileges, Passing on privileges, Revoke Privileges from Users
Management of Schema Objects
Add Modify and drop a Column, Add Drop and Defer a constraint, Enabling & Disabling a constraint, Create & remove indexes
Manage Objects with Data Dictionary Views
Understanding Data Dictionaries, Using dictionary views, USER_OBJECTS & ALL_OBJECTS Views, Table and Column Information, Querying the dictionary views for constraint information, Querying the dictionary views for views sequence index and synonym information, Add a comment to a table, Querying the dictionary views for comment information
Manipulate Large Data Sets
Using Subqueries to manipulate data, Retrieving data using a subquery as source, Insert using a subquery as a target, Using WITH CHECK OPTION keyword on DML statements, Listing the types of multitable INSERT statements, Merge roes in a table, Track changes in Data over a period of time
Retrieve Data using Sub-queries
Multiple column subqueries, Pairwise &Nonpairwise comparison, Scalar subquery expressions, Understanding Co-related Subqueries, Updating & deleting rows using Co-related subqueries, using EXISTS & NOT EXISTS operators, Invoking the WITH clause, Using recursive WITH clause
Regular Expression Support
Using Regular Expression functions & conditions in SQL, Using Meta Characters with Regular Expressions, Performing basic search using REGEXP_LIKE function, Finding patterns using REGEXP_INSTR function, Extracting substrings using REGEXP_SUBSTR function, Replacing patterns using REGEXP_REPLACE function, Usage of Sub-Expressions with Regular Expression Support, Implementing the REGEXP_COUNT function