Breathing 1Z0-051 Exam Dumps 2021

NEW QUESTION 1
Which two statements complete a transaction? (Choose two)

• A. DELETE employees;
• B. DESCRIBE employees;
• C. ROLLBACK TO SAVEPOINT C;
• D. GRANT SELECT ON employees TO SCOTT;
• E. ALTER TABLE employeesSET UNUSED COLUMN sal;
• F. Select MAX(sal)FROM employeesWHERE department_id = 20;

Answer: DE

Explanation:
D: GRANT is a DML operation which will cause an implicit commit
E: It is important to understand that an implicit COMMIT occurs on the database when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a CREATE TABLE statement, used to create a database object, or an ALTER TABLE statement, used to alter a database object.
Incorrect Answers A:The DELETE command is data-manipulation language (DML) command and it does not complete a transaction. B:The DESCRIBE command is internal SQL*Plus command and it has nothing to do with completion a transaction.
C: ROLLBACK is not used to commit or complete a transaction, it is used to undo a transaction F:SELECT command is used to retrieve data. It does not complete a transaction.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 281-282 Chapter 3: Advanced Data Selection in Oracle

NEW QUESTION 2
Examine the structure of the EMP_DEPT_VU view:

Which SQL statement produces an error?

• A. SELECT * FROM emp_dept_vu;
• B. SELECT department_id, SUM(salary) FROM emp_dept_vu GROUP BY department_id;
• C. SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu GROUP BY department_id, job_id;
• D. SELECT job_id, SUM(salary) FROM emp_dept_vu WHERE department_id IN (10,20) GROUP BY job_id HAVING SUM(salary) > 20000;
• E. None of the statements produce an error; all are vali

Answer: E

Explanation: Explanation: None of the statements produce an error. Incorrect Answer: AStatement will not cause error BStatement will not cause error CStatement will not cause error DStatement will not cause error

NEW QUESTION 3
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 4
The STUDENT_GRADES table has these columns:
STUDENT_IDNUMBER(12)
SEMESTER_ENDDATE
GPANUMBER(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, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC;
• B. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa ASC
• C. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
• D. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end DESC;
• E. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
• F. SELECT student_id,semester_end,gpa FROM student_grades ORDER BY semester_end,gpa DESC

Answer: F

NEW QUESTION 5
What is true about updates through a view?

• A. You cannot update a view with group function
• B. When you update a view group functions are automatically compute
• C. When you update a view only the constraints on the underlying table will be in effec
• D. When you update a view the constraints on the views always override the constraints on the underlying table

Answer: A

NEW QUESTION 6
For which action can you use the TO_DATE function?

• A. Convert any date literal to a date
• B. Convert any numeric literal to a date
• C. Convert any character literal to a date
• D. Convert any date to a character literal
• E. Format ’10-JAN-99’ to ‘January 10 1999’

Answer: C

NEW QUESTION 7
Which two statements are true regarding views? (Choose two.)

• A. A sub query that defines a view cannot include the GROUP BY clause
• B. A view is created with the sub query having the DISTINCT keyword can be updated
• C. A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table
• D. A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated

Answer: CD

Explanation:
Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view

NEW QUESTION 8
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 9
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

Evaluate the following query: Exhibit:

The above query produces an error on execution. What is the reason for the error?

• A. An alias cannot be used in an expression
• B. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression
• C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values
• D. The alias NAME should not be enclosed within double quotation marks

Answer: A

NEW QUESTION 10
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 11
Which substitution variable would you use if you want to reuse the variable without prompting the user each time?

• A. &
• B. ACCEPT
• C. PROMPT
• D. &&

Answer: D

Explanation:
To reuse the variable without prompting the user each time you can use && substitution
variable.
Incorrect Answers
A:This substitution variable will prompt the user each time.
B:ACCEPT is command, not substitution variable. It used to define more accurate or
specific prompt or when you want more output to display as the values are defined.
C:PROMPT is part of the ACCEPT command, it is not a variable.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 165-173
Chapter 4: Sub queries

NEW QUESTION 12
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 13
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
theDEPARTMENTS 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 colum
• B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID colum
• C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES tabl
• D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequenc
• E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES tabl
• F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID colum

Answer: CD

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.
Incorrect Answer:
AEMP_ID_SEQ sequence can be use to populate JOB_ID
BEMP_ID_SEQ sequence will not be invalidate when column in EMPLOYEE_ID is modify.
EEMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEES
table.
FEMP_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 14
The CUSTOMERS table has these columns:

The CUSTOMER_ID column is the primary key for the table.
You need to determine how dispersed your customer base is.
Which expression finds the number of different countries represented in the CUSTOMERS table?

• A. COUNT(UPPER(country_address))
• B. COUNT(DIFF(UPPER(country_address)))
• C. COUNT(UNIQUE(UPPER(country_address)))
• D. COUNT DISTINCT UPPER(country_address)
• E. COUNT(DISTINCT (UPPER(country_address)))

Answer: E

NEW QUESTION 15
What is true regarding sub queries?

• A. The inner query always sorts the results of the outer query
• B. The outer query always sorts the results of the inner query
• C. The outer query must return a value to the outer query
• D. The inner query returns a value to the outer query
• E. The inner query must always return a value or the outer query will give an error

Answer: D

Explanation: The inner query returns a value to the outer query. If the inner query does not return a value, the outer query does not return a result

NEW QUESTION 16
Examine the structure of the INVOICE table: Exhibit:

Which two SQL statements would execute successfully? (Choose two.)

• A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete') FROM invoice;
• B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available') FROM invoice;
• C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice;
• D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available') FROM invoice;

Answer: AC

Explanation:
The NVL2 Function
The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not. 5-6 The NVL function If the first term is not null, the second parameter is returned, else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null. The NVL2 function takes three mandatory parameters. Its syntax is NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG. They must either be of the same type, or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter.

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

• A. A foreign key cannot contain NULL value
• B. The column with a UNIQUE constraint can store NULLS .
• C. A constraint is enforced only for an INSERT operation on a tabl
• D. You can have more than one column in a table as part of a primary ke

Answer: BD

NEW QUESTION 18
View the Exhibit and examine the structure of the SALES and PRODUCTS tables.

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You want to list each product ID and the number of times it has been sold.
Evaluate the following query:
SQL>SELECT p.prod_id, COUNT(s.prod_id)
FROM products p _____________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN options can be used in the blank in the above query to get the required output? (Choose two.)

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

Answer: BC

NEW QUESTION 19
Examine the structure and data in the PRICE_LIST table:
Name Null Type
PROD_ID NOT NULL NUMBER(3) PROD_PRICE VARCHAR2(10) PROD_ID PROD_PRICE
100 $234.55 101 $6,509.75 102 $1,234
You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.
Which SQL statement would give the required result?

• A. SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST;
• B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST;
• C. SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;
• D. SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;

Answer: B

Explanation: Use TO_NUMBER on the prod_price column to convert from char to number
to be able to multiply it with 0.25. Then use the TO_CHAR function (with
formatting'$99,999.00') to convert the number back to char.
Incorrect:
Not C: Use the formatting'$99,999.00' with the TO_CHAR function, not with the
TO_NUMBER function.
Note:
*
Using the TO_CHAR Function The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available. The syntax is as follows: TO_CHAR(number1, [format], [nls_parameter]), The number1 parameter is mandatory and must be a value that either is or can be implicitly converted into a number. The optional format parameter may be used to specify numeric formatting information like width, currency symbol, the position of a decimal point, and group (or thousands) separators and must be enclosed in single
*
Syntax of Explicit Data Type Conversion Functions TO_NUMBER(char1, [format mask], [nls_parameters]) = num1 TO_CHAR(num1, [format mask], [nls_parameters]) = char1 TO_DATE(char1, [format mask], [nls_parameters]) = date1 TO_CHAR(date1, [format mask], [nls_parameters]) = char1

NEW QUESTION 20
Which tasks can be performed using SQL functions that are built into Oracle database? (Choose three.)

• A. finding the remainder of a division
• B. adding a number to a date for a resultant date value
• C. comparing two expressions to check whether they are equal
• D. checking whether a specified character exists in a given string
• E. removing trailing, leading, and embedded characters from a character string

Answer: ACD