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.

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

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;

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.

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

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 

Displaying Data from Multiple Tables

The related tables of a large database are linked through the use of foreign and primary keys or what are often referred to as common columns. The ability to join tables will enable you to add more meaning to the result table that is produced. For ”n” number tables to be joined in a query, minimum (n-1) join conditions are necessary. Based on the join conditions, Oracle combines the matching pair of rows and displays the one which satisfies the join condition.

Joins are classified as below

• Natural join (also known as an equijoin or a simple join) – Creates a join by using a commonly named and defined column.

• Non-equality join – Joins tables when there are no equivalent rows in the tables to be joined-for example, to match values in one column of a table with a range of values in another table.

• Self-join – Joins a table to itself.

• Outer join – Includes records of a table in output when there”s no matching record in the other table.

• Cartesian join (also known as a Cartesian product or cross join) – Replicates each row from the first table with every row from the second table.Creates a join between tables by displaying every possible record combination.

Natural Join

The NATURAL keyword can simplify the syntax of an equijoin.A NATURAL JOIN is possible whenever two (or more) tables have columns with the same name,and the columns are join compatible, i.e., the columns have a shared domain of values.The join operation joins rows from the tables that have equal column values for the same named columns.

Consider the one-to-many relationship between the DEPARTMENTS and EMPLOYEES tables.Each table has a column named DEPARTMENT_ID.This column is the primary key of the DEPARTMENTS table and a foreign key of the EMPLOYEES table.

SELECT E.first_name NAME,D.department_name DNAME
FROM employees E NATURAL JOIN departments D;

FIRST_NAME DNAME
---------- ------
MILLER     DEPT 1
JOHN       DEPT 1
MARTIN     DEPT 2
EDWIN      DEPT 2

The below SELECT query joins the two tables by explicitly specifying the join condition with the ON keyword.

SELECT E.first_name NAME,D.department_name DNAME
FROM employees E JOIN departments D
ON (E.department_id = D.department_id);

There are some limitations regarding the NATURAL JOIN.You cannot specify a LOB column with a NATURAL JOIN.Also, columns involved in the join cannot be qualified by a table name or alias.

USING Clause

Using Natural joins, Oracle implicitly identify columns to form the basis of join. Many situations require explicit declaration of join conditions. In such cases, we use USING clause to specify the joining criteria. Since, USING clause joins the tables based on equality of columns, it is also known as Equijoin. They are also known as Inner joins or simple joins.

Syntax:

SELECT <column list>
FROM   TABLE1   JOIN   TABLE2
USING (column name)

Consider the below SELECT query, EMPLOYEES table and DEPARTMENTS table are joined using the common column DEPARTMENT_ID.

SELECT E.first_name NAME,D.department_name DNAME
FROM employees E JOIN departments D
USING (department_id);

Self Join

A SELF-JOIN operation produces a result table when the relationship of interest exists among rows that are stored within a single table. In other words, when a table is joined to itself, the join is known as Self Join.

Consider EMPLOYEES table,which contains employee and their reporting managers.To find manager”s name for an employee would require a join on the EMP table itself. This is a typical candidate for Self Join.

SELECT e1.FirstName Manager,e2.FirstName Employee
FROM employees e1 JOIN employees e2
ON (e1.employee_id = e2.manager_id)
ORDER BY e2.manager_id DESC;

Non Equijoins

A non-equality join is used when the related columns can”t be joined with an equal sign-meaning there are no equivalent rows in the tables to be joined.A non-equality join enables you to store a range”s minimum value in one column of a record and the maximum value in another column. So instead of finding a column-tocolumn match, you can use a non-equality join to determine whether the item being shipped falls between minimum and maximum ranges in the columns.If the join does find a matching range for the item, the corresponding shipping fee can be returned in the results. As with the traditional method of equality joins, a non-equality join can be performed in a WHERE clause. In addition, the JOIN keyword can be used with the ON clause to specify relevant columns for the join.

SELECT E.first_name,
            J.job_hisal,
            J.job_losal,
            E.salary
     FROM employees E JOIN job_sal J
     ON (E.salary BETWEEN J.job_losal AND J.job_losal);

We can make use all comparison parameter discussed earlier like equality and inequality operators, BETWEEN, IS NULL, IS NOT NULL, and RELATIONAL.

Outer Joins

An Outer Join is used to identify situations where rows in one table do not match rows in a second table, even though the two tables are related.

There are three types of outer joins: the LEFT, RIGHT, and FULL OUTER JOIN. They all begin with an INNER JOIN, and then they add back some of the rows that have been dropped. A LEFT OUTER JOIN adds back all the rows that are dropped from the first (left) table in the join condition, and output columns from the second (right) table are set to NULL. A RIGHT OUTER JOIN adds back all the rows that are dropped from the second (right) table in the join condition, and output columns from the first (left) table are set to NULL. The FULL OUTER JOIN adds back all the rows that are dropped from both the tables.

Right Outer Join

A RIGHT OUTER JOIN adds back all the rows that are dropped from the second (right) table in the join condition, and output columns from the first (left) table are set to NULL. Note the below query lists the employees and their corresponding departments. Also no employee has been assigned to department 30.

SELECT E.first_name, E.salary, D.department_id
FROM employees E, departments D
WHERE E.DEPARTMENT_ID (+) = D.DEPARTMENT_ID;

FIRST_NAME SALARY     DEPARTMENT_ID
---------- ---------- ----------
JOHN       6000       10
EDWIN      2000       20
MILLER     2500       10
MARTIN     4000       20
                      30

Left Outer Join

A LEFT OUTER JOIN adds back all the rows that are dropped from the first (left) table in the join condition, and output columns from the second (right) table are set to NULL. The query demonstrated above can be used to demonstrate left outer join, by exchanging the position of (+) sign.

SELECT E.first_name, E.salary, D.department_id
FROM employees E, departments D
WHERE   D.DEPARTMENT_ID = E.DEPARTMENT_ID (+);

FIRST_NAME SALARY     DEPARTMENT_ID
---------- ---------- ----------
JOHN       6000       10
EDWIN      2000       20
MILLER     2500       10
MARTIN     4000       20
                      30

Full Outer Join

The FULL OUTER JOIN adds back all the rows that are dropped from both the tables. Below query shows lists the employees and their departments. Note that employee ”MAN” has not been assigned any department till now (it”s NULL) and department 30 is not assigned to any employee.

SELECT  nvl (e.first_name,''-'') first_name, nvl (to_char (d.department_id),''-'') department_id
FROM employee e FULL OUTER JOIN department d
ON e. depARTMENT_ID = d. depARTMENT_ID;

FIRST_NAME DEPARTMENT_ID
---------- --------------------
MAN        -
JOHN       10
EDWIN      20
MILLER     10
MARTIN     20
-          30

6 rows selected.

Cartesian product or Cross join

For two entities A and B, A * B is known as Cartesian product. A Cartesian product consists of all possible combinations of the rows from each of the tables. Therefore, when a table with 10 rows is joined with a table with 20 rows, the Cartesian product is 200 rows (10 * 20 = 200).For example, joining the employee table with eight rows and the department table with three rows will produce a Cartesian product table of 24 rows (8 * 3 = 24).

Cross join refers to the Cartesian product of two tables. It produces cross product of two tables. The above query can be written using CROSS JOIN clause.

A Cartesian product result table is normally not very useful. In fact, such a result table can be terribly misleading. If you execute the below query for the EMPLOYEES and DEPARTMENTS tables, the result table implies that every employee has a relationship with every department, and we know that this is simply not the case!

SELECT E.first_name, D.DNAME
FROM employees E,departments D;

Cross join can be written as,

SELECT E.first_name, D.DNAME
FROM employees E CROSS JOIN departments D;

(Oracle Certified Oracle Database 11g Administrator)

Oracle Database 11g: SQL Fundamentals certification exams has a defined Syllabus to check your expertise. As per Oracle Official website, following is the list of chapters and covered topic to be examined in the certification exams.

Retrieving Data Using the SQL SELECT Statement

• List the capabilities of SQL SELECT statements

• Execute a basic SELECT statement

Restricting and Sorting Data

• Limit the rows that are retrieved by a query

• Sort the rows that are retrieved by a query

• Use ampersand substitution to restrict and sort output at runtime

Using Single-Row Functions to Customize Output

• Describe various types of functions available in SQL

• Use character, number, and date functions in SELECT statements

Using Conversion Functions and Conditional Expressions

• Describe various types of functions available in SQL

• Use character, number, and date functions in SELECT statements

Reporting Aggregated Data Using the Group Functions

• Identify the available group functions

• Describe the use of group functions

• Group data by using the GROUP BY clause

• Include or exclude grouped rows by using the HAVING clause

Displaying Data from Multiple Tables

• Write SELECT statements to access data from more than one table using equijoins and nonequijoins

• Join a table to itself by using a self-join

• View data that generally does not meet a join condition by using outer joins

• Generate a Cartesian product of all rows from two or more tables

Using Subqueries to Solve Queries

• Define subqueries

• Describe the types of problems that the subqueries can solve

• List the types of subqueries

• Write single-row and multiple-row subqueries

Using the Set Operators

• Describe set operators

• Use a set operator to combine multiple queries into a single query

• Control the order of rows returned

Manipulating Data

• Describe each data manipulation language (DML) statement

• Insert rows into a table

• Update rows in a table

• Delete rows from a table

• Control transactions

Using DDL Statements to Create and Manage Tables

• Categorize the main database objects

• Review the table structure

• List the data types that are available for columns

• Create a simple table

• Explain how constraints are created at the time of table creation

• Describe how schema objects work

Creating Other Schema Objects

• Create simple and complex views

• Retrieve data from views

• Create, maintain, and use sequences

• Create and maintain indexes

• Create private and public synonyms

General Functions

General functions are used to handle NULL values in database. The objective of the general NULL handling functions is to replace the NULL values with an alternate value. We shall briefly see through these functions below.

NVL

The NVL function substitutes an alternate value for a NULL value.

Syntax:

NVL( Arg1, replace_with )

In the syntax, both the parameters are mandatory. Note that NVL function works with all types of data types. And also that the data type of original string and the replacement must be in compatible state i.e. either same or implicitly convertible by Oracle.

If arg1 is a character value, then oracle converts replacement string to the data type compatible with arg1 before comparing them and returns VARCHAR2 in the character set of expr1. If arg1 is numeric, then Oracle determines the argument with highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

The SELECT statement below will display ”n/a” if an employee has been not assigned to any job yet i.e. JOB_ID is NULL. Otherwise, it would display the actual JOB_ID value.

SELECT  first_name, NVL(JOB_ID, ''n/a'')
FROM employees;

NVL2

As an enhancement over NVL, Oracle introduced a function to substitute value not only for NULL columns values but also for NOT NULL columns. NVL2 function can be used to substitute an alternate value for NULL as well as non NULL value.

Syntax:

NVL2( string1, value_if_NOT_null, value_if_null )

The SELECT statement below would display ”Bench” if the JOB_CODE for an employee is NULL. For a definite not null value of JOB CODE, it would show constant value ”Job Assigned”.

SQL> SELECT NVL2(JOB_CODE, ''Job Assigned'', ''Bench'')
FROM employees;

NULLIF

The NULLIF function compares two arguments expr1 and expr2. If expr1 and expr2 are equal, it returns NULL; else, it returns expr1. Unlike the other null handling function, first argument can”t be NULL.

Syntax:

NULLIF (expr1, expr2)

Note that first argument can be an expression that evaluates to NULL, but it can”t be the literal NULL. Both the parameters are mandatory for the function to execute.

The below query returns NULL since both the input values, 12 are equal.

SELECT	NULLIF (12, 12)
FROM DUAL;

Similarly, below query return ”SUN” since both the strings are not equal.

SELECT NULLIF (''SUN'', ''MOON'')
FROM DUAL;

COALESCE

COALESCE function, a more generic form of NVL, returns the first non-null expression in the argument list. It takes minimum two mandatory parameters but maximum arguments has no limit.

Syntax:

COALESCE (expr1, expr2, ... expr_n )

Consider the below SELECT query. It selects the first not null value fed into address fields for an employee.

SELECT COALESCE (address1, address2, address3) Address
FROM  employees;

Interestingly, the working of COALESCE function is similar to IF..ELSIF..ENDIF construct. The query above can be re-written as –

IF address1 is not null THEN
   result := address1;
ELSIF address2 is not null THEN
   result := address2;
ELSIF address3 is not null THEN
   result := address3;
ELSE
   result := null;
END IF;

Conditional Functions

Oracle provides conditional functions DECODE and CASE to impose conditions even in SQL statement.

The DECODE function

The function is the SQL equivalence of IF..THEN..ELSE conditional procedural statement. DECODE works with values/columns/expressions of all data types.

Syntax:

DECODE (expression, search, result [, search, result]... [, default])

DECODE function compares expression against each search value in order. If equality exists between expression and search argument, then it returns the corresponding result. In case of no match, default value is returned, if defined, else NULL. In case of any type compatibility mismatch, oracle internally does possible implicit conversion to return the results.

As a matter of fact, Oracle considers two nulls to be equivalent while working with DECODE function.

SELECT DECODE(NULL,NULL,''EQUAL'',''NOT EQUAL'')
FROM DUAL;

DECOD
-----
EQUAL

If expression is null, then Oracle returns the result of the first search that is also null. The maximum number of components in the DECODE function is 255.

SELECT	first_name, salary, DECODE (hire_date, sysdate,''NEW JOINEE'',''EMPLOYEE'')
	FROM employees;

CASE expression

CASE expressions works on the same concept as DECODE but differs in syntax and usage.

Syntax:

CASE  [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END

Oracle search starts from left and moves rightwards until it finds a true condition, and then returns result expression associated with it. If no condition is found to be true, and an ELSE clause exists, then Oracle returns result defined with else. Otherwise, Oracle returns null.

The maximum number of arguments in a CASE expression is 255. All expressions count toward this limit, including the initial expression of a simple CASE expression and the optional ELSE expression. Each WHEN … THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the return_expr itself is a CASE expression.

SELECT first_name, CASE	WHEN salary < 200 THEN ''GRADE 1''
			WHEN salary > 200 AND salary < 5000 THEN ''GRADE 2''
			ELSE ''GRADE 3''
		   END CASE
FROM employees;

ENAM    CASE
----    -------
JOHN    GRADE 2
EDWIN   GRADE 3
KING    GRADE 1