Error Vs Bug Vs Defect

Error : Deviation for actual and the expected/theoritical value .

Bug : An Error found in the development environment before the product is shipped to the customer .

Defect : An Error found in the product itself after it is shipped to the customer .

 Also, check out Free ISTQB Training Material here

www.testing4success.com - Application QA/Testing     Mobile App QA/Testing     Web Testing     Training
Iphone App QA  Android App QA  Web QA 

What is the Querry for retrieving the 2nd highest

select max(salary) from where salary! (select max(salary) from )

ROWNUM and ROWID

ROWNUM and ROWID

Questions:
How do I limit the number of rows returned by a query?
How do I write a query to get the Top-N salaries from the employee table?
How can I add unique, sequential numbers to an existing table?
How can I differentiate between two completely identical rows?
How can I find a faster way to retrieve a queried row?
How can I find the last row processed in a big batch?

There is one thing all these questions have in common: the answer involves either ROWNUM or ROWID.


So what is ROWNUM and ROWID?

First of all, both are covered in the SQL Reference, Basic Elements of Oracle SQL, Chapter 2:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540.pdf

They are also both referred to as pseudo-columns. That is, they are not "real" columns that will show up when you DESC a table. They don't actually exist anywhere in the database. But they're available for you to use.

In fact, ROWNUM only exists for a row once it is retrieved from a query. It represents the sequential order in which Oracle has retrieved the row. Therefore it will always exist, be at least 1, and be unique (among the rows returned by the query). Obviously it will change from query-to-query. Let's look at a quick example:

viv@Desk> SELECT ROWNUM, ENAME, SAL
  2  FROM EMP;

    ROWNUM ENAME             SAL
---------- ---------- ----------
         1 SMITH             800
         2 ALLEN            1600
         3 WARD             1250
         4 JONES            2975
         5 MARTIN           1250
         6 BLAKE            2850
         7 CLARK            2450
         8 SCOTT            3000
         9 VOLLMAN          5000
        10 TURNER           1500
        11 ADAMS            1100
        12 JAMES             950
        13 FORD             3000
        14 MILLER           1300

Ok so let's say we want the 5 highest paid employees. Should be easy:

viv@Desk> SELECT ROWNUM, ENAME, SAL
  2  FROM EMP
  3  WHERE ROWNUM < 6
  4  ORDER BY SAL DESC;

    ROWNUM ENAME             SAL
---------- ---------- ----------
         4 JONES            2975
         2 ALLEN            1600
         3 WARD             1250
         5 MARTIN           1250
         1 SMITH             800

Whoops! Turns out ROWNUM is assigned before results are ordered, not after. Knowing that, we can write it like this:

viv@Desk> SELECT ENAME, SAL
  2  FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) E
  3  WHERE ROWNUM < 6;

ENAME             SAL
---------- ----------
VOLLMAN          5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850

What about ROWID? ROWID actually represents the physical location of the record/row in the database. That being the case, it is (according to Oracle documentation) the fastest way to retrieve a particular row. Faster than an index, even.

Can you use ROWID to differentiate between duplicate rows?
Yes, you can. Since it actually represents the physical location of a row, no two rows within the same table will have the same ROWID. Notice the caveat I added: within the same table. If you're using clustering, two records from different tables could theoretically share the same ROWID.

Do ROWIDs change?
Yes, especially with index organized or partitioned tables. Because ROWIDs represent the physical location of a record/row, the ROWID will change every time the record is physically moved.

Can you use ROWID as a primary key?
No, that's not advisable. While the ROWID will be unique, you would ideally want to use a primary key that doesn't change.

How do you use ROWID to figure out what was the last record that was processed?
Using DBMS_SQL.LAST_ROW_ID to get the ROWID of the last row processed.

http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

Oracle NVL function- TO CHECK THE NULL COLUMN

Oracle NVL function

The NVL function is used to replace NULL values by another value.

The syntax for the NVL function is:
NVL( value_in, replace_with )

value_in if the function to test on null values. The value_in field can have a datatype char, varchar2, date or number datatype.
replace_with is the value that is returned if value_in has a null value. The NVL statement works like this pl/sql code:

if (value_in is NULL) then
  return replace_with;
else
  return value_in;
end if;

Sample code: 

select nvl(salary, 0)
from   employees;

select nvl(ref_code,'Unknown')
from   users;