Friday, December 30, 2011
Oracle Hierarchical queries
It was really confusing when I was going through the documentation of Oracle Hierarchical queries for the first time. I red it many times to understand it well, practiced many times to save it in my memory and put it down in my own words so that it stays there for a long time in the way I understood it. And I hope you too can understand it well.
When we talk about hierarchical queries, then it is very well clear that we are dealing with hierarchical data. Hope you know what a hierarchical data refers to. It can be a parent child relation, boss to employee relation, manager to reportee etc. So whatever such data we have in a table we need to select the data suitable to our convenience.
Understanding Hierarchical query clause:
When we are trying to fetch data from our hierarchical table, it is very obvious that we need to know two things.
- From which level of hierarchy we need to start? That is we need a starting point.
- What kind of relationship are we looking in this hierarchy? That is we need a connection between the data.
START WITH condition CONNECT BY condition
Hence START WITH specifies the root row of the hierarchy and CONNECT BY specifies the relationship between the parent rows and child rows of the hierarchy.
Initially while I was going through the examples of hierarchical query the operation PRIOR was so confusing for me and I was not able to understand this code well enough. Then I learned in a different method and now it become easy for me. Let me try to explain it in the below example.
--Create one table to hold our hierarchical data
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 30 17:52:02 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table hierarchy_test
2 (
3 emp_id number(10),
4 l_name varchar2(10),
5 m_name varchar2(10),
6 f_name varchar2(10),
7 manager_id number(10),
8 constraint hierarchy_test_pk primary key (emp_id)
9 );
Table created.
SQL>
--Now insert the hierarchical data into this table
SQL> INSERT INTO hierarchy_test VALUES (100,'King','','','');
1 row created.
SQL> INSERT INTO hierarchy_test VALUES (101,'Scott','','','100');
1 row created.
SQL> INSERT INTO hierarchy_test VALUES (102,'King','','',101);
1 row created.
SQL> INSERT INTO hierarchy_test VALUES (103,'King','','',101);
1 row created.
SQL> INSERT INTO hierarchy_test VALUES (104,'King','','',101);
1 row created.
SQL> INSERT INTO hierarchy_test VALUES (105,'King','','',103);
1 row created.
SQL> INSERT INTO hierarchy_test VALUES (106,'King','','',103);
--Example for hierarchical query
SQL> select emp_id, manager_id, level
2 from hierarchy_test
3 start with emp_id=100
4 connect by PRIOR emp_id = manager_id
5 order by level;
EMP_ID MANAGER_ID LEVEL
---------- ---------- ----------
100 1
101 100 2
104 101 3
102 101 3
103 101 3
106 103 4
105 103 4
7 rows selected.
You can use PRIOR keyword on either side of the operator '='. But I got confused why? What difference does it make. Also I was not able to understand why this keyword is used here.
Let me read this clause as below so that I make sure that I understood it well.
connect by PRIOR emp_id = manager_id
as
connect by previous emp_id = manager_id of the current row.
So, here in the example, first row emp_id=100, manager_id=null.
Now to get the current row get all the records for which the manager_id is equal to the employee id of the previous record. Now you go back and look into the result, you will be clear on why we use PRIOR in our code.
You can rewrite the above code as below with same result, as per my above sentence.
SQL> select emp_id, manager_id, level
2 from hierarchy_test
3 start with emp_id=100
4 connect by manager_id =PRIOR emp_id
5 order by level;
EMP_ID MANAGER_ID LEVEL
---------- ---------- ----------
100 1
101 100 2
104 101 3
102 101 3
103 101 3
106 103 4
105 103 4
7 rows selected.
SQL>
What if you give the PRIOR keyword just opposite as below for our data?
SQL> select emp_id, manager_id, level
2 from hierarchy_test
3 start with emp_id=100
4 connect by emp_id = PRIOR manager_id
5 order by level;
EMP_ID MANAGER_ID LEVEL
---------- ---------- ----------
100 1
SQL>
We get only one result set right?
Here what happens is we started with emp_id 100 and for the next row we said that, get me the row for which the employee id is the manager id of the previous record!!
Ooops!!
Kind don't have a manager and hence we have that column value as null. Hence oracle doesn't find any way to proceed further. Hence we got only one record.
Hope you got my point.
Now, what if I am not using the start by keyword. Your code will work but result is bad and not as expected.
SQL> select emp_id, manager_id, level
2 from hierarchy_test
3 connect by PRIOR emp_id = manager_id
EMP_ID MANAGER_ID LEVEL
---------- ---------- ----------
104 101 1
106 103 1
101 100 1
103 101 1
105 103 1
100 1
102 101 1
106 103 2
105 103 2
104 101 2
103 101 2
EMP_ID MANAGER_ID LEVEL
---------- ---------- ----------
102 101 2
101 100 2
104 101 3
102 101 3
103 101 3
105 103 3
106 103 3
106 103 4
105 103 4
20 rows selected.
SQL>
Level 4 considers only 105 and 106 whereas level 3 consider 105,106, 102, 103 and 104 and it goes like that till level 1. Hence give the start with keyword with your hierarchical query.
Related Articles:
How do Oracle process Hierarchical queries.
****
Hope this is useful. Thanks Phoenix.
Tweet
Wednesday, December 28, 2011
UNIX interview questions
Q: What is the difference between vi and emacs editor?
Ans: vi is a modaleditor whereas emacs is not. That is in vi editor you have edit mode and command mode whereas in emacs only one mode. For more details visit vi editor.
Q: UNIX is case sensitive. To avoid this issue with case sensitive what option you can use while searching for a pattern?
Ans:
grep 'Phoenix' *.txt may give you output, whereas grep 'PHOENIX' *.txt may not.
To avoid this case sensitivity use the option 'i'
grep -i 'PHOENIX' *.txt
Q: What is meant by a filter in UNIX?
A filter is a program which can receive a flow of data from std input, process (or filter) it and send the result
to the std output.
Q: What is the significance of tee command?
Ans:
A powerful command that reads the standard input and sends it to standard output while redirecting a copy to a file specified. It is just re-route the pipline with tee.
Q: What is the significance of awk command?
Ans:
Helps you grab specific columns of information, modify text as it follow past, and swap the order of column information in a file.
Helps in analyzing and manipulating text files. Another alternative for this is the sed command which is less powerful compared to awk command.
Q: What does the command “ $who | sort –logfile > newfile” do?
Ans: This example explains the tricky use of hyphen (-). Above code gives the output from who command as input for sort command, meanwhile sort command will open the file called logfile and the content of this file is sorted togehter with the output of who and moving that to the file called newfile.
Q: What does the command “$ls | wc –l > file1” do?
Ans: Here ls is the input for the command wc and takes the count of line from ls and the count of lines is stored into file1 instead of displaying in the monitor.
Q: Which of the following commands is not a filter man , (b) cat , (c) pg , (d) head
Ans:
to the std output.Q: What is the difference between the redirection operators > and >>?
Ans:
When redirection operator > overwrite the content of file >> operator appends to file.
Q: Explain the steps that a shell follows while processing a command.
Ans:
Below are the steps followed by shell
Parsing: Shell first breaks up the command line into words, using spaces and delimiters, unless quoted. All consecutive spaces or tabs are replaces with single space.
Variable Evaluation: All words preceded by a $ are evaluated as variables, unless quoted or escaped.
Command Substitution:Any commands surrounded by backquotes is executed by the shell which then replaces the standard output of the command into the command line.
Wild Card Interpretation: The shell finally scans the command line for the wildcard characters (*,.,?,[,]). Any word containing a wild card is replaced by a sorted list of fienames that match the pattern. The list of this filenames then become the argument to the command.
PATH Evaluation:
It finally looks for the PATH variable to determine the sequence of directories it has to search in order to hunt for the command.
Q: What difference between cmp and diff commands?
Ans:
Q: Which command is used to delete all files in current directory and all its sub-directories?
Ans:
rm -r *
c - character, b - binary (octal), d-decimal, od=Octal Dump.
Q: What will the following command do/
$echo *
Ans;
It is similar to ls command, list down all the files in the current directory.
Q: Is it possible to create a new file system in UNIX?
Ans: Yes, 'mkfs' is used to create a new file system.
Q: Is it possible to restrict the incoming message?
Ans: Yes, using the 'mesg' command
Q:What is the use of the command "ls -x chapter[1-5]"?
wc -l for counting lines in a file.
Q. How does the kernel differentiate device files and ordinary files?
Kernel checks 'type' field in the file's inode structure.
***
Hope this is helpful. Thanks Phoenix.
Ans: vi is a modaleditor whereas emacs is not. That is in vi editor you have edit mode and command mode whereas in emacs only one mode. For more details visit vi editor.
Q: UNIX is case sensitive. To avoid this issue with case sensitive what option you can use while searching for a pattern?
Ans:
grep 'Phoenix' *.txt may give you output, whereas grep 'PHOENIX' *.txt may not.
To avoid this case sensitivity use the option 'i'
grep -i 'PHOENIX' *.txt
Q: What is meant by a filter in UNIX?
A filter is a program which can receive a flow of data from std input, process (or filter) it and send the result
to the std output.
Q: What is the significance of tee command?
Ans:
A powerful command that reads the standard input and sends it to standard output while redirecting a copy to a file specified. It is just re-route the pipline with tee.
Q: What is the significance of awk command?
Ans:
Helps you grab specific columns of information, modify text as it follow past, and swap the order of column information in a file.
Helps in analyzing and manipulating text files. Another alternative for this is the sed command which is less powerful compared to awk command.
Q: What does the command “ $who | sort –logfile > newfile” do?
Ans: This example explains the tricky use of hyphen (-). Above code gives the output from who command as input for sort command, meanwhile sort command will open the file called logfile and the content of this file is sorted togehter with the output of who and moving that to the file called newfile.
Q: What does the command “$ls | wc –l > file1” do?
Ans: Here ls is the input for the command wc and takes the count of line from ls and the count of lines is stored into file1 instead of displaying in the monitor.
Q: Which of the following commands is not a filter man , (b) cat , (c) pg , (d) head
Ans:
Ans: man
A filter is a program which can receive a flow of data from std input, process (or filter) it and send the resultto the std output.Q: What is the difference between the redirection operators > and >>?
Ans:
When redirection operator > overwrite the content of file >> operator appends to file.
Q: Explain the steps that a shell follows while processing a command.
Ans:
Below are the steps followed by shell
Parsing: Shell first breaks up the command line into words, using spaces and delimiters, unless quoted. All consecutive spaces or tabs are replaces with single space.
Variable Evaluation: All words preceded by a $ are evaluated as variables, unless quoted or escaped.
Command Substitution:Any commands surrounded by backquotes is executed by the shell which then replaces the standard output of the command into the command line.
Wild Card Interpretation: The shell finally scans the command line for the wildcard characters (*,.,?,[,]). Any word containing a wild card is replaced by a sorted list of fienames that match the pattern. The list of this filenames then become the argument to the command.
PATH Evaluation:
It finally looks for the PATH variable to determine the sequence of directories it has to search in order to hunt for the command.
Q: What difference between cmp and diff commands?
Ans:
cmp - Compares two files byte by byte and displays the first mismatch
diff - tells the changes to be made to make the files identicalQ: What is the difference between cat and more command?
Ans:
Cat displays file contents. If the file is large the contents scroll off the screen before we view it. So command more is like a pager which displays the contents page by page.
Q: Write a command to kill the last background job?
Ans:
Kill $!
Q: Which command is used to delete all files in current directory and all its sub-directories?
Ans:
rm -r *
Q: Write a command to display a file’s contents in various formats?
Ans:$od -cbd file_namec - character, b - binary (octal), d-decimal, od=Octal Dump.
Q: What will the following command do/
$echo *
Ans;
It is similar to ls command, list down all the files in the current directory.
Q: Is it possible to create a new file system in UNIX?
Ans: Yes, 'mkfs' is used to create a new file system.
Q: Is it possible to restrict the incoming message?
Ans: Yes, using the 'mesg' command
Q:What is the use of the command "ls -x chapter[1-5]"?
ls stands for list; so it displays the list of the files that starts with 'chapter' with suffix '1' to '5', chapter1, chapter2, and so on.
Q: Is ‘du’ a command? If so, what is its use?
Ans:
Yes, it stands for ‘disk usage’. With the help of this command you can find the disk capacity and free space
of the disk.Q: Is it possible to count number char, line in a file; if so, How?
Ans:
Yes, wc-stands for word count.
wc -c for counting number of characters in a file.wc -l for counting lines in a file.
Q: Name the data structure used to maintain file identification?
Ans:
inode, each file has a separate inode and a unique inode number.
Q: How many prompts are available in a UNIX system?
Ans:
Two prompts, PS1 (Primary Prompt), PS2 (Secondary Prompt).
Kernel checks 'type' field in the file's inode structure.
***
Hope this is helpful. Thanks Phoenix.
Thursday, December 22, 2011
Oracle sql statement processing steps.
Whenever a SQL statement is submitted to execute, Oracle goes through the below mentioned steps
Hope this is useful. Thanks Phoenix
- Check for identical statements in memory to avoid performance overhead due to parsing.
- Allocate memory in shared memory area.
- Evaluate the syntax of the statement to check whether all the oracle key words are spell correctly.
- Do semantic check, where all the objects (tables or columns) are validated and check for the user's privilege to access these objects.
- Form an execution plan to execute the statement.
Hope this is useful. Thanks Phoenix
Wednesday, December 21, 2011
Oracle Autonomous Transaction
Let me try to explain Oracle Autonomous Transaction in a little different way so that you can remember it very easily.
How many different types of plsql blocks can you write?
Ooops!!
Procedures and Functions are the pls/ql blocks written uniquely with or without parameters. What else could I do with this? What are all these different types available? Let us try it out.
1. We use to write a Procedure/Function without giving it a name for adhock purpose as we don't want to store that in the database to use it later. - ANONYMOUS PROC/FUNC
2. We use to use this anonymous items in declaration section of plsql as well - LOCAL PROC/FUNC
3. When we need this proc/func to be stored in database to use frequently, we store it with a name tagged to it- STAND ALONE PROC/FUNC or say stored Procedure or Function.
4. To logically group our procs/funcs we can place these procedures or functions inside a package - PACKAGED PROC/FUNC
So now we know there are 4 different ways we can use procedures and functions.
We can use all the above mentioned different ways of proc/fncs for autonomous transactions.
Autonomous Transactions?????
I had heard about Autonomous Institutes we're they function independently.
PL/SQL autonomous transaction also work exactly the same. They function independently. It helps you leave the calling transaction and performs an individual transaction and resume to the calling transaction without affecting the calling transaction.
Confused? No need to worry. You will get it by the time you complete this.
Here calling transaction refers to your main PL/SQL block and the autonomous transaction is called from this block. The purpose of your main PL/SQL block and autonomous PL/SQL block is entirely different. Both behave as transaction done is seperate sessions. There is no link between both these type of transactions, hence only committed data can be shared among them.
Autonomous transaction code block is nothing but plsql blocks. Syntax is as below,
Example for anonymous:
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
Statement;
COMMIT;
END;
/
Example for stored PROC:
CREATE OR REPLACE PROCEDURE PROCEDURE_NAME AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
statement;
COMMIT;
END;
/
We have 5 different type of plsql blocks which can be used for this purpose. Four of them are those discussed above and the fifth one is "TYPE methods".
Let us try to understand autonomous transaction a little more deep by going through few scenarios.
1. You need to insert two records into an empty table.
INSERT INTO temp_table (id, name) VALUES (1, 'Scott');
INSERT INTO temp_table (id, name) VALUES (2, 'Peter');
Next without committing this transaction you want to execute an autonomous transaction, say an individual transaction.
Before executing the autonomous transaction, select count(*) give you result: 2
SELECT count(*) FROM temp_table;
Execute autonomous transaction which inserts 5 records to the same table and have a commit statement within this autonomous transaction code block.
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR i IN 3 .. 5 LOOP
INSERT INTO temp_table (id, name)
VALUES (i, 'Name ' || i);
END LOOP;
COMMIT;
END;
/
Now select count(*) give you result as expected:7
Now execute a rollback.
Rollback;
Select count(*) gives you only 5 as the result.
The records inserted in current transaction got rolled back. The commit affected only the autonomous transaction block.
Hope now, you are clear with how to user autonomous transaction.
Now the question is why should we use autonomous transaction?
It is most popularly used for error logging. In applications particularly like banking, you can't commit a transaction until it is successfully completed. Hence to capture any error that may happen in between any transaction we use autonomous transaction.
How can we use this?
Create a proc as autonomous which logs data into one table. Call this proc in the exceptional section of your plsql block before you rollback.The error data is committed successfully by the autonomous transaction and your calling block actions are rolled back
Restrict the use of autonomous transaction only for error logging, else it will be a disaster.
Hope you understood well. Regards Shiyas..
Tuesday, December 13, 2011
How to retrieve accidentally overwritten content in my blog
Necessity is the father of invention!!
Today I feel, I tried something out of the box for a solution I need and here I share it with my readers.
I worked hard on my third blog BUS#356 V, edited and edited, red many times and just before posting in social networks called up one of my close friend to review. Upon receiving nice feedback, I posted and was doing my final touch up. I was working on my friend's brand new Apple laptop the functionality of which is awesome but sophisticated as well. I ended up in deleting the content of my blog without any backup.
My friends started calling me up to tell me that my blog is blank. I go broke on realizing that all my work is gone within a second. I was getting more hits into my site and everyone could see only blank page. Only option I have is to write it again but still it cant be in its original form as I have spent too much time on that.
I started googling for any options to retrieve my overwritten data. I can find only for deleted one, but not for overwritten files in blog site. Then I started looking into the history. But that too goes in vain. The browsing history has all the urls, but as it says "All Roads Lead To Venice", all the history urls upon clicking on that will take me to my blogsite where I can see only blank content which was latest update saved in the web.
I sat for some time not knowing what to do. I start setting up my mind for writing the entire thing once again, then work and rework on it. I made up my mind.
Suddenly the thought came to mind, what if I hit the history URL which I havent touched and dont allow the system to get the data from web. Will I get the page which I visited?
Didnt waited for a second thought, I pulled out the network cable hastly. Searched for the list of history. Selected the one which I havent touched after this data loss and hit.
I got my data which need only slight changes which I have done after my friends review.
In future, if this can be useful to atleast one, I will be happy.
Please don't forget to leave me a comment, if you are reading this for a solution.
Today I feel, I tried something out of the box for a solution I need and here I share it with my readers.
I worked hard on my third blog BUS#356 V, edited and edited, red many times and just before posting in social networks called up one of my close friend to review. Upon receiving nice feedback, I posted and was doing my final touch up. I was working on my friend's brand new Apple laptop the functionality of which is awesome but sophisticated as well. I ended up in deleting the content of my blog without any backup.
My friends started calling me up to tell me that my blog is blank. I go broke on realizing that all my work is gone within a second. I was getting more hits into my site and everyone could see only blank page. Only option I have is to write it again but still it cant be in its original form as I have spent too much time on that.
I started googling for any options to retrieve my overwritten data. I can find only for deleted one, but not for overwritten files in blog site. Then I started looking into the history. But that too goes in vain. The browsing history has all the urls, but as it says "All Roads Lead To Venice", all the history urls upon clicking on that will take me to my blogsite where I can see only blank content which was latest update saved in the web.
I sat for some time not knowing what to do. I start setting up my mind for writing the entire thing once again, then work and rework on it. I made up my mind.
Suddenly the thought came to mind, what if I hit the history URL which I havent touched and dont allow the system to get the data from web. Will I get the page which I visited?
Didnt waited for a second thought, I pulled out the network cable hastly. Searched for the list of history. Selected the one which I havent touched after this data loss and hit.
I got my data which need only slight changes which I have done after my friends review.
In future, if this can be useful to atleast one, I will be happy.
Please don't forget to leave me a comment, if you are reading this for a solution.
Friday, December 9, 2011
Oracle Interview Questions Part 1: SQL
Q: What is the difference between delete and truncate?
Ans:
1. Delete can be rolled back before commiting the transaction whereas a truncate cannot be rolled back
2. Delete is a DML whereas Truncate is DDL
3. Truncate deletes the entire data at once whereas delete does it row by row.
4. Truncate is faster and performance benefit when compated to delete, if the requirement is to flush the table completely.
Q: What is the syntax of DECODE function and what is the use?
Ans: It has the same functionality IF-THEN-ELSE statement.
syntax: decode(expression,search[,result,search,result..][,default])
Example:
select decode (department, 1, 'Finance',2,'Accounts', 'Miscellaneous') from dual;
Q: What does COALESE function does?
Ans:
COALESCE function goes through the given list of values/expressions and returns the first non null expression. If all the values/expressions are null then it returns null. COALESCE is similar to IF-THEN-ELSE statement.
syntax: coalesce( expr1, expr2, ... expr_n )
SELECT coalesce( firstname, middlename, lastname) result
FROM employees;
Its equivalent IF-THEN-ELSE is as below.
IF firstname is not null
THEN
result:=firstname;
ELSIF middlename is not null
THEN
result:=midddlename;
ELSIF lastname is not null
THEN
result:=lastname
ELSE
result:=null;
end if;
Q: A given column has values with NULL data. On sorting the column in ascending order, where does the null value comes? First or Last?
Ans: Last
Q: What is the default sort order in Oracle?
Ans: ascending
Q: Provided a list of numbers or characters or dates. What function helps you to retrieve the highes value from the list?
Ans: greatest(x,y,..)
and for the least it is least(x,y,..)
Q: The column firstname is of datatype varchar2 which has null values as well. What is the output of below function for a null value?
NVL(firstname,0)
Ans: NVL returns the passed value if the expression is null else returns the erpression.
Here the function fails as the column is of datatype varchar2 and the specified value is of type number. Hence it fails.
For NVL to work, the data type of both column and passing value should be same.
Q: How NVL2 works?
Ans: syntax: NVL2(X,Y,Z)
NVL2 returns Y if X is not null, else returns Z
Q: What is the syntax for NULLIF?
Ans: NULLIF(x,y)
function returns NULL if x=y else returns X
Q: Write an SQL query to fetch all the managers from an employee table whose JOB_ID ends with either '_MAN' or '_MGR'
Ans:
SELECT FIRST_NAME, LAST_NAME, JOB_ID
FROM EMPLOYEES
WHERE REGEXP_LIKE(JOB_ID, '(_m[an|gr])', 'i');
[parameter i indicates case-insensitive]
Q: Select every employee whose last name has a double vowel
(two adjacent occurrences of the same vowel).
Ans:
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, '([AEIOU])\1', 'i');
Q: Exmaple for using REGEXP_REPLACE
Ans:
SELECT PHONE_NUMBER "Old Format",
REGEXP_REPLACE(PHONE_NUMBER,
'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
'(\1) \2-\3') "New Format"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;
Old Format New Format
-----------------------------------------------------------------
515.123.4567 (515) 123-4567
515.123.4568 (515) 123-4568
515.123.4569 (515) 123-4569
The search pattern has three regular expressions, each of which is enclosed in parentheses. The metacharacter [[:digit:]] represents a digit, the metacharacter {n} specifies n occurrences, and the metacharacter \ is an escape character. The character immediately after an escape character is interpreted as a literal. Without the escape character, the metacharacter . represents any character. The replace string uses \1, \2, and \3 to represent the first, second, and third regular expressions in the search pattern, respectively. (In the replace string, \ is not an escape character.)
Q: Example to extract Street number including hyphen from the given street address.
Ans:
select street_address address, REGEXP_SUBSTR(street_address,'[[:digit:]-]+') "Number" from locations
Address Number
-----------------------------------------------
2007 Zagora St 2007
2004 Charade Rd 2004
147 Spadina Ave 147
6092 Boxwood St 6092
40-5-12 Laogianggen 40-5-12
Q: Can we use a oracle keyword as alias in any select statement?
select 1 number from dual;
ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
*Cause:
*Action:
Error at Line: 1 Column: 9
solution: Use the keyword in double quotes
select 1 "number" from dual;
Output:
number
---------
1
Q: Example to count number of spaces in a street address
Ans:
Q: What is the result of below query? Why?
select 10000 + null from dual;
Ans: null. In oracle any scalar operation with null always results in null.
Ans:
1. Delete can be rolled back before commiting the transaction whereas a truncate cannot be rolled back
2. Delete is a DML whereas Truncate is DDL
3. Truncate deletes the entire data at once whereas delete does it row by row.
4. Truncate is faster and performance benefit when compated to delete, if the requirement is to flush the table completely.
Q: What is the syntax of DECODE function and what is the use?
Ans: It has the same functionality IF-THEN-ELSE statement.
syntax: decode(expression,search[,result,search,result..][,default])
Example:
select decode (department, 1, 'Finance',2,'Accounts', 'Miscellaneous') from dual;
Q: What does COALESE function does?
Ans:
COALESCE function goes through the given list of values/expressions and returns the first non null expression. If all the values/expressions are null then it returns null. COALESCE is similar to IF-THEN-ELSE statement.
syntax: coalesce( expr1, expr2, ... expr_n )
SELECT coalesce( firstname, middlename, lastname) result
FROM employees;
Its equivalent IF-THEN-ELSE is as below.
IF firstname is not null
THEN
result:=firstname;
ELSIF middlename is not null
THEN
result:=midddlename;
ELSIF lastname is not null
THEN
result:=lastname
ELSE
result:=null;
end if;
Q: A given column has values with NULL data. On sorting the column in ascending order, where does the null value comes? First or Last?
Ans: Last
Q: What is the default sort order in Oracle?
Ans: ascending
Q: Provided a list of numbers or characters or dates. What function helps you to retrieve the highes value from the list?
Ans: greatest(x,y,..)
and for the least it is least(x,y,..)
Q: The column firstname is of datatype varchar2 which has null values as well. What is the output of below function for a null value?
NVL(firstname,0)
Ans: NVL returns the passed value if the expression is null else returns the erpression.
Here the function fails as the column is of datatype varchar2 and the specified value is of type number. Hence it fails.
For NVL to work, the data type of both column and passing value should be same.
Q: How NVL2 works?
Ans: syntax: NVL2(X,Y,Z)
NVL2 returns Y if X is not null, else returns Z
Q: What is the syntax for NULLIF?
Ans: NULLIF(x,y)
function returns NULL if x=y else returns X
Q: Write an SQL query to fetch all the managers from an employee table whose JOB_ID ends with either '_MAN' or '_MGR'
Ans:
SELECT FIRST_NAME, LAST_NAME, JOB_ID
FROM EMPLOYEES
WHERE REGEXP_LIKE(JOB_ID, '(_m[an|gr])', 'i');
[parameter i indicates case-insensitive]
Q: Select every employee whose last name has a double vowel
(two adjacent occurrences of the same vowel).
Ans:
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, '([AEIOU])\1', 'i');
Q: Exmaple for using REGEXP_REPLACE
Ans:
SELECT PHONE_NUMBER "Old Format",
REGEXP_REPLACE(PHONE_NUMBER,
'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
'(\1) \2-\3') "New Format"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;
Old Format New Format
-----------------------------------------------------------------
515.123.4567 (515) 123-4567
515.123.4568 (515) 123-4568
515.123.4569 (515) 123-4569
The search pattern has three regular expressions, each of which is enclosed in parentheses. The metacharacter [[:digit:]] represents a digit, the metacharacter {n} specifies n occurrences, and the metacharacter \ is an escape character. The character immediately after an escape character is interpreted as a literal. Without the escape character, the metacharacter . represents any character. The replace string uses \1, \2, and \3 to represent the first, second, and third regular expressions in the search pattern, respectively. (In the replace string, \ is not an escape character.)
Q: Example to extract Street number including hyphen from the given street address.
Ans:
select street_address address, REGEXP_SUBSTR(street_address,'[[:digit:]-]+') "Number" from locations
Address Number
-----------------------------------------------
2007 Zagora St 2007
2004 Charade Rd 2004
147 Spadina Ave 147
6092 Boxwood St 6092
40-5-12 Laogianggen 40-5-12
Q: Can we use a oracle keyword as alias in any select statement?
select 1 number from dual;
ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
*Cause:
*Action:
Error at Line: 1 Column: 9
solution: Use the keyword in double quotes
select 1 "number" from dual;
Output:
number
---------
1
Q: Example to count number of spaces in a street address
Ans:
Q: What is the result of below query? Why?
select 10000 + null from dual;
Ans: null. In oracle any scalar operation with null always results in null.
Wednesday, December 7, 2011
How to type in French charcters from your keyboard
Search for French Characters in your key board ends here!!
Fortunately or Unfortunately I came across a requirement to type in French characters in my system. I started searching for those odd characters in my key board and the end result was failure. I couldn't find it at all..
Before explaining how to type in French Characters let us have a basic understanding about French alphabets.
French alphabet consist of 26 letters, lowercase and uppercase, with five diacritics and two orthographic ligatures. The latter part, diacritics and ligatures, is what we are inerested about and need to know how we type it in.
Below listed down are the Diacritics and Ligatures:
| Àà, Ââ, Ææ |
| Çç |
| Éé, Èè, Êê, Ëë |
| Îî, Ïï |
| Ôô, Œœ |
| Ùù, Ûû, Üü |
| Ÿÿ |
Getting confused with the special characteres?
Lets us make it much more simple. For this let us look into Diacritics and Ligatures seperately.
Diacritics:
All the special characters that you have seen in the above table on top of each characters is called Diacritic Marks. The possible diacritic marks are the acute(´), grave(`), and cicumflex (^) accents, the diaresis (¨) and the cedilla ( ¸ ).
Let us not go much deep into how these are combined with our alphabets and how it is pronounced.
Ligatures:
There are two ligatures œ and æ which are treated like the sequences oe and ae.
Now, I hope you got a basic idea about the French characters and so moving to check on how to type in those characters from our keyboard.
The idea of typing in French characters works out with alt key and some 3 or 4 digit numbers. What you have to do is to press down the alt key and enter the 3 or 4 digit number and on releasing the alt key, your required French character will appear.
Below are the list of combinations and now its time for you to test it.
| Grave Accent | Accute Accent | Circumflex | Tréma | Ligature | Cedilla | |
|---|---|---|---|---|---|---|
| a | ALT + 133 (à) | NA | ALT + 131 (â) | ALT + 132 (ä) | ALT + 145 (æ) | NA |
| A | ALT + 0192 (À) | NA | ALT + 0194 (Â) | ALT + 142 (Ä) | ALT + 146 (Æ) | NA |
| e | ALT + 138 (è) | ALT + 130 (é) | ALT + 136 (ê) | ALT + 137 (ë) | NA | NA |
| E | ALT + 0200 (È) | ALT + 144 (É) | ALT + 0202 (Ê) | ALT + 0203 (Ë) | NA | NA |
| i | NA | NA | ALT + 140 (î) | ALT + 139 (ï) | NA | NA |
| I | NA | NA | ALT + 0206 (Î) | ALT + 0207 (Ï) | NA | NA |
| o | NA | NA | ALT + 147 (ô) | NA | ALT + 0156 (œ) | NA |
| O | NA | NA | ALT + 0212 (Ô) | NA | ALT + 0140 (Œ) | NA |
| u | ALT + 151 (ù) | NA | ALT + 150 (û) | ALT + 129 (ü) | NA | NA |
| U | ALT + 0217 (Ù) | NA | ALT + 0219 (Û) | ALT + 154 (Ü) | NA | NA |
| C | NA | NA | NA | NA | NA | ALT + 135 (ç) |
| C | NA | NA | NA | NA | NA | ALT + 128 (Ç) |
Found it useful? Please leave a comment.
Monday, December 5, 2011
Replace funtion in Javascript
Replace function description:
The replace function, replace(), looks for a substring in the given string and replaces that with new value provided.
The syntax for using this is:
string.replace(substring,newstring)
Example:
Assume variable string has values assigned as string ="AtoZ on Tech"
Using replace functin on this, provide the substring and newstring to replace
i.e
<script type="text/javascript">
var string="AtoZ on Tech";
string=string.replace("to","2");
document.write(string);
</script>
Output:
A2Z on Tech
Done a slight modification in susbstring. Will this work?
<script type="text/javascript">
var string="AtoZ on Tech";
string=string.replace(/to/,"2");
document.write(string);
</script>
Yes, this works same exactly as the above code.
Will the below code work?
<script type="text/javascript">
var string="AtoZ on Tech";
string=string.replace("To","2");
document.write(string);
</script>
No, as here "T" is in uppercase. So, how can we have a case-insensitive search and replace?
<script type="text/javascript">
var string="AtoZ on Tech";
string=string.replace(/To/i,"2");
document.write(string);
</script>
Output: A2Z on Tech
Now, let me extend our string variable. as below.
<script type="text/javascript">
var string="AtoZ on Tech";
string= string + " is a blogsite discussing all about technology from A to Z";
string=string.replace(/To/i,"2");
document.write(string);
</script>
Output:A2Z on Tech is a blogsite discussing all about technology from A to Z
The second "A to Z" is as it is. Here only the first occurance is replaced. How to replace all the occurences of search string?
Then you should give the option for global represented by "g.
<script type="text/javascript">
var string="AtoZ on Tech";
string= string + " is a blogsite discussing all about technology from A to Z";
string=string.replace(/To/ig,"2");
document.write(string);
</script>
Now you know that we have used "i" for case-insensitive matching and "g" for global matching. These are called modifiers. Modifiers will specify what kind of search we have to do. There is one more modifier used in javascript, ie "m" for multiline matching. This we will be discussin in our later blogs.
Ok. So far is fine. What if you dont have a specific substring and you have only a pattern to be replaced?
Then you have to go for Regular Expression.
What is Regular Expression?
Regular Expression gives a pattern of characters like a-z for chars, 0-9 for int and can have some special character pattern as well.
Using regular expression we search for any pattern defined in the regular expression(RegExp) and replace it with the required string/value.
<script type="text/javascript">
var string="A2Z on Tech";
string=string.replace(/[^a-z,0-9]/ig,"-");
document.write(string);
</script>
The above code looks for a pattern match (any character other than a-z, A-Z and number 0-9) and is replaced by -. (This is one which we generally use to generate SEO friendly URL)
The replace function, replace(), looks for a substring in the given string and replaces that with new value provided.
The syntax for using this is:
string.replace(substring,newstring)
Example:
Assume variable string has values assigned as string ="AtoZ on Tech"
Using replace functin on this, provide the substring and newstring to replace
i.e
<script type="text/javascript">
var string="AtoZ on Tech";
string=string.replace("to","2");
document.write(string);
</script>
Output:
A2Z on Tech
Done a slight modification in susbstring. Will this work?
<script type="text/javascript">
var string="AtoZ on Tech";
string=string.replace(/to/,"2");
document.write(string);
</script>
Yes, this works same exactly as the above code.
Will the below code work?
<script type="text/javascript">
var string="AtoZ on Tech";
string=string.replace("To","2");
document.write(string);
</script>
No, as here "T" is in uppercase. So, how can we have a case-insensitive search and replace?
<script type="text/javascript">
var string="AtoZ on Tech";
string=string.replace(/To/i,"2");
document.write(string);
</script>
Output: A2Z on Tech
Now, let me extend our string variable. as below.
<script type="text/javascript">
var string="AtoZ on Tech";
string= string + " is a blogsite discussing all about technology from A to Z";
string=string.replace(/To/i,"2");
document.write(string);
</script>
Output:A2Z on Tech is a blogsite discussing all about technology from A to Z
The second "A to Z" is as it is. Here only the first occurance is replaced. How to replace all the occurences of search string?
Then you should give the option for global represented by "g.
<script type="text/javascript">
var string="AtoZ on Tech";
string= string + " is a blogsite discussing all about technology from A to Z";
string=string.replace(/To/ig,"2");
document.write(string);
</script>
Now you know that we have used "i" for case-insensitive matching and "g" for global matching. These are called modifiers. Modifiers will specify what kind of search we have to do. There is one more modifier used in javascript, ie "m" for multiline matching. This we will be discussin in our later blogs.
Ok. So far is fine. What if you dont have a specific substring and you have only a pattern to be replaced?
Then you have to go for Regular Expression.
What is Regular Expression?
Regular Expression gives a pattern of characters like a-z for chars, 0-9 for int and can have some special character pattern as well.
Using regular expression we search for any pattern defined in the regular expression(RegExp) and replace it with the required string/value.
<script type="text/javascript">
var string="A2Z on Tech";
string=string.replace(/[^a-z,0-9]/ig,"-");
document.write(string);
</script>
The above code looks for a pattern match (any character other than a-z, A-Z and number 0-9) and is replaced by -. (This is one which we generally use to generate SEO friendly URL)
Friday, December 2, 2011
Find the number of occurence of a character in a given string in Oracle
Scenario: Need to find out the number of occurrence of a particular character in the given string.
Example: Find number of "e"s in the given string ("Oracle is an interesting thing to learn.")
Solution:
select length('Oracle is an interesting thing to learn') - length(replace('Oracle is an interesting thing to learn','e',''))
from dual;
4
Explained:
59-55 = 4, Hence four occurrence.
Another one Use Case:
Get number of comma separated values in a given column where the values are 100,123,124,145.
Use above method to find number of occurrence of character , (comma) in the givens string and add one to that gives you the number of comma separated values.
In Oracle 11g we have new function to save our time and shorten our code length.
REGEXP_COUNT
select regexp_count('EXPERIENCE','E') FROM DUAL;
This will give you the result 4.
Example: Find number of "e"s in the given string ("Oracle is an interesting thing to learn.")
Solution:
select length('Oracle is an interesting thing to learn') - length(replace('Oracle is an interesting thing to learn','e',''))
from dual;
4
Explained:
59-55 = 4, Hence four occurrence.
Another one Use Case:
Get number of comma separated values in a given column where the values are 100,123,124,145.
Use above method to find number of occurrence of character , (comma) in the givens string and add one to that gives you the number of comma separated values.
In Oracle 11g we have new function to save our time and shorten our code length.
REGEXP_COUNT
select regexp_count('EXPERIENCE','E') FROM DUAL;
This will give you the result 4.
Thursday, December 1, 2011
Understanding UNIX hierarchichal file system
In UNIX the fundamental basic principle is that anything that is not a folder is file, whether it be a normal file, I/O connections anything.
Using calculator in UNIX
UNIX provides both INFIX and POSTFIX calculators.
INFIX is when the operations are embedded within the operators
12*2
POSTFIX is when the operators are listed down and then followed by operations.
12 2 *
So, now to use infix calculator type the command "bc"
bash-3.00$ bc
Nothing happend. Now type in what you need to calculate and the system gives you output
1*2
2
1*2+3
5
2+3*2
8
Ok, now how to get out of this? Its simple. "quit"
.
.
2+3*2
8
quit
bash-3.00$
Various powerful options with bc command:
Find square root?
bash-3.00$ bc
sqrt(16)
4
similarly use below:
Postfix calculator:
user command "dc"
bash-3.00$ dc
2
3
*
p
6
2 3 *
p
6
2 3 * 4 +
p
10
Use "p" to print the result of your calculation.
INFIX is when the operations are embedded within the operators
12*2
POSTFIX is when the operators are listed down and then followed by operations.
12 2 *
So, now to use infix calculator type the command "bc"
bash-3.00$ bc
Nothing happend. Now type in what you need to calculate and the system gives you output
1*2
2
1*2+3
5
2+3*2
8
Ok, now how to get out of this? Its simple. "quit"
.
.
2+3*2
8
quit
bash-3.00$
Various powerful options with bc command:
Find square root?
bash-3.00$ bc
sqrt(16)
4
similarly use below:
Postfix calculator:
user command "dc"
bash-3.00$ dc
2
3
*
p
6
2 3 *
p
6
2 3 * 4 +
p
10
Use "p" to print the result of your calculation.
sqrt(n) Square root of n
% Remainder
^ To the power of (3^5 is 3 to the power of 5)
s(n) Sine(n)
c(n) Cosine(n)
e(n) Exponential(n)
l(n) Log(n)
How to find the date and time in an UNIX system
What I would like to know is the current date and time, so just type in command "date"
bash-3.00$ date
Fri Dec 2 11:02:42 IST 2011
What if I need to look into the calendar? Here it is..
bash-3.00$ cal
December 2011
S M Tu W Th F S
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
This list down only current month. What if I need the entire year?
Its very simple. With the command "cal" mention the year you are interested in
bash-3.00$ cal 2011
2011
Jan Feb Mar
S M Tu W Th F S S M Tu W Th F S S M Tu W Th F S
1 1 2 3 4 5 1 2 3 4 5
2 3 4 5 6 7 8 6 7 8 9 10 11 12 6 7 8 9 10 11 12
9 10 11 12 13 14 15 13 14 15 16 17 18 19 13 14 15 16 17 18 19
16 17 18 19 20 21 22 20 21 22 23 24 25 26 20 21 22 23 24 25 26
23 24 25 26 27 28 29 27 28 27 28 29 30 31
30 31
Apr May Jun
S M Tu W Th F S S M Tu W Th F S S M Tu W Th F S
1 2 1 2 3 4 5 6 7 1 2 3 4
3 4 5 6 7 8 9 8 9 10 11 12 13 14 5 6 7 8 9 10 11
10 11 12 13 14 15 16 15 16 17 18 19 20 21 12 13 14 15 16 17 18
17 18 19 20 21 22 23 22 23 24 25 26 27 28 19 20 21 22 23 24 25
24 25 26 27 28 29 30 29 30 31 26 27 28 29 30
Jul Aug Sep
S M Tu W Th F S S M Tu W Th F S S M Tu W Th F S
1 2 1 2 3 4 5 6 1 2 3
3 4 5 6 7 8 9 7 8 9 10 11 12 13 4 5 6 7 8 9 10
10 11 12 13 14 15 16 14 15 16 17 18 19 20 11 12 13 14 15 16 17
17 18 19 20 21 22 23 21 22 23 24 25 26 27 18 19 20 21 22 23 24
24 25 26 27 28 29 30 28 29 30 31 25 26 27 28 29 30
31
Oct Nov Dec
S M Tu W Th F S S M Tu W Th F S S M Tu W Th F S
1 1 2 3 4 5 1 2 3
2 3 4 5 6 7 8 6 7 8 9 10 11 12 4 5 6 7 8 9 10
9 10 11 12 13 14 15 13 14 15 16 17 18 19 11 12 13 14 15 16 17
16 17 18 19 20 21 22 20 21 22 23 24 25 26 18 19 20 21 22 23 24
23 24 25 26 27 28 29 27 28 29 30 25 26 27 28 29 30 31
30 31
Ok, I agree. But what I need to look into the month of Feb for the year 1999?
bash-3.00$ cal 2 1999
February 1999
S M Tu W Th F S
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28
Is that ok?
Be careful while specifying the year. You have to give fully year, iec 2011 not 11. See the difference below.
bash-3.00$ cal 12 2011
December 2011
S M Tu W Th F S
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
bash-3.00$ cal 12 11
December 11
S M Tu W Th F S
1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31
bash-3.00$ date
Fri Dec 2 11:02:42 IST 2011
What if I need to look into the calendar? Here it is..
bash-3.00$ cal
December 2011
S M Tu W Th F S
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
This list down only current month. What if I need the entire year?
Its very simple. With the command "cal" mention the year you are interested in
bash-3.00$ cal 2011
2011
Jan Feb Mar
S M Tu W Th F S S M Tu W Th F S S M Tu W Th F S
1 1 2 3 4 5 1 2 3 4 5
2 3 4 5 6 7 8 6 7 8 9 10 11 12 6 7 8 9 10 11 12
9 10 11 12 13 14 15 13 14 15 16 17 18 19 13 14 15 16 17 18 19
16 17 18 19 20 21 22 20 21 22 23 24 25 26 20 21 22 23 24 25 26
23 24 25 26 27 28 29 27 28 27 28 29 30 31
30 31
Apr May Jun
S M Tu W Th F S S M Tu W Th F S S M Tu W Th F S
1 2 1 2 3 4 5 6 7 1 2 3 4
3 4 5 6 7 8 9 8 9 10 11 12 13 14 5 6 7 8 9 10 11
10 11 12 13 14 15 16 15 16 17 18 19 20 21 12 13 14 15 16 17 18
17 18 19 20 21 22 23 22 23 24 25 26 27 28 19 20 21 22 23 24 25
24 25 26 27 28 29 30 29 30 31 26 27 28 29 30
Jul Aug Sep
S M Tu W Th F S S M Tu W Th F S S M Tu W Th F S
1 2 1 2 3 4 5 6 1 2 3
3 4 5 6 7 8 9 7 8 9 10 11 12 13 4 5 6 7 8 9 10
10 11 12 13 14 15 16 14 15 16 17 18 19 20 11 12 13 14 15 16 17
17 18 19 20 21 22 23 21 22 23 24 25 26 27 18 19 20 21 22 23 24
24 25 26 27 28 29 30 28 29 30 31 25 26 27 28 29 30
31
Oct Nov Dec
S M Tu W Th F S S M Tu W Th F S S M Tu W Th F S
1 1 2 3 4 5 1 2 3
2 3 4 5 6 7 8 6 7 8 9 10 11 12 4 5 6 7 8 9 10
9 10 11 12 13 14 15 13 14 15 16 17 18 19 11 12 13 14 15 16 17
16 17 18 19 20 21 22 20 21 22 23 24 25 26 18 19 20 21 22 23 24
23 24 25 26 27 28 29 27 28 29 30 25 26 27 28 29 30 31
30 31
Ok, I agree. But what I need to look into the month of Feb for the year 1999?
bash-3.00$ cal 2 1999
February 1999
S M Tu W Th F S
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28
Is that ok?
Be careful while specifying the year. You have to give fully year, iec 2011 not 11. See the difference below.
bash-3.00$ cal 12 2011
December 2011
S M Tu W Th F S
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
bash-3.00$ cal 12 11
December 11
S M Tu W Th F S
1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31
Subscribe to:
Posts (Atom)