Oracle Basics

The Oracle basics part covers the fundamentals of the Oracle Database. The main goal of this tutorial series is to help you build a strong Oracle Database Foundation. After completing this series, you’ll be able to write complex SQL statements for querying data and managing database objects.

This tutorial series is practical, therefore, you need to access an Oracle Database 12c, 18c, or 19c environment for the hands-on exercises and proof of concepts. If you don’t have an Oracle Database system to work with, you can download it from Oracle website and install it for the learning purposes.

For detailed information on how to install the Oracle Database and download a sample database for practice, please check the Getting Started with the Oracle Database section.

Section 1. Querying data

This section helps you learn how to query data from the Oracle Database. We will start with a simple query that allows you to retrieve data from a single table.

  • SELECT – show you how to query data from a single table.

Section 2. Sorting data

  • ORDER BY – sort the result set of a query in ascending or descending order.

Section 3. Filtering data

  • DISTINCT  – introduce you how to eliminate duplicate rows from the output of a query.
  • WHERE – learn how to specify a condition for rows in the result set returned by a query.
  • AND – combine two or more Boolean expressions and return true if all expressions are true.
  • OR–  combine two or more Boolean expressions and return true if one of the expressions is true.
  • FETCH – show you how to limit rows returned by a query using the row limiting clause.
  • IN – determine if a value matches any value in a list or a subquery.
  • BETWEEN – filter data based on a range of values.
  • LIKE  – perform matching based on specific patterns.
  • IS NULL and IS NOT NULL – check if an expression or values in a column is NULL or not.

Section 4. Joining tables

  • A visual explanation of Oracle Joins – a brief introduction to joins in Oracle using visual illustrations.
  • INNER JOIN – show you how to query rows from a table that have matching rows from another table.
  • LEFT JOIN – introduce you to the left-join concept and learn how to use it to select rows from the left table that have or don’t have the matching rows in the right table.
  • RIGHT JOIN – explain the right-join concept and show you how to apply it to query rows from the right table that have or don’t have the matching rows in the left table.
  • FULL OUTER JOIN – describe how to use the full outer join or full join to query data from two tables.
  • CROSS JOIN – cover how to make a Cartesian product from multiple tables.
  • Self-join – show you how to join a table to itself to query hierarchical data or compare rows within the same table.

Section 5. Grouping data

  • GROUP BY– teach you how to group rows into subgroups and apply an aggregate function for each group
  • HAVING – show you how to filter a group of rows.

 Section 6. Subquery

  • Subquery – introduce the concept of subquery and how to use the subqueries to perform advanced data selection techniques.
  • Correlated Subquery – learn about the correlated subquery which is a subquery that depends on the values returned by the outer query.
  • EXISTS and NOT EXISTS – check for the existence of rows returned by a subquery.
  • ANYSOME, and ALL – compare a value to a list or subquery. Note that SOME and ANY are the same so they are interchangeable.

Section 7. Set Operators

This section walks you the steps of using the set operators to combine result sets of two or more independent queries.

  • UNION – show you how to combine the results of two queries into a single result.
  • INTERSECT – teach you how to make an intersection of the results of two independent queries.
  • MINUS – learn how to subtract a result from another.

Section 8. More on Groupings

  • Grouping sets – introduce you to the grouping set concepts and show you how to generate multiple grouping sets in a query.
  • CUBE – learn how to use CUBE to generate subtotals for all possible combinations of a specified group of dimensions.
  • ROLLUP – describe how to calculate multiple levels of subtotals across a specified group of dimensions.
  • PIVOT – show you how to transpose rows to columns to make the crosstab reports.
  • UNPIVOT – a guide to rotating columns into rows.

Section 9. Modifying data

In this section, you’ll learn how to change the contents of an Oracle database. The SQL commands for modifying data are referred to as Data Manipulation Language (DML).

  • INSERT – learn how to insert a row into a table.
  • INSERT INTO SELECT – insert data into a table from the result of a query.
  • INSERT ALL – discuss multitable insert statement to insert multiple rows into a table or multiple tables.
  • UPDATE – teach you how to change the existing values of a table.
  • DELETE – show you how to delete one or more row from a table.
  • MERGE – walk you through the steps of performing a mixture of insertion, update, and deletion using a single statement.

Section 10. Data definition

This section shows you how to manage the most important database objects including databases and tables.

  • CREATE TABLE – walk you through the steps of creating new tables in the database.
  • Identity Column – learn how to use the identity clause to define the identity column for a table.
  • ALTER TABLE – teach you how to change the structure of existing tables.
  • ALTER TABLE ADD column – show you how to add one or more columns to an existing table
  • ALTER TABLE MODIFY column – show you how to change the definition of existing columns in a table.
  • Drop columns – learn how to use various statements to drop one or more columns from a table.
  • DROP TABLE – show you how to delete tables from the database.
  • TRUNCATE TABLE – delete all data from a table faster and more efficiently.
  • RENAME table –  walk you through the process of renaming a table and handling its dependent objects.
  • Virtual columns – introduce you to virtual columns and how to use them in the database tables.

Section 11. Oracle data types

  • Oracle data types – give you an overview of the built-in Oracle data types.
  • NUMBER – introduces you to the numeric data type and show you how to use it to define numeric columns for a table.
  • FLOAT – demystify float data type in Oracle by practical examples.
  • CHAR – learn about fixed-length character string.
  • NCHAR –  show you how to store fixed-length Unicode character data and explain the differences between CHAR and NCHAR data types
  • VARCHAR2 – introduce you to the variable-length character and show you how to define variable-length character columns in a table.
  • NVARCHAR2 – learn how to store variable-length Unicode characters in the database.
  • DATE – discuss the date and time data type and show you how to handle date-time data effectively.
  • TIMESTAMP – introduce you how to store date and time with the fractional seconds precision.
  • INTERVAL– focus on the interval data types to store periods of time.
  • TIMESTAMP WITH TIME ZONE – learn how to store datetime with timezone data.

Section 12. Constraints

  • Primary key  – explain you to the primary key concept and show you how to use the primary key constraint to manage a primary key of a table.
  • Foreign key – introduce you to the foreign key concept and show you use the foreign key constraint to enforce the relationship between tables.
  • NOT NULL constraint – show you how to ensure a column not to accept null values.
  • UNIQUE constraint – discuss how to ensure data stored in a column or a group of columns is unique among rows within the whole table.
  • CHECK constraint – walk you through the process of adding logic for checking data before storing them in tables.

Section 13. Temporary Tables

  • Global temporary table – learn about the global temporary tables and how to create a new global temporary table.
  • Private temporary table – introduce the private temporary table and how to create a new private temporary table.