1z0-071 Dumps PDF 2023 Strategy Your Preparation Efficiently [Q36-Q60]

Rate this post

1z0-071 Dumps PDF 2023 Strategy Your Preparation Efficiently

Latest Verified & Correct Oracle 1z0-071 Questions

QUESTION 36
View the Exhibit and examine the structure of the PROMOTIONS table.

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

It produces an error because subqueries cannot be used with the CASE expression.

It shows COST_REMARK for all the promos in the promo category ‘TV’.

It shows COST_REMARK for all the promos in the table.

It produces an error because the subquery gives an error.

QUESTION 37
Which three actions can you perform by using the ORACLE DATAPUMP access driver?

Create a directory object for an external table.

Read data from an external table and load it into a table in the database.

Query data from an external table.

Create a directory object for a flat file.

Execute DML statements on an external table.

Read data from a table in the database and insert it into an external table.

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

They can be used on columns or expressions.

They can be passed as an argument to another group function.

They can be used with a SQL statement that has a GROUP BY clause.

They can be used only on one column in the SELECT clause.

They can be used together with the single-row functions in the SELECT clause.

QUESTION 39
View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.

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?

The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.

The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.

The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.

The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.

QUESTION 40
Examine the SQL statement used to create the TRANSACTION table. (Choose the best answer.) SQL > CREATE TABLE transaction (trn_id char(2) primary key, Start_date date DEFAULT SYSDATE, End_date date NOT NULL); The value ‘A1’ does not exist for trn_id in this table.
Which SQL statement successfully inserts a row into the table with the default value for START_DATE?

INSERT INTO transaction VALUES (‘A1’, DEFAULT, TO_DATE(DEFAULT+10))

INSERT INTO transaction VALUES (‘A1’, DEFAULT, TO_DATE(‘SYSDATE+10’))

INSERT INTO transaction (trn_id, end_date) VALUES (‘A1′, ’10-DEC-2014’)

INSERT INTO transaction (trn_id, start_date, end_date) VALUES (‘A1′, , ’10-DEC-2014’)

QUESTION 41
Examine the description of the transactions table:
Which two SQL statements execute successfully?

SELECT customer_id AS “CUSTOMER-ID”, transaction_date AS DATE, amount+100 “DUES” from transactions;

SELECT customer_id AS ‘CUSTOMER-ID’,transaction_date AS DATE, amount+100 ‘DUES’ from transactions;

SELECT customer_id CUSTID, transaction_date TRANS_DATE,amount+100 DUES FROM transactions;

SELECT customer_id AS “CUSTOMER-ID”, transaction_date AS “DATE”, amount+100 DUES FROM transactions;

SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 “DUES AMOUNT” FROM transactions;

QUESTION 42
Which statement is true regarding the INTERSECT operator?

It ignores NULL values

The number of columns and data types must be identical for all SELECT statements in the query

The names of columns in all SELECT statements must be identical

Reversing the order of the intersected tables the result

QUESTION 43
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?

Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.

Enclose the literal character string in the SELECTclause within the double quotation marks.

Do not enclose the character literal string in the SELECTclause within the single quotation marks.

Use escape character to negate the single quotation mark inside the literal character string in the SELECTclause.

QUESTION 44
You need to display the first names of all customers from the CUSTOMERS table that contain the character
‘e’ and have the character ‘a’ in the second last position.
Which query would give the required output?

SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, ‘e’)<>0 ANDSUBSTR(cust_first_name, -2, 1)=’a’;

SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, ‘e’)<>” ANDSUBSTR(cust_first_name, -2, 1)=’a’;

SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, ‘e’)IS NOT NULL ANDSUBSTR(cust_first_name, 1, -2)=’a’;

SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, ‘e’)<>0 ANDSUBSTR(cust_first_name, LENGTH(cust_first_name), -2)=’a’;

QUESTION 45
Which two statements are true regarding the EXISTSoperator used in the correlated subqueries?
(Choose two.)

The outer query stops evaluating the result set of the inner query when the first value is found.

It is used to test whether the values retrieved by the inner query exist in the result of the outer query.

It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.

The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

QUESTION 46
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?

Employees 100 and 200 will have the same SALARY as before the update command.

Employee 100 will have SALARY set to the same value as the SALARY of employee 200.

Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200.

Employees 100 and 200 will have the same JOB ID as before the update command.

Employee 200 will have SALARY set to the same value as the SALARY of employee 100.

Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100

QUESTION 47
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_idFROM order_itemsGROUP BY order_idHAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity))FROM order_items GROUP BY order_id);

SELECT order_idFROM order_itemsWHERE(unit_price*quantity) = (SELECT
MAX(unit_price*quantity)FROM order_items)GROUP BY order_id;

SELECT order_idFROM order_itemsWHERE(unit_price*quantity) =
MAX(unit_price*quantity)GROUP BY order_id;

SELECT order_idFROM order_itemsWHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_itemsGROUP BY order_id)

QUESTION 48
The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues this GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What must be done to fix the statement?

PUBLIC should be replaced with specific usernames.

ALL should be replaced with a list of specific privileges.

WITH GRANT OPTION should be added to the statement.

Separate GRANT statements are required for the ORDERS and ORDER_ITEMS tables.

QUESTION 49
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:

You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses.
Which SQL statement must you use?

SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)

SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN
.department_details d ON (c.depatrment_id=d.department_id)

SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=d. department_id)

SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>d. department_id)

QUESTION 50
Evaluate the following SQL commands:

The command to create a table fails. Identify the reason for the SQL statement failure.

You cannot use ORD_NOand ITEM_NOcolumns as a composite primary key because ORD_NOis also the FOREIGN KEY.

You cannot use the BETWEENclause in the condition of a CHECKconstraint.

You cannot use the NEXTVALsequence value as a DEFAULTvalue for a column.

You cannot use SYSDATEin the condition of a CHECKconstraint.

QUESTION 51
View the exhibit and examine the structure of the EMPLOYEEStable.

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_NAMEof the managers and the second column would have LAST_NAMEof the employees.
Which SQL statement would you execute?

SELECT m.last_name “Manager”, e.last_name “Employee”
FROM employees m JOIN employees e
ON 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.manager_id = 100;

SELECT m.last_name “Manager”, e.last_name “Employee”
FROM employees m JOIN employees e
ON 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 e
WHERE m.employee_id = e.manager_id and AND e.manager_id = 100

QUESTION 52
View the exhibits and examine the structures of the COSTSand PROMOTIONStables.

Evaluate the following SQL statement:
SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions
WHERE promo_cost < ALL
( SELECT MAX(promo_cost) FROM promotions
GROUP BY (promo_end_date-
promo_begin_date)));
What would be the outcome of the above SQL statement?

It displays prod IDs in the promo with the lowest cost.

It displays prod IDs in the promos with the lowest cost in the same time interval.

It displays prod IDs in the promos with the highest cost in the same time interval.

It displays prod IDs in the promos which cost less than the highest cost in the same time interval.

QUESTION 53
Examine the structure of the MEMBERS table: (Choose the best answer.)

Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC; What would be the result execution?

It displays all cities in descending order, within which the last names are further sorted in descending order.

It fails because a column alias cannot be used in the ORDER BY clause.

It fails because a column number and a column alias cannot be used together in the ORDER BY clause.

It displays all cities in ascending order, within which the last names are further sorted in descending order.

QUESTION 54
Evaluate these commands which execute successfully CREATE SEQUENCE ord_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 100000
CYCLE
CACHE 5000;
Create table ord_items(
ord_no number(4) default ord_seq.nextval not null,
Item_no number(3),
Qty number(3),
Expiry_date date,
Constraint it_pk primary key(ord_no,item_no),
Constraint ord_fk foreign key (ord_no) references orders(ord_no));
Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?

Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.

Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.

Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times

IF sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.

Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.

QUESTION 55
View the Exhibit and examine the structure of the CUSTOMERS table.

You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10,000.
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE ‘A%’ OR
UPPER (cust_last_name) LIKE ‘B%’ OR UPPER (cust_last_name) LIKE ‘C%’)
AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER (cust_last_name) BETWEEN ‘A’ AND ‘C’
AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?

Only the second query gives the correct result

Both execute successfully but do not give the required result

Only the first query gives the correct result

Both execute successfully and give the same result

QUESTION 56
You need to display the date 11-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’.
Which SQL statement would give the required result?

SELECT TO_CHAR (TO_DATE (’11-oct-2007′), ‘fmDdthsp “of” Month, Year’) FROM DUAL

SELECT TO_CHAR (’11-oct-2007′, ‘fmDdspth “of” Month, Year’)
FROM DUAL

SELECT TO_CHAR (TO_DATE (’11-oct-2007′), ‘fmDdspth of month, year’)
FROM DUAL

SELECT TO_DATE (TO_CHAR (’11-oct-2007′), ‘fmDdspth “of” Month, Year’)) FROM DUAL

QUESTION 57
Which two statements are true about Data Manipulation Language (DML) statements? (Choose two.)

An INSERT INTO…VALUES.. statement can add multiple rows per execution to a table.

An UPDATE… SET… statement can modify multiple rows based on multiple conditions on a table.

A DELETE FROM….. statement can remove rows based on only a single condition on a table.

An INSERT INTO… VALUES….. statement can add a single row based on multiple conditions on a table.

A DELETE FROM….. statement can remove multiple rows based on multiple conditions on a table.

An UPDATE….SET…. statement can modify multiple rows based on only a single condition on a table.

QUESTION 58
Examine these SQL statements which execute successfully:

Which two statements are true after execution? (Choose two.)

The foreign key constraint will be disabled.

The primary key constraint will be enabled and DEFERRED.

The foreign key constraint will be enabled and DEFERRED.

The foreign key constraint will be enabled and IMMEDIATE.

The primary key constraint will be enabled and IMMEDIATE.

QUESTION 59
Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

A sequence can issue duplicate values

A sequence’s unallocated cached value are lost if the instance shuts down

Sequences can always have gaps

Two or more tables cannot have keys generated from the same sequence

A sequence can only be dropped by a DBA

A sequence number that was allocated can be rolled back if a transaction fails

QUESTION 60
You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and LOGIN_ID must be unique and not null.
Which two SQL statements would create the required table? (Choose two.)

CREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(100),hire_date DATE,CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id));

CREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(25),hire_date DATE,CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));

CREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY,login_id NUMBER UNIQUE,employee_name VARCHAR2(25),hire_date DATE);

CREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(100),hire_date DATE,CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id);CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id));

CREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_nn NOT NULL,login_id NUMBER CONSTRAINT login_id_nn NOT NULL,employee_name VARCHAR2(100),hire_date DATE,CONSTRAINT emp_num_id_uk UNIQUE (employee_id, login_id));

1z0-071 PDF Dumps Are Helpful To produce Your Dreams Correct QA’s: https://www.free4dump.com/1z0-071-braindumps-torrent.html

You may also like

Leave a Reply Cancel reply