24 May, 2023

What Download 1Z0-071 Prep Is

100% Guarantee of 1Z0-071 testing engine materials and practice exam for Oracle certification for customers, Real Success Guaranteed with Updated 1Z0-071 pdf dumps vce Materials. 100% PASS Oracle Database 12c SQL exam Today!

Online 1Z0-071 free questions and answers of New Version:

Page: 1 / 15

Total 187 questions Full Exam Access

Question 1

You must write a query that prompts users for column names and conditions every time it is executed. (Choose the best answer.)
The user must be prompted only once for the table name. Which statement achieves those objectives?

SELECT &col1, '&col2'FROM &tableWHERE &&condition = '&cond';

SELECT &col1, &col2 FROM "&table"WHERE &condition =&cond;

SELECT &col1, &col2 FROM &&tableWHERE &condition = &cond;

SELECT &col1, &col2 FROM &&tableWHERE &condition = &&cond

Question 2

Which three statements are true regarding subqueries? (Choose three.)

The ORDER BY Clause can be used in a subquery.

A subquery can be used in the FROM clause of a SELECT statement.

If a subquery returns NULL, the main query may still return rows.

A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.

Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

Question 3

Examine the commands used to create the DEPARTMENT_DETAILS and the COURSE-DETAILS tables: SQL> CREATE TABLE DEPARTMfiNT_D£TAILS
DEPARTMENT_ID NUMBER PRIMARY KEY , DEPARTMEHT_NAME VARCHAR2(50) ,
HOD VARCHAP2(50));
SQL> CREATE TABLE COURSE-DETAILS (COURSE ID NUMBER PRIMARY KEY , COURS_NAME VARCHAR2 (50) ,
DEPARTMEHT_ID NUMBER REFERENCES DEPARTMENT_DETAIL
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?

SELECT d.departranc_id, c.cours«_id FROM cource_deatils c LEFT OUTER JOIN departmnt_details d ON (c.dapartmsnt_id=d.departtnent_id);

SELECT d.department_id,

course_id FROM dapartment_details d RIGHT OUTER JOIN course_dotails c ON (c.depattnient_id=d.department_id) ;

SELECT d.department i

ccours_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id);

SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department id).- (DEPARTMENT_ID) ;

Question 4

Evaluate the following statement. INSERT ALL
WHEN order_total < 10000 THEN INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders
WHEN order_total > 200000 AND order_total < 20000 THEN INTO large_orders
SELECT order_id, order_total, customer_id FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

They are evaluated by the first WHEN claus

If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

They are evaluated by the first WHEN claus

If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.

The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.

Question 5

Examine the command:
SQL> ALTER TABLE books_transactions
ADD CONSTRAINT fk_book_id FOREIGN KEY (book_id) REFERENCES books (book_id) ON DELETE CASCADE; What does ON DELETE CASCADE imply?

When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.

When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted butthe table structure is retained.

When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.

When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.

Question 6

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?

SELECT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.

SELECT DISTINCT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.

SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.

SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers

Question 7

Examine this SELECT statement and view the Exhibit to see its output: (Choose two.)
1Z0-071 dumps exhibit

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

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.

The R_CONSTRAINT_NAME column contains an alternative name for the constraint.

In the second column, 'c' indicates a check constraint.

The STATUS column indicates whether the table is currently in use.

Question 8

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

All constraints can be defined at the column level and at the table level.

A constraint can be disabled even if the constraint column contains data.

A column with the UNIQUE constraint can contain NULLS.

A foreign key column cannot contain NULLS.

A constraint is enforced only for INSERT operations.

Question 9

Which two statements are true about sequences crated in a single instance Oracle database?

The numbers generated by an explicitly defined sequence can only be used to insert data in one table.

DELETE <sequencename> would remove a sequence from the database.

CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.

When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.

When the database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.

Question 10

Which statement is true regarding external tables?

The CREATE TABLE AS SELECT statement can be used to upload data into regular table in the database from an external table.

The data and metadata for an external table are stored outside the database.

The default REJECT LIMIT for external tables is UNLIMITED.

ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.

Question 11

Examine the structure of the BOOKS_TRANSACTIONS table:
1Z0-071 dumps exhibit

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

SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS

SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS

SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;

SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS

Question 12

Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)

During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.

During executing, the oracle server may read data from storage if the required data is not already in memory.

During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.

During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

Question 13

View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.
1Z0-071 dumps exhibit

The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS table.
The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Examine this command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id FROM sales;
Which statement is true?

The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.

The NEW_SALES table would get created and all the NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

Question 14

View the exhibit and examine the structure of the PROMOTIONS table.
1Z0-071 dumps exhibit

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the ‘INTERNET’ category.
Which query would give you the required output?

SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions) ANDpromo_category= ‘INTERNET’;

SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);

SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = ‘INTERNET’);

SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);

Question 15

View the Exhibit and examine the structure of the EMPLOYEES table.
You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.
1Z0-071 dumps exhibit

Which SQL statement would you execute?

SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees eON m.employee_id = e.manager_id WHERE m.manager_id=100;

SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE e.managerjd=100;

SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees eON e.employee_id = m.manager_id WHERE m.manager_id=100;

SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees eWHERE m.employee_id = e.manager_id AND e.managerjd=100;

Question 16

Which three statements are true regarding the usage of the WITH clause in complex correlated subqueries: (Choose three.)

It can be used only with the SELECT clause.

The WITH clause can hold more than one query.

If the query block name and the table name are the same, then the table name takes precedence.

The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block

Question 17

Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)

The nested query executes after the outer query returns the row.

The nested query executes first and then the outer query executes.

The outer query executes only once for the result returned by the inner query.

Each row returned by the outer query is evaluated for the results returned by the inner query.

Question 18

View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables. (Choose all that apply.)
1Z0-071 dumps exhibit

Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.
SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE JOB_ID = 'SA_MAN'
------------------------------------- SELECT EMPLOYEE_ID FROM JOB_HISTORY WHERE JOB_ID = 'SA_MAN';
Choose two correct SET operators which would cause the query to return the desired result.

UNION

MINUS

INTERSECT

UNION ALL

Question 19

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. (Choose two.)
1Z0-071 dumps exhibit

Evaluate this SQL statement: ALTER TABLE emp
DROP COLUMN first_name; Which two statements are true?

The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.

The FIRST_NAME column would be dropped provided at least one column remains in the table.

The FIRST_NAME column would be dropped provided it does not contain any data.

The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.

Question 20

Examine the structure of the EMPLOYEES table. (Choose two.)
1Z0-071 dumps exhibit

You must display the maximum and minimum salaries of employees hired 1 year ago. Which two statements would provide the correct output?

SELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);

SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE-365)GROUP BY maxsal, minsal;

SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);

SELECT MIN(Salary), MAX(salary)FROM (SELECT salary FROM employeesWHERE hire_date < SYSDATE-365);

Page: 1 / 15

Total 187 questions Full Exam Access