Oracle 1Z0-071 Free Practice Questions 2021

NEW QUESTION 1
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 2
View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.

You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the views successfully?

• A. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;
• B. CREATE OR REPLACE VIEW ord_vu (order_id, order_date)AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;
• C. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;
• D. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)||"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_dateWHITH CHECK OPTION;

Answer: C

NEW QUESTION 3
View the exhibit and examine the structure of the EMPLOYEES table.

You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?

• A. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE m.manager_id = 100;
• B. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE e.manager_id = 100;
• C. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON e.employee_id = m.manager_idWHERE m.manager_id = 100;
• D. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eWHERE m.employee_id = e.manager_id and AND e.manager_id = 100

Answer: B

NEW QUESTION 4
Evaluate the following SQL statement:
SELECT product_name || 'it's not available for order' FROM product_information
WHERE product_status = 'obsolete';
You received the following error while executing the above query: ERROR
ORA-01756: quoted string not properly terminated What would you do to execute the query successfully?

• A. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.
• B. Enclose the literal character string in the SELECT clause within the double quotation marks.
• C. Do not enclose the character literal string in the SELECT clause within the single quotation marks.
• D. Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause.

Answer: A

Explanation: References:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm

NEW QUESTION 5
You must write a query that prompts users for column names and conditions every time it is executed. (Choose the best answer.)
The user must be prompted only once for the table name. Which statement achieves those objectives?

• A. SELECT &col1, '&col2'FROM &tableWHERE &&condition = '&cond';
• B. SELECT &col1, &col2 FROM "&table"WHERE &condition =&cond;
• C. SELECT &col1, &col2 FROM &&tableWHERE &condition = &cond;
• D. SELECT &col1, &col2 FROM &&tableWHERE &condition = &&cond

Answer: C

NEW QUESTION 6
View the Exhibit and examine the description of the ORDERS table. (Choose two.)

Which two WHERE clause conditions demonstrate the correct usage of conversion functions?

• A. WHERE Order_date_IN ( TO_DATE('OCT 21 2003', 'MON DD YYYY'), TO_CHAR('NOV 21 2003', 'MON DD YYYY') )
• B. WHERE Order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
• C. WHERE TO_CHAR(Order_date, 'MON DD YYYY') = 'JAN 20 2003'
• D. WHERE Order_date > ( TO_DATE('JUL 10 2006', 'MON DD YYYY')

Answer: CD

NEW QUESTION 7
View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.


Evaluate the following SQL statement: SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL
(SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_date- promo_begin_date)));
What would be the outcome of the above SQL statement?

• A. It displays prod IDs in the promo with the lowest cost.
• B. It displays prod IDs in the promos with the lowest cost in the same time interval.
• C. It displays prod IDs in the promos with the highest 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 8
View the exhibit and examine the structure of the STORES table. STORES table
NameNull?Type
---------------------- ------------- STORE_IDNUMBER NAMEVARCHAR2(100)
ADDRESSVARCHAR2(200) CITYVARCHAR2(100) COUNTRYVARCHAR2(100) START_DATEDATE END_DATEDATE PROPERTY_PRICENUMBER
You want to display the NAME of the store 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, '01-JAN-2000')<=36;
• B. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address,start_date,property_price, property_price*115/100FROM storesWHERETO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=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 MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

Answer: D

NEW QUESTION 9
Which statement is true about an inner join specified in the WHERE clause of a query?

• A. It must have primary-key and foreign-key constraints defined on the columns used in the join condition.
• B. It requires the column names to be the same in all tables used for the join conditions.
• C. It is applicable for equijoin and nonequijoin conditions.
• D. It is applicable for only equijoin conditions.

Answer: C

NEW QUESTION 10
See the Exhibit and examine the structure of the PROMOTIONS table:

Using the PROMOTIONS table,
you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:

What would be the outcome?

• A. It generates an error because multiple conditions cannot be specified for the WHEN clause.
• B. It executes successfully and gives the required result.
• C. It generates an error because CASE cannot be used with group functions.
• D. It generates an error because NULL cannot be specified as a return value.

Answer: B

Explanation: CASE Expression
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn ELSE else_expr]
END

NEW QUESTION 11
Examine the structure of the BOOKS_ TRANSACTIONS table:

Examine the SQL statement:

Which statement is true about the outcome?

• A. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
• B. It displays details for members who have borrowed before today's date with either RM asTRANSACTION_TYPE or MEMBER_ID as A101 and A102.
• C. It displays details for only members A101and A102 who have borrowed before today with RM as TRANSACTION_TYPE.
• D. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.

Answer: A

NEW QUESTION 12
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 ORDER BY clauses either one of which can complete the query.

• A. ORDER BY "Last name"
• B. ORDER BY 2, cust_id
• C. ORDER BY CUST_NO
• D. ORDER BY 2, 1
• E. ORDER BY "CUST_NO"

Answer: ABD

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 13
In which three situations does a transaction complete?

• A. when a PL/SQL anonymous block is executed
• B. when a DELETE statement is executed
• C. when a ROLLBACK command is executed
• D. when a data definition language (DDL) statement is executed
• E. when a TRUNCATE statement is executed after the pending transaction

Answer: CDE

Explanation: References:
https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm

NEW QUESTION 14
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. (Choose the best answer.)

You executed this UPDATE statement: UPDATE
( SELECT order_date, order_total, customer_id FROM orders) Set order_date = '22-mar-2007'
WHERE customer_id IN
(SELECT customer_id FROM customers
WHERE cust_last_name = 'Roberts' AND credit_limit = 600); Which statement is true regarding the execution?

• A. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
• B. It would not execute because two tables cannot be referenced in a single UPDATE statement.
• C. It would execute and restrict modifications to the columns specified in the SELECT statement.
• D. It would not execute because a SELECT statement cannot be used in place of a table name.

Answer: C

NEW QUESTION 15
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 executes successfully but does not give the correct output.
• B. It executes successfully but gives the correct output.
• C. It returns an error because the TO_CHAR function is not valid.
• D. It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Answer: D

NEW QUESTION 16
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 17
Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

• A. Finding the lowest value
• B. Finding the quotient
• C. Raising to a power
• D. Subtraction
• E. Addition

Answer: ACE

NEW QUESTION 18
You must create a SALES table with these column specifications and data types: (Choose the best answer.) SALESID: Number
STOREID: Number ITEMID: Number
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified
PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified Which statement would create the table?

• A. CREATE TABLE Sales(SALESID NUMBER (4),STOREID NUMBER (4),ITEMID NUMBER (4),QTY NUMBER DEFAULT = 1,SLSDATE DATE DEFAULT SYSDATE,PAYMENT VARCHAR2(30) DEFAULT = "CASH");
• B. CREATE TABLE Sales(SALESID NUMBER (4),STOREID NUMBER (4),ITEMID NUMBER (4),QTY NUMBER DEFAULT = 1,SLSDATE DATE DEFAULT 'SYSDATE',PAYMENT VARCHAR2(30) DEFAULT CASH);
• C. CREATE TABLE Sales(SALESID NUMBER (4),STOREID NUMBER (4),ITEMID NUMBER (4),qty NUMBER DEFAULT = 1,SLSDATE DATE DEFAULT SYSDATE,PAYMENT VARCHAR2(30) DEFAULT = "CASH");
• D. Create Table sales(salesid NUMBER (4),Storeid NUMBER (4),Itemid NUMBER (4),QTY NUMBER DEFAULT 1,Slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT 'CASH');

Answer: D