[Sep 29, 2021] Get Free Updates Up to 365 days On Developing 1z0-071 Braindumps
Best Quality Oracle 1z0-071 Exam Questions
NEW QUESTION 49
Which three statements are true regarding subqueries? (Choose three.)
- A. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.
- B. A subquery can be used in the FROM clause of a SELECT statement.
- C. The ORDER BY Clause can be used in a subquery.
- D. If a subquery returns NULL, the main query may still return rows.
- E. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
Answer: B,C,D
Explanation:
Explanation/Reference:
NEW QUESTION 50
Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.)
- A. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
- B. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
- C. Delete statements can always be done on a table through a view.
- D. Insert statements can always be done on a table through a view.
- E. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
- F. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.
Answer: A,C,D
NEW QUESTION 51
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?
- A. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.
- B. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
- C. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
- D. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
Answer: B
Explanation:
Explanation
References:
http://psoug.org/definition/WHEN.htm
NEW QUESTION 52
Which three statements are true regarding group functions? (Choose three.)
- A. They can be used with a SQL statement that has a GROUP BY clause.
- B. They can be used only on one column in the SELECT clause.
- C. They can be used together with the single-row functions in the SELECT clause.
- D. They can be used on columns or expressions.
- E. They can be passed as an argument to another group function.
Answer: C,D,E
Explanation:
References:
https://www.safaribooksonline.com/library/view/mastering-oracle-sql/0596006322/ch04.html
NEW QUESTION 53
View the exhibit and examine the ORDERS table.
ORDERS
Name
Null?
Type
ORDER ID
NOT NULL
NUMBER(4)
ORDATE DATE
DATE
CUSTOMER ID
NUMBER(3)
ORDER TOTAL
NUMBER(7,2)
The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?
- A. ALTER TABLE ordersADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
- B. ALTER TABLE ordersMODIFY CONSTRAINT orders_cust_id_nn NOT NULL
(customer_id); - C. ALTER TABLE ordersADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;
- D. ALTER TABLE ordersMODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);
Answer: D
NEW QUESTION 54
Which statement is true regarding external tables?
- A. The data and metadata for an external table are stored outside the database.
- 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 CREATE TABLE AS SELECT statement can be used to upload data into regular table in the database from an external table.
Answer: D
Explanation:
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm
NEW QUESTION 55
You want to display the date for the first Monday of the next month and issue the following command:
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 rrrrshould be replaced by rrin the format string.
- D. In generates an error because fmand double quotation marks should not be used in the format string.
- E. In generates an error because TO_CHARshould be replaced with TO_DATE.
Answer: A
NEW QUESTION 56
View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)
Examine this SQL statement:
SELECT department_id "DEPT_ID", department_name, 'b' FROM
departments
WHERE departments_id=90
UNION
SELECT department_id, department_name DEPT_NAME, 'a' FROM
departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output?
- A. ORDER BY 3;
- B. ORDER BY DEPT_ID;
- C. ORDER BY 'b';
- D. ORDER BY DEPT_NAME;
Answer: A,B
NEW QUESTION 57
Examine the following query:
SQL> SELECT prod_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY;
What is the output of this query?
- A. It displays 5 percent of the products with the highest amount sold.
- B. It displays the first 5 percent of the rows from the SALES table.
- C. It results in an error because the ORDER BY clause should be the last clause.
- D. It displays 5 percent of the products with the lowest amount sold.
Answer: D
Explanation:
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
NEW QUESTION 58
View the Exhibit and examine the data in ORDERS_MASTER and MONTHLYjDRDERS 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?
- A. The ORDERS_MASTER table would contain the ORDERJDs 1,2 and 3.
- B. The ORDERS MASTER table would contain the ORDER IDs 1,2,3 and 4.
- C. The ORDERS_MASTER table would contain the ORDERJDs 1,2 and 4.
- D. The ORDERS_MASTER table would contain the ORDERJDs 1 and 2.
Answer: C
NEW QUESTION 59
Which statement is true about the Oracle SQL, DELETE and TRUNCATE statements?
- A. DELETE but not TRUNCATE statement can be used to selectively remove rows from a table.
- B. DELTE and TRUNCATE statements can have a rollback done to restore data into a table.
- C. DELETE and TRUNCATE statements remove all indexes for the tables on which they are performed.
- D. DELETE but not TRUNCATE statement can be used to remove data from selective columns and rows of a table.
Answer: A
NEW QUESTION 60
You must create a SALES table with these column specifications and data types:
SALESID: Number
STOREID: Number
ITEMID: Number
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified Which statement would create the table? (Choose the best answer.)
- A. CREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT 1,slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT 'CASH');
- B. CREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT = 1,slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT = "CASH");
- C. CREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT = 1,slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT = "CASH");
- D. CREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT 1,slsdate DATE DEFAULT 'SYSDATE',payment VARCHAR2(30) DEFAULT CASH);
Answer: A
NEW QUESTION 61
Which three actions can you perform only with system privileges?
- A. Query any table in a database.
- B. Use the WITH GRANT OPTION clause.
- C. Truncate a table in another schema.
- D. Execute a procedure in another schema.
- E. Log in to a database.
- F. Access flat files via a database, which are stored in an operating system directory.
Answer: A,D,E
NEW QUESTION 62
Examine the structure of the ORDERS table: (Choose the best answer.)
You want to find the total value of all the orders for each year and issue this command:
SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders
GROUP BY TO_CHAR(order_date, 'yyyy');
Which statement is true regarding the result?
- A. It executes successfully but does not give the correct output.
- B. It executes successfully but gives the correct output.
- C. It returns an error because the TO_CHAR function is not valid.
- D. It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
Answer: D
NEW QUESTION 63
Examine the description of the TRANSACTIONS table:
Which two SQL statements execute successfully?
- A. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100 DUES FROM transactions;
- B. SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 "DUES AMOUNT" FROM transactions;
- C. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount +100 DUES FROM transactions;
- D. SELECT customer_id AS 'CUSTOMER-ID', transaction_date AS DATE, amount + 100 'DUES' FROM transactions;
- E. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount + 100 "DUES" FROM transactions;
Answer: A,C
NEW QUESTION 64
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:
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?
- A. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c.department_id);
- B. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c.department_id);
- C. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d.department_id);
- D. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d.department_id);
Answer: A
NEW QUESTION 65
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
FROM product_information
WHERE UPPER(product_name) IN ('LASERPRO', 'CABLE'); - B. SELECT UPPER(product_name)
FROM product_information
WHERE product_id = 2254; - C. SELECT product_id, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name) = 'LASERPRO' OR list_price > 1000; - D. SELECT UPPER(product_name)
FROM product_information;
Answer: A
NEW QUESTION 66
Which two are true about multitable insert statements?
- A. They can transform a row from a source table Into multiple rows In a target table.
- B. The conditional insert first statement always Inserts a row Into a single table.
- C. The conditional insert all statement inserts rows into a single table by aggregating source rows.
- D. They always use subqueries.
- E. The unconditional insert all statement must have the same number of columns In both the source and target tables.
Answer: A,D
NEW QUESTION 67
View the Exhibit and examine the structures of the employees and departments tables.
You must update the employees table according to these requirements::
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department id corresponding to London (locationid 2100).
-Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission In location_id 2100 to 1.5 times the average commission of their department.
You issue this command:
What is the result?
- A. It executes successfully but does not produce the desired update.
- B. It executes successfully and produces the desired update.
- C. It generates an error because a subquery cannot have a join condition in an update statement.
- D. It generates an error because multiple columns cannot be specified together in an UPDATE statement.
Answer: A
NEW QUESTION 68
Which three queries execute successfully?
- A. SELECT 1 / SYSDATE - DATE '2019-01-01' FROM DUAL;
- B. SELECT SYSDATE - 1 - DATE'2019-01-01' EROM DUAL;
- C. SELECT SYSDATE / DATE '2019-01-01' - 1 FROM DUAL
- D. SELECT (SYSDATE-DATE '2019-01-01') / 1 FROM DUAL;
- E. SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL;
- F. SELECT 1 - SYSDATE- DATE '2019-01-01' FROM DUAL;
Answer: B,D,E
NEW QUESTION 69
View the Exhibit and examine the structures of the employees and departments tables.
You must update the employees table according to these requirements::
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department id corresponding to London (locationid 2100).
-Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission In location_id 2100 to 1.5 times the average commission of their department.
You issue this command:
What is the result?
- A. It executes successfully but does not produce the desired update.
- B. It executes successfully and produces the desired update.
- C. It generates an error because a subquery cannot have a join condition in an update statement.
- D. It generates an error because multiple columns cannot be specified together in an UPDATE statement.
Answer: A
NEW QUESTION 70
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit IN (1000, 2000, 3000);
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit = 1000 or cust_credit_limit = 2000 or
cust_credit_limit = 3000
Which statement is true regarding the above two queries?
- A. Performance would improve in query 2.
- B. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.
- C. There would be no change in performance.
- D. Performance would degrade in query 2.
Answer: C
Explanation:
Explanation/Reference:
References:
http://oraclexpert.com/restricting-and-sorting-data/
NEW QUESTION 71
Which three statements are correct regarding indexes? (Choose three.)
- A. Indexes should be created on columns that are frequently referenced as part of any expression.
- B. For each DML operation performed on a table, the corresponding indexes are automatically updated if required.
- C. A non-deferrable PRIMARY KEYor UNIQUE KEYconstraint in a table automatically attempts to create a unique index.
- D. When a table is dropped, corresponding indexes are automatically dropped.
Answer: B,C,D
Explanation:
Explanation/Reference:
References:
http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/
NEW QUESTION 72
......
Oracle Exam Practice Test To Gain Brilliante Result: https://www.dumpsmaterials.com/1z0-071-real-torrent.html
Tested Material Used To 1z0-071: https://drive.google.com/open?id=1h9tlQeFNO6JP3q4Kc3e9BsWrFPyc5X6b
