Category: Oracle

Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management.

Oracle query to find alphanumeric data in column

Sample Data SELECT ‘3874’ col FROM DUAL UNION ALL SELECT ’74’ col FROM DUAL UNION ALL SELECT ‘123’ col FROM DUAL UNION ALL SELECT ’22FRE’ col FROM DUAL UNION ALL SELECT ‘ABCD’ col FROM DUAL UNION ALL SELECT ‘AB33FRE’ col FROM DUAL Query Syntax SELECT COLUMN_NAME FROM TABLE_NAME WHERE REGEXP_LIKE(COLUMN_NAME,'[[:alpha:]]’) AND REGEXP_INSTR(COLUMN_NAME,'[0-9]’)>0; Output COLUMN_NAME 22FRE […]

SQL DBA

SQL DBA SQL statement for creating table space Syntax CREATE TABLESPACE <TablespaceName> DATAFILE ‘<Datafile Path>’ SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED ; Example CREATE TABLESPACE TUTORIALTBS DATAFILE ‘C:oraclexeoradataXETUTORIALTBS.DBF’ SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED ;   SQL statement for alter table space — ADD DATAFILE Syntax ALTER TABLESPACE <TablespaceName> ADD DATAFILE ‘<Datafile Path>’ SIZE 4G […]

SQL UPDATE

SQL UPDATE The UPDATE command is used to change or update data values in a table. UPDATE all rows one column Syntax UPDATE <TableName> SET <ColumnName 1>=<Expression> or <Value> Example UPDATE EMPLOYEE SET COUNTRY=”INDIA”; UPDATE all rows more than one column Syntax UPDATE <TableName> SET <ColumnName 1>=<Expression> or <Value>, <ColumnName 2>=<Expression> or <Value> Example UPDATE […]

SQL INSERT

SQL INSERT DATA INTO A TABLE FROM ANOTHER TABLE We can insert data into table in two different ways. They are 1) Using INSERT INTO without using SELECT statement. Syntax INSERT INTO <TableName>(<ColumnName1>,<ColumnName2>) VALUES (<ColumnValue1>,<ColumnValue2>); Example INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME) VALUES (‘Narayana’,’Ragi’); 2) Using INSERT INTO with SELECT statement Syntax INSERT INTO <TableName> SELECT […]

SQL CREATE

SQL CREATE TABLE FROM ANOTHER TABLE We can create tables two ways they are 1) Using CREATE TABLE by specifying columns names, columns data types, columns data length. Syntax: CREATE TABLE <TableName> ( <ColumnName1> <DataType>(Length), <ColumnName2> <DataType>(Length)); Example CREATE TABLE EMPLOYEE (NAME Varchar2(20), ID Number(10)); 2) Using CREATE TABLE statement for target table and SELECT […]

SQL ORDER BY

SQL ORDER BY Oracle allow data from a table to be viewed in a sorted order. The rows retrieved from the table will be sorted in either ascending or descending order depending on the condition specified in the SELECT statement. Syntax SELECT * FROM <TableName> ORDER BY <ColumnName1>,<ColumnName2> <[Sort Order]> The ORDER BY clause will […]