Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 151:

  Examine this business rule:

  Each student can work on multiple projects and each project can have multiple students.

  You must design an Entity Relationship (ER) model 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? (Choose two.)

  A. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
  B. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the projects entity.
  C. 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.
  D. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into one-to-many relationships.
  E. The ER must have a one-to-many relationship between the STUDENTS and PROJECTS entities.
  C. 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.
  D. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into one-to-many relationships.

• Question 152:

  Which statement is true regarding the SESSION_PRIVS dictionary view?

  A. It contains the object privileges granted to other users by the current user session.
  B. It contains the system privileges granted to other users by the current User session.
  C. It contains the current system privileges available in the user session.
  D. It contains the current object privileges available in the user session.
  C. It contains the current system privileges available in the user session.

• Question 153:

  The SYSDATE function displays the current Oracle Server date as:

  21 -MAY-19

  You wish to display the date as:

  MONDAY, 21 MAY, 201 9

  Which statement will do this?

  A. SELECT TO_DATE(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;
  B. SELECT TO_CHAR(SYSDATE, 'FMDD, DAY MONTH, YYYY') FROM DUAL;
  C. SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;
  D. SELECT TO_CHAR(SYSDATE, 'FMDAY, DDTH MONTH, YYYY') EROM DUAL;
  C. SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;

• Question 154:

  View the Exhibit and examine the structure of CUSTOMERS table.

  Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.

  Which SQL statement would produce the required result?

  

  A. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
  B. SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
  C. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
  D. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT" FROM customers;
  A. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;

• Question 155:

  The ORDERS table has a primary key constraint on the ORDER_ID column.

  The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table.

  The constraint is defined with on DELETE CASCADE.

  There are rows in the ORDERS table with an ORDER_TOTAL less than 1000.

  Which three DELETE statements execute successfully?

  A. DELETE order_id FROM orders WHERE order_total < 1000;
  B. DELETE orders WHERE order_total < 1000
  C. DELETE * FROM orders WHERE order_total < 1000;
  D. DELETE FROM orders;
  E. DELETE FROM orders WHERE order_total < 1000;
  B. DELETE orders WHERE order_total < 1000
  D. DELETE FROM orders;
  E. DELETE FROM orders WHERE order_total < 1000;

• Question 156:

  Which three statements are true about built-in data types? (Choose three.)

  A. The default length for a CHAR column is always one character.
  B. A VARCHAR2 blank-pads column values only in the data stored is non-numeric and contains no special characters.
  C. A VARCHAR2 column definition does not require the length to be specified.
  D. A CHAR column definition does not require the length to be specified.
  E. A BLOB stores unstructured binary data within the database.
  F. A BFILE stores unstructured binary data in operating system files.
  D. A CHAR column definition does not require the length to be specified.
  E. A BLOB stores unstructured binary data within the database.
  F. A BFILE stores unstructured binary data in operating system files.

• Question 157:

  Which two are true about the precedence of opertors and condtions

  A. + (addition) has a higher order of precedence than * (mliplpition)
  B. NOT has a higher order of precedence than AND and OR in a condition.
  C. AND and OR have the same order of precedence in a condition
  D. Operators are evaluated before conditions.
  E. || has a higher order of precedence than +(addition)
  B. NOT has a higher order of precedence than AND and OR in a condition.
  D. Operators are evaluated before conditions.

• Question 158:

  Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?

  A. second normal form
  B. first normal form
  C. third normal form
  D. fourth normal form
  A. second normal form

  ---

  Explanation/Reference:

  References: https://blog.udemy.com/database-normal-forms/

• Question 159:

  Examine these statements: Which two are true? (Choose two.)

  

  A. The MGR column in the DEPT table will not be able to contain NULL values.
  B. The CREATE TABLE EMP statement must precede the CREATE TABLE DEPT statement for all threestatements to execute successfully.
  C. Both foreign key constraint definitions must be removed from the CREATE TABLE statements, andbe added with ALTER TABLE statements once both tables are created, for the two CREATE TABLEstatements to execute successfully in the order shown.
  D. The DEFT FKEY constraint definition must be removed from the CREATE TABLE DEF statement.and be added with an AITER TABLE statement once both tables are created, for the two CREATE TABLE statements to execute successfully in the order shown.
  E. The Deptno column in the emp table will be able to contain nulls values.
  F. All three statements execute successfully in the order shown
  D. The DEFT FKEY constraint definition must be removed from the CREATE TABLE DEF statement.and be added with an AITER TABLE statement once both tables are created, for the two CREATE TABLE statements to execute successfully in the order shown.
  E. The Deptno column in the emp table will be able to contain nulls values.

• Question 160:

  View the Exhibit and examine the details of PRODUCT_INFORMATION table.

  

  You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:

  SELECT product_name 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 not execute because the same column has been used in both sides of the AND logical operator to form the condition.
  B. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
  C. It would execute and the output would display the desired result.
  D. It would execute but the output would return no rows.
  D. It would execute but the output would return no rows.