Exam Code: 1Z0-061 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database 12c SQL Fundamentals
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1Z0-061 Exam.

2017 Apr 1Z0-061 practice question

Q31. You need to create a table with the following column specifications: 

1. Employee ID (numeric data type) for each employee 

2. Employee Name (character data type) that stores the employee name 

3. Hire date, which stores the date of joining the organization for each employee 

4. Status (character data type), that contains the value 'active1 if no data is entered 

5. Resume (character large object [CLOB] data type), which contains the resume submitted by the employee 

Which is the correct syntax to create this table? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:

Explanation: 

CLOB Character data (up to 4 GB) 

NUMBER [(p, s)] Number having precision p and scale s (Precision is the total number of decimal digits and scale is the number of digits to the right of the decimal point; precision can range from 1 to 38, and scale can range from –84 to 127.) 


Q32. You execute the following commands: 

For which substitution variables are you prompted for the input? 

A. None, because no input required 

B. Both the substitution variables 'hiredate' and 'mgr_id\ 

C. Only 'hiredate' 

D. Only 'mgr_id' 

Answer:


Q33. Which two statements are true regarding single row functions? 

A. MOD: returns the quotient of a division B. TRUNC: can be used with number and date values 

C. CONCAT: can be used to combine any number of values 

D. SYSDATE: returns the database server current date and time 

E. INSTR: can be used to find only the first occurrence of a character in a string 

F. TRIM: can be used to remove all the occurrences of a character from a string 

Answer: B,D 

Explanation: 

ROUND: Rounds value to a specified decimal TRUNC: Truncates value to a specified decimal MOD: Returns remainder of division SYSDATE is a date function that returns the current database server date and time. 

Date-Manipulation Functions Date functions operate on Oracle dates. All date functions return a value of the DATE data type except MONTHS_BETWEEN, which returns a numeric value. MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month. ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative. NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string. LAST_DAY(date): Finds the date of the last day of the month that contains date The above list is a subset of the available date functions. ROUND and TRUNC number functions can also be used to manipulate the date values as shown below: ROUND(date[, 'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day. TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day. 

The CONCAT Function The CONCAT function joins two character literals, columns, or expressions to yield one larger character expression. Numeric and date literals are implicitly cast as characters when they occur as parameters to the CONCAT function. Numeric or date expressions are evaluated before being converted to strings ready to be concatenated. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or expressions resulting in character values. 

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 

The TRIM function literally trims off leading or trailing (or both) character strings from a 

given source string: 


Q34. You need to produce a report where each customer's credit limit has been incremented by $1000. In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New credit Limit. The column headings should have only the first letter of each word in uppercase. 

Which statement would accomplish this requirement? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:

Explanation: 

A column alias: 

-Renames a column heading 

-Is useful with calculations 

-Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.) 

-Requires double quotation marks if it contains spaces or special characters, or if it is case sensitive. 


Q35. In which three situations does a transaction complete? 

A. When a DELETE statement is executed 

B. When a ROLLBACK command is executed 

C. When a PL/SQL anonymous block is executed 

D. When a data definition language (DDL) statement is executed 

E. When a TRUNCATE statement is executed after the pending transaction 

Answer: B,D,E 


Up to date 1Z0-061 test preparation:

Q36. 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 


Q37. YOU need to display the date ll-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven'. 

Which SQL statement would give the required result? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:


Q38. View the Exhibit and evaluate the structure and data in the CUST_STATUS table. You issue the following SQL statement: 

Which statement is true regarding the execution of the above query? 

A. It produces an error because the AMT_SPENT column contains a null value. 

B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT. 

C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null. 

D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function. 

Answer:

Explanation: 

The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned. 


Q39. View the Exhibits and examine the structures of the products and sales tables. 

Which two SQL statements would give the same output? A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: A,C 


Q40. You want to display the date for the first Monday of the next month and issue the following command: 

What is the outcome? 

A. It executes successfully and returns the correct result. 

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

C. It generates an error because TO_CHAR should be replaced with TO_DATE. 

D. It generates an error because rrrr should be replaced by rr in the format string. 

E. It generates an error because fm and double quotation marks should not be used in the format string. 

Answer: