1Z0-061 Free Practice Test

- (Topic 1)
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?

1. A. WHERE promo_name NOT LIKE \'T%\' AND promo_name NOT LIKE \'N%\' AND promo_cost > 20000 AND promo_end_date > ‘1-JAN-01\'
2. B. WHERE promo_name NOT LIKE \'T%\' OR promo_name NOT LIKE \'N%\' AND promo_cost >20000 AND promo_end_date > ‘1-JAN-01’
3. C. WHERE (promo_name NOT LIKE \'T%\' AND promo_name NOT LIKE \'N%\')OR promo_cost >20000 OR promo_end_date > ‘1-JAN-01’
4. D. WHERE (promo_name NOT LIKE \'%T%\' OR promo_name NOT LIKE \'%N%\') AND(promo_cost >20000 AND promo_end_date > ‘1-JAN-01’)

Correct Answer: A
We must use the AND operator, not the OR operator, as all the Boolean statements must be true to meet the conditions.

- (Topic 4)
Examine the following query:
<>

1. A. It displays 5 percent of the products with the highest amount sold.
2. B. It displays the first 5 percent of the rows from the SALES table.
3. C. It displays 5 percent of the products with the lowest amount sold.
4. D. It results in an error because the ORDER BY clause should be the last clause.

Correct Answer: C

- (Topic 2)
Examine the structure of the STUDENTS table:
<>>

1. A. SELECT . student_ id, marks, ROWNUM "Rank"FROM . studentsWHERE . ROWNUM<= 10AND . finish_date BETWEEN \'01-JAN-99\' AND \'31-DEC-99AND . course_id = \'INT_SQL\'ORDER BY . marks DESC;
2. B. SELECT . student_id, marks, ROWID "Rank". FROM . student
3. C. WHERE . ROWID <=10. AND . finish_date BETWEEN \'01-JAN-99\' AND \'31-DEC-99\'. AND . course_id = \'INT_SQL\'. ORDER BY . marks;
4. D. SELECT . student_id, marks, ROWNUM "Rank". FROM . (SELECT student_id, mark
5. E. FROM . student
6. F. WHERE . ROWNUM <= 10. AND . finish_date BETWEEN \'01-JAN-99\' AND \'31-DEC-. 99\'. AND . course_id = \'INT_SQL\'. ORDER BY . marks DESC);
7. G. SELECT . student_id, marks, ROWNUM "Rank”. FROM . (SELECT student_id, mark
8. H. FROM . student
9. I. WHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC-99’. AND course_id = ‘INT_SQL’. ORDER BY marks DESC). WHERE . ROWNUM <= 10 ;.
10. J. SELEC
11. K. student id, marks, ROWNUM “Rank”. FRO
12. L. (SELECT student_id, mark
13. M. FROM student
14. N. ORDER BY marks). WHER
15. O. ROWNUM <= 10. AN
16. P. finish date BETWEEN ’01-JAN-99’ AND ’31-DEC-99’. AN
17. Q. course_id = ‘INT_SQL’;

Correct Answer: D

- (Topic 3)
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
<>>

1. A. INSERT INTO ordersVALUES (1, \'10-mar-2007\', \'direct\',(SELECT customer_idFROM customersWHERE cust_last_name=\'Roberts\' ANDcredit_limit=600), 1000);
2. B. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name=\'Roberts\' ANDcredit_limit=600), order_total)VALUES(1, \'10-mar-2007\', \'direct\', &&customer_id, 1000);
3. C. INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND c.cust_last_name=\'Roberts\' ANDc.credit_limit=600 )VALUES (1, \'10-mar-2007\', \'direct\',(SELECT customer_idFROM customersWHERE cust_last_name=\'Roberts\' ANDcredit_limit=600), 1000);
4. D. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name=\'Roberts\' ANDcredit_limit=600), order_total)VALUES(1, \'10-mar-2007\', \'direct\', &customer_id, 1000);

Correct Answer: A

- (Topic 1)
You need to display the first names of all customers from the customers table that contain the character 'e' and have the character 'a' in the second last position.
Which query would give the required output?

1. A. Option A
2. B. Option B
3. C. Option C
4. D. Option D

Correct Answer: A
The SUBSTR(string, start position, number of characters) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position:
substr(\'http://www.domain.com\', 12, 6) = domain
The position at which the first character of the returned string begins. When position is 0 (zero), then it is treated as 1.
When position is positive, then the function counts from the beginning of string to find the first character.
When position is negative, then the function counts backward from the end of string. substring_length
The length of the returned string. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters.
SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points. When you do not specify a value for this argument, then the function
The INSTR(source string, search item, [start position], [nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins: instr(\'http://www.domain.com\', \'.\', 1, 2) = 18