Highest Quality 1Z0-061 Exam Dumps 2021
We offers 1z0 061 practice test. "Oracle Database 12c SQL Fundamentals", also known as 1Z0-061 exam, is a Oracle Certification. This set of posts, Passing the 1Z0-061 exam with oracle database 12c sql fundamentals 1z0 061 pdf free download, will help you answer those questions. The 1z0 061 dumps pdf covers all the knowledge points of the real exam. 100% real 1z0 061 dumps and revised by experts!
Online Oracle 1Z0-061 free dumps demo Below:
NEW QUESTION 1
Which view should a user query to display the columns associated with the constraints on a table owned by the user?
- A. USER_CONSTRAINTS
- B. USER_OBJECTS
- C. ALL_CONSTRAINTS
- D. USER_CONS_COLUMNS
- E. USER_COLUMNS
Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view.
A- table to view all constraints definition and names
B- show all object name belong to user
C- does not display column associated
E- no such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25
NEW QUESTION 2
Which statement describes the ROWID data type?
- A. Binary data up to 4 gigabytes.
- B. Character data up to 4 gigabytes.
- C. Raw binary data of variable length up to 2 gigabytes.
- D. Binary data stored in an external file, up to 4 gigabytes.
- E. A hexadecimal string representing the unique address of a row in its table.
Explanation: The ROWID datatype stores information related to the disk location of table rows. They also uniquely identify the rows in your table. The ROWID datatype is stored as a hexadecimal string.
A:. It is not a binary data. The ROWID datatype is a hexadecimal string. B:. It is not a character data. The ROWID datatype is a hexadecimal string.
C:. It is not a raw binary data. The ROWID datatype is a hexadecimal string.
D:. It is not binary data stored in an external file. The ROWID datatype is a hexadecimal string.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 216 Chapter 5: Creating Oracle Database Objects
NEW QUESTION 3
EMPLOYEES and DEPARTMENTS data: EMPLOYEES
On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID.
On the DEPARTMENTS table DEPARTMENT_ID is the primary key. Evaluate this UPDATE statement.
UPDATE employees SET mgr_id =
. (SELECT mgr_id
. FROM. employees
. WHERE dept_id=
. (SELECT department_id
. FROM departments
. WHERE department_name = 'Administration')),
. Salary = (SELECT salary
. . FROM employees
. . WHERE emp_name = 'Smith') WHERE job_id = 'IT_ADMIN';
What happens when the statement is executed?
- A. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105.
- B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105.
- C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105.
- D. The statement fails because there is more than one row matching the employee name Smith.
- E. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table.
- F. The statement fails because there is no 'Administration' department in the DEPARTMENTS table.
Explanation: '=' is use in the statement and sub query will return more than one row. Employees table has 2 row matching the employee name Smith.
The update statement will fail.
Incorrect Answers :
A- The Update statement will fail no update was done.
B- The update statement will fail no update was done.
C- The update statement will fail no update was done.
E- The update statement will fail but not due to job_it='IT_ADMIN'
F- The update statement will fail but not due to department_id='Administration'
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Sub queries, p. 6-12
NEW QUESTION 4
Examine the structure of the PROMOS table:
You want to generate a report showing promo names and their duration (number of days).
If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed. Which queries give the correct output? (Choose all that apply.)
- A. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date, 'ONGOING')) FROM promos;
- B. SELECT promo_name, COALESCE(TO_CHAR(promo_end_date - promo_start_date), 'ONGOING') FROM promos;
- C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date), 'ONGOING') FROM promos;
- D. SELECT promo_name, DECODE(promo_end_date-promo_start_date, NULL, 'ONGOING', promo_end_date - promo_start_date) FROM promos;
- E. SELECT promo_name, ecode(coalesce(promo_end_date, promo_start_date), null, 'ONGOING', promo_end_date - promo_start_date)FROM promos;
NEW QUESTION 5
Which is an iSQL*Plus command?
- A. INSERT
- B. UPDATE
- C. SELECT
- D. DESCRIBE
- E. DELETE
- F. RENAME
Explanation: The only SQL*Plus command in this list: DESCRIBE. It cannot be used as SQL command. This command returns a description of tablename, including all columns in that table, the datatype for each column and an indication of whether the column permits storage of NULL values.
A - INSERT is not a SQL*PLUS command
B - UPDATE is not a SQL*PLUS command
C - SELECT is not a SQL*PLUS command
E - DELETE is not a SQL*PLUS command
F - RENAME is not a SQL*PLUS command
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7
NEW QUESTION 6
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30)
JOB_ID NUMBER SAL NUMBER
MGR_ID NUMBER . References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER . Foreign key to DEPARTMENT_ID column of the. . DEPARTMENTS table
You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table.
Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)
- A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.
- B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.
- C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.
- D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
- E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.
- F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.
Explanation: the EMP_ID_SEQ sequence is not affected by modification to the EMPLOYEES table. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
A- EMP_ID_SEQ sequence can be use to populate JOB_ID
B- EMP_ID_SEQ sequence will not be invalidate when column in EMPLOYEE_ID is modify.
E- EMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEES table.
F- EMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEE_ID column.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 12-4
NEW QUESTION 7
See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables:
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following the CREATE TABLE command: Exhibit:
Which statement is true regarding the above command?
- A. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match
- B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table
- C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition
- D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table
Explanation: Creating a Table Using a Subquery
Create a table and insert rows by combining the CREATE TABLE statement and the AS subquery option.
CREATE TABLE table [(column, column...)] AS subquery;
Match the number of specified columns to the number of subquery columns. Define columns with column names and default values.
The table is created with the specified column names, and the rows retrieved by the SELECT statement are inserted into the table.
The column definition can contain only the column name and default value.
If column specifications are given, the number of columns must equal the number of columns in the subquery SELECT list.
If no column specifications are given, the column names of the table are the same as the column names in the subquery.
The column data type definitions and the NOT NULL constraint are passed to the new table. Note that only the explicit NOT NULL constraint will be inherited. The PRIMARY KEY column will not pass the NOT NULL feature to the new column. Any other constraint rules are not passed to the new table. However, you can add constraints in the column definition.
NEW QUESTION 8
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
NEW QUESTION 9
Evaluate the following SQL statements: Exhibit:
Which is the correct output of the above query?
- A. +00-300, +54-02, +00 11:12:10.123457
- B. +00-300, +00-650, +00 11:12:10.123457
- C. +25-00, +54-02, +00 11:12:10.123457
- D. +25-00, +00-650, +00 11:12:10.123457
NEW QUESTION 10
The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query successfully?
SELECT * FROM ord;
- A. CREATE SYNONYM ord FOR orders; This command is issued by OE.
- B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
- C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
- D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
Explanation: Creating a Synonym for an Object
To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects.
This method can be especially useful with lengthy object names, such as views. In the syntax:
PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created Guidelines
The object cannot be contained in a package.
A private synonym name must be distinct from all other objects that are owned by the same user.
If you try to execute the following command (alternative B, issued by OE):
NEW QUESTION 11
Examine the structure of the PRODUCTS table:
You want to change the definition of the PRODUCTS table. The PROD_DETAILS column must be changed to allow 4000 characters.
Which statement is valid?
- A. ALTER TABLE productsMODIFY (prod_details CHAR2 (4000));
- B. ALTER TABLE productsMODIFY COLUMN (prod_details CHAR (4000));
- C. ALTER TABLE productsCHANGE (prod_details VARCHAR2 (4000));
- D. ALTER TABLE productsMODIFY (prod_details VARCHAR2 (4000));
NEW QUESTION 12
Which two statements are true about constraints? (Choose two.)
- A. The UNIQUE constraint does not permit a null value for the column.
- B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
- C. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.
- D. The NOT NULL constraint ensures that null values are not permitted for the column.
Explanation: B: A unique constraint can contain null values because null values cannot be compared to anything.
D: The NOT NULL constraint ensure that null value are not permitted for the column
A- statement is not true
C- statement is not true
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-9
NEW QUESTION 13
View the Exhibit and examine the structure of the customers table.
NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the customers table.
Evaluate the following insert statement:
The insert statement fails when executed. What could be the reason?
- A. The values clause cannot be used in an INSERT with a subquery.
- B. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.
- C. The where clause cannot be used in a subquery embedded in an INSERT statement.
- D. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table.
Explanation: Copying Rows from Another Table
Write your INSERT statement with a subquery: Do not use the VALUES clause.
Match the number of columns in the INSERT clause to those in the subquery. Inserts all the rows returned by the subquery in the table, sales_reps.
NEW QUESTION 14
View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the promotions table, and the required output format.
Which two queries give the correct result?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
NEW QUESTION 15
Which three statements are true about the ALTER TABLE …. DROP COLUMN …. command?
- A. A column can be dropped only if it does not contain any data.
- B. A column can be dropped only if another column exists in the table.
- C. A dropped column can be rolled back.
- D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
- E. A parent key column in the table cannot be dropped.
NEW QUESTION 16
Examine the following SQL commands:
Which statement is true regarding the execution of the above SQL commands?
- A. Both commands execute successfully.
- B. The first CREATE TABLE command generates an error because the NULL constraint is not valid.
- C. The second CREATE TABLE command generates an error because the CHECK constraint is not valid.
- D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column.
- E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints.
Explanation: Defining Constraints
The slide gives the syntax for defining constraints when creating a table. You can create constraints at either the column level or table level. Constraints defined at the column level are included when the column is defined. Table-level constraints are defined at the end of
the table definition and must refer to the column or columns on which the constraint pertains in a set of parentheses. It is mainly the syntax that differentiates the two; otherwise, functionally, a columnlevel constraint is the same as a table-level constraint. NOT NULL constraints must be defined at the column level.
Constraints that apply to more than one column must be defined at the table level.
Recommend!! Get the Full 1Z0-061 dumps in VCE and PDF From Surepassexam, Welcome to Download: https://www.surepassexam.com/1Z0-061-exam-dumps.html (New 339 Q&As Version)