Oracle SQL for beginners Training Course

Relational Database Models

• Architectural components of relational databases
• Methods for linking tables
• Concepts of database normalization and denormalization
• Application of relational operators

Data Retrieval

• Best practices for composing SQL queries
• SELECT statement syntax
• Retrieving all columns from a table
• Performing calculations within queries
• Using column aliases
• Working with literal values
• Utilizing the concatenation operator

Filtering Results

• The WHERE clause explained
• Usage of comparison operators
• Pattern matching with the LIKE condition
• Specifying ranges using BETWEEN ... AND
• Handling null values with IS NULL
• Checking for membership with the IN condition
• Combining conditions with Boolean operators (AND, OR, NOT)
• Applying multiple conditions within a WHERE clause
• Operator precedence rules
• Eliminating duplicates with the DISTINCT clause

Data Sorting

• The ORDER BY clause
• Sorting by multiple columns or complex expressions

SQL Functions

• Distinguishing between single-row and multi-row functions
• Handling text, numeric, and date data types
• Explicit and implicit data conversion
• Utilizing conversion functions
• Nesting function calls
• Testing function performance using the dual table
• Retrieving the current date with the SYSDATE function
• Managing NULL values in functions

Data Aggregation and Grouping

• Overview of aggregate functions
• The role of NULL values in aggregate operations
• Forming data groups with the GROUP BY clause
• Grouping across multiple columns
• Filtering aggregated results with the HAVING clause

Multi-Table Data Retrieval

• Types of table connectors
• Implementing NATURAL JOIN
• Assigning table aliases
• Executing joins within the WHERE clause
• Performing INNER JOINs
• Using external joins: LEFT, RIGHT, and FULL OUTER JOIN
• Understanding the Cartesian product

Subqueries

• Embedding subqueries in the SELECT command
• Differences between single-row and multi-row subqueries
• Operators specific to single-row subqueries
• Applying grouping within subqueries
• Operators for multi-row subqueries: IN, ALL, ANY
• Behavior of NULL values in subquery contexts

Set Operators

• Combining results with the UNION operator
• Preserving duplicates with UNION ALL
• Finding common rows with the INTERSECT operator
• Identifying differences with the MINUS operator

Data Modification: Insert, Update, and Delete

• Using the INSERT command
• Copying data between tables
• Modifying existing records with the UPDATE command
• Removing records using the DELETE command
• Clearing tables with the TRUNCATE command

Transactions

• Managing transaction integrity with COMMIT, ROLLBACK, and SAVEPOINT

DDL Commands

• Identification of core database objects
• Naming conventions for database objects
• Creating new tables
• Available data types for columns
• Setting default values
• Defining nullability with NULL and NOT NULL options

Table Management

• Enforcing referential integrity via CHECK, PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints
• Creating tables from existing query results
• Removing tables using DROP TABLE
• Inspecting table structures with the DESCRIBE command

Additional Schema Objects

• Generating sequences
• Creating synonyms
• Defining database views