Free Download Pass4sure and Lead2pass 1Z0-051 Exam Question with PDF & VCE (71-80)

QUESTION 71
View the Exhibit and examine the description of SALES and PROMOTIONS tables.
You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values.
Which DELETE statements are valid? (Choose all that apply.)

image

A.    DELETE
FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’)
AND promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘everyday low price’);
B.    DELETE
FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’)
OR promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘everyday low price’);
C.    DELETE
FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’
OR promo_name = ‘everyday low price’);
D.    D DELETE
FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name IN (‘blowout sale’,’everyday low price’));

Answer: BCD

QUESTION 72
View the Exhibit and examine the description for the PRODUCTS and SALES table.
PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table.
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?

image

A.    DELETE
FROM products
WHERE prod_id = (SELECT prod_id
FROM sales
WHERE time_id – 3*365 = SYSDATE );
B.    DELETE
FROM products
WHERE prod_id = (SELECT prod_id
FROM sales
WHERE SYSDATE >= time_id – 3*365 );
C.    DELETE
FROM products
WHERE prod_id IN (SELECT prod_id
FROM sales
WHERE SYSDATE – 3*365 >= time_id);
D.    DELETE
FROM products
WHERE prod_id IN (SELECT prod_id
FROM sales
WHERE time_id >= SYSDATE – 3*365 );

Answer: C

QUESTION 73
View the Exhibit and examine the structure and data in the INVOICE table.

image
Which two SQL statements would execute successfully? (Choose two.)

A.    SELECT AVG(inv_date )
FROM invoice;
B.    SELECT MAX(inv_date),MIN(cust_id)
FROM invoice;
C.    SELECT MAX(AVG(SYSDATE – inv_date))
FROM invoice;
D.    SELECT AVG( inv_date – SYSDATE), AVG(inv_amt)
FROM invoice;

Answer: BD

QUESTION 74
Which two statements are true regarding the COUNT function? (Choose two.)

A.    The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.
B.    COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in
any of the columns.
C.    COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL
value in the CUST_ID column.
D.    COUNT(DISTINCT inv_amt)returns the number of rows excluding rows containing duplicates and
NULL values in the INV_AMT column.
E.    A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

Answer: BD
Explanation:
Using the COUNT Function
The COUNT function has three formats:
COUNT(*)
COUNT(expr)
COUNT(DISTINCT expr)
COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause.
In contrast,
COUNT(expr) returns the number of non-null values that are in the column identified by expr.
COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.

QUESTION 75
View the Exhibit and examine the structure of the CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows the average credit limit for customers in WASHINGTON and NEW YORK.
Which SQL statement would produce the required result?

image

A.    SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’)
GROUP BY cust_credit_limit, cust_city;
B.    SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’)
GROUP BY cust_city,cust_credit_limit;
C.    SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’)
GROUP BY cust_city;
D.    SELECT cust_city, AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’);

Answer: C

QUESTION 76
Examine the structure of the MARKS table:
name                 Null           Type
—————   ————  —————–
STUDENT_ID          NOT NULL      VARCHAR2(4)
STUDENT_NAME                       VARCHAR2(25)
SUBJECT1                            NUMBER(3)
SUBJECT2                            NUMBER(3)
SUBJECT3                            NUMBER(3)
Which two statements would execute successfully? (Choose two.)

A.    SELECT student_name,subject1
FROM marks
WHERE subject1 > AVG(subject1);
B.    SELECT student_name,SUM(subject1)
FROM marks
WHERE student_name LIKE ‘R%’;
C.    SELECT SUM(subject1+subject2+subject3)
FROM marks
WHERE student_name IS NULL;
D.    SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)
FROM marks
WHERE subject1 > subject2;

Answer: CD

QUESTION 77
View the Exhibit and examine the structure of the CUSTOMERS table.
Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?

image

A.    SELECT cust_city, cust_income_level, MAX(cust_credit_limit ) FROM customers
GROUP BY cust_city, cust_income_level, cust_credit_limit;
B.    SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers
GROUP BY cust_city, cust_income_level;
C.    SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers
GROUP BY cust_credit_limit, cust_income_level, cust_city ;
D.    SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers
GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);

Answer: B

QUESTION 78
View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
SQL>SELECT promo_category, AVG(promo_cost) Avg_Cost, AVG(promo_cost)*.25 Avg_Overhead
FROM promotions
WHERE UPPER(promo_category) IN (‘TV’, ‘INTERNET’,’POST’)
GROUP BY Avg_Cost
ORDER BY Avg_Overhead;
The above query generates an error on execution.
Which clause in the above SQL statement causes the error?

image

A.    WHERE
B.    SELECT
C.    GROUP BY
D.    ORDER BY

Answer: C

QUESTION 79
Examine the structure of the ORDERS table:
Name           Null             Type
———     ———-     ——————–
ORDER_ID      NOT NULL        NUMBER(12)
ORDER_DATE    NOT NULL        TIMESTAMP(6)
CUSTOMER_ID   NOT NULL        NUMBER(6)
ORDER_STATUS                   NUMBER(2)
ORDER_TOTAL                    NUMBER(8,2)
You want to find the total value of all the orders for each year and issue the following 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 outcome?

A.    It executes successfully and gives the correct output.
B.    It gives an error because the TO_CHAR function is not valid.
C.    It executes successfully but does not give the correct output.
D.    It gives an error because the data type conversion in the SELECT list does not match the data type
conversion in the GROUP BY clause.

Answer: D

QUESTION 80
View the Exhibit and examine the structure of the SALES table.
The following query is written to retrieve all those product ID s from the SALES table that have more than 55000 sold and have been ordered more than 10 times.
SQL> SELECT prod_id
FROM sales
WHERE quantity_sold > 55000 AND COUNT(*)>10
GROUP BY prod_id
HAVING COUNT(*)>10;
Which statement is true regarding this SQL statement?

image

A.    It executes successfully and generates the required result.
B.    It produces an error because COUNT(*) should be specified in the SELECT clause also.
C.    It produces an error because COUNT(*) should be only in the HAVING clause and not in the
WHERE clause.
D.    It executes successfully but produces no result because COUNT(prod_id) should be used instead
of COUNT(*).

Answer: C

…go to http://www.lead2pass.com/1z0-051.html to download the full version Q&As.