Oracle SQL and PL/SQL

SQL COUNT function

COUNT function returns the total number from the expression. It is an example of aggregate functions.

Syntax:

1. select count(column_name) from table_name;
2. select count(*) from table_name;

Count(*) returns total number of rows.
Count(column_name) returns total number of rows have data. ie it ignores null columns.

Examples:-

Consider below EMP table structure

Empno	Empname	Manager	Deptno
10	Bill		5
11	Solomon	Bill	5
12	Susan	Bill	5
13	Wendy	Solomon	1
14	Benjamin	Solomon	1
15	Tom	Solomon	1
16	Henry	Solomon	2
17	Robert	Susan	2
18	Paul	Solomon	2

1. Find the number of employees.

select count(*) from EMP;

COUNT(*)

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

2. Find the total number of managers.

select count(manager) from EMP;

COUNT(MANAGER)

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

3. Find all managers handling more than one employees.

select manager ,count(*) from EMP group by manager having count(*) > 1;

MANAGER COUNT(*)
------------------ ----------
Bill 2
Solomon 5

4. Find total number of managers.

select count(distinct manager) from EMP;

COUNT(DISTINCTMANAGER)
--------------------------------------------
3

Related topics
AVG function
MAX Function

SQL AVG function

AVG function returns the average from the expression. It is an example of aggregate functions.

Syntax:

select avg(column_name) from table_name;

Examples:-

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find the average salary among the employees.

select avg(salary) from EMP;

AVG(SALARY)

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

9088.88889

2. Find the average salary among the employees in a department (Using where clause).

select avg(salary) from EMP where deptno = 5;

AVG(SALARY)

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

10666.6667

3. Find the average salary in each department (Using group by clause).

select deptno,avg(salary) from EMP group by deptno;

DEPTNO AVG(SALARY)

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

1 8033.33333
2 8566.66667
5 10666.6667

4. Find the all department details having average salary higher than 9000 (Using having by clause).

select deptno,avg(salary) from EMP group by deptno having avg(salary) > 9000;

DEPTNO AVG(SALARY)
---------- ---------------------
5 10666.6667

SQL MIN function

MIN function returns the minimum from the expression. It is an example of aggregate functions.

Syntax:

select min(column_name) from table_name;

Examples:-

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find the lowest salary among the employees.

select min(salary) from EMP;

MIN(SALARY)

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

7500

2. Find the lowest salary among the employees in a department (Using where clause).

select min(salary) from EMP where deptno = 5;

MIN(SALARY)

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

10000

3. Find the lowest salary in each department (Using group by clause).

select deptno,min(salary) from EMP group by deptno;

DEPTNO MIN(SALARY)

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

1 7500

2 8500

5 10000

SQL MAX function

MAX function returns the maximum from the expression. It is an example of aggregate functions.

Syntax:

select max(column_name) from table_name;

Examples:-

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find the highest salary among the employees.

select max(salary) from EMP;

MAX(SALARY)

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

12000

2. Find the highest salary among the employees in a department (Using where clause).

select max(salary) from EMP where deptno = 1;

MAX(SALARY)

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

9000

3. Find the highest salary in each department (Using group by clause).

select deptno,max(salary) from EMP group by deptno;

DEPTNO MAX(SALARY)

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

1 9000

2 9500

5 12000

Database transaction and ACID properties

Database Transaction
A transaction is logical unit of work which has a complete status performed within a database management system. It should not be in an inconsistent status.

Example :-
Assume a user has a savings account having 4000 unit of currency. He withdraws 100 unit of currency from an ATM and this withdrawal is referred as a transaction.

1. User/Card/Account authentication.
2. Withdrawal request for 100 unit of currency.
3. Balance verification.
4. Dispenses the currency to user and update the balance to 3900.
5. Print receipt and exit.

Assume that a network error happened after Step 3. The transaction will not be completed.

ACID properties
A database transaction must follow ACID properties.

A - Atomic
C - Consistent
I - Isolated
D - Durable

Atomic :-
This property ensures the transaction either in complete status or nothing has started. There must not be any scenario like user received some currency amount and balance remains same or balance updated and failed to dispense the amount.

Consistent:-
Every transaction must be consistent.

Isolated:-
Every transaction must be isolated to avoid the locks or deadlocks. Deadlock is a condition when two or more users or processes waiting for the other to release a resource.

Durable:-
A transaction which has committed or saved must be there for permanently.

Read more on how to install Oracle 11g on Ubuntu Linux here

Oracle SQL Functions

Oracle database supplied with many in built SQL functions. Oracle recommends to use in built SQL function rather than using a user defined functions. It perform better than user defined functions. So try to utilize in built SQL functions as much as you can. Some of the SQL functions really helps while writing complex SQLs.

Most used SQL functions can be categorized as

1. Aggregate functions - Returns a single result from a group of rows.
Examples :- AVG, MAX, MIN, COUNT,SUM etc.

2. Numeric functions - Accepts numeric input and returns numeric result. It is also called as Mathematical functions.
Examples :- SIN, COS, TAN, MOD, POWER, EXP etc.

3. Character functions - Returns character result from character input.
Examples :- CHR, LOWER, UPPER, TRIM etc.

4. Date functions - To manipulate date data.
Examples :- SYSDATE, ADD_MONTHS, TRUNC etc.

5. Conversion functions - To convert one data type to another data type
Examples :- TO_CHAR, TO_DATE, TO_NUMBER etc.

6. Analytical functions - It computes aggregate based on group of data
Examples :- RANK, DENSE_RANK, LAG etc.

Apart from these Oracle has few more functions like Collection functions, Large Object functions, XML functions, Data Mining functions, Hierarchical functions etc.

Some of the above listed functions which falls multiple categories. For example TRUNC is a Numeric as well as Date function. TRUNC(date) returns date value while TRUNC(number) returns numeric value.

Each individual SQL function explained in separate sections with examples.

DUAL table in Oracle

DUAL table is a special single row table present by default in Oracle Databases.

Structure of DUAL table
DUAL table has only one column called DUMMY as Varchar2(1) data type and value as X.

Examples
1) select 1 from DUAL;
    returns 1

2) select 1+2 from DUAL;
    returns 3

3) select sysdate from DUAL;
    returns system date of oracle database.

4) select `sql` from DUAL;
    returns`sql`

5) select user from DUAL;
    returns oracle user logged in.

6) select
   (select empno from emp where empno = 10)
   from DUAL;
    returns 10

DUAL table can be used to test the SQL functions ( Both in-built and user defined functions)

7) select func_salary(10) from DUAL;
where func_salary is a user defined function returns a value when argument is 10.

8) select LOWER(`ORAclE`) from DUAL;
returns`oracle` where LOWER is a in-built SQL function.

DUAL table can be used in SQL and PL/SQL.