OCA Oracle Database SQL | Introduction to SQL | 1Z0-071 Training Course

OCA Oracle Database SQL | Introduction to SQL | 1Z0-071 Course Contents

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

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

Using Subqueries to Solve Queries

Define subqueries

Describe the types of problems subqueries can solve

Describe the types of subqueries

Use 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

Manipulating Data

Insert rows into a table

Update rows in a table

Delete rows from a table

Control transactions

Creating Other Schema Objects

Create simple and complex views with visible/invisible columns

Create, maintain and use sequences

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