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

Question : Examine the structure proposed for the transactions table:
Which two statements are true regarding the creation and storage of data in the above table
structure?
A. The CUST_STATUS column would give an error.
B. The TRANS_VALIDITY column would give an error.
C. The CUST_STATUS column would store exactly one character.
D. The CUST_CREDIT_LIMIT column would not be able to store decimal values.
E. The TRANS_VALIDITY column would have a maximum size of one character.
F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds



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


Correct Answer : 4

Explanation: VARCHAR2(size)Variable-length character data (A maximum size must be specified: minimum
size is 1; maximum size is 4, 000.)
CHAR [(size)] Fixed-length character data of length size bytes (Default and minimum size is 1;
maximum size is 2, 000.)
NUMBER [(p, s)] Number having precision p and scale s (Precision is the total number of decimal
digits and scale is the number of digits to the right of the decimal point; precision can range from 1
to 38, and scale can range from -84 to 127.)
DATE Date and time values to the nearest second between January 1, 4712 B.C., and December
31, 9999 A.D.






Question : You need to create a table for a banking application. One of the columns in the table has the following requirements:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and
subtracted with date data type without using conversion functions.
3) The maximum period of the credit provision in the application is 30 days.
4) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?

1. DATE
2. NUMBER
3. TIMESTAMP
4. INTERVAL DAY TO SECOND
5. INTERVAL YEAR TO MONTH

Correct Answer : 4
Explanation: INTERVAL DAY TO SECOND is an interval literal that stores a period of time using days, hours, minutes, and seconds.

An interval literal specifies a period of time, and Oracle supports two types of interval literals: YEAR_TO_MONTH and DAY TO SECOND. For DAY TO SECOND, you can specify these differences in terms in terms of days, hours, minutes, and seconds. DAY TO SECOND contains a leading field and may contain an optional trailing field. If trailing field is specified it must be less significant than the leading field. For example, INTERVAL MINUTE TO DAY is not valid.

A DAY TO MINUTE interval considers an interval of days to the nearest minute.








Question : Which three tasks can be performed using SQL functions built into Oracle Database?
A. Displaying a date in a nondefault format
B. Finding the number of characters in an expression
C. Substituting a character string in a text expression with a specified string
D. Combining more than two columns or expressions into a single column in the output

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



Correct Answer : 1
Use || for concatenating in oracle for combining two columns in one column and not functions.

Related Questions

---

Question : Examine the data in the ORD_ITEMS table:
Evaluate the given query:
Which statement is true regarding the outcome of the above query?

1. It gives an error because the having clause should be specified after the group by clause.
2. It gives an error because all the aggregate functions used in the having clause must be specified in the select list.
3. Access Mostly Uused Products by 50000+ Subscribers
double the minimum quantity of that item in the table.
4. 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.





Question : View the Exhibit and examine the data in the promotions table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the post
category that were launched before January 1, 2000.
Which SQL statement would you use?

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



Question : You need to create a table with the following column specifications:
1. Employee ID (numeric data type) for each employee
2. Employee Name (character data type) that stores the employee name
3. Access Mostly Uused Products by 50000+ Subscribers
4. Status (character data type), that contains the value 'active1 if no data is entered
5. Resume (character large object [CLOB] data type), which contains the resume submitted by the
employee
Which is the correct syntax to create this table?

1. It fails because the DB_8K_CACHE_SIZE parameter cannot be changed dynamically.
2. It succeeds only if memory is available from the autotuned components if SGA.
3. Access Mostly Uused Products by 50000+ Subscribers
4. It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_MAX_SIZE.


Question : Examine the structure of the sales table:
Evaluate the given create table statement:
Which two statements are true about the creation of the SALES1 table?


1. The SALES1 table is created with no rows but only a structure.
2. The SALES1 table would have primary key and unique constraints on the specified columns.
3. Access Mostly Uused Products by 50000+ Subscribers
4. The SALES1 table would have not null and unique constraints on the specified columns.
5. The SALES1 table would not be created because column-specified names in the select and
create table clauses do not match


Question : Which two statements are true regarding subqueries?
A. A subquery can retrieve zero or more rows.
B. Only two subqueries can be placed at one level.
C. A subquery can be used only in SQL query statements.
D. A subquery can appear on either side of a comparison operator.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT
statement.

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


Question : Examine the structure of the products table:
You want to display the names of the products that have the highest total value for UNIT_PRICE * QTY_IN_HAND.
Which SQL statement gives the required output?

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