A Review Of Exact 1Z0-061 training tools

The article at Testaimer.com going over http://www.testaimer.com/1Z0-061-test is very comprehensive.

Q21. The customers table has the following structure: 

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? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 


Q22. View the Exhibit and examine the structures of the employees and departments tables. 

You want to update the employees table as follows: 

-Update only those employees who work in Boston or Seattle (locations 2900 and 2700). 

-Set department_id for these employees to the department_id corresponding to London (location_id 2100). 

-Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department. 

-Set the employees' commission in iocation_id 2100 to 1.5 times the average commission of their department. 

You issue the following command: 

What is the outcome? 

A. It executes successfully and gives the correct result. 

B. It executes successfully but does not give the correct result. 

C. It generates an error because a subquery cannot have a join condition in an update statement. 

D. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an update statement. 


Q23. Examine the structure and data of the CUST_TRANS table: 

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

A. SELECT transdate + '10' FROM cust_trans; 

B. SELECT * FROM cust_trans WHERE transdate = '01-01-07' 

C. SELECT transamt FROM cust_trans WHERE custno > "11"; 

D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07' 

E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000; 

Answer: A,C,D 

Q24. In the customers table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'Abigail'. 

Evaluate the following query: 

What would be the outcome? 

A. Abigail PA 

B. Abigail Pa 

C. Abigail IS 

D. An error message 


Q25. Examine the structure of the transactions table: 

You want to display the date, time, and transaction amount of transactions that where done before 12 noon. The value zero should be displayed for transactions where the transaction amount has not been entered. 

Which query gives the required result? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 


Q26. You want to create a table employees in which the values of columns EMPLOYEES_ID and LOGIN_ID must be unique and not null. Which two SQL statements would create the required table? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

E. Option E 

F. Option F 

Answer: D,E 

Q27. Examine the data in the PROMO_BEGIN_DATE column of the promotions table: 

You want to display the number of promotions started in 1999 and 2000. Which query gives the correct output? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 


Q28. Which statement is true regarding the default behavior of the order by clause? 

A. In a character sort, the values are case-sensitive. 

B. NULL values are not considered at all by the sort operation. 

C. Only those columns that are specified in the select list can be used in the order by clause. 

D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions. 



Character Strings and Dates 

Character strings and date values are enclosed with single quotation marks. 

Character values are case-sensitive and date values are format-sensitive. 

The default date display format is DD-MON-RR. 

Q29. 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? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 



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 

Q30. View the Exhibit and examine the data in the costs table. 

You need to generate a report that displays the IDs of all products in the costs table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query: 

Which statement is true regarding the above query? 

A. It executes and produces the required result. 

B. It produces an error because an expression cannot be used in the order by clause. 

C. It produces an error because the DESC option cannot be used with an expression in the order by clause. 

D. It produces an error because the expression in the ORDER by clause should also be specified in the SELECT clause.