1z0-071 – Prepaway Exam Dumps

Q173. View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. (Choose two.)

Evaluate this SQL statement:
ALTER TABLE emp
DROP COLUMN first_name;
Which two statements are true?

The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.

The FIRST_NAME column would be dropped provided at least one column remains in the table.

The FIRST_NAME column would be dropped provided it does not contain any data.

The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.

Q174. Which two statements are true about Oracle databases and SQL?

Updates performed by a database user can be rolled back by another user by using the ROLLBACK command.

The database guarantees read consistency at select level on user-created tablers.

When you execute an UPDATE statement, the database instance locks each updated row.

A query can access only tables within the same schema.

A user can be the owner of multiple schemas In the same database.

Q175. Examine the description of the ORDERS table:

Which three statements execute successfully?

(SELECT * FROM orders
UNION ALL
SELECT* FROM invoices) ORDER BY order _id;

SELECE order _id, order _ date FRON orders
LNTERSECT
SELECT invoice_ id, invoice_ id, order_ date FROM orders

SELECT order_ id, invoice_ data order_ date FROM orders
MINUS
SELECT invoice_ id, invoice_ data FROM invoices ORDER BY invoice_ id;

SELECT * FROM orders ORDER BY order_ id
INTERSEOT
SELECT * FROM invoices ORDER BY invoice_ id;

SELECT order_ id, order_ data FROM orders
UNION ALL
SELECT invoice_ id, invoice_ data FROM invoices ORDER BY order_ id;

SELECT * FROM orders
MINUS
SELECT * FROM INVOICES ORDER BY 1

SELECT * FROM orders ORDER BY order_ id
UNION
SELECT * FROM invoices;

Q176. The PRODUCT_INFORMATIONtable has a UNIT_PRICEcolumn of data type NUMBER (8, 2).
Evaluate this SQL statement:
SELECT TO_CHAR(unit_price, ‘$9,999’) FROM product_information;
Which two statements are true about the output? (Choose two.)

A row whose UNIT_PRICEcolumn contains the value 1023.99will be displayed as $1,024.

A row whose UNIT_PRICEcolumn contains the value 10235.99will be displayed as $1,0236.

A row whose UNIT_PRICEcolumn contains the value 10235.99will be displayed as #######.

A row whose UNIT_PRICEcolumn contains the value 10235.99will be displayed as $1,023.

A row whose UNIT_PRICEcolumn contains the value 1023.99will be displayed as $1,023.

Q177. Which three privileges can be restricted to a subset of columns in a table?

ALTER

REFERENCES

UPDATE

SELECT

INDEX

INSERT

DELETE

Q178. View the Exhibit and examine the data in the PRODUCTS table.

You must display product names from the PRODUCTS table that belong to the ‘Software/other’ category with minimum prices as either $2000 or $4000 and with no unit of measure.
You issue this query:

Which statement is true?

It executes successfully but returns no result.

It executes successfully and returns the required result.

It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.

It generates an error because the condition specified for the PROD_CATEGORY column is not valid.

Q179. View and Exhibit and examine the structure and data in the INVOICE table. (Choose two.)

Which two statements are true regarding data type conversion in query expressions?

inv_date = ’15-february-2008′ :uses implicit conversion

inv_amt = ‘0255982’ : requires explicit conversion

inv_date > ’01-02-2008′ : uses implicit conversion

CONCAT(inv_amt, inv_date) : requires explicit conversion

inv_no BETWEEN ‘101’ AND ‘110’ : uses implicit conversion

Q180. Which three are true about multiple INSERT statements?

They can be performed only by using a subquery.

They can be performed on relational tables.

They can be performed on views.

They can be performed on remote tables.

They can be performed on external tables using SQL*Loader.

They can insert each computed row into more than one table.

Q181. Examine the description of the SALES table:

The SALES table has 5,000 rows.
Examine this statement:
CREATE TABLE sales1 (prod id, cust_id, quantity_sold, price)
AS
SELECT product_id, customer_id, quantity_sold, price
FROM sales
WHERE 1=1
Which two statements are true?

SALES1 is created with 1 row.

SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table.

SALES1 Is created with 5,000 rows.

SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.

Q182. Choose the best answer.
Examine the description of the EMPLOYEES table:

Which query is valid?

SELECT dept_id, join_date,SUM(salary) FROM employees GROUP BY dept_id, join_date;

SELECT depe_id,join_date,SUM(salary) FROM employees GROUP BY dept_id:

SELECT dept_id,MAX(AVG(salary)) FROM employees GROUP BY dept_id;

SELECT dept_id,AVG(MAX(salary)) FROM employees GROUP BY dapt_id;

Q183. View the Exhibit and examine, the description for the SALES and CHANNELS tables. (Choose the best answer.)

You issued this SQL statement:
INSERT INTO SALES VALUES (23, 2300, SYSDATE,
(SELECT CAHNNEL_ID
FROM CHANNELS
WHERE CHANNEL_DESC=’DIRECT SALES’), 12, 1, 500);
Which statement is true regarding the result?

The statement will fail because the sub-query in the VALUES clause is not enclosed within single quotation marks.

The statement will fail because a subquery cannot be used in a VALUES clause.

The statement will execute and a new row will be inserted in the SALES table.

The statement will fail because the VALUES clause is not required with the subquery.

Q184. Examine this query:
SELECT SUBSTR (SYSDATE,1,5) ‘Result’ FROM DUAL
Which statement is true?

It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)

It fails unless the expression is modified to SUBSTR (TO_ CHAR(SYSDATE),1,5)

It fails unless the expression is modified to SUBSTR (TO_ CHAR(TRUNC(SYSDATE)),1,5)

It executes successfully with an implicit data type conversion

Q185. Examine the data in the INVOICES table:

Examine the data in the CURRENCIES table:
CURRENCY_CODE
————-
JPY
GPB
CAD
EUR
USD
Which query returns the currencies in CURRENCIES that are not present in INVOICES?

SELECT currency_ code FROM currencies
MINUS
SELECT currency_ code FROM invoices;

SELECT * FROM currencies
WHERE NOT EXISTS (
SELECT NULL FROM invoices WHERE currency_ code = currency_ code);

SELECT currency_ code FROM currencies
INTERSECT
SELECT currency_ code FROM invoices;

SELECT * FROM currencies
MINUS
SELECT * FROM invoices;

Q186. Examine this incomplete query:
SELECT DATA’2019-01-01’+<INTERVAL CLAUSE>
FROM DUAL;
Which three clauses can replace<INTERVAL CLAUSE>ti add 22 hours to the date?

INTERVAL ’12:00′

INTERVAL’0,5’DAY

INTERVAL’12’ HOUR

INTERVAL’720’MINUTE

INTERVAL’0 12’DAY TO HOUR

INTERVAL’11:60’HOUR TO MINUTE

Q187. View the Exhibit and examine the description of the ORDERS table.

Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

WHERE order_date_IN ( TO_DATE(‘OCT 21 2003′,’MON DD YYYY’), TO_CHAR(‘NOV 21
2003′,’MON DD YYYY’) )

WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),’MON DD YYYY’)

WHERE TO_CHAR(order_date,’MON DD YYYY’) = ‘JAN 20 2003’

WHERE order_date > TO_DATE(‘JUL 10 2006′,’MON DD YYYY’)

Q188. Which two are true about granting object privileges on tables, views, and sequences?

DELETE can be granted on tables, views, and sequences.

REFERENCES can be grantrd only on tables.

INSERT can be granted only on tables and sequences.

SELECT can be granted on tables, views, and sequences.

ALTER can be granted only on tables and sequences.

Q189. A subquery is called a single-row subquery when _______.

There is only one subquery in the outer query and the inner query returns one or more values

The inner query returns a single value to the outer query.

The inner query uses an aggregating function and returns one or more values.

The inner query returns one or more values and the outer query returns a single value.

Q190. Which two statements are true about the SET VERIFY ON command?

It displays values for variables created by the DEFINE command.

It can be used in SQL Developer and SQL*Plus.

It can be used only in SQL*plus.

It displays values for variables prefixed with &&.

It displays values for variables used only in the WHERE clause of a query.

Prepaway Exam Dumps

Category: 1z0-071

Pass Oracle 1z0-071 exam Dumps 100 Pass Guarantee With Latest Demo [Q173-Q190]

Category Archives: 1z0-071