Szkolenia Oracle

Cel szkolenia

This Oracle Database: Introduction to SQL training helps you write subqueries, combine multiple queries into a single query using SET operators and report aggregated data using group functions. Learn this and more through hands-on exercises.

Learn To:

  • Understand the basic concepts of relational databases ensure refined code by developers.
  • Create reports of sorted and restricted data.
  • Run data manipulation statements (DML).
  • Control database access to specific objects.
  • Manage schema objects.
  • Manage objects with data dictionary views.
  • Retrieve row and column data from tables.
  • Control privileges at the object and system level.
  • Create indexes and constraints; alter existing schema objects.
  • Create and query external tables.

Plan szkolenia Rozwiń listę

  1. Introduction to Oracle Database
    • List the features of Oracle Database 11g
    • Discuss the basic design, theoretical, and physical aspects of a relational database
    • Categorize the different types of SQL statements
    • Describe the data set used by the course
    • Log on to the database using SQL Developer environment
    • Save queries to files and use script files in SQL Developer
  2. Retrieve Data using the SQL SELECT Statement
    • List the capabilities of SQL SELECT statements
    • Generate a report of data from the output of a basic SELECT statement
    • Select All Columns
    • Select Specific Columns
    • Use Column Heading Defaults
    • Use Arithmetic Operators
    • Understand Operator Precedence
    • Learn the DESCRIBE command to display the table structure
  3. Learn to Restrict and Sort Data
    • Write queries that contain a WHERE clause to limit the output retrieved
    • List the comparison operators and logical operators that are used in a WHERE clause
    • Describe the rules of precedence for comparison and logical operators
    • Use character string literals in the WHERE clause
    • Write queries that contain an ORDER BY clause to sort the output of a SELECT statement
    • Sort output in descending and ascending order
  4. Usage of Single-Row Functions to Customize Output
    • Describe the differences between single row and multiple row functions
    • Manipulate strings with character function in the SELECT and WHERE clauses
    • Manipulate numbers with the ROUND, TRUNC, and MOD functions
    • Perform arithmetic with date data
    • Manipulate dates with the DATE functions
  5. Invoke Conversion Functions and Conditional Expressions
    • Describe implicit and explicit data type conversion
    • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
    • Nest multiple functions
    • Apply the NVL, NULLIF, and COALESCE functions to data
    • Use conditional IF THEN ELSE logic in a SELECT statement
  6. Aggregate Data Using the Group Functions
    • Use the aggregation functions to produce meaningful reports
    • Divide the retrieved data in groups by using the GROUP BY clause
    • Exclude groups of data by using the HAVING clause
  7. Display Data From Multiple Tables Using Joins
    • Write SELECT statements to access data from more than one table
    • View data that generally does not meet a join condition by using outer joins
    • Join a table to itself by using a self join
  8. Use Sub-queries to Solve Queries
    • Describe the types of problem that sub-queries can solve
    • Define sub-queries
    • List the types of sub-queries
    • Write single-row and multiple-row sub-queries
  9. The SET Operators
    • Describe the SET operators
    • Use a SET operator to combine multiple queries into a single query
    • Control the order of rows returned
  10. Data Manipulation Statements
    • Describe each DML statement
    • Insert rows into a table
    • Change rows in a table by the UPDATE statement
    • Delete rows from a table with the DELETE statement
    • Save and discard changes with the COMMIT and ROLLBACK statements
    • Explain read consistency
  11. Use of DDL Statements to Create and Manage Tables
    • Categorize the main database objects
    • Review the table structure
    • List the data types available for columns
    • Create a simple table
    • Decipher how constraints can be created at table creation
    • Describe how schema objects work
  12. Other Schema Objects
    • Create a simple and complex view
    • Retrieve data from views
    • Create, maintain, and use sequences
    • Create and maintain indexes
    • Create private and public synonyms
  13. Control User Access
    • Differentiate system privileges from object privileges
    • Create Users
    • Grant System Privileges
    • Create and Grant Privileges to a Role
    • Change Your Password
    • Grant Object Privileges
    • How to pass on privileges?
    • Revoke Object Privileges
  14. Management of Schema Objects
    • Add, Modify, and Drop a Column
    • Add, Drop, and Defer a Constraint
    • How to enable and Disable a Constraint?
    • Create and Remove Indexes
    • Create a Function-Based Index
    • Perform Flashback Operations
    • Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
    • Query External Tables
  15. Manage Objects with Data Dictionary Views
    • Explain the data dictionary
    • Use the Dictionary Views
    • Table and Column Information
    • Query the dictionary views for constraint information
    • Query the dictionary views for view, sequence, index and synonym information
    • Add a comment to a table
    • Query the dictionary views for comment information
  16. Manipulate Large Data Sets
    • Use Subqueries to Manipulate Data
    • Retrieve Data Using a Subquery as Source
    • Insert Using a Subquery as a Target
    • Usage of the WITH CHECK OPTION Keyword on DML Statements
    • List the types of Multitable INSERT Statements
    • Use Multitable INSERT Statements
    • Merge rows in a table
    • Track Changes in Data over a period of time
  17. Data Management in different Time Zones
    • Time Zones
    • Compare Date and Time in a Session’s Time Zone
    • Difference between DATE and TIMESTAMP
    • INTERVAL Data Types
  18. Retrieve Data Using Sub-queries
    • Multiple-Column Subqueries
    • Pairwise and Nonpairwise Comparison
    • Scalar Subquery Expressions
    • Solve problems with Correlated Subqueries
    • Update and Delete Rows Using Correlated Subqueries
    • The EXISTS and NOT EXISTS operators
    • Invoke the WITH clause
    • The Recursive WITH clause
  19. Regular Expression Support
    • Use the Regular Expressions Functions and Conditions in SQL
    • Use Meta Characters with Regular Expressions
    • Perform a Basic Search using the REGEXP_LIKE function
    • Find patterns using the REGEXP_INSTR function
    • Extract Substrings using the REGEXP_SUBSTR function
    • Replace Patterns Using the REGEXP_REPLACE function
    • Usage of Sub-Expressions with Regular Expression Support
    • Implement the REGEXP_COUNT function
Pobierz konspekt szkolenia w formacie PDF

Dodatkowe informacje


Recommended Related Training Courses:

  • Oracle Database 11g: SQL Tuning Workshop Release 2
  • Oracle Database 11g: Performance Tuning DBA Release 2
  • Oracle SQL Tuning for Developers Workshop
  • Oracle Database 11g: Data Mining Techniques
Poziom trudności
Czas trwania 5 dni
Zobacz więcej szkoleń Oracle

Prosimy o wypełnienie poniższego formularza, jeśli chcą Państwo uzyskać więcej informacji o powyższym szkoleniu.

Cena szkolenia

  • Forma szkolenia: stacjonarne


Czytaj więcej o ctab

Czas trwania

5 dni

Dostępne terminy