Course Oracle Database ProgrammerLC-ORACLE

Oracle databases and the SQL language

1. Introduction to databases:
   • basic concepts related to databases
   • relational data model
   • transactional databases.
2. A few words about Oracle:
   • DBMS architecture,
   • versions and licenses,
   • available tools,
   • environment configuration for work.
3. Create simple SQL queries:
   • displaying data retrieved from the database,
   • sorting and limiting the number of returned rows,
   • using alternative column names – aliases,
   • basic mathematical operations and combining strings,
   • data filtering,
   • substitution of variables during query execution,
   • NULL value and its handling in the database,
   • elimination of repetitive rows – DISTINCT clause.
4. Extended data selection:
   • logical operators for conjunction and negation of conditions,
   • comparison operators (>, >=, =, <, <=, <>, !=),
   • range operator (BETWEEN),
   • membership operator (IN),
   • pattern operator (LIKE).
5. Scalar functions
   • operations related to dates and time,
   • character type operations,
   • numeric functions
   • conditional functions, logical operators (CASE, DECODE, COALESCE,
     NVL, NVL2),
   • implicit type conversion,
   • functions related to type conversion: TO_CHAR, TO_DATE, TO_NUMBER,
   • use of functions in all places of the query
6. Data aggregation
   • grouping functions,
   • filtering rows after aggregation.
7. Set operations
   • UNION, UNION ALL, MINUS, INTERSECT operators,
   • checking the order of the rows returned by operations on sets.
8. Joining data sets:
   • available join criteria, equijoin, theta-join, natural joins
   • Oracle and ANSI SQL syntax
   • cross joins,
   • external joins,
   • self joins,
   • anti joins.
9. Nested queries (subqueries)
   • scope of subqueries,
   • scalar subqueries,
   • multi-line subqueries,
   • correlated and uncorrelated subqueries.
10. DML (Data Manipulation Language) commands and transactions
    • inserting rows,
    • deleting data from the database,
    • changing data in the database,
    • the scope of constraints when performing DML operations
11. Transaction management
    • transaction validation, rollback, savepoints,
    • Locks on concurrent execution of operations,
    • deadlocks,
    • UNDO space principles and FLASHBACK operations.
12. DDL (Data Definition Language) commands
    • Creating, deleting and modifying tables, column types,
    • constraints, primary and foreign keys,
    • creating simple and complex views,
    • schemas in the database,
    • other database objects: indexes, synonyms (public and private), sequences.
13. Database design
    • Basic information about standardisation,
    • normalization vs. efficiency
    • selecting appropriate data types
    • good practices of creating and naming objects

Programming in Oracle

1. Introduction to PL/SQL programming:
   • lexical units and conventions,
   • anonymous blocks and programs,
   • Executable block and declaration block,
   • running anonymous blocks and programs.
2. Defining and handling variables:
   • declaring and changing the values of variables,
   • use of standard data types in variables,
   • %TYPE and %ROWTYPE attributes,
   • working with compound types, custom compound types,
   • Records, tables and record tables,
   • constants.
3. Structured programming:
   • control statements: conditions, loops.
4. INTO clause
   • use of SQL functions,
   • exception handling, typical built-in exceptions,
   • creating your own exceptions,
   • labels, visibility of names, nested blocks.
5. Cursors:
   • implicit and explicit cursors,
   • cursors with parameters,
   • row blocking (FOR UPDATE clause),
   • WHERE CURRENT OF clause.
6. Program development:
   • stored procedures,
   • creating functions with their use in SQL,
   • parameters in programs,
   • ways to call programs.
7. Packages:
   • package construction and dependencies,
   • running packages,
   • polymorphism in packages (overloading),
   • embedded packages.
8. Triggers:
   • DML triggers at the command and line level,
   • DML triggers on views,
   • DDL command triggers,
   • triggers on system events.
9. Dynamic SQL:
   • the need to use dynamic SQL, contraindications,
   • native dynamic SQL,
   • DBMS_SQL package.

Administration of Oracle database:

1. Introduction to databases:
   • basic concepts related to databases
   • relational data model
   • transactional databases
   • Oracle versions and licenses
   • database tools
   • database versus data instance
   • basic administrator tasks
2. Configuring the network environment:
   • Oracle engine operation logic in a network environment
   • static and dynamic listener
   • registering a database in the system
   • oracle Net Configuration Assistant
   • database link
3. Physical and logical structure of the database:
   • logical breakdown and relationship with physical data structure
   • contents of database blocks
   • creation of tablespace
   • system tablespaces
4. Transaction management:
   • validation, rollback of transactions, savepoints
   • locks on concurrent execution of operations
   • deadlocks
   • UNDO space principle and FLASHBACK operations
5. ORACLE file architecture:
   • creation and management of control files
   • server parameter files – binary and text
   • redo log files
   • archived redo log files
   • backup files
   • use of message log – alert log
   • trace files
   • password files
6. Database instance architecture and management:
   • database parameters
   • parameter initialization types
   • ADR
   • instance memory structures
   • memory management using AMM and ASMM
   • background processes
   • database dictionaries and system views
7. Launching and shutting down an instance:
   • launch modes
   • shutdown modes
   • checking the database status
   • database services
8. User and rights management:
   • user rights
   • inheritance of objects and system privileges
   • built-in roles and creating your own roles
   • administrator accounts
   • management of users and roles in container databases
   • database profiles