ALPHAPRIMETECH
112 New South Road,
Hicksville, NY
11801
+1 (212) 629 - 7366
admin@alphaprimetech.org

• Oracle and Structured Query Language (SQL)
• Identify the connection between an ERD and a Relational Database
• Explain the relationship between a database and SQL
• Describe the purpose of DDL
• Describe the purpose of DML
• Build a SELECT statement to retrieve data from an Oracle Database table

Restricting and Sorting Data

• Use the ORDER BY clause to sort SQL query results
• Limit the rows that are retrieved by a query
• Use ampersand substitution to restrict and sort output at runtime
• Use SQL row limiting clause

Using Single-Row Functions to Customize Output

• Use various types of functions available in SQL
• Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements

Using Conversion Functions and Conditional Expressions

• Describe various types of conversion functions that are available in SQL
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Apply general functions and conditional expressions in a SELECT statement

Reporting Aggregated Data Using the Group Functions

• Describe the use of group functions
• Group data by using the GROUP BY clause
• Include or exclude grouped rows by using the HAVING clause

Displaying Data from Multiple Tables

• Describe the different types of joins and their features
• Use SELECT statements to access data from more than one table using equijoins and nonequijoins
• Join a table to itself by using a self-join
• View data that generally does not meet a join condition by using outer joins

Using Subqueries to Solve Queries

• Define subqueries
• Describe the types of problems subqueries can solve
• Describe the types of subqueries
• Query data using correlated subqueries
• Update and delete rows using correlated subqueries
• Use the EXISTS and NOT EXISTS operators
• Use the WITH clause
• Use single-row and multiple-row subqueries

Using the Set Operators

• Describe set operators
• Use a set operator to combine multiple queries into a single query
• Control the order of rows returned

Manipulating Data

• Truncate data
• Insert rows into a table
• Update rows in a table
• Delete rows from a table
• Control transactions

Using DDL Statements to Create and Manage Tables

• Describe data types that are available for columns
• Create a simple table
• Create constraints for tables
• Drop columns and set column UNUSED
• Create and use external tables

Managing Objects with Data Dictionary Views

• Query various data dictionary views

Controlling User Access

• Differentiate system privileges from object privileges
• Grant privileges on tables and on a user
• Distinguish between privileges and roles

Managing Schema Objects

• Describe how schema objects work
• Create simple and complex views with visible/invisible columns
• Create, maintain and use sequences
• Create and maintain indexes including invisible indexes and multiple indexes on the same columns
• Perform flashback operations

Manipulating Large Data Sets

• Describe the features of multitable INSERTs
• Merge rows in a table

• List the architectural components of Oracle Database
• Explain the memory structures
• Describe the background processes
• Explain the relationship between logical and physical storage structures

• Use database management tools

• Understand initialization parameter files
• Start up and shut down an Oracle database instance
• View the alert log and access dynamic performance views
• Configure Oracle Net Services
• Use tools for configuring and managing the Oracle network
• Configure client-side network
• Configure communication between databases

• Create and manage database user accounts
• Grant and revoke privileges
• Create and manage roles
• Create and manage profiles

• Describe the storage of table row data in blocks
• Create and manage tablespaces

• Explain how Oracle database server automatically manages space
• Save space by using compression
• Proactively monitor and manage tablespace space usage
• Use the Segment Advisor
• Reclaim wasted space from tables and indexes by using the segment
shrink functionality
• Manage resumable space allocation

• Explain DML and undo data generation
• Monitor and administer undo data
• Describe the difference between undo data and redo data
• Configure undo retention

• Describe the locking mechanism and how Oracle manages data concurrency
• Monitor and resolve locking conflicts

• Explain DBA responsibilities for security and auditing
• Enable standard database auditing and unified auditing

• Identify the importance of checkpoints, redo log files, and archive log files

• Configure the fast recovery area
• Configure ARCHIVELOG mode

• Create consistent database backups
• Back up your database without shutting it down
• Create incremental backups
• Automate database backups
• Manage backups

• Determine the need for performing recovery
• Use Recovery Manager (RMAN) and the Data Recovery Advisor

• Describe ways to move data
• Use SQL*Loader to load data from a non-Oracle database
• Use external tables to move data via platform-independent files
• Explain the general architecture of Oracle Data Pump
• Use Data Pump Export and Import to move data between Oracle databases

• Use Oracle Scheduler to simplify management tasks
• Use job chains to perform a series of related tasks
• Use Scheduler jobs on remote systems
• Use advanced Scheduler features to prioritize jobs