Oracle Database 12c: Basic SQL

Introduction

• Welcome
• What you need to know

1. Introduction to SQL

• What is SQL?
• Database tables
• Overview of table data types
• SQL query structure

2. Preparing the Course Environment

• Downloading the course VM

3. Using Oracle SQL Developer

• 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

4. Basic SELECT Queries

• 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

5. String, Number, and Date Functions

• 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

6. Joining Table Data

• 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

7. Manipulating Data

• 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

8. Grouping Data

• Introduction to aggregate functions
• Using aggregate functions
• Using GROUP BY
• Using GROUP BY with MAX, MIN, and AVG
• Using HAVING

9. Advanced Topics

• Primary keys
• NOT NULL constraints
• Check constraints
• Foreign key constraints
• Subqueries

Conclusion

• Next steps