Szkolenia Oracle

Cel szkolenia

This Introduction to SQL/PLSQL Accelerated course will teach you SQL and PL/SQL programming language concepts. Learn how to write SQL commands, develop stored PL/SQL procedures, functions, packages and database triggers.This accelerated course covers 10 days worth of content in only 5 days.

Learn To:

  • Understand the fundamental and core concepts of relational databases.
  • Create reports of sorted and restricted data.
  • Run data manipulation statements (DML).
  • 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.
  • Create anonymous PL/SQL blocks, functions and procedures.
  • Conditionally control code flow (loops, control structures).
  • Create stored procedures, functions and packages.
  • Conditionally control code flow (loops, control structures).
  • Use PL/SQL packages to group and contain related constructs.
  • Create triggers to solve business challenges.
  • Leverage the Oracle supplied PL/SQL packages for various programming tasks.

Plan szkolenia Rozwiń listę

  1. Introduction to Oracle Database
    • List the features of Oracle Database 12c
    • 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 Object
    • 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. Introduction to PL/SQL
    • Overview of PL/SQL
    • Identify the benefits of PL/SQL Subprograms
    • Overview of the types of PL/SQL blocks
    • Create a Simple Anonymous Block
    • How to generate output from a PL/SQL Block?
  14. Declare PL/SQL Identifiers
    • List the different Types of Identifiers in a PL/SQL subprogram
    • Usage of the Declarative Section to Define Identifiers
    • Use variables to store data
    • Identify Scalar Data Types
    • The %TYPE Attribute
    • What are Bind Variables?
    • Sequences in PL/SQL Expressions
  15. Write Executable Statements
    • Describe Basic PL/SQL Block Syntax Guidelines
    • Learn to Comment the Code
    • Deployment of SQL Functions in PL/SQL
    • How to convert Data Types?
    • Describe Nested Blocks
    • Identify the Operators in PL/SQL
  16. Interaction with the Oracle Server
    • Invoke SELECT Statements in PL/SQL
    • Retrieve Data in PL/SQL
    • SQL Cursor concept
    • Avoid Errors by using Naming Conventions when using Retrieval and DML Statements
    • Data Manipulation in the Server using PL/SQL
    • Understand the SQL Cursor concept
    • Use SQL Cursor Attributes to Obtain Feedback on DML
    • Save and Discard Transactions
  17. Control Structures
    • Conditional processing using IF Statements
    • Conditional processing using CASE Statements
    • Describe simple Loop Statement
    • Describe While Loop Statement
    • Describe For Loop Statement
    • Use the Continue Statement
  18. Composite Data Types
    • Use PL/SQL Records
    • The %ROWTYPE Attribute
    • Insert and Update with PL/SQL Records
    • INDEX BY Tables
    • Examine INDEX BY Table Methods
    • Use INDEX BY Table of Records
  19. Explicit Cursors
    • What are Explicit Cursors?
    • Declare the Cursor
    • Open the Cursor
    • Fetch data from the Cursor
    • Close the Cursor
    • Cursor FOR loop
    • The %NOTFOUND and %ROWCOUNT Attributes
    • Describe the FOR UPDATE Clause and WHERE CURRENT Clause
  20. Exception Handling
    • Understand Exceptions
    • Handle Exceptions with PL/SQL
    • Trap Predefined Oracle Server Errors
    • Trap Non-Predefined Oracle Server Errors
    • Trap User-Defined Exceptions
    • Propagate Exceptions
    • RAISE_APPLICATION_ERROR Procedure
  21. Stored Procedures
    • Create a Modularized and Layered Subprogram Design
    • Modularize Development With PL/SQL Blocks
    • Understand the PL/SQL Execution Environment
    • List the benefits of using PL/SQL Subprograms
    • List the differences between Anonymous Blocks and Subprograms
    • Create, Call, and Remove Stored Procedures
    • Implement Procedures Parameters and Parameters Modes
    • View Procedure Information
  22. Stored Functions and Debugging Subprograms
    • Create, Call, and Remove a Stored Function
    • Identify the advantages of using Stored Functions
    • Identify the steps to create a stored function
    • Invoke User-Defined Functions in SQL Statements
    • Restrictions when calling Functions
    • Control side effects when calling Functions
    • View Functions Information
    • How to debug Functions and Procedures?
Pobierz konspekt szkolenia w formacie PDF

Dodatkowe informacje

Wymagania

Recommended Related Training Courses:

  • Using Java - for PL/SQL and Database Developers
  • Oracle Database: SQL Tuning for Developers
  • Oracle Database 12c: Analytic SQL for Data Warehousing
Poziom trudności
Certyfikat Uczestnicy szkoleń otrzymają zaświadczenia o ukończeniu kursu sygnowane przez firmę Oracle.
Prowadzący Autoryzowany wykładowca Oracle.
Lokalizacje Kraków - ul. Tatarska 5, II piętro, godz. 9:00 - 16:00
Warszawa - ul. Bielska 17, godz. 9:00 - 16:00
Zobacz więcej szkoleń tego producenta

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

14750 PLN NETTO

Czytaj więcej o ctab

Czas trwania

5 dni

Dostępne terminy

Brak ustalonych terminów dla tego szkolenia.

Zaproponuj własny termin