The SELECT query is the most often used query in any database. So , learning this query is very important. Even if you don't write it manually, as some tools make your task easier by providing code assists. But this query helps you to understand many aspects of database concepts, ranging from DBA task to PL/SQL. I hope this tutorial helps learning SELECT query faster and easier.

We will be referring  tables from default schema/user SCOTT in Oracle.

The generalized select query looks like :


            { * |  [columnName  [ AS newColumnName ] ] ,

                    [columnName1 [ AS newColumnName1]],




           FROM tableName [ALIAS][,]

           [WHERE <condition>]

           [GROUP BY columnlist ] [HAVING <condition>]

           [ORDER BY columnlist]

We will start with simple query and then keep on building complex queries.





  1.  Select all the details of the employee from emp table.

    SELECT * FROM emp;
  2. Select only the names of all the employees in emp table;

    SELECT ename FROM emp;
  3. Select records using multiple  columns;

    SELECT eno,ename,sal FROM emp;
  4. Select records Using alias;
    SELECT eno,ename,salary AS sal FROM emp;
    1. Select records using multiple alias
      SELECT eno AS empno,ename AS empname ,sal AS salary FROM emp;
    2. Using functions to enhance alias
      SELECT count(sal) as totalsal FROM emp;
  5. Derived or Computed fields: Columns values were manipulated as it gets retrived.
    1. Find the monthly salary of employee,( The salary stored is on Annum basis).
      SELECT sal / 12 FROM emp;
    2. Using Alias to decorate derived or computed fields.
      SELECT sal / 12 AS monthly_salary FROM emp
    3. Calculate the sum of monthly salary and the commissions of the employee.
      SELECT ename,(sal / 12) + nvl(comm,0) AS monthsalwithcomm FROM emp;