Vivid 1Z0-071 Dumps Questions 2021

NEW QUESTION 1
Examine the business rule:
Each student can work on multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario?

• A. The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities.
• B. The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships.
• C. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
• D. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
• E. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.

Answer: BE

Explanation: References:
http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512021.html

NEW QUESTION 2
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 3
Which task can be performed by using a single Data Manipulation Language (DML) statement?

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

Answer: A

NEW QUESTION 4
View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.

Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) AS
SELECT order_id.order_date,customer_id FROM orders;
Which statement is true regarding the above command?

• A. The NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.
• B. The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
• C. The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
• D. The NEW_ODRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

Answer: B

NEW QUESTION 5
View the Exhibits and examine PRODUCTS and SALES tables. Exhibit 1

Exhibit 2

You issue the following query to display product name the number of times the product has been sold:

What happens when the above statement is executed?

• A. The statement executes successfully and produces the required output.
• B. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
• C. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.
• D. The statement produces an error because ITEM_CNT cannot be displayed in the outer query.

Answer: A

NEW QUESTION 6
Which two tasks can be performed by using Oracle SQL statements?

• A. changing the password for an existing database user
• B. connecting to a database instance
• C. querying data from tables across databases
• D. starting up a database instance
• E. executing operating system (OS) commands in a session

Answer: AC

Explanation: References:
http://www.techonthenet.com/oracle/password.php
https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm

NEW QUESTION 7
You execute the SQL statement: SQL> CREATE TABLE citizens
(citizen_id CHAR (10) PRIMARY KEY, last_name VARCHAR2 (50) NOT NULL, first_name VARCHAR2 (50),
address VARCHAR2 (100),
city VARCHAR2 (30) DEFAULT ‘SEATTLE’ NOT NULL,
CONSTRAINT cnames CHECK (first_name<>last_name) ); What is the outcome?

• A. It fails because the NOT NULL and DEFAULT options cannot be combined for the same column.
• B. It succeeds and CITY can contain only ‘SEATTLE’ or null for all rows.
• C. It fails because the condition for the CANAMES constraint is not valid.
• D. It succeeds and an index is crated for CITIZEN_ID.

Answer: A

NEW QUESTION 8
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 9
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?

• A. SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)
• B. SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN.department_details d ON (c.depatrment_id=d.department_id)
• C. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=
• D. department_id)
• E. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>
• F. department_id)

Answer: C

NEW QUESTION 10
Which two statements are true regarding single row functions? (Choose two.)

• A. MOD : returns the quotient of a division.
• B. TRUNC : can be used with NUMBER and DATE values.
• C. CONCAT : can be used to combine any number of values.
• D. SYSDATE : returns the database server current date and time.
• E. INSTR : can be used to find only the first occurrence of a character in a string.
• F. TRIM : can be used to remove all the occurrences of a character from a string.

Answer: BD

NEW QUESTION 11
You want to display 5 percent of the rows from the SALES table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows.
Which query will provide the required result?

• A. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS WITH TIES;
• B. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;
• C. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;
• D. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS ONLY;

Answer: A

NEW QUESTION 12
Examine the data in the CUSTOMERS table:

You want to list all cities that have more than one customer along with the customer details. Evaluate the following query:

Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)

• A. LEFT OUTER JOIN
• B. JOIN
• C. NATURAL JOIN
• D. RIGHT OUTER JOIN
• E. FULL OUTER JOIN

Answer: BD

NEW QUESTION 13
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 14
You issue the following command to drop the PRODUCTS table: (Choose all that apply.) SQL > DROP TABLE products;
Which three statements are true about the implication of this command?

• A. All data along with the table structure is deleted.
• B. A pending transaction in the session is committed.
• C. All indexes on the table remain but they are invalidated.
• D. All views and synonyms on the table remain but they are invalidated.
• E. All data in the table is deleted but the table structure remains.

Answer: ABD

NEW QUESTION 15
View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator: SQL > SELECT p.prod_id, count(s.prod_id)
FROM products p sales s ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN operations can be used to obtain the required output?

• A. FULL OUTER JOIN
• B. JOIN
• C. LEFT OUETR JOIN
• D. RIGHT OUTER JOIN

Answer: AC

NEW QUESTION 16
View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?

• A. UPDATE dept dSET city = ALL (SELECT cityFROM locations lWHERE d.location_id = l.location_id);
• B. UPDATE dept dSET city = (SELECT cityFROM locations l)WHERE d.location_id = l.location_id;
• C. UPDATE dept dSET city = ANY (SELECT cityFROM locations l)
• D. UPDATE dept dSET city = (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

Answer: D

NEW QUESTION 17
The first DROP operation is performed on PRODUCTS table using the following command: DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command: FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?

• A. It recovers only the table structure.
• B. It recovers the table structure, data, and the indexes.
• C. It recovers the table structure and data but not the related indexes.
• D. It is not possible to recover the table structure, data, or the related indexes.

Answer: D

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

NEW QUESTION 18
Which three statements are true reading subquenes?

• A. A Main query can have many subqueries.
• B. A subquery can have more than one main query.
• C. The subquery and main query must retrieve date from the same table.
• D. The subquery and main query can retrieve data from different tables.
• E. Only one column or expression can be compared between the subquery and main query.
• F. Multiple columns or expressions can be compared between the subquery and main query.

Answer: ADF