[UPDATED 2022] 1z0-071 dumps Free Test Engine Verified By Certified Experts
Realistic 1z0-071 Accurate & Verified Answers As Experienced in the Actual Test!
NEW QUESTION 67
The BOOKS_TRANSACTIONS table exists in your schema in this database.
You execute this SQL statement when connected to your schema in your database instance.
SQL> SELECT * FROM books_transactions ORDER BY 3;
What is the result?
- A. The first three rows in the table are displayed in the order that they are stored.
- B. All table rows are displayed sorted in ascending order of the values in the third column.
- C. Only the three rows with the lowest values in the key column are displayed in the order that they are stored.
- D. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
Answer: B
NEW QUESTION 68
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 succeeds and CITY can contain only 'SEATTLE' or null for all rows.
- B. It fails because the condition for the CANAMES constraint is not valid.
- C. It succeeds and an index is crated for CITIZEN_ID.
- D. It fails because the NOT NULL and DEFAULT options cannot be combined for the same column.
Answer: D
NEW QUESTION 69
Which two statements are true regarding constraints? (Choose two.)
- A. A table can have only one primary key but multiple foreign keys.
- B. Only the primary key can be defined at the column and table levels.
- C. Both primary key and foreign key constraints can be defined at both column and table levels.
- D. The foreign key and parent table primary key must have the same name.
- E. A table can have only one primary key and one foreign key.
Answer: A,C
NEW QUESTION 70
View the Exhibit and examine the data in the EMPLOYEES table.
Exhibit
You want to generate a report showing the total compensation paid to each employee to date.
You issue the following query:
What is the outcome?
- A. IT executes successfully and gives the correct output.
- B. It executes successfully but does not give the correct output.
- C. It generates an error because the concatenation operator can be used to combine only two items.
- D. It generates an error because the alias is not valid.
- E. It generates an error because the usage of the ROUND function in the expression is not valid.
Answer: B
NEW QUESTION 71
What is true about non-equijoin statement performance?
- A. Table aliases can improve performance.
- B. The BETWEEN condition always performs less well than using the >= and <= conditions.
- C. The join syntax used makes no difference to performance.
- D. The BETWEEN condition always performs better than using the >= and <= conditions.
- E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
Answer: A
Explanation:
Explanation/Reference: https://www.academia.edu/17342225/SQL_notes
NEW QUESTION 72
The ORDERStable has a primary key constraint on the ORDER_IDcolumn.
The ORDER_ITEMStable has a foreign key constraint on the ORDER_IDcolumn, referencing the primary key of the ORDERStable.
The constraint is defined with ON DELETE CASCADE.
There are rows in the ORDERStable with an ORDER_TOTALof less than 1000.
Which three DELETEstatements execute successfully?
DELETE order_id FROM orders WHERE order_total < 1000;
- A. DELETE orders WHERE order_total < 1000;
- B. DELETE FROM orders;
- C.
- D. DELETE * FROM orders WHERE order_total < 1000;
- E. DELETE FROM orders WHERE order_total < 1000;
Answer: A,C,D
NEW QUESTION 73
Which three are true about multitable INSERTstatements? (Choose three.)
- A. They can be performed only by using a subquery.
- B. They can be performed on remote tables.
- C. They can be performed on external tables using SQL* Loader.
- D. They can be performed on views.
- E. They can be performed on relational tables.
- F. They can insert each computed row into more than one table.
Answer: D,E,F
NEW QUESTION 74
Evaluate the following SQL statement:
SQL> select cust_id, cust_last_name "Last name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDERBYclauses either one of which can complete the query.
- A. ORDER BY "CUST_NO"
- B. ORDER BY 2, cust_id
- C. ORDER BY 2, 1
- D. ORDER BY CUST_NO
- E. ORDER BY "Last name"
Answer: B,C,E
Explanation:
Explanation/Reference:
Explanation:
Using the ORDER BY Clause in Set Operations
-The ORDER BY clause can appear only once at the end of the compound query.
-Component queries cannot have individual ORDER BY clauses.
-The ORDER BY clause recognizes only the columns of the first SELECT query.
-By default, the first column of the first SELECT query is used to sort the output in an ascending order.
NEW QUESTION 75
View the Exhibit and examine the structure of the CUSTOMERS table.
CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table.
CUSTOMERS need to be updated to reflect the latest information about the customers.
What is the error in the following MERGE statement?
- A. The WHERE clause cannot be used with INSERT.
- B. The INTO clause is misplaced in the command.
- C. CUSTOMER_VU cannot be used as a data source.
- D. The CUSTOMER_ID column cannot be updated.
Answer: D
NEW QUESTION 76
Examine the structure of the ORDERS table: (Choose the best answer.)
You want to find the total value of all the orders for each year and issue this command:
SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders
GROUP BY TO_CHAR(order_date, 'yyyy');
Which statement is true regarding the result?
- A. It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
- B. It executes successfully but does not give the correct output.
- C. It returns an error because the TO_CHAR function is not valid.
- D. It executes successfully but gives the correct output.
Answer: A
NEW QUESTION 77
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 with the highest cost in the same time interval.
- B. It displays prod IDs in the promo with the lowest cost.
- C. It displays prod IDs in the promos with the lowest cost in the same time interval.
- D. It displays prod IDs in the promos which cost less than the highest cost in the same time interval.
Answer: D
NEW QUESTION 78
View the Exhibit and examine the data in the PROMOTIONS table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000.
Which SQL statement would you use?
- A. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_cost LIKE
'post%' AND promo_begin_date < '01-01-2000'; - B. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';
- C. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';
- D. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category =
'post' AND promo_begin_date < '01-01-00';
Answer: B
NEW QUESTION 79
Examine the structure of the BOOKS_TRANSACTIONS table:
Examine the SQL statement:
Which statement is true about the outcome?
- A. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.
- B. It displays details for members who have borrowed before today's date with either RM as TRANSACTION_TYPE or MEMBER_ID as A101 and A102.
- C. It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE.
- D. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
Answer: A
NEW QUESTION 80
Which two statements will return the names of the three employees with the lowest salaries?
- A. SELECT last name, salary
FROM employees
ORDER BY salary
FETCE FIRST 3 RONS ONLY; - B. SELECT last_ name, salary
FROM employees
WHERE ROWNUM <=3
ORDER BY salary - C. SELECT last_ name, salary
FBOM employees
WEERE
ORDER BY SELECT
ROINUM <= 3
salary FROM
employees); - D. SELECT last_ name, salary
FROM
(SELECT" FROM employees ORDER BY salary)
WHERE ROWNUM <=3 - E. SELECT last_ name, salary
FROM employees
FETCH FIRST 3 ROWS ONLY
ORDER BY salary;
Answer: A,D
NEW QUESTION 81
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 (order_id, order_date, order mode,customer_id, order_total) VALUES (1, TO_DATE (NULL),'online',101, NULL) ;
- B. INSERT INTO (SELECT order_id, order date, customer_id FROM orders) VALUES (1, '09-mar-2007",101);
- C. INSERT INTO orders VALUES (1, '09-mar-2007', 'online',' ',1000);
- 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: B,E
NEW QUESTION 82
Which three are true about the MERGEstatement? (Choose three.)
- A. It can use views to produce source rows.
- B. It can merge rows only from tables.
- C. It can update, insert, or delete rows conditionally in multiple tables.
- D. It can use subqueries to produce source rows.
- E. It can update the same row of the target table multiple times.
- F. It can combine rows from multiple tables conditionally to insert into a single table.
Answer: A,C,D
Explanation:
Explanation/Reference: https://www.oracletutorial.com/oracle-basics/oracle-merge/
NEW QUESTION 83
View the Exhibit and examine the structure of the CUSTOMERStable.
CUSTOMER_VUis a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERStable.
CUSTOMERSneed to be updated to reflect the latest information about the customers.
What is the error in the following MERGEstatement?
- A. CUSTOMER_VUcannot be used as a data source.
- B. The WHEREclause cannot be used with INSERT.
- C. The CUSTOMER_IDcolumn cannot be updated.
- D. The INTOclause is misplaced in the command.
Answer: C
Explanation:
Explanation/Reference:
NEW QUESTION 84
Evaluate the following query:
SQL> SELECT TRUNC (ROUND (156.00, -1),-1)
FROM DUAL;
What would be the outcome?
- A. 0
- B. 1
- C. 2
- D. 3
- E. 4
Answer: D
Explanation:
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm
https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2127.htm
NEW QUESTION 85
View the Exhibit and examine the structure of the CUSTOMERStable.
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. Only the first query gives the correct result
- B. Both execute successfully and give the same result
- C. Both execute successfully but do not give the required result
- D. Only the second query gives the correct result
Answer: A
NEW QUESTION 86
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. LEFT OUETR JOIN
- B. RIGHT OUTER JOIN
- C. FULL OUTER JOIN
- D. JOIN
Answer: A,C
NEW QUESTION 87
View the exhibit and examine the structure of the STORES table.
You must display the NAME of stores along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.
The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?
- A. SELECT name, concat(address||', '||city||', ', country) AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
- B. SELECT name, concat(address||', '||city||', ',country) AS full_address, start_date, property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,'01-JAN-2000') <=36;
- C. SELECT name, address||', '||city||', '||country AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
- D. SELECT name, concat(address||', '||city||', ',country) AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
Answer: A
NEW QUESTION 88
Which statement is true about transactions?
- A. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
- B. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
- C. Each Data Definition Language (DDL) statement executed forms a single transaction.
- D. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.
Answer: C
Explanation:
Explanation
https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038
NEW QUESTION 89
n the customers table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'Abigail'.
Evaluate the following query:
What would be the outcome?
- A. Abigail IS
- B. An error message
- C. Abigail PA
- D. Abigail Pa
Answer: D
NEW QUESTION 90
View the Exhibit and examine the structure of the ORDERS table.
You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.
Which query would give you the desired result?
- A. SELECT order_id, order_date FROM orders
WHERE order_date >
ANY
(SELECT order_date FROM orders WHERE customer_id = 101); - B. SELECT order_id, order_date FROM orders
WHERE order_date > IN
(SELECT order_date FROM orders WHERE customer_id = 101); - C. SELECT order_id, order_date FROM orders
WHERE order_date > ALL
(SELECT order_date FROM orders WHERE customer_id = 101); - D. SELECT order_id, order_date FROM orders
WHERE order_date > ALL
(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
Answer: C
NEW QUESTION 91
Examine the description of the EMPLOYEES table:
Which query requires explicit data type conversion?
- A. SELECT SUBSTR(join_date, 1, 2) - 10 FROM employees;
- B. SELECT join_date + '20' FROM employees;
- C. SELECT join_date || ' ' || salary FROM employees;
- D. SELECT join_date FROM employees WHERE join_date > '10-02-2018';
- E. SELECT salary + '120.50' FROM employees;
Answer: B
NEW QUESTION 92
......
Latest Oracle 1z0-071 Practice Test Questions: https://pass4lead.newpassleader.com/Oracle/1z0-071-exam-preparation-materials.html