Frequently Asked Common SQL interview Questions:
51) What is difference between blob and clob in oracle?
The main difference between BLOB and CLOB in Oracle are :
* BLOB stores values as LOB (Large Object) in bitstreams
* CLOB Stores values as LOB (Large Object) in character streams.
52) How you will avoid duplicating records in a query?
By using DISTINCT
53) What are various Oracle Operator?
Different operators:
Unary operators:- Operates on one operand.
Binary:- Operates on two operands.
Arithmetic operators:- Used to perform basic arithmetic operations like addition, subtraction etc. they are listed below:
• + – – Denotes positive or negative expression. They are unary operators.
• */- Denotes multiplication or division. They are binary operators.
• ||- Concatenates strings
• =, !=. <, >, <=, >= are all comparison operators.
• OR, AND, NOT are all logical operators
Example: sql>SELECT * FROM emp WHERE job = ‘CLERK’ AND deptno = 10;
SQL interview preparation
54) What is IN operator?
IN operator in a query allows you to have multiple values in a WHERE clause.
Example: to return records of employees staying in Pune and Mumbai.
sql> select *from dept where loc in(‘NEW YORK’,’CHICAGO’);
54) What is Like operator?
LIKE in oracle enables the user to search for a string of the matching type. “%” is used as a wild card in the query.
Example: The query below will display results of employee names starting with A
Sql> SELECT ename FROM emp WHERE ename LIKE ‘A%’;
55) Define Is Null operator.
IS NULL operator is usually used to check if a columns value is NULL or not.
Example: Sql> select *from emp where comm is not null;
56) What are the comparison operators in oracle. List them with description for SQL interview questions and answers?
Comparison operators in oracle are used to compare data. Such operators are used in WHERE clause.
List of such operators:
1)= -> EQUAL TO
2)<> != -> Both means NOT EQUAL TO
3)< LESS THAN
4)> GREATER THAN
5)<= >= -> LESS THAN OR EQUAL TO, GREATER THAN OR EQUAL TO
6)ANY- compares one value with any value
Example:
sql>Select * from employee Where sal >=2000;
57) What is the Result of the following ‘VIK’||NULL||’RAM’ ?
a] Error
b] VIK RAM
c] VIKRAM
d] NULL
Ans : C
58) What are the wildcards used for pattern matching.
The “LIKE” operator performs the pattern matching in SQL.
for single character substitution and % for multi-character substitution.
59) SQL>select * from EMP where ename like ‘A%’;
State true or false.
!=,
<>,
^=
all denote the same operation. Answer: True
60) What operator tests column for absence of data?
IS NULL operator.
61) Operators used in SELECT statements.
= Equal
<> or != Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
62) Display the records between two range?
Sql>select * from emp where sal between 1000 and 3000.
63) Which command displays the SQL command in the SQL buffer, and then executes it?
RUN.
64) What is difference between SUBSTR and INSTR asked in SQL technical interview questions?
SUBSTR returns a specified portion of a string eg SUBSTR(‘BCDEF’,4) output BCDE INSTR provides character position in which a pattern is found in a string. eg INSTR(‘ABC-DC-F’,’-‘,2) output 7 (2nd occurence of ‘-‘)
65) When do you use WHERE clause and when do you use HAVING clause in SQL interview questions for beginners?
HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause. The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.
66) How do you find the number of rows in a Table ?
A bad answer is count them (SELECT COUNT(*) FROM table_name)
A good answer is :- ‘By generating SQL to ANALYZE TABLE table_name COUNT STATISTICS by querying Oracle System Catalogues (e.g. USER_TABLES or ALL_TABLES).
The best answer is to refer to the utility which Oracle released which makes it unnecessary to do ANALYZE TABLE for each Table individually.
67) How many LONG columns are allowed in a table ? Is it possible to use LONG columns in WHERE clause or ORDER BY ?
Only one LONG columns is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.
68) What is NVL() function?
Oracle deals with Null values using NVL function. The function replaces the NULL value in the given column with the value provide to it. The function accepts two parameters, the first one is the column name and the second one is the value with which NULL has to be replaced.
Example :Sql> select nvl(comm,0) from emp;
69) Describe the use of following oracle numeric functions with an example.?
NVL
ABS,CEIL,FLOOR, MOD
SQRT
EXP,LN AND LOG
ROUND AND TRUNC
AVG, COUNT, MAX, MIN AND SUM
STDDEV AND VARIANCE
DISTINCT AND LIST function
70) What are conversion functions?
CHARTOROWID , Converts a string to a ROWID,CONVERT, Converts a string from one character set to another, HEXTORAW , Converts from hexadecimal to raw format, RAWTOHEX ,Converts from raw value to hexadecimal ,ROWIDTOCHAR ,Converts a binary ROWID value to a character string, TO_CHAR ,Converts a number or date to a string.
TO_DATE ,Converts a string to a date ,TO_NUMBER ,Converts a string to a number.
71) What is nested function?
When one function is called inside the other, it is called a nested function.
72) What are SQL functions in oracle?
There are two types of functions –
Single row that operates row by row. Group function operates on multiple rows.
Functions that fall under single functions are Date, Numeric, Character, Conversion and miscellaneous function
Functions that fall under group functions are avg, max, min, count, and sum.
73) Explain the use of Translate Function in Oracle?
The translate function performs a character wise replacement of a string. The syntax is as follows:
translate( input_string , string1 , string2 )
string1: this is the string which will be looked for in the input string
string2: this is the string that will replace the string1 characters in the input string.
Example: Sql>select translate(‘HELLO123′,’L21′,’Ixy’) from dual;
Output: TRANSLAT
——–
HEIIOyx3
73) What is translate and decode in oracle?
Translate: translate function replaces a sequence of characters in a string with another set of characters. The replacement is done single character at a time.
Syntax: translate( string1, string_to_replace, replacement_string )
Example: translate (‘1tech23’, ‘123’, ‘456);
Decode: The DECODE function compares one expression to one or more other expressions and, when the base expression is equal to a search expression, it returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified, or NA when it is not.
Syntax: DECODE (expr , search, result [, search , result]… [, default])
Example: sql>SELECT employee_name, decode(employee_id, 10000, ‘tom’, 10001, ‘peter’, 10002, ‘jack’ ‘Gateway’) result FROM employee;
74) What are Group Functions in Oracle?
COUNT(): This function returns the number of rows in the group. A column name or ‘*’ may be passed as a parameter.
MIN(column_name): This ffunction returns the minimum value of the expression evaluated on each row of the group.
MAX(column_name): It returns the maximum value of the expression evaluated on each row of the group.
AVG(column_name): It returns the average value of the expression evaluated on each row of the group.
75) Describe the use of following oracle string functions with an example.?
Concatenation(||)
RPAD and LPAD
Ltrim, Rtrim and Trim
LOWER, UPPER, and INITCAP
LENGTH
SUBSTR
INSTR
ASCII AND CHR
SOUNDEX
76) what is COALESCE function questions asked during SQL interview questions for experienced?
– This function is used to return the value which is set not to be null in the list.
– Incase all values in the list are null the coalesce function will return NULL.
– Its representation:
Coalesce(value1, value2, value3,…)
77) Differentiate between: TRANSLATE and REPLACE.
- Translate is used to substitute character by character.
- Replace is used to substitute a single character with a word.
78) Explain how to sort the rows that are retrieved by a query.?
Rows that are returned by a query can be sorted using the ORDER BY clause. By default the rows are sorted in an ascending order.
Example: sql>Select * from emp ORDER BY sal DESC;
79) Explain how to sort the rows that are retrieved by a query.?
Rows that are returned by a query can be sorted using the ORDER BY clause. By default the rows are sorted in an ascending order.
Example: sql>Select * from emp ORDER BY sal DESC;
80) Describe the use of following oracle date functions with an example.
Sysdate
Current_date
SYSTIME STAMP
ROUND and TRUNC in date calculation
TO_DATE and TO_CHAR formatting
1. Sysdate : Sydate in Oracle /PLSQL is used to return the current date and time of the system in which the database is configured.
Example: Returns the System date and employee from the table
Select SYSDATE, id from employee Where emp_id >100;
2. Current_date :Current_date in Oracle /PLSQL is used to return the current date of the time zone of the existing or running SQL session.
Example: Select current_date from employee Will return: 16-JAN-2010 10:14:33
3. SYSTIMESTAMP : SYSTIMESTAMP in Oracle /PLSQL is used to return the current system (on which the database is configured) date and time which includes fractions of seconds and time zone.
Example: Select SYSTIMESTAMP from employee Will return: 16-JAN-10 12.38.55.538741 PM -08:00
4. ROUND and TRUNC in date calculation :
ROUND in Oracle /PLSQL is used to return the next rounded value of a number. The number of decimal places to be rounded is determined by a parameter.
Example:
ROUND(120.411) will return 120
ROUND(120.411, 1) will return 120.4
Select ROUND(salary_amt,2) from employee
TRUNC in Oracle /PLSQL is used to scrap or truncate the number of digits specifed. The number of digits to be truncated is determined by a parameter.
Example:
TRUNC(120.411, 1) will return 120.41
Select TRUNC(salary_amt,2) from employee
5. TO_DATE and TO_CHAR formatting
TO_DATE function in Oracle /PLSQL is used to convert a given string in DATE format.
Example:
to_date(‘2010/07/09’, ‘yyyy/mm/dd’) would return a date value of Jan 10, 2010.
TO_CHAR function in Oracle /PLSQL is used to convert a given number (DATE or number) to string.
Example:
to_char(sysdate, ‘FMMonth DD, YYYY’); would return ‘Jan 10, 2010′
Here, FM parameter suppresses the blank spaces and zeros.
81) SQL single-row character munipulation functions?
SQL single-row character munipulation functions
CONCAT(string1, string2)
INITCAP(string)
LENGTH(string)
LPAD(string, #,padding_char)
RPAD(string, #,padding_char)
LTRIM(string,searchString)
RTRIM(string,searchString)
REPLACE(string,searchString,replacement)
SUBSTR(string,start,length)
UPPER(string)
LOWER(string)
82) What is the default format of date in Oracle? How can I change my default date format?
The default format for date in oracle is DD-MON-YY.
We can change it by using this SQL commnd
ALTER SESSION set nls_date_format=’desired format’;
Desired format is like Day-Mon-yyyy 24hh:mi:ss
83) What is the output of the following query
SELECT TRUNC(1234.5678,-2) FROM DUAL;
Sql> Answers: 1200.
‘trunc’ is not a recognized built-in function name.so use select round(1234.5678,-2) from ‘tablename’. this funtion work well.
84) What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;
This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary. where ever the commission is null, it is replaced by 0 & added to the salary
85) Which date function is used to find the difference between two dates?
we have to use months_between date function which returns float value.
Sql>select months_between(’07-jun-2007′,’02-jun-2006′) from dual;
To get result in integer use round function with it.
sql>select round(months_between(’07-jun-2007′,’02-jun-2006′)) from dual;
86) What is difference between DBMS and RDBMS frequently asked in SQL interview questions for freshers?
RDBMS have Normalization. Normalization means to refining the redundant and maintain the stablization.
the DBMS hasn’t normalization concept.
1.RDBMS=DBMS+ Referential Integrity
2. An RDBMS is a one that follows 12 rules of CODD.
RDBMS we can make it as a server, it connect more systems but DBMS only woke in host system, we can’t make DBMS as global..
87) What are two methods of retrieving SQL?
1-select
2-using cursor
88) Which function is used to find the largest integer less than or equal to a specific value?
Syntax FLOOR (n)
Purpose Returns largest integer equal to or less than n.
Example : sql>SELECT FLOOR(15.7) “Floor” FROM DUAL;
Returns the following result.
Floor
———
15
89) How to get short name & long name of the month or month of given date
select to_char(inputdate,’mon’) as shortname, to_char(inputdate,’month’) as longname from (select to_date(&sa_dt,’dd-mon-yy’) inputdate from dual);
90) What is difference between date and timestamp in oracle
The main differences between DATE and TIMESTAMP in Oracle are:
- Date Stores value as century,year,month,date,hour,minute and second.
- TIMESTAMP stores values as year ,month, day, hour, minute and fractional seconds.
91) How to convert numbers to character in oracle?
You can convert numeric values to characters by using the TO_CHARO function as shown in the following examples:
SELECT TO_CHAR(4123.4570) FROM DUAL 123.457
SELECT TO_ $4,123.46 SELECT TO_ -4.12E+03
CHAR(4123.457, ‘$9,999,999.99) FROM DUAL
CHAR(-4123.457, 9999999.99EEEE) FROM DUAL
91)how convert characters to numbers in oracle?
You can convert characters to numbers by using the TO_NUMBERO function as shown in the following examples:
SELECT TO_NUMBER(4123.4570) FROM DUAL 4123.457
SELECT TO_NUMBER(‘ $4,123.46159,999,999.99) FROM DUAL 4123.46
SELECT TO_NUMBER(‘ -4.12E+03) FROM DUAL -4120
92) How to convert dates to characters in oracle?
You can convert dates to characters using the TO_CHARO function as shown in the following examples:
SELECT TO_CHAR(SYSDATE, DD-MON-YYYY) FROM DUAL;
— SYSDATE returns the current date 07-MAY-2006
SELECT TO_CHAR(SYSDATE, ‘YYYY/MM/DD) FROM DUAL; 2006/05/07 SELECT TO_CHAR(SYSDATE, ‘MONTH DD, YYYY) FROM DUAL;
MAY 07, 2006
SELECT TO_CHAR(SYSDATE, %MONTH DD, YYYY) FROM DUAL;
May 7, 2006
SELECT TO_CHAR(SYSDATE, %DAY, MONTH DD, YYYY) FROM DUAL;
SUNDAY, MAY 7, 2006
93) How to convert Characters to Dates in oracle?
You ‘view all Answersles to characters using the TO_DATEO function as shown in the following exam
SELECT TO_DATE(07-MAY-2006′, DD-MON-YYYY) FROM DUAL;
07-MAY-06
SELECT TO_DATE(2006/05/07 ‘YYYY/MM/DD) FROM DUAL;
07-MAY-06
SELECT TO_DATE(MAY 07, 2006′, ‘MONTH DD, YYYY) FROM DUAL;
07-MAY-06
SELECT TO_DATE(May 7, 2006′, %MONTH DD, YYYY) FROM DUAL;
07-MAY-06
SELECT TO_DATE(‘SUNDAY, MAY 7, 2006’, ‘fmDAY, MONTH DD, YYYY) FROM DUAL; 07-MAY-06
94)How to convert times to characters in oracle?
You can convert dates to characters using the TO_CHARO function as shown in the following examples: SELECT TO CHAR(SYSDATE, ‘HH:MI:SS) FROM DUAL;
04:49:49
SELECT TO CHAR(SYSDATE, ‘HH24:MI:SS.FF) FROM DUAL;
96) How to convert characters to times in oracle?
You can convert dates to characters using the TO_CHAR() function as shown in the following examples:
SELECT TO CHAR(TO_DATE(04:49:49′, ‘HH:MI:SS),
‘DD-MON-YYYY HH24:MI:SS) FROM DUAL;
— Default date is the first day of the current month 01-MAY-2006 04:49:49
SELECT TO CHAR(TO_TIMESTAMP(16:52:57.847000000′, ‘HH24:MI:SS.FF9), DD-MON-YYYY HH24:MI:SS.FF9)
FROM DUAL;
01-MAY-2006 16:52:57.847000000
SELECT TO CHAR(TO_DATE(69520′, ‘SSSSS), ‘DD-MON-YYYY HH24:MI:SS) FROM DUAL; 01-MAY-2006 19:18:40
97) How to use null as conditions in oracle?
If you want to compare values against NULL as conditions, you should use the “IS NULL” or “IS NOT NULL” operator. Do not use “=” or “<>” against NULL. The sample script below shows you some good examples:
SELECT ‘A’ IS NULL FROM DUAL;
–Error: Boolean is not data type.
–Boolean can only be used as conditions
SELECT CASE WHEN ‘A’ IS NULL THEN TRUE’ ELSE ‘FALSE’ END
FROM DUAL;
FALSE
SELECT CASE WHEN “IS NULL THEN TRUE’ ELSE ‘FALSE’ END FROM DUAL;
TRUE
SELECT CASE WHEN O IS NULL THEN TRUE’ ELSE ‘FALSE’ END FROM DUAL;
FALSE
SELECT CASE WHEN NULL IS NULL THEN TRUE’ ELSE ‘FALSE’ END FROM DUAL;
TRUE
SELECT CASE WHEN ‘A’ = NULL THEN TRUE’ ELSE ‘FALSE’ END FROM DUAL;
-Do not use “=”
FALSE
SELECT CASE WHEN ‘A’ <> NULL THEN TRUE’ ELSE ‘FALSE’ END
FROM DUAL;
-Do not use “<>”
FALSE
SELECT CASE WHEN NULL = NULL THEN TRUE’ ELSE ‘FALSE’ END
FROM DUAL;
—
Do not use “=” FALSE
98) How to concatenate two text values in oracle?
There are two ways to concatenate two text values together:
- CONCAT() function.
- ‘||’ operation.
Here is some examples on how to use them:
SELECT ‘ggl’ || ‘Center’ || !.com’ FROM DUAL;
globalguideline.com
SELECT CONCAT(‘globalguideline’,’.com) FROM DUAL; globalguideline.com
99) How to increment dates by 1 in oracle?
If you have a date, and you want to increment it by 1. You can do this by adding the date with a date interval. You can also do this by adding the number 1 directly on the date. The tutorial example below shows you how to adding numbers to dates, and take date differences:
SELECT TO_DATE(30-APR-06) + 1 FROM DUAL;
— Adding 1 day to a date
01-MAY-06
SELECT TO_DATE(01-MAY-06) – TO_DATE(30-APR-06) FROM DUAL;
–Taking date differences
1
SELECT SYSTIMESTAMP + 1 FROM DUAL;
The number you add is always in days.
08-MAY-06
SELECT TO CHAR(SYSTIMESTAMP+1,DD-MON-YYYY HH24:MI:SS. FF3′) FROM DUAL;
Error: Adding 1 to a timestamp makes it a date.
100) How to calculate date and time differences in oracle frequently asked in SQL interview questions for data analysts?
If you want to know how many years, months, days and seconds are there between two dates or times, you can use the date and time interval expressions: YEAR… TO MONTH and DAY… TO SECOND. The tutorial exercise below gives you some good examples:
SELECT
(TO_DATE(01-MAY-2006 16:52:57′,’DD-MON-YYYY HH24:MI:SS)
TO_DATE(31-JAN-1897 09:26:50′,’DD-MON-YYYY HH24:MI:SS’)) YEAR(4) TO MONTH FROM DUAL;
- 109 years and 3 months
109-3
SELECT
(TO_DATE(01-MAY-2006 16:52:57′,’DD-MON-YYYY HH24:MI:SS)
TO_DATE(31-JAN-1897 09:26:50′,’DD-MON-YYYY HH24:MI:SS’)) DAY(9) TO SECOND FROM DUAL;
— 39901 days and some seconds 39901 7:26:7.0
SELECT
(TO_TIMESTAMP(01-MAY-2006 16:52:57.847′, ‘DD-MON-YYYY HH24:MI:SS. FF3′) – TO_TIMESTAMP(31-JAN-1897 09:26:50.124’,
‘DD-MON-YYYY HH24:MI:SS.FF3))
YEAR(4) TO MONTH FROM DUAL; — 109 years and 3 months
109-3
SELECT
(TO_TIMESTAMP(01-MAY-2006 16:52:57.847′, ‘DD-MON-YYYY HH24:MI:SS. FF3) –
TO_TIMESTAMP(31-JAN-1897 09:26:50.124′, ‘DD-MON-YYYY HH24:MI:SS. FF3’))
DAY(9) TO SECOND
FROM DUAL;
— 39
101) How to use in condition in oracle frequently asked in common SQL interview questions?
An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE.
Some examples are given in the script below:
SELECT CASE WHEN 3 IN (1,2,3,5) THEN
TRUE’ ELSE ‘FALSE’ END FROM DUAL; TRUE
SELECT CASE WHEN 3 NOT IN (1,2,3,5) THEN TRUE’ ELSE ‘FALSE’ END FROM DUAL; FALSE
SELECT CASE WHEN ‘Y’ IN (‘F’,’Y’,”) THEN TRUE’ ELSE ‘FALSE’ END FROM DUAL; TRUE
102) How to use like condition in oracle?
LIKE condition is also called pattern patch. There 3 main rules on using LIKE condition:
+
is used in the pattern to match any one character.
%’ is used in the pattern to match any zero or more characters.
ESCAPE clause is used to provide the escape character in the pattern.
The following script provides you some good pattern matching examples:
SELECT CASE WHEN ‘globalguideline.com’ LIKE % Center %’ THEN TRUE’ ELSE ‘FALSE’ END FROM DUAL;
TRUE
SELECT CASE WHEN ‘globalguideline.com’ LIKE ‘%CENTER%’ THEN TRUE’ ELSE ‘FALSE’ END FROM DUAL;
Case sensitive by default
FALSE
SELECT CASE WHEN ‘globalguideline.com’ LIKE %Center_com’ THEN TRUE’ ELSE ‘FALSE’ END FROM DUAL;
TRUE
SELECT CASE WHEN ‘100% correct’ LIKE ‘100% %’ ESCAPE ” THEN TRUE’ ELSE ‘FALSE’ END FROM DUAL;
TRUE
103) How to use regular expression in pattern match conditions in oracle common SQL practice questions?
If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern patch function: REGEXP_LIKE().
The following script provides you some good examples:
SELECT CASE WHEN REGEXP_LIKE (‘globalguideline.com’, ‘ggl.,
1) THEN TRUE’ ELSE ‘FALSE’ END FROM DUAL;
TRUE
SELECT CASE WHEN REGEXP_LIKE (‘globalguideline.com’, ‘*com$’, i) THEN TRUE’ ELSE ‘FALSE’ END FROM DUAL;
TRUE
SELECT CASE WHEN REGEXP_LIKE (‘globalguideline.com’, “F.*’,”) THEN TRUE’ ELSE ‘FALSE’ END FROM DUAL;
TRUE
104) Can the query output be sorted by multiple columns in oracle?
You can specifying multiple columns in the ORDER BY clause as shown in the following example statement, which returns employees’ salaries sorted by department and salary value: Sql> select deptno, ename, sal from emp order by deptno,sal;
105) How to sort output in descending order in oracle?
If you want to sort a column in descending order, you can specify the DESC keyword in the ORDER BY clause. The following SELECT statement first sorts the department in descending order, then sorts the salary in ascending order:
Sql> select deptno,ename,sal from emp order by deptno desc ,sal
106) How to use group functions in the select clause using oracle ?
If group functions are used in the SELECT clause, they will be used on the rows that meet the query selection criteria,
the output of group functions will be returned as output of the query.
Sql>select count(deptno),min(deptno),max(deptno) from emp;
107 ) Can group functions be mixed with non group selection fields?
If a group function is used in the SELECT clause, all other selection fields must be group level fields. Non-group fields can not be mixed with group fields in the SELECT clause. The script below gives you an example of invalid SELECT statements with group and non-gorup selection fields:
Sql> select count(*),deptno from emp;
Error: ORA-00937: not a single-group group function
In this example, COUNT(*) is a group field and department_id is a non-group field.
108) How to apply filtering criteria at group level in oracle?
If you want to return only specific groups from the query, you can apply filtering criteria at the group level by using the
HAVING clause inside the GROUP BY clause. The following script gives you a good HAVING example:
Sql> select deptno,min(sal),max(sal) ,avg(sal) from emp group by deptno having avg(sal) 2000;
109) Can group functions be used in the order by clause in oracle popular SQL interview questions in the interview?
Answer: Sql> select deptno,sal,count(*) from emp group by deptno,sal having count(*)>1
order by count(*) desc;
FINALLY HAPPY FACE
Please click for Part 1
