Online Access Free 1Z0-051 Practice Test

View the Exhibit and examine the structure of the PRODUCTS tables.
You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category.
Which query would give the correct output?

• A.SELECT prod_category,avg(prod_list_price) FROM products HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
• B.SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
• C.SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
• D.SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products);

View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the query statement:

What would be the outcome of the above statement?

• A.It produces an error because the condition on CUST_LAST_NAME is invalid.
• B.It produces an error because the AND operator cannot be used to combine multiple BETWEEN clauses.
• C.It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null values.
• D.It executes successfully.

Examine the description of the EMP_DETAILS table given below: Exhibit:

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)

• A.You cannot add a new column to the table with LONG as the data type
• B.An EMP_IMAGE column can be included in the GROUP BY clause
• C.You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column
• D.An EMP_IMAGE column cannot be included in the ORDER BY clause

Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?

• A.SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
  FROM customers
  WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
• B.SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
  FROM customers
  WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
• C.SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
  FROM customers
  WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
• D.SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
  FROM customers
  WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');

Here is the structure and data of the CUST_TRANS table: Exhibit:

Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.
Which three SQL statements would execute successfully? (Choose three.)

• A.SELECT * FROM cust_trans WHERE transdate = '01-01-07';
• B.SELECT transamt FROM cust_trans WHERE custno > '11';
• C.SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
• D.SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;
• E.SELECT transdate + '10' FROM cust_trans;