Oracle

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: