Oracle SQL for beginners Training Course

Relational Database Models

• Anatomy of a relational database
• Types of table relationships
• Database normalization and denormalization
• Relational Operators

Extracting Data

• Best practices for writing SQL queries
• SELECT statement syntax
• Retrieving all columns
• Performing arithmetic operations in queries
• Using column aliases
• Working with literals
• The concatenation operator

Filtering Results

• Using the WHERE clause
• Comparison operators
• The LIKE condition
• Prerequisite: BETWEEN ... AND
• Checking for NULL values (IS NULL)
• The IN condition
• Boolean operators: AND, OR, and NOT
• Combining multiple conditions within the WHERE clause
• Operator precedence rules
• Eliminating duplicates with the DISTINCT clause

Sorting Data

• Utilizing the ORDER BY clause
• Sorting by multiple columns or custom expressions

SQL Functions

• Distinguishing between single-row and multi-row functions
• Character, numeric, and date functions
• Explicit vs. implicit data type conversion
• Conversion functions
• Nesting functions
• Testing function performance using the dual table
• Retrieving the current date with the SYSDATE function
• Managing NULL values

Aggregating Data via Grouping

• Grouping functions
• How aggregate functions handle NULL values
• Creating data groups using the GROUP BY clause
• Grouping across multiple columns
• Filtering group results with the HAVING clause

Retrieving Data from Multiple Tables

• Join types
• Using NATURAL JOIN
• Assigning table aliases
• Defining joins within the WHERE clause
• INNER JOIN
• External joins: LEFT, RIGHT, and FULL OUTER JOIN
• The Cartesian product

Subqueries

• Embedding subqueries in the SELECT statement
• Single-row vs. multi-row subqueries
• Operators for single-row subqueries
• Grouping considerations in subqueries
• Operators for multi-row subqueries: IN, ALL, ANY
• Handling NULL values in subqueries

Set Operators

• UNION operator
• UNION ALL operator
• INTERSECT operator
• MINUS operator

Data Manipulation: Insert, Update, and Delete

• The INSERT command
• Copying data from another table
• The UPDATE command
• The DELETE command
• The TRUNCATE command

Transactions

• Using COMMIT, ROLLBACK, and SAVEPOINT commands

DDL Commands

• Core database objects
• Object naming conventions
• Table creation
• Available data types for columns
• The DEFAULT option
• NULL and NOT NULL constraints

Table Management

• Referential integrity: CHECK, PRIMARY KEY, FOREIGN KEY, UNIQUE
• Creating tables from query results
• Dropping tables with DROP TABLE
• The DESCRIBE command

Other Schema Objects

• Sequences
• Synonyms
• Views