Oracle Database Associate (OCA)

Oracle Database SQL | 1Z0-071

Using Structured Query Language (SQL)

• Explain the relationship between a database and SQL


Using Data Manipulation Language (DML) and Transaction Control Language (TCL

• Describe the purpose of DML

• Use DML to manage data in tables

• Use TCL to manage transactions


Using Basic SELECT statements

• Build a SELECT statement to retrieve data from an Oracle Database table

• Use the WHERE clause to the SELECT statement to filter query results


Defining Table Joins

• Describe the different types of joins and their features

• Use joins to retrieve data from multiple tables

• Use self joins


Using Conversion Functions and Conditional Expressions

• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

• Apply general functions and conditional expressions in a SELECT statement


Displaying Data from Multiple Tables

• 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 the Set Operators

• Use a set operator to combine multiple queries into a single query

• Control the order of rows returned


Using DDL Statements to Create and Manage Tables

• Describe data types that are available for columns

• Create a simple table

• Create constraints for tables

• Describe how schema objects work

• Execute a basic SELECT statement


Managing Objects with Data Dictionary Views

• Use the data dictionary views to research data on objects

• Query various data dictionary views


Managing Schema Objects

• Manage constraints

• Create and maintain indexes including invisible indexes and multiple indexes on the same columns

• Drop columns and set column UNUSED

• Perform flashback operations

• Create and use external tables


Using Data Definition Language (DDL)

• Describe the purpose of DDL

• Use DDL to manage tables and their relationships

• Explain the theoretical and physical aspects of a relational database


Defining SELECT Statements

• Identify the connection between an ERD and a database using SQL SELECT statements


Restricting and Sorting Data

• Use the ORDER BY clause to sort SQL query results

• Limit the rows that are retrieved by a query

• Sort the rows that are retrieved by a query

• Use ampersand substitution to restrict and sort output at runtime


Using Single-Row Functions to Customize Output

• Use various types of functions available in SQL

• Use conversion functions

• Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements


Manipulating Data

• Insert rows into a table

• Update rows in a table

• Delete rows from a table

• Control transactions


Controlling User Access

• Differentiate system privileges from object privileges

• Grant privileges on tables and on a user

• Distinguish between privileges and roles


Manipulating Large Data Sets

• Describe the features of multitable INSERTs

• Merge rows in a table