Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)

Question : Examine the structure of the transactions table:
You want to display the date, time, and transaction amount of transactions that where done before
12 noon. The value zero should be displayed for transactions where the transaction amount has
not been entered.
Which query gives the required result?


1. A
2. B
3. Access Mostly Uused Products by 50000+ Subscribers
4. D



Correct Answer : Get Lastest Questions and Answer :
Exp:







Question : What is the outcome?

1. It executes successfully and gives the correct output.
2. It executes successfully but does not give the correct output.
3. Access Mostly Uused Products by 50000+ Subscribers
4. It generates an error because the SUBSTR function cannot be nested in the CONCAT function.


Correct Answer : Get Lastest Questions and Answer : Exp: REPLACE (text, search_string, replacement_string)
Searches a text expression for a character string and, if found, replaces it with a specified
replacement string
The REPLACE Function
The REPLACE function replaces all occurrences of a search item in a source string with a
replacement term and returns the modified source string. If the length of the replacement term is
different from that of the search item, then the lengths of the returned and source strings will be
different. If the search string is not found, the source string is returned unchanged. Numeric and
date literals and expressions are evaluated before being implicitly cast as characters when they
occur as parameters to the REPLACE function.
The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is
REPLACE (source string, search item, [replacement term]).
If the replacement term parameter is omitted, each occurrence of the search item is removed from
the source string. In other words, the search item is replaced by an empty string. .
The following queries illustrate the REPLACE function with numeric and date expressions:
Query 1: select replace(10000-3, '9', '85') from dual
Query 2: select replace(sysdate, 'DEC', 'NOV') from dual





Question : Evaluate the following SQL commands:
The command to create a table fails. Identify the two reasons for the SQL statement failure?
A. You cannot use SYSDATE in the condition of a check constraint.
B. You cannot use the BETWEEN clause in the condition of a check constraint.
C. You cannot use the NEXTVAL sequence value as a default value for a column.
D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the foreign key.

1. A,B
2. B,C
3. Access Mostly Uused Products by 50000+ Subscribers
4. C,D
5. B,D


Correct Answer : Get Lastest Questions and Answer :

Exp: CHECK Constraint
The CHECK constraint defines a condition that each row must satisfy. The condition can use the
same constructs as the query conditions, with the following exceptions:
References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
Calls to SYSDATE, UID, USER, and USERENV functions
Queries that refer to other values in other rows
A single column can have multiple CHECK constraints that refer to the column in its definition.
There is no limit to the number of CHECK constraints that you can define on a column.
CHECK constraints can be defined at the column level or table level.
CREATE TABLE employees
(...
Salary NUMBER(8, 2) CONSTRAINT emp_salary_min
CHECK (salary > 0),

Related Questions

---

Question : 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.

1. A,B,C
2. B,C,E
3. A,C,E
4. D,E,F
4. A,B,D



Question : Evaluate the following SQL statement:
Which statement is true regarding the above query if one of the values generated by the subquery is null?

1. It produces an error.
2. It executes but returns no rows.
3. It generates output for null as well as the other values produced by the subquery.
4. It ignores the null value and generates output for the other values produced by the subquery.



Question :

1. A
2. B
3. C
4. D


Question : Which statement is true regarding the default behavior of the order by clause?

1. In a character sort, the values are case-sensitive.
2. NULL values are not considered at all by the sort operation.
3. Only those columns that are specified in the select list can be used in the order by clause.
4. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.



Question : View the Exhibits and
examine the structures of
the products and sales tables.
Which two SQL statements
would give the same output?


1. A,C
2. B,D
3. C,D
4. A,D
5. B,C



Question :

1. A
2. B
3. C
4. D