DBMS with Oracle Training

General Information

Description

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.

Course Materials

Required Materials

  • Laptop
  • 6+ GB Ram(Recommended 8 GB)

Optional Materials

  • Internet Connection

Course Syllabus

DBMS Fundamentals

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

Joins

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

Using Sub-Queries

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

Quiz & Awards

Project

Apply for DBMS with Oracle Training