Exactprep 1z0-071 Questions

Page: 2 / 24

Question 8

Examine this SQL statement:

SELECT cust_id, cus_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, any one of which can complete the query successfully.

Options:

ORDERBY 2, 1

ORDER BY "CUST_NO"

ORDER BY 2,cust_id

ORDER BY CUST_NO

ORDER BY "Last Name"

Question 9

Which three are true about subqueries?

Options:

A subquery can be used in a WHERE clause.

A subquery can be used in a HAVING clause.

=ANY can only evaluate the argument against a subcjuery if it returns two or more values.

A subquery cannot be used in a FROM clause.

< any returns true if the argument is less than the lowest value returned by the subquery.

A subquery cannot be used in the select list.

Question 10

Examine the description of the CUSTOMERS table:

You need to display last names and credit limits of all customers whose last name starts with A or B In lower or upper case, and whose credit limit is below 1000.

Examine this partial query:

SELECT cust_last_nare, cust_credit_limit FROM customers

Which two WHERE conditions give the required result?

Options:

WHERE UPPER(cust_last_name) IN ('A%', 'B%') AND cust_credit_limit < 1000:

WHERE (INITCAP(cust_last_name) LIKE ‘A%' OR ITITCAP(cust_last_name) LIKE ‘B%') AND cust_credit_limit < 1000

WHERE UPPER(cust_last_name) BETWEEN UPPER('A%' AND 'B%’) AND ROUND(cust_credit_limit) < 1000;

WHERE (UPPER(cust_last_name) LIKE 'A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND ROUND(cust_credit_limit) < 1000;

WHERE (UPPER(cust_last_name) like INITCAP ('A') OR UPPER(cust_last_name) like INITCAP('B')) AND ROUND(cust_credit_limit) < ROUND(1000) ;

Question 11

Which three statements are true about performing DML operations on a view with no INSTEAD OF triggers defined?

Options:

Insert statements can always be done on a table through a view.

The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.

Delete statements can always be done on a table through a view.

Views cannot be used to add rows to an underlying table If the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.

Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the primary key columns are not referenced in the defining query of the view.

Views cannot be used to add or modify rows in an underlying table If the defining query of the view contains the DISTINCT keyword.