Passing exam with 1z1-061 latest training materials

View the Exhibit and examine the structure of the PRODUCTS table.
You want to display only those product names with their list prices where the list price is at least double the minimum price.
The report should start with the product name having the maximum list price satisfying this condition.
Evaluate the following SQL statement:
SQL>SELECT prod_name, prod_list_price
FROM products
WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output?
(Choose all that apply.)

• A. ORDER BY (2*prod_min_price)DESC, prod_name;
• B. ORDER BY prod_name, (2*prod_min_price)DESC;
• C. ORDER BY prod_list_price DESC, prod_name;
• D. ORDER BY prod_list_price DESC, prod_name DESC;
• E. ORDER BY prod_name DESC, prod_list_price DESC;

Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

• A. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
• B. The WHERE clause can be used to exclude rows after dividing them into groups.
• C. The WHERE clause can be used to exclude rows before dividing them into groups.
• D. The HAVING clause can be used with aggregate functions in subqueries.
• E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.

Examine the structure of the sales table:

Evaluate the following create table statement:

Which two statements are true about the creation of the SALES1 table?

• A. The SALES1 table would not be created because column-specified names in the select and create table clauses do not match,
• B. The SALES1 table is created with no rows but only a structure.
• C. The SALES1 table would have not null and unique constraints on the specified columns.
• D. The SALES1 table would not be created because of the invalid where clause.
• E. The SALES1 table would have primary key and unique constraints on the specified columns.

View the Exhibit and examine the structure of the promotions table.
You need to generate a report of all promos from the promotions table based on the following conditions:
1. The promo name should not begin with 'T' or 'N'.
2. The promo should cost more than $20000.
3. The promo should have ended after 1st January 2001.
Which where clause would give the required result?

• A. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND
  promo_cost > 20000 AND promo_end_date > '1-JAN-01'
• B. WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND
  promo_cost >20000 AND promo_end_date > '1-JAN-01'
• C. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR
  promo_cost >20000 OR promo_end_date > '1-JAN-01'
• D. WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%')
  AND(promo_cost >20000 AND promo_end_date > '1-JAN-01')

You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the default format of dd-mon-rr. Which two SQL statements would give the required output? (Choose two.)

• A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL:
• B. SELECT TO_CHAR(SYSDATE. 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL:
• C. SELECT SYSDATE - TOJDATE(X)1/JANUARY/2007") FROM DUAL:
• D. SELECT SYSDATE - TOJDATE('01-JANUARY-2007') FROM DUAL:
• E. SELECT TO_DATE(SYSDATE. *DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL: