Oracle 19c : Introduction to SQL (Oracle 19c Intro to SQL)

Learning the SQL language is one of the most basic tasks required for the use of a relational database. SQL proficiency is essential for business users, database developers, database administrators and any other database professional. This course introduces the basics of the SQL language and the Oracle Relational Database Management System (RDBMS).

One will become acquainted with the differences in the working environment between a traditional on-premise database installation and the Oracle database service cloud-computing platform. This course also considers intermediate-level SQL topics such as writing database queries using the SQL-99 syntax and exploiting the power of built-in functions that extend the capabilities of SQL.

Since SQL is an industry standard language, many of the topics presented and many of the skills you will acquire will be applicable to other database platforms, such as Microsoft SQL Server, IBM DB2, the open-source databases MySQL and PostgreSQL, and others.

This course takes a unique approach to SQL training in that it incorporates data modelling theory, relational database theory, graphical depictions of theoretical concepts and numerous examples of actual SQL syntax into one learning vehicle.

You will learn how to complete an application schema definition by creating database objects such as relational views, sequences, synonyms, indexes and others to compliment the table definitions. The crucial topic of data integrity and how this is protected using declarative constraints are covered.

With this course, we will also leave the idyllic realm of the learning environment and begin to explore such practical real-world considerations as database object security and database performance.


  • Category
    Database
  • Duration
    20 Hrs
  • Level
    Beginner

What Will I Learn ?

• Identify the major structural components of the Oracle Database 19c

• Create reports of aggregated data

• Write SELECT statements that include queries

• Retrieve row and column data from tables

• Run data manipulation statements (DML) in Oracle Database 19c

• Create tables to store data

• Utilize views to display data

• Control database access to specific objects

• Manage schema objects

• Display data from multiple tables using the ANSI SQL 99 JOIN syntax

• Manage objects with data dictionary views

• Write multiple-column sub-queries

• Employ SQL functions to retrieve customized data

• Use scalar and correlated sub-queries

• Create reports of sorted and restricted data

Prerequisite Knowledge

  • Familiarity with data processing concepts and tech

Who can benefits ?

The target audience for this course is all Oracle professionals, both business and systems professionals. Among the specific groups for whom this course will be helpful are:
  • Application Developers
  • Business Analysts
  • Data Warehouse Administrator
  • Developer
  • Forms Developer
  • PL/SQL Developer
  • System Analysts

Opportunity Scope

Mentor shall discuss on classroom.

Modules / Chapter

Introducing Oracle Database 19c
    List the features of Oracle Database 19c
    Discuss the basic design, theoretical and physical aspects of a relational database
    Categorize the different types of SQL statements
    Describe the data set used by the course
    Log onto the database using the SQL Developer environment Save queries to files and use script     files in SQL Developer

Retrieving Data Using the SQL SELECT Statement
    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 Defaults
    Use Arithmetic Operators
    Understand Operator Precedence
    Learn the DESCRIBE command to display the table structure

Restricting and Sorting Data
    Write queries that contain a WHERE clause to limit the output retrieved
    List the comparison operators and logical operators that are used in a WHERE clause
    Describe the rules of precedence for comparison and logical operators
    Use character string literals in the WHERE clause
    Write queries that contain an ORDER BY clause sort the output of a SELECT statement
    Sort output in descending and ascending order

Using Single-Row Functions to Customize Output
    Describe the differences between single row and multiple row functions
    Manipulate strings with character function in the SELECT and WHERE clauses
    Manipulate numbers with the ROUND, TRUNC and MOD functions
    Perform arithmetic with date data
    Manipulate dates with the date functions

Using Conversion Functions and Conditional Expressions
    Describe implicit and explicit data type conversion
    Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
    Nest multiple functions
    Apply the NVL, NULLIF, and COALESCE functions to data
    Use conditional IF THEN ELSE logic in a SELECT statement

Reporting Aggregated Data Using the Group Functions
    Use the aggregation functions in SELECT statements to produce meaningful reports
    Create queries that divide the data in groups by using the GROUP BY clause
    Create queries that exclude groups of date by using the HAVING clause

Displaying Data From Multiple Tables
    Write SELECT statements to access data from more than one table
    View data that generally does not meet a join condition by using outer joins
    Join a table by using a self join

Using Sub-queries to Solve Queries
    Describe the types of problem that sub-queries can solve
    Define sub-queries
    List the types of sub-queries
    Write single-row and multiple-row sub-queries

Using the SET Operators
    Describe the SET operators
    Use a SET operator to combine multiple queries into a single query
    Control the order of rows returned when using the SET operators

Manipulating Data
    Describe each DML statement
    Insert rows into a table with the INSERT statement
    Use the UPDATE statement to change rows in a table
    Delete rows from a table with the DELETE statement
    Save and discard changes with the COMMIT and ROLLBACK statements
    Explain read consistency

Using DDL Statements to Create and Manage Tables
    Categorize the main database objects
    Review the table structure
    List the data types available for columns
    Create a simple table
    Decipher how constraints can be created at table creation Describe how schema objects work

Creating Other Schema Objects
    Create a simple and complex view
    Retrieve data from views
    Create, maintain, and use sequences
    Create and maintain indexes
    Create private and public synonyms

Controlling User Access
    Differentiate system privileges from object privileges
    Grant privileges on tables
    View privileges in the data dictionary
    Grant roles
    Distinguish between privileges and roles

Managing Schema Objects
    Add constraints
    Create indexes
    Create indexes using the CREATE TABLE statement
    Create function-based indexes
    Drop columns and set column UNUSED
    Perform FLASHBACK operations                 
    Create and use external tables

Managing Objects with Data Dictionary Views
    Explain the data dictionary
    Find table information
    Report on column information    
    View constraint information
    Find view information
    Verify sequence information
    Understand synonyms
    Add comments

Manipulating Large Data Sets
    Manipulate data using sub-queries
    Describe the features of multi-table inserts
    Use the different types of multi-table inserts    
    Merge rows in a table
    Track the changes to data over a period of time

Managing Data in Different Time Zones
    Use data types similar to DATE that store fractional seconds and track time zones
    Use data types that store the difference between two date-time values
    Practice using the multiple data-time functions for globalize applications

Retrieving Data Using Sub-queries
    Write a multiple-column sub-query
    Use scalar sub-queries in SQL
    Solve problems with correlated sub-queries
    Update and delete rows using correlated sub-queries
    Use the EXISTS and NOT EXISTS operators
    Use the WITH clause

Regular Expression Support
    List the benefits of using regular expressions
    Use regular expressions to search for, match, and replace string

Enquiry Form

Required fields are marked (*).

(Max 350 words only)

Contact Information

  • Address

    Anamnagar - 32 Kathmandu, Nepal

  • Email

    info@labanepal.com

  • Phone

    +977-1-4102721, 4102722, 4244804

  • Opening Hours

    10 AM - 5 PM

Registration Form

Required fields are marked (*).

(Max 350 words only)

Contact Information

  • Address

    Anamnagar - 32 Kathmandu, Nepal

  • Email

    info@labanepal.com

  • Phone

    +977-1-4102721, 4102722, 4244804

  • Opening Hours

    10 AM - 5 PM

newsletter

Sign Up for News and Offers

Subscribe for the latest news and great deals we offer