It is impossible to pass Oracle 1z0 061 dumps exam without any help in the short term. Come to Examcollection soon and find the most advanced, correct and guaranteed Oracle 1z0 061 practice test practice questions. You will get a surprising result by our Regenerate Oracle Database 12c SQL Fundamentals practice guides.
♥♥ 2021 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.surepassexam.com/1Z0-061-exam-dumps.html
Q1. In which three situations does a transaction complete?
A. When a DELETE statement is executed
B. When a ROLLBACK command is executed
C. When a PL/SQL anonymous block is executed
D. When a data definition language (DDL) statement is executed
E. When a TRUNCATE statement is executed after the pending transaction
Answer: B,D,E
Q2. 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.
Q3. You need to create a table for a banking application. One of the columns in the table has the following requirements:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and subtracted with date data type without using conversion functions.
3) The maximum period of the credit provision in the application is 30 days.
4) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
A. DATE
B. NUMBER
C. TIMESTAMP
D. INTERVAL DAY TO SECOND
E. INTERVAL YEAR TO MONTH
Answer: D
Q4. 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: D
Q5. 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
Q6. Which two statements are true regarding constraints?
A. A table can have only one primary key and one foreign key.
B. A table can have only one primary key but multiple foreign keys.
C. Only the primary key can be defined at the column and table levels.
D. The foreign key and parent table primary key must have the same name.
E. Both primary key and foreign key constraints can be defined at both column and table levels.
Answer: B,E
Q7. View the Exhibit and examine the structure of the products table.
Using the products table, you issue the following query to generate the names, current list price, and discounted list price for all those products whose list price falls below $10 after a discount of 25% is applied on it.
The query generates an error. What is the reason for the error?
A. The parenthesis should be added to enclose the entire expression.
B. The double quotation marks should be removed from the column alias.
C. The column alias should be replaced with the expression in the where clause.
D. The column alias should be put in uppercase and enclosed within double quotation marks in the where clause.
Answer: C
Q8. View the Exhibit and examine the data in the employees table:
You want to display all the employee names and their corresponding manager names. Evaluate the following query:
Which join option can be used in the blank in the above query to get the required output?
A. INNER JOIN
B. FULL OUTER JOIN
C. LEFT OUTER JOIN
D. RIGHT OUTER JOIN
Answer: C
Q9. 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.
Q10. Evaluate the following query:
What would be the outcome of the above query?
A. It produces an error because flower braces have been used.
B. It produces an error because the data types are not matching.
C. It executes successfully and introduces an 's at the end of each PROMO_NAME in the output.
D. It executes successfully and displays the literal "{'s start date was \} * for each row in the output.
Answer: C Explanation:
So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string
Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any other character literal.
These character-enclosing symbols could have been anything other than single quotation marks.
Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or <angle brackets>. Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character
The syntax of the alternative quote operator is as follows:
q'delimiter'character literal which may include the single quotes delimiter' where delimiter can be any character or bracket.
Alternative Quote (q) Operator
Specify your own quotation mark delimiter.
Select any delimiter.
Increase readability and usability.
SELECT department_name || q'[ Department's Manager Id: ]'
|| manager_id
AS "Department and Manager"
FROM departments;
Alternative Quote (q) Operator
Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter.
You can choose any convenient delimiter, single-byte or multi byte, or any of the following character pairs: [ ], { }, ( ), or < >.
In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets
[] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string.