Oracle Database SQL - 1Z0-071

Oracle Database SQL - 1Z0-071

Using Structured Query Language (SQL)    

•     Explain the relationship between a database and SQL

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

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

Defining SELECT Statements    

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

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

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

Defining Table Joins    

•     Describe the different types of joins and their features
•     Use joins to retrieve data from multiple tables
•     Use self joins

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

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

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    

•     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
•     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

Using the Set Operators    

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

Manipulating 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
•     Describe how schema objects work
•     Execute a basic SELECT statement

Creating Other Schema Objects    

•     Create simple and complex views with visible/invisible columns
•     Create, maintain and use sequences

Managing Objects with Data Dictionary Views    

•     Use the data dictionary views to research data on objects
•     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    

•     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

Manipulating Large Data Sets    

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