Aprenda Sql e prepare-se para certificação Oracle

SQL Mastery: Data Retrieval, Manipulation & Management

Unlock the power of SQL with this comprehensive guide! Learn to efficiently retrieve, manipulate, and manage data using SQL commands and techniques. This course covers everything from basic data selection to advanced database design.

Data Retrieval & Filtering

• SELECT Statement Fundamentals: Master the core SELECT statement, including PROJECTION, SELECTION, and JOIN concepts. Understand basic SQL syntax for data retrieval.
• Operators & Concatenation: Learn to use arithmetic and string concatenation operators within SQL queries for powerful data manipulation.
• SQL vs. iSQL*Plus: Understand the differences between standard SQL and the iSQL*Plus environment.
• iSQL*Plus Interface & Commands: Navigate the iSQLPlus interface and learn to execute various iSQLPlus commands.
• Scripting SQL: Save and execute SQL statements from script files for efficient data management.
• Data Restriction & Sorting: Refine your queries with WHERE clauses, comparison operators, and the LIKE operator for precise data filtering.
• Logical Operators: Utilize AND, OR, and NOT to create complex filtering conditions.
• ORDER BY Clause: Sort query results using the ORDER BY clause.
• Dynamic Filtering with &: Implement runtime filtering and sorting using substitution with the & operator in iSQL*Plus.

Data Personalization with Functions

• Single-Row vs. Multi-Row Functions: Understand the distinction between single-row and multi-row SQL functions.
• Text Manipulation: Explore text functions for data manipulation and formatting.
• Date & Numeric Functions: Utilize date and numeric functions to perform calculations and formatting.
• SYSDATE Function: Retrieve the current date using the SYSDATE function.
• DUAL Table: Use the DUAL table to evaluate SQL functions.
• Arithmetic Operations on Dates: Apply arithmetic operations to dates within SELECT statements.

Aggregate Functions & Grouping

• Understanding Aggregate Functions: Learn about and categorize common aggregate functions.
• Using Aggregate Functions: Apply aggregate functions like SUM, AVG, COUNT, MIN, and MAX.
• DISTINCT with Aggregate Functions: Use DISTINCT to retrieve unique values with aggregate functions.
• Handling Null Values: Understand how aggregate functions handle NULL values.
• GROUP BY Clause: Group data using the GROUP BY clause.
• Multiple Grouping Columns: Group data by multiple columns.
• Avoiding Invalid Queries: Prevent errors with proper use of GROUP BY.
• HAVING Clause: Filter grouped data using the HAVING clause.

Multi-Table Data Retrieval

• JOIN Syntax (SQL99): Master the JOIN clause for retrieving data from multiple tables using SQL99 syntax.
• Table Renaming: Use table renaming for concise and explicit column identification.
• CROSS JOIN: Understand and utilize CROSS JOIN to generate Cartesian products.
• NATURAL JOIN: Retrieve data from tables with columns of the same name using NATURAL JOIN.
• USING Clause: Join tables based on specific columns using the USING clause.
• ON Clause: Join tables based on specified conditions using the ON clause.
• Outer Joins (LEFT, RIGHT, FULL): Understand and use different types of outer joins to retrieve all rows from one or both tables.
• Combining Joins with AND: Add conditions to joins using the AND operator.

Subqueries for Complex Queries

• SUBQUERY Syntax: Learn the syntax for using subqueries in SELECT statements with the WHERE clause.
• Subquery Guidelines: Follow best practices for writing effective subqueries.
• Types of Subqueries: Understand different types of subqueries (e.g., scalar, multi-row).
• Single-Row Subqueries with GROUP: Execute subqueries with a single row and utilize the GROUP function.
• Identifying Invalid Subqueries: Recognize and correct errors in subqueries.
• Multi-Row Subqueries: Execute subqueries with multiple rows.
• ANY and ALL Operators: Understand how ANY and ALL operators work