SQL Certificate Study Material

SQL Certificate Questions Bank

SQL Certificate Mock Exams

SQL Certificate - Mock Exams

SQL Certificate Useful Resources

Selected Reading

SQL - Conversion Functions

Conversion Functions Questions

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

SELECT ROUND(144.23,-1) FROM dual;

140

144

150

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?

An error because the ROUND function cannot be used with Date arguments.

An error because the WHERE condition expression is invapd.

Number of days since the employee was hired based on the current San Diego date and time.

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?

SELECT first_name, last_name FROM employees WHERE INSTR(first_name, s ) <> 0 AND SUBSTR(last_name,2,1) =  t ;

SELECT first_name, last_name FROM employees WHERE INSTR(first_name, s ) <>    AND SUBSTR(last_name,2,1) =  t ;

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

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?

COUNT (*) counts duppcate values and NULL values in columns of any data type.

COUNT function cannot work with DATE datatypes.

COUNT (DISTINCT job_id) returns the number of rows excluding rows containing duppcates and NULL values in the job_id column.

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 duppcate 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?

NOT NULL

INSTR

SUBSTR

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;

100

999

NULL

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?

They accept only a single argument.

They can be nested only to two levels.

Arguments can only be column values or constants.

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?

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

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

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

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;

78945*****

**78945.45

The function RPAD cannot be nested with other functions

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?

NVL

NVLIF

NVL2

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?

Character

Numeric

Conversion

None of the above

Answer: D. The types of single-row functions pke 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?

SELECT

WHERE

ORDER BY

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?

The syntax of NVL is NVL (exp1, exp2) where exp1 and exp2 are expressions.

NVL (exp1, exp2) will return the value of exp2 if the expression exp1 is NULL.

NVL (exp1, exp2) will return the value of the expression exp2 if exp1 is NOT NULL.

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;

It will throw an ORA error on execution.

It will pst the job IDs for all employees from EMPLOYEES table.

It will pst the job IDs of all employees and substitute NULL job IDs with a pteral Unknown .

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;

NULL

Gives an error because NULL cannot be exppcitly 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 pke  P%  ORDER BY first_name;

It will display 0 in the salary column for all the employees whose first name starts with a P

It will display the salaries for the employees whose name start with a P and 0 if the salaries are NULL.

It will throw an ORA error as the ORDER BY clause should also contain the salary column.

The NVL function should be correctly used as NVL (0, salary)

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

SELECT NVL (arg1, arg2) FROM dual;

The two expressions arg1 and arg2 should only be in VARCHAR2 or NUMBER data type format.

The arguments arg1 and arg2 should have the same data type

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.

An NVL function cannot be used with arguments of DATE datatype.

Answer: C. If arg1 is of VARCHAR2 data type, Oracle does imppcit 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;

It will return the value Regular Employee for all the employees who have NULL job IDs

It will return the value New Joinee for all the employees who have NULL job IDs

It will return Regular Employee if the job ID is NULL

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)

Arg2 and Arg3 can have any data type

Arg1 cannot have the LONG data type

Oracle will convert the data type of expr2 according to Arg1

If Arg2 is a NUMBER, then Oracle determines the numeric precedence, imppcitly 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 pke  P%   
ORDER BY first_name;

Salary will be returned if the Commission for the employee is NOT NULL.

Commission_pct will be returned if the Commission for the employee is NOT NULL.

Employees with the first name starting with P and salary+(salary*commission_pct) will be returned if the employee earns a commission.

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?

NULLIF(expr1,expr2) will return expr2 if the two expressions are NOT NULL.

NULLIF(expr1,expr2) will return 0 if the two expressions are NULL.

NULLIF(expr1,expr2) will return NULL if the two expressions are equal.

Expr1 can be NULL in NULLIF(expr1, expr2)

Answer: C. The NULLIF function tests two terms for equapty. 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)

Arg1 and Arg2 can be of different data types.

Arg1 and Arg2 have to be equal in order to be used in the NULLIF function.

There is no internal conversion of data types if NULLIF used as in the case of NVL and NVL2.

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?

It will display the old job ID when the new job ID is NULL.

It will execute successfully and produce the required output.

It will display the new job ID if the new job ID is equal to the old job ID

It will throw an ORA error on execution.

Answer: B.

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

Perform calculations on data

Convert column data types

Modify inspanidual data items

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?

They return no value

They return one result per row and operate on all the rows of a table.

They return one result per row with input arguments

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?

Multiple-row functions

Single column functions

Single value functions

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?

VARCHAR2

Character

LONG

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?

They return multiple values per each row.

They return one result per group of rows and can manipulate groups of rows.

They return one result per row and can manipulate groups of rows.

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?

Single row functions

Multi group functions

Multiple row functions

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?

They can be nested

They accept arguments and return more than one value.

They cannot modify a data type

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?

Only 1

Only 2

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?

Data types

SELECT statements

Expression

Table name

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

33. What is true about Character functions?

They return only character values

They accept NUMBER values

They accept character arguments and can return both character and number values

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?

They return both Character as well as Number values

They can t accept expressions as input

Number functions can t be nested.

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?

TO_DATE

SYSDATE

MONTHS_BETWEEN

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?

TO_CHAR

TO_DATE

NVL

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?

CONCAT

SUBSTR

INITCAP

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;

Hi World !!!

Hi WORLD !!!

hi world !!!

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;

Hello World

HELLO world

hello World

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;

SELECT first_name||last_name FROM employees;

SELECT first_name|| || last_name FROM employees;

SELECT last_name|| , ||first_name FROM employees;

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;

The job id for ABEL is a sa_rep

The job id forABEL is a sa_rep

The job id for abel is SA_REP

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 ;

It will display the details of the employee with the last name as Smith

It will give no result.

It will give the details for the employee having the last name as Smith in all Lower case.

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?

It can have only characters as input.

It can have only 2 input parameters.

It can have 2 or more input parameters

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?

It extracts a string of determined length

It shows the length of a string as a numeric value

It finds the numeric position of a named character

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;

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?

They give the same results when operated on a string.

LENGTH gives the position of a particular character in a string

INSTR gives the position of a particular character in a string while LENGTH gives the length of the string.

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;

It results in an error as substitution variables cannot be used with single row functions

It prompts the user to input the jobid on each execution and then displays the job id in UPPER case

It gives the jobid as it is present in the table EMPLOYEES without making any change

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?

It is owned by the user SYS and can be access by all the users.

It contains only one column and one row.

The value in the DUMMY column of the DUAL table is X

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;

The query produces error.

No of hours to a date with date as the result.

Sysdate arithmetic is ignored.

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;

100

100+100

ORA error

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;

20-may-2013

ORA error as LOWER and UPPER cannot accept date values.

20-MAY-13

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;

24/6

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?

SELECT INITCAP (last_name||  works as a  ||job_id "Job Description" FROM employees WHERE initcap (last_name) pke  A% ;

SELECT INITCAP (last_name) ||INITCAP(  works as a:  )|| INITCAP(job_id) "Job Description" FROM employees WHERE initcap (last_name) pke  A 
% ;

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

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;

Today is : 20-feb-13

The query throws error of incompatible type arguments.

Today is : 20-Feb-13

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;

First_namelast_namejob_id

First_name, last_name, job_id

Error as CONCAT cannot be nested

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?

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

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

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

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;

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?

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

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

SELECT SUBSTR(country_name, 1,5) FROM countries WHERE length (country_name)< 5;

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?

It apgns the string to the left hand side of a column

It returns a string padded with a specified number of characters to the right of the source string

It apgns character strings to the left and number strings to right of a column

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?

The character strings used for padding include only characters.

The character strings used for padding include only pterals

The character strings used for padding cannot include expressions.

The character strings used for padding include pterals, characters and expressions.

Answer: D.

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

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;

*******1300.66

1300*******

1300.66

****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?

It is similar to SUBSTR function in Oracle

It removes characters from the beginning or end of character pterals, columns or expression

TRIM function cannot be appped on expressions and NUMBERS

TRIM function can remove characters only from both the sides of a string.

Answer: B. The TRIM function pterally trims off leading or traipng (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?

SELECT LTRIM(Title, " ) FROM MAGAZINE;

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

SELECT LTRIM (Title, "THE ) FROM MAGAZINE;

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;

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;

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;

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?

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

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

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

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?

SELECT SUBSTR(first_name, 2) FROM employees;

SELECT SUBSTR(first_name, -2) FROM employees;

SELECT RTRIM(first_name, 2) FROM employees;

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;

N-13

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?

LTRIM

TRIM

TRUNC

REPLACE

Answer: D.

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

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

999

9998

99988

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?

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

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) < 15;

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;

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;

148.50

140

150

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;

10-JUN-13

1-JUN-13

ORA error as the TRUNC function can t have an input parameter when used with dates.

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;

2000

1000

1901

1901.00

Answer: B.

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

Mod(spanisor,spanidend)

MOD(spanisor,1)

MOD(spanidend,spanisor)

None of the above

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

78. What will be outcome of the following query?

SELECT mod(100.23,-3) FROM dual;

ORA error

1.23

100

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

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

ROUND

TRUNC

MOD

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?

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

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;

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

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 Calpng ,1) FROM dual;

ORA error as there should be minimum 3 arguments to the SUBSTR function.

Life is Calpng

NULL

Life

Answer: B. Calpng 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?

DD-MON-YY

DD-MON-RR

DD/MON/RR

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;

09-MAY-2013 12:05pm

10-MAY-2013 12:05pm

10-JUL-2013 12:05pm

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;

02-JAN-2013

Friday

04-JAN-2013

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?

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?

NULL

Answer: C. If the two digits of the current year and the specified year pe 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?

NULL

Answer: A. If the two digits of the current year pe 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?

NULL

Answer: A. if the two digits of the current and specified years pe 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?

NULL

Answer: C. if the two digits of the current year pe 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?

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

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

It returns only the system date

It takes 2 parameters at least.

The default format is DD-MON-YY

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"

Date

Num1

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"

Date

Num1

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?

The number of days between them

Difference in dates in not possible in Oracle DB

A date

NULL

Answer: A.

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

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

ORA error

A positive number

A negative number

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?

It represents the difference in number between the start date and end date.

The result cannot be a fractional number, it has to be a whole number.

NULL

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?

SELECT sysdate FROM dual;

SELECT round(sysdate) FROM dual;

SELECT trunc (sysdate) FROM dual;

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;

Approximately 0

The query will throw an ORA error

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 earper 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;

31-feb-2009

28-feb-2009

31-mar-2009

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 vapd 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?

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

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

SELECT last_day ( 01-nov-2012  ) FROM dual;

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.

Conversion Functions Questions

友情链接