How to extract Month, Year value from date using Extract Function in Oracle

Extract Function in Oracle

The extract function extracts a value from a date. You can only extract YEAR, MONTH, and DAY from a DATE



Syntax:

  EXTRACT (
{ YEAR
MONTH
DAY
HOUR
MINUTE
SECOND }
{ TIMEZONE_HOUR
TIMEZONE_MINUTE }
{ TIMEZONE_REGION
TIMEZONE_ABBR }
FROM { date_value
interval_value } )


• Works in - Oracle 11g, Oracle 10g, Oracle 9i


Sample:



select extract(YEAR FROM sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)

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

2013

1 row selected.





select extract(MONTH FROM sysdate) from dual;


EXTRACT(MONTHFROMSYSDATE)

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

5

1 row selected.



select extract(DAY FROM sysdate) from dual;



EXTRACT(DAYFROMSYSDATE)

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

14

1 row selected.

Read More

INSERT Statement

INSERT Statement

The SQL INSERT statement allows you to insert a single record or multiple records into a table.

Syntax:

INSERT INTO table

(column-1, column-2, ... column-n)

VALUES

(value-1, value-2, ... value-n);



INSERT Statement - Using VALUES keyword example

Sample:

insert into customer (customer_id,customer_name)
values (101,'CustName');



SQL INSERT Statement - Using sub-selects

Sample:

insert into customer (customer_id,customer_name)
select cust_id,first_name from customer_temp;

Read More

CREATE TABLE Statement

The SQL CREATE TABLE statement allows you to create and define a table. Each column must have a data type. 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.




The syntax for the SQL CREATE TABLE statement is:

CREATE TABLE table_name

(
column1 datatype null/not null,
column2 datatype null/not null,
...
);



For Example



CREATE TABLE customer
(
customer_id number(10) not null,
customer_name varchar2(50) null,
address varchar2(50),
city varchar2(50),
state varchar2(25),
zip_code varchar2(10)
);

Read More

SQL Statement Types

SQL Statement Types:

1. DDL – Data Definition Language.

2. DML – Data Manipulation Language.

3. DCL – Data Control languages



DDL Statements:

• Create – it used create objects like Tables,Views and etc…

• Alter – It used to alter the Structure of the Obejects like Tables, Views and etc…

• Truncate – It removes all the records in table.

• Grant – Provides / Assigns Privileges

DML Statements:

• Select – Retrieve data from tables.

• Insert – It helps to insert data in to table.

• Update – It helps to Modify the Existing data in table.

• Delete – It helps to Remove data from table.



DCL Statements:

• Commit – It will ends the current Transaction & making the changes to permanent.

• Savepoint – It will identify a point or Num of transaction in Set of Transactions to which we can later roll back.

• Rollback- It will undo / remove all the changes made by Current transaction.

Read More