Updated Dec 13, 2023 Verified Pass 1z0-071 Exam in First Attempt Guaranteed
Free 1z0-071 Sample Questions and 100% Cover Real Exam Questions (Updated 305 Questions)
NEW QUESTION # 115
Evaluate these commands which execute successfully:
Which two statements are true about the ORD_ITEMStable and the ORD_SEQsequence?
- A. Column ORD_NOgets the next number from sequence ORD_SEQwhenever a row is inserted into ORD_ITEMSand no explicit value is given for ORD_NO.
- B. If sequence ORD_SEQis dropped then the default value for column ORD_NOwill be NULL for rows inserted into ORD_ITEMS.
- C. Any user inserting rows into table ORD_ITEMSmust have been granted access to sequence ORD_SEQ.
- D. Sequence ORD_SEQcycles back to 1 after every 5000 numbers and can cycle 20 times.
- E. Sequence ORD_SEQis guaranteed not to generate duplicate numbers.
Answer: C,E
NEW QUESTION # 116
Which is true about the & and && prefixes with substitution variables?
- A. An & prefix to an undefined substitution variable, which is referenced twice in the same query, will prompt for a value twice .
- B. Both & and && can prefix a substitution variable name in queries and DML statements.
- C. The && prefix will not prompt for a value even if the substitution variable is not previously defined in the session.
- D. & can prefix a substitution variable name only in queries. DML
- E. An && prefix to an undefined substitution variable, which is referenced multiple times in multiple queries, will prompt for a value once per query.
Answer: A,B
NEW QUESTION # 117
Which two tasks can be performed by using Oracle SQL statements?
- A. changing the password for an existing database
- B. starting up a database instance
- C. executing operating system (OS) commands in a session
- D. connecting to a database instance
- E. querying data from tables across databases
Answer: C,E
NEW QUESTION # 118
Which two statements are true regarding a SAVEPOINT? (Choose two.)
- A. Rolling back to a SAVEPOINT can undo a DELETE statement
- B. Rolling back to a SAVEPOINT can undo a TRUNCATE statement
- C. Only one SAVEPOINT may be issued in a transaction
- D. A SAVEPOINT does not issue a COMMIT
- E. Rolling back to a SAVEPOINT can undo a CREATE INDEX statement
Answer: A,D
NEW QUESTION # 119
Examine the description of the CUSTOMERS table:
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. LEFT OUTER JOIN with self join
- B. self join
- C. RIGHT OUTER JOIN with self join
- D. FULL OUTER JOIN with self join
- E. subquery
Answer: B,E
NEW QUESTION # 120
View the Exhibit and examine the structure of the PROMOTION table.
You have to generate a report that displays the promo named start data for all promos that started after that last promo in the 'INTTERNET' category.
- A. SELECT promo-name, promo-being_date FROM promotion
WHERE promo-being-date IN (SELECT promo_biing_date
FROM promotions
WHERE promo_category='INTYERNET'); - B. Select promo_name, promo_being_date FROM promoptions
WHERE promo_being_data > ANY (SELCT promo_being-date
FROM promotions
WHERE promo_category = 'INTERNET' - C. SELECT promo-name, promo-being _date FROM promotions
Where promo_being_data >ALL (SELECT MAX (promo_being-date)
FROM promotions ) AND
Promo-category ='INTERNET'; - D. SELECT promo_neme, promo_being_date FROM promotions
WHERE promo_being_date > All (SELECT promo_beinjg-date
FROM promotions
WHERE promo_category ='INTERNET' );
Answer: D
NEW QUESTION # 121
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. (Choose the best answer.)
You executed this UPDATE statement:
Which statement is true regarding the execution?
- A. It would not execute because two tables cannot be referenced in a single UPDATE statement.
- B. It would not execute because a SELECT statement cannot be used in place of a table name.
- C. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
- D. It would execute and restrict modifications to the columns specified in the SELECT statement.
Answer: D
NEW QUESTION # 122
You issued the following command:
SQL> DROP TABLE employees;
Which three statements are true? (Choose three.)
- A. Sequences used in the employees table become invalid.
- B. The employees table can be recovered using the rollback command.
- C. All indexes and constraints defined on the table being dropped are also dropped.
- D. The employees table is moved to the recycle bin
- E. The space used by the employees table is reclaimed immediately.
- F. All uncommitted transactions are committed.
Answer: C,D,F
NEW QUESTION # 123
Which two statements are true about conditional INSERT ALL? (Choose two.)
- A. It cannot have an ELSEclause.
- B. A single WHEN condition can be used for multiple INTOclauses.
- C. Each row returned by the subquery can be inserted into only a single target table.
- D. The total number of rows inserted is always equal to the number of rows returned by the subquery.
- E. Each WHENcondition is tested for each row returned by the subquery.
Answer: B,E
NEW QUESTION # 124
You issue the following command to drop the PRODUCTStable:
SQL > DROP TABLE products;
Which three statements are true about the implication of this command? (Choose three.)
- A. A pending transaction in the session is committed.
- B. All data in the table is deleted but the table structure remains.
- C. All indexes on the table remain but they are invalidated.
- D. All data along with the table structure is deleted.
- E. All views and synonyms on the table remain but they are invalidated.
Answer: A,D,E
Explanation:
Explanation
NEW QUESTION # 125
View the Exhibit and examine the structure of the CUSTOMERS table.
Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.
Customers with no credit limit should have "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')*.15 "NEW CREDIT"FROM customers;
Answer: A
NEW QUESTION # 126
Evaluate the following SELECT statement and view the exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status, FROM user_constraints WHERE table_name = 'ORDERS'; CONSTRAINT_NAME CON SEARCH_CONDITION R_CONSTRAINT_NAME DELETE_RULE STATUS ORDER_DATE_NN C
"ORDER_DATE" IS NOT NULL
ENABLED
ORDER_CUSTOMER_ID_NN
C
"CUSTOMER_ID" IS NOT NULL
ENABLED
ORDER_MODE_LOV
C
order _mode in ('direct', 'online')
ENABLED
ORDER TOTAL MIN
C
order total >= 0
ENABLED
ORDER PK
P
ENABLED
ORDERS CUSTOMER ID
R
CUSTOMERS ID
SET NULL
ENABLED
ORDERS SALES REP
R
EMP EMP ID
SET NULL
ENABLED
Which two statements are true about the output? (Choose two.)
- A. In the second column, 'c' indicates a check constraint.
- B. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.
- C. The STATUS column indicates whether the table is currently in use.
- D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.
Answer: A,D
NEW QUESTION # 127
View the exhibit and examine the structure of the CUSTOMERStable.
Which two tasks would require subqueries or joins to be executed in a single statement?
- A. listing of customers who do not have a credit limit and were born before 1980
- B. finding the number of customers, in each city, whose marital status is 'married'.
- C. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
- D. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city
'Tokyo'. - E. finding the average credit limit of male customers residing in 'Tokyo'or 'Sydney'
Answer: C,D
NEW QUESTION # 128
View the Exhibit and examine the structure of the CUSTOMERS table.
You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10,000.
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE 'A%' OR
UPPER (cust_last_name) LIKE 'B%' OR UPPER (cust_last_name) LIKE 'C%')
AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER (cust_last_name) BETWEEN 'A' AND 'C'
AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?
- A. Both execute successfully but do not give the required result
- B. Only the first query gives the correct result
- C. Only the second query gives the correct result
- D. Both execute successfully and give the same result
Answer: B
NEW QUESTION # 129
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 generates an error because = is not valid and should be replaced by the IN operator.
- C. It executes successfully but does not give the correct output.
- D. It executes successfully and gives the correct output.
Answer: A
NEW QUESTION # 130
Which statement adds a column called SALARYto the EMPLOYEEStable having 100 rows, which cannot contain null?
- A. ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL; - B. ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) NOT NULL; - C. ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL; - D. ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT NOT NULL;
Answer: A
NEW QUESTION # 131
Examine the structure of the MEMBERStable.
Which query can be used to display the last names and city names only for members from the states MO and MI?
- A. SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state
='MI'; - B. SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');
- C. SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
- D. SELECT last_name, city FROM members WHERE state LIKE 'M%';
Answer: B
NEW QUESTION # 132
Examine the structure of the EMPLOYEES table.
You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.
Which query would retrieve the required result?
- A. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000;
- B. (SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;
- C. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)UNION ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000);
- D. SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;
Answer: A
NEW QUESTION # 133
Examine this statement:
What is returned upon execution?
- A. 1 row
- B. an error
- C. 0 rows
- D. 2 rows
Answer: D
NEW QUESTION # 134
......
Download Real Oracle 1z0-071 Exam Dumps Test Engine Exam Questions: https://braindumpsschool.vce4plus.com/Oracle/1z0-071-valid-vce-dumps.html