By Exam Number: 1Z0-061
Exam Title: Oracle Database 12c: SQL Fundamentals (retiring October 31, 2018)
Associated Certification Paths
Oracle Database 11g Administrator Certified Associate
Oracle PL/SQL Developer Certified Associate
Oracle Database 12c Administrator Certified Associate
Duration: 120 minutes
Number of Questions: 75
Passing Score: 65%
Validated Against: This exam has been validated against Oracle Database 12.1.0.1.0.
Format: Multiple Choice
Complete Recommended Training
Complete the training below to prepare for your exam (optional):
Complete one of the courses below to prepare for your exam (optional):
Oracle Database: Introduction to SQL
Oracle Database: SQL Workshop I
Oracle Database: SQL and PL/SQL Fundamentals
Additional Preparation and Information
A combination of Oracle training and hands-on experience (attained via labs and/or field experience) provides the best preparation for passing the exam.
Practice Exams: Oracle Authorized practice exam from Kaplan IT Training: 1Z0-061 Oracle Database 12c: SQL Fundamentals
This exam retires October 31, 2018. You may purchase vouchers for the exam through April 30, 2018.
Introduction
Describe the features of Oracle Database 12c
Describe the salient features of Oracle Cloud 12c
Explain the theoretical and physical aspects of a relational database
Describe Oracle server’s implementation of RDBMS and object relational database management system (ORDBMS)
Retrieving Data using the SQL SELECT Statement
Explain the capabilities of SQL SELECT statements
Execute a basic SELECT statement
Restricting and Sorting Data
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
Describe various types of functions available in SQL
Use character, number, and date functions in SELECT statements
Using Conversion Functions and Conditional Expressions
Describe various types of conversion functions that are available in SQL
Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
Apply conditional expressions in a SELECT statement
Reporting Aggregated Data Using the Group Functions
Identify the available 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 Using Joins
Write 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
Generate a Cartesian product of all rows from two or more tables
Using Subqueries to Solve Queries
Define subqueries
Describe the types of problems that the subqueries can solve
Describe the types of subqueries
Write single-row and multiple-row subqueries
Using the SET Operators
Describe set operators
Use a set operator to combine multiple queries into a single query
Control the order of rows returned
Managing Tables using DML statements
Truncate data
Insert rows into a table
Update rows in a table
Delete rows from a table
Control transactions
Introduction to Data Definition Language
Categorize the main database objects
Explain the table structure
Describe the data types that are available for columns
Create a simple table
Explain how constraints are created at the time of table creation
Describe how schema objects work
QUESTION: 1
You need to create a table for a banking application. One of the columns in the table has the
following requirements:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and subtracted with date data type without using conversion functions.
3) The maximum period of the credit provision in the application is 30 days.
4) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
A. DATE
B. NUMBER
C. TIMESTAMP
D. INTERVAL DAY TO SECOND
E. INTERVAL YEAR TO MONTH
Answer: D
INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. This
data type is useful for representing the precise difference between two datetime values.
You can perform a number of arithmetic operations on date (DATE), timestamp (TIMESTAMP,
TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE) and interval (INTERVAL
DAY TO SECOND and INTERVAL YEAR TO MONTH) data.
– It stores duration of the credit as days
– The format stored is numeric format, and you know that numeric values can be easily added and
subtracted with date data type without using conversion functions (i.e. SELECT SYSDATE – 1 FROM DUAL;)
– The interest has to be calculated for the number of days an individual has taken a credit for, so it
will be easy to calculate interest by using the interest rate and duration of the the credit which is numeric format.
References:
QUESTION: 2
Which three tasks can be performed using SQL functions built into Oracle Database?
A. Displaying a date in a nondefault format
B. Finding the number of characters in an expression
C. Substituting a character string in a text expression with a specified string
D. Combining more than two columns or expressions into a single column in the output
Answer: A,B,C
QUESTION: 3
Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?
A. First normal form
B. Second normal form
C. Third normal form
D. Fourth normal form
Answer: B
According to the Second Normal Form (2NF) there must be no partial dependencies on a
concatenated key.
QUESTION: 4
Examine the types and examples of relationships that follow:
1. One-to-one a) Teacher to students
2. One-to-many b) Employees to Manager
3. Many-to-one c) Person to SSN
4. Many-to-many d) Customers to products
Which option indicates the correctly matched relationships?
A. 1-a, 2-b, 3-c, and 4-d
B. 1-c, 2-d, 3-a, and 4-b
C. 1-c, 2-a, 3-b, and 4-d
D. 1-d, 2-b, 3-a, and 4-c
Answer: C
QUESTION: 5
Which two statements are true regarding subqueries?
A. A subquery can retrieve zero or more rows.
B. Only two subqueries can be placed at one level.
C. A subquery can be used only in SQL query statements.
D. A subquery can appear on either side of a comparison operator.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
Answer: A,D
Using a Subquery to Solve a Problem
Suppose you want to write a query to find out who earns a salary greater than Abel’s salary.
To solve this problem, you need two queries: one to find how much Abel earns, and a second query
to find who earns more than that amount.
You can solve this problem by combining the two queries, placing one query inside the other query.
The inner query (or subquery) returns a value that is used by the outer query (or main query).
Using a subquery is equivalent to performing two sequential queries and using the result of the first
query as the search value in the second query.
Subquery Syntax
A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You
can build powerful statements out of simple ones by using subqueries. They can be very useful when
you need to select rows from a table with a condition that depends on the data in the table itself.
You can place the subquery in a number of SQL clauses, including the following:
WHERE clause
HAVING clause
FROM clause
In the syntax:
operator includes a comparison condition such as >, =, or IN
Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) and
multiple-row operators (IN, ANY, ALL, EXISTS).
The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement.
The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query.
Guidelines for Using Subqueries
Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition
for readability. (However, the subquery can appear on either side of the comparison operator.) Use
single-row operators with single-row subqueries and multiple-row operators with multiple-row subqueries.
Subqueries can be nested to an unlimited depth in a FROM clause but to “only” 255 levels in a
WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.
Click here to view complete Q&A of 1Z0-061 exam
Certkingdom Review, Certkingdom PDF Torrents
Best Oracle 1Z0-061 Certification, Oracle 1Z0-061 Training at certkingdom.com
Comments Off on 1Z0-061 Oracle Database 12c: SQL Fundamentals