All About Printable 1Z0-061 Practice Test

NEW QUESTION 1

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.

Answer: C

NEW QUESTION 2

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.

There is only one customer with the CUST_LAST_NAME column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the
customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

• A. INSERT INTO ordersVALUES (1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
• B. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES(1, '10-mar-2007', 'direct', &&customer_id, 1000);
• C. INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND c.cust_last_name='Roberts' ANDc.credit_limit=600 )VALUES (1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
• D. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES(1, '10-mar-2007', 'direct', &customer_id, 1000);

Answer: A

NEW QUESTION 3

View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.

Evaluate the following SQL statements:

Which statement is true regarding the above compound query?

• A. It shows products that have a cost recorded irrespective of sales
• B. It shows products that were sold and have a cost recorded
• C. It shows products that were sold but have no cost recorded
• D. It reduces an error

Answer: C

NEW QUESTION 4

Exhibit contains the structure of 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 products whose list price is the second highest in the table.
• C. It shown the names of all products whose list price is less than the maximum list price
• D. It shows the names of all products in the table

Answer: B

NEW QUESTION 5

Which statements are correct regarding indexes? (Choose all that apply.)

• A. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.
• B. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
• C. A FOREIGN KEY constraint on a column in a table automatically creates a non unique key
• D. When a table is dropped, the corresponding indexes are automatically dropped

Answer: ABD

NEW QUESTION 6

Evaluate this SQL statement:
SELECT ename, sal, 12*sal+100 FROM emp;
The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?

• A. No change is required to achieve the desired results.
• B. SELECT ename, sal, 12*(sal+100) FROM emp;
• C. SELECT ename, sal, (12*sal)+100 FROM emp;
• D. SELECT ename, sal+100, *12 FROM emp;

Answer: B

Explanation:
to achieve the result you must add 100 to sal before multiply with 12. Select ename, sal, 12*(sal+100) from EMP;
Incorrect
A- Multiplication and division has priority over addition and subtraction in Operator precedence.
C- Give wrong results
D- Wrong syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-11

NEW QUESTION 7

View the Exhibits and examine the structure of the products and sales tables. Exhibit 1:

Exhibit 2:

Which two SQL statements would give the same output? (Choose two.)

• A. SELECT prod_id FROM productsINTERSECTSELECT prod_id FROM sales;
• B. SELECT prod_id FROM productsMINUSSELECT prod_id FROM sales;
• C. SELECT DISTINCT p.prod_idFROM products p JOIN sales sON p.prod_id=s.prod_id;
• D. SELECT DISTINCT p.prod_idFROM products p JOIN sales sON p.prod_id <> s.prod_id;

Answer: AC

NEW QUESTION 8

Which two statements are true regarding constraints? (Choose two.)

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

NEW QUESTION 9

A view and a synonym are created as follows:
create view dept_v as select * from dept; create synonym dept_s for dept_v; Subsequently the table DEPT is dropped.
What will happen if you query the synonym DEPT_S ? (Choose the best answer.)

• A. There will not be an error because the synonym addresses the view, which still exists, but there will beno rows returned.
• B. There will not be an error if you first recompile the view with the command ALTER VIEW DEPT_VCOMPILE FORCE;
• C. There will be an error because the synonym will be invalid.
• D. There will be an error because the view will be invalid.
• E. There will be an error because the view will have been dropped implicitly when the table was dropped.

Answer: D

Explanation:
The synonym will be fine, but the view will be invalid. Oracle will attempt to recompile the view, but this will fail.

NEW QUESTION 10

Evaluate the following SQL statement:

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

The SQL statements executed in a user session as follows:

Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)

• A. Both the DELETE statements and the UPDATE statement are rolled back
• B. The rollback generates an error
• C. Only the DELETE statements are rolled back
• D. Only the seconds DELETE statement is rolled back
• E. No SQL statements are rolled back

Answer: BE

NEW QUESTION 12

What does the FORCE option for creating a view do?

• A. creates a view with constraints
• B. creates a view even if the underlying parent table has constraints
• C. creates a view in another schema even if you don't have privileges
• D. creates a view regardless of whether or not the base tables exist

Answer: D

Explanation:
create a view regardless of whether or not the base tables exist.
Incorrect
A- the option is not valid
B- the option is not valid
C- the option is not valid
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-3

NEW QUESTION 13

You created an ORDERS table with the following description: Exhibit:

You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column.
Which statement is true in this scenario?

• A. You cannot add a primary key constraint if data exists in the column
• B. You can add the primary key constraint even if data exists, provided that there are no duplicate values
• C. The primary key constraint can be created only a the time of table creation
• D. You cannot have two constraints on one column

Answer: B

NEW QUESTION 14

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%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
• B. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'
• C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND 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')

Answer: C

NEW QUESTION 15

View the Exhibit and examine the data in the PROMOTIONS table.

You need to display all promo categories that do not have 'discount' in their subcategory.
Which two SQL statements give the required result? (Choose two.)

• A. SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_subcategory = 'discount';
• B. SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_subcategory = 'discount';
• C. SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_subcategory <> 'discount';
• D. SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_subcategory <> 'discount';

Answer: AD

NEW QUESTION 16

Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?

• A. DROP emp_dept_vu;
• B. DELETE emp_dept_vu;
• C. REMOVE emp_dept_vu;
• D. DROP VIEW emp_dept_vu;
• E. DELETE VIEW emp_dept_vu;
• F. REMOVE VIEW emp_dept_vu;

Answer: D

Explanation:
DROP VIEW viewname;
Incorrect
A- Not a valid drop view statement
B- Not a valid drop view statement
C- Not a valid drop view statement
E- Not a valid drop view statement
F- Not a valid drop view statement
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-20

NEW QUESTION 17

View the Exhibit and examine the structure of the product, component, and PDT_COMP tables.
In product table, PDTNO is the primary key.
In component table, COMPNO is the primary key.
In PDT_COMP table, <PDTNO, COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in product table and COMPNO is the foreign key referencing the COMPNO in component table.
You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist.
Evaluate the following query:
SQL>SELECT pdtno, pdtname, compno, compname FROM product pdt_comp
USING (pdtno) component USING (compno) WHERE compname IS NOT NULL;
Which combination of joins used in the blanks in the above query gives the correct output?

• A. JOIN; JOIN
• B. FULL OUTER JOIN; FULL OUTER JOIN
• C. RIGHT OUTER JOIN; LEFT OUTER JOIN
• D. LEFT OUTER JOIN; RIGHT OUTER JOIN

Answer: C

NEW QUESTION 18

Which two statements are true regarding sub queries? (Choose two.)

• A. A sub query can retrieve zero or more rows.
• B. Only two sub queries can be placed at one level.
• C. A sub query can be used only in SQL query statements.
• D. A sub query can appeal* on either side of a comparison operator.
• E. There is no limit on the number of sub query levels in the WHERE clause of a SELECT statement.

Answer: AD

NEW QUESTION 19

The STUDENT_GRADES table has these columns:
STUDENT_ID. NUMBER(12) SEMESTER_END. DATE GPA. NUMBER(4, 3)
The registrar has asked for a report on the average grade point average (GPA), sorted from the highest grade point average to each semester, starting from the earliest date.
Which statement accomplish this?

• A. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY semester_end DESC, gpa DESC;
• B. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY semester_end, gpa ASC
• C. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY gpa DESC, semester_end ASC;
• D. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY gpa DESC, semester_end DESC;
• E. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY gpa DESC, semester_end ASC;
• F. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY semester_end, gpa DESC

Answer: F

NEW QUESTION 20

You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference.
Which method or feature should you use?

• A. Compare Period ADDM report
• B. AWR Compare Period report
• C. Active Session History (ASH) report
• D. Taking a new snapshot and comparing it with a preserved snapshot

Answer: B

Explanation:
The awrddrpt.sql report is the Automated Workload Repository Compare Period Report. The awrddrpt.sql script is located in the $ORACLE_HOME/rdbms/admin directory. Incorrect:
Not A:Compare Period ADDM
Use this report to perform a high-level comparison of one workload replay to its capture or to another replay of the same capture. Only workload replays that contain at least 5 minutes of database time can be compared using this report.

NEW QUESTION 21

The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4) ENAME VARCHAR2 (25) JOB_ID VARCHAR2(10)
Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends
with a the letter "n"?

• A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';
• B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, , -1, 1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';
• C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1, 1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = 'n';
• D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1, 1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = 'n';

Answer: A

Explanation:
INSTR is a character function return the numeric position of a named string. INSTR(NAMED, ’a’)
Incorrect
B- Did not return a numeric position for ‘a’.
C- Did not return a numeric position for ‘a’.
D- Did not return a numeric position for ‘a’.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-8

NEW QUESTION 22

Which is an iSQL*Plus command?

• A. INSERT
• B. UPDATE
• C. SELECT
• D. DESCRIBE
• E. DELETE
• F. RENAME

Answer: D

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.
Incorrect:
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 23

View the Exhibit and examine the structure of the PROMOTIONS table.

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.
Which query would give you the required output?

• A. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions)ANDpromo_category = 'INTERNET';
• B. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category='INTERNET');
• C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');
• D. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');

Answer: C

NEW QUESTION 24

The EMPLOYEES table has these columns:
LAST NAME. VARCHAR2(35) SALARY. NUMBER(8, 2) HIRE_DATE. DATE
Management wants to add a default value to the SALARY column. You plan to alter the table by using this SQL statement:
ALTER TABLE EMPLOYEES MODIFY (SALARY DEFAULT 5000);
What is true about your ALTER statement?

• A. Column definitions cannot be altered to add DEFAULT values.
• B. A change to the DEFAULT value affects only subsequent insertions to the table.
• C. Column definitions cannot be altered at add DEFAULT values for columns with a NUMBER data type.
• D. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.

Answer: B

Explanation:
A change to the DEFAULT value affects only subsequent insertions to the table. Existing
rows will not be affected.
Incorrect Answers
A:. Column definitions can be altered to add DEFAULT values.
C:. Column definitions can be altered to add DEFAULT values. It works for columns with a NUMBER data type also.
D:. A change to the DEFAULT value affects only subsequent insertions to the table. Existing rows will not be affected.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 219-224 Chapter 5: Creating Oracle Database Objects

NEW QUESTION 25

Which are iSQL*Plus commands? (Choose all that apply.)

• A. INSERT
• B. UPDATE
• C. SELECT
• D. DESCRIBE
• E. DELETE
• F. RENAME

Answer: D

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.
Incorrect
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 26

Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1 START WITH 100
INCREMENT BY 10
MAXVALUE 200 CYCLE NOCACHE;
The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?

• A. 1
• B. 10
• C. 100
• D. an error

Answer: A

Explanation:
But why the answer is not "C" ?
Because you didn't specify the MINVALUE for the sequence. If you check the sequence definition that you created it will have the default value of 1, which it reverts to when cycling.
If you wanted to keep the minimum value you would need to specify it in the sequence creation.
sequence Is the name of the sequence generator
INCREMENT BY n Specifies the interval between sequence numbers, where n is an integer (If this clause is omitted, the sequence increments by 1.)
START WITH n Specifies the first sequence number to be generated (If this clause is omitted, the sequence starts with 1.)
MAXVALUE n Specifies the maximum value the sequence can generate
NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and –1 for a descending sequence (This is the default option.)
MINVALUE n Specifies the minimum sequence value
NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and –(10^26) for a descending sequence (This is the default option.)
CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching its maximum or minimum value
(NOCYCLE is the default option.)
CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and keeps in memory (By default, the Oracle server caches 20 values.)

NEW QUESTION 27

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which DELETE statement is valid?

• A. DELETE FROM employeesWHERE employee_id = (SELECT employee_id FROM employees);
• B. DELETE * FROM employeesWHERE employee_id=(SELECT employee_id FROM new_employees);
• C. DELETE FROM employeesWHERE employee_id IN (SELECT employee_id . FROM new_employees . WHERE name = ‘Carrey’);
• D. DELETE * FROM employeesWHERE employee_id IN (SELECT employee_id . FROM new_employees . WHERE name = ‘Carrey’);

Answer: C

NEW QUESTION 28

View the Exhibit and examine the structure of the CUSTOMERS table .Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?

• A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit ) FROM customersGROUP BY cust_city, cust_income_level, cust_credit_limit;
• B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customersGROUP BY cust_city, cust_income_level;
• C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customersGROUP BY cust_credit_limit, cust_income_level, cust_city ;
• D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customersGROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);

Answer: B

NEW QUESTION 29

Examine the structure of the DEPARTMENTS table:

You execute the following command: SQL> ALTER TABLE departments
SET UNUSED (country);
Which two statements are true? (Choose two.)

• A. Synonyms existing om the DEPARTMENTS table would have to be re-created.
• B. Unique key constraints defined on the COUNTRY column are removed.
• C. Views created on the DEPARTMENTS table that include the COUNTRY column are automatically modified and remain valid.
• D. Indexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is executed.
• E. A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command.

Answer: CE

NEW QUESTION 30

View the Exhibit and examine the data in the PROJ_TASK_DETAILS table.

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