High quality 1Z0-071 Study Guides 2021

NEW QUESTION 1
Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

• A. You can use column alias in the GROUP BY clause.
• B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
• C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
• D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
• E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY cause.

Answer: DE

NEW QUESTION 2
A non-correlated subquery can be defined as . (Choose the best answer.)

• A. A set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query.
• B. A set of sequential queries, all of which must return values from the same table.
• C. A set of sequential queries, all of which must always return a single value.
• D. A SELECT statement that can be embedded in a clause of another SELECT statement only.

Answer: A

NEW QUESTION 3
View the Exhibit and examine the structure of the PORDUCT_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_informationWHERE UPPER(product_name) = 'LASERPRO' OR list_price > 1000;
• B. SELECT UPPER(product_name)FROM product_information;
• C. SELECT UPPER(product_name)FROM product_informationWHERE product_id = 2254;
• D. SELECT product_idFROM product_informationWHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');

Answer: D

NEW QUESTION 4
View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.

You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task? (Choose two.)

• A. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id)WHERE quantity_on_hand < 5;
• B. SELECT product_id, quantity_on_hand, supplier_idFROM product_informationNATURAL JOIN inventories AND quantity_on_hand < 5;
• C. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
• D. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOINinventories iON (pi.product_id=i.product_id)USING (product_id) AND quantity_on_hand < 5;

Answer: AC

NEW QUESTION 5
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

• A. SELECT TO_CHAR (1890.55, '$99G999D00') FROM DUAL
• B. SELECT TO_CHAR (1890.55, '$9,999V99') FROM DUAL;
• C. SELECT TO_CHAR (1890.55, '$0G000D00') FROM DUAL;
• D. SELECT TO_CHAR (1890.55, '$99,999D99') FROM DUAL;
• E. SELECT TO_CHAR (1890.55, '$99G999D99') FROM DUAL

Answer: ACE

NEW QUESTION 6
Evaluate the following CRTEATE TABLE commands:
CREATE_TABLE orders
(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE, cust_id NUMBER (4) );
CREATE TABLE ord_items (ord _no NUMBER (2),
item_no NUMBER(3),
qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
expiry_date date CHECK (expiry_date> SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) ); Why would the ORD_ITEMS table not get created?

• A. SYSDATE cannot be used with the CHECK constraint.
• B. The BETWEEN clause cannot be used for the CHECK constraint.
• C. The CHECK constraint cannot be placed on columns having the DATE data type.
• D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Answer: A

NEW QUESTION 7
Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1 table, which are not present in the SALES2 table.
SALES1 table NameNullType
----------------------------------------------- SALES_IDNUMBER STORE_IDNUMBER ITEMS_IDNUMBER QUANTITYNUMBER SALES_DATEDATE
SALES2 table NameNullType
---------------------------------------------- SALES_IDNUMBER STORE_IDNUMBER
ITEMS_IDNUMBER QUANTITYNUMBER SALES_DATEDATE
Which set operator generates the required output?

• A. INTERSECT
• B. UNION
• C. PLUS
• D. MINUS
• E. SUBTRACT

Answer: D

Explanation: References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

NEW QUESTION 8
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:

You want to generate a list of all department IDs that do not exist in the COURSE_DETAILS table. You execute the SQL statement:

What is the outcome?

• A. It fails because the join type used is incorrect.
• B. It executes successfully and displays the required list.
• C. It executes successfully but displays an incorrect list.
• D. It fails because the ON clause condition is not valid.

Answer: B

NEW QUESTION 9
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.

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?

• A. 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.
• B. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
• C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
• D. 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.

Answer: D

NEW QUESTION 10
View the Exhibit and examine the structure in the EMPLOYEES tables.

Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees
WHERE department_id= 50 ORDER BY department_id UNION
SELECT employee_id, department_id FROM employees
WHERE department_id=90 UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=10;
What would be the outcome of the above SQL statement?

• A. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
• B. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
• C. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
• D. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.

Answer: D

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

• A. All constraints can be defined at the column level and at the table level.
• B. A constraint can be disabled even if the constraint column contains data.
• C. A column with the UNIQUE constraint can contain NULLS.
• D. A foreign key column cannot contain NULLS.
• E. A constraint is enforced only for INSERT operations.

Answer: BC

NEW QUESTION 12
Which statement is true regarding the default behaviour of the ORDER by clause?

• A. Numeric values are displayed in descending order if they have decimal positions.
• B. Only columns that are specified in the SELECT list can be used in the ORDER by clause.
• C. In a character sort, the values are case-sensitive.
• D. NULLs are not including in the sort operation

Answer: C

NEW QUESTION 13
Examine the structure of the EMPLOYEES table. NameNull?Type
---------------------- ------------ EMPLOYEE_IDNOT NULLNUMBER(6) FIRST_NAMEVARCHAR2(20) LAST_NAMENOT NULLVARCHAR2(25) EMAILNOT NULLVARCHAR2(25) PHONE NUMBERVARCHAR2(20) HIRE_DATENOT NULLDATE JOB_IDNOT NULLVARCHAR2(10) SALARYNUMBER(8,2) COMMISSION_PCTNUMBER(2,2) MANAGER_IDNUMBER(6) DEPARTMENT_IDNUMBER(4)
There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.
Which query provides the correct output?

• A. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.manager_id = m.employee_id)AND e.employee_id = 123;
• B. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.manager_id)WHERE e.employee_id = 123;
• C. SELECT e.last_name, e.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.employee_id)WHERE e.employee_id = 123;
• D. SELECT m.last_name, e.manager_idFROM employees e LEFT OUTER JOIN employees mon (e.manager_id = m.manager_id)WHERE e.employee_id = 123;

Answer: B

NEW QUESTION 14
Which task can be performed by using a single Data Manipulation Language (DML) statement?

• A. adding a column constraint when inserting a row into a table
• B. adding a column with a default value when inserting a row into a table
• C. removing all data only from one single column on which a unique constraint is defined
• D. removing all data only from one single column on which a primary key constraint is defined

Answer: C

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

• A. 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.
• B. During executing, the oracle server may read data from storage if the required data is not already in memory.
• C. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
• D. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

Answer: B

NEW QUESTION 16
Examine these SQL statements that are executed in the given order:
CREATE TABLE emp
(emp_no NUMBER (2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, ename VARCHAR 2 (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 will be the status of the foreign key EMP_MGR_FK?

• A. It will be enabled and immediate.
• B. It will be enabled and deferred.
• C. It will remain disabled and can be re-enabled manually.
• D. It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

Answer: C

NEW QUESTION 17
Evaluate this ALTER TABLE statement: (Choose the best answer.) ALTER TABLE orders
SET UNUSED (order_date); Which statement is true?

• A. After executing the ALTER TABLE command, a new column called ORDER_DATE can be added to the ORDERS table.
• B. The ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully.
• C. ROLLBACK can be used to restore the ORDER_DATE column.
• D. The DESCRIBE command would still display the ORDER_DATE column.

Answer: A

NEW QUESTION 18
View the exhibit and examine the structure and data in the invoice table. (Choose two.)

Which two SQL statements would execute successfully?

• A. SELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice
• B. SELECT AVG(inv_date) FROM invoice
• C. SELECT MAX(inv_date), MIN(cust_id) FROM invoice
• D. SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice

Answer: CD