Instance: It refers to the Oracle process that accesses the database. An instance works on the database but first it has to mount it. An instance can mount atmost one database but a database can be mounted by multiple instances.
Database: It refers to the set of files where the metadata and the application data is stored. This is the operational data.
SID: An SID identifies an instance.
The Exists clause:
The exists clause is used to check for the existence of rows in the subquery. Based on whether the rows exist, the outer query would be executed. If the subquery does not return any rows then the outer query would not be executed.
- select 1 from table t2; => This would return as many rows as there are in the table "t2" . If we have the following query:
select count(*) from table t1 where exists (select 1 from table t2); => The result would be the row count of the table t1 if there t2 is not empty and has some rows.
- select count(*) from table t1 where exists (select 1 from table t2 where t1.id = t2.id); => This is an example of a corelated subquery.
An interesting question:
Suppose there is a table "Emp(id, salary)" and you need to return the top 3 employees w.r.t salary. You could do this:
select * from (select * from Emp E order by salary desc) where rownum < 4;