1Z0-071 Free Practice Test

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?

1. A. SELECT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
2. B. SELECT DISTINCT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
3. C. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
4. D. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers

Correct Answer: B

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?

1. A. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS WITH TIES;
2. B. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;
3. C. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;
4. D. SELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS ONLY;

Correct Answer: A

Examine the structure of the CUSTOMERS table: (Choose two.)

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?

1. A. Subquery
2. B. Self-join
3. C. Full outer-join with self-join
4. D. Left outer-join with self-join
5. E. Right outer-join with self-join

Correct Answer: AB

Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED (order_date); Which statement is true?

1. A. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
2. B. The ORDER_DATE column should be empty for the ALTER TABLE command to execute succsessfully.
3. C. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
4. D. The DESCRIBE command would still display the ORDER_DATE column.

Correct Answer: A

View the Exhibit and examine the structure in the EMPLOYEES tables.

Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees
WHERE department_id= 50 ORDER BY department_id UNION
SELECT employee_id, department_id FROM employees
WHERE department_id=90 UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=10;
What would be the outcome of the above SQL statement?

1. A. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
2. B. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
3. C. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
4. D. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.

Correct Answer: D