Get Oracle Version ( SQL )

You can get all information about Oracle Version ( SQL ). Below Examples,

Get Oracle Version ( SQL ) Database Version

select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production  

Get Oracle Version ( SQL ) Select Full Product Details

select * from product_component_version;PRODUCT
----------------------------------------------------------------
VERSION
----------------------------------------------------------------
STATUS
----------------------------------------------------------------
NLSRTL
10.2.0.2.0
ProductionOracle Database 10g Enterprise Edition
10.2.0.2.0
ProdPRODUCT
----------------------------------------------------------------
VERSION
----------------------------------------------------------------
STATUS
----------------------------------------------------------------PL/SQL
10.2.0.2.0
Production

TNS for Solaris:
10.2.0.2.0

PRODUCT
----------------------------------------------------------------
VERSION
----------------------------------------------------------------
STATUS
----------------------------------------------------------------
Production

Get Oracle Version ( SQL ) Select Version With Banner

select * from v$version where banner like 'Oracle%';

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
Select Version With Banner

select * from v$version where banner like 'PL/SQL%';
BANNER
----------------------------------------------------------------
PL/SQL Release 10.2.0.2.0 - Production

 

Create table In Oracle Simple Example

Create table In Oracle

The Data Definition Language (DDL) manages table and index structure. The most basic items of DDL are the CREATE, ALTER, RENAME, DROP and TRUNCATE statements. Here we see about Create Table with examples

Create Table Statement

A CREATE TABLE statement creates a table. Tables contain columns and constraints, rules to which data must conform. Table-level constraints specify a column or columns. Columns have a data type and can specify column constraints (column-level constraints).

Create table In Oracle Syntax:

CREATE TABLE TABLENAME (
     field1 data_type,
     field2 data_type,
     ...
     ...
     fieldn data_type
 )

Create table In Simple Example Declaration:

here,
 Consider
 Two Fields;
 1) E_ID as number
 2) E_Name as varchar(20)

Create table In Oracle Simple Example Code:

CREATE TABLE Example
 CREATE TABLE EMPLOYEE
     (
     E_ID NUMBER,
     E_NAME VARCHAR2(20)
     );

Drop In Oracle

Drop In Oracle:

Assume table name is EMPLOYEE

DROP TABLE_NAME;
DROP EMPLOYEE;

Count In Oracle SQL

Assume table name is EMPLOYEE and it has E_NAME Field.

Count In Oracle SQL  : All Records In Table

SELECT COUNT(*) FROM EMPLOYEE;

Count In Oracle SQL : All Records In Table with Head Name

SELECT COUNT(*) AS EMPLOYYE_COUNT FROM EMPLOYEE;

Count Column Records In Table

SELECT COUNT(E_NAME) FROM EMPLOYEE;

Count Distinct Column Records In Table

SELECT COUNT(DISTINCT E_NAME) FROM EMPLOYEE;

Count In Oracle SQL : Selected Records In Table

SELECT COUNT(*) FROM EMPLOYEE WHERE CONDITION E_NAME = 'BOND';

Count In Oracle SQL : Selected Records with Head Name

SELECT COUNT(*) FROM EMPLOYEE NAME_BOND WHERE CONDITION E_NAME = 'BOND';

Delete In Oracle SQL

Assume table name is EMPLOYEE and it has E_NAME Field.

Delete In Oracle SQL : All Rows In Tables

DELETE TABLE_NAME;
DELETE EMPLOYEE;

Delete In Oracle SQL : All Rows In Tables

DELETE FROM TABLE_NAME;
DELETE FROM EMPLOYEE;

Delete In Oracle SQL : Selected Rows In Tables

DELETE FROM TABLE_NAME WHERE CONDITION;
DELETE FROM EMPLOYEE WHERE E_NAME = 'RAJU';

Delete In Oracle SQL : through Select Rows In Tables

DELETE FROM (SELECT * FROM TABLE_NAME WHERE CONDITION);
DELETE FROM (SELECT * FROM EMPLOYEE WHERE E_NAME = 'RAJU');

Get Date Time In Oracle

Date Time In Oracle: Date From System In Oracle

SELECT SYSDATE FROM DUAL;

Date Time In Oracle: Date From System In Oracle

SELECT TO_CHAR(SYSDATE,'YYYY MM DD') FROM DUAL;

Date Time In Oracle: Time From System In Oracle

SELECT TO_CHAR(SYSDATE,'HH MI SS') FROM DUAL;

Date Time In Oracle: Date From System with Heading In Oracle

SELECT TO_CHAR(SYSDATE,'YYYY MM DD') TODAY_DATE FROM DUAL;

Date Time In Oracle: Time From System with Heading In Oracle

SELECT TO_CHAR(SYSDATE,'HH MI SS') NOW_TIME FROM DUAL;

Date Time In Oracle: Time(24 Hours) From System with Heading In Oracle

SELECT TO_CHAR(SYSDATE,'HH24 MI SS') NOW_TIME FROM DUAL;

Date Time In Oracle: Time(12 Hours) From System with Heading In Oracle

SELECT TO_CHAR(SYSDATE,'HH MI SS') NOW_TIME FROM DUAL;

Date Time In Oracle: Date & Time(12 Hours) From System with Heading In Oracle

SELECT TO_CHAR(SYSDATE,'YYYY MM DD HH24 MI SS') DAT_TIME FROM DUAL;

Create Sequence In Oracle

Sequence In Oracle Definition:

You can create for auto increment number/ID creation. Oracle generates sequence of number depend upon our code boundary.

Sequence In Oracle Syntax:

CREATE SEQUENCE SEQUENCE_NAME

        MINVALUE VALUE        ( Assign Minimum Value )

        MAXVALUE VALUE        ( Assign Maximum Value )

        START WITH VALUE    ( Assign Start Value )

        INCREMENT BY VALUE;    ( Assign Increment Value )

Sequence In Oracle Example:

CREATE SEQUENCE DUMMY_SEQUENCE

    MINVALUE 1

    MAXVALUE 10000

    START WITH 1

    INCREMENT BY 1

    CACHE 20;

Sequence Usage:

    DUMMY_SEQUENCE.NEXTVAL

Sequence Inside Query

INSERT INTO DUMMY_PERSONAL_DTLS

(DUMMY_ID,DUMMY_FNAME,DUMMY_LANME)

VALUES

(DUMMY_SEQUENCE.NEXTVAL,'RAJINI','THIYAGARAAJ');