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
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: