SQL

Preparation for “OCA”, exam 1z0-071

Exam topics

Relational Database concepts
  • Explaining the theoretical and physical aspects of a relational database
  • Relating clauses in SQL Select Statement to Components of an ERD
  • Explaining the relationship between a database and SQL

Retrieving Data using the SQL SELECT Statement

  • Using Column aliases
  • Using The SQL SELECT statement
  • Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
  • Using Arithmetic expressions and NULL values in the SELECT statement

Restricting and Sorting Data

  • Applying Rules of precedence for operators in an expression
  • Limiting Rows Returned in a SQL Statement
  • Using Substitution Variables
  • Using the DEFINE and VERIFY commands
  • Sorting Data

Using Single-Row Functions to Customize Output

  • Manipulating strings with character functions in SQL SELECT and WHERE clauses
  • Performing arithmetic with date data
  • Manipulating numbers with the ROUND, TRUNC and MOD functions
  • Manipulating dates with the date function

Using Conversion Functions and Conditional Expressions

  • Applying the NVL, NULLIF, and COALESCE functions to data
  • Understanding implicit and explicit data type conversion
  • Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Nesting multiple functions

Reporting Aggregated Data Using Group Functions

  • Restricting Group Results
  • Creating Groups of Data
  • Using Group Functions

Displaying Data from Multiple Tables

  • Using Self-joins
  • Using Various Types of Joins
  • Using Non equijoins
  • Using OUTER joins
  • Understanding and Using Cartesian Products

Using Subqueries to Solve Queries

  • Using Single Row Subqueries
  • Using Multiple Row Subqueries
  • Update and delete rows using correlated subqueries

Using SET Operators

  • Matching the SELECT statements
  • Using the ORDER BY clause in set operations
  • Using The INTERSECT operator
  • Using The MINUS operator
  • Using The UNION and UNION ALL operators

Managing Tables using DML statements

  • Managing Database Transactions
  • Controlling transactions
  • Perform Insert, Update and Delete operations
  • Performing multi table Inserts
  • Performing Merge statements

Managing Indexes Synonyms and Sequences

  • Managing Indexes
  • Managing Synonyms
  • Managing Sequences

Use DDL to manage tables and their relationships

  • Describing and Working with Tables
  • Describing and Working with Columns and Data Types
  • Creating tables
  • Dropping columns and setting column UNUSED
  • Truncating tables
  • Creating and using Temporary Tables
  • Creating and using external tables
  • Managing Constraints

Managing Views

  • Managing Views

Controlling User Access

  • Differentiating system privileges from object privileges
  • Granting privileges on tables
  • Distinguishing between granting privileges and roles

Managing Objects with Data Dictionary Views

  • Using data dictionary views

Managing Data in Different Time Zones

  • Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP
  • Working with INTERVAL data types

source: https://education.oracle.com/oracle-database-sql/pexam_1z0-071