SQL - Restricting and Sorting Data Questions

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

SELECT ROUND(144.23,-1) FROM dual;

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 invalid.
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 duplicate 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 duplicates 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 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?

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

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 list the job IDs for all employees from EMPLOYEES table.
It will list the job IDs of all employees and substitute NULL job IDs with a literal '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
1
0
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;

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

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

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

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

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

Perform calculations on data
Convert column data types
Modify individual 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 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?

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?

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

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

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;

4
24
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) like 'A%';

SELECT INITCAP (last_name) ||INITCAP(' works as a: ')|| INITCAP(job_id) "Job Description" FROM employees WHERE initcap (last_name) like '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)

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 aligns 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 aligns 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 literals
The character strings used for padding cannot include expressions.
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?

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 literals, columns or expression
TRIM function cannot be applied on expressions and NUMBERS
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?

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

3
N-13
0
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)

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(divisor,dividend)
MOD(divisor,1)
MOD(dividend,divisor)
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;

ORA error
1.23
100
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?

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

ORA error as there should be minimum 3 arguments to the SUBSTR function.
Life is Calling
NULL
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?

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?

19
21
20
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?

19
21
20
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?

19
21
20
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?

19
21
20
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?

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

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

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

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.

SQL - Using Single-Row Functions Questions