This dump is 100% valid to pass Oracle Oracle Database Jan 14,2022 Hotest 1Z0-061 vce dumps exam. The only tips is please do not just memorize the questions and answers, you need to get through understanding of it because the question changed a little in the real exam. Follow the instructions in the Geekcert Oracle Database Hotest 1Z0-061 study guide Oracle Database 12c: SQL Fundamentals PDF and VCEs. All Geekcert materials will help you pass your Oracle Oracle Database exam successfully.
Geekcert| 1Z0-061 exam dumps with pdf and vce, 100% pass guaranteed! Geekcert – best way to guarantee your 1Z0-061 certification and exam success! Geekcert 1Z0-061 certification dumps : oracle, ibm and many more. 4,500 exam dumps: pass your 1Z0-061 certification exam with Geekcert. get 1Z0-061 certification with Geekcert study materials and practice tests.
We Geekcert has our own expert team. They selected and published the latest 1Z0-061 preparation materials from Oracle Official Exam-Center: https://www.geekcert.com/1Z0-061.html
The following are the 1Z0-061 free dumps. Go through and check the validity and accuracy of our 1Z0-061 dumps.Questions and answers from 1Z0-061 free dumps are 100% free and guaranteed. See our full 1Z0-061 dumps if you want to get a further understanding of the materials.
Question 1:
Which three SQL statements would display the value 1890.55 as $1, 890.55?
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
Correct Answer: ADE
Question 2:
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.
Correct Answer: BD
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.
Question 3:
View the Exhibit and examine the structure of the promotions table.
You need to generate a report of all promos from the promotions table based on the following conditions:
1.
The promo name should not begin with \’T\’ or \’N\’.
2.
The promo should cost more than $20000.
3.
The promo should have ended after 1st January 2001. Which where clause would give the required result?
A. WHERE promo_name NOT LIKE \’T%\’ AND promo_name NOT LIKE \’N%\’ AND promo_cost > 20000 AND promo_end_date > `1-JAN-01\’
B. WHERE promo_name NOT LIKE \’T%\’ OR promo_name NOT LIKE \’N%\’ AND promo_cost >20000 AND promo_end_date > `1-JAN-01\’
C. WHERE (promo_name NOT LIKE \’T%\’ AND promo_name NOT LIKE \’N%\’)OR promo_cost >20000 OR promo_end_date > `1-JAN-01\’
D. WHERE (promo_name NOT LIKE \’%T%\’ OR promo_name NOT LIKE \’%N%\’) AND(promo_cost >20000 AND promo_end_date > `1-JAN-01\’)
Correct Answer: A
We must use the AND operator, not the OR operator, as all the Boolean statements must be true to meet the conditions.
Question 4:
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
Correct Answer: AF
Question 5:
View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.
Evaluate the following two queries:
SQL>SELECT DISTINCT promo_category to_char(promo_cost)”code” FROM promotions
ORDER BY code;
SQL>SELECT DISTINCT promo_category promo_cost “code”
FROM promotions ORDER BY 1;
Which statement is true regarding the execution of the above queries?
A. Only the first query executes successfully.
B. Only the second query executes successfully.
C. Both queries execute successfully but give different results.
D. Both queries execute successfully and give the same result.
Correct Answer: B
Question 6:
A UNIQUE constraint on a column requires an index.
Which of the following scenarios is correct? (Choose one or more correct answers.)
A. If a UNIQUE index already exists on the column, it will be used.
B. If a NONUNIQUE index already exists it will be used.
C. If a NONUNIQUE index already exists on the column, a UNIQUE index will be created implicitly.
D. If any index exists on the column, there will be an error as Oracle attempts to create another index implicitly.
Correct Answer: AB
Question 7:
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.
Correct Answer: A
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.
Question 8:
View the Exhibit and examine the structure of the SALES table.
The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.
Which statement is true regarding this SQL statement?
A. It executes successfully and generates the required result.
B. It produces an error because count(*) should be specified in the SELECT clause also.
C. It produces an error because count{*) should be only in the HAVING clause and not in the WHERE clause.
D. It executes successfully but produces no result because COUNT (prod_id) should be used instead of COUNT (*).
Correct Answer: C
Restricting Group Results with the HAVING Clause You use the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate information. In the syntax, group_condition restricts
the groups of rows returned to those groups for which the specified condition is true.
The Oracle server performs the following steps when you use the HAVING clause:
1.
Rows are grouped.
2.
The group function is applied to the group.
3.
The groups that match the criteria in the HAVING clause are displayed. The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are
formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.
Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.
Question 9:
You create a sequence as follows:
create sequence seq1 start with 1;
After selecting from it a few times, you want to reinitialize it to reissue the numbers already generated.
How can you do this?
A. You must drop and re-create the sequence.
B. You can\’t. Under no circumstances can numbers from a sequence be reissued once they have been used.
C. Use the command ALTER SEQUENCE SEQ1 START WITH 1; to reset the next value to 1.
D. Use the command ALTER SEQUENCE SEQ1 CYCLE; to reset the sequence to its starting value.
Correct Answer: A
It is not possible to change the next value of a sequence, so you must re-create it.
Question 10:
View the Exhibit and examine the structure of the EMPLOYEES table.
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.
Correct Answer: A
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
Question 11:
View the Exhibit and examine the structure of the products table.
Evaluate the following query:
What would be the outcome of executing the above SQL statement?
A. It produces an error.
B. It shows the names of all products in the table.
C. It shows the names of products whose list price is the second highest in the table.
D. It shows the names of all products whose list price is less than the maximum list price.
Correct Answer: C
Question 12:
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? (Choose two.)
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: BC
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.
Question 13:
The customers table has the following structure:
You need to write a query that does the following tasks:
1.
Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2.
Only those customers whose income level has a value should be considered.
3.
Customers whose tax amount is null should not be considered. Which statement accomplishes all the required tasks?
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: B
Question 14:
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? (Choose three.)
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.
Correct Answer: ABD
A: The DROP TABLE statement moves a table or object table to the recycle bin.
B: If a user issues a DDL (CREATE, ALTER, or DROP) or DCL (GRANT or REVOKE) command, the transaction in progress (if any) will
Incorrect:
Not C: Dropping a table invalidates dependent objects, such as indexes and constraints.
References:
Question 15:
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
Correct Answer: BD
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: