100% Updated Oracle 1z0-071 Enterprise PDF Dumps
Use Valid Exam 1z0-071 by FreeCram Books For Free Website
The Oracle 1Z0-071 is a certification exam that proves the candidate’s knowledge of SQL technology and SQL concepts required to participate in any database project. The target audience for this exam is the IT professionals with some experience in working with these technologies. The fundamental knowledge of general computing concepts and command line interfaces will be an advantage as well.
NEW QUESTION # 70
Which two are true about external tables that use the ORACLE _DATAPUMP access driver?
- A. Creating an external table creates a dump file that can be used only by an external table in the same database.
- B. When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks.
- C. When creating an external table, data can be selected only from a table whose rows are stored in database blocks.
- D. Creating an external table creates a dump file that can be used by an external table in the same or a different database.
- E. Creating an external table creates a directory object.
Answer: C,D
NEW QUESTION # 71
Which two are true about queries using set operators such as UNION?
- A. CHAR columns of different lengths used with a set operator retum a vAacsua mhtoe e equals the longest CHAR value.
- B. Queries using set operators do not perform implicit conversion across data type groups (e.g. character, numeric)
- C. In a query containing multiple set operators INTERSECT always takes precedence over UNION and UNION ALL
- D. All set operators are valid on columns all data types.
- E. An expression in the first SELECT list must have a column alias for the expression
Answer: A,C
Explanation:
Set operators allow you to combine multiple queries into a single query. The key points for each option are:
A). An expression in the first SELECT list does not need to have a column alias; however, the column alias given to an expression in the first SELECT list is used as the column name of the output.
B). This statement is true. When CHAR columns of different lengths are combined with a set operator, Oracle will pad the shorter values with spaces to match the length of the longest value. This behavior is consistent with the SQL standard and Oracle's treatment of the CHAR datatype.
C). Oracle Database performs implicit conversion between compatible data types where necessary when using set operators. For example, a NUMBER can be implicitly converted to a VARCHAR2 and vice versa if the context requires it.
D). This is true. When multiple set operators are used in a single query, the INTERSECT operator takes precedence over the UNION and UNION ALL operators. This order of operation can be overridden by using parentheses.
E). Not all set operators work with all data types. For instance, LOB and LONG RAW data types cannot be used with set operators.
References:
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Set Operators"
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Data Types"
NEW QUESTION # 72
Examine the description of the PRODUCT_STATUStable:
The STATUScolumn contains the values IN STOCKor OUT OF STOCKfor each row.
Which two queries will execute successfully? (Choose two.)
- A.
- B.
- C.
- D.
- E.
- F.
Answer: A,D
NEW QUESTION # 73
Sales data of a company is stored in two tables, SALES1and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1table, which are not present in the SALES2table.
SALES1 table
Name Null Type
------------------ ------------- ----------------
SALES_ID NUMBER
STORE_ID NUMBER
ITEMS_ID NUMBER
QUANTITY NUMBER
SALES_DATE DATE
SALES2 table
Name Null Type
---------------- ------------- -----------------
SALES_ID NUMBER
STORE_ID NUMBER
ITEMS_ID NUMBER
QUANTITY NUMBER
SALES_DATE DATE
Which set operator generates the required output?
- A. SUBTRACT
- B. UNION
- C. INTERSECT
- D. MINUS
- E. PLUS
Answer: D
Explanation:
Explanation/Reference:
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm
NEW QUESTION # 74
View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name, list_price
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?
- A. It would execute but would return no rows.
- B. It would not execute because the entire WHERE clause is not enclosed within parentheses.
- C. It would execute and return the desired.
- D. It would not execute because the same column has been used twice with the AND logical operator.
Answer: A
NEW QUESTION # 75
View the Exhibit and examine the structure of the PRODUCTS table. (Choose the best answer.)
You must display the category with the maximum number of items.
You issue this query:
SQL > SELECT COUNT(*), prod_category_id
FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM porducts);
What is the result?
- A. It generate an error because the subquery does not have a GROUP BY clause.
- B. It executes successfully and gives the correct output.
- C. It generates an error because = is not valid and should be replaced by the IN operator.
- D. It executes successfully but does not give the correct output.
Answer: A
NEW QUESTION # 76
Examine the structure of the INVOICE table.
Which two SQL statements would execute successfully? (Choose two.)
- A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete')FROM invoice;
- B. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)FROM invoice;
- C. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available')FROM invoice;
- D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available')FROM invoice;
Answer: A,B
NEW QUESTION # 77
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?
- A. Use the escape character to negate the single quotation mark within the literal character string in the SELECT clause
- B. Enclose the character literal string in the SELECT clause within double quotation marks
- C. Use the Oracle (q) operator and delimiter to allow the use of a single quotation mark within the literal character string in the SELECT clause
- D. Remove the single quotation marks enclosing the character literal string in the SELECT clause
Answer: C
Explanation:
Explanation
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm
NEW QUESTION # 78
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 remain disabled and has to be enabled manually using the ALTER TABLEcommand.
- 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 be automatically enabled and deferred.
Answer: A
Explanation:
Explanation/Reference:
NEW QUESTION # 79
View the Exhibit and examine the structure of the ORDERS table.
The columns ORDER_MODE and ORDER TOTAL have the default values'direct "and respectively.
Which two INSERT statements are valid? (Choose two.)
- A. INSERT INTO orders VALUES (1, '09-mar-2007', 'online',' ',1000);
- B. INSERT INTO orders (order_id, order_date, order mode,customer_id, order_total) VALUES (1, TO_DATE (NULL),'online',101, NULL) ;
- C. INSERT INTO (SELECT order_id, order date, customer_id FROM orders) VALUES (1, '09-mar-2007",101);
- D. INSERT INTO orders (order id, order_date, order mode, order_total)VALUES (1,'10-mar-2007','online', 1000)
- E. INSERT INTO orders VALUES('09-mar-2007',DEFAULT,101, DEFALLT);
Answer: C,E
NEW QUESTION # 80
Examine this list of queries:
Which two statements are true?
- A. 2 returns the value 20.
- B. 1 and 4 give different results.
- C. 1 and 4 give the same result.
- D. 2 and 3 give the same result.
- E. 3 returns an error.
Answer: A,C
NEW QUESTION # 81
Examine the description of the countries table:
Examine the description of the departments table:
Examine the description of the locations table:
Which two queries will return a list of countries with no departments?
- A.
- B.
- C.
- D.
Answer: B,C
Explanation:
The query's goal is to return a list of countries that have no departments linked to them.
Option B and Option D are the correct answers because they use set operations that will effectively return countries that do not have a corresponding entry in the departments table:
* Option B uses the NOT IN subquery to exclude countries that have departments linked to them. It looks for country_id values in the countries table that are not present in the list of country_id values associated with locations that are, in turn, associated with departments. This will correctly return countries that have no departments.
* Option D uses the MINUS set operator, which subtracts the results of the second SELECT statement from the results of the first. This statement will return all countries from the countries table minus those that have an associated department_id in the departments table, effectively listing countries with no departments.
Option A and Option C are incorrect because:
* Option A will not execute successfully as it tries to join tables using a column (country_id) that doesn't exist in the departments table, which will lead to an error.
* Option C's use of INTERSECT is incorrect for this requirement. INTERSECT returns only the rows that exist in both queries. Since we want countries with no departments, using INTERSECT would actually return the opposite of what is required.
References:
* Oracle Documentation on NOT IN clause: SQL Language Reference - Subquery
* Oracle Documentation on MINUS operator: SQL Language Reference - Set Operators Therefore, the correct options are B and D, which use subquery exclusion and the MINUS set operator, respectively, to accurately identify and return countries without departments.
NEW QUESTION # 82
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 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 JOIN proj_task_details d
ON (p.task_id = d.task_id); - C. 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); - D. 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);
Answer: D
NEW QUESTION # 83
Which two are true about granting privileges on objects? (Choose two.)
- A. The owner of an object acquires all object privileges on that object by default.
- B. The WITH GRANT OPTIONclause can be used only by DBA users.
- C. A table owner must grant the REFERENCESprivilege to allow other users to create FOREIGN KEY constraints using that table.
- D. An object privilege can be granted to a role only by the owner of that object.
- E. An object privilege can be granted to other users only by the owner of that object.
Answer: A,C
Explanation:
Explanation/Reference: https://docs.oracle.com/cd/B19306_01/network.102/b14266/authoriz.htm#i1008214
NEW QUESTION # 84
Examine the description of the PRODUCTS table:
Which two statements execute without errors?
- A. MERGE INTO new_prices n
USING products p
ON (p.prod_id =n.prod_id)
WHEN NOT MATCHED THEN
INSERT (n.prod _id, n.price) VALUES (p.prod_id, cost*.01)
WHERE (p.cost<200); - B. MERGE INTO new_prices n
USING (SELECT * FROM products) p
WHEN MATCHED THEN
UPDATE SET n.price= p.cost* 01
WHEN NOT MATCHED THEN
INSERT(n.prod_id, n.price) VALUES(p.prod_id, cost*.01)
WHERE(p.cost<200); - C. MERGE INTO new_prices n
USING (SELECT * FROM products WHERE cost>150) p
ON (n.prod_id= p.prod_id)
WHEN MATCHED THEN
DELETE WHERE (p.cost<200) - D. MERGE INTO new_prices n
USING (SELECT * FROM products WHERE cost>150) p
ON (n.prod_id= p.prod_id)
WHEN MATCHED THEN
UPDATE SET n.price= p.cost*.01
DELETE WHERE (p.cost<200);
Answer: D
Explanation:
B: True. This MERGE statement should execute without errors. It uses a conditionally filtered selection from the products table as a source to update or delete rows in the new_prices table based on whether the prod_id matches and the cost is greater than 150. The delete operation within a MERGE statement is allowed in Oracle when a WHEN MATCHED clause is specified.
The MERGE statement is correctly structured with a USING clause that includes a subquery with a valid WHERE condition, an ON condition that specifies how to match rows between the source and the target, and a WHEN MATCHED THEN clause that specifies the update and delete operations based on the cost condition.
References:Oracle SQL documentation specifies that within a MERGE statement, you can specify a WHEN MATCHED clause to update and/or delete rows in the target table based on the condition specified after the DELETE keyword.
NEW QUESTION # 85
Examine the description of the PRODUCT_ STATUS table:
The STATUS column contains the values IN STOCK or OUT OF STocK for each row.
Which two queries will execute successfully?
- A. SELECT prod_id q's not available" from product_ status WHERE status = 'OUT OF STOCK';
- B. SELECT PROD_ID||q'('s not available)' FROM
product_ status WHERE status = 'OUT OF STOCK'; - C. SELECT prod_id ||q'(' s not available)' 'CURRENT AVAILABILITY' FROM
product_ status WHERE status = 'OUT OF STOCK' - D. SELECT PROD_ID||q'('s not available)' "CURRENT AVAILABILITY"
FROM product_ status WHERE status = 'OUT OF STOCK'; - E. SELECT prod_id ||q"' s not available" FROM
product_ status WHERE status = 'OUT OF STOCK'; - F. SELECT prod_id "CURRENT AVAILABILITY"||q' ('s not available)' from product_ status WHERE status
= 'OUT OF STOCK';
Answer: B,D
NEW QUESTION # 86
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?
- A. It produces an error because subqueries cannot be used with the CASE expression.
- B. It shows COST_REMARK for all the promos in the promo category 'TV'.
- C. It shows COST_REMARK for all the promos in the table.
- D. It produces an error because the subquery gives an error.
Answer: C
NEW QUESTION # 87
You issued this command: DROP TABLE hr. employees;
Which three statements are true?
- A. ALL constraints defined on HR, EMPLOYEES are dropped.
- B. Views referencing HR, EMPLOYEES are dropped.
- C. Synonyms for HR EMPLOYEES are dropped.
- D. The HR. EMPLOYEES table may be moved to the recycle bin.
- E. All indexes defined on HR, EMPLOYEES are dropped.
- F. Sequences used to populate columns in the HR. EMPLOYEES table are dropped.
Answer: A,D,E
NEW QUESTION # 88
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) = MAX(unit_price*quantity)
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) = (SELECT MAX(unit_price*quantity)
FROM order_items)
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 # 89
Examine this SQL statement:
Which two are true?
- A. The UPDATE statement executes successfully even if the subquery selects multiple rows
- B. All existing rows in the ORDERS table are updated
- C. The subquery is not a correlated subquery
- D. The subquery is executed before the UPDATE statement is executed
- E. The subquery is executed for every updated row in the ORDERS table
Answer: C
NEW QUESTION # 90
Which two statements are true about Oracle synonyms?
- A. A synonym can have a synonym.
- B. A synonym can be created on an object in a package.
- C. Any user can create a PUBLICsynonym.
- D. All private synonym names must be unique in the database.
- E. A synonym has an object number.
Answer: B,D
Explanation:
Explanation/Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm
NEW QUESTION # 91
View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.
Evaluate the following SQL statement:
What would be the outcome of the above SQL statement?
- A. It displays prod IDs in the promos which cost less than the highest cost in the same time interval.
- B. It displays prod IDs in the promos with the highest cost in the same time interval.
- C. It displays prod IDs in the promos with the lowest cost in the same time interval.
- D. It displays prod IDs in the promo with the lowest cost.
Answer: A
NEW QUESTION # 92
Examine the structure of the CUSTOMERS table: (Choose two.)
CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?
- A. Subquery
- B. Full outer-join with self-join
- C. Right outer-join with self-join
- D. Left outer-join with self-join
- E. Self-join
Answer: A,E
NEW QUESTION # 93
View and Exhibit and examine the structure and data in the INVOICE table. (Choose two.)
Which two statements are true regarding data type conversion in query expressions?
- A. CONCAT(inv_amt, inv_date) : requires explicit conversion
- B. inv_date > '01-02-2008' : uses implicit conversion
- C. inv_no BETWEEN '101' AND '110' : uses implicit conversion
- D. inv_amt = '0255982' : requires explicit conversion
- E. inv_date = '15-february-2008' :uses implicit conversion
Answer: C,E
NEW QUESTION # 94
......
Oracle 1z0-071 Official Cert Guide PDF: https://www.freecram.com/Oracle-certification/1z0-071-exam-dumps.html
Free Oracle PL/SQL Developer Certified Associate 1z0-071 Official Cert Guide PDF Download: https://drive.google.com/open?id=1HKCh6V9fLb62eBlb0USBviKsExrJLlyd