Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

sql

Concatenate Strings Using || Operator in Oracle SQL PLSQL

by

The || operator in Oracle SQL / PLSQL can be used to concatenate multiple strings to form a single string.

Syntax for the using the || operator in Oracle SQL / PLSQL is:

SELECT string_1 || string_2 || string_3 || . . . || string_N
FROM table_name;

string_1, strring_2 . . string_N are the strings to be concatenated.

Examples:

Using || operator in Oracle SQL / PLSQL SELECT statement:

SELECT 'MONDAY '|| 'TUESDAY '|| 'WEDNESDAY '
FROM dual;

Will return ‘MONDAY TUESDAY WEDNESDAY ‘

SELECT 'TECH' || 'HONEY' || '.COM'
FROM dual;

Will return ‘TECHHONEY.COM’

CUBE in Oracle SQL – PLSQL

by

The CUBE in Oracle SQL / PLSQL is an extension for the GROUP BY clause.

Syntax for CUBE in Oracle SQL / PLSQL is:
SELECT column(s),
AGGREGATE_FUNCTION(s),
FROM table_name
GROUP BY CUBE column(s)
[ORDER BY column(s)];

Example:

Using CUBE in Oracle SQL / PLSQL Query
Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10

If we write our query as:

SELECT department
       ,commission
       ,SUM(salary) Total_Salary
FROM employee
GROUP BY CUBE (department, commission);

We will get the following output:

Department Commission Total_Salary
IT 20 48000
IT 48000
Sales 10 82000
Sales 82000
Support 5 30000
Support 30000
5 30000
10 82000
20 48000
160000

Let’s understand the fetched results to understand CUBE:

  • 1st, 3rd and 5th row tells us the commission and the total salary grouped by IT, Sales and Support department along with the respective commissions
  • 2nd, 4th and 6th row tells us the total salary in the IT, Sales and Support department neglecting the commissions being given to the employees.
  • 7th, 8th and 9th row tells us the total salary being given to the employees having commissions of 5 , 10 and 20 respectively neglecting the departments in which they work.
  • The 10th row tells us the total salary being given to the employees, neglecting the department and the commissions of the employees.

PARTITION BY Keyword in Oracle SQL – PLSQL

by

In simple terms the PARTITION BY keyword in Oracle SQL / PLSQL is used to partition or segregate the records based on groups

Syntax for the PARTITION BY keyword in Oracle SQL / PLSQL is:
SELECT columns
,aggregate_function OVER (PARTITION BY column(s))
FROM table_name;

Example 1:

Using PARTITION BY keyword

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10

If we write our query as:

SELECT employee_id
       ,employee_name
       ,department
       ,COUNT(*) OVER (PARTITION BY department) Total
FROM employee;

We will get the following result:

Employee_Id Employee_Name Department Total
103 Emp C IT 2
102 Emp B IT 2
106 Emp F Sales 3
105 Emp E Sales 3
101 Emp A Sales 3
104 Emp D Support 1

Here we can see that in the ‘Total’ column we have retrieved ‘2’ for ‘IT’ department as there are ‘2’ records available in employee table for ‘IT’ department similarly we have ‘3’ and ‘1’ records for ‘Sales’ and ‘Support’ departments.

ROWNUM Pseudo Column in Oracle SQL – PLSQL

by

The ROWNUM is a pseudo column in Oracle SQL / PLSQL which returns a row’s position in the fetched result set.
ROWNUM is evaluated AFTER records are SELECTED from the data-base and BEFORE execution of the ORDER BY clause.

Syntax for the ROWNUM function in Oracle SQL / PLSQL is:

SELECT column(s)
FROM table_name
WHERE ROWNUM < n;

Example 1:
Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

If we write out query as:

SELECT *
FROM employee
WHERE ROWNUM <3;

We will get the following result:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20

Here we can see that the PSEUDO COLUMN ROWNUM has restricted the number of rows in the fetched result to 2.

However ROWNUM always returns no data when used as:
WHERE ROWNUM > n
Or
WHERE ROWNUM BETWEEN n AND m
Or
WHERE ROWNUM IN(a,b,c…n)

INDEX in Oracle SQL – PLSQL

by

In Oracle SQL /PLSQL an INDEX is basically a performance tuning method which allows us to retrieve or fetch the desired records faster.
An INDEX will create an entry for each value that is stored in the indexed columns.

Syntax for creating an INDEX in Oracle SQL / PLSQL is:

CREATE [UNIQUE] INDEX index_name
ON TABLE table_name (column_name1,column_name2, . . . ,column_nameN)
[COMPUTE STATISTICS];

  • The keyword “UNIQUE” indicates that value / combination of values in the indexed column should be unique.
  • COMPUTE STATISTICS keyword tells Oracle to collect the statistics when the index is being created, these statistics are then used by the Oracle Optimizer to choose the best plan for the execution of any SQL statement.

Example:

CREATE INDEX employee_index
ON TABLE employee (employee_id);

Here we have created an index named ‘employee_index’ on the ‘employee’ table it contains only one column ‘employee_id’.

We can create indexes with more than one columns as shown below:

CREATE INDEX employee_index
ON TABLE employee (employee_id, employee_name);

Here we have created an index named ‘employee_index’ on the ‘employee’ table it contains two columns ‘employee_id’ and ‘employee_name’.

Creating Function Based Index:

In Oracle we can not only create indexes based on columns but we can also create indexes based on functions.

Syntax for creating a FUNCTION BASED INDEX in Oracle SQL / PLSQL is:

CREATE [UNIQUE] INDEX index_name
ON TABLE table_name (function_name1,function_name2, . . . . function_nameN)
[COMPUTE STATISTICS];

Example:

CREATE INDEX emp_name_index
ON TABLE employee (UPPER(employee_name));

Here we have created an index named ‘emp_name_index’ based on the uppercase evaluation of the ‘employee_name’ column in the ‘employee’ table’.

Renaming an INDEX:

The syntax to RENAME an INDEX in Oracle SQL / PLSQL is:

ALTER INDEX index_name
RENAME TO new_index_name;

Example:

ALTER INDEX employee_index
RENAME TO emp_index;

Above statement will rename the ’employee_index’ to ’emp_index’.

Rebuilding an INDEX to compute statistics:

If we forget to COMPUTE STATISTICS while creating an INDEX we can REBUILD the index again to compute the statistics:

Syntax to REBUILD the INDEX in Oracle SQL / PLSQL is:

ALTER INDEX index_name
REBUILD COMPUTE STATISTICS;

Example:

ALTER INDEX employee_index
REBUILD COMPUTE STATISTICS;

In the above statement we are collecting the statistics for the ‘employee_index’.

Dropping an INDEX:

Syntax for dropping an INDEX in Oracle SQL / PLSQL is:

DROP INDEX index_name;

Example:

DROP INDEX employee_index;