How to make first letter uppercase

By using INITCAP function it is possible to make first letter of word UPPERCASE

example

select INITCAP('sample initcap') from dual

result: Sample Initcap

How to find a word with different endings in the string

SELECT

REGEXP_SUBSTR('I have earned 100 dollars today ','earn((ing)|(ed))')

FROM dual;

result : earned

If you change the string

SELECT

REGEXP_SUBSTR('I have been earning 100 dollars today ','earn((ing)|(ed))')

FROM dual;

result: earning

How to add first name first letter to last name name

By using regular expression replace it is possible to add first letter of name to the beginning of the last name.

SELECT LOWER(regexp_replace('Lucky, Edy E', '(.+)(, )([A-Z])(.+)','\3\1', 1, 1))

  FROM DUAL;

Result : elucky

How to find alphabetic characters (a-z, A-Z)

By using '[[:alpha:]]' with regular expressions it is possible to find 
alphabetic characters. By adding {3} at the end we limit the length of matching 
alphabetic characters.


Select * from test_table where REGEXP_LIKE(test_column, '[[:alpha:]]');

Select * from test_table where REGEXP_LIKE(test_column, '[[:alpha:]]{3}');

How to find alphanumeric characters (a-z, A-Z, 0-9)

By using '[[:alnum:]]' with regular expressions it is possible to find 

alphanumeric characters.By adding {3} at the end we limit the length of 

matching alphanumeric characters.

Select * from test_table where REGEXP_LIKE(test_column, '[[:alnum:]]');

Select * from test_table where REGEXP_LIKE(test_column, '[[:alnum:]]{3}');

How to find repeating substring within string

By using regular expression it is possible to find repeating substring within string

select 

        regexp_replace('telltell','^(.+?)\1*$','\1') sub_str

  from  dual

How to find first character of string

By suing SUBSTR  it is possible to find it.

 select SUBSTR ('testing est for test', 1, 1) from dual

resul:t

How to remove repeated character from string

By using  regular experssion it is possible to remove repeated characters from string

Example

select regexp_replace('Tesstt', '(.)\1+','\1') 
  from dual;

Result: Test

How to find last character in the string

By using SUBSTR function it is possible to find last character in string.

Select SUBSTR('last character', -1) from dual

result: r

Ceil Function

In Oracle/PLSQL, the ceil function returns the smallest integer value that is greater than or equal to a number.
The syntax for the ceil function is: ceil( number ) number is the value used to find the smallest integer value.

 For Example
ceil(32.65) would return 33
ceil(32) would return 32
ceil(-32.65) would return -32
ceil(-32) would return -32

Views, Materialized Views and Synonyms

Views

A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used. All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.
In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).
Using views encourages the use of Shared SQL with the benefit of reduced memory usage.
Read-Only vs Updatable Views
The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS, and USER_UPDATABLE_COLUMNS indicate which view columns are updatable.
An updatable view lets you insert, update, and delete rows in the view and propagate the changes to the target master table.
In order to be updatable, a view cannot contain any of the following constructs: SET or DISTINCT operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY, or START WITH clause, a subquery (or collection expression) in a SELECT list or finally (with some exceptions) a JOIN . See the Oracle Database Administrator's Guide for full details. Views that are not updatable can be modified using an INSTEAD OF trigger.

Materialized Views

Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.
A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.
The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.
You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.
A materialized view can be stored in the same database as its base table(s) or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.
A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.

Synonyms

A synonym is an alias for any table, view, materialized view, sequence, procedure, function, or package. A public synonym is owned by the user group PUBLIC and every user in a database can access it. A private synonym is in the schema of a specific user who has control over its availability to others.
Synonyms are used to:
- Mask the real name and owner of a schema object
- Provide global (public) access to a schema object
- Provide location transparency for tables, views, or program units of a remote database.
- Simplify SQL statements for database users
e.g. to query the table PATIENT_REFERRALS with SQL:
SELECT * FROM MySchema.PATIENT_REFERRALS;
CREATE PUBLIC SYNONYM referrals FOR MySchema.PATIENT_REFERRALS;
After the public synonym is created, you can query with a simple SQL statement:
SELECT * FROM referrals;

How to bring sentence till the second occurrence of mentioned word

Bu using regular expression it is possible to find sentence till the second occurrence of mentioned word.

select 

regexp_replace('Payables A 2204216 22490587 Payables A 15 2648191', '^(.*)Payables.*$', '\1') p_result

from dual

select 
substr('Payables A 2204216 22490587 Payables A 15 22648191',1
,instr('Payables A 2204216 22490587 Payables A 15 22648191', 'Payables',1,2)-1)  NAMES
from dual;

How to find number of words in the string

By using length function it is possible to find number of words in the sentence.  First, we find the length of the string and length of the same string without spaces. The difference between them plus 1 will be the number of words in the sentence.

SELECT LENGTH('Oracle PL SQL') - LENGTH(REPLACE('Oracle PL SQL', ' ', '')) + 1 WordCount

from DUAL

PROCEDURES

This Oracle tutorial explains how to create and drop procedures in Oracle/PLSQL with syntax and examples.

CREATE PROCEDURE

Just as you can in other languages, you can create your own procedures in Oracle.

Syntax

The syntax to create a procedure in Oracle is:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]

IS
    [declaration_section]

BEGIN
    executable_section

[EXCEPTION
    exception_section]

END [procedure_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

1. IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
2. OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

Example

Let's look at an example of how to create a procedure in Oracle.

The following is a simple example of a procedure:

CREATE OR REPLACE Procedure UpdateCourse
   ( name_in IN varchar2 )

IS
   cnumber number;

   cursor c1 is
   SELECT course_number
    FROM courses_tbl
    WHERE course_name = name_in;

BEGIN

   open c1;
   fetch c1 into cnumber;

   if c1%notfound then
      cnumber := 9999;
   end if;

   INSERT INTO student_courses
   ( course_name,
     course_number )
   VALUES
   ( name_in,
     cnumber );

   commit;

   close c1;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

This procedure is called UpdateCourse. It has one parameter called name_in. The procedure will lookup the course_number based on course name. If it does not find a match, it defaults the course number to 99999. It then inserts a new record into the student_courses table.

DROP PROCEDURE

Once you have created your procedure in Oracle, you might find that you need to remove it from the database.

Syntax

The syntax to a drop a procedure in Oracle is:

DROP PROCEDURE procedure_name;

procedure_name

The name of the procedure that you wish to drop.

Example

Let's look at an example of how to drop a procedure in Oracle.

For example:

DROP PROCEDURE UpdateCourse;

This example would drop the procedure called UpdateCourse

FUNCTIONS

 

This Oracle tutorial explains how to create and drop functions in Oracle/PLSQL with syntax and examples.

CREATE FUNCTION

Just as you can in other languages, you can create your own functions in Oracle.

Syntax

The syntax to create a function in Oracle is:

CREATE [OR REPLACE] FUNCTION function_name
   [ (parameter [,parameter]) ]

   RETURN return_datatype

IS | AS

   [declaration_section]

BEGIN
   executable_section

[EXCEPTION
   exception_section]

END [function_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

1. IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
2. OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

Example

Let's look at an example of how to create a function in Oracle.

The following is a simple example of an Oracle function:

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
   cnumber number;

   cursor c1 is
   SELECT course_number
     FROM courses_tbl
     WHERE course_name = name_in;

BEGIN

   open c1;
   fetch c1 into cnumber;

   if c1%notfound then
      cnumber := 9999;
   end if;

   close c1;

RETURN cnumber;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

This function is called FindCourse. It has one parameter called name_in and it returns a number. The function will return the course number if it finds a match based on course name. Otherwise, it returns a 99999.

You could then reference your new function in a SQL statement as follows:

SELECT course_name, FindCourse(course_name) AS course_id
FROM courses
WHERE subject = 'Mathematics';

DROP FUNCTION

Once you have created your function in Oracle, you might find that you need to remove it from the database.

Syntax

The syntax to a drop a function in Oracle is:

DROP FUNCTION function_name;

function_name

The name of the function that you wish to drop.

Example

Let's look at an example of how to drop a function in Oracle.

For example:

DROP FUNCTION FindCourse;

This example would drop the function called FindCourse.

COMMENTS

This SQL tutorial explains how to use comments within your SQL statements with syntax and examples.

DESCRIPTION

In SQL, you can comment your code just like any other language. Comments can appear on a single line or span across multiple lines. Let's explore how to comment your SQL statements.

SYNTAX

There are two syntaxes that you can use to create a comment in SQL.

Syntax Using -- symbol

The syntax for creating a comment in SQL using -- symbol is:

-- comment goes here

A comment started with -- symbol must be at the end of a line in your SQL statement with a line break after it. This method of commenting can only span a single line within your SQL and must be at the end of the line.

Syntax Using /* and */ symbols

The syntax for creating a comment in SQL using /* and */ symbols is:

/* comment goes here */

A comment that starts with /* symbol and ends with */ and can be anywhere in your SQL statement. This method of commenting can span several lines within your SQL.

EXAMPLE - COMMENT ON A SINGLE LINE

Let's look at an example that shows how to create a comment in SQL that is on a single line.

For example:

Here is a comment that appears on its own line in SQL:

SELECT websites.site_name
/* Author: TechOnTheNet.com */
FROM websites;

Here is a comment that appears in the middle of the line in SQL:

SELECT  /* Author: TechOnTheNet.com */  websites.site_name
FROM websites;

Here is a comment that appears at the end of the line in SQL:

SELECT websites.site_name  /* Author: TechOnTheNet.com */
FROM websites;

or

SELECT websites.site_name  -- Author: TechOnTheNet.com
FROM websites;

EXAMPLE - COMMENT ON MULTIPLE LINES

You can also create a comment that spans multiple lines in your SQL statement.

For example:

SELECT websites.site_name
/*
 * Author: TechOnTheNet.com
 * Purpose: To show a comment that spans multiple lines in your SQL statement.
 */
FROM websites;

This comment spans across multiple lines in SQL and in this example, it spans across 4 lines.

You can also create a comment that spans multiple lines using this syntax:

SELECT websites.site_name /* Author: TechOnTheNet.com
Purpose: To show a comment that spans multiple lines in your SQL statement. */
FROM websites;

SQL will assume that everything after the /* symbol is a comment until it reaches the */ symbol, even if it spans multiple lines within the SQL statement. So in this example, the comment will span across 2 lines.

How to find the first day of a month

By using the format string MM to extract the month, and then using the DAY format to return the first day of the month within the date , it is possible to find first day of a month:

select
   to_char(
      trunc(:p_date,'MM'),'DAY'
       )
from dual;

How to find dates between two given dates

By using below query, it is possible to generate dates between to given dates. 

SELECT start_date - 1 + rownum as p_date
FROM all_objects
WHERE start_date - 1 + rownum <= end_date

VIEW

This SQL tutorial explains how to create, update, and drop SQL VIEWS with syntax and examples.

DESCRIPTION

The SQL VIEW is, in essence, a virtual table that does not physically exist. Rather, it is created by a SQL statement that joins one or more tables.

CREATE SQL VIEW

Syntax

The syntax for the SQL CREATE VIEW Statement is:

CREATE VIEW view_name AS
  SELECT columns
  FROM tables
  [WHERE conditions];

view_name

The name of the SQL VIEW that you wish to create.

WHERE conditions

Optional. The conditions that must be met for the records to be included in the VIEW.

Example

Here is an example of how to use the SQL CREATE VIEW:

CREATE VIEW sup_orders AS
  SELECT suppliers.supplier_id, orders.quantity, orders.price
  FROM suppliers
  INNER JOIN orders
  ON suppliers.supplier_id = orders.supplier_id
  WHERE suppliers.supplier_name = 'IBM';

This SQL CREATE VIEW example would create a virtual table based on the result set of the select statement. You can now query the SQL VIEW as follows:

SELECT *
FROM sup_orders;

UPDATE SQL VIEW

You can modify the definition of a SQL VIEW without dropping it by using the SQL CREATE OR REPLACE VIEW Statement.

Syntax

The syntax for the SQL CREATE OR REPLACE VIEW Statement is:

CREATE OR REPLACE VIEW view_name AS
  SELECT columns
  FROM table
  [WHERE conditions];

Example

Here is an example of how you would use the SQL CREATE OR REPLACE VIEW Statement:

CREATE or REPLACE VIEW sup_orders AS
  SELECT suppliers.supplier_id, orders.quantity, orders.price
  FROM suppliers
  INNER JOIN orders
  ON suppliers.supplier_id = orders.supplier_id
  WHERE suppliers.supplier_name = 'Microsoft';

This SQL CREATE OR REPLACE VIEW example would update the definition of the SQL VIEW called sup_orders without dropping it. If the SQL VIEW did not yet exist, the SQL VIEW would merely be created for the first time.

DROP SQL VIEW

Once a SQL VIEW has been created, you can drop it with the SQL DROP VIEW Statement.

Syntax

The syntax for the SQL DROP VIEW Statement is:

DROP VIEW view_name;

view_name

The name of the view that you wish to drop.

Example

Here is an example of how to use the SQL DROP VIEW Statement:

DROP VIEW sup_orders;

This SQL DROP VIEW example would drop/delete the SQL VIEW called sup_orders.

LOCAL TEMPORARY TABLES

This SQL tutorial explains how to create SQL LOCAL TEMPORARY tables with syntax and examples.

DESCRIPTION

SQL LOCAL TEMPORARY TABLES are distinct within modules and embedded SQL programs within SQL sessions.

SYNTAX

The syntax for SQL DECLARE LOCAL TEMPORARY TABLE is:

DECLARE LOCAL TEMPORARY TABLE table_name
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Parameters or Arguments

table_name

The name of the local temporary table that you wish to create.

column1, column2

The columns that you wish to create in the local temporary table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

EXAMPLE

Let's look at a SQL DECLARE LOCAL TEMPORARY TABLE example:

DECLARE LOCAL TEMPORARY TABLE suppliers_temp
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50)
);

This example would create a LOCAL TEMPORARY TABLE called suppliers_temp.

GLOBAL TEMPORARY TABLES

This SQL tutorial explains how to create SQL GLOBAL TEMORARY tables with syntax and examples.

DESCRIPTION

SQL GLOBAL TEMPORARY TABLES are tables that are created distinct within SQL sessions.

SYNTAX

The syntax for SQL CREATE GLOBAL TEMPORARY TABLE is:

CREATE GLOBAL TEMPORARY TABLE table_name
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Parameters or Arguments

table_name

The name of the global temporary table that you wish to create.

column1, column2

The columns that you wish to create in the global temporary table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

EXAMPLE

Let's look at a SQL CREATE GLOBAL TEMPORARY TABLE example:

CREATE GLOBAL TEMPORARY TABLE suppliers_temp
( supplier_id numeric(10) NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50)
);

DROP TABLE STATEMENT

This SQL tutorial explains how to use the SQL DROP TABLE statement with syntax and examples.

DESCRIPTION

The SQL DROP TABLE statement allows you to remove or delete a table from the SQL database.

SYNTAX

The syntax for the SQL DROP TABLE statement is:

DROP TABLE table_name;

Parameters or Arguments

table_name

The name of the table to remove from the database.

EXAMPLE

You may have found that you've created a table that you no longer require. You can use the DROP TABLE statement to remove the table from your database.

Let's look at an example that shows how to drop a table using the DROP TABLE statement.

For example:

DROP TABLE suppliers;

This DROP TABLE statement example would drop the table called suppliers. This would both remove the records associated with the suppliers table as well as its table definition.

Once you have dropped the table, you can recreate the suppliers table without getting an error that the table already exists.

Let's look at one more example where we prefix the table name with the database name.

For example:

DROP TABLE totn.contacts;

In this example, e have dropped a table called contacts that is in the totn database.

ALTER TABLE STATEMENT

This SQL tutorial explains how to use the SQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with lots of clear, concise examples). We've also added some practice exercises that you can try for yourself.

DESCRIPTION

The SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table.

ADD COLUMN IN TABLE

Syntax

To add a column in a table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD column_name column-definition;

Example

Let's look at a SQL ALTER TABLE example that adds a column.

For example:

ALTER TABLE supplier
  ADD supplier_name char(50);

This SQL ALTER TABLE example will add a column called supplier_name to the supplier table.

ADD MULTIPLE COLUMNS IN TABLE

Syntax

To add multiple columns to an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD (column_1 column-definition,
       column_2 column-definition,
       ...
       column_n column_definition);

Example

Let's look at SQL ALTER TABLE example that adds more than one column.

For example:

ALTER TABLE supplier
  ADD (supplier_name char(50),
       city char(45));

This SQL ALTER TABLE example will add two columns, supplier_name as a char(50) field and city as a char(45) field to thesupplier table.

MODIFY COLUMN IN TABLE

Syntax

To modify a column in an existing table, the SQL ALTER TABLE syntax is:

For Oracle, MySQL, MariaDB:

ALTER TABLE table_name
  MODIFY column_name column_type;

For SQL Server:

ALTER TABLE table_name
  ALTER COLUMN column_name column_type;

For PostgreSQL:

ALTER TABLE table_name
  ALTER COLUMN column_name TYPE column_definition;

Example

Let's look at an example of how to modify a column called supplier_name using the ALTER TABLE statement. Note that most databases have a slightly different syntax.

For Oracle:

ALTER TABLE supplier
  MODIFY supplier_name char(100) NOT NULL;

For MySQL and MariaDB:

ALTER TABLE supplier
  MODIFY supplier_name VARCHAR(100) NOT NULL;

For SQL Server:

ALTER TABLE supplier
  ALTER COLUMN supplier_name VARCHAR(100) NOT NULL;

For PostgreSQL:

ALTER TABLE supplier
  ALTER COLUMN supplier_name TYPE CHAR(100),
  ALTER COLUMN supplier_name SET NOT NULL;

MODIFY MULTIPLE COLUMNS IN TABLE

Syntax

To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is:

For Oracle:

ALTER TABLE table_name
  MODIFY (column_1 column_type,
          column_2 column_type,
          ...
          column_n column_type);

For MySQL and MariaDB:

ALTER TABLE table_name
  MODIFY column_1 column_definition
    [ FIRST | AFTER column_name ],
  MODIFY column_2 column_definition
    [ FIRST | AFTER column_name ],
  ...
;

For PostgreSQL:

ALTER TABLE table_name
  ALTER COLUMN column_name TYPE column_definition,
  ALTER COLUMN column_name TYPE column_definition,
  ...
;

Example

Let's look at an example that uses the ALTER TABLE statement to modify more than one column. In this example, we will modify two columns called supplier_name and city.

For Oracle:

ALTER TABLE supplier
  MODIFY (supplier_name char(100) NOT NULL,
          city char(75));

For MySQL and MariaDB:

ALTER TABLE supplier
  MODIFY supplier_name VARCHAR(100) NOT NULL,
  MODIFY city VARCHAR(75);

For PostgreSQL:

ALTER TABLE supplier
  ALTER COLUMN supplier_name TYPE CHAR(100),
  ALTER COLUMN supplier_name SET NOT NULL,
  ALTER COLUMN city TYPE CHAR(75);

DROP COLUMN IN TABLE

Syntax

To drop a column in an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  DROP COLUMN column_name;

Example

Let's look at an example that drops (ie: deletes) a column from a table.

For example:

ALTER TABLE supplier
  DROP COLUMN supplier_name;

This SQL ALTER TABLE example will drop the column called supplier_name from the table called supplier.

RENAME COLUMN IN TABLE

Syntax

To rename a column in an existing table, the SQL ALTER TABLE syntax is:

For Oracle and PostgreSQL:

ALTER TABLE table_name
  RENAME COLUMN old_name TO new_name;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'table_name.old_column', 'new_name', 'COLUMN';

For MySQL and MariaDB:

ALTER TABLE table_name
  CHANGE COLUMN old_name TO new_name;

Example

Let's look at an example that renames a column in the supplier table from supplier_name to sname.

For Oracle (9i Rel2 and up) and PostgreSQL:

ALTER TABLE supplier
  RENAME COLUMN supplier_name TO sname;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'supplier.supplier_name', 'sname', 'COLUMN';

For MySQL and MariaDB:

ALTER TABLE supplier
  CHANGE COLUMN supplier_name sname VARCHAR(100);

In MySQL and MariaDB, you must specify the data type of the column when you rename it.

RENAME TABLE

Syntax

To rename a table, the SQL ALTER TABLE syntax is:

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:

ALTER TABLE table_name
  RENAME TO new_table_name;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'table_name', 'new_table_name';

Example

Let's look at an example that renames a table called supplier to the new name vendor.

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:

ALTER TABLE supplier
  RENAME TO vendor;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'supplier', 'vendor';