Skip to main content

Posts

Showing posts from February, 2011

Calculating nth Max salary

This is a very common question in any interview on SQL. Max salary can be calculated in different ways. Following are the few examples and their explanation.

1. Using simple max function. (Retruns First Max Salary only)

SQL> SELECT MAX(sal)
           FROM emp;

 MAX(SAL)
----------
      5000

This is to find the first max salary from EMP table. MAX is a group function which returns maximum value out of group of records or all the records.

Click Here, to know more about MAX

2. Using subqueries.

SQL> SELECT MAX(sal)
           FROM emp
           WHERE sal < (SELECT MAX(sal) FROM emp);

  MAX(SAL)
----------
      3000

Look at the above query closely, sal < max(sal) returns all the salaries less than first max salary. Calculating maximum salary out of all the salaries less than the first maximum salary will return second maximum salary.

So use multiple subqueries to get nth max salary.

3. Using ROWNUM

ROWNUM is a psuedocolumn which retruns a number indicating the order in which …

SQL Interview Questions

APEX -- Oracle is giving back to Community

Have you ever seen a online database which is free to use and free to practice.
Imagine you do not have to dowload GB's database software, install on your PC or laptop to practice SQL plus.

APEX

Application Express is a freeware application development framework based on Oracle Database. This was previously called HTML DB, long back while this was still HTML DB I had developed a Learning management system Oracle University which never went live on production. :-( but never stopped me from working on it.

You can download the current version from http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

But if you do not want to download and just want to practice or explore the options you can try creating an account on http://apex.oracle.com/

For begginer follow the steps below to create your own account and start using APEX for sql basics practice.

Installing Oracle 10g Database

Once you have downloaded the software from www.oracle.com/downloads, following steps can be used to install the software.

1. Unzip the dump downloaded from oracle.com




2. Once Unzipped, click on setup.exe to run Oracle Universal Installer Program. This will open a window as shown below.