Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)
Question : Which statement adds a column called salary
to the employees table having 100 rows, which
cannot contain null?
1. A
2. B
3. C
4. D
Correct Answer : 1
Explanation: NOT NULL Constraints
A NOT NULL constraint prohibits a column from containing nulls. The NULL keyword by itself does not actually define an integrity constraint, but you can specify it to explicitly permit a column to contain nulls. You must define NOT NULL and NULL using inline specification. If you specify neither NOT NULL nor NULL, then the default is NULL.
NOT NULL constraints are the only constraints you can specify inline on XMLType and VARRAY columns.
To satisfy a NOT NULL constraint, every row in the table must contain a value for the column.
Note: Oracle Database does not index table rows in which all key columns are null except in the case of bitmap indexes. Therefore, if you want an index on all rows of a table, then you must either specify NOT NULL constraints for at least one of the index key columns or create a bitmap index.
Restrictions on NOT NULL Constraints
NOT NULL constraints are subject to the following restrictions:
You cannot specify NULL or NOT NULL in a view constraint.
You cannot specify NULL or NOT NULL for an attribute of an object. Instead, use a CHECK constraint with the IS [NOT] NULL condition.
Question : Which two statements are true regarding single row functions?
A. MOD: returns the quotient of a division
B. TRUNC: can be used with number and date values
C. CONCAT: can be used to combine any number of values
D. SYSDATE: returns the database server current date and time
E. INSTR: can be used to find only the first occurrence of a character in a string
F. TRIM: can be used to remove all the occurrences of a character from a string
1. A,C
2. A,B
3. D,E
4. B,F
5. B,D
Correct Answer : 5
Explanation: ROUND: Rounds value to a specified decimal
TRUNC: Truncates value to a specified decimal , MOD: Returns remainder of division , SYSDATE is a date function that returns the current database server date and time. Date-Manipulation Functions : Date functions operate on Oracle dates. All date functions return a value of the DATE data type
except MONTHS_BETWEEN, which returns a numeric value. MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.
ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative.
NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string. LAST_DAY(date): Finds the date of the last day of the month that contains date The above list is a subset of the available date functions. ROUND and TRUNC number functions can also be used to manipulate the date values as shown below: ROUND(date[, 'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day. TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.
The CONCAT Function : The CONCAT function joins two character literals, columns, or expressions to yield one larger character expression. Numeric and date literals are implicitly cast as characters when they occur as parameters to the CONCAT function. Numeric or date expressions are evaluated before being converted to strings ready to be concatenated. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or expressions resulting in character values. The INSTR(source string, search item, [start position], [nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins: instr('http://www.domain.com', '.', 1, 2) = 18 The TRIM function literally trims off leading or trailing (or both) character strings from a given
source string
Question : Which two statements are true regarding the count function?
A. The count function can be used only for CHAR, VARCHAR2, and NUMBER data types.
B. Count (*) returns the number of rows including duplicate rows and rows containing null value in any of the columns.
C. Count (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
D. Count (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
E. A select statement using the COUNT function with a DISTINCT keyword cannot have a where clause.
1. A,C
2. A,B
3. D,E
4. B,E
5. B,D
Correct Answer : 5
Explanation: Using the COUNT Function
The COUNT function has three formats:
COUNT(*)
COUNT(expr)
COUNT(DISTINCT expr)
COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement,
including duplicate rows and rows containing null values in any of the columns. If a WHERE
clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the
condition in the WHERE clause.
In contrast,
COUNT(expr) returns the number of non-null values that are in the column identified by expr.
COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column
identified by expr.
Related 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
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
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
Question :
1. A,B,C
2. B,C,D
3. A,C,D
4. A,B,D
Question : Select the wrong statement from below.
1. TO_CHAR may convert date items to character items.
2. TO_DATE may convert character items to date items.
3. TO_CHAR may convert numbers to character items.
4. TO_DATE may convert date items to character items.
Question : What value is returned after executing the following statement?
TO_CHAR(1210.73, '9999.9')
1. 1210.9
2. 1210.7
3. 1210.73
4. 1210