Top Oracle 1z1-071 Courses Online - Updated [Nov-2021]

1z1-071 Practice Dumps - Verified By BraindumpsIT Updated 305 Questions

Topics of Oracle 1Z0-071: Oracle Database SQL Exam

The Oracle 1Z0-071 dumps tests the abilities and knowledge of the candidates by checking the following objectives:

Relational Database concepts

• Explaining the relationship between a database and SQL
• Relating clauses in SQL Select Statement to Components of an ERD
• Explaining the theoretical and physical aspects of a relational database

Retrieving Data using the SQL SELECT Statement

• Using Arithmetic expressions and NULL values in the SELECT statement
• Using Column aliases
• Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
• Using The SQL SELECT statement

Restricting and Sorting Data

• Applying Rules of precedence for operators in an expression
• Using Substitution Variables
• Using the DEFINE and VERIFY commands
• Limiting Rows Returned in a SQL Statement
• Sorting Data

Using Single-Row Functions to Customize Output

• Manipulating numbers with the ROUND, TRUNC and MOD functions
• Manipulating strings with character functions in SQL SELECT and WHERE clauses
• Manipulating dates with the date function
• Performing arithmetic with date data

Using Conversion Functions and Conditional Expressions

• Understanding implicit and explicit data type conversion
• Applying the NVL, NULLIF, and COALESCE functions to data
• Nesting multiple functions
• Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

Reporting Aggregated Data Using Group Functions

• Restricting Group Results
• Creating Groups of Data
• Using Group Functions

Displaying Data from Multiple Tables

• Using Self-joins
• Using Various Types of Joins
• Understanding and Using Cartesian Products
• Using OUTER joins
• Use of non-equijoins

Using Subqueries to Solve Queries

• Update and delete rows using correlated subqueries
• Using Multiple Row Subqueries
• Using Single Row Subqueries

Using SET Operators

• Matching the SELECT statements
• Using The MINUS operator
• Using The INTERSECT operator
• Using the ORDER BY clause in set operations
• Using The UNION and UNION ALL operators

Managing Tables using DML statements

• Managing Database Transactions
• Controlling transactions
• Performing multi table Inserts
• Performing Merge statements
• Perform Insert, Update and Delete operations

Managing Indexes Synonyms and Sequences

• Managing Indexes
• Managing Synonyms
• Managing Sequences

Use DDL to manage tables and their relationships

• Creating and using Temporary Tables
• Describing and Working with Tables
• Describing and Working with Columns and Data Types
• Creating and using external tables
• Creating tables
• Truncating tables
• Dropping columns and setting columns UNUSED
• Managing Constraints

Managing Views

• Distinguishing between granting privileges and roles
• Differentiating system privileges from object privileges
• Controlling User Access
• Managing Views
• Granting privileges on tables

Managing Objects with Data Dictionary Views

• Working with INTERVAL data types
• Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP
• Managing Data in Different Time Zones
• Using data dictionary views

Who should take the Oracle 1Z0-071: Oracle Database SQL Exam

This course is for software administrators, database designers, and system operators.

Introduction to Oracle 1Z0-071: Oracle Database SQL Exam

The Oracle Database SQL Management exam evaluates the knowledge and skills of RAC and Grid Infrastructure Database Administrators and System Administrators with at least one year of experience. Candidates should be able to mount, maintain, monitor, tune and restore RAC databases, clusters and Oracle Automated Storage Management (ASM). You should have a clear knowledge of ASM, Clusterware and Oracle RAC database architectures. They should also be able to track and tune the upload, configuration, backup, and retrieval of these components.

For Oracle Data Base Software produced by Oracle Corporation, the Oracle Real Application Cluster (RAC) in data base computing offers clustering software and high availability in Oracle database environments. Oracle Corporate provides RAC in the Business version, provided that the nodes are clustered using Oracle Clusterware. Oracle RAC requires many computers to operate Oracle RDBMS applications concurrently with clustering while accessing a single database.

Oracle Grid Infrastructure is the software Oracle that maintains a storage framework that involves file system control, volume management and restart process automation. You need to set up Oracle grid infrastructure before deploying Oracle Database if you want to use Oracle restart or Oracle Automated Storage Management (ASM) services.

 

NEW QUESTION 177
Examine this SQL statement:
SELECT cust_id, cust_last_name "Last Name
FROM customers
WHERE countryid=10
UNION
SELECT custid CUSTNO, cust_last_name
FROM customers
WHERE countryid=30
Identify three ORDER BY clauses, any one of which can complete the query successfully.

• A. ORDER BY"CUST NO"
• B. ORDER BY CUSTNO
• C. ORDER BY 2, cust_id
• D. ORDER BY "Last Name"
• E. ORDERBY2, 1

Answer: C,D,E

 

NEW QUESTION 178
Examine the description of the PROMOTIONS TABLE:
You want to display the unique is promotion costs in each promotion category.
Which two queries can be used?

• A. SELECT DISTINCT promo_cost ||'in'IIDISTINCT promo_category promotions ORDER BY1;
• B. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
• C. SELECT promo_category, DISTINCT promo_cost FROM promotiong ORDER BY 2:
• D. SELECT promo_cost, promo_category FROM promotions ORDER BY 1
• E. select DISTINCT promo_categoryIl 'has'||promol_cost as COSTS FROM promotions ORDER BY 1:

Answer: B,E

 

NEW QUESTION 179
Examine the commands used to create DEPARTMENT_DETAILSand COURSE_DETAILS tables:

You want to generate a list of all department IDs that do not exist in the COURSE_DETAILStable.
You execute the SQL statement:

What is the outcome?

• A. It executes successfully and displays the required list.
• B. It fails because the ON clause condition is not valid.
• C. It executes successfully but displays an incorrect list.
• D. It fails because the join type used is incorrect.

Answer: A

 

NEW QUESTION 180
Examine the description of the CUSTOMERS table:

For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?

• A. Option C
• B. Option A
• C. Option E
• D. Option B
• E. Option D

Answer: B

 

NEW QUESTION 181
Which two statements are true about the DUAL table?

• A. It can be accessed only by the SYS user.
• B. It can display multiple rows but only a single column.
• C. It can be used to display only constants or pseudo columns.
• D. It can be accessed by any user who has the SELECT privilege in any schema.
• E. It consists of a single row and single column of VARCHAR2 data type.
• F. It can display multiple rows and columns.

Answer: D,E

 

NEW QUESTION 182
View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?

• A. The statement would execute successfully to produce the required output.
• B. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
• C. The statement would not execute because the GROUP BY clause cannot be used in the inline.
• D. The statement would not execute because inline views and outer joins cannot be used together.

Answer: A

 

NEW QUESTION 183
You want to display the date for the first Monday of the next month and issue the following command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'),
'dd "is the first Monday for" fmmonth rrrr')
FROM DUAL;
What is the outcome?

• A. It executes successfully and returns the correct result.
• B. It executes successfully but does not return the correct result.
• C. In generates an error because TO_CHARshould be replaced with TO_DATE.
• D. In generates an error because fmand double quotation marks should not be used in the format string.
• E. In generates an error because rrrrshould be replaced by rrin the format string.

Answer: A

 

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

• A. A column with the UNIQUE constraint can store NULLS.
• B. A constraint is enforced only for an INSERT operation on a table.
• C. You can have more than one column in a table as part of a primary key.
• D. A foreign key cannot contain NULL values.

Answer: A,C

 

NEW QUESTION 185
Examine the structure of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?

• A. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
• B. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS
• C. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS
• D. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE
  FROM BOOKS_TRANSACTIONS

Answer: B

 

NEW QUESTION 186
View the Exhibit and examine the description of the EMPLOYEEStable.

You want to calculate the total renumeration for each employee. Total renumeration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.
Which SQL statement would you execute to get the desired output?
SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct,

• A. FROM EMPLOYEES;
• B. FROM EMPLOYEES;
  SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12
• C. "Total"
  FROM EMPLOYEES;
  SELECT first_name, salary*12 + NVL(salary,0)*commission_pct, "Total"
• D. salary,salary+commission_pct))"Total"
  FROM EMPLOYEES;
  SELECT first_name, salary, salary*12+salary*commission_pct "Total"

Answer: D

 

NEW QUESTION 187
View the Exhibit and examine the structure of the ORDER_ITEMS table.

You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.
Which query would produce the desired result?
SELECT order_id

• A. FROM order_items
  WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity)
  FROM order_items)
  GROUP BY order_id;
  SELECT order_id
• B. FROM order_items
  WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)
  FROM order_items
  GROUP BY order_id)
• C. FROM order_items
  WHERE(unit_price*quantity) = MAX(unit_price*quantity)
  GROUP BY order_id;
  SELECT order_id
• D. FROM order_items
  GROUP BY order_id
  HAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity)) FROM order_items GROUP BY order_id); SELECT order_id

Answer: D

 

NEW QUESTION 188
View the Exhibit and examine the structure of the PORDUCT_INFORMATION table. (Choose the best answer.)

PRODUCT_ID column is the primary key.
You create an index using this command:
SQL > CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table.
Which query would use the UPPER_NAME_IDX index?

• A. SELECT UPPER(product_name)FROM product_informationWHERE product_id = 2254;
• B. SELECT product_idFROM product_informationWHERE UPPER(product_name) IN ('LASERPRO',
  'CABLE');
• C. SELECT product_id, UPPER(product_name)FROM product_informationWHERE
  UPPER(product_name) = 'LASERPRO' OR list_price > 1000;
• D. SELECT UPPER(product_name)FROM product_information;

Answer: B

 

NEW QUESTION 189
The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER.
Which two queries execute successfully?

• A. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
• B. SELECT NVL2 (cust_credit_limit * .15, 'Not Available') FROM customers;
• C. SELECT TO_CHAR(NVL(cust_credit_limit * .15, 'Not Available')) FROM customers;
• D. SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
• E. SELECT NVL (cust_credit_limit * .15, 'Not Available') FROM customers;

Answer: B,D

 

NEW QUESTION 190
Examine this SELECT statement and view the Exhibit to see its output:

SELECT constraints_name, constraints_type, search_condition, r_constraint_name, delete_rule, status,
FROM user_constraints
WHERE table_name = 'ORDERS';
Which two statements are true about the output? (Choose two.)

• A. In the second column, 'c' indicates a check constraint.
• B. The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.
• C. The R_CONSTRAINT_NAME column contains an alternative name for the constraint.
• D. The STATUS column indicates whether the table is currently in use.

Answer: A,B

 

NEW QUESTION 191
You execute this command:

Which two are true?

• A. It retains the indexes defined on the table.
• B. It drops any triggers defined on the table.
• C. It always retains the space used by the removed rows.
• D. A ROLLBACK statement can be used to retrieve the deleted data.
• E. A FLASHBACK TABLE statement can be used to retrieve the deleted data.
• F. It retains the integrity constraints defined on the table.

Answer: A,F

 

NEW QUESTION 192
View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best answer.)

The PROJ_TASK_DETAILS table stores information about project tasks and the relation between them.
The BASED_ON column indicates dependencies between tasks.
Some tasks do not depend on the completion of other tasks.
You must generate a report listing all task IDs, the task ID of any task upon which it depends and the name of the employee in charge of the task upon which it depends.
Which query would give the required result?

• A. SELECT p.task_id, p.based_on, d.task_in_charge
  FROM proj_task_details p LEFT OUTER JOIN proj_task_details d
  ON (p.based_on = d.task_id);
• B. SELECT p.task_id, p.based_on, d.task_in_charge
  FROM proj_task_details p FULL OUTER JOIN proj_task_details d
  ON (p.based_on = d.task_id);
• C. SELECT p.task_id, p.based_on, d.task_in_charge
  FROM proj_task_details p JOIN proj_task_details d
  ON (p.based_on = d.task_id);
• D. SELECT p.task_id, p.based_on, d.task_in_charge
  FROM proj_task_details p JOIN proj_task_details d
  ON (p.task_id = d.task_id);

Answer: A

Explanation:
Explanation/Reference:

 

NEW QUESTION 193
The customers table has the following structure:

You need to write a query that does the following tasks:
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customers whose income level has a value should be considered.
3. Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?

• A. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE (cust_income_level, tax_amount) IS NOT NULL;
• B. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE cust_income_level <> NULL ANDtax_amount <> NULL;
• C. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE cust_income_level IS NOT NULL ANDcust_credit_limit IS NOT NULL;
• D. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE cust_income_level IS NOT NULL ANDtax_amount IS NOT NULL;

Answer: C

 

NEW QUESTION 194
Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION?

• A. Column positions must be used in the ORDER BY clause.
• B. Only column names from the first SELECT statement in the compound query are recognized.
• C. Each SELECT statement in the compound query must have its own ORDER BY clause.
• D. Each SELECT statement in the compound query can have its own ORDER BY clause.
• E. The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause.

Answer: B,D

 

NEW QUESTION 195
Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY.
Only the EMPLOYEES_ID column is indexed.
Rows exist for employees 100 and 200.
Examine this statement:
UPDATE employees
SET (job_id, salary) =
(SELECT job_id, salary
FROM employees
WHERE employee_id = 200)
WHERE employee id=100;
Which two statements are true?

• A. Employees 100 and 200 will have the same JOB ID as before the update command.
• B. Employee 100 will have SALARY set to the same value as the SALARY of employee 200.
• C. Employee 200 will have SALARY set to the same value as the SALARY of employee 100.
• D. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100
• E. Employees 100 and 200 will have the same SALARY as before the update command.
• F. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200.

Answer: B,F

 

NEW QUESTION 196
View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.
ORDERS_MASTER
ORDER_ID
ORDER_TOTAL
1
1000
2
2000
3
3000
4
MONTHLY_ORDERS
ORDER_ID
ORDER_TOTAL
2
2500
3
Evaluate the following MERGE statement:
MERGE_INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total)
What would be the outcome of the above statement?

• A. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.
• B. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.
• C. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.
• D. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.

Answer: C

Explanation:
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

 

NEW QUESTION 197
Which two statements are true regarding the DELETEand TRUNCATEcommands? (Choose two.)

• A. DELETEcan be used to remove rows only for tables that are parents for a child table that has a referential integrity constraint referring to the parent.
• B. DELETEcan be used to remove data from specific columns as well as complete rows.
• C. DELETEcan be used to remove rows from only one table in one statement.
• D. DELETEcan be used to remove rows from multiple tables in one statement.
• E. DELETEand TRUNCATEcan be used for tables that are parents for a child table that has a referential integrity constraint having an ON DELETErule.

Answer: C,E

 

NEW QUESTION 198
......

New (2021) Oracle 1z1-071  Exam Dumps: https://www.braindumpsit.com/1z1-071_real-exam.html

Updated 1z1-071  Exam Dumps - PDF Questions and Testing Engine: https://drive.google.com/open?id=1FiLuBkUTdKaow0U89_Ak3Qx0x22pcLe0