1Z0-071 Oracle Database SQL

Exam Number: 1Z0-071
Exam Title: Oracle Database SQL
Associated Certification Paths
Oracle Database 11g Administrator Certified Associate
Oracle PL/SQL Developer Certified Associate
Oracle Database 12c Administrator Certified Associate
Oracle Database SQL Certified Associate
Duration: 100 minutes
Number of Questions: 73
Passing Score: 63%
Validated Against: This exam was validated against 11g Release 2 version 11.2.0.1.0 and up to 12c Release 1 version 12.1.0.1.0.
Format: Multiple Choice

Complete Recommended Training
Oracle Database 12c: Introduction to SQL or
Oracle Database: Introduction to SQL

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.

Also available in Spanish – 1Z0-071-ESN on the Pearson VUE website.
Practice Exams: Oracle Authorized practice exam from Kaplan IT Training: 1Z0-071 DBCert: Oracle Database SQL

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

QUESTION 1
Evaluate the following SQL statement:
SELECT product_name || ‘it’s not available for order’
FROM product_information
WHERE product_status = ‘obsolete’;
You received the following error while executing the above query:
ERROR:
ORA-01756: quoted string not properly terminated
What would you do to execute the query successfully?

A. Enclose the character literal string in the SELECT clause within the double quotation marks.
B. Do not enclose the character literal string in the SELECT clause within the single quotation marks.
C. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.
D. Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause.

Answer: C

QUESTION 2
Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME
————————
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You need to display customers’ second names where the second name starts with “Mc” or “MC.”
Which query gives the required output?

A. SELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1) FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1))=’Mc’;
B. SELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)) LIKE ‘Mc%’;
C. SELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1) FROM customers
WHERE SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1) LIKE INITCAP(‘MC%’);
D. SELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)) = INITCAP(‘MC%’);

Answer: B

QUESTION 3
Which three statements are true regarding group functions? (Choose three.)

A. They can be used on columns or expressions.
B. They can be passed as an argument to another group function.
C. They can be used only with a SQL statement that has the GROUP BY clause.
D. They can be used on only one column in the SELECT clause of a SQL statement.
E. They can be used along with the single-row function in the SELECT clause of a SQL statement.

Answer: A,B,E

QUESTION 4
Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED order_date;
Which statement is true?

A. The DESCRIBE command would still display the ORDER_DATE column.
B. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
C. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.
D. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.

Answer: D

QUESTION 5
Which two statements are true regarding constraints? (Choose two.)

A. A foreign key cannot contain NULL values.
B. A column with the UNIQUE constraint can contain NULL.
C. A constraint is enforced only for the INSERT operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All the constraints can be defined at the column level as well as the table level

Answer: B,D

QUESTION 6
Which three statements are true regarding the data types?

A. Only one LONG column can be used per table.
B. ATIMESTAMP data type column stores only time values with fractional seconds.
C. The BLOB data type column is used to store binary data in an operating system file.
D. The minimum column width that can be specified for a varchar2 data type column is one.
E. The value for a CHAR data type column is blank-padded to the maximum defined column width.

Answer: A,D,E

QUESTION 7
The first DROP operation is performed on PRODUCTS table using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?

A. It recovers only the table structure.
B. It recovers the table structure, data, and the indexes.
C. It recovers the table structure and data but not the related indexes.
D. It is not possible to recover the table structure, data, or the related indexes.

Answer: D

Click here to view complete Q&A of 1Z0-071 exam
Certkingdom Review, Certkingdom PDF Torrents