oracle database sql expert 1z0-047

8/12/2019 Oracle Database SQL Expert 1Z0-047

1/4

Introduction to Oracle Database

List the features of Oracle Database 11g

Discuss the basic design, theoretical, and physical aspects of a relational database

Categorize the different types of SQL statements

Describe the data set used by the course

Log on to the database using SQL Developer environment

Save queries to files and use script files in SQL Developer

Retrieve Data using the SQL SELECT Statement

List the capabilities of SQL SLC! statements

"enerate a report of data from the output of a basic SLC! statement

Select #ll Columns

Select Specific Columns

$se Column %eading Defaults

$se #rithmetic Operators

$nderstand Operator &recedence

Learn the DSC'() command to display the table structure

Learn to Restrict and Sort Data

*rite queries that contain a *%' clause to limit the output retrieved

List the comparison operators and logical operators that are used in a *%' clause

Describe the rules of precedence for comparison and logical operators

$se character string literals in the *%' clause

*rite queries that contain an O'D' )+ clause to sort the output of a SLC! statement

Sort output in descending and ascending order

Usage of Single-Ro !unctions to Customi"e Out#ut

Describe the differences beteen single ro and multiple ro functions

-anipulate strings ith character function in the SLC! and *%' clauses

-anipulate numbers ith the 'O$.D, !'$.C, and -OD functions

&erform arithmetic ith date data

-anipulate dates ith the D#! functions

Invo$e Conversion !unctions and Conditional E%#ressions

Describe implicit and e/plicit data type conversion

$se the !O0C%#', !O0.$-)', and !O0D#! conversion functions

.est multiple functions

#pply the .L, .$LL(2, and CO#LSC functions to data

$se conditional (2 !%. LS logic in a SLC! statement


2/4

&ggregate Data Using the 'rou# !unctions

$se the aggregation functions to produce meaningful reports

Divide the retrieved data in groups by using the "'O$& )+ clause

/clude groups of data by using the %#(." clause

Dis#la( Data !rom )ulti#le Tables Using *oins

*rite SLC! statements to access data from more than one table

ie data that generally does not meet a 3oin condition by using outer 3oins

4oin a table to itself by using a self 3oin

Use Sub-+ueries to Solve Queries

Describe the types of problem that sub5queries can solve

Define sub5queries

List the types of sub5queries *rite single5ro and multiple5ro sub5queries

The SET O#erators

Describe the S! operators

$se a S! operator to combine multiple queries into a single query

Control the order of ros returned

Data )ani#ulation Statements

Describe each D-L statement (nsert ros into a table

Change ros in a table by the $&D#! statement

Delete ros from a table ith the DL! statement

Save and discard changes ith the CO--(! and 'OLL)#C6 statements

/plain read consistency

Use of DDL Statements to Create and )anage Tables

Categorize the main database ob3ects

'evie the table structure

List the data types available for columns

Create a simple table

Decipher ho constraints can be created at table creation

Describe ho schema ob3ects or7

Other Schema Ob,ects

Create a simple and comple/ vie


3/4

'etrieve data from vies

Create, maintain, and use sequences

Create and maintain inde/es

Create private and public synonyms

Control User &ccess

Differentiate system privileges from ob3ect privileges

Create $sers

"rant System &rivileges

Create and "rant &rivileges to a 'ole

Change +our &assord

"rant Ob3ect &rivileges

%o to pass on privileges8

'evo7e Ob3ect &rivileges

)anagement of Schema Ob,ects

#dd, -odify, and Drop a Column

#dd, Drop, and Defer a Constraint

%o to enable and Disable a Constraint8

Create and 'emove (nde/es

Create a 2unction5)ased (nde/

&erform 2lashbac7 Operations

Create an /ternal !able by $sing O'#CL0LO#D' and by $sing O'#CL0D#!#&$-&

Query /ternal !ables

)anage Ob,ects ith Data Dictionar( ies

/plain the data dictionary

$se the Dictionary ies

$S'0O)4C!S and #LL0O)4C!S ies

!able and Column (nformation

Query the dictionary vies for constraint information

Query the dictionary vies for vie, sequence, inde/ and synonym information

#dd a comment to a table

Query the dictionary vies for comment information

)ani#ulate Large Data Sets

$se Subqueries to -anipulate Data

'etrieve Data $sing a Subquery as Source

(nsert $sing a Subquery as a !arget

$sage of the *(!% C%C6 O&!(O. 6eyord on D-L Statements


4/4

List the types of -ultitable (.S'! Statements

$se -ultitable (.S'! Statements

-erge ros in a table

!rac7 Changes in Data over a period of time

Data )anagement in different Time .ones

!ime 9ones

C$''.!0D#!, C$''.!0!(-S!#-&, and LOC#L!(-S!#-&

Compare Date and !ime in a Session:s !ime 9one

D)!(-9O. and SSS(O.!(-9O.

Difference beteen D#! and !(-S!#-&

(.!'#L Data !ypes

$se ;!'#C!, !90O22S! and 2'O-0!9

(nvo7e !O0!(-S!#-&,!O0+-(.!'#L and !O0DS(.!'#L

Retrieve Data Using Sub-+ueries

-ultiple5Column Subqueries

&airise and .onpairise Comparison

Scalar Subquery /pressions

Solve problems ith Correlated Subqueries

$pdate and Delete 'os $sing Correlated Subqueries

!he ;(S!S and .O! ;(S!S operators

(nvo7e the *(!% clause

!he 'ecursive *(!% clause

Regular E%#ression Su##ort

$se the 'egular /pressions 2unctions and Conditions in SQL

$se -eta Characters ith 'egular /pressions

&erform a )asic Search using the '";&0L(6 function

2ind patterns using the '";&0(.S!' function

/tract Substrings using the '";&0S$)S!' function

'eplace &atterns $sing the '";&0'&L#C function

$sage of Sub5/pressions ith 'egular /pression Support

(mplement the '";&0CO$.! function

oracle database sql expert 1z0-047

Documents