Act now and download your Oracle 1z0 061 pdf test today! Do not waste time for the worthless Oracle oracle 1z0 061 tutorials. Download Avant-garde Oracle Oracle Database 12c SQL Fundamentals exam with real questions and answers and begin to learn Oracle 1z0 061 dumps with a classic professional.

Q1. Examine the structure of the employees table. 

You want to display the maximum and minimum salaries of employees hired 1 year ago. Which two statements would get the correct output? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: B,D 

Explanation: 

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z1 0.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_subselectexamples.htm.


Q2. Examine the types and examples of relationships that follow: 

1. One-to-one a) Teacher to students 

2. One-to-many b) Employees to Manager 

3. Many-to-one c) Person to SSN 

4. Many-to-many d) Customers to products 

Which option indicates the correctly matched relationships? 

A. 1-a, 2-b, 3-c, and 4-d 

B. 1-c, 2-d, 3-a, and 4-b 

C. 1-c, 2-a, 3-b, and 4-d 

D. 1-d, 2-b, 3-a, and 4-c 

Answer:


Q3. Examine the structure of the employees table: 

There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID. 

You want to display the name, joining date, and manager for all the employees. Newly hired employees are yet to be assigned a department or a manager. For them, 'No Manager1 should be displayed in the manager column. 

Which SQL query gets the required output? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:

Reference: http://ivrainbow65.blogspot.com/ 


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

Answer:

Explanation: 

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 


Q5. 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: 


Q6. You issue the following command to alter the country column in the departments table: Which statement is true? 

A. It produces an error because column definitions cannot be altered to add default values. 

B. It executes successfully and all the rows that have a null value for the country column will be updated with the value 'USA'. 

C. It executes successfully. The modification to add the default value takes effect only from subsequent insertions to the table. 

D. It produces an error because the data type for the column is not specified. 

Answer:


Q7. Examine the structure of the customers table: 

CUSTNO is the primary key in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names. 

Which two methods can you use to get the required result? 

A. Self-join 

B. Subquery 

C. Full outer-join with self-join 

D. Left outer-join with self-join 

E. Right outer-join with self-join 

Answer: A,B 


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

Answer:

Explanation: 

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. 


Q9. Which three tasks can be performed using SQL functions built into Oracle Database? 

A. Displaying a date in a nondefault format 

B. Finding the number of characters in an expression 

C. Substituting a character string in a text expression with a specified string 

D. Combining more than two columns or expressions into a single column in the output 

Answer: A,B,C 


Q10. Which two statements are true regarding the count function? 

A. The count function can be used only for CHAR, VARCHAR2, and NUMBER data types. 

B. Count (*) returns the number of rows including duplicate rows and rows containing null value in any of the columns. 

C. Count (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column. 

D. Count (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column. 

E. A select statement using the COUNT function with a DISTINCT keyword cannot have a where clause. 

Answer: B,D 

Explanation: 

Using the COUNT Function 

The COUNT function has three formats: 

COUNT(*) 

COUNT(expr) 

COUNT(DISTINCT expr) 

COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT 

statement, including duplicate rows and rows containing null values in any of the columns. 

If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause. 

In contrast, 

COUNT(expr) returns the number of non-null values that are in the column identified by expr. 

COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.