Master the 1z0 061 practice test Oracle Database 12c SQL Fundamentals content and be ready for exam day success quickly with this Examcollection oracle 1z0 061 practice exam. We guarantee it!We make it a reality and give you real 1z0 061 dumps questions in our Oracle 1z0 061 practice test braindumps.Latest 100% VALID Oracle 1z0 061 practice test Exam Questions Dumps at below page. You can use our Oracle oracle 1z0 061 braindumps and pass your exam.
Q1. Which statement is true regarding the UNION operator?
A. By default, the output is not sorted.
B. Null values are not ignored during duplicate checking.
C. Names of all columns must be identical across all select statements.
D. The number of columns selected in all select statements need not be the same.
Answer: D
Explanation:
The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.
Q2. 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
Q3. 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: A
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
Q4. 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: D
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')
Q5. You want to create a sales table with the following column specifications and data types:
SALESID: Number STOREID: Number ITEMID: Number QTY: Number, should be set to 1 when no value is specified SLSDATE: Date, should be set to current date when no value is specified PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified
Which statement would create the table?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q6. 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: B
Q7. 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
Q8. Using the customers table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Explanation: Duplicate Rows Unless you indicate otherwise, SQL displays the results of a query without eliminating the duplicate rows.
To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword.
You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns.