FB

Oracle

Oracle Database is one of the most popular databases in the world. It’s targeted at large organizations, and has many features to allow administrators to manage the database and for developers to improve their query performance.

The latest version of Oracle is 21c. A free version of Oracle is available, called Oracle Express, and their Standard and Enterprise versions are licensed products.

Setting Up Oracle

You have several options when getting started with Oracle database:

  • Install Oracle Express on Windows (video)
  • Install Oracle on a Mac (guide or video)
  • Install Oracle with Docker (Windows, Mac, or Linux) (guide or video)
  • How to Create a Connection (guide, video)
  • How to Resolve Oracle Connection Issues (guide)
  • Set Up the Sample Oracle HR Schema (video)

SQL Developer is a popular and free IDE you can use to work with Oracle databases. Check out the guide to SQL Developer.

SQL IDEs: If you don’t want to use SQL Developer, this post lists all SQL IDEs with comparisons of features.

Learning the Basics

Introduction to SQL video course: a series of videos to get you started with learning Oracle SQL

59 Best Resources for Learning SQL: A large list of other places to learn SQL

19 Best Database Books: a list of books on database-related topics

Basic SQL Statements

Data Manipulation

SELECT Statement Guide: view data in a table

INSERT Statement Guide: add data to a table

UPDATE Statement Guide: modify data in a table

DELETE Statement Guide: remove data from a table

Clauses

WHERE Clause: filter data from your query

ORDER BY Clause: specify the order of the results of a Select query

GROUP BY: allows you to group data by values in a column and use aggregate functions

HAVING Clause: allows you to filter data after a group has been applied

DISTINCT: eliminate duplicate rows from your results

Tables

Create Table guide: create a new table on the database

Alter Table guide: make changes to an existing table

Drop Table guide: remove a table from the database

Truncate Table guide: remove all data from a table

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

Additional SQL Features

Operators: perform calculations and operations on data

Set Operators: combine results from two queries into one

CASE Statement: use conditional (if then else) logic in a query

MERGE: update or insert data into a table based on a condition

Pivot and Unpivot: Translate rows to columns or columns to rows

 

Joins

Joins guide: a guide to joins which are how you combine multiple tables together in a query

One to One joins (video): definition of this join type and an example

One to Many joins (video): definition of this join type and an example

Many to Many joins (video): definition of this join type and an example

Self Join (video): definition of this join type and an example

 

Functions

Aggregate Functions: functions that aggregate data into one or more rows

Analytic Functions: functions that let you analyse data across multiple rows

Partition By: an explanation of the Partition By keyword

Regular Expression Functions: a guide to Regular Expression functions

Oracle Interval Functions: a list of all interval functions in Oracle

Oracle Date Functions: a list of all date functions in Oracle

Oracle Timezone Functions: a list of all timezone functions in Oracle

 

Objects

There are many more objects in an Oracle database besides tables. Learn all about them in these guides.

PL/SQL Tutorial: learn how to write PL/SQL code (Procedural Language Structured Query Language)

Triggers: a piece of PL/SQL code that runs on certain events

Indexes: a guide to Indexes in SQL

Dual Table: a dummy table in Oracle

Synonyms: an object that refers to another object

Sequences: an object that generates a unique number

Constraints: rules that can be set on your data

Keys: surograte, natural, primary, foreign, composite, and more

Stored Procedures: a guide to stored procedures in SQL

Views and Materialised Views: objects that let you view data in certain ways

Database Schema: an explanation of this term

Temporary Tables: what it is and how to create one

 

Further Learning

More features and topics in SQL and Oracle database.

Glossary of Terms: A definition of many terms used in SQL and database development

Data Types: a guide to data types in many different databases

Oracle Data Types: a list and recommendations for Oracle data types

Character Sets: an overview of character sets in Oracle

TNSNAMES.ora: an explanation of the TNSNAMES.ORA file and what it’s for.

Interview Questions and Answers: a list of 69 interview questions and answers, plus interview tips

DML, DDL, and TCL: a list of SQL commands and what DDL, DML, and TCL mean

Oracle 12c New Features: all the new features in Oracle 12c for developers

Oracle 18c Guide: a guide to the changes in Oracle 18c

Container and Pluggable Databases: a guide on working with and connecting to container databases and pluggable databases

Aliases: A guide to column aliases and table aliases in SQL

Subqueries: a guide to subqueries, what they are, and many different examples.

Limiting Results: a guide to SQL row limiting and Top-N queries

CTE (WITH Clause): a guide to Common Table Expressions (CTEs) aka the WITH clause

Oracle Certified Associate: a guide and FAQ for the Oracle Certified Associate certification (1Z0-071)

Transactions and Isolation Levels: an explanation of transactions in SQL and what isolation levels are

Concatenate: what concatenation is and how to do it in SQL

Booleans: how SQL handles booleans and how you can implement them

Wildcards: An explanation of what wildcards are and how to use them

Null: a guide on what NULL is and how to handle them in SQL

Data Warehouses: an in-depth guide to data warehouses

Comments: a guide to comments in SQL and why they can help

Database Version Control, CI, and CD: An in-depth guide on database version control, continuous integration, and continuous deployment

How To

A collection of articles detailing how to do common tasks in SQL queries.

Convert Data Types: how to convert data types in Oracle SQL

Select Rows with a Max Value: how to select rows with a maximum value for a column

Limit Rows After Ordering: how to limit the number of rows returned after ordering

Auto Increment: how to create an auto-increment column in Oracle

Find Row with Max Value: how to find the row with the max value for a column in Oracle

List Tables in a Database: how to list all the tables in a database or a schema

Escape Single Quotes: how to escape single quotes in SQL

Remove Duplicate Records: how to remove duplicate records in SQL

Calculate Mean, Median, and Mode: how to calculate the mean, median, and mode in Oracle SQL

Database Timezones: The Ultimate Guide: how to store and work with dates, times, and timezones in a database

References

Here’s a compilation of reference lists that can be useful when working with Oracle.

Oracle SQL Functions: a list of all functions in Oracle

Oracle SQL Code list: a list of all SQL Code values used by the SQLCODE function

Oracle Error Messages: a list of the most common Oracle SQL messages with solutions

42 ETL Tools: a list of 42 different ETL tools.

50 Experts Reveal Top Tools: a range of Oracle SQL experts reveal their top SQL tools.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: