Resources to oracle database 12c sql fundamentals 1z0 061 pdf free download

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

2017 NEW RECOMMEND

Free VCE & PDF File for Oracle 1Z0-061 Real Exam
(Full Version!)

Pass on Your First TRY 100% Money Back Guarantee Realistic Practice Exam Questions

Free Instant Download NEW 1Z0-061 Exam Dumps (PDF & VCE):
Available on:
http://www.certleader.com/1Z0-061-dumps.html

Q31. You need to generate a list of all customer last names with their credit limits from the customers table. 

Those customers who do not have a credit limit should appear last in the list. 

Which two queries would achieve the required result? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: B,C 

Explanation: 

If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order. Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence. ANSWER C Sorting The default sort order is ascending: 

. Numeric values are displayed with the lowest values first (for example, 1 to 999). 

. Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01-JAN-95). 

. Character values are displayed in the alphabetical order (for example, “A” first and “Z” last). 

. Null values are displayed last for ascending sequences and first for descending sequences. 

-ANSWER B 

. You can also sort by a column that is not in the SELECT list. 

Q32. Evaluate the following SQL statement: 

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

A. It executes successfully and displays rows in the descending order of PROMO_CATEGORY. 

B. It produces an error because positional notation cannot be used in the order by clause with set operators. 

C. It executes successfully but ignores the order by clause because it is not located at the end of the compound statement. 

D. It produces an error because the order by clause should appear only at the end of a compound query-that is, with the last select statement. 

Answer:

Q33. Examine the structure of the orders table: 

You want to find the total value of all the orders for each year and issue the following command: 

Which statement is true regarding the outcome? 

A. It executes successfully and gives the correct output. 

B. It gives an error because the TO_CHAR function is not valid. 

C. It executes successfully but does not give the correct output. 

D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause. 

Answer:

Q34. Examine the create table statements for the stores and sales tables. 

SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE); 

SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id)); 

You executed the following statement: 

SQL> DELETE from stores 

WHERE store_id=900; 

The statement fails due to the integrity constraint error: 

ORA-02292: integrity constraint (HR.STORE_ID_FK) violated 

Which three options ensure that the statement will execute successfully? 

A. Disable the primary key in the STORES table. 

B. Use CASCADE keyword with DELETE statement. 

C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table. 

D. Disable the FOREIGN KEY in SALES table and then delete the rows. 

E. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option. 

Answer: A,C,D 

Q35. You issue the following command to drop the products table: 

SQL> DROP TABLE products; 

Which three statements are true about the implication of this command? 

A. All data along with the table structure is deleted. 

B. A pending transaction in the session is committed. 

C. All indexes on the table remain but they are invalidated. 

D. All views and synonyms remain but they are invalidated. 

E. All data in the table is deleted but the table structure remains. 

Answer: A,B,D 

Q36. Examine the data in the ename and hiredate columns of the employees table: 

You want to generate a list of user IDs as follows: You issue the following query: 

What is the outcome? 

A. It executes successfully and gives the correct output. 

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

C. It generates an error because the REPLACE function is not valid. 

D. It generates an error because the SUBSTR function cannot be nested in the CONCAT function. 

Answer:

Explanation: 

REPLACE (text, search_string, replacement_string) Searches a text expression for a character string and, if found, replaces it with a specified replacement string The REPLACE Function The REPLACE function replaces all occurrences of a search item in a source string with a replacement term and returns the modified source string. If the length of the replacement term is different from that of the search item, then the lengths of the returned and source strings will be different. If the search string is not found, the source string is returned unchanged. Numeric and date literals and expressions are evaluated before being implicitly cast as characters when they occur as parameters to the REPLACE function. The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is REPLACE (source string, search item, [replacement term]). If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string. . The following queries illustrate the REPLACE function with numeric and date expressions: Query 1: select replace(10000-3, '9', '85') from dual Query 2: select replace(sysdate, 'DEC', 'NOV') from dual 

Q37. View the Exhibit and examine the structure of the promotions table. 

Evaluate the following SQL statement: 

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

A. It shows COST_REMARK for all the promos in the table. 

B. It produces an error because the SUBQUERY gives an error. 

C. It shows COST_REMARK for all the promos in the promo category 'TV' 

D. It produces an error because SUBQUERIES cannot be used with the case expression. 

Answer:

Q38. View the Exhibit and examine the structure of the customers table. 

NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the customers table. 

Evaluate the following insert statement: The insert statement fails when executed. 

What could be the reason? 

A. The values clause cannot be used in an INSERT with a subquery. 

B. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match. 

C. The where clause cannot be used in a subquery embedded in an INSERT statement. 

D. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table. 

Answer:

Explanation: 

Copying Rows from Another Table 

Write your INSERT statement with a subquery: 

Do not use the VALUES clause. 

Match the number of columns in the INSERT clause to those in the subquery. 

Inserts all the rows returned by the subquery in the table, sales_reps. 

Q39. View the Exhibit and examine the structure of the customers table. 

Using the customers table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed. 

Which SQL statement would produce the required result? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:

Explanation: 

NVL Function 

Converts a null value to an actual value: 

Data types that can be used are date, character, and number. 

Data types must match: 

– NVL(commission_pct, 0) 

– NVL(hire_date, '01-JAN-97') 

– NVL(job_id, 'No Job Yet') 

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

Certleader Dumps
Certleader is a company specialized on providing high quality IT exam materials and fully committed to assist our respected clients crack any IT certification tests on their 1st efforts.