Oracle Database 12c: Basic SQL 

Welcome

What you need to know

What is SQL?

Database tables

Overview of table data types

SQL query structure

Downloading the course VM

Introduction to Oracle SQL Developer

Creating a connection to the database

Managing existing connections

Exploring database objects

Filtering specific objects

Using SQL Developer tabs

Viewing query output

Case sensitivity of SQL commands

Viewing SQL execution history

Query output as grid or script

Writing your first SELECT query

Selecting only specific columns

Using WHERE to filter specific rows

Using multiple WHERE conditions

Using WHERE to filter partial strings

Using WHERE to filter numeric ranges

Using WHERE IN and NOT IN for strings

Using WHERE IN and NOT IN for numbers

Using WHERE to filter dates

Using WHERE to filter exact strings

Using DISTINCT to eliminate duplicates

Performing basic numeric calculations

Using column aliases

Describe the structure of a table

Using ORDER BY

Introduction to functions

Using SUBSTRING, LENGTH, and CONCAT

Using LENGTH

Using CONCAT

Using LOWER and UPPER

Using INSTR

Using RPAD and LPAD

Using TO_CHAR to convert numbers

Using TO_CHAR to format DATE values

Using TO_CHAR to filter DATE values

The special Oracle DUAL table

Using ROUND

Using TRUNC

Using SYSDATE

Using SYSTIMESTAMP

Using MONTHS_BETWEEN

Using ADD_MONTHS

Introduction to SQL joins

Joining data from tables

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

Joins with table aliases

Oracle join syntax vs. ANSI syntax

Introduction to DML and DDL commands

Creating a simple table

Inserting a new row to a table

COMMIT and ROLLBACK

Introduction to transactions

Deleting rows for a table

Updating values

Multiple-statement transactions

Truncating all data from a table

Adding and dropping columns from a table

Introduction to aggregate functions

Using aggregate functions

Using GROUP BY

Using GROUP BY with MAX, MIN, and AVG

Using HAVING

Primary keys

NOT NULL constraints

Check constraints

Foreign key constraints

Subqueries

Next steps