Oracle 1Z0-071 Quiz 21 Topic 10 Questions 101-105

Question: 1

Examine the structure and data in the PRICE_LIST table:

q1_1Z0-071

You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.

Which SQL statement would give the required result?

ASELECT TO_CHAR (prod_price* .25, '$99,999.99')FROM PRICE_LIST

BSELECT TO_CHAR (TO_NUMBER(prod_price) * .25, '$99,999.00')FROM PRICE_LIST

CSELECT TO_CHAR (TO_NUMBER(prod_price, '$99,999.99') * . 25, '$99,999.00')FROM PRICE_LIST

DSELECT TO_NUMBER (TO_NUMBER(prod_price, '$99,999.99') * . 25, '$99,999.00')FROM PRICE_LIST

Show Answer

Question: 2

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

q2_1Z0-071

Which statement would add a column called PRICE, which cannot contain NULL?

AALTER TABLE productsADD price NUMBER(8,2) NOT NULL;

BALTER TABLE productsADD price NUMBER(8,2) DEFAULT NOT NULL;

CALTER TABLE productsADD price NUMBER(8,2) DEFAULT 0 NOT NULL;

DALTER TABLE productsADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL.

Show Answer

Question: 3

The customers table has the following structure:

q3_1Z0-071

You need to write a query that does the following tasks:

1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.

2. Only those customers whose income level has a value should be considered.

3. Customers whose tax amount is null should not be considered.

Which statement accomplishes all the required tasks?

ASELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE cust_income_level IS NOT NULL ANDtax_amount IS NOT NULL;

BSELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE cust_income_level IS NOT NULL ANDcust_credit_limit IS NOT NULL;

CSELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE cust_income_level <> NULL ANDtax_amount <> NULL;

DSELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE (cust_income_level, tax_amount) IS NOT NULL;

Show Answer

Question: 4

View the Exhibit and examine the structure of the SALES table.

q4_1Z0-071

The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 items.

q4_1Z0-071

Which statement is true regarding this SQL statement?

AIt executes successfully and generates the required result.

BIt produces an error because COUNT (*) should be specified in the SELECT clause also.

CIt produces an error because COUNT (*) should be only in the HAVING clause and not in the WHERE clause.

DIt executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).

Show Answer

Question: 5

View the Exhibit and examine the description for the PRODUCTS and SALES table.

q5_1Z0-071

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table with ON DELETE CASCADE option. The SALES table contains data for the last three years. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years.

Which is the valid DELETE statement?

ADELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE time_id - 3*365 = SYSDATE );

BB. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE SYSDATE >= time_id - 3*365 );

CDELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE SYSDATE - 3*365 >= time_id);

DDELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE time_id >= SYSDATE - 3*365 );

Show Answer

Oracle 1Z0-071 Quiz:21 Topic:10 Questions:101-105