Master the 1z0 061 practice test Oracle Database 12c SQL Fundamentals content and be ready for exam day success quickly with this Actualtests 1z0 061 practice test exam cost. We guarantee it!We make it a reality and give you real 1z0 061 dumps pdf questions in our Oracle 1z0 061 dumps pdf braindumps.Latest 100% VALID Oracle oracle 1z0 061 Exam Questions Dumps at below page. You can use our Oracle oracle 1z0 061 braindumps and pass your 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
Q1. 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
Q2. 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
Q3. 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.
Using the COUNT Function
The COUNT function has three formats:
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.
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.
Q4. 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.
Q5. 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.
Q6. Evaluate the following query:
SQL> SELECT TRUNC(ROUND(156.00, -1), -1)
What would be the outcome?
Function Purpose ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.) TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero
Q7. 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
Q8. 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.
Q9. Examine the structure of the sales table:
Evaluate the following create table statement:
Which two statements are true about the creation of the SALES1 table?
A. The SALES1 table is created with no rows but only a structure.
B. The SALES1 table would have primary key and unique constraints on the specified columns.
C. The SALES1 table would not be created because of the invalid where clause.
D. The SALES1 table would have not null and unique constraints on the specified columns.
E. The SALES1 table would not be created because column-specified names in the select and create table clauses do not match,
Q10. 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
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: