Free 1Z0-061 Study Guides 2021

Want to know 1z0 061 dumps pdf features? Want to lear more about 1z0 061 dumps pdf experience? Study oracle database 12c sql fundamentals 1z0 061 pdf free download. Gat a success with an absolute guarantee to pass Oracle 1Z0-061 (Oracle Database 12c SQL Fundamentals) test on your first attempt.

Also have 1Z0-061 free dumps questions for you:

NEW QUESTION 1
View the Exhibit and examine the structure of the PRODUCTS table.
1Z0-061 dumps exhibit
You need to generate a report in the following format: CATEGORIES
---------------------------------------------
5MP Digital Photo Camera's category is Photo Y Box's category is Electronics
Envoy Ambassador's category is Hardware
Which two queries would give the required output? (Choose two.)

  • A. SELECT prod_name || q'''s category is ' || prod_category CATEGORIES FROM products;
  • B. SELECT prod_name || q'['s ]'category is ' || prod_category CATEGORIES FROM products;
  • C. SELECT prod_name || q''s' || ' category is ' || prod_category CATEGORIES FROM products;
  • D. SELECT prod_name || q'<'s >' || 'category is ' || prod_category CATEGORIES FROM products;

Answer: CD

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 multibyte, 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.

NEW QUESTION 2
Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c?

  • A. ALTER TABLE SPLIT PARTITION
  • B. ALTER TABLE MERGE PARTITION
  • C. ALTER TABLE TRUNCATE PARTITION
  • D. ALTER TABLE ADD PARTITION
  • E. ALTER TABLE DROP PARTITION
  • F. ALTER TABLE MOVE PARTITION

Answer: CE

Explanation: Asynchronous Global Index Maintenance for DROP and TRUNCATE PARTITION This feature enables global index maintenance to be delayed and decoupled from a DROP and TRUNCATE partition without making a global index unusable. Enhancements include faster DROP and TRUNCATE partition operations and the ability to delay index maintenance to off-peak time.

NEW QUESTION 3
Which two statements are true about sequences created in a single instance database? (Choose two.)

  • A. CURRVAL is used to refer to the last sequence number that has been generated
  • B. DELETE <sequencename> would remove a sequence from the database
  • C. The numbers generated by a sequence can be used only for one table
  • D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement
  • E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted

Answer: AD

Explanation: Gaps in the Sequence
Although sequence generators issue sequential numbers without gaps, this action occurs independent of a commit or rollback. Therefore, if you roll back a statement containing a sequence, the number is lost.
Another event that can cause gaps in the sequence is a system crash. If the sequence caches values in memory, those values are lost if the system crashes.
Because sequences are not tied directly to tables, the same sequence can be used for multiple tables.
However, if you do so, each table can contain gaps in the sequential numbers. Modifying a Sequence
If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement
To remove a sequence, use the DROP statement: DROP SEQUENCE dept_deptid_seq;

NEW QUESTION 4
See the Exhibit and examine the structure and data in the INVOICE table: Exhibit:
1Z0-061 dumps exhibit
Which two SQL statements would execute successfully? (Choose two.)

  • A. SELECT MAX(inv_date), MIN(cust_id) FROM invoice;
  • B. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
  • C. SELECT (AVG(inv_date) FROM invoice;
  • D. SELECT AVG(inv_date - SYSDATE), AVG(inv_amt) FROM invoice;

Answer: AD

NEW QUESTION 5
Here is the structure and data of the CUST_TRANS table: Exhibit:
1Z0-061 dumps exhibit
Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table. Which three SQL statements would execute successfully? (Choose three.)

  • 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: ACD

NEW QUESTION 6
Examine the data in the ORD_ITEMS table:
ORD_NO ITEM_NO QTY
------------------------------------------- 1 111 10
1 222 20
1 333 30
2 333 30
2 444 40
3 111 40
Evaluate the following query: SQL>SELECT item_no, AVG(qty)
FROM ord_items
HAVING AVG(qty) > MIN(qty) * 2
GROUP BY item_no;
Which statement is true regarding the outcome of the above query?

  • A. It gives an error because the HAVING clause should be specified after the GROUP BYclause.
  • B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list.
  • C. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.
  • D. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.

Answer: C

NEW QUESTION 7
Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)

  • A. The BLOB data type column is used to store binary data in an operating system file
  • B. The minimum column width that can be specified for a VARCHAR2 data type column is one
  • C. A TIMESTAMP data type column stores only time values with fractional seconds
  • D. The value for a CHAR data type column is blank-padded to the maximum defined column width
  • E. Only One LONG column can be used per table

Answer: BDE

Explanation: LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB. There can only be one LONG column in a table.
DVARCHAR2 Variable-length character data, from 1 byte to 4KB. The data is stored in the database character set. The VARCHAR2 data type must be qualified with a number indicating the maximum length of the column.
If a value is inserted into the column that is less than this, it is not a problem: the value will only take up as much space as it needs. If the value is longer than this maximum, the INSERT will fail with an error. VARCHAR2(size)
Variable-length character data (A maximum size must be specified: minimum size is 1; maximum size is 4, 000.)
BLOB Like CLOB, but binary data that will not undergo character set conversion by Oracle Net.
BFILE A locator pointing to a file stored on the operating system of the database server. The size of the files is limited to 4GB.
TIMESTAMP This is length zero if the column is empty, or up to 11 bytes, depending on the precision specified.
Similar to DATE, but with precision of up to 9 decimal places for the seconds, 6 places by default.

NEW QUESTION 8
Where can sub queries be used? (Choose all that apply)

  • A. field names in the SELECT statement
  • B. the FROM clause in the SELECT statement
  • C. the HAVING clause in the SELECT statement
  • D. the GROUP BY clause in the SELECT statement
  • E. the WHERE clause in only the SELECT statement
  • F. the WHERE clause in SELECT as well as all DML statements

Answer: ABCF

Explanation: SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.
A subquery can have any of the usual clauses for selection and projection. The following are required clauses:
A SELECT list A FROM clause
The following are optional clauses: WHERE
GROUP BY HAVING
The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.

NEW QUESTION 9
View the Exhibit and examine the data in the PROJ_TASK_DETAILS table.
1Z0-061 dumps exhibit
The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them.
The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks.
You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on.
Which query would give the required result?

  • A. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.based_on = d.task_id);
  • B. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);
  • C. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);
  • D. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.task_id = d.task_id);

Answer: B

NEW QUESTION 10
Evaluate the following SQL statement:
1Z0-061 dumps exhibit
Which statement is true regarding the above query if one of the values generated by the subquery is null?

  • A. It produces an error.
  • B. It executes but returns no rows.
  • C. It generates output for null as well as the other values produced by the subquery.
  • D. It ignores the null value and generates output for the other values produced by the subquery.

Answer: D

NEW QUESTION 11
You need to create a table named ORDERS that contain four columns:
1. AN ORDER_ID column of number data type
2. A CUSTOMER_ID column of number data type
3. AN ORDER_STATUS column that contains a character data type
4. A DATE_ORDERED column to contain the date the order was placed.
When a row is inserted into the table, if no value is provided when the order was placed, today’s date should be used instead.
Which statement accomplishes this?

  • A. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE);
  • B. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE DEFAULT SYSDATE);
  • C. CREATE OR REPLACE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE DEFAULT SYSDATE);
  • D. CREATE OR REPLACE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE);
  • E. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE = SYSDATE);
  • F. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE DEFAULT SYSDATE);

Answer: B

Explanation: Requirement that Order_Status should be a character data type
Not E: Order_status must be a character data type. There is also a syntax error.

NEW QUESTION 12
Consider these three statements:
create synonym s1 for employees; create public synonym s1 for departments; select * from s1;
Which of the following statements is correct?

  • A. The second statement will fail because an object S1 already exists.
  • B. The third statement will show the contents of EMPLOYEES.
  • C. The third statement will show the contents of DEPARTMENTS.
  • D. The third statement will show the contents of the table S1, if such a table exists in the current schema.

Answer: B

Explanation: The order of priority is to search the schema namespace before the public namespace, so it will be the private synonym (to EMPLOYEES) that will be found.

NEW QUESTION 13
Which constraint can be defined only at the column level?

  • A. UNIQUE
  • B. NOT NULL
  • C. CHECK
  • D. PRIMARY KEY
  • E. FOREIGN KEY

Answer: B

Explanation: The NOT NULL constraint can be defined only at the column level. It enforces that a value must be defined for this column such that the column may not be NULL for any row.
Incorrect Answers
A:. The UNIQUE constraint enforces uniqueness on values in the constrained column. It can be defined not only at the column level.
C:. The CHECK constraint enforces that values added to the constrained column must be present in a static list of values permitted for the column.
D:. The PRIMARY KEY constraint stipulates that values in the constrained column(s) must be unique and not NULL. If the primary key applies to multiple columns, then the combination of values in the columns must be unique and not NULL.
E:. The FOREIGN KEY constraint enforces that only values in the primary key of a parent table may be included as values in the constrained column(s) of the child table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232 Chapter 5: Creating Oracle Database Objects

NEW QUESTION 14
View the Exhibit and examine the data in the costs table.
1Z0-061 dumps exhibit
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:
1Z0-061 dumps exhibit
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.

Answer: A

NEW QUESTION 15
What is true about sequences?

  • A. The start value of the sequence is always 1.
  • B. A sequence always increments by 1.
  • C. The minimum value of an ascending sequence defaults to 1.
  • D. The maximum value of descending sequence defaults to 1.

Answer: C

NEW QUESTION 16
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?
1Z0-061 dumps exhibit

  • 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.

P.S. 2passeasy now are offering 100% pass ensure 1Z0-061 dumps! All 1Z0-061 exam questions have been updated with correct answers: https://www.2passeasy.com/dumps/1Z0-061/ (339 New Questions)