1. What is true about data types in Oracle DB?

1. They are given to columns for sorting purposes.
2. They are given to columns for a structured representation in a table.
3. They are given to columns to constrain the nature of the data it can store.
4. They are not mandatory.

Answer: C. Data types define the nature of data which a column can store in a table. A column can store only one type of data. The primary data types available in Oracle are NUMBER, VARCHAR2, and DATE.

2. What is true about nested functions?

1. There is a limit to use the Nested functions only 5 times in Oracle DB.
2. They are evaluated from the outermost level to the innermost level.
3. They are evaluated from the innermost level to the outermost level.
4. All the functions in a Nested expression return the same data type.

Answer: C. Single row functions can group functions can be nested in a SELECT query in which the innermost function is the first one to be executed. The result of the execution of innermost function serves as the input for the outer function.

3. Which of the following functions simplify working with columns that potentially contain null values?

1. Nested functions
2. General functions
3. Conditional functions
4. None of the above

Answer: B. The general functions like NVL, NVL2, NULLIF, and COALESCE are used to pacify the effect of NULL while displaying the query results. They bypass the NULL values by assigning an alternative value.

4. Which of the following data types are appropriate for general functions?

1. VARCHAR2
2. NUMBER
3. DATE
4. All Datatypes

Answer: D. General functions are usually compatible with all primary data types like NUMBER, VARCHAR2 and DATE.

5. What is true about the COALESCE function?

1. It accepts minimum 2 and maximum 5 input parameters
2. It always returns the first NULL value among the input parameters
3. It can accept unlimited number of input parameters
4. It returns the first non-null parameter else it returns a null.

Answer: C, D. The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2,Ö,exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE is a general form of the NVL function, as the following two equations illustrate: COALESCE(expr1,expr2) = NVL(expr1,expr2), COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3))

6. How many input parameters are mandatory in NVL function?

1. 0
2. 1
3. 2
4. 3

Answer: C. The NVL function takes two mandatory parameters. Its syntax is NVL(original, ifnull), where original represents the term being tested and ifnull is the result returned if the original term evaluates to null. The data types of the original and ifnull parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert ifnull to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter.

7. What is wrong in the following statement?

NVL (ifnull, original)

1. There is nothing wrong
2. The parameter original is not required
3. The parameter ”ifnull” is not required
4. The correct statement is NVL (original,ifnull)

Answer: D. The NVL function evaluates whether a column or expression of any data type is null or not. If the term is null, an alternative not null value is returned; otherwise, the initial term is returned.

8. What will be the output of the following query?

SELECT NVL(1234) FROM dual;

1. 1234
2. 1000
3. NULL
4. ORA-00909:invalid number of arguments error

Answer: D. he NVL function takes two mandatory parameters. Its syntax is NVL(original, ifnull), where original represents the term being tested and ifnull is the result returned if the original term evaluates to null.

9. What will be output of the following query?

SELECT NVL(1234,'' '') FROM dual;

1. A white space i.e. ” ”
2. 1234
3. NULL value
4. ORA-01722: invalid number

Answer: D. The data types of the original and ifnull parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert ifnull to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter. The 1234 should be in single quotes. Implicit conversion of data type doesn”t happen in this case.

10. What will be outcome of the following query?

SELECT NVL(SUBSTR(''abc'',-4),''SUBSTR didn''t work'') FROM dual;

1. abc
2. bc
3. c
4. SUBSTR didn”t work

Answer: D.

11. You need to extract a report which gives the first name, last name and the commission percentage earned by all the employees in department 100. The report should not have any columns which are empty. All the columns should have at least a ”0” if there is no value for them. Which of the following queries will fulfill this requirement? (Consider the table structure as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

1. SELECT first_name, last_name, commission_pct*salary FROM employees WHERE department_id  = 100;

2. SELECT first_name, last_name, NVL(commission_pct*salary,0) monthly_commission FROM employees WHERE department_id  = 100;

3. SELECT first_name, last_name, NVL(commission_pct,0)*salary monthly_commission FROM employees WHERE department_id  = 100;

4. SELECT first_name, last_name, commission_pct*salary FROM employees;

Answer: B, C.

12. What are the types of Data conversions in Oracle DB?

1. Implicit conversions
2. Explicit conversions
3. External conversions
4. Physical conversions

Answer: A, B. TO_CHAR, TO_NUMBER and TO_DATE are the three most widely used conversion functions and are discussed in detail. The TO_CHAR function converts numeric and date information into characters, while TO_NUMBER and TO_DATE convert character data into numbers and dates, respectively.

13. What happens during an implicit conversion in Oracle DB?

1. Oracle DB implicitly converts one data type to the expected data type
2. The user has to convert the data type to the expected data type
3. Oracle DB doesn”t convert any data type
4. Implicit conversion can be controlled by the user

Answer: A. If Oracle database implicitly converts a value to a compatible data type, it is known as Implicit conversion.

14. What happens during an explicit conversion in Oracle DB?

1. Oracle DB converts one data type to the other and displays to the user explicitly
2. Oracle DB prompts the user to convert one data type to the other and then converts the data type
3. The user uses conversion functions supplied by Oracle DB to convert data types
4. The data type is never converted explicitly in Oracle DB

Answer: C. When the programmer has to programmatically convert a value using one of the conversion functions, it is known as explicit conversion.

15. Which of the following conversion methods is recommended for the reliability of SQL statements in Oracle DB?

1. Implicit and Explicit conversions
2. Implicit conversion
3. Explicit conversion
4. None of the above

Answer: C. TO_CHAR, TO_NUMBER and TO_DATE are the three most widely used conversion functions and are discussed in detail. The TO_CHAR function converts numeric and date information into characters, while TO_NUMBER and TO_DATE convert character data into numbers and dates, respectively.

16. Which of the following is a valid implicit conversion performed by Oracle?

1. NUMBER TO VARCHAR2
2. NUMBER TO DATE
3. CHAR TO DATE
4. DATE TO VARCHAR2

Answer: A, D.

17. Examine the structure of the EMPLOYEES table as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

Which conversion method is applied to the following query?

SELECT first_name, salary
FROM employees
WHERE hire_date > ''01-JAN-13 

1. No conversion happens and this query results in an ORA error
2. Explicit conversion
3. Implicit conversion
4. Both Implicit and explicit conversion

Answer: C. The string (VARCHAR2 or CHAR) is converted implicitly to a DATE by Oracle giving the required output as selected.

18. Which of the following is supported with respect to expression evaluation is supported by Oracle DB?

1. NUMBER TO CHAR
2. DATE TO VARCHAR2
3. CHAR to DATE
4. NUMBER TO DATE

Answer: A, B. DATE and NUMBER values can easily be converted to their character equivalents. Implicit character to date conversions are possible when the character string conforms to the following date patterns: [D|DD] separator1 [MON|MONTH] separator2 [R|RR|YY|YYYY].

19. What is mandatory for and implicit conversion of CHAR to NUMBER in Oracle to work?

1. Nothing in particular is mandatory for this type of conversion
2. It is mandatory that the character string represents a valid number
3. No such conversion is supported by Oracle
4. CHAR to NUMBER has to be converted explicitly only

Answer: B. Character data must represent a valid number to be considered for implicit conversion.

20. Which of the following expressions can be used explicitly for a conversion of a CHAR to a NUMBER?

1. TO_CHAR
2. Use TO_DATE and then convert the date to a NUMBER
3. TO_NUMBER
4. Such conversion is not possible

Answer: C. The TO_NUMBER function returns an item of type NUMBER. Character strings converted into numbers must be suitably formatted so that any nonnumeric components are translated or stripped away with an appropriate format mask.

21. Which of the following expressions can be used explicitly for a conversion of a NUMBER to a CHAR?

1. TO_CHAR
2. Use TO_DATE and then convert the date to a NUMBER
3. TO_NUMBER
4. Such conversion is not possible

Answer: A. The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available.

22. Which of the following expressions can be used explicitly for a conversion of a CHAR to a DATE?

1. TO_CHAR
2. Use TO_DATE and then convert the date to a NUMBER
3. TO_NUMBER
4. TO_DATE

Answer: D. The TO_DATE function returns an item of type DATE. Character strings converted to dates may contain all or just a subset of the date time elements comprising a DATE. When strings with only a subset of the date time elements are converted, Oracle provides default values to construct a complete date. Components of character strings are associated with different date time elements using a format model or mask.

23. Which of the following expressions can be used explicitly for a conversion of a DATE to a CHAR?

1. TO_CHAR
2. TO_DATE
3. TO_NUMBER
4. Such conversion is not possible

Answer: A. The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available.

24. Which of the following are the functions for explicit conversion provided by Oracle to convert one data type to the other?

1. TO_CHAR
2. TO_DATE
3. TO_NUMBER
4. All of the above

Answer: D. TO_CHAR, TO_NUMBER and TO_DATE are the three most widely used conversion functions and are discussed in detail. The TO_CHAR function converts numeric and date information into characters, while TO_NUMBER and TO_DATE convert character data into numbers and dates, respectively.

25. Interpret the working of the below function.

TO_CHAR(number/date, [format], [nlsparameters])

1. It converts a VARCHAR2 to a CHAR
2. It converts a number/date to a VARCHAR2 string with the format model [format]
3. It converts a VARCHAR2 to a NUMBER or a DATE
4. [NLSPARAMETERS] is mandatory in the statement

Answer: B. The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available.

26. What does the [NLSPARAMETERS] clause in the following statement specify?

TO_CHAR(number/date, [format], [nlsparameters])

1. Decimal character
2. Group separator
3. Currency symbol
4. All of the above

Answer: D.

27. What value will the TO_CHAR (number/date, [format], [nlsparameters]) use if the [nlsparameters] parameter is omitted?

1. It throws an ORA error
2. The [nlsparameters] parameter is mandatory and it can”t be omitted.
3. It will use the default parameter values for the session.
4. It will use the default parameter values set during the database design.

Answer: C. By default, the TO_CHAR function considers the NLS settings of the current active session.

28. What is true about the following statement?

TO_CHAR(number/date, [format], [nlsparameters])

1. The nlsparameters parameter specifies the language in which the month and day names are returned.
2. The nlsparameters parameter is omitted on the execution of the above statement.
3. The nlsparameters parameter will return a NULL whether specified or not
4. The nlsparameters parameter will return the default language of the DB on every execution

Answer: A.

29. What is true regarding the following statement in Oracle DB?

TO_NUMBER(char, [format],[nlsparameters])

1. It converts any string to a number in the format specified in [format]
2. It converts only a NUMBER to the desired format as mentioned in [format]
3. It converts a string with digits to a number in the format specified in [format]
4. The result of this function is always a character

Answer: C. The TO_NUMBER function returns an item of type NUMBER. Character strings converted into numbers must be suitably formatted so that any nonnumeric components are translated or stripped away with an appropriate format mask.

30. What is true regarding the following statement in Oracle DB?

TO_DATE(char, [format],[nlsparameters])

1. It converts any string to a DATE in the format specified in [format]
2. It converts only a DATE to another DATE in the desired format as mentioned in [format]
3. It converts a string with DATE to a number in the format specified in [format]
4. It converts a string with DATE to a DATE in the format specified in [format]

Answer: C. The TO_DATE function returns an item of type DATE. Character strings converted to dates may contain all or just a subset of the date time elements comprising a DATE.

31. What will be the result if the [format] parameter in the following statement is omitted?

TO_DATE(char, [format],[nlsparameters])

1. It will return a DATE value with the format DD-MON-YY
2. It will return a DATE value with the format DD-MON-RR
3. It will return a character value
4. It will return a NUMBER value

Answer: A.

32. Which of the following is true about the following statement in Oracle DB?

TO_CHAR(date, ''frmt'')

1. The fmt can be written in double quotes as well as single quotes.
2. Case of the fmt doesn”t matter in this function
3. Fmt can include any character or NUMBER
4. The fmt has to be enclosed in single quotes and has to be a valid date format.

Answer: D.

33. What will the following statement on execution yield?

SELECT TO_CHAR (''01-JAN-13'' ''DD-MON-YY'') FROM dual;

1. 01-JAN-13
2. 01-01-2013
3. An ORA error
4. 1-JAN-13

Answer: C. The parameters ”01-JAN-13” and format model should be separated by a “,”.

34. What is true about the [fmt] parameter in the following statement?

 TO_DATE ([date as string],[format])

1. The fmt can be written in double quotes as well as single quotes.
2. Case of the fmt doesn”t matter in this function
3. The [fmt] parameter has an ”fm” element which removes spaces and suppresses leading zeroes.
4. Fmt can include any character or NUMBER

Answer: C.

35. What is the abbreviation for the FM modifier in Oracle DB?

1. First Move
2. Filter Mode
3. Fill Mode
4. First Mode

Answer: C. The format model ”fm” stands for Fill Mode.

36. What is the abbreviation for the FX modifier in Oracle DB?

1. First Expression
2. Fill Expression
3. First Extra
4. Format Exact

Answer: D. The format model ”fm” stands for Format Exact.

37. How many maximum places for display will Oracle DB allocate to the Month element in the following statement?

SELECT TO_CHAR (sysdate, ''fmMonth'') FROM dual;

1. 5
2. 6
3. 7
4. 9

Answer: D. The longest word for Month is ”September” and hence Oracle pads according to 9 places for the display of the Month parameter.

38. Which of the following is true about the FM modifier in Oracle DB?

1. This modifier suppresses blank padding in the subsequent character elements such as MONTH
2. This modifier suppresses leading zeroes for subsequent number of elements such as MI
3. This modifier has no effect on the date format
4. This modifier is mandatory for all the date formats used with the function TO_CHAR

Answer: A, B.

39. What happens when the FM modifier is not used in the DATE format model in Oracle DB?

1. The result of the character element is left padded with blanks to a variable length
2. The result of the character element is right padded with blanks to a fixed length
3. The leading zeroes are not returned in the result of the character element
4. The length of the return value is fixed if the FM modifier is used

Answer: B.

40. How is a number result justified in the output buffer in a number format element of a TO_CHAR function when the FM modifier is used?

1. Right
2. Left
3. Centre
4. None of the above

Answer: B. The FM modifier suppresses blanks added to the left of the number.

41. What will be the outcome of the following query?

SELECT TO_CHAR (TO_DATE(''01-JAN-13''), ''fmDD Month YYYY'') FROM dual;

1. 1 January2013
2. 1 January 2013
3. 1 Jan 2013
4. 1 January 13

Answer: B. The TO_CHAR formats the input date as per the given format model.

42. How many spaces will be added to the ”DD” of the following query?

SELECT TO_CHAR (TO_DATE(''01-JAN-13'',''DD-MON-YY''), ''fmDD Month YYYY'') FROM dual;

1. 0
2. 1
3. 2
4. 3

Answer: A. The FM modifier removes all the padded spaces from the Date format..

43. What will be the outcome of the following query?

SELECT TO_CHAR (TO_DATE(''01-JAN-13'',''DD-MON-YY''), ''fmDdspth "of" Month YYYY fmHH:MI:SS AM'') FROM dual;

1. It will return an ORA error because of the use of double quotes in the Date format
2. 1st January 2013
3. First of JANUARY 2013 12:00:00 AM
4. First of January 2013 12:00:00 AM

Answer: D. The TO_CHAR formats the input date ”01-JAN-13” as per the given format.

44. Which of the following specifies the exact match for the character argument and the date format model of a TO_DATE function?

1. TO_DATE
2. TO_CHAR
3. FM
4. FX

Answer: D.

45. What is true about the FX modifier in the Oracle DB?

1. It is case sensitive
2. It ignores the spaces in the character argument when matching with the Date format model mentioned
3. The punctuations and quoted text in the character argument do not necessarily match the format model
4. None of the above

Answer: D.

46. What will be the outcome of the following query?

SELECT TO_DATE (''January   21, 2013'' , ''fxMonth DD, YYYY'') FROM dual;

1. It will execute successfully
2. It will give the result January 21, 2013
3. It creates an ORA error
4. It will give the result JANUARY 21, 2013

Answer: C. The character argument should match exactly with the format model if FX is used. Here the extra spaces after January are mismatching.

47. What is true about the FX modifier in Oracle DB?

1. It can be used with TO_CHAR
2. It can be used with both TO_CHAR and TO_DATE
3. It can be used with only TO_DATE
4. None of the above

Answer: C. The FX format modifier can only be used with the TO_DATE function.

48. Assuming the SYSDATE is 01-JAN-13, what will be the outcome of the following query?

SELECT TO_CHAR (SYSDATE, ''DDTH'') FROM dual;

1. 1st of January
2. 1st
3. 1 ST
4. 01ST

Answer: D.

49. Assuming the SYSDATE is 01-JAN-13, what will be the outcome of the following query?

SELECT TO_CHAR (SYSDATE, ''fmDDTH'') FROM dual;

1. 1st of January
2. 1st
3. 1ST
4. 01ST

Answer: C.

50. Assuming the SYSDATE is 01-JAN-13 and falls on Tuesday, what will be the outcome of the following query?

SELECT TO_CHAR (SYSDATE, ''fmDay'')||''''''s Meeting'' FROM dual;

1. Tuesday
2. TUESDAY
3. TUESDAY”s Meeting
4. Tuesday”s Meeting

Answer: D.

51. What will be the outcome of the following query?

SELECT TO_DATE(''01 / JAN / 13'',''DD-MON-YY'') FROM dual;

1. ORA error
2. 01-JAN-2013
3. 01-JANUARY-13
4. 01-JAN-13

Answer: D.

52. What will be the outcome of the following query?

SELECT TO_DATE(''01 ## JAN / 13'',''DD-MON-YY'') FROM dual;

1. ORA error
2. 01-JAN-2013
3. 01-JANUARY-13
4. 01-JAN-13

Answer: A. Use a single delimiter between the dates.

53. What will be the outcome of the following query?

SELECT TO_DATE(''01/JAN/13'',''fxDD-MON-YY'') FROM dual;

1. 01-JAN-2013
2. ORA error
3. 01-JAN-13
4. 01-JANUARY-13

Answer: B. With the format exact modifier, the input literal must match the format string.

54. What will be the outcome of the following query?

SELECT TO_DATE(''01-JAN-13'',''fxDD-MON-YY'') FROM dual;

1. 01-JAN-2013
2. ORA error
3. 01-JAN-13
4. 01-JANUARY-13

Answer: C.

55. What will be the outcome of the following query?

SELECT TO_DATE (''11-JAN-2013'',''fxDD-MON-YYYY'') FROM dual;

1. 11-JAN-13
2. 11-01-13
3. 11-JAN-2013
4. ORA error

Answer: C.

56. An employee Allen was hired on 1-JAN -13. What will be the outcome of the following query? (Assume that the NLS parameter for the session is set to DD-MON-YY)

SELECT TO_DATE(hire_date, ''fxfmDD-MON-YY'') FROM employees WHERE first_name=''ALLEN 

1. ORA error
2. 01-JAN-2013
3. 1-JAN-13
4. 1-JAN-2013

Answer: C.

57. What will be the outcome of the following query?

SELECT TO_CHAR(TO_DATE (''01-JAN-2013''), ''DD-Month-RR'') FROM dual;

1. 01-JAN-13
2. 01-01-2013
3. 01-January-13
4. 01-January -13

Answer: D. The Month modifier is padded up to 9 places with spaces.

Examine the structure of the EMPLOYEES table as given and answer the questions 58 and 59 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

58. You need to list out the first and the last names for all the employees who were hired before the year 1990. Which of the following WHERE statements will give you the required results? (Assume that this list is to be generated on ”01-JAN-2013”)

1. WHERE TO_DATE (hire_date, ”DD-MON-YY”)
2. WHERE TO_DATE (hire_date, ”DD-MON-YYYY”)
3. WHERE TO_DATE (hire_date, ”DD-MON-YY”)
4. WHERE TO_DATE (hire_date, ”DD-MON-RR”)

Answer: D. Using the RR format will consider the year portion of the date between 1950 and 1999.

59. Which of the following is an example of a nested function?

1. SELECT lower(last_name) FROM employees;

2. SELECT upper (last_name) FROM employees;

3. SELECT concat (first_name, last_name) FROM employees;

4. SELECT upper (concat(SUBSTR(first_name,1,6),''_UK'')) FROM employees; 

Answer: D. More than one functions in a function is known as nesting of functions.

60. What is true about the COALESCE function in Oracle DB?

1. It can take only 2 arguments
2. All the arguments in the COALESCE function can be of different data types
3. All the arguments in the COALESCE function should be of the same data type
4. None of the above

Answer: C. The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2,Ö,exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on.

61. Which of the following functions is used for conditional expressions?

1. TO_CHAR
2. COALESCE
3. NVL
4. CASE

Answer: D. The CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression.

62. What will be the outcome of the following query?

SELECT TO_CHAR(TO_DATE(''01-JAN-13'',''DD-MON-YY''),''dy-mon-yyyy'') FROM dual;

1. 01-jan-2013
2. 01-jan-13
3. tue-jan-13
4. tue-jan-2013

Answer: D. The format model ”dy” spells the first three letters of the day from the input date. ”DY” will give ìTUEî and not ìtueî as in the query given above.

63. What will be the outcome of the following query?

SELECT TO_CHAR(TO_DATE(''01-JAN-13'',''DD-MON-YY''),''fmDAY-mon-yyyy'') FROM dual;

1. 1-jan-2013
2. 01-jan-13
3. TUESDAY -jan-13
4. TUESDAY-jan-2013

Answer: D. fmDAY (for all capital letters) or fmday (for all small letters) format model will spell the day of the input date without any trailing or leading spaces.

64. What will be the outcome of the following query?

SELECT TO_CHAR(TO_DATE(''19-JUN-13''),''qth'') FROM dual;

1. 1st
2. 2nd
3. 3rd
4. 4th

Answer: B. The format model ”q” gives the quarter in which the given date falls. In the given query, APR-JUN is the 2nd quarter.

Examine the structure of the EMPLOYEES table as given and answer the questions 65 to 67 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

65. Some employees joined company ABC in the second week of the current year i.e. 2013. You need to list out the first names, last names and the department IDs for all these employees. Which of the following queries will give you the required result?

1. SELECT first_name, last_name, department_id  FROM employees WHERE TO_DATE(hire_date,''w'') >2;

2. SELECT first_name, last_name, department_id  FROM employees WHERE TO_DATE(hire_date,''w'') between 1 and 2;

3. SELECT first_name, last_name, department_id  FROM employees WHERE TO_CHAR(hire_date,''w'') 

4. SELECT first_name, last_name, department_id  FROM employees WHERE TO_CHAR(sysdate,''ww'') =2;

Answer: D. The format model ”ww” gives the week of the year.

66. The management of a company ”ABC” wants to find out how many employees were hired in the 3rd quarter of the year 2012. Which of the following queries will give the required result?

1. SELECT count(employee_id  ) FROM employees WHERE TO_CHAR(hire_date, ''q'') > 1;

2. SELECT count(employee_id  ) FROM employees Where TO_CHAR(hire_date, ''q'') = 3;

3. SELECT employee_id   FROM employees Where TO_CHAR(hire_date, ''q'') = 3;

4. SELECT count(employee_id  ) FROM employees Where TO_CHAR(hire_date, ''q'') between 0 and 3;

Answer: B. The format model ”q” gives the quarter of a year.

67. A certificate of achievement has to be printed and presented to all those employees who joined the organization before the year 2008 and are still a part of the organization. The printing of the first name, last name and the dates will happen by using placeholders fetched from a query. The Certificate should contain all the digits spelled out. Example: Tuesday, the 1st of January, Two Thousand and eight. The final text of the Certificate should be in the following form: This is to certify that first_name last_name who joined the organization on Tuesday, the 1st of January, Two Thousand and eight has successfully completed 5 glorious years in the company. Which of the following queries will be helpful in printing the dates as in the required format?

1. SELECT TO_CHAR (hire_date, ''fmDay,"the "ddth "of " Month, Yysp.'') FROM employees;

2. SELECT TO_CHAR (hire_date, ''Day,"the "ddth "of " Mon, Yyyy.'') FROM employees;

3. SELECT TO_CHAR (hire_date, ''fmDAY,"the "ddth "of " Month, Ysp.'') FROM employees;

4. SELECT TO_CHAR (hire_date, ''fmDay,"the "ddth "of " MONTH, Rsp.'') FROM employees;

Answer: A. The ”sp” identifier spells the year in simple english language.

68. A report has to be generated which creates an audit history table for all the employees from an available paper source. The paper source only has data for the year 2011 when the employees were hired. This data only has the year of the hire date. You need to put the date in the audit-history table as 1st of January of that particular year (without leading zeroes and spaces). Which of the following clauses will achieve this requirement?

1. TO_DATE(”2011”,”YYYY”)
2. TO_CHAR (TO_DATE (”2011”,”YYYY”),”fmMM/DD/YYYY”)
3. TO_CHAR(”2011”,”DD-MON-YYYY”)
4. TO_DATE (”01-01-2011”,”DD-MM-YYYY”)

Answer: B.

69. What will be the outcome of the following query?

SELECT TO_NUMBER (''$3000'') FROM dual;

1. 3000
2. $3000
3. NULL
4. ORA error

Answer: D. The query throws error of “ORA-01722: invalid number” because the given string cannot be recognized in numbers.

70. What will be the outcome of the following query?

SELECT TO_NUMBER(''$3,000.67'',''$999,999.99'') FROM dual;

1. $3000.67
2. 3000
3. 3000.67
4. ORA error as the input string has lesser characters than the format model mentioned.

Answer: C. The appropriate format model helps the TO_NUMBER to convert given string in numbers.

71. What will be the outcome of the following query?

SELECT TO_NUMBER(''$3,000,000.67'',''$999,999.99'') FROM dual;

1. $3,000,000.67
2. 3000,000.67
3. 3000.67
4. ORA error as the format model has lesser characters than the input string. It should be the same.

Answer: D.

72. What will the following query yield?

SELECT TO_NUMBER(''456.23'',''999.99'') FROM dual;

1. ORA error
2. 456.23
3. 456
4. None of the above

Answer: B.

73. What is true about the nested functions?

1. Nesting implies the use of output from one function as an input to another.
2. Nesting can be applied up to 3 levels of nesting.
3. Nesting are applied to Multiple row functions to any level of depth
4. None of the above

Answer: A. The output from a function execution is used as input for its preceding function.

74. What will be the result of the following query?

SELECT NULLIF(1,2-1) FROM dual;

1. 0
2. 1
3. NULL
4. None of the above

Answer: C. The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. Here 1 and the expression “2-1” are considered equal by oracle and hence NULL is returned.

75. What will be the outcome of the following query?

SELECT NULLIF(''01-JAN-2013'',''01-JAN-13'') FROM dual;

1. 1-JAN-13
2. 01-JAN-2013
3. NULL
4. ORA error

Answer: B. Since the lengths for both the dates is different, the first parameter is returned.

76. What is the ratio of mandatory parameters to optional parameters in the COALESCE function in Oracle DB?

1. 0:1
2. 1:2
3. 2: any number
4. None of the above

Answer: C. The COALESCE function takes two mandatory parameters and any number of optional parameters. OALESCE is a general form of the NVL function, as the following two equations illustrate: COALESCE(expr1,expr2) = NVL(expr1,expr2), COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3)).

77. Which of the following equations are true?

1. COALESCE(expr1,expr2) = NVL(expr1,expr2)
2. COALESCE(expr1,expr2) = NVL2(expr1,expr2,expr3)
3. COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3))
4. All of the above

Answer: A, C.

78. Which of the following is the correct syntax of NVL2?

1. NVL(original,ifnotnull)
2. NVL2(original,ifnull,ifnotnull)
3. NVL(original,NULL)
4. NVL(original,ifnull) and NVL2(original,ifnotnull,ifnull)

Answer: D.

79. Which of the following functions is an ANSI standard keyword inherited in Oracle?

1. CASE
2. DECODE
3. Both A and B
4. None of the above

Answer: A. CASE is an ANSI SQL compliant and not Oracle specific.

80. What is true about the DECODE statement in Oracle DB?

DECODE(expr1,comp1,iftrue1,comp2,[iftrue2]) 

1. Comp2 is not optional
2. If expr1 is equal to comp1 then comp2 is returned
3. If expr1 is equal to comp1 then iftrue1 is returned
4. None of the above

Answer: C. The DECODE function implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. The DECODE function takes at least three mandatory parameters, but can take many more.

81. What is true about the parameters in the DECODE function?

1. All parameters must be VARCHAR2
2. No expressions can be parameters to the DECODE function
3. All parameters must be NUMBER
4. The return data type is the same as that of the first matching comparison item.

Answer: D. The DECODE function implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not.

82. What will be the outcome of the following query?

SELECT DECODE (null,null,''expr3'') FROM dual;

1. NULL
2. 0
3. Expr3
4. ORA error

Answer: C. DECODE considers two NULL values to be equivalent. One of the anomalies of NULL in Oracle.

83. What will be the outcome of the following query?

SELECT DECODE (''elephant'',''rat'',''lion'',''tiger'',''cat'',''squirrel'',''elephant'',''koala'',''rat'',''And it continues'') FROM dual;

1. elephant
2. rat
3. koala
4. And it continues

Answer: D. The DECODE function takes at least three mandatory parameters, but can take many more.

84. What is the number of minimum mandatory parameters for the CASE expression in Oracle DB?

1. 0
2. 1
3. 2
4. 3

Answer: D. The CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression. It takes atleast 3 mandatory parameters but it can take more also.

85. Which of the following keyword combinations is used to enclose a CASE statement in Oracle DB?

1. CASEÖEND IF;
2. IFÖEND IF;
3. CASEÖ;
4. CASEÖEND;

Answer: D.

86. Which of the following values is returned in case of a false value if the ELSE block in the CASE statement is undefined?

1. 0
2. NULL
3. Either 0 or NULL
4. None of the above

Answer: B.

87. Which of the following options is true if more than one WHEN..THEN levels exist in a CASE statement?

1. The CASE searches or compares only the first level and exists without checking other levels of WHENÖTHEN.
2. The CASE statement will search in all the levels of WHENÖTHEN until it finds a match.
3. Both A and B
4. None of the above

Answer: B.

88. What data types can be the search, comparison and result parameters in the CASE statement?

1. VARCHAR2
2. DATE
3. NUMBER
4. Column values, literals and expressions

Answer: D.

89. The CASE statement cannot be used in which of the following parts of an Oracle SQL query?

1. SELECT
2. None of these options
3. WHERE
4. ORDER BY

Answer: B.

90. Examine the structure of the EMPLOYEES table as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

What will be the outcome of the following query in Oracle DB?

SELECT first_name, salary,
CASE department_id WHEN 100
THEN ''Accounts''
WHEN 101
THEN ''Human Resources''
WHEN 102
THEN ''Sales''
ELSE ''Unknown''
 END
 FROM employees;

1. It will create an ORA error as more than one WHENÖTHEN statements cannot be written in the CASE statement.
2. It will display the department IDs as 100,101,102
3. It will only display ”Accounts” where ever the department ID 100 appears and ignore the remaining commands.
4. None of the above

Answer: D. The CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression.

91. What is the maximum number of WHENÖTHEN levels a CASE statement in Oracle DB can have?

1. Unlimited
2. 1000
3. 216
4. 255

Answer: D.

92. What will be the outcome of the following query?

SELECT NVL2(
       NULLIF (''BMW'',''AUDI''),
       ''HYUNDAI'',
       ''FERRARI''
       )
       FROM dual;

1. BMW
2. FERRARI
3. NULL
4. HYUNDAI

Answer: D. 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. If the first term is not null, the second parameter is returned, else the third parameter is returned.

93. Assuming the SYSDATE is 01-JAN-13 , what will the following query yield?

SELECT TO_CHAR (sysdate, ''fmddth" of" Month YYYY'') FROM dual;

1. 1st January, 2013
2. 1st of Jan, 2013
3. 01st of January, 2013
4. 1st of January 2013

Answer: D. The ìthî format model gives the day of the date as ìstî or ìthî.

94. What will be the outcome of the following query?

SELECT TO_CHAR (TO_DATE(''01-JAN-13'',''DD-MON-YY''), ''MmSP Month Yyyysp'') FROM dual;

1. First January Two Thousand Thirteen
2. First JAN Two Thousand Thirteen
3. One January Two Thousand Thirteen
4. None of the above

Answer: C.

95. What will be the outcome of the following query?

SELECT TO_CHAR (TO_DATE(''01-JAN-13'',''DD-MON-YY''), ''DD-MON-YYYY hh24SpTh'') FROM dual;

1. First January Two Thousand Thirteen
2. One January Two Thousand Thirteen
3. ORA error
4. 01-JAN-2013 zeroeth

Answer: D. Spelling out the timestamp component can be done using ”SpTh” format modifier.

96. Which of these functions do the work similar to if-then-else logic in SQL statements?

1. TO_CHAR
2. TO_NUMBER
3. Both A and B
4. CASE

Answer: D. The CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression.

97. Examine the structure of the EMPLOYEES table as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

What will be the outcome of the following SQL query?

SELECT DECODE (salary,10000) FROM employees; 

1. 10000
2. NULL
3. 0
4. ORA error

Answer: B. The DECODE function implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. The DECODE function takes at least three mandatory parameters, but can take many more. If the default value in the DECODE function is omitted, a NULL is returned.

98. You need to display the time of the Oracle DB session up to 3 decimal places of the fractional seconds. Which of the following queries will give the required output?

1. SELECT TO_CHAR(sysdate, ''DD-MON-YY HH24:MI:SS.FF'') FROM dual;

2. SELECT TO_CHAR(sysdate, ''DD-MON-YY HH24:MI:SS'') FROM dual;

3. SELECT TO_CHAR(sysdate, ''DD-MON-YY HH24:MI:SS.FF3'') FROM dual;

4. SELECT TO_CHAR(sysdate, ''DD-MON-YY'') FROM dual;

Answer: C. The FF [1..9] extension to the HH:MI:SS format yields fractional seconds up to 1..9 digits in the fractional seconds.

99. Which of the following punctuation marks can be used with Dates and Times in Oracle DB?

1. #
2. @
3. ,
4. :

Answer: C, D.

100. Examine the structure of the EMPLOYEES table as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

You need to find the day of the year when the employee Jaimie Patrick was hired in the company ”ABC”. Which of the following queries will give the required output?

1. SELECT TO_CHAR(hire_date, ''DDD'') FROM employees WHERE last_name = ''Patrick'' AND first_name = ''John 

2. SELECT TO_CHAR(hire_date, ''YYYY'') FROM employees WHERE last_name = ''Patrick'' AND first_name = ''John 

3. SELECT TO_CHAR(hire_date, ''DD-MON-YYYY'') FROM employees WHERE last_name = ''Patrick'' AND first_name = ''John

4. SELECT TO_CHAR(hire_date, ''DD-MON-RR'') FROM employees WHERE last_name = ''Patrick'' AND first_name = ''John 

Answer: A. The format model ”DDD” returns the day of the year on which the given date falls.

101. A report is required to be generated which gives the timings for all the batch runs that started on midnight 1st June, 2013. These timings should be in the precision of seconds after midnight. Which of the following clauses will fulfill the requirement?

1. TO_CHAR(sysdate,”HH24:MI:SS”)
2. TO_CHAR(sysdate,”HH24:MI:SS.FF”)
3. TO_CHAR(sysdate,”HH24:MI:SSSS”)
4. TO_CHAR(sysdate,”HH24:MI:SS.FF3”)

Answer: C. the ”SSSS” format model gives the seconds after midnight.

1. What will be the outcome of the following query?

SELECT ROUND(144.23,-1) FROM dual;

1. 140
2. 144
3. 150
4. 100

Answer: A. The ROUND function will round off the value 144.23 according to the specified precision -1 and returns 140.

Examine the structure of the EMPLOYEES table as given and answer the questions 2 and 3 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

2. You are currently located in New Jersey and have connected to a remote database in San Diego. You issue the following command.

SELECT ROUND (sysdate-hire_date,0) FROM employees WHERE (sysdate-hire_date)/180 = 2;

What is the outcome of this query?

1. An error because the ROUND function cannot be used with Date arguments.
2. An error because the WHERE condition expression is invalid.
3. Number of days since the employee was hired based on the current San Diego date and time.
4. Number of days since the employee was hired based on the current New Jersey date and time.

Answer: C. The SYSDATE function will take the current time of the database which it is connecting to remotely. You must perform basic arithmetic operation to adjust the time zone.

3. You need to display the names of the employees who have the letter ”s” in their first name and the letter ”t” at the second position in their last name. Which query would give the required output?

1. SELECT first_name, last_name FROM employees WHERE INSTR(first_name,''s'')  0 AND SUBSTR(last_name,2,1) = ''t

2. SELECT first_name, last_name FROM employees WHERE INSTR(first_name,''s'')  '''' AND SUBSTR(last_name,2,1) = ''t

3. SELECT first_name, last_name FROM employees WHERE INSTR(first_name,''e'') IS NOT NULL AND SUBSTR(last_name,2,1) = ''t

4. SELECT first_name, last_name FROM employees WHERE INSTR(first_name,''e'')  0 AND SUBSTR(last_name,LENGTH(first_name),1) =
   ''t

Answer: A. The INSTR function returns the position of a given character in the required string. The SUBSTR function returns set of characters from the string from a given starting and end position.

4. Which of the following statements is true regarding the COUNT function?

1. COUNT (*) counts duplicate values and NULL values in columns of any data type.
2. COUNT function cannot work with DATE datatypes.
3. COUNT (DISTINCT job_id) returns the number of rows excluding rows containing duplicates and NULL values in the job_id column.
4. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

Answer: A. The COUNT(*) function 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.

5. Which of the following commands is used to count the number of rows and non-NULL values in Oracle database?

1. NOT NULL
2. INSTR
3. SUBSTR
4. COUNT

Answer: D. The COUNT (ALL column_name) is used to count number of rows excluding NULLs. Similarly, COUNT(*) is used to count the column values including NULLs.

6. What will be the outcome of the query given below?

SELECT 100+NULL+999 FROM dual;

1. 100
2. 999
3. NULL
4. 1099

Answer: C. Any arithmetic operation with NULL results in a NULL.

7. Which of the following statements are true regarding the single row functions?

1. They accept only a single argument.
2. They can be nested only to two levels.
3. Arguments can only be column values or constants.
4. They can return a data type value different from the one that is referenced.

Answer: D. Single row functions can take more than one argument and the return type can be different from the data type of the inputs.

8. Which of the below queries will format a value 1680 as $16,80.00?

1. SELECT TO_CHAR(1680.00,''$99G99D99'') FROM dual;

2. SELECT TO_CHAR(1680.00,''$9,999V99'') FROM dual;

3. SELECT TO_CHAR(1680.00,''$9,999D99'') FROM dual;

4. SELECT TO_CHAR(1680.00,''$99G999D99'') FROM dual;

Answer: A, D. The format model $99G999D99 formats given number into numeric, group separator, and decimals. Other format elements can be leading zeroes, decimal position, comma position, local currency, scientific notation, and sign.

9. Determine the output of the below query.

SELECT RPAD(ROUND(''78945.45''),10,''*'') FROM dual;

1. 78945*****
2. **78945.45
3. The function RPAD cannot be nested with other functions
4. 78945.45****

Answer: A. The LPAD(string, num, char) and RPAD(string, num, char) functions add a character to the left or right of a given string until it reaches the specified length (num) after padding. The ROUND function rounds the value 78945.45 to 78945 and then pads it with ”*” until length of 10 is reached.

10. Which of the following commands allows you to substitute a value whenever a NULL or non-NULL value is encountered in an SQL query?

1. NVL
2. NVLIF
3. NVL2
4. LNNVL

Answer: C. The NVL2 function takes minimum three arguments. The NVL2 function checks the first expression. If it is not null, the NVL2 function returns the second argument. If the first argument is null, the third argument is returned.

11. Which of the following type of single-row functions cannot be incorporated in Oracle DB?

1. Character
2. Numeric
3. Conversion
4. None of the above

Answer: D. The types of single-row functions like character, numeric, date, conversion and miscellaneous as well as programmer-written can be incorporated in Oracle DB.

12. Out of the below clauses, where can the single-row functions be used?

1. SELECT
2. WHERE
3. ORDER BY
4. All of the above

Answer: D. Single row function can be used in SELECT statement, WHERE clause and ORDER BY clause.

13. What is true regarding the NVL function in Oracle DB?

1. The syntax of NVL is NVL (exp1, exp2) where exp1 and exp2 are expressions.
2. NVL (exp1, exp2) will return the value of exp2 if the expression exp1 is NULL.
3. NVL (exp1, exp2) will return the value of the expression exp2 if exp1 is NOT NULL.
4. NVL (exp1, exp2) will return exp1 if the expression exp2 is NULL.

Answer: B. NVL function replaces a null value with an alternate value. Columns of data type date, character, and number can use NVL to provide alternate values. Data types of the column and its alternative must match.

14. Examine the structure of the EMPLOYEES table as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

What will be the outcome of the following query?

SELECT last_name, NVL(job_id, ''Unknown'')
FROM employees
WHERE last_name LIKE ''A%''
ORDER BY last_name;

1. It will throw an ORA error on execution.
2. It will list the job IDs for all employees from EMPLOYEES table.
3. It will list the job IDs of all employees and substitute NULL job IDs with a literal ”Unknown”.
4. It will display the last names for all the employees and their job IDs including the NULL values in the job ID.

Answer: C. The NVL function replaces a null value with an alternate value. Columns of data type date, character, and number can use NVL to provide alternate values. Data types of the column and its alternative must match.

15. What will the outcome of the following query?

SELECT NVL (NULL,''1'') FROM dual;

1. NULL
2. 1
3. 0
4. Gives an error because NULL cannot be explicitly specified to NVL function

Answer: B. The NVL will treat NULL as a value and returns the alternate argument i.e. 1 as the result.

16. What will be the outcome of the following query? (Consider the structure of the EMPLOYEES table as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT employee_id , NVL(salary, 0) FROM employees WHERE first_name like ''P%'' ORDER BY first_name;

1. It will display 0 in the salary column for all the employees whose first name starts with a ”P”
2. It will display the salaries for the employees whose name start with a ”P” and 0 if the salaries are NULL.
3. It will throw an ORA error as the ORDER BY clause should also contain the salary column.
4. The NVL function should be correctly used as NVL (0, salary)

Answer: B. NVL function replaces a null value with an alternate value. Columns of data type date, character, and number can use NVL to provide alternate values. Data types of the column and its alternative must match.

17. Which of the following statements is true regarding the NVL statement?

SELECT NVL (arg1, arg2) FROM dual;

1. The two expressions arg1 and arg2 should only be in VARCHAR2 or NUMBER data type format.
2. The arguments arg1 and arg2 should have the same data type
3. If arg1 is VARCHAR2, then Oracle DB converts arg2 to the datatype of arg1 before comparing them and returns VARCHAR2 in the character set of arg1.
4. An NVL function cannot be used with arguments of DATE datatype.

Answer: C. If arg1 is of VARCHAR2 data type, Oracle does implicit type conversion for arg2 id arg2 is of NUMBER datatype. In all other cases, both the arguments must be of same datatype.

18. What will be the outcome of the following query? (Consider the structure of the EMPLOYEES table as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT NVL2(job_id,''Regular Employee'',''New Joinee'') FROM employees;

1. It will return the value ”Regular Employee” for all the employees who have NULL job IDs
2. It will return the value ”New Joinee” for all the employees who have NULL job IDs
3. It will return ”Regular Employee” if the job ID is NULL
4. It will throw an ORA error on execution.

Answer: B. The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.

19. Which of the following is true for the statement given as under.

NVL2 (arg1, arg2, arg3)

1. Arg2 and Arg3 can have any data type
2. Arg1 cannot have the LONG data type
3. Oracle will convert the data type of expr2 according to Arg1
4. If Arg2 is a NUMBER, then Oracle determines the numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

Answer: D. The data types of the arg2 and arg3 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 arg3 to the type of the arg2 parameter. The data type returned by the NVL2 function is the same as that of the arg2 parameter.

20. Examine the structure of the EMPLOYEES table as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

What will be the outcome of the query mentioned below?

SeLECT first_name, salary, NVL2(commission_pct,  salary + (salary * commission_pct), salary) "Income"
FROM employees
WHERE first_name like ''P%''
ORDER BY first_name;

1. Salary will be returned if the Commission for the employee is NOT NULL.
2. Commission_pct will be returned if the Commission for the employee is NOT NULL.
3. Employees with the first name starting with ”P” and salary+(salary*commission_pct) will be returned if the employee earns a commission.
4. The query throws an error because a mathematical expression is written inside NVL2.

Answer: C. The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.

21. What is true about the NULLIF function in Oracle DB?

1. NULLIF(expr1,expr2) will return expr2 if the two expressions are NOT NULL.
2. NULLIF(expr1,expr2) will return 0 if the two expressions are NULL.
3. NULLIF(expr1,expr2) will return NULL if the two expressions are equal.
4. Expr1 can be NULL in NULLIF(expr1, expr2)

Answer: C. The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(arg1,arg2), where the arguments arg1 and arg2 are compared. If they are identical, then NULL is returned. If they differ, the arg1 is returned.

22. Pick the correct answer given after the statement shown as under.

NULLIF (arg1,arg2) 

1. Arg1 and Arg2 can be of different data types.
2. Arg1 and Arg2 have to be equal in order to be used in the NULLIF function.
3. There is no internal conversion of data types if NULLIF used as in the case of NVL and NVL2.
4. This is equivalent to CASE WHEN Arg1 = Arg22 THEN NULL ELSE Arg1 END.

Answer: D.

23. Examine the structure of the EMPLOYEES table as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

You need to create a report from the HR schema displaying employees who have changed jobs since they were hired. You execute the query given below.

SELECT e.last_name, NULLIF(e.job_id, j.job_id,"Old Job ID")
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name;

What will be the outcome of the query given above?

1. It will display the old job ID when the new job ID is NULL.
2. It will execute successfully and produce the required output.
3. It will display the new job ID if the new job ID is equal to the old job ID
4. It will throw an ORA error on execution.

Answer: B.

24. Which of the following is not a property of functions?

1. Perform calculations on data
2. Convert column data types
3. Modify individual data items
4. None of the above

Answer: D. Functions can perform calculations, perform case conversions and type conversions.

25. What is the most appropriate about single row functions?

1. They return no value
2. They return one result per row and operate on all the rows of a table.
3. They return one result per row with input arguments
4. They return one result per set of rows and operate on multiple rows.

Answer: B. Single row functions always return one result per row and they operate on single rows only; hence the name ‘Single Row” is given to them.

26. What among the following is a type of Oracle SQL functions?

1. Multiple-row functions
2. Single column functions
3. Single value functions
4. Multiple columns functions

Answer: A. There are basically two types of functions – Single row and Multiple row functions.

27. What among the following is a type of single-row function?

1. VARCHAR2
2. Character
3. LONG
4. NULLIF

Answer: B. Character, Date, Conversion, General, Number are the types of Single row functions.

28. What is the most appropriate about Multiple Row Functions?

1. They return multiple values per each row. 
2. They return one result per group of rows and can manipulate groups of rows. 
3. They return one result per row and can manipulate groups of rows. 
4. They return multiple values per a group of row.

Answer: B. Multiple Row functions always work on a group of rows and return one value per group of rows.

29. Which of the following are also called Group functions?

1. Single row functions
2. Multi group functions
3. Multiple row functions
4. Single group functions.

Answer: C. Group functions are same as Multi row functions and aggregate functions.

30. Which of the following is true about Single Row Functions?

1. They can be nested
2. They accept arguments and return more than one value.
3. They cannot modify a data type
4. They cannot accept expressions as arguments.

Answer: A. Single row functions can be nested up to multiple levels.

31. What is the number of arguments Single Row functions accept?

1. 0
2. Only 1
3. Only 2
4. 1 or more than 1

Answer: D. Single row functions can accept one or more arguments depending upon the objective they serve.

32. Which of the following can be an argument for a Single Row Function?

1. Data types
2. SELECT statements
3. Expression
4. Table name

Answer: C. A user-supplied constant, variable value, column value and expression are the types of arguments of a single row function.

33. What is true about Character functions?

1. They return only character values
2. They accept NUMBER values
3. They accept character arguments and can return both character and number values
4. They accept values of all data type

Answer: C. The character function INSTR accepts a string value but returns numeric position of a character in the string.

34. What is true about Number functions?

1. They return both Character as well as Number values
2. They can”t accept expressions as input
3. Number functions can”t be nested.
4. They accept Number arguments and return Number values only.

Answer: D.

35. Which of the following is an exception to the return value of a DATE type single-row function?

1. TO_DATE
2. SYSDATE
3. MONTHS_BETWEEN
4. TO_NUMBER

Answer: C. All the DATE data type functions return DATE as return values except MONTHS_BETWEEN which returns a number.

36. Which of the following is not a Conversion type Single Row function?

1. TO_CHAR
2. TO_DATE
3. NVL
4. TO_NUMBER

Answer: C. Conversion functions convert a value from one data type to another. The NVL function replaces a null value with an alternate value.

37. Which of the following is a Case-Conversion Character function?

1. CONCAT
2. SUBSTR
3. INITCAP
4. REPLACE

Answer: C. The CONCAT, SUBSTR and REPLACE are Character-manipulation Character functions while INITCAP, LOWER and UPPER are case conversion character functions.

38. What will be the outcome of the following query?

SELECT lower(''HI WORLD !!!'')  FROM dual;

1. Hi World !!!
2. Hi WORLD !!!
3. hi world !!!
4. HI WORLD !!!

Answer: C. The LOWER function converts a string to lower case characters.

39. What will be the outcome of the following query?

SELECT lower(upper(initcap(''Hello World'') )) FROM dual;

1. Hello World
2. HELLO world
3. hello World
4. hello world

Answer: C. Case conversion characters can be nested in the SELECT queries.

Examine the structure of the EMPLOYEES table as given and answer the questions 40 to 42 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

40. Which of the following queries will give the same result as given in the query given below?

SELECT CONCAT(first_name, last_name) FROM employees;

1. SELECT first_name||last_name FROM employees;
2. SELECT first_name||” ” || last_name FROM employees;
3. SELECT last_name||”, ”||first_name FROM employees;
4. SELECT first_name||”,”||last_name FROM employees;

Answer: A. The CONCAT function joins two strings without any space in between.

41. What will be the outcome of the following query?

SELECT ''The job id for ''||upper(last_name) ||'' is a ''||lower(job_id) FROM employees;

1. The job id for ABEL is a sa_rep
2. The job id forABEL is a sa_rep
3. The job id for abel is SA_REP
4. The job id for abel is sa_rep

Answer: A.

42. Assuming the last names of the employees are in a proper case in the table employees, what will be the outcome of the following query?

SELECT employee_id, last_name, department_id  FROM employees WHERE last_name = ''smith

1. It will display the details of the employee with the last name as Smith
2. It will give no result.
3. It will give the details for the employee having the last name as ”Smith” in all Lower case.
4. It will give the details for the employee having the last name as ”Smith” in all INITCAP case.

Answer: B. Provided the last names in the employees table are in a proper case, the condition WHERE last_name = ”smith” will not be satistified and hence no results will be displayed.

43. What is true about the CONCAT function in Oracle DB?

1. It can have only characters as input.
2. It can have only 2 input parameters.
3. It can have 2 or more input parameters
4. It joins values by putting a white space in between the concatenated strings by default.

Answer: B. The CONCAT function accepts only two arguments of NUMBER or VARCHAR2 datatypes.

44. What is true about the SUBSTR function in Oracle DB?

1. It extracts a string of determined length
2. It shows the length of a string as a numeric value
3. It finds the numeric position of a named character
4. It trims characters from one (or both) sides from a character string

Answer: A. The SUBSTR(string, x, y) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position (x). When position is positive, then the function counts from the beginning of string to find the first character. When position is negative, then the function counts backward from the end of string.

45. What will be the outcome of the following query?

SELECT length(''hi'') FROM dual;

1. 2
2. 3
3. 1
4. hi

Answer: A. the LENGTH function simply gives the length of the string.

46. What is the difference between LENGTH and INSTR functions in Oracle DB?

1. They give the same results when operated on a string.
2. LENGTH gives the position of a particular character in a string
3. INSTR gives the position of a particular character in a string while LENGTH gives the length of the string.
4. LENGTH and INSTR can be used interchangeably.

Answer: C.

47. Examine the structure of the EMPLOYEES table as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

What will be the outcome of the following query?

SELECT upper(&jobid) FROM employees;

1. It results in an error as substitution variables cannot be used with single row functions
2. It prompts the user to input the jobid on each execution and then displays the job id in UPPER case
3. It gives the jobid as it is present in the table EMPLOYEES without making any change
4. It will not ask the user to input the job id and will convert all the job IDs in the table in UPPER case

Answer: B. Substitution variables can be used with the UPPER and LOWER functions.

48. What is false about the table DUAL in Oracle database?

1. It is owned by the user SYS and can be access by all the users.
2. It contains only one column and one row.
3. The value in the DUMMY column of the DUAL table is ”X”
4. The DUAL table is useful when you want to return a value only once

Answer: C. The DUAL table has one column named DUMMY and one row which has a value ”X”.

49. What will be the result of the following query?

SELECT sysdate+4/12 FROM dual;

1. The query produces error.
2. No of hours to a date with date as the result.
3. Sysdate arithmetic is ignored.
4. Returns the system date as result.

Answer: B. Arithmetic operations can be performed on dates in the Oracle DB.

50. What will be the outcome of the following query?

SELECT lower (100+100) FROM dual;

1. 100
2. 100+100
3. ORA error
4. 200

Answer: D. Arithmetic expressions can be specified within case conversion functions.

51. What will be the outcome of the following query if the SYSDATE = 20-MAY-13?

SELECT upper (lower (sysdate)) FROM dual;

1. 20-may-2013
2. ORA error as LOWER and UPPER cannot accept date values.
3. 20-MAY-13
4. 20-May-13

Answer: C. The functions UPPER and LOWER can accept date type inputs and will yield the same result as they do on Strings.

52. What is the result of the following query?

SELECT INITCAP (24/6) FROM dual;

1. 4
2. 24
3. 24/6
4. No result

Answer: A. Arithmetic expressions can be specified within case conversion functions.

53. Examine the structure of the EMPLOYEES table as given here.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

You need to display the last name of all employees which starts with the letter ”A”. Which of the following queries will yield the required result?

1. SELECT INITCAP (last_name||'' works as a ''||job_id "Job Description" FROM employees WHERE initcap (last_name) like ''A%

2. SELECT INITCAP (last_name) ||INITCAP('' works as a: '')|| INITCAP(job_id) "Job Description" FROM employees WHERE initcap (last_name) like ''A
   %

3. SELECT INITCAP (last_name||'' works as a ''||INITCAP(job_id)) "Job Description" FROM employees WHERE initcap (last_name) = ''A

4. SELECT UPPER (LOWER (last_name||'' works as a ''||job_id)) "Job Description" FROM employees WHERE lower (last_name) = ''A

Answer: A, B.

54. Assuming the SYSDATE is 20-FEB-13, What will be the outcome of the following query?

SELECT CONCAT (''Today is :'', SYSDATE) FROM dual;

1. Today is : 20-feb-13
2. The query throws error of incompatible type arguments.
3. Today is : 20-Feb-13
4. Today is : 20-FEB-13

Answer: D. The CONCAT function accepts arguments of all types.

55. What will be the result pattern of the following query?

SELECT CONCAT(first_name, CONCAT (last_name, job_id)) FROM dual;

1. First_namelast_namejob_id
2. First_name, last_name, job_id
3. Error as CONCAT cannot be nested
4. First_namelast_name, job_id

Answer: A. The CONCAT function can be nested with self or other character function.

56. Examine the structure of the EMPLOYEES table as given here.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

You need to generate a report which shows the first name, last name and the salary for all the employees in the department 100. The report should show the results in the form ”Andy Smith earns 50000”. Which of the following queries will give the required output?

1. SELECT concat (first_name,concat ('' '', concat(last_name, concat('' earns '', SALARY)))) Concat_String FROM employees WHERE department_id =
   100;

2. SELECT concat (first_name, last_name||'' ''|| salary) FROM employees WHERE department_id = 100;

3. SELECT concat (first_name, concat(last_name, '' ''))||earns||salary FROM employees WHERE department_id = 100;

4. SELECT concat (first_name, concat(last_name, ''earns salary'') FROM employees WHERE department_id = 100;

Answer: A. The CONCAT function can be nested with self or other character function.

57. What will the following query show as a result?

SELECT LENGTH(''It is a lovely day today!'') FROM dual;

1. 25
2. 19
3. 20
4. 0

Answer: A. The LENGTH functions counts blank spaces, tabs and special characters too.

58. You need to display the country name from the COUNTRIES table. The length of the country name should be greater than 5 characters. Which of the following queries will give the required output?

1. SELECT country_name FROM countries WHERE LENGTH (country_name)= 5;

2. SELECT country_name FROM countries WHERE length (country_name)> 5;

3. SELECT SUBSTR(country_name, 1,5) FROM countries WHERE length (country_name)

4. SELECT country_name FROM countries WHERE length (country_name)  5;

Answer: B. The LENGTH function can be used in WHERE clause.

59. How does the function LPAD works on strings?

1. It aligns the string to the left hand side of a column
2. It returns a string padded with a specified number of characters to the right of the source string
3. It aligns character strings to the left and number strings to right of a column
4. It returns a string padded with a specified number of characters to the left of the source string

Answer: D. The LPAD(string, length after padding, padding string) and RPAD(string, length after padding, padding string) functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

60. Which of the following options is true regarding LPAD and RPAD functions?

1. The character strings used for padding include only characters.
2. The character strings used for padding include only literals
3. The character strings used for padding cannot include expressions.
4. The character strings used for padding include literals, characters and expressions.

Answer: D.

61. What is the maximum number of input arguments in LPAD and RPAD functions?

1. 1
2. 2
3. 3
4. 0

Answer: C. LPAD and RPAD take maximum of 3 arguments. If there are 2 arguments given, the padding happens by spaces.

62. What will be the outcome of the following query?

SELECT lpad (1000 +300.66, 14, ''*'') FROM dual;

1. *******1300.66
2. 1300*******
3. 1300.66
4. ****1300.66

Answer: A. To make the total length of 14 characters, the return value 1300.66 is padded with 7 asterisks (*) on the left.

63. What is true regarding the TRIM function?

1. It is similar to SUBSTR function in Oracle
2. It removes characters from the beginning or end of character literals, columns or expression
3. TRIM function cannot be applied on expressions and NUMBERS
4. TRIM function can remove characters only from both the sides of a string.

Answer: B. The TRIM function literally trims off leading or trailing (or both) character strings from a given source string. TRIM function when followed by TRAILING or LEADING keywords, can remove characters from one or both sides of a string.

64. You need to remove the occurrences of the character ”.” and the double quotes ””” from the following titles of a book present in the table MAGAZINE.

"HUNTING THOREAU IN NEW HAMPSHIRE" THE ETHNIC NEIGHBORHOOD."

Which of the following queries will give the required result?

1. SELECT LTRIM(Title,''"'') FROM MAGAZINE;

2. SELECT LTRIM(RTRIM(Title,''."''),''"'') FROM MAGAZINE;

3. SELECT LTRIM (Title,''"THE'') FROM MAGAZINE;

4. SELECT LTRIM(RTRIM(Title,''."THE''),''"'') FROM MAGAZINE;

Answer: B. The LTRIM and RTRIM functions can be used in combination with each other.

65. What will be returned as a result of the following query?

SELECT INSTR(''James'',''x'') FROM dual;

1. 1
2. 2
3. 0
4. 3

Answer: C. INSTR function returns a 0 when the search string is absent in the given string.

66. What will be the outcome of the following query?

SELECT INSTR(''1$3$5$7$9$'',''$'',3,4)FROM dual;

1. 2
2. 10
3. 7
4. 4

Answer: B. INSTR function search for the 4th occurrence of ”$” starting from the 3rd position.

67. What will be the result of the following query?

SELECT INSTR(''1#3#5#7#9#'', -3,2) FROM dual;

1. #5
2. #3
3. #7
4. #9

Answer: D. SUBSTR function will search 3 places starting from the end of string and will give 2 characters in the forward direction giving #9.

Examine the structure of the EMPLOYEES table as given below and answer the questions 68 and 69 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

68. You need to extract a consistent 15 character string based on the SALARY column in the EMPLOYEES table. If the SALARY value is less than 15 characters long, zeros must be added to the left of the value to yield a 15 character string. Which query will fulfill this requirement?

1. SELECT rpad(salary, 15,0) FROM employees;

2. SELECT lpad(salary,15,0) FROM employees;

3. SELECT ltrim(salary,15,0) FROM employees;

4. SELECT trim(salary,15,0) FROM employees;

Answer: B. The LPAD and RPAD functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

69. You need to display the last 2 characters from the FIRST_NAME column in the EMPLOYEES table without using the LENGTH function. Which of the following queries can fulfill this requirement?

1. SELECT SUBSTR(first_name, 2) FROM employees;

2. SELECT SUBSTR(first_name, -2) FROM employees;

3. SELECT RTRIM(first_name, 2) FROM employees;

4. SELECT TRIM(first_name, 2) FROM employees;

Answer: B. The SUBSTR(string, x, y) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position (x). When position is positive, then the function counts from the beginning of string to find the first character. When position is negative, then the function counts backward from the end of string.

70. Assuming the SYSDATE is 13-JUN-13, what will be the outcome of the following query?

SELECT SUBSTR(sysdate,10,7) FROM dual;

1. 3
2. N-13
3. 0
4. NULL

Answer: D. The query will give a NULL as the position 10 to start with in the SYSDATE doesn”t exist.

71. Which of the following is used to replace a specific character in a given string in Oracle DB?

1. LTRIM
2. TRIM
3. TRUNC
4. REPLACE

Answer: D.

72. What will be the outcome of the following query?

SELECT replace(9999.00-1,''8'',88) FROM dual;

1. 999
2. 9998
3. 99988
4. 9999.88

Answer: C. The REPLACE function searches for ”8” in 9998 and replaces it with ”88”.

73. Examine the structure of the EMPLOYEES table as given here.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

You need to retrieve the first name, last name (separated by a space) and the formal names of employees where the combined length of the first name and last name exceeds 15 characters. A formal name is formed by the first letter of the First Name and the first 14 characters of the last name. Which of the following queries will fulfill this requirement?

1. SELECT first_name, last_name ,SUBSTR(first_name, 1,1)||'' ''||SUBSTR(last_name, 1,14) formal_name FROM employees;

2. SELECT first_name, last_name ,SUBSTR(first_name, 1,14)||'' ''||SUBSTR(last_name, 1,1) formal_name FROM employees WHERE length
   (first_name) + length(last_name) 

3. SELECT first_name, last_name ,SUBSTR(first_name, 1,1)||'' ''||SUBSTR(last_name, 1,14) formal_name FROM employees WHERE length
   (first_name) + length(last_name) =15;

4. SELECT first_name, last_name ,SUBSTR(first_name, 1,1)||'' ''||SUBSTR(last_name, 1,14) formal_name FROM employees WHERE length
   (first_name) + length(last_name) > 15;

Answer: D.

74. What will be the outcome of the following query?

SELECT round(148.50) FROM dual;

1. 148.50
2. 140
3. 150
4. 149

Answer: D. if the decimal precision is absent, the default degree of rounding is 0 and the source is rounded to the nearest whole number.

75. Assuming the sysdate is 10-JUN-13, What will be the outcome of the following query?

SELECT trunc (sysdate,''mon'') FROM dual;

1. 10-JUN-13
2. 1-JUN-13
3. ORA error as the TRUNC function can”t have an input parameter when used with dates.
4. 31-JUN-13

Answer: B. The date is truncated to the first day of the month. Similarly, it can be done for year also.

76. What will be the result of the following query?

SELECT trunc(1902.92,-3) FROM dual;

1. 2000
2. 1000
3. 1901
4. 1901.00

Answer: B.

77. What is the syntax of the MOD function in Oracle DB?

1. Mod(divisor,dividend)
2. MOD(divisor,1)
3. MOD(dividend,divisor)
4. None of the above

Answer: C. The MOD function is used to get the remainder of a division operation.

78. What will be outcome of the following query?

SELECT mod(100.23,-3) FROM dual;

1. ORA error
2. 1.23
3. 100
4. 0

Answer: B. The MOD function gives the same answer for a positive divisor as well as a negative divisor

79. Which of the following functions are used to differentiate between even or odd numbers in Oracle DB?

1. ROUND
2. TRUNC
3. MOD
4. REPLACE

Answer: C. The MOD function can be used to check whether a given number is even or odd. If MOD (num,2) returns zero, the number ”num” is an even. If MOD (num,2) returns 1, the number ”num” is odd.

80. Examine the structure of the EMPLOYEES table as given below.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

You need to allocate the first 12 employees to one of the four teams in a round-robin manner. The employee IDs start with a 100. Which of the following queries will fulfill the requirement?

1. SELECT * FROM employees WHERE employee_id between 100 and 111 ORDER BY employee_id;

2. SELECT first_name, last_name, employee_id, mod(employee_id, 4) Team# FROM employees WHERE employee_id between 100 and 111
   ORDER BY employee_id;

3. SELECT first_name, last_name,mod(employee_id, 2) Team# FROM employees WHERE employee_ID  100;

4. SELECT first_name, last_name, mod(employee_id, 4) Team# FROM employees WHERE employee_ID = 100;

Answer: B.

81. What will be the outcome of the following query?

SELECT SUBSTR(''Life is Calling'',1) FROM dual;

1. ORA error as there should be minimum 3 arguments to the SUBSTR function.
2. Life is Calling
3. NULL
4. Life

Answer: B. Calling the SUBSTR function with just the first two parameters results in the function extracting a string from a start position to the end of the given source string.

82. What is the default data format for the sysdate in SQL Developer?

1. DD-MON-YY
2. DD-MON-RR
3. DD/MON/RR
4. DD/MON/YYYY

Answer: C. For SQL*PLUS the default date format is DD-MON-RR.

83. Assuming the SYSDATE to be 10-JUN-2013 12:05pm, what value is returned after executing the below query?

SELECT add_months(sysdate,-1) FROM dual;

1. 09-MAY-2013 12:05pm
2. 10-MAY-2013 12:05pm
3. 10-JUL-2013 12:05pm
4. 09-JUL-2013 12:05pm

Answer: B. The ADD_MONTHS(date, x) function adds ”x” number of calendar months to the given date. The value of ”x” must be an integer and can be negative.

84. What value will be returned after executing the following statement? Note that 01-JAN-2013 occurs on a Tuesday.

SELECT next_day(''01-JAN-2013'',''friday'') FROM dual;

1. 02-JAN-2013
2. Friday
3. 04-JAN-2013
4. None of the above

Answer: C. The NEXT_DAY(date,”day”) finds the date of the next specified day of the week (”day”) following date. The value of char may be a number representing a day or a character string.

85. What is the maximum number of parameters the ROUND function can take?

1. 0
2. 1
3. 2
4. 3

Answer: C. If there is only one parameter present, then the rounding happens to the nearest whole number

86. Assuming the present date is 02-JUN-2007, what will be the century returned for the date 24-JUL-2004 in the DD-MON-RR format?

1. 19
2. 21
3. 20
4. NULL

Answer: C. If the two digits of the current year and the specified year lie between 0 and 49, the current century is returned.

87. Assuming the present date is 02-JUN-2007, what will be the century returned for the date 24-JUL-94 in the DD-MON-RR format?

1. 19
2. 21
3. 20
4. NULL

Answer: A. If the two digits of the current year lie between 0 and 49 and the specified year falls between 50 and 99, the previous century is returned.

88. Assuming the present date is 02-JUN-1975, what will be the century returned for the date 24-JUL-94 in the DD-MON-RR format?

1. 19
2. 21
3. 20
4. NULL

Answer: A. if the two digits of the current and specified years lie between 50 and 99, the current century is returned by default.

89. Assuming the present date is 02-JUN-1975, what will be the century returned for the date 24-JUL-07 in the DD-MON-RR format?

1. 19
2. 21
3. 20
4. NULL

Answer: C. if the two digits of the current year lie between 50 and 99 and the specified year falls between 0 and 49, the next century is returned.

90. How many parameters does the SYSDATE function take?

1. 1
2. 2
3. 4
4. 0

Answer: D. The SYSDATE is a pseudo column in Oracle.

91. What is true about the SYSDATE function in Oracle DB?

1. It returns only the system date
2. It takes 2 parameters at least.
3. The default format is DD-MON-YY
4. The default format of SYSDATE is DD-MON-RR and it returns the date and time of the system according to the database server.

Answer: D.

92. What will be the datatype of the result of the following operation?

“Date3 = Date1-Date2”

1. Date
2. Num1
3. 0
4. NULL

Answer: B. Subtraction of two dates results in number of days.

93. What will be the datatype of the result of the following operation?

“Date2 = Date1-Num1”

1. Date
2. Num1
3. 0
4. NULL

Answer: A. Subtraction of a number from a date value results in date.

94. What does a difference between two dates represent in Oracle DB?

1. The number of days between them
2. Difference in dates in not possible in Oracle DB
3. A date
4. NULL

Answer: A.

95. What will be the outcome of the following query?

SELECT months_between(''21-JUN-13'',''19-JUN-13'') FROM dual; 

1. ORA error
2. A positive number
3. A negative number
4. 0

Answer: C. If the first parameter is less than the second parameter, the MONTHS_BETWEEN returns a negative number.

96. What can be deduced if the result of MONTHS_BETWEEN (start_date,end_date) function is a fraction?

1. It represents the difference in number between the start date and end date.
2. The result cannot be a fractional number, it has to be a whole number.
3. NULL
4. It represents the days and the time remaining after the integer difference between years and months is calculated and is based on a 31-day month.

Answer: D.

97. You are connected to a remote database in Switzerland from India. You need to find the Indian local time from the DB. Which of the following will give the required result?

1. SELECT sysdate FROM dual;

2. SELECT round(sysdate) FROM dual;

3. SELECT trunc (sysdate) FROM dual;

4. SELECT current_date FROM dual;

Answer: D.

98. What will be the outcome of the following query?

SELECT months_between (to_date (''29-feb-2008''), to_date (''29-feb-2008 12:00:00'',''dd-mon-yyyy hh24:mi:ss''))*31 FROM dual; 

1. Approximately 0
2. 1
3. The query will throw an ORA error
4. 0.5 days

Answer: D. The 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.

99. What will be the outcome of the following query?

SELECT add_months (''31-dec-2008'',2.5) FROM dual; 

1. 31-feb-2009
2. 28-feb-2009
3. 31-mar-2009
4. 15-jan-2009

Answer: B. the fractional part of 2.5 will be ignored and 2 months will be added to 31-dec-2012 which is 31-feb-2013 but as it is not a valid date, the result is 28-feb-2009.

100. You need to identify the date in November when the staff will be paid. Bonuses are paid on the last Friday in November. Which of the following will fulfill the requirement?

1. SELECT next_day (''30-nov-2012'' , ''Friday'') FROM dual;

2. SELECT next_day (''30-nov-2012'' , ''Friday'') -7 FROM dual;

3. SELECT last_day (''01-nov-2012'' ) FROM dual;

4. SELECT next_day (''30-nov-2012'' , ''sat'') -1 FROM dual;

Answer: B. The NEXT_DAY(date,”day”) and LAST_DAY (date,”day”) functions find the date of the next or last specified day of the week (”day”) following date. The value of char may be a number representing a day or a character string.

Using DDL Statements to Create and Manage Tables

A schema is the collection of multiple database objects,which are known as schema objects.These objects have direct access by their owner schema.Below table lists the schema objects.

• Table – to store data

• View – to project data in a desired format from one or more tables

• Sequence – to generate numeric values

• Index – to improve performance of queries on the tables

• Synonym – alternative name of an object

One of the first steps in creating a database is to create the tables that will store an organization”s data.Database design involves identifying system user requirements for various organizational systems such as order entry, inventory management, and accounts receivable. Regardless of database size and complexity, each database is comprised of tables.

Creating the table

To create a table in the database,a DBA must have certain information in hand – the table name, column name, column data types, and column sizes. All this information can be modified later using DDL commands.

Table Naming Conventions –

• The name you choose for a table must follow these standard rules:

• The name must begin with a letter A-Z or a-z

• Can contain numbers and underscores

• Can be in UPPER of lower case

• Can be up to 30 characters in length

• Cannot use the same name of another existing object in your schema

• Must not be a SQL reserved word

Following the above guidelines, ”EMP85” can be a valid table name.But 85EMP is not.Similarly, UPDATE cannot be a chosen as a table name since it a SQL reserved keyword.

CREATE TABLE statement

The CREATE TABLE is a DDL statement which is used to create tables in the database.The table gets created as soon as the CREATE TABLE script is executed and is ready to hold the data onwards.The user must have the CREATE TABLE system privilege to create the table in its own schema.But to create a table in any user”s schema, user must have CREATE ANY TABLE schema.

Here is the syntax of a basic CREATE TABLE statement.There may be many additional clauses to explicitly provide the storage specifications or segment values.

CREATE TABLE [schema.]table
          ( { column datatype [DEFAULT expr] [column_constraint] ...
            | table_constraint}
         [, { column datatype [DEFAULT expr] [column_constraint] ...
            | table_constraint} ]...)
         [AS subquery]

In the above syntax, DEFAULT specifies default value which can be used during INSERT statement if the column is ignored. It cannot contain references to other table columns or pseudo columns (CURRVAL, NEXTVAL, LEVEL, and ROWNUM) except SYSDATE and USER, or date constants that are not fully specified.

Constraints are the rules defined optionally at the column level or table level (covered later in this chapter).These rules are checked during any data action (Insert, update) on the table and raise error to abort the action upon its violation.

For example, the CREATE TABLE statement below creates a table EMP_TEST. Note the column specifications, data type and precision.

CREATE TABLE SCOTT.EMP_TEST
(EMPID NUMBER,
ENAME VARCHAR2(100),
DEPARTMENT_ID NUMBER,
SALARY NUMBER,
JOB_ID VARCHAR2(3),
HIREDATE DATE,
COMM NUMBER);

A user can refer the tables from other user”s schema by prefixing the username or schema with the table name.For example, a user GUEST wishes to query the employee name and salary from the EMP_TEST table which is owned by SCOTT. He can issue the below query –

SELECT  ENAME, SALARY,
FROM 	GUEST.EMP_TEST;

A column can hold a default value during the time of table creation.It helps to restrict the NULL values getting into the column. Default value can be deduced from either a literal, expression or SQL function which must return a compatible data type to the column. In the below CREATE TABLE statement, note that the LOCATION_ID column has default value 100.

CREATE TABLE SCOTT.DEPARTMENT
(DEPARTMENT_ID NUMBER,
   DNAME VARCHAR2 (100),
   LOCATION_ID NUMBER DEFAULT 100);

CTAS – Create table using subquery

A table can be created from an existing table in the database using a subquery option.It copies the table structure as well as the data from the table. Data can also be copied based on conditions.The column data type definitions including the explicitly imposed NOT NULL constraints are copied into the new table.

The below CTAS script creates a new table EMP_BACKUP. Employee data of department 20 gets copied into the new table.

CREATE TABLE EMP_BACKUP
AS
SELECT * FROM EMP_TEST
WHERE department_id=20;

Data types

Data types are used to specify the basic behavior of a column in the table.On a broader basis,column behavior can either belong to number,character or a date family.There are multiple other subtypes which belong to these families.

Number data type

The NUMBER datatype encompasses both integer,fixed-point,and floating-point numeric values.Early versions of Oracle defined different datatypes for each of these different types of numbers,but now the NUMBER datatype serves all of these purposes.Choose the NUMBER datatype when a column must store numerical data that can be used in mathematical calculations.Occasionally,the NUMBER datatype is used to store identification numbers where those numbers are generated by the DBMS as sequential numbers.

NUMBER (p, s), where p is the precision up to 38 digits and s is the scale (number of digits to the right of the decimal point).The scale can range between -84 to 127.

NUMBER (p),is a fixed-point number with a scale of zero and a precision of p.

FLOAT [(p)],where p is the binary precision that can range from 1 to 126. If p is not specified the default value is binary 126.

Date data type

For each DATE data type, Century, Year, Month, Day, Hour, Minute, Second are stored in database. Every database system has a default date format that is defined by the initialization parameter NLS_DATE_FORMAT. This parameter is usually set to DD-MON-YY.If you do not specify a time, the default time is 12:00:00 a.m.

Character data type

Oracle supports three predefined character datatypes including CHAR, VARCHAR, VARCHAR2, and LONG.VARCHAR and VARCHAR2 are actually synonymous, and Oracle recommends using VARCHAR2 instead of VARCHAR.Use the CHAR datatype when the column will store character values that are fixed-length.For example, a Social Security number (SSN) in the United States is assigned to every citizen and is always 9 characters in size (even though an SSN is strictly composed of digits,the digits are treated as characters), and would be specified as CHAR(9). Use the VARCHAR2 datatype to store alphanumeric data that is variable-length.For example, a customer name or address will vary considerably in terms of the number of characters to be stored.The maximum size of a VARCHAR2 column is 4,000 characters.

LOB data type

Oracle provides several different LOB datatypes, including CLOB (character large object) and BLOB (binary large object).Columns of these datatypes can store unstructured data including text, image, video, and spatial data.The CLOB datatype can store up to eight terabytes of character data using the CHAR database character set.The BLOB datatype is used to store unstructured binary large objects such as those associated with image and video data where the data is simply a stream of “bit” values.A BLOB datatype can store up to eight terabytes of binary data.The NCLOB data type can store character large objects in multibyte national character set up to 8TB to 128TB.The BFILE data type value works as a file locator or pointer to file on the server”s file system. The maximum file size supported is 8TB to 128TB.

Constraints

Constraints are the set of rules defined in Oracle tables to ensure data integrity.These rules are enforced placed for each column or set of columns.Whenever the table participates in data action, these rules are validated and raise exception upon violation. The available constraint types are NOT NULL, Primary Key, Unique, Check, and Foreign Key.

The below syntax can be used to impose constraint at the column level.

Syntax:

column [data type] [CONSTRAINT constraint_name] constraint_type

All constraints except NOT NULL, can also be defined at the table level. Composite constraints can only be specified at the table level.

NOT NULL Constraint

A NOT NULL constraint means that a data row must have a value for the column specified as NOT NULL.If a column is specified as NOT NULL,the Oracle RDBMS will not allow rows to be stored to the employee table that violate this constraint.It can only be defined at column level, and not at the table level.

Syntax:

COLUMN [data type] [NOT NULL]

UNIQUE constraint

Sometimes it is necessary to enforce uniqueness for a column value that is not a primary key column.The UNIQUE constraint can be used to enforce this rule and Oracle will reject any rows that violate the unique constraint.Unique constraint ensures that the column values are distinct, without any duplicates.

Syntax:

Column Level:

COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]

Table Level: CONSTRAINT [constraint name] UNIQUE (column name)

Note: Oracle internally creates unique index to prevent duplication in the column values.Indexes would be discussed later in PL/SQL.

CREATE TABLE TEST
( ... ,
  NAME VARCHAR2(20)
          CONSTRAINT TEST_NAME_UK UNIQUE,
  ... );

In case of composite unique key,it must be defined at table level as below.

CREATE TABLE TEST
( ... ,
  NAME VARCHAR2(20),
  STD VARCHAR2(20) ,
      CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD)
 );

Primary Key

Each table must normally contain a column or set of columns that uniquely identifies rows of data that are stored in the table.This column or set of columns is referred to as the primary key.Most tables have a single column as the primary key.Primary key columns are restricted against NULLs and duplicate values.

Points to be noted –

• A table can have only one primary key.

• Multiple columns can be clubbed under a composite primary key.

• Oracle internally creates unique index to prevent duplication in the column values.Indexes would be discussed later in PL/SQL.

Syntax:

Column level:

COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]

Table level:

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

The following example shows how to use PRIMARY KEY constraint at column level.

CREATE TABLE TEST
( ID  NUMBER CONSTRAINT TEST_PK PRIMARY KEY,
  ...  );
  

The following example shows how to define composite primary key using PRIMARY KEY constraint at the table level.

CREATE TABLE TEST
 ( ...,
   CONSTRAINT TEST_PK PRIMARY KEY (ID)
 );
 

Foreign Key

When two tables share the parent child relationship based on specific column, the joining column in the child table is known as Foreign Key.This property of corresponding column in the parent table is known as Referential integrity.Foreign Key column values in the child table can either be null or must be the existing values of the parent table.Please note that only primary key columns of the referenced table are eligible to enforce referential integrity.

If a foreign key is defined on the column in child table then Oracle does not allow the parent row to be deleted,if it contains any child rows.However,if ON DELETE CASCADE option is given at the time of defining foreign key,Oracle deletes all child rows while parent row is being deleted.Similarly,ON DELETE SET NULL indicates that when a row in the parent table is deleted, the foreign key values are set to null.

Syntax:

Column Level:

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]

Table level:

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

The following example shows how to use FOREIGN KEY constraint at column level.

CREATE TABLE TEST
(ccode varchar2(5)
     CONSTRAINT TEST_FK REFERENCES PARENT_TEST(ccode),
   ...
);

Usage of ON DELETE CASCADE clause

CREATE TABLE TEST
(ccode varchar2(5)
   CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode)
   ON DELETE CASCADE,
   ...
);

Check constraint

Sometimes the data values stored in a specific column must fall within some acceptable range of values.A CHECK constraint requires that the specified check condition is either true or unknown for each row stored in the table.Check constraint allows to impose a conditional rule on a column, which must be validated before data is inserted into the column. The condition must not contain a sub query or pseudo column CURRVAL NEXTVAL, LEVEL, ROWNUM, or SYSDATE.

Oracle allows a single column to have more than one CHECK constraint. In fact, there is no practical limit to the number of CHECK constraints that can be defined for a column.

Syntax:

Column level:

COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]

Table level:

CONSTRAINT [name] CHECK (condition)

The following example shows how to use CHECK constraint at column level.

CREATE TABLE TEST
( ...,
   GRADE char (1) CONSTRAINT TEST_CHK
   CHECK (upper (GRADE) in (''A'',''B'',''C'')),
   ...
);

The following example shows how to use CHECK constraint at table level.

CREATE TABLE TEST
( ...,
   CONSTRAINT TEST_CHK
   CHECK (stdate < = enddate),
);

ALTER TABLE statement

A DBA can make changes to the table structure or column definitions after the table has been created in the database.The DDL command ALTER TABLE is used to perform such actions.Alter command provides multiple utilities exclusive for schema objects.The ALTER TABLE statement is used to add, drop, rename, and modify a column in a table.

The below ALTER TABLE statement renames the table EMP to EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

The below ALTER TABLE statement adds a new column TESTCOL to the EMP_NEW table

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

The below ALTER TABLE statement renames the column TESTCOL to TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

The below ALTER TABLE statement drop the column TESTNEW from EMP_NEW table

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

The below ALTER TABLE statement adds primary key on the EMPLOYEE_ID column.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

The below ALTER TABLE statement drop the primary key.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

The below ALTER TABLE statement switches the table mode to read only.

ALTER TABLE EMP_NEW READ ONLY;

Read Only Tables

Read only tables came as an enhancement in Oracle 11g.It allows the tables to be used for read only purpose. In earlier oracle versions, tables were made read only by granting SELECT privilege to the other users, but owner still had the read write privilege.But now,if a table is set as Read only,even owner doesn”t have access on data manipulation.

Syntax:

ALTER TALE [TABLE NAME] READ ONLY

ALTER TALE [TABLE NAME] READ WRITE

Illustration

SQL>CREATE TABLE ORATEST (id NUMBER)

SQL>INSERT INTO ORATEST VALUES (1);

SQL>ALTER TABLE ORATEST READ ONLY;

SQL> INSERT INTO ORATEST VALUES (2);
INSERT INTO ORATEST VALUES (2)
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> UPDATE ORATEST SET id = 2;
UPDATE ORATEST SET id = 2
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> DELETE FROM ORATEST;
DELETE FROM ORATEST
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> TRUNCATE TABLE ORATEST;
TRUNCATE TABLE ORATEST
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> ALTER TABLE ORATEST ADD (description VARCHAR2 (50));
ALTER TABLE ORATEST ADD (description VARCHAR2 (50))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> ALTER TABLE ORATEST READ WRITE;

Table altered.

SQL> DELETE FROM ORATEST;

1 row deleted.

DROP TABLE statement

The DROP TABLE statement is used to remove a table from the database. The dropped table and its data remain no longer available for selection.Dropped table can be recovered using FLASHBACK utility,if available in recyclebin.Dropping a table drops the index and triggers associated with it.

Syntax:

DROP TABLE [TABLE NAME] [PURGE]

The below statement will drop the table and place it into the recyclebin.

DROP TABLE emp_new;

The below statement will drop the table and flush it out from the recyclebin also.

DROP TABLE emp_new PURGE;

Reporting Aggregate data using the Group functions

SQL has numerous predefined aggregate functions that can be used to write queries to produce exactly this kind of information.The GROUP BY clause specifies how to group rows from a data table when aggregating information, while the HAVING clause filters out rows that do not belong in specified groups.

Aggregate functions perform a variety of actions such as counting all the rows in a table, averaging a column”s data, and summing numeric data. Aggregates can also search a table to find the highest “MAX” or lowest “MIN” values in a column. As with other types of queries, you can restrict, or filter out the rows these functions act on with the WHERE clause. For example, if a manager needs to know how many employees work in an organization, the aggregate function named COUNT(*) can be used to produce this information.The COUNT(*) function shown in the below SELECT statement counts all rows in a table.

SELECT COUNT(*)
FROM employees;

  COUNT(*)
----------
        24

The result table for the COUNT(*) function is a single column from a single row known as a scalar result or value. Notice that the result table has a column heading that corresponds to the name of the aggregate function specified in the SELECT clause.

Some of the commonly used aggregate functions are as below –

SUM( [ALL | DISTINCT] expression )

AVG( [ALL | DISTINCT] expression )

COUNT( [ALL | DISTINCT] expression )

COUNT(*)

MAX(expression)

MIN(expression)

The ALL and DISTINCT keywords are optional, and perform as they do with the SELECT clauses that you have learned to write.The ALL keyword is the default where the option is allowed.The expression listed in the syntax can be a constant,a function, or any combination of column names, constants, and functions connected by arithmetic operators.However, aggregate functions are most often used with a column name. Except COUNT function,all the aggregate functions do not consider NULL values.

There are two rules that you must understand and follow when using aggregates:

• Aggregate functions can be used in both the SELECT and HAVING clauses (the HAVING clause is covered later in this chapter).

• Aggregate functions cannot be used in a WHERE clause. Its violation will produce the Oracle ORA-00934 group function is not allowed here error message.

Illustrations

The below SELECT query counts the number of employees in the organization.

SELECT COUNT(*) Count
FROM employees;

COUNT
-----
   24

The below SELECT query returns the average of the salaries of employees in the organization.

SELECT AVG(Salary) average_sal
FROM employees;

AVERAGE_SAL
-----------
      15694

The below SELECT query returns the sum of the salaries of employees in the organization.

SELECT SUM(Salary) total_sal
FROM employees;

TOTAL_SAL
---------
    87472

The below SELECT query returns the oldest and latest hired dates of employees in the organization.

SELECT MIN (hire_date) oldest, MAX (hire_date) latest
FROM employees;

OLDEST		LATEST
---------	-----------
16-JAN-83	01-JUL-2012

GROUP BY

Aggregate functions are normally used in conjunction with a GROUP BY clause. The GROUP BY clause enables you to use aggregate functions to answer more complex managerial questions such as:

What is the average salary of employees in each department?

How many employees work in each department?

How many employees are working on a particular project?

Group by function establishes data groups based on columns and aggregates the information within a group only. The grouping criterion is defined by the columns specified in GROUP BY clause. Following this hierarchy, data is first organized in the groups and then WHERE clause restricts the rows in each group.

Guidelines of using GROUP BY clause

(1) All the dependent columns or columns used in GROUP BY function must form the basis of grouping, hence must be included in GROUP BY clause also.

SELECT	DEPARTMENT_ID, SUM(SALARY)
FROM employees;

DEPARTMENT_ID,
*
ERROR at line 2:
ORA-00937: not a single-group group function

(2) GROUP BY clause does not support the use of column alias, but the actual names.

(3) GROUP BY clause can only be used with aggregate functions like SUM, AVG, COUNT, MAX, and MIN.If it is used with single row functions,Oracle throws an exception as “ORA-00979: not a GROUP BY expression”.

(4) Aggregate functions cannot be used in a GROUP BY clause. Oracle will return the “ORA-00934: group function not allowed” here error message.

Below query lists the count of employees working in each department.

SELECT  DEPARTMENT_ID,  COUNT (*)
FROM employees
GROUP BY DEPARTMENT_ID;

Similarly, below query to find sum of salaries for respective job ids in each department. Note the group is established based on Department and Job id. So they appear in GROUP BY clause.

SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

The below query also produces the same result. Please note that grouping is based on the department id and job id columns but not used for display purpose.

SELECT SUM (SALARY)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

Use of DISTINCT, ALL keywords with Aggregate functions

By specifying DISTINCT keyword with the input parameter, group by function considers only the unique value of the column for aggregation. By specifying ALL keyword with the input parameter, group by function considers all the values of the column for aggregation, including nulls and duplicates. ALL is the default specification.

The HAVING clause

The HAVING clause is used for aggregate functions in the same way that a WHERE clause is used for column names and expressions.Essentially,the HAVING and WHERE clauses do the same thing, that is filter rows from inclusion in a result table based on a condition. While it may appear that a HAVING clause filters out groups, it does not.Rather,a HAVING clause filters rows.

When all rows for a group are eliminated so is the group.To summarize, the important differences between the WHERE and HAVING clauses are:

A WHERE clause is used to filter rows BEFORE the GROUPING action (i.e., before the calculation of the aggregate functions).

A HAVING clause filters rows AFTER the GROUPING action (i.e., after the calculation of the aggregate functions).

SELECT JOB_ID,	SUM (SALARY)
FROM employees
GROUP BY JOB_ID
HAVING SUM (SALARY) > 10000;

The HAVING clause is a conditional option that is directly related to the GROUP BY clause option because a HAVING clause eliminates rows from a result table based on the result of a GROUP BY clause.

SELECT department_id, AVG(Salary)
FROM employees
HAVING AVG(Salary) > 33000;
ERROR at line 1:  ORA-00937: not a single-group group function 

The essential capabilities of SELECT statement are Selection, Projection and Joining. Displaying specific columns from a table is known as a project operation. We will now focus on displaying specific rows of output. This is known as a select operation. Specific rows can be selected by adding a WHERE clause to a SELECT query. As a matter of fact, the WHERE clause appears just after the FROM clause in SELECT query hierarchy. The sequence has to be maintained in all scenarios. If violated, Oracle raises an exception.

Syntax:

SELECT *|{[DISTINCT] column| expression [alias],..}
FROM table
[WHERE condition(s)]

In the syntax,

• WHERE clause is the keyword

• [condition] contains column names, expressions, constants, literals and a comparison operator.

Suppose that your manager is working on the quarterly budget for your organization. As part of this activity, it is necessary to produce a listing of each employee”s essential details, but only for employees that are paid at least $25,000 annually. The SQL query below accomplishes this task. Note the use of the WHERE clause shown in bold text.

SELECT Employee_ID, Last_Name, First_Name, Salary
FROM employees
WHERE Salary >= 25000;

EMPLOYEE_ID  LAST_NAME        FIRST_NAME       SALARY
----------   ---------------  ---------------  -----------
88303        Jones            Quincey          $30,550.00
88404        Barlow           William          $27,500.00
88505        Smith            Susan            $32,500.00

3 rows selected 

Points to be noted –

• A SELECT clause can contain only one WHERE clause. However, multiple filter conditions can be appended to WHERE clause using AND or OR operator.

• The columns, literals or expressions in a predicate clause must be of similar or interconvertible data types.

• Column alias cannot be used in the WHERE clause.

• Character literals must be enclosed within single quotation marks and are case sensitive.

• Date literals must be enclosed within single quotation marks and are format sensitive. Default format is DD-MON-RR.

Comparison Operators

Comparison operators are used in predicates to compare one term or operand with another term. SQL offers comprehensive set of equality, inequality and miscellaneous operators. They can be used depending on the data and filter condition logic in the SELECT query. When you use comparison operators in a WHERE clause, the arguments (objects or values you are comparing) on both sides of the operator must be either a column name, or a specific value. If a specific value is used, then the value must be either a numeric value or a literal string. If the value is a character string or date, you must enter the value within single quotation marks (” ”).

Oracle has nine comparison operators to be used in equality or inequality conditions.

Operator  Meaning
=         equal to
<         less than
>         greater than
>=        greater than or equal to
<=        less than or equal to
!=        not equal to
<>        not equal to

Other Oracle operators are BETWEEN..AND, IN, LIKE, and IS NULL.

The BETWEEN Operator

The BETWEEN operator can be used to compare a column value within a definite range. The specified range must have a lower and upper limit where both are inclusive during comparison. Its use is similar to composite inequality operator (<= and >=). It can be used with numeric, character and date type values.

For example, the WHERE condition SALARY BETWEEN 1500 AND 2500 in a SELECT query will list those employees whose salary is between 1500 and 2500.

The IN Operator

The IN operator is used to test a column value in a given set of value. If the column can be equated to any of the values from the given set, the condition is validated. The condition defined using the IN operator is also known as the membership condition.

For example, the WHERE condition SALARY IN (1500, 3000, 2500) in a SELECT query will restrict the rows where salary is either of 1500, 3000 or 2500.

The LIKE Operator

The LIKE operator is used for pattern matching and wildcard searches in a SELECT query. If a portion of the column value is unknown, wildcard can be used to substitute the unknown part. It uses wildcard operators to build up the search string, thus search is known as Wildcard search. These two operators are Percentile (”%”) and Underscore (”_”). Underscore (”_”) substitutes a single character while percentile (”%”) replaces more than one characters. They can be used in combination as well.

For example, the below SELECT query lists the first names of those employees whose last name starts with ”SA”.

SELECT first_name
FROM employees
WHERE last_name LIKE ''SA%

IS (NOT) NULL Conditions

To be noted, NULL values cannot be tested using equality operator. It is because NULL values are unknown and unassigned while equality operator tests for a definite value. The IS NULL operator serves as equality operator to check NULL values of a column.

For example, the WHERE condition COMMISSION_PCT IS NULL in a SELECT query will list employees who don”t have commission percentage.

Logical Operators

Multiple filter conditions can be added to the WHERE clause predicate. More than one condition can be combined together using logical operators AND, OR and NOT.

• AND: joins two or more conditions, and returns results only when all of the conditions are true.

• OR: joins two or more conditions, and it returns results when any of the conditions are true.

• NOT: negates the expression that follows it.

The AND operator links two or more conditions in a WHERE clause and returns TRUE only if all the conditions are true. Suppose that a manager needs a list of female employees. Further, the list should only include employees with last names that begin with the letter “E” or that come later in the alphabet. Additionally, the result table should be sorted by employee last name. There are two simple conditions to be met. The WHERE clause may be written as: WHERE Gender = ”F” AND last_name > ”E”.

SELECT last_name "Last Name", first_name "First Name", Gender "Gender"
FROM employees
WHERE Gender = ''F'' AND last_name > ''E''
ORDER BY last_name;

The OR operator links more than one condition in a WHERE clause and returns TRUE if either of the condition returns true. Suppose that your organizational manager”s requirements change a bit. Another employee listing is needed, but in this listing the employees should: (1) be female or, (2) have a last name that begins with the letter “T” or a letter that comes later in the alphabet. The result table should be sorted by employee last name. In this situation either of the two conditions can be met in order to satisfy the query. Female employees should be listed along with employees having a name that satisfies the second condition.

The NOT operator is used to negate an expression or conition.

The ORDER BY Clause

When you display only a few rows of data, it may be unnecessary to sort the output; however, when you display numerous rows, managers may be aided in decision making by having the information sorted. Output from a SELECT statement can be sorted by using the optional ORDER BY clause. When you use the ORDER BY clause, the column name on which you are ordering must also be a column name that is specified in the SELECT clause.

The below SQL query uses an ORDER BY clause to sort the result table by the last_name column in ascending order. Ascending order is the default sort order.

SELECT last_name, first_name
FROM employees
WHERE last_name >= ''J''
ORDER BY last_name;

last_name        first_name
---------------  ---------------
Jones            Quincey
Klepper          Robert
Quattromani      Toni
Schultheis       Robert

Sorting can be based on numeric and date values also. Sorting can also be done based on multiple columns.

By default, the ORDER BY clause will sort output rows in the result table in ascending order. We can use the keyword DESC (short for descending) to enable descending sort. The alternative default is ASC which sorts in ascending order, but the ASC keyword is rarely used since it is the default. When the ASC or DESC optional keyword is used, it must follow the column name on which you are sorting in the WHERE clause.

Positional Sorting – Numeric position of the column in the selected column list can be given in ORDER BY clause, instead of column name. It is mainly used in UNION queries (discussed later). The Query orders the result set by salary since it appears 2nd in the column list.

SELECT  first_name, salary
FROM employees
ORDER BY 2;

Substitution Variables

When a SQL query has to be executed more than once for the different set of inputs, substitution variables can be used. Substitution variables can be used to prompt for user inputs before the query execution. They are widely used in query based report generation which takes data range from the users as input for the conditional filtering and data display. Substitution variables are prefixed by a single-ampersand (&) symbol to temporarily store values. For example,

SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM employees
WHERE LAST_NAME = &last_name
OR EMPLOYEE_ID = &EMPNO;

When the above SELECT query is executed, oracle identifies the ”&” as substitution variable. It prompts user to enter value for ”last_name” and ”EMPNO” as below.

Enter value for last_name:
Enter value for empno:

Once the user provides inputs to both the variables, values are substituted, query is verified and executed.

Points to be noted –

• If the variable is meant to substitute a character or date value, the literal needs to be enclosed in single quotes. A useful technique is to enclose the ampersand substitution variable in single quotes when dealing with character and date values.

• Both SQL Developer and SQL* Plus support the substitution variables and the DEFINE/UNDEFINE commands. Though SQL Developer or SQL* Plus does not support validation checks (except for data type) on user input.

• You can use the substitution variables not only in the WHERE clause of a SQL statement, but also as substitution for column names, expressions, or text.

Using the Double-Ampersand Substitution Variable

When the same substitution variable is used at more than one place, then to avoid re-entering the same data again, we use double ampersand substitution. In such cases, value of the substitution variable, once entered, would be substituted at all instants of usage.

SELECT first_name, HIRE_DATE, SEPARATION_DATE
FROM employees
WHERE HIRE_DATE LIKE ''%&DT%'' AND SEPARATION_DATE ''%&&DT%''

Note that the same value of &DT is substituted twice in the above query. So, its value once given by the user will be substituted at two places.

The DEFINE and VERIFY Commands

Setting the definition of variables in a session is set by DEFINE feature of SQL* Plus. The variables can be defined in the session, so as to avoid halt during query execution. Oracle reads the same variable whenever encountered in an SQL query. It is in ON state by default. With the help of DEFINE clause, one can declare a variable in command line before query execution as DEFINE variable=value;.

Verify command verifies the above substitution showing as OLD and NEW statement. It is OFF by default and can be set to ON using SET command.

SQL> SET DEFINE ON
SQL> SET VERIFY ON
SQL> DEFINE NAME = MARTIN''
SQL> SELECT first_name, SALARY
FROM employees
WHERE first_name = ''&NAME
OLD   1: select first_name, sal from employee where first_name = ''&first_name''
new   1: select first_name, sal from employee where first_name = ''MARTIN''

first_name     SALARY
-------        -------
MARTIN         5000

1. Identify the capabilities of SELECT statement.

1. Projection
2. Selection
3. Data Control
4. Transaction

Answer: A, B. The SELECT statement can be used for selection, projection and joining.

2. Determine the capability of the SELECT statement demonstrated in the given query.

SELECT e.ename, d.dname
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.sal > 1000;

1. Selection
2. Filtering
3. Joining
4. Projection

Answer: A, C, D. Projection is including only the required columns in query, while Selection is selecting only the required data. Joining means combining two tables together through a connecting column.

3. Which of the following clause is used to suppress duplicates in a SELECT statement?

1. INTERSECT
2. DUPLICATE
3. DISTINCT
4. UNIQUE

Answer: C, D. Duplicate data can be restricted with the use of DISTINCT or UNIQUE in the SELECT statement.

4. Chose the statements which correctly specify a rule to write a SQL statement

1. SQL statements are case sensitive
2. Keywords can be abbreviated to build a standard
3. SQL statements are case in-sensitive
4. clauses must be placed together

Answer: C.SQL statements are not case sensitive.

5. Determine the output of the below query –

SELECT ''5+7''
FROM dual;

1. 12
2. 5+7
3. 5
4. 7

Answer: B.Oracle treats the values within double quotes as string expressions.

6. Write a query to display employee details (Name, Department, Salary and Job) from EMP table.

1. SELECT ename, deptno, sal, job FROM emp;

2. SELECT * FROM emp;

3. SELECT DISTINCT ename, deptno, sal, job FROM emp;

4. SELECT ename, deptno, sal FROM emp;

Answer A.Select the required from the tables each separated by a comma.

7. Which of the below queries displays employees” name and new salary after the increment of 1000?

1. SELECT ename, sal FROM emp;

2. SELECT ename, sal=sal+1000 FROM emp;

3. SELECT ename, sal+1000 FROM emp;

4. SELECT ename, 1000 FROM emp;

Answer: C. Basic arithmetic calculations can be done using the columns in SELECT statements.

8. Determine the output of the below query

SELECT 36/2-5*10 FROM dual;

1. 130
2. -32
3. -120
4. 175

Answer: B. Multiplication and Division occur before addition and subtraction.

9. Determine the output of the below query

SELECT (100-25)/15*(20-3) FROM dual;

1. 0.294
2. -85
3. 63.67
4. 85

Answer: D. Expression within the brackets are executed before the divisions and multiplications in the expression.

10. Chose the statements which correctly define a NULL value.

1. NULL is a special value with zero bytes
2. NULL is no value or unknown value
3. NULL is represented by a blank space
4. NULL is not same as zero

Answer: B, D.NULL is NO VALUE but neither same as zero nor as blank or space character.

11. Determine the output of the below query

SELECT sal + NULL
FROM emp
WHERE empno = 7369;

1. sal + NULL
2. NULL
3. 0
4. 1250

Answer: B. Any arithmetic operation with NULL results in NULL.

12. Which of the below statements define column alias correctly?

1. A column alias renames a column heading
2. A column alias is an alternate column in a table
3. A column alias can be specified during table definition
4. A column alias immediately follows the column or expression in the SELECT statement

Answer: A, D. Column Alias can be used to name an expression in the SELECT statement.

13. Specify the column alias NEWSAL for the expression containing salary in the below SQL query

SELECT ename, job, sal + 100 FROM emp;

1. (sal + 100) AS NEWSAL
2. (sal + 100) NEWSAL
3. (sal + 100) IS NEWSAL
4. sal + 100 IS NEWSAL

Answer: A, B.Use ”AS” to signify new alias to a column expression.

14. Specify the column alias “New Salary” for the expression containing salary in the below SQL query

SELECT ename, job, sal + 100 FROM emp;

1. (sal + 100) AS New Salary
2. (sal + 100) “New Salary”
3. (sal + 100) IS New Salary
4. sal + 100 as “New Salary”

Answer: B, D. Column alias with space and special characters must be enquoted within double quotes.

15. Which command is used to display the structure of a table?

1. LIST
2. SHOW
3. DESCRIBE
4. STRUCTURE

Answer: C.DESCRIBE is used to show the table structure.

16. Predict the output when below statement is executed in SQL* Plus?

DESC emp

1. Raises error “SP2-0042: unknown command “desc emp” – rest of line ignored.”
2. Lists the columns of EMP table
3. Lists the EMP table columns, their data type and nullity
4. Lists the columns of EMP table along with their data types

Answer: C. DESCRIBE is used to show the table structure along with table columns, their data type and nullity

17. Which of the below statements are true about the DESCRIBE command?

1. It can be used in SQL*Plus only
2. It can be used in both SQL*Plus as well as SQL Developer
3. It doesn”t works for object tables
4. It doesn”t works for SYS owned tables

Answer: B.

18. Which of the below alphanumeric characters are used to signify concatenation operator in SQL?

1. +
2. ||
3. –
4. ::

Answer: B.In SQL, concatenation operator is represented by two vertical bars (||).

19. Which of the below statements are correct about the usage of concatenation operator in SQL?

1. It creates a virtual column in the table
2. It generates a character expression as the result of concatenation of one or more strings
3. It creates a link between two character columns
4. It can be used to concatenate date expressions with other columns

Answer: B, D. Concatenation operator joins two values as an expression.

20. Predict the output of the below query

SELECT ename || NULL
FROM emp
WHERE empno = 7369

1. SMITH
2. SMITH NULL
3. SMITHNULL
4. ORA-00904: “NULL”: invalid identifier

Answer: A. Concatenation with NULL results into same value.

21. Predict the output of the below query

SELECT 50 || 0001
FROM dual

1. 500001
2. 51
3. 501
4. 5001

Answer: C. The leading zeroes in the right operand of expression are ignored by Oracle.

22. You execute the below query

SELECT e.ename||'' departments''s name is:''|| d.dname
FROM emp e, dept d
where e.deptno=d.deptno;

And get the exception – ORA-01756: quoted string not properly terminated. Which of the following solutions can permanently resolve the problem?

1. Use double quote marks for the literal character string
2. Use [q] operator to enquote the literal character string and selecting the delimiter of choice
3. Remove the single quote mark (apostrophe) from the literal character string
4. Use another delimiter to bypass the single quote apostrophe in the literal string

Answer: B. The [q] operator is used to enquote character literals with a quote.

23. Which of the below SELECT statement shows the correct usage of [q] operator?

1. SELECT e.ename || q''[department''s name is]''|| d.dname
      FROM emp e, dept d
      WHERE e.deptno = d.deptno;

2. SELECT e.ename || q[''department''s name is'']|| d.dname
      FROM emp e, dept d
      WHERE e.deptno = d.deptno;

3. SELECT e.ename || q[department''s name is]|| d.dname
      FROM emp e, dept d
      WHERE e.deptno = d.deptno;

4. SELECT e.ename || q''(department''s name is)''|| d.dname
      FROM emp e, dept d
      WHERE e.deptno = d.deptno;

Answer: A

24. Which of the below SELECT statement is used to select all columns of EMP table?

1. SELECT ALL FROM emp

2. SELECT # FROM emp

3. SELECT * FROM emp

4. SELECT empno,ename,deptno,sal,job,mgr,hiredate FROM emp

Answer: C. The character ”*” is used to select all the columns of the table.

25. Which of the below SQL query will display employee names, department, and annual salary?

1. SELECT ename, deptno, sal FROM emp;

2. SELECT ename, deptno, sal + comm FROM emp;

3. SELECT ename, deptno, (sal * 12) Annual_Sal FROM emp;

4. Annual salary cannot be queried since the column doesn”t exists in the table

Answer: C. Use numeric expressions in SELECT statement to perform basic arithmetic calculations.

Apart from tables, other essential schema objects are view, sequences,indexes and synonyms.A view is a logical or virtual table. Synonyms are simply alias names for database objects.Synonyms also simplify query writing and provide an element of system security by disguising the actual name of a database object.Sequences are special database objects that support the automatic generation of integer values,and are often used to generate primary key values for tables.Indexes are created on table columns to facilitate the rapid retrieval of information from tables.

Views

A database view is a logical or virtual table based on a query.Views are queried just like tables.This means that from your perspective as a developer or from a database system user”s perspective, a view looks like a table.The definition of a view as an object is stored within a database”s data dictionary; however,a view stores no data itself.A database also stores the execution plan for creating a view-this means that data can be retrieved rapidly through use of a view even though the actual data presented by a SELECT query of a view is not stored as part of a view.Rather,the data is “gathered together” each time that a view is queried from the database tables for which a view is defined-these are termed base tables.

The general syntax is given below.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [ViewName]
[(Column Alias Name...)]
AS [Query]
[WITH [CHECK OPTION] [READ ONLY] [CONSTRAINT]];

From the syntax,

The FORCE option allows a view to be created even if a base table that the view references does not already exist.This option is used to create a view prior to the actual creation of the base tables and accompanying data.

The NOFORCE option is the opposite of FORCE and allows a system user to create a view if they have the required privileges to create a view, and if the tables from which the view is created already exist. This is the default option.

The WITH READ ONLY option allows creation of a view that is read-only.You cannot use the DELETE,INSERT,or UPDATE commands to modify data for a read-only view.

The WITH CHECK OPTION clause allows the update of rows that can be selected through the view.It also enables you to specify constraints on values.The CONSTRAINT clause works in conjunction with the WITH CHECK OPTION clause to enable a database administrator to assign a unique name to the CHECK OPTION.If a database administrator omits the CONSTRAINT clause,Oracle will automatically assign the constraint a system-generated name that will not be very meaningful.

Types of Views

A Simple view is created on top of one table only.It is a simple SELECT query with no functions or group clause,but just selection of columns from the table without any transformation.If a DML is performed on the view, it is straightaway reflected in the base table.

A Complex view is created on multiple tables using joins.It can contain SQL functions,Group by functions.But since the view is on multiple data and selection of columns is also not simple, it does not allow DML operation on it.

Illustration

Simple View: The below simple view select employee name, department id and salary for the employees with JOB ID as DEV.

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary
FROM employees
WHERE job_id = ''DEV

Complex view: The below example shows the department name, average salary drawn in the department and the count of employees working in it.

CREATE OR REPLACE VIEW EMP_VU
AS
SELECT department_name, AVG (salary) avg_sal, COUNT (first_name) count
FROM employees E, departments D
WHERE E.department_id = D.department_id
GROUP BY department_name;

DESCRIBE [view name] describes the view structure. Columns are listed in the same sequence as in the view definition.

DML operations on a View

DML operations can be easily exercised on simple views.As stated earlier, the insert, update and delete operations actually happen on the base table.

When you execute an UPDATE, DELETE, or INSERT DML statement on a view, you are actually manipulating the data rows for the base table or tables on which the view is defined.There are restrictions on the use of UPDATE, DELETE, and INSERT statements with views.First, to use the UPDATE, DELETE, or INSERT statement with a view, the view must be updateable.A view is updateable if the SELECT clause does not specify any aggregate function in the SELECT listing.Additionally, the view could not have been created through use of a GROUP BY, DISTINCT, or UNION clause or clauses.It is permissible for aggregate functions to be used in a SELECT subquery in a FROM clause. Also, the view cannot have any derived columns in the SELECT list. Next, if a view is created as the result of a JOIN operation (a join view), the UPDATE and INSERT statements can only modify or insert rows into one of the base tables at a time. You cannot modify rows from two or more tables with a single data manipulation language (DML) statement.Finally, a DELETE statement can only execute against a view if a table is referenced in a FROM clause. This simply means that you cannot delete rows from a table that has not been specified.

WITH CHECK OPTION clause

WITH CHECK OPTION is an optional clause that specifies the level of checking to be done when inserting or updating data through a view.If a view is created using WITH CHECK OPTION clause, every row which gets inserted or updated in the base table through the view must comply with the view definition. Note that the option cannot be specified if the view is created as read-only.

For example, a view V_EMP_DEV is created for employees who are developers (JOB_ID=DEV).

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary,
FROM employees
WHERE job_id = ''DEV''
WITH CHECK OPTION empvu_dev;

A user attempts to update salary of an HR employee through the view but encounters an exception. Its because the view was created WITH CHECK OPTION.

UPDATE v_emp_dev
SET salary = salary+500
WHERE JOB_ID = ''HR
ORA-01402: view WITH CHECK OPTION where-clause violation

If it would have been a simple view, the UPDATE statement would not have raised any exception.

Dropping the view

A database administrator (DBA) or view owner can drop a view with the DROP VIEW statement.If a view has defined constraints, then you need to specify the CASCADE CONSTRAINTS clause when dropping a view; otherwise, the DROP VIEW statement fails to process.If another view or other database object such as a synonym or materialized view (both of these objects are discussed later in this chapter) references a dropped view,Oracle does not drop these database objects; rather, Oracle marks them as invalid.You can drop these invalid objects or redefine them in order to make them valid again.

The below DROP VIEW command drops the view EMP_VU from the database.

DROP VIEW EMP_VU;

Sequences

Oracle provides the capability to generate sequences of unique numbers for this type of use, and they are called sequences.Generally,sequences are used to generate unique,sequential integer values that are used as primary key values in database tables.A sequence of numbers can be generated in either ascending or descending order.Note that a number once generated by sequence cannot be rolled back.

Syntax

CREATE SEQUENCE <sequence name>
[INCREMENT BY < number >]
[START WITH < start value number>]
[MAXVALUE < MAXIMUM VLAUE NUMBER>]
[NOMAXVALUE]
[MINVALUE < minimum value number>]
[CYCLE | NOCYCLE]
[CACHE < number of sequence value to cache> | NOCACHE]
[ORDER | NOORDER];

From the syntax,

The CREATE SEQUENCE statement must specify a unique sequence name. This is the only required clause in the statement. If you do not specify any of the other clauses,all sequence numbers generated will follow the Oracle default settings.

The INCREMENT BY clause determines how a sequence increments as each number is generated. The default increment is one; however,if you have a good reason for a sequence to skip numbers, you can specify a different increment.A positive numeric increment generates ascending sequence numbers with an interval equal to the interval you select.A negative numeric increment generates descending sequence numbers.

The START WITH clause specifies the starting numeric value for the sequence-the default starting number is one.Additionally,you must specify a start value if you already have some rows with data in the column that will now store sequence values.

The MAXVALUE clause specifies the maximum value to which a sequence can be incremented. In the absence of a MAXVALUE, the maximum allowable value that can be generated for a sequence is quite large, 10 to the 27th power – 1. The default is NOMAXVALUE.

The MINVALUE clause specifies the minimum value of a sequence for a decrementing sequence (one that generates numbers in descending order). The default is NOMINVALUE.

The CYCLE clause specifies that sequence values can be reused if the sequence reaches the specified MAXVALUE. If the sequence cycles, numbers are generated starting again at the START WITH value.

The CACHE clause can improve system performance by enabling Oracle to generate a specified batch of sequenced numbers to be stored in cache memory.

If you specify CACHE without specifying a number,the default cache size is 20 sequence numbers.Optionally,you can specify NOCACHE to prevent the cache of sequence numbers.

The ORDER clause specifies that sequence numbers are allocated in the exact chronological order in which they are requested.

NEXTVAL and CURRVAL

Sequence values are generated through the use of two pseudo columns named currval and nextval.A pseudo column behaves like a table column, but pseudo columns are not actually stored in a table.The first time you select the nextval pseudo column, the initial value in the sequence is returned.Subsequent selections of the nextval pseudo column cause the sequence to increment as specified in the INCREMENT BY clause and return the newly generated sequence value.The currval pseudo column returns the current value of the sequence, which is the value returned by the last reference to nextval.

In a session,NEXTVAL, and not the CURRVAL must be the first action on the sequence. This is because in a session, when NEXTVAL generates the first number of the session from the sequence, Oracle keeps the current value in the CURRVAL.

Syntax:

Sequence.NEXTVAL
Sequence.CURRVAL

Points to be noted –

• CURRVAL and NEXTVAL can only be used in the Outer SQL of a select statement.

• CURRVAL and NEXTVAL can be used in INSERT statement to substitute a column primary key.It can be used both as a subquery clause and also in VALUES clause.

• CURRVAL and NEXTVAL can be used to update values in the tables.

• CURRVAL and NEXTVAL cannot be in VIEW select list,with DISTINCT keyword,with GROUP BY,HAVING,or ORDER BY clauses,and DEFAULT expression in a CREATE TABLE or ALTER TABLE statement.

Modifying the sequence

Sequence owner can modify a sequence to alter the attributes like INCREMENT BY value, MINVALUE, MAXVALUE, CYCLE or CACHE clauses only. Note that the changes done would be reflected in the upcoming numbers.

Syntax:

ALTER SEQUENCE [sequence name]
INCREMENT BY n
MAXVALUE n
NOCACHE
NOCYCLE

Dropping the sequence

The DROP SEQUENCE command drops sequences that need to be recreated or are no longer needed.

DROP SEQUENCE [sequence name]

Indexes

Indexes are the database objects that are used to tune the performance of the SELECT query.There are different types of indexes including those used to enforce primary key constraints,unique indexes,non-unique indexes,and concatenated indexes,among others.Without indexes,queries would require Oracle to scan all rows in a table in order to return the required rows for the result table.An index is created on table columns,which then stores all the values of the column under index segment.Unlike sequence,indexes are table specific.They are automatically dropped once the table has been dropped.

Indexes can be created automatically or manually.When you specify a PRIMARY KEY constraint or UNIQUE constraint,Oracle will automatically create a unique index to support rapid data retrieval for the specified table.

Alternatively,user can create indexes manually to optimize the query performance.Manually created indexes can be unique or non unique.Non-unique indexes can be B-Tree,Bitmap or Function based index.By default,Oracle creates B-Tree indexes on columns.Here is the syntax

Syntax

CREATE [UNIQUE][BITMAP]INDEX index
ON table (column [, column]...);

Note that UNIQUE and BITMAP must be specified only for unique and bitmap indexes.By default, Oracle creates B-Tree indexes for normal indexes.

A composite index (also called a concatenated index) is an index created on multiple columns of a table. Columns in a composite index can appear in any order and need not be adjacent columns in the table. Composite indexes enhance row retrieval speed for queries in which the WHERE clause references all or the leading portion of the columns in the composite index. An index can contain a maximum of 32 columns.

For example, a user creates index IDX_EMP on HIRE_DATE column of EMPLOYEES table.The index usage will reduce the disk I/O by traversing the indexed path scan and finds the data which is filtered on HIRE_DATE column.

CREATE INDEX IDX_EMP ON employees(hire_date);

Dropping the Index

Indexes cannot be modified but can be altered for analysis,rebuilding or stats computation purposes.If the index definition has to be modified,it has to be dropped and recreated.The syntax of the DROP INDEX command is simple.

DROP INDEX index_name;

Synonyms

A synonym is an alias,that is,a form of shorthand used to simplify the task of referencing a database object.The concept is analogous to the use of nicknames for friends and acquaintances.Referencing an object owned by another user requires the schema name to be prefixed with it. With the help of a synonym, you reduce the effort of referencing the object along with the schema name.In this way, synonym provides location transparency because the synonym name hides the actual object name and its owner.

There are two categories of synonyms, public and private.A public synonym can be used to allow easy access to an object for all system users. In fact, the individual creating a public synonym does not own the synonym-rather,it will belong to the PUBLIC user group that exists within Oracle.Private synonyms, on the other hand,belong to the system user that creates them and reside in that user”s schema.

Syntax

CREATE [PUBLIC] SYNONYM [synonym name]
FOR OBJECT;

A system user can grant the privilege to use private synonyms that they own to other system users.In order to create synonyms, you need to have the CREATE SYNONYM privilege.Further, you must have the CREATE PUBLIC SYNONYM privilege in order to create public synonyms.If a synonym is declared as public,the synonym name cannot already be in use as a public synonym.Attempting to create a public synonym that already exists will cause the CREATE PUBLIC SYNONYM command to fail, and Oracle will return the ORA-00955: name is already used by an existing object error message.

Illustration

Consider two users U1 and U2.U1 has access to EMPLOYEES table. So to enable the access on EMPLOYEES table to U2 also, a synonym can be created in U2 schema. Access must be granted by U1 to U2.

CONN U2/U2
SQL> CREATE SYNONYM EMP_SYN	FOR U1.employees;

CONN U1/U1
SQL> GRANT ALL ON EMP_SYN TO U2;

CONN U2/U2
SQL> SELECT * FROM EMP_SYN;

Dropping a Synonym

A uer can drop the synonym which it owns. To drop a public synonym, you must have the DROP PUBLIC SYNONYM privilege.

DROP SYNONYM EMP_SYN;

Oracle provide Data Manipulation Language commands to exercise data operations in the database.Data operations can be populating the database tables with the application or business data,modifying the data and removing the data from the database,whenever required. Besides the data operations,there are set of commands which are used to control these operations.These commands are grouped as Transaction Control Language.

There are three types of DML statements involved in a logical SQL transaction namely, Insert, Update, Delete and Merge.A transaction is the logical collection of DML actions within a database session.

INSERT statement

The INSERT command is used to store data in tables. The INSERT command is often used in higher-level programming languages such as Visual Basic.NET or C++ as an embedded SQL command; however,this command can also be executed at the SQL*PLUS prompt in command mode.There are two different forms of the INSERT command. The first form is used if a new row will have a value inserted into each column of the row. The second form of the INSERT command is used to insert rows where some of the column data is unknown or defaulted from another business logic.This form of the INSERT command requires that you specify column names for which data are being stored.

Syntax:

The below syntax can be followed if the values for all the columns in the table is definite and known.

INSERT INTO table
VALUES (column1 value, column2 value,
...);

The below syntax can be used if only few columns from the table have to be populated with a value. Rest of the columns can deduce their values either as NULL or from a different business logic.

INSERT INTO table (column1 name, column2 name, . . .)
VALUES (column1 value, column2 value, . . .);

The INSERT statement below creates a new employee record in the EMPLOYEES table. Note that it inserts the values for the primary columns EMPLOYEE_ID, FIRST_NAME, SALARY and DEPARTMENT_ID.

INSERT INTO employees (EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID)
VALUES (130, ''KEMP'', 3800, 10);

Otherwise, complete employee data can be inserted in the EMPLOYEES table without specifying the column list using the below INSERT statement – provided the values are known beforehand and must be in compliance with the data type and position of columns in the table.

INSERT INTO employees
VALUES (130, ''KEMP'',''GARNER'', ''kemp.garner@xxx.com'', ''48309290'',TO_DATE (''01-JAN-2012''), ''SALES'', 3800, 0, 110, 10);

Values to be inserted must be compatible with the data type of the column. Literals, fixed values and special values like functions, SYSDATE, CURRENT_DATE, SEQ.CURRVAL (NEXTVAL), or USER can be used as column values. Values specified must follow the generic rules. String literals and date values must be enclosed within quotes. Date value can be supplied in DD-MON-RR or D-MON-YYYY format, but YYYY is preferred since it clearly specifies the century and does not depend on internal RR century calculation logic.

INSERT-AS-SELECT (IAS) statement

Data can be populated into the target table from the source table using INSERT..AS..SELECT (IAS) operation. Its a direct path read operation.Its a simple way of creating copy of the data from one table to another or creating a backup copy of the table which the source table operations are online.

For example, data can be copied from EMPLOYEES table to EMP_HISTORY table.

INSERT INTO EMP_HISTORY
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, SALARY, DEPARTMENT_ID
FROM employees;

UPDATE statement

The UPDATE command modifies the data stored in a column.It can update single or multiple rows at a time depending on the result set filtered by conditions specified in WHERE clause. Note that Updating columns is different from altering columns. Earlier in this chapter, you studied the ALTER command.The ALTER command changes the table structure, but leaves the table data unaffected.The UPDATE command changes data in the table, not the table structure.

Syntax:

UPDATE table
SET column = value [, column = value ...]
[WHERE condition]

From the syntax,

The SET column = expression can be any combination of characters, formulas, or functions that will update data in the specified column name.The WHERE clause is optional, but if it is included, it specifies which rows will be updated.Only one table can be updated at a time with an UPDATE command.

The UPDATE statement below updates the salary of employee JOHN to 5000.

UPDATE employees
SET salary = 5000
WHERE UPPER (first_name) = ''JOHN

Though WHERE predicates are optional, but must be logically appended so as to modify only the required row in the table. The UPDATE statement below updates the salaries of all the employees in the table.

UPDATE employees
SET salary = 5000;

Multiple columns can also be updated by specifying multiple columns in SET clause separated by a comma. For example, if both salary and job role has to be changed to 5000 and SALES respectively for JOHN, the UPDATE statement looks like,

UPDATE employees
SET	SALARY = 5000,
	JOB_ID = ''SALES''
WHERE UPPER (first_name) = ''JOHN

1 row updated.

Another way of updating multiple columns of the same row shows the usage of subquery.

UPDATE employees
SET (SALARY, JOB_ID) = (SELECT 5000, ''SALES'' FROM DUAL)
WHERE UPPER (ENAME) = ''JOHN''

DELETE statement

The DELETE command is one of the simplest of the SQL statements. It removes one or more rows from a table. Multiple table delete operations are not allowed in SQL.The syntax of the DELETE command is as below.

DELETE FROM table_name
    [WHERE condition];

The DELETE command deletes all rows in the table that satisfy the condition in the optional WHERE clause. Since the WHERE clause is optional, one can easily delete all rows from a table by omitting a WHERE clause since the WHERE clause limits the scope of the DELETE operation.

The below DELETE statement would remove EDWIN”s details from EMP table.

DELETE employees
WHERE UPPER (ENAME) = ''EDWIN''

1 row deleted.

Note: DELETE [TABLE NAME] and DELETE FROM [TABLE NAME] hold the same meaning.

The WHERE condition in the conditional delete statements can make use of subquery as shown below.

DELETE FROM employees
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
				    FROM LOCATIONS
				    WHERE LOCATION_CODE = ''SFO'')

TRUNCATE

Truncate is a DDL command which is used to flush out all records from a table but retaining the table structure. It does not supports WHERE condition to remove the selected records.

Syntax:

TRUNCATE [table name]

It is Auto Commit i.e. it commits the current active transaction in the session. Truncating the table does not drops dependent indexes, triggers or table constraints. If a table A is parent of a reference constraint of a table B in the database, the table A could not be truncated.

Transaction

A transaction is a logical unit of work done in database. It can either contain –

• Multiple DML commands ending with a TCL command i.e. COMMIT or ROLLBACK

• One DDL command

• One DCL command

Beginning of a transaction is marked with the first DML command. It ends with a TCL, DDL or DCL command. A TCL command i.e. COMMIT or ROLLBACK is issues explicitly to end an active transaction. By virtue of their basic behavior, if any of DDL or DCL commands get executed in a database session, commit the ongoing active transaction in the session. If the database instance crashes abnormally, the transaction is stopped.

COMMIT, ROLLBACK and SAVEPOINT are the transaction control language. COMMIT applies the data changes permanently into the database while ROLLBACK does anti-commit operation. SAVEPOINT controls the series of a transaction by setting markers at different transaction stages. User can roll back the current transaction to the desired save point, which was set earlier.

COMMIT – Commit ends the current active transaction by applying the data changes permanently into the database tables. COMMIT is a TCL command which explicitly ends the transaction. However, the DDL and DCL command implicitly commit the transaction.

SAVEPOINT – Savepoint is used to mark a specific point in the current transaction in the session. Since it is logical marker in the transaction, savepoints cannot be queried in the data dictionaries.

ROLLBACK – The ROLLBACK command is used to end the entire transaction by discarding the data changes. If the transaction contains marked savepoints, ROLLBACK TO SAVEPOINT [name] can be used to rollback the transaction upto the specified savepoint only. As a result, all the data changes upto the specified savepoint will be discarded.

Demonstration

Consider the EMPLOYEES table which gets populated with newly hired employee details during first quarter of every year. The clerical staff appends each employee detail with a savepoint, so as to rollback any faulty data at any moment during the data feeding activity. Note that he keeps the savepoint names same as the employee names.

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (105, ''Allen'',TO_DATE (''15-JAN-2013'',''SALES'',10000,10);

SAVEPOINT Allen;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, ''Kate'',TO_DATE (''15-JAN-2013'',''PROD'',10000,20);

SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, ''McMan'',TO_DATE (''15-JAN-2013'',''ADMIN'',12000,30);

SAVEPOINT McMan;

Suppose, the data feeding operator realises that he has wrongly entered the salary of ”Kate” and ”McMan”. He rolls back the active transaction to the savepoint Kate and re-enters the employee details for Kate and McMan.

ROLLBACK TO SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, ''Kate'',TO_DATE (''15-JAN-2013'',''PROD'',12500,20);

SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, ''McMan'',TO_DATE (''15-JAN-2013'',''ADMIN'',13200,30);

SAVEPOINT McMan;

Once he is done with the data entry, he can commit the entire transaction by issuing COMMIT in the current session.

Read Consistency

Oracle maintains consistency among the users in each session in terms of data access and read/write actions.

When a DML occurs on a table, the original data values changed by the action are recorded in the database undo records. As long as transaction is not committed into database, any user in other session that later queries the modified data views the original data values. Oracle uses current information in the system global area and information in the undo records to construct a read-consistent view of a table”s data for a query. Only when a transaction is committed, the changes of the transaction made permanent. The transaction is the key to Oracle”s strategy for providing read consistency.

Start point for read-consistent views is generated on behalf of readers

Controls when modified data can be seen by other transactions of the database for reading or updating

1. What will be the outcome of the following query?

SELECT ROUND(144.23,-1) FROM dual;

1. 140
2. 144
3. 150
4. 100

Answer: A. The ROUND function will round off the value 144.23 according to the specified precision -1 and returns 140.

Examine the structure of the EMPLOYEES table as given and answer the questions 2 and 3 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

2. You are currently located in New Jersey and have connected to a remote database in San Diego. You issue the following command.

SELECT ROUND (sysdate-hire_date,0) FROM employees WHERE (sysdate-hire_date)/180 = 2;

What is the outcome of this query?

1. An error because the ROUND function cannot be used with Date arguments.
2. An error because the WHERE condition expression is invalid.
3. Number of days since the employee was hired based on the current San Diego date and time.
4. Number of days since the employee was hired based on the current New Jersey date and time.

Answer: C. The SYSDATE function will take the current time of the database which it is connecting to remotely. You must perform basic arithmetic operation to adjust the time zone.

3. You need to display the names of the employees who have the letter ”s” in their first name and the letter ”t” at the second position in their last name. Which query would give the required output?

1. SELECT first_name, last_name FROM employees WHERE INSTR(first_name,''s'')  0 AND SUBSTR(last_name,2,1) = ''t

2. SELECT first_name, last_name FROM employees WHERE INSTR(first_name,''s'')  '''' AND SUBSTR(last_name,2,1) = ''t

3. SELECT first_name, last_name FROM employees WHERE INSTR(first_name,''e'') IS NOT NULL AND SUBSTR(last_name,2,1) = ''t

4. SELECT first_name, last_name FROM employees WHERE INSTR(first_name,''e'')  0 AND SUBSTR(last_name,LENGTH(first_name),1) =
   ''t

Answer: A. The INSTR function returns the position of a given character in the required string. The SUBSTR function returns set of characters from the string from a given starting and end position.

4. Which of the following statements is true regarding the COUNT function?

1. COUNT (*) counts duplicate values and NULL values in columns of any data type.
2. COUNT function cannot work with DATE datatypes.
3. COUNT (DISTINCT job_id) returns the number of rows excluding rows containing duplicates and NULL values in the job_id column.
4. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

Answer: A. The COUNT(*) function 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.

5. Which of the following commands is used to count the number of rows and non-NULL values in Oracle database?

1. NOT NULL
2. INSTR
3. SUBSTR
4. COUNT

Answer: D. The COUNT (ALL column_name) is used to count number of rows excluding NULLs. Similarly, COUNT(*) is used to count the column values including NULLs.

6. What will be the outcome of the query given below?

SELECT 100+NULL+999 FROM dual;

1. 100
2. 999
3. NULL
4. 1099

Answer: C. Any arithmetic operation with NULL results in a NULL.

7. Which of the following statements are true regarding the single row functions?

1. They accept only a single argument.
2. They can be nested only to two levels.
3. Arguments can only be column values or constants.
4. They can return a data type value different from the one that is referenced.

Answer: D. Single row functions can take more than one argument and the return type can be different from the data type of the inputs.

8. Which of the below queries will format a value 1680 as $16,80.00?

1. SELECT TO_CHAR(1680.00,''$99G99D99'') FROM dual;

2. SELECT TO_CHAR(1680.00,''$9,999V99'') FROM dual;

3. SELECT TO_CHAR(1680.00,''$9,999D99'') FROM dual;

4. SELECT TO_CHAR(1680.00,''$99G999D99'') FROM dual;

Answer: A, D. The format model $99G999D99 formats given number into numeric, group separator, and decimals. Other format elements can be leading zeroes, decimal position, comma position, local currency, scientific notation, and sign.

9. Determine the output of the below query.

SELECT RPAD(ROUND(''78945.45''),10,''*'') FROM dual;

1. 78945*****
2. **78945.45
3. The function RPAD cannot be nested with other functions
4. 78945.45****

Answer: A. The LPAD(string, num, char) and RPAD(string, num, char) functions add a character to the left or right of a given string until it reaches the specified length (num) after padding. The ROUND function rounds the value 78945.45 to 78945 and then pads it with ”*” until length of 10 is reached.

10. Which of the following commands allows you to substitute a value whenever a NULL or non-NULL value is encountered in an SQL query?

1. NVL
2. NVLIF
3. NVL2
4. LNNVL

Answer: C. The NVL2 function takes minimum three arguments. The NVL2 function checks the first expression. If it is not null, the NVL2 function returns the second argument. If the first argument is null, the third argument is returned.

11. Which of the following type of single-row functions cannot be incorporated in Oracle DB?

1. Character
2. Numeric
3. Conversion
4. None of the above

Answer: D. The types of single-row functions like character, numeric, date, conversion and miscellaneous as well as programmer-written can be incorporated in Oracle DB.

12. Out of the below clauses, where can the single-row functions be used?

1. SELECT
2. WHERE
3. ORDER BY
4. All of the above

Answer: D. Single row function can be used in SELECT statement, WHERE clause and ORDER BY clause.

13. What is true regarding the NVL function in Oracle DB?

1. The syntax of NVL is NVL (exp1, exp2) where exp1 and exp2 are expressions.
2. NVL (exp1, exp2) will return the value of exp2 if the expression exp1 is NULL.
3. NVL (exp1, exp2) will return the value of the expression exp2 if exp1 is NOT NULL.
4. NVL (exp1, exp2) will return exp1 if the expression exp2 is NULL.

Answer: B. NVL function replaces a null value with an alternate value. Columns of data type date, character, and number can use NVL to provide alternate values. Data types of the column and its alternative must match.

14. Examine the structure of the EMPLOYEES table as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT last_name, NVL(job_id, ''Unknown'')
FROM employees
WHERE last_name LIKE ''A%''
ORDER BY last_name;

1. It will throw an ORA error on execution.
2. It will list the job IDs for all employees from EMPLOYEES table.
3. It will list the job IDs of all employees and substitute NULL job IDs with a literal ”Unknown”.
4. It will display the last names for all the employees and their job IDs including the NULL values in the job ID.

Answer: C. The NVL function replaces a null value with an alternate value. Columns of data type date, character, and number can use NVL to provide alternate values. Data types of the column and its alternative must match.

15. What will the outcome of the following query?

SELECT NVL (NULL,''1'') FROM dual;

1. NULL
2. 1
3. 0
4. Gives an error because NULL cannot be explicitly specified to NVL function

Answer: B. The NVL will treat NULL as a value and returns the alternate argument i.e. 1 as the result.

16. What will be the outcome of the following query? (Consider the structure of the EMPLOYEES table as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT employee_id , NVL(salary, 0) FROM employees WHERE first_name like ''P%'' ORDER BY first_name;

1. It will display 0 in the salary column for all the employees whose first name starts with a ”P”
2. It will display the salaries for the employees whose name start with a ”P” and 0 if the salaries are NULL.
3. It will throw an ORA error as the ORDER BY clause should also contain the salary column.
4. The NVL function should be correctly used as NVL (0, salary)

Answer: B. NVL function replaces a null value with an alternate value. Columns of data type date, character, and number can use NVL to provide alternate values. Data types of the column and its alternative must match.

17. Which of the following statements is true regarding the NVL statement?

SELECT NVL (arg1, arg2) FROM dual;

1. The two expressions arg1 and arg2 should only be in VARCHAR2 or NUMBER data type format.
2. The arguments arg1 and arg2 should have the same data type
3. If arg1 is VARCHAR2, then Oracle DB converts arg2 to the datatype of arg1 before comparing them and returns VARCHAR2 in the character set of arg1.
4. An NVL function cannot be used with arguments of DATE datatype.

Answer: C. If arg1 is of VARCHAR2 data type, Oracle does implicit type conversion for arg2 id arg2 is of NUMBER datatype. In all other cases, both the arguments must be of same datatype.

18. What will be the outcome of the following query? (Consider the structure of the EMPLOYEES table as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT NVL2(job_id,''Regular Employee'',''New Joinee'') FROM employees;

1. It will return the value ”Regular Employee” for all the employees who have NULL job IDs
2. It will return the value ”New Joinee” for all the employees who have NULL job IDs
3. It will return ”Regular Employee” if the job ID is NULL
4. It will throw an ORA error on execution.

Answer: B. The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.

19. Which of the following is true for the statement given as under.

NVL2 (arg1, arg2, arg3)

1. Arg2 and Arg3 can have any data type
2. Arg1 cannot have the LONG data type
3. Oracle will convert the data type of expr2 according to Arg1
4. If Arg2 is a NUMBER, then Oracle determines the numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

Answer: D. The data types of the arg2 and arg3 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 arg3 to the type of the arg2 parameter. The data type returned by the NVL2 function is the same as that of the arg2 parameter.

20. Examine the structure of the EMPLOYEES table as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SeLECT first_name, salary, NVL2(commission_pct,  salary + (salary * commission_pct), salary) "Income"
FROM employees
WHERE first_name like ''P%''
ORDER BY first_name;

1. Salary will be returned if the Commission for the employee is NOT NULL.
2. Commission_pct will be returned if the Commission for the employee is NOT NULL.
3. Employees with the first name starting with ”P” and salary+(salary*commission_pct) will be returned if the employee earns a commission.
4. The query throws an error because a mathematical expression is written inside NVL2.

Answer: C. The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.

21. What is true about the NULLIF function in Oracle DB?

1. NULLIF(expr1,expr2) will return expr2 if the two expressions are NOT NULL.
2. NULLIF(expr1,expr2) will return 0 if the two expressions are NULL.
3. NULLIF(expr1,expr2) will return NULL if the two expressions are equal.
4. Expr1 can be NULL in NULLIF(expr1, expr2)

Answer: C. The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(arg1,arg2), where the arguments arg1 and arg2 are compared. If they are identical, then NULL is returned. If they differ, the arg1 is returned.

22. Pick the correct answer given after the statement shown as under.

NULLIF (arg1,arg2) 

1. Arg1 and Arg2 can be of different data types.
2. Arg1 and Arg2 have to be equal in order to be used in the NULLIF function.
3. There is no internal conversion of data types if NULLIF used as in the case of NVL and NVL2.
4. This is equivalent to CASE WHEN Arg1 = Arg22 THEN NULL ELSE Arg1 END.

Answer: D.

23. Examine the structure of the EMPLOYEES table as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

You need to create a report from the HR schema displaying employees who have changed jobs since they were hired. You execute the query given below.

SELECT e.last_name, NULLIF(e.job_id, j.job_id,"Old Job ID")
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name;

What will be the outcome of the query given above?

1. It will display the old job ID when the new job ID is NULL.
2. It will execute successfully and produce the required output.
3. It will display the new job ID if the new job ID is equal to the old job ID
4. It will throw an ORA error on execution.

Answer: D.

24. Which of the following is not a property of functions?

1. Perform calculations on data
2. Convert column data types
3. Modify individual data items
4. None of the above

Answer: D. Functions can perform calculations, perform case conversions and type conversions.

25. What is the most appropriate about single row functions?

1. They return no value
2. They return one result per row and operate on all the rows of a table.
3. They return one result per row with input arguments
4. They return one result per set of rows and operate on multiple rows.

Answer: B. Single row functions always return one result per row and they operate on single rows only; hence the name ‘Single Row” is given to them.

26. What among the following is a type of Oracle SQL functions?

1. Multiple-row functions
2. Single column functions
3. Single value functions
4. Multiple columns functions

Answer: A. There are basically two types of functions – Single row and Multiple row functions.

27. What among the following is a type of single-row function?

1. VARCHAR2
2. Character
3. LONG
4. NULLIF

Answer: B and D. As Character and NULLIF are single row function and rest are the datatypes.

28. What is the most appropriate about Multiple Row Functions?

1. They return multiple values per each row. 
2. They return one result per group of rows and can manipulate groups of rows. 
3. They return one result per row and can manipulate groups of rows. 
4. They return multiple values per a group of row.

Answer: B. Multiple Row functions always work on a group of rows and return one value per group of rows.

29. Which of the following are also called Group functions?

1. Single row functions
2. Multi group functions
3. Multiple row functions
4. Single group functions.

Answer: C. Group functions are same as Multi row functions and aggregate functions.

30. Which of the following is true about Single Row Functions?

1. They can be nested
2. They accept arguments and return more than one value.
3. They cannot modify a data type
4. They cannot accept expressions as arguments.

Answer: A. Single row functions can be nested up to multiple levels.

31. What is the number of arguments Single Row functions accept?

1. 0
2. Only 1
3. Only 2
4. 1 or more than 1

Answer: D. Single row functions can accept one or more arguments depending upon the objective they serve.

32. Which of the following can be an argument for a Single Row Function?

1. Data types
2. SELECT statements
3. Expression
4. Table name

Answer: C. A user-supplied constant, variable value, column value and expression are the types of arguments of a single row function.

33. What is true about Character functions?

1. They return only character values
2. They accept NUMBER values
3. They accept character arguments and can return both character and number values
4. They accept values of all data type

Answer: C. The character function INSTR accepts a string value but returns numeric position of a character in the string.

34. What is true about Number functions?

1. They return both Character as well as Number values
2. They can”t accept expressions as input
3. Number functions can”t be nested.
4. They accept Number arguments and return Number values only.

Answer: D.

35. Which of the following is an exception to the return value of a DATE type single-row function?

1. TO_DATE
2. SYSDATE
3. MONTHS_BETWEEN
4. TO_NUMBER

Answer: C. All the DATE data type functions return DATE as return values except MONTHS_BETWEEN which returns a number.

36. Which of the following is not a Conversion type Single Row function?

1. TO_CHAR
2. TO_DATE
3. NVL
4. TO_NUMBER

Answer: C. Conversion functions convert a value from one data type to another. The NVL function replaces a null value with an alternate value.

37. Which of the following is a Case-Conversion Character function?

1. CONCAT
2. SUBSTR
3. INITCAP
4. REPLACE

Answer: C. The CONCAT, SUBSTR and REPLACE are Character-manipulation Character functions while INITCAP, LOWER and UPPER are case conversion character functions.

38. What will be the outcome of the following query?

SELECT lower(''HI WORLD !!!'')  FROM dual;

1. Hi World !!!
2. Hi WORLD !!!
3. hi world !!!
4. HI WORLD !!!

Answer: C. The LOWER function converts a string to lower case characters.

39. What will be the outcome of the following query?

SELECT lower(upper(initcap(''Hello World'') )) FROM dual;

1. Hello World
2. HELLO world
3. hello World
4. hello world

Answer: D. Case conversion characters can be nested in the SELECT queries.

Examine the structure of the EMPLOYEES table as given and answer the questions 40 to 42 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

40.Which of the following queries will give the same result as given in the query given below?

SELECT CONCAT(first_name, last_name) FROM employees;

1. SELECT first_name||last_name FROM employees;
2. SELECT first_name||” ” || last_name FROM employees;
3. SELECT last_name||”, ”||first_name FROM employees;
4. SELECT first_name||”,”||last_name FROM employees;

Answer: A. The CONCAT function joins two strings without any space in between.

41. What will be the outcome of the following query?

SELECT ''The job id for ''||upper(last_name) ||'' is a ''||lower(job_id) FROM employees;

1. The job id for ABEL is a sa_rep
2. The job id forABEL is a sa_rep
3. The job id for abel is SA_REP
4. The job id for abel is sa_rep

Answer: A.

42. Assuming the last names of the employees are in a proper case in the table employees, what will be the outcome of the following query?

SELECT employee_id, last_name, department_id  FROM employees WHERE last_name = ''smith

1. It will display the details of the employee with the last name as Smith
2. It will give no result.
3. It will give the details for the employee having the last name as ”Smith” in all Lower case.
4. It will give the details for the employee having the last name as ”Smith” in all INITCAP case.

Answer: B. Provided the last names in the employees table are in a proper case, the condition WHERE last_name = ”smith” will not be satistified and hence no results will be displayed.

43. What is true about the CONCAT function in Oracle DB?

1. It can have only characters as input.
2. It can have only 2 input parameters.
3. It can have 2 or more input parameters
4. It joins values by putting a white space in between the concatenated strings by default.

Answer: B. The CONCAT function accepts only two arguments of NUMBER or VARCHAR2 datatypes.

44. What is true about the SUBSTR function in Oracle DB?

1. It extracts a string of determined length
2. It shows the length of a string as a numeric value
3. It finds the numeric position of a named character
4. It trims characters from one (or both) sides from a character string

Answer: A. The SUBSTR(string, x, y) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position (x). When position is positive, then the function counts from the beginning of string to find the first character. When position is negative, then the function counts backward from the end of string.

45. What will be the outcome of the following query?

SELECT length(''hi'') FROM dual;

1. 2
2. 3
3. 1
4. hi

Answer: A. the LENGTH function simply gives the length of the string.

46. What is the difference between LENGTH and INSTR functions in Oracle DB?

1. They give the same results when operated on a string.
2. LENGTH gives the position of a particular character in a string
3. INSTR gives the position of a particular character in a string while LENGTH gives the length of the string.
4. LENGTH and INSTR can be used interchangeably.

Answer: C.

47. Examine the structure of the EMPLOYEES table as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT upper(&jobid) FROM employees;

1. It results in an error as substitution variables cannot be used with single row functions
2. It prompts the user to input the jobid on each execution and then displays the job id in UPPER case
3. It gives the jobid as it is present in the table EMPLOYEES without making any change
4. It will not ask the user to input the job id and will convert all the job IDs in the table in UPPER case

Answer: B. Substitution variables can be used with the UPPER and LOWER functions.

48. What is false about the table DUAL in Oracle database?

1. It is owned by the user SYS and can be access by all the users.
2. It contains only one column and one row.
3. The value in the DUMMY column of the DUAL table is ”X”
4. The DUAL table is useful when you want to return a value only once

Answer: C. The DUAL table has one column named DUMMY and one row which has a value ”X”.

49. What will be the result of the following query?

SELECT sysdate+4/12 FROM dual;

1. The query produces error.
2. No of hours to a date with date as the result.
3. Sysdate arithmetic is ignored.
4. Returns the system date as result.

Answer: B. Arithmetic operations can be performed on dates in the Oracle DB.

50. What will be the outcome of the following query?

SELECT lower (100+100) FROM dual;

1. 100
2. 100+100
3. ORA error
4. 200

Answer: D. Arithmetic expressions can be specified within case conversion functions.

51. What will be the outcome of the following query if the SYSDATE = 20-MAY-13?

SELECT upper (lower (sysdate)) FROM dual;

1. 20-may-2013
2. ORA error as LOWER and UPPER cannot accept date values.
3. 20-MAY-13
4. 20-May-13

Answer: C. The functions UPPER and LOWER can accept date type inputs and will yield the same result as they do on Strings.

52. What is the result of the following query?

SELECT INITCAP (24/6) FROM dual;

1. 4
2. 24
3. 24/6
4. No result

Answer: A. Arithmetic expressions can be specified within case conversion functions.

53. Examine the structure of the EMPLOYEES table as given here.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

You need to display the last name of all employees which starts with the letter ”A”. Which of the following queries will yield the required result?

1. SELECT INITCAP (last_name||'' works as a ''||job_id "Job Description" FROM employees WHERE initcap (last_name) like ''A%

2. SELECT INITCAP (last_name) ||INITCAP('' works as a: '')|| INITCAP(job_id) "Job Description" FROM employees WHERE initcap (last_name) like ''A
   %

3. SELECT INITCAP (last_name||'' works as a ''||INITCAP(job_id)) "Job Description" FROM employees WHERE initcap (last_name) = ''A

4. SELECT UPPER (LOWER (last_name||'' works as a ''||job_id)) "Job Description" FROM employees WHERE lower (last_name) = ''A

Answer: A, B.

54. Assuming the SYSDATE is 20-FEB-13, What will be the outcome of the following query?

SELECT CONCAT (''Today is :'', SYSDATE) FROM dual;

1. Today is : 20-feb-13
2. The query throws error of incompatible type arguments.
3. Today is : 20-Feb-13
4. Today is : 20-FEB-13

Answer: D. The CONCAT function accepts arguments of all types.

55. What will be the result pattern of the following query?

SELECT CONCAT(first_name, CONCAT (last_name, job_id)) FROM dual;

1. First_namelast_namejob_id
2. First_name, last_name, job_id
3. Error as CONCAT cannot be nested
4. First_namelast_name, job_id

Answer: A. The CONCAT function can be nested with self or other character function.

56. Examine the structure of the EMPLOYEES table as given here.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

You need to generate a report which shows the first name, last name and the salary for all the employees in the department 100. The report should show the results in the form ”Andy Smith earns 50000”. Which of the following queries will give the required output?

1. SELECT concat (first_name,concat ('' '', concat(last_name, concat('' earns '', SALARY)))) Concat_String FROM employees WHERE department_id =
   100;

2. SELECT concat (first_name, last_name||'' ''|| salary) FROM employees WHERE department_id = 100;

3. SELECT concat (first_name, concat(last_name, '' ''))||earns||salary FROM employees WHERE department_id = 100;

4. SELECT concat (first_name, concat(last_name, ''earns salary'') FROM employees WHERE department_id = 100;

Answer: A. The CONCAT function can be nested with self or other character function.

57. What will the following query show as a result?

SELECT LENGTH(''It is a lovely day today!'') FROM dual;

1. 25
2. 19
3. 20
4. 0

Answer: A. The LENGTH functions counts blank spaces, tabs and special characters too.

58. You need to display the country name from the COUNTRIES table. The length of the country name should be greater than 5 characters. Which of the following queries will give the required output?

1. SELECT country_name FROM countries WHERE LENGTH (country_name)= 5;

2. SELECT country_name FROM countries WHERE length (country_name)> 5;

3. SELECT SUBSTR(country_name, 1,5) FROM countries WHERE length (country_name)

4. SELECT country_name FROM countries WHERE length (country_name)  5;

Answer: B. The LENGTH function can be used in WHERE clause.

59. How does the function LPAD works on strings?

1. It aligns the string to the left hand side of a column
2. It returns a string padded with a specified number of characters to the right of the source string
3. It aligns character strings to the left and number strings to right of a column
4. It returns a string padded with a specified number of characters to the left of the source string

Answer: D. The LPAD(string, length after padding, padding string) and RPAD(string, length after padding, padding string) functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

60. Which of the following options is true regarding LPAD and RPAD functions?

1. The character strings used for padding include only characters.
2. The character strings used for padding include only literals
3. The character strings used for padding cannot include expressions.
4. The character strings used for padding include literals, characters and expressions.

Answer: D.

61. What is the maximum number of input arguments in LPAD and RPAD functions?

1. 1
2. 2
3. 3
4. 0

Answer: C. LPAD and RPAD take maximum of 3 arguments. If there are 2 arguments given, the padding happens by spaces.

62. What will be the outcome of the following query?

SELECT lpad (1000 +300.66, 14, ''*'') FROM dual;

1. *******1300.66
2. 1300*******
3. 1300.66
4. ****1300.66

Answer: A. To make the total length of 14 characters, the return value 1300.66 is padded with 7 asterisks (*) on the left.

63. What is true regarding the TRIM function?

1. It is similar to SUBSTR function in Oracle
2. It removes characters from the beginning or end of character literals, columns or expression
3. TRIM function cannot be applied on expressions and NUMBERS
4. TRIM function can remove characters only from both the sides of a string.

Answer: B. The TRIM function literally trims off leading or trailing (or both) character strings from a given source string. TRIM function when followed by TRAILING or LEADING keywords, can remove characters from one or both sides of a string.

64. You need to remove the occurrences of the character ”.” and the double quotes ””” from the following titles of a book present in the table MAGAZINE.

"HUNTING THOREAU IN NEW HAMPSHIRE" THE ETHNIC NEIGHBORHOOD."

Which of the following queries will give the required result?

1. SELECT LTRIM(Title,''"'') FROM MAGAZINE;

2. SELECT LTRIM(RTRIM(Title,''."''),''"'') FROM MAGAZINE;

3. SELECT LTRIM (Title,''"THE'') FROM MAGAZINE;

4. SELECT LTRIM(RTRIM(Title,''."THE''),''"'') FROM MAGAZINE;

Answer: B. The LTRIM and RTRIM functions can be used in combination with each other.

65. What will be returned as a result of the following query?

SELECT INSTR(''James'',''x'') FROM dual;

1. 1
2. 2
3. 0
4. 3

Answer: C. INSTR function returns a 0 when the search string is absent in the given string.

66. What will be the outcome of the following query?

SELECT INSTR(''1$3$5$7$9$'',''$'',3,4)FROM dual;

1. 2
2. 10
3. 7
4. 4

Answer: B. INSTR function search for the 4th occurrence of ”$” starting from the 3rd position.

67. What will be the result of the following query?

SELECT INSTR(''1#3#5#7#9#'', -3,2) FROM dual;

1. #5
2. #3
3. #7
4. #9

Answer: D. SUBSTR function will search 3 places starting from the end of string and will give 2 characters in the forward direction giving #9.

Examine the structure of the EMPLOYEES table as given below and answer the questions 68 and 69 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

68. You need to extract a consistent 15 character string based on the SALARY column in the EMPLOYEES table. If the SALARY value is less than 15 characters long, zeros must be added to the left of the value to yield a 15 character string. Which query will fulfill this requirement?

1. SELECT rpad(salary, 15,0) FROM employees;

2. SELECT lpad(salary,15,0) FROM employees;

3. SELECT ltrim(salary,15,0) FROM employees;

4. SELECT trim(salary,15,0) FROM employees;

Answer: B. The LPAD and RPAD functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

69. You need to display the last 2 characters from the FIRST_NAME column in the EMPLOYEES table without using the LENGTH function. Which of the following queries can fulfill this requirement?

1. SELECT SUBSTR(first_name, 2) FROM employees;

2. SELECT SUBSTR(first_name, -2) FROM employees;

3. SELECT RTRIM(first_name, 2) FROM employees;

4. SELECT TRIM(first_name, 2) FROM employees;

Answer: B. The SUBSTR(string, x, y) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position (x). When position is positive, then the function counts from the beginning of string to find the first character. When position is negative, then the function counts backward from the end of string.

70. Assuming the SYSDATE is 13-JUN-13, what will be the outcome of the following query?

SELECT SUBSTR(sysdate,10,7) FROM dual;

1. 3
2. N-13
3. 0
4. NULL

Answer: D. The query will give a NULL as the position 10 to start with in the SYSDATE doesn”t exist.

71. Which of the following is used to replace a specific character in a given string in Oracle DB?

1. LTRIM
2. TRIM
3. TRUNC
4. REPLACE

Answer: D.

72. What will be the outcome of the following query?

SELECT replace(9999.00-1,''8'',88) FROM dual;

1. 999
2. 9998
3. 99988
4. 9999.88

Answer: C. The REPLACE function searches for ”8” in 9998 and replaces it with ”88”.

73. Examine the structure of the EMPLOYEES table as given here.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

You need to retrieve the first name, last name (separated by a space) and the formal names of employees where the combined length of the first name and last name exceeds 15 characters. A formal name is formed by the first letter of the First Name and the first 14 characters of the last name. Which of the following queries will fulfill this requirement?

1. SELECT first_name, last_name ,SUBSTR(first_name, 1,1)||'' ''||SUBSTR(last_name, 1,14) formal_name FROM employees;

2. SELECT first_name, last_name ,SUBSTR(first_name, 1,14)||'' ''||SUBSTR(last_name, 1,1) formal_name FROM employees WHERE length
   (first_name) + length(last_name) 

3. SELECT first_name, last_name ,SUBSTR(first_name, 1,1)||'' ''||SUBSTR(last_name, 1,14) formal_name FROM employees WHERE length
   (first_name) + length(last_name) =15;

4. SELECT first_name, last_name ,SUBSTR(first_name, 1,1)||'' ''||SUBSTR(last_name, 1,14) formal_name FROM employees WHERE length
   (first_name) + length(last_name) > 15;

Answer: D.

74. What will be the outcome of the following query?

SELECT round(148.50) FROM dual;

1. 148.50
2. 140
3. 150
4. 149

Answer: D. if the decimal precision is absent, the default degree of rounding is 0 and the source is rounded to the nearest whole number.

75. Assuming the sysdate is 10-JUN-13, What will be the outcome of the following query?

SELECT trunc (sysdate,''mon'') FROM dual;

1. 10-JUN-13
2. 1-JUN-13
3. ORA error as the TRUNC function can”t have an input parameter when used with dates.
4. 31-JUN-13

Answer: B. The date is truncated to the first day of the month. Similarly, it can be done for year also.

76. What will be the result of the following query?

SELECT trunc(1902.92,-3) FROM dual;

1. 2000
2. 1000
3. 1901
4. 1901.00

Answer: B.

77. What is the syntax of the MOD function in Oracle DB?

1. Mod(divisor,dividend)
2. MOD(divisor,1)
3. MOD(dividend,divisor)
4. None of the above

Answer: C. The MOD function is used to get the remainder of a division operation.

78. What will be outcome of the following query?

SELECT mod(100.23,-3) FROM dual;

1. ORA error
2. 1.23
3. 100
4. 0

Answer: B. The MOD function gives the same answer for a positive divisor as well as a negative divisor.

79. Which of the following functions are used to differentiate between even or odd numbers in Oracle DB?

1. ROUND
2. TRUNC
3. MOD
4. REPLACE

Answer: C. The MOD function can be used to check whether a given number is even or odd. If MOD (num,2) returns zero, the number ”num” is an even. If MOD (num,2) returns 1, the number ”num” is odd.

80. Examine the structure of the EMPLOYEES table as given below.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

You need to allocate the first 12 employees to one of the four teams in a round-robin manner. The employee IDs start with a 100. Which of the following queries will fulfill the requirement?

1. SELECT * FROM employees WHERE employee_id between 100 and 111 ORDER BY employee_id;

2. SELECT first_name, last_name, employee_id, mod(employee_id, 4) Team# FROM employees WHERE employee_id between 100 and 111
   ORDER BY employee_id;

3. SELECT first_name, last_name,mod(employee_id, 2) Team# FROM employees WHERE employee_ID  100;

4. SELECT first_name, last_name, mod(employee_id, 4) Team# FROM employees WHERE employee_ID = 100;

Answer: B.

81. What will be the outcome of the following query?

SELECT SUBSTR(''Life is Calling'',1) FROM dual;

1. ORA error as there should be minimum 3 arguments to the SUBSTR function.
2. Life is Calling
3. NULL
4. Life

Answer: B. Calling the SUBSTR function with just the first two parameters results in the function extracting a string from a start position to the end of the given source string.

82. What is the default data format for the sysdate in SQL Developer?

1. DD-MON-YY
2. DD-MON-RR
3. DD/MON/RR
4. DD/MON/YYYY

Answer: C. For SQL*PLUS the default date format is DD-MON-RR.

83. Assuming the SYSDATE to be 10-JUN-2013 12:05pm, what value is returned after executing the below query?

SELECT add_months(sysdate,-1) FROM dual;

1. 09-MAY-2013 12:05pm
2. 10-MAY-2013 12:05pm
3. 10-JUL-2013 12:05pm
4. 09-JUL-2013 12:05pm

Answer: B. The ADD_MONTHS(date, x) function adds ”x” number of calendar months to the given date. The value of ”x” must be an integer and can be negative.

84. What value will be returned after executing the following statement? Note that 01-JAN-2013 occurs on a Tuesday.

SELECT next_day(''01-JAN-2013'',''friday'') FROM dual;

1. 02-JAN-2013
2. Friday
3. 04-JAN-2013
4. None of the above

Answer: C. The NEXT_DAY(date,”day”) finds the date of the next specified day of the week (”day”) following date. The value of char may be a number representing a day or a character string.

85. What is the maximum number of parameters the ROUND function can take?

1. 0
2. 1
3. 2
4. 3

Answer: C. If there is only one parameter present, then the rounding happens to the nearest whole number

86. Assuming the present date is 02-JUN-2007, what will be the century returned for the date 24-JUL-2004 in the DD-MON-RR format?

1. 19
2. 21
3. 20
4. NULL

Answer: C. If the two digits of the current year and the specified year lie between 0 and 49, the current century is returned.

87. Assuming the present date is 02-JUN-2007, what will be the century returned for the date 24-JUL-94 in the DD-MON-RR format?

1. 19
2. 21
3. 20
4. NULL

Answer: A. If the two digits of the current year lie between 0 and 49 and the specified year falls between 50 and 99, the previous century is returned.

88. Assuming the present date is 02-JUN-1975, what will be the century returned for the date 24-JUL-94 in the DD-MON-RR format?

1. 19
2. 21
3. 20
4. NULL

Answer: A. if the two digits of the current and specified years lie between 50 and 99, the current century is returned by default.

89. Assuming the present date is 02-JUN-1975, what will be the century returned for the date 24-JUL-07 in the DD-MON-RR format?

1. 19
2. 21
3. 20
4. NULL

Answer: C. if the two digits of the current year lie between 50 and 99 and the specified year falls between 0 and 49, the next century is returned.

90. How many parameters does the SYSDATE function take?

1. 1
2. 2
3. 4
4. 0

Answer: D. The SYSDATE is a pseudo column in Oracle.

91. What is true about the SYSDATE function in Oracle DB?

1. It returns only the system date
2. It takes 2 parameters at least.
3. The default format is DD-MON-YY
4. The default format of SYSDATE is DD-MON-RR and it returns the date and time of the system according to the database server.

Answer: D.

92. What will be the datatype of the result of the following operation?

“Date3 = Date1-Date2”

1. Date
2. Num1
3. 0
4. NULL

Answer: B. Subtraction of two dates results in number of days.

93. What will be the datatype of the result of the following operation?

“Date2 = Date1-Num1”

1. Date
2. Num1
3. 0
4. NULL

Answer: A. Subtraction of a number from a date value results in date.

94. What does a difference between two dates represent in Oracle DB?

1. The number of days between them
2. Difference in dates in not possible in Oracle DB
3. A date
4. NULL

Answer: A.

95. What will be the outcome of the following query?

SELECT months_between(''21-JUN-13'',''19-JUN-13'') FROM dual; 

1. ORA error
2. A positive number
3. A negative number
4. 0

Answer: C. If the first parameter is less than the second parameter, the MONTHS_BETWEEN returns a negative number.

96. What can be deduced if the result of MONTHS_BETWEEN (start_date,end_date) function is a fraction?

1. It represents the difference in number between the start date and end date.
2. The result cannot be a fractional number, it has to be a whole number.
3. NULL
4. It represents the days and the time remaining after the integer difference between years and months is calculated and is based on a 31-day month.

Answer: D.

97. You are connected to a remote database in Switzerland from India. You need to find the Indian local time from the DB. Which of the following will give the required result?

1. SELECT sysdate FROM dual;

2. SELECT round(sysdate) FROM dual;

3. SELECT trunc (sysdate) FROM dual;

4. SELECT current_date FROM dual;

Answer: D.

98. What will be the outcome of the following query?

SELECT months_between (to_date (''29-feb-2008''), to_date (''29-feb-2008 12:00:00'',''dd-mon-yyyy hh24:mi:ss''))*31 FROM dual; 

1. Approximately 0
2. 1
3. The query will throw an ORA error
4. 0.5 days

Answer: D. The 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.

99. What will be the outcome of the following query?

SELECT add_months (''31-dec-2008'',2.5) FROM dual; 

1. 31-feb-2009
2. 28-feb-2009
3. 31-mar-2009
4. 15-jan-2009

Answer: B. the fractional part of 2.5 will be ignored and 2 months will be added to 31-dec-2012 which is 31-feb-2013 but as it is not a valid date, the result is 28-feb-2009.

100. You need to identify the date in November when the staff will be paid. Bonuses are paid on the last Friday in November. Which of the following will fulfill the requirement?

1. SELECT next_day (''30-nov-2012'' , ''Friday'') FROM dual;

2. SELECT next_day (''30-nov-2012'' , ''Friday'') -7 FROM dual;

3. SELECT last_day (''01-nov-2012'' ) FROM dual;

4. SELECT next_day (''30-nov-2012'' , ''sat'') -1 FROM dual;

Answer: B. The NEXT_DAY(date,”day”) and LAST_DAY (date,”day”) functions find the date of the next or last specified day of the week (”day”) following date. The value of char may be a number representing a day or a character string.

Using Single row functions to customize output

Oracle SQL supplies a rich library of in-built functions which can be employed for various tasks. The essential capabilities of a functions can be the case conversion of strings, in-string or substring operations, mathematical computations on numeric data, and date operations on date type values. SQL Functions optionally take arguments from the user and mandatorily return a value.

On a broader category, there are two types of functions :-

Single Row functions – Single row functions are the one who work on single row and return one output per row. For example, length and case conversion functions are single row functions.

Multiple Row functions – Multiple row functions work upon group of rows and return one result for the complete set of rows. They are also known as Group Functions.

Single row functions

Single row functions can be character functions, numeric functions, date functions, and conversion functions. Note that these functions are used to manipulate data items. These functions require one or more input arguments and operate on each row, thereby returning one output value for each row. Argument can be a column, literal or an expression. Single row functions can be used in SELECT statement, WHERE and ORDER BY clause. Single row functions can be –

• General functions – Usually contains NULL handling functions. The functions under the category are NVL, NVL2, NULLIF, COALESCE, CASE, DECODE.

• Case Conversion functions – Accepts character input and returns a character value. Functions under the category are UPPER, LOWER and INITCAP.

  • UPPER function converts a string to upper case.

  • LOWER function converts a string to lower case.

  • INITCAP function converts only the initial alphabets of a string to upper case.

• Character functions – Accepts character input and returns number or character value. Functions under the category are CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM and REPLACE.

  • CONCAT function concatenates two string values.

  • LENGTH function returns the length of the input string.

  • SUBSTR function returns a portion of a string from a given start point to an end point.

  • INSTR function returns numeric position of a character or a string in a given string.

  • LPAD and RPAD functions pad the given string upto a specific length with a given character.

  • TRIM function trims the string input from the start or end.

  • REPLACE function replaces characters from the input string with a given character.

• Date functions – Date arithmetic operations return date or numeric values. Functions under the category are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND and TRUNC.

  • MONTHS_BETWEEN function returns the count of months between the two dates.

  • ADD_MONTHS function add ”n” number of months to an input date.

  • NEXT_DAY function returns the next day of the date specified.

  • LAST_DAY function returns last day of the month of the input date.

  • ROUND and TRUNC functions are used to round and truncates the date value.

• Number functions – Accepts numeric input and returns numeric values. Functions under the category are ROUND, TRUNC, and MOD.

  • ROUND and TRUNC functions are used to round and truncate the number value.

  • MOD is used to return the remainder of the division operation between two numbers.

Illustrations

General functions

The SELECT query below demonstrates the use of NVL function.

SELECT first_name, last_name, salary, NVL (commission_pct,0)
FROM employees
WHERE rownum < 5;

FIRST_NAME           LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)
-------------------- ------------------------- ---------- ---------------------
Steven               King                           24000                     0
Neena                Kochhar                        17000                     0
Lex                  De Haan                        17000                     0
Alexander            Hunold                          9000                     0

Case Conversion functions

The SELECT query below demonstrates the use of case conversion functions.

SELECT UPPER (first_name), INITCAP (last_name), LOWER (job_id)
FROM employees
WHERE rownum < 5;

UPPER(FIRST_NAME)    INITCAP(LAST_NAME)        LOWER(JOB_
-------------------- ------------------------- ----------
STEVEN               King                      ad_pres
NEENA                Kochhar                   ad_vp
LEX                  De Haan                   ad_vp
ALEXANDER            Hunold                    it_prog

Character functions

The SELECT query below demonstrates the use of CONCAT function to concatenate two string values.

SELECT CONCAT (first_name, last_name)
FROM employees
WHERE rownum < 5;

CONCAT(FIRST_NAME,LAST_NAME)
--------------------------------
EllenAbel
SundarAnde
MozheAtkinson
DavidAustin

The SELECT query below demonstrates the use of SUBSTR and INSTR functions. SUBSTR function returns the portion of input string from 1st position to 5th position. INSTR function returns the numeric position of character ”a” in the first name.

SELECT SUBSTR (first_name,1,5), INSTR (first_name,''a'')
FROM employees
WHERE rownum < 5;

SUBST INSTR(FIRST_NAME,''A'')
----- ---------------------
Ellen                     0
Sunda                     5
Mozhe                     0
David                     2

The SELECT query below demonstrates the usage of LPAD and RPAD to pretty print the employee and job information.

SELECT RPAD(first_name,10,''_'')||LPAD (job_id,15,''_'')
FROM employees
WHERE rownum < 5;

RPAD(FIRST_NAME,10,''_'')||
-------------------------
Steven____________AD_PRES
Neena_______________AD_VP
Lex_________________AD_VP
Alexander_________IT_PROG

Number functions

The SELECT query below demonstrates the use of ROUND and TRUNC functions.

SELECT ROUND (1372.472,1)
FROM dual;

ROUND(1372.472,1)
-----------------
           1372.5

SELECT TRUNC (72183,-2)
FROM dual;

TRUNC(72183,-2)
---------------
          72100

Date arithmetic operations

The SELECT query below shows a date arithmetic function where difference of employee hire date and sysdate is done.

SELECT employee_id, (sysdate - hire_date) Employment_days
FROM employees
WHERE rownum < 5;

EMPLOYEE_ID EMPLOYMENT_DAYS
----------- ---------------
        100      3698.61877
        101      2871.61877
        102      4583.61877
        103      2767.61877
        

Date functions

The SELECT query below demonstrates the use of MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY and LAST_DAY functions.

SELECT employee_id, MONTHS_BETWEEN (sysdate, hire_date) Employment_months
FROM employees
WHERE rownum < 5;

EMPLOYEE_ID EMPLOYMENT_MONTHS
----------- -----------------
        100        121.504216
        101        94.3751837
        102        150.633248
        103        90.9558289

SELECT ADD_MONTHS (sysdate, 5), NEXT_DAY (sysdate), LAST_DAY (sysdate)
FROM dual;

ADD_MONTH NEXT_DAY( LAST_DAY(
--------- --------- ---------
01-JAN-14 05-AUG-13 31-AUG-13