Oracle 19c Introduction To Sql

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