Oracle Certified Associate (1Z0-071) Study Course

This course is a study guide for the Oracle SQL Certified Associate (1Z0-071) exam).

You'll learn:

  • All of the topics included in the Oracle Certified SQL Associate certification (exam code 1Z0-071)
  • Tips for taking and preparing for the exam
  • A practice exam (not exam dumps) for preparing you for the exam

Get Access to All Courses Now

Access this course and all other courses (and more) in Database Star Academy instantly:

Course Topics:


  • Course Intro
  • Exam Information
  • Exam Tips
  • Setting Up the Sample Database


  • Explain the relationship between a database and SQL
  • Identify the connection between an ERD and a database using SQL SELECT statements
  • Describe the purpose of DML
  • Build a SELECT statement to retrieve data from an Oracle Database table
  • 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


  • 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


  • Use various types of functions available in SQL
  • Use character, number, and date functions in SELECT statements
  • Use analytical functions in SELECT statements
  • Use conversion functions
  • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Apply general functions and conditional expressions in a SELECT statement
  • Describe the use of group functions
  • Group data by using the GROUP BY clause
  • Include or exclude grouped rows by using the HAVING clause

Set Operators and Subqueries

  • Use a set operator to combine multiple queries into a single query
  • Control the order of rows returned
  • Define subqueries
  • Describe the types of problems subqueries can solve
  • Describe the types of subqueries
  • Use single-row and multiple-row subqueries
  • Use correlated subqueries
  • Update and delete rows using correlated subqueries
  • Use the EXISTS and NOT EXISTS operators
  • Use the WITH clause

Manipulating Data

  • Insert rows into a table
  • Update rows in a table
  • Delete rows from a table
  • Describe the features of multitable INSERTs
  • Control transactions
  • Merge rows in a table

Using DDL

  • Describe the purpose of DDL
  • Describe data types that are available for columns
  • Create a simple table
  • Create constraints for tables
  • Describe how schema objects work

Access and Schema Objects

  • Query various data dictionary views
  • 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
  • Create simple and complex views with visible/invisible columns
  • Create, maintain and use sequences
  • Differentiate system privileges from object privileges
  • Grant privileges on tables and on a user
  • Distinguish between privileges and roles

© 2019 Database Star | Privacy | Terms of Service