SQL concepts - 8. Subqueries

A query nested within a query is known as subquery.

For example, you want to see all the employees whose salary is above average salary. For this you have to first compute the average salary using AVG function and then compare employees salaries with this computed salary. This is possible using subquery. Here the sub query will first compute the average salary and then main query will execute.

Select * from emp where sal > (select avg(sal) from emp);

Similarly we want to see the name and empno of that employee whose salary is maximum.

Select * from emp where sal = (select max(sal) from emp);

To see second maximum salary

Select max(sal) from emp where

       sal < (select max(sal) from emp);

Similarly to see the Third highest salary.

Select max(sal) from emp where

        sal < (select max(sal) from emp Where

               sal < (select max(sal) from emp));

We want to see how many employees are there whose salary is above average.

Select count(*) from emp where

      sal > (select max(sal) from emp);

We want to see those employees who are working in Hyderabad. Remember emp and dept are joined on deptno and city column is in the dept table. Assuming that wherever the department is located the employee is working in that city.

Select * from emp where deptno

      in (select deptno from dept where city=’HYD’);

You can also use subquery in FROM clause of SELECT statement.

For example the following query returns the top 5 salaries from employees table.

Select sal from (select sal from emp order sal desc)

             where rownum <= 5;

To see the sum salary deptwise you can give the following query.

Select sum(sal) from emp group by deptno;

Now to see the average total salary deptwise you can give a sub query in FROM clause.

select  avg(depttotal) from (select sum(sal) as depttotal from emp group by deptno);

WITH

The above average total salary department wise can also be achieved in 9i using WITH clause given below

WITH

  DEPTOT AS (select sum(sal) as dsal from emp

                group by deptno)

  select avg(dsal) from deptot;

GROUP BY QUERIES

You can group query results on some column values. When you give a SELECT statement without group by clause then all the resultant rows are treated as a single group.

For Example, we want to see the sum salary of all employees dept wise. Then the following query will achieved the result

Select deptno,sum(sal) from emp group by deptno;

Similarly we want to see the average salary dept wise

Select deptno,avg(sal) from emp group by deptno;

Similarly we want to see the maximum salary in each department.

Select deptno,max(sal) from emp group by deptno;

Similarly the minimum salary.

Select deptno,min(sal) from emp group by deptno;

Now we want to see the number of employees working in each department.

Select deptno,count(*) from emp group by deptno;

Now we want to see total salary department wise where the dept wise total salary is above 5000.

For this you have to use HAVING clause. Remember HAVING clause is used to filter groups and WHERE clause is used to filter rows. You cannot use WHERE clause to filter groups.

select deptno,sum(sal) from emp group by deptno

                  having sum(sal) >= 5000;

We want to see those departments  and the number of employees working in them where the number of employees is more than 2.

Select deptno, count(*) from emp group by deptno

                        Having count(*) >=2;

Instead of displaying deptno you can also display deptnames  by using join conditions.

For example we want to see deptname and average salary of them.

Select dname,avg(sal) from emp,dept

           where emp.deptno=dept.deptno group by dname;

Similarly to see sum of sal.

Select dname,sum(sal) from emp,dept

   where emp.deptno=dept.deptno group by dname;

Now we want to see the cities name and the no of employees working in each city. Remember emp and dept are joined on deptno and city column is in the dept table. Assuming that wherever the department is located the employee is working in that city.

Select dept.city,count(empno) from emp,dept

       where emp.deptno=dept.deptno

             Group by dept.city;

ROLLUP

The ROLLUP operation in the simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions in the GROUP BY specification, and returns a single row of summary for each group. You can use the ROLLUP operation to produce subtotal values by using it with the SUM function. When used with SUM,ROLLUP generates subtotals from the most detailed level to the grand total. Aggregate functions such as COUNT can be used to produce other kinds of superaggregates.

For example, given three expressions (n=3) in the ROLLUP clause of the simple_grouping_clause, the operation results in n+1 = 3+1 = 4 groupings.

Rows grouped on the values of the first 'n' expressions are called regular rows, and the others are called superaggregate rows.

The following query uses rollup operation to show sales amount product wise and year wise. To see the structure of the sales table refer to appendices.

Select prod,year,sum(amt) from sales

     group by rollup(prod,year);

CUBE

The CUBE operation in the simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the specification, and returns a single row of summary information for each group. You can use the CUBE operation to produce cross-tabulation values.

For example, given three expressions (n=3) in the CUBE clause of the simple_grouping_clause, the operation results in 2ⁿ = 2³ = 8 groupings. Rows grouped on the values of 'n' expressions are called regular rows, and the rest are called superaggregate rows.

The following query uses CUBE operation to show sales amount product wise and year wise. To see the structure of the sales table refer to appendices.

Select prod,year,sum(amt) from sales

     group by CUBE(prod,year);

CASE EXPRESSION

CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures.

For example the following query uses CASE expression to display Department Names based on deptno.

Select empno,ename,sal,CASE deptno when 10 then ‘Accounts’

                    When 20 then ‘Sales’

                    When 30 then ‘R&D’

                    Else “Unknown’ end

            From emp;

The following statement finds the average salary of the employees in the employees table using $2000 as the lowest salary possible:

SELECT AVG(CASE WHEN e.sal > 2000 THEN e.sal

   ELSE 2000 END) "Average Salary" from emp e;

SQL concepts - 7. Joins

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle performs a join whenever multiple tables appear in the query's FROMclause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Join Conditions

Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list.

Equijoins

An equijoin is a join with a join condition containing an equality operator ( = ). An equijoin combines rows that have equivalent values for the specified columns.

For example the following query returns empno,name,sal,deptno and department name and city from department table.

select emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname,dept.city from emp,dept where emp.deptno=dept.deptno;

The above query can also be written like, using aliases, given below.

select e.empno, e.ename, e.sal, e.deptno, d.dname, d.city from emp e, dept d where emp.deptno=dept.deptno;

The above query can also be written like given below without using  table qualifiers.

select empno,ename,sal,dname,city from emp,dept where emp.deptno=dept.deptno;

And if you want to see all the columns of both tables then the query can be written like this.

select * from emp,dept where emp.deptno=dept.deptno;

Non Equi Joins.

Non equi joins is used to return result from two or more tables where exact join is not possible.

For example we have emp table and salgrade table. The salgrade table contains grade and their low salary and high salary. Suppose you want to find the grade of employees based on their salaries then you can use NON EQUI join.

select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.lowsal and s.hisal

Self Joins

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.

For example the following query returns employee names and their manager names for whom they are working.

Select e.empno, e.ename, m.ename  “Manager” from emp e,

emp m where e.mgrid=m.empno

Inner Join

An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition.

Outer Joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

• To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT [OUTER] JOIN syntax, or apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B.
• To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the ANSI RIGHT [OUTER] syntax, or apply the outer join operator (+) to all columns of A in the join condition. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
• To write a query that performs an outer join and and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the ANSI FULL [OUTER] JOIN syntax.

For example the following query returns all the employees and department names and even those department names where no employee is working.

select e.empno,e.ename,e.sal,e.deptno,d.dname,d.city  from emp e, dept d where e.deptno(+)=d.deptno;

That is specify the (+) sign to the column which is lacking values.

Cartesian Products

If two tables in a join query have no join condition, Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product.

SQL concepts - 6. Built-in Functions

SQL functions are built into Oracle and are available for use in various appropriate SQL statements. You can also create your own function using PL/SQL.

Single-Row Functions

Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses.

Number Functions

Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits.

The number functions available in Oracle are:

ABS  ACOS ASIN ATAN ATAN2 BITAND CEIL COS COSH EXP FLOOR LN LOG
MOD POWER ROUND (number) SIGN SIN SINH SQRT TAN TANH TRUNC (number)
 

ABS

ABS returns the absolute value of n.

The following example returns the absolute value of -87:

SELECT ABS(-87) "Absolute" FROM DUAL;

 Absolute

----------

        87

ACOS

ACOS returns the arc cosine of n. Inputs are in the range of -1 to 1, and outputs are in the range of 0 to pi and are expressed in radians.

The following example returns the arc cosine of .3:

SELECT ACOS(.3)"Arc_Cosine" FROM DUAL;

Arc_Cosine

----------

1.26610367

Similar to ACOS, you have ASIN (Arc Sine), ATAN (Arc Tangent) functions.

CIEL

Returns the lowest integer above the given number.

Example:

The following function return the lowest integer above 3.456;

select ciel(3.456) “Ciel” from dual;

Ciel

---------

        4

FLOOR         

Returns the highest integer below the given number.

Example:

The following function return the highest integer below 3.456;

select floor(3.456) “Floor” from dual;

Floor

------------

        3

COS

Returns the cosine of an angle (in radians).

Example:

The following example returns the COSINE angle of 60 radians.

select  cos(60) “Cosine” from dual;

SIN

Returns the Sine of an angle (in radians).

Example:

The following example returns the SINE angle of 60 radians.

select  SIN(60) “Sine” from dual;

 

TAN

Returns the Tangent of an angle (in radians).

Example:

The following example returns the tangent angle of 60 radians.

select  Tan(60) “Tangent” from dual;

Similar to SIN, COS, TAN  functions hyperbolic functions  SINH, COSH, TANH are also available in oracle.

MOD

Returns the remainder after dividing m with n.

Example

The following example returns the remainder after dividing 30 by 4.

Select mod(30,4) “MOD” from dual;

MOD

---------

        2

POWER

Returns the power of m, raised to n.

Example

The following example returns the 2 raised to the power of 3.

select  power(2,3) “Power” from dual;

POWER

---------

        8

EXP

Returns the e raised to the power of n.

Example

The following example returns the e raised to power of 2.

select exp(2) “e raised to 2” from dual;

E RAISED TO 2

-------------

       

LN

Returns natural logarithm of n.

Example

The following example returns the natural logarithm of 2.

select ln(2) from dual;

LN

------------

LOG

Returns the logarithm, base m, of n.

Example

The following example returns the log of 100.

select log(10,100) from dual;

LOG

---------

        2

ROUND

Returns a decimal number rounded of to a given decimal positions.

Example

The following example returns the no. 3.4573 rounded to 2 decimals.

select round(3.4573,2) “Round” from dual;

Round

------------

        3.46

TRUNC

Returns a decimal number Truncated to a given decimal positions.

Example

The following example returns the no. 3.4573 truncated to 2 decimals.

select round(3.4573,2) “Round” from dual;

Round

------------

        3.45

SQRT

Returns  the square root of a given number.

Example

The following example returns the square root of  16.

select  sqrt(16) from dual;

SQRT

---------

        4

Character Functions

Character functions operate on values of dataype  CHAR or VARCHAR.

LOWER

Returns a given string in lower case.

select LOWER(‘SAMI’) from dual;

LOWER

-------------

sami

UPPER

Returns a given string in UPPER case.

select UPPER(‘Sami’) from dual;

UPPER

------------------

SAMI

                    

INITCAP

Returns a given string with Initial letter in capital.

select INITCAP(‘mohammed sami’) from dual;

INITCAP

------------------

Mohammed Sami

LENGTH

Returns the length of a given string.

select length(‘mohammed sami’) from dual;

LENGTH

------------

        13

SUBSTR

Returns a substring from a given string. Starting from position p to n characters.

For example the following query returns “sam” from the string “mohammed sami”.

select substr('mohammed sami',10,3) from dual;

Substr

--------

sam

INSTR

Tests whether a given character occurs in the given string or not. If the character occurs in the string then returns the first position of its occurrence otherwise returns 0.

Example

The following query tests whether the character “a” occurs in string “mohammed sami”

select instr('mohammed sami','a') from dual;

INSTR

--------

4

REPLACE

Replaces a given set of characters in a string with another set of characters.

Example

The following query replaces “mohd” with “mohammed” .

select replace('ali mohd khan','mohd','mohammed') from dual;

REPLACE

---------

ali mohammed khan

 

TRANSLATE

This function is used to encrypt characters. For example you can use this function to replace characters in a given string with your coded characters.

Example

The following query replaces characters A with B, B with C, C with D, D with E,...Z with A, and a with b,b with c,c with d, d with e ....z with a.

select translate('interface','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz','BCDEFGHIJKLMNOPQRSTUVWXYZAbcdefghijklmnopqrstuvwxyza') “Encrypt” from dual;

Encrypt

-----------

joufsgbdf

SOUNDEX

This function is used to check pronounciation rather than exact characters. For example many people write names as “smith” or “smyth” or “smythe” but they are pronounced as smith only.

Example

The following example compare those names which are spelled differently but are pronouced as “smith”.

Select ename from emp where soundex(ename)=soundex('smith');

ENAME

---------

Smith

Smyth

Smythe

RPAD

Right pads a given string with a given character to n number of characters.

Example

The following query rights pad ename with '*'  until it becomes 10 characters.

select rpad(ename,'*',10) from emp;

Ename

----------

Smith*****

John******

Mohammed**

Sami******

LPAD

Left pads a given string with a given character upto n number of characters.

Example

The following query left pads ename with '*'  until it becomes 10 characters.

select lpad(ename,'*',10) from emp;

Ename

----------

*****Smith

******John

**Mohammed

******Sami

LTRIM

Trims blank spaces from a given string from left.

Example

The following query returns string “       Interface        “ left trimmed.

select ltrim('       Interface       ') from dual;

Ltrim

--------------

Interface 

RTRIM

Trims blank spaces from a given string from Right.

Example

The following query returns string “       Interface        “ right trimmed.

select rtrim('       Interface       ') from dual;

Rtrim

------------

   Interface 

TRIM

Trims a given character from left or right or both from a given string.

Example

The following query removes zero from left and right of a given string.

Select trim(0 from '00003443500') from dual;

Trim

----------

34435

CONCAT

Combines a given string with another string.

Example

The following Query combines ename with literal string “ is a “ and jobid.

Select concat(concat(ename,' is a '),job) from emp;

Concat

----------------

Smith is a clerk

John is a Manager

Sami is a G.Manager

 Miscellaneous Single Row Functions

 COALESCE

 Coalesce function returns the first not null value in the expression list.

 Example.

 The following query returns salary+commision, if commission is null then returns salary, if salary is also null then returns 1000.

select empno,ename,salary,comm,coalesce(salary+comm,salary,1000) “Net Sal” from emp;

ENAME     SALARY    COMM NET SAL

-----     ------    ---- -------

SMITH     1000      100  1100

SAMI      3000          3000

SCOTT                   1000

RAVI               200  1000

DECODE

            DECODE(expr, searchvalue1, result1,searchvalue2,result2,..., defaultvalue)

Decode functions compares an expr with search value one by one. If the expr does not match any of the search value then returns the default value. If the default value is omitted then returns null.

Example

The following query returns the department names according the deptno. If the deptno does not match any of the search value then returns “Unknown Department”

select decode(deptno,10,'Sales',20,'Accounts,30,'Production,

          40,'R&D','Unknown Dept') As DeptName from emp;

DEPTNAME

----------

Sales

Accounts

Unknown Dept.

Accounts

Production

Sales

R&D

Unknown Dept.

 

GREATEST

            GREATEST(expr1, expr2, expr3,expr4...)

Returns the greatest expr from a expr list.

Example

select greatest(10,20,50,20,30) from dual;

GREATEST

--------

50

select greatest('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;

GREATEST

--------

TANYA

 

LEAST

            LEAST(expr1, expr2, expr3,expr4...)

It is simillar to greatest. It returns the least expr from the expression list.

select least(10,20,50,20,30) from dual;

LEAST

--------

10

select least('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;

LEAST

--------

RAVI

NVL

                        NVL2(expr1,expr2)

This function is oftenly used to check null values. It  returns  expr2 if the  expr1 is null, otherwise returns expr1.

Example

The following query returns commission if commission is null then returns 'Not Applicable'.

Select ename,nvl(comm,'Not Applicable') “Comm” from dual;

ENAME     COMM

------    ----

Scott     300

Tiger     450

Sami      Not Applicable

Ravi      300

Tanya     Not Applicable

NVL2

            NVL2(expr1,expr2,expr3)

NVL2 returns  expr2 if expr1 is not null, otherwise return expr3.

Example

The following query returns salary+comm if comm is not null, otherwise just returns salary.

select salary,comm,nvl2(comm,salary+comm,salary) “Income” from emp;

SALARY    COMM      INCOME

------    ----      ------

1000      100       1100

2000               2000

2300      200       2500

3400               3400

 

NULLIF

            NULLIF(expr1, expr2)

Nullif compares expr1 with expr2. If they are equal then returns null, otherwise return expr1.

Example.

The following query shows old jobs of those employees  who have changed their jobs in the company by comparing the current job with old job in oldemp table.

Select ename,nullif(e.job,o.job) “Old Job” from emp e, oldemp o where e.empno=o.empno;

ENAME         OLD JOB

-----         -------

SMITH         CLERK

SAMI                   

SCOTT         MANAGER

UID

Returns the current session ID of user logged on.

Example

select uid from dual;

UID

----

20

USER

Returns the username of the current user logged on.

select user from dual;

USER

---------

SCOTT

SYS_CONTEXT

SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

EXAMPLE

The following query returns the username of the current user.

Select sys_context('USERENV','SESSION_USER') “Username” from dual;

USERNAME

---------

SCOTT

Similar to SESSION_USER parameter for namespace USERENV the other important parameters are

ISDBA            :To check whether the current user is having DBA privileges or not.

HOST              :Returns the name of  host machine from which the client is connected.

INSTANCE    :The instance identification number of the current instance

IP_ADDRESS: IP address of the machine from which the client is connected.

DB_NAME    :Name of the database as specified in the DB_NAME initialization parameter

VSIZE

                        VSIZE(expr)

Returns the internal representation of expr in bytes.

Example

The following query return the representation of  ename in bytes.

select ename,vsize(ename) as Bytes from emp;

ENAME     BYTES

------    ------

SCOTT     5

SAMI      4

RAVI      4

KIRAN     5

Aggregate Functions

 Aggregate functions return a single value based on groups of rows, rather than single value for each row. You can use Aggregate functions in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups.

The important Aggregate functions are :

Avg     Sum     Max     Min      Count     Stddev     Variance

AVG

            AVG( ALL /DISTINCT        expr)

Returns the average value of expr.

Example

The following query returns the average salary of all employees.

select avg(sal) “Average Salary” from emp;

Average Salary

------------------------

2400.40

SUM

                        SUM(ALL/DISTINCT           expr)

Returns the sum value of expr.

Example

The following query returns the sum salary of all employees.

select sum(sal) “Total Salary” from emp;

Total Salary

------------------------

26500

  

MAX

                        MAX(ALL/DISTINCT          expr)

Returns maximum value of expr.

Example

The following query returns the max salary from the employees.

select max(sal) “Max Salary” from emp;

Maximum Salary

------------------------

4500

MIN

                        MIN(ALL/DISTINCT           expr)

Returns minimum value of expr.

Example

The following query returns the minimum salary from the employees.

select min(sal) “Min Salary” from emp;

Minimum Salary

------------------------

1200

COUNT

                        COUNT(*)      OR      COUNT(ALL/DISTINCT     expr)

Returns the number of rows in the query. If you specify expr then count ignore nulls. If you specify the asterisk (*), this function returns all rows, including duplicates and nulls. COUNT never returns null.

Example

The following query returns the number of  employees.

Select count(*) from emp;

COUNT

------

14

The following query counts the number of employees whose salary is not null.

Select count(sal) from emp;

COUNT

------

12

STDDEV

            STDDEV(ALL/DISTINCT   expr)

STDDEV returns sample standard deviation of expr, a set of numbers.

Example

The following query returns the standard deviation of salaries.

select stddev(sal) from emp;

Stddev

-------  

1430

VARIANCE

            VARIANCE(ALL/DISTINCT          expr)

Variance returns the variance of expr.

Example

The following query returns the variance of salaries.

select variance(sal) from emp;

Variance

-------  

1430