[2021] Pass 1z0-071 Exam - Real Questions & Answers
1z0-071 Exam Questions Get Updated [2021] with Correct Answers
What is the duration, language, and format of Oracle 1Z0-071: Oracle Database SQL Exam
- This exam is only offered in English.
- This exam consists of 78 questions.
- Certification Validity period : 3 years.
Certifications from the Oracle database include frequent recertification. To keep your certificate valid, you must upgrade to the latest version within 1 year of the revocation of Oracle’s credentials.
NEW QUESTION 113
Examine the description of the PRODUCT_INFORMATION table:
Which query retrieves the number of products with a null list price?
- A. SELECT COUNT (list_price) FROM product_information WHERE list_price = NULL;
- B. SELECT COUNT(NVL(list_price, 0)) FROM product_information WHERE list_price IS NULL;
- C. SELECT COUNT (list_price) FROM product_information WHERE list_price IS NULL;
- D. SELECT COUNT (DISTINCT list_price) FROM product_information WHERE list_price IS NULL;
Answer: B
NEW QUESTION 114
Which three statements are true about views in an Oracle database?
- A. Views can be updated without the need to re-grant privileges on the view.
- B. The WITH CHECK clause prevents certain rows from being displayed when querying the view.
- C. Date Manipulation Language (DML) can always be used on views.
- D. Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error.
- E. Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error.
- F. Tables in the defining query of a view must always exist in order to create the view.
- G. The WITH CHECK clause prevents certain rows from being updated or inserted.
Answer: A,E,G
NEW QUESTION 115
* MANAGER is an existing role with no privileges or roles.
* EMP is an existing role containing the CREATE TABLEprivilege.
* EMPLOYEES is an existing table in the HR schema.
Which two commands execute successfully? (Choose two.)
- A. GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager;
- B. GRANT CREATE TABLE, SELECT ON hr.employees TO manager;
- C. GRANT CREATE SEQUENCE TO manager, emp;
- D. GRANT CREATE TABLE, emp TP manager;
- E. GRANT SELECT, INSERT ON hr.employees TO manager WITH GRANT OPTION;
Answer: C,E
NEW QUESTION 116
View the Exhibit and examine the structure of the PRODUCT_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 product_id, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name) = 'LASERPRO' OR list_price > 1000; - B. SELECT UPPER(product_name)
FROM product_information; - C. SELECT UPPER(product_name)
FROM product_information
WHERE product_id = 2254; - D. SELECT product_id
FROM product_information
WHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');
Answer: D
NEW QUESTION 117
Examine the description of the PRODUCT_INFORMATION table:
Which query retrieves the number of products with a null list price?
- A. SELECT COUNT (list_price) FROM product_information WHERE list_price = NULL;
- B. SELECT COUNT(NVL(list_price, 0)) FROM product_information WHERE list_price IS NULL;
- C. SELECT COUNT (list_price) FROM product_information WHERE list_price IS NULL;
- D. SELECT COUNT (DISTINCT list_price) FROM product_information WHERE list_price IS NULL;
Answer: B
NEW QUESTION 118
Which statement is true regarding external tables?
- A. The CREATE TABLE AS SELECT statement can be used to upload data into regular table in the database from an external table.
- B. The default REJECT LIMIT for external tables is UNLIMITED.
- C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
- D. The data and metadata for an external table are stored outside the database.
Answer: A
Explanation:
Explanation
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm
NEW QUESTION 119
View the Exhibit and examine the structure of the BOOKS table.
The BOOKS table contains details of 100 books.
Examine the commands executed and their outcome:
Which statement is true?
- A. Both ROLLBACK commands restore the 101 rows that were deleted.
- B. Both ROLLBACK commands restore the 100 rows that were deleted.
- C. The first rollback restores the 101 rows that were deleted and the second rollback causes the row was inserted to be deleted and commits the changes.
- D. The first rollback restores the 100 rows that were deleted and the second rollback commits only the changes.
Answer: C
NEW QUESTION 120
View the Exhibit and examine the structure of the PROMOTION table.
You have to generate a report that displays the promo named start data for all promos that started after that last promo in the 'INTTERNET' category.
- A. SELECT promo-name, promo-being_date FROM promotion
WHERE promo-being-date IN (SELECT promo_biing_date
FROM promotions
WHERE promo_category='INTYERNET'); - B. SELECT promo_neme, promo_being_date FROM promotions
WHERE promo_being_date > All (SELECT promo_beinjg-date
FROM promotions
WHERE promo_category ='INTERNET' ); - C. Select promo_name, promo_being_date FROM promoptions
WHERE promo_being_data > ANY (SELCT promo_being-date
FROM promotions
WHERE promo_category = 'INTERNET' - D. SELECT promo-name, promo-being _date FROM promotions
Where promo_being_data >ALL (SELECT MAX (promo_being-date)
FROM promotions ) AND
Promo-category ='INTERNET';
Answer: B
NEW QUESTION 121
View the exhibit and examine the data in ORDERS_MASTERand MONTHLY_ORDERStables.
Evaluate the following MERGEstatement:
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_MASTERtable would contain the ORDER_IDs1 and 2.
- B. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2, 3 and 4.
- C. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 4.
- D. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 3.
Answer: C
Explanation:
Explanation/Reference:
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
NEW QUESTION 122
View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)
You executed this SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the result? (Choose two.)
- A. The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
- B. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
- C. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
- D. The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
- E. The values in all columns would be returned in descending order.
Answer: B,D
NEW QUESTION 123
View the exhibit and examine the structure of ORDERSand CUSTOMERStables.
Which INSERT statement should be used to add a row into the ORDERStable for the customer whose CUST_LAST_NAMEis Robertsand CREDIT_LIMITis 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.
INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id,
- A. VALUES (1,'10-mar-2007', 'direct',
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
INSERT INTO orders (order_id, order_date, order_mode, - B. (SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), order_total)
VALUES (1,'10-mar-2007', 'direct', &&customer_id, 1000); - C. o.order_total
FROM orders o, customers c
WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND
c.credit_limit=600)
VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
INSERT INTO orders (order_id, order_date, order_mode, - D. (SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), order_total)
VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);
INSERT INTO orders
Answer: A
NEW QUESTION 124
Examine the description of the TRANSACTIONStable:
Which two SQL statements execute successfully?
SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount + 100
- A. 'DUES' FROM transactions;
- B. DUES FROM transactions;
SELECT customer_id AS 'CUSTOMER-ID', transaction_date AS DATE, amount + 100 - C. 100 "DUES AMOUNT" FROM transactions;
SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount +100 DUES FROM - D. "DUES" FROM transactions;
SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + - E. transactions;
SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100
Answer: B,E
NEW QUESTION 125
Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
( emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
ename VARCHAR2(15),
salary NUMBER (8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no));
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_PK?
- A. It would be automatically enabled and deferred.
- B. It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.
- C. It would be automatically enabled and immediate.
- D. It would remain disabled and has to be enabled manually using the ALTER TABLE command.
Answer: D
NEW QUESTION 126
Which two will execute successfully?
- A. SELECT NVL('DATE',200) FROM (SELECT NULL AS "DATE" FROM DUAL);
- B. SELECT COALESCE('DATE',SYSDATE) FROM (SELECT NULL AS "DATE" FROM DUAL) ;
- C. SELECT NVL('DATE',SYSDATE) FROM DUAL;
- D. SELECT COALESCR('DATE', SYSDATE) FROM DUAL;
- E. SELECT COALESCE(O,SYSDATE) TRCH DUAL;
Answer: A,C
NEW QUESTION 127
Which three statements are true regarding the usage of the WITH clause in complex correlated subqueries:
(Choose three.)
- A. 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
- B. If the query block name and the table name are the same, then the table name takes precedence.
- C. The WITH clause can hold more than one query.
- D. It can be used only with the SELECT clause.
Answer: A,C,D
NEW QUESTION 128
......
Practice 1z0-071 Questions With Certification guide Q&A from Training Expert Test4Engine: https://www.test4engine.com/1z0-071_exam-latest-braindumps.html
Free Oracle 1z0-071 Test Practice Test Questions Exam Dumps: https://drive.google.com/open?id=1H46L7SQ-sm-fTsGEIDYF4-3_PGR9Op0o