create

create table date_demo(hiredate   DATE);

default format of date is: DD-MON-RR

Date -> allows Date/Time(default format is dd-mon-rr)

DROP TABLE DATE_DEMO;

CREATE TABLE DATE_DEMO(HIREDATE DATE);

INSERT INTO DATE_DEMO VALUES('01-JAN-14');

SELECT * FROM DATE_DEMO;

INSERT INTO DATE_DEMO VALUES('01/JAN/14');

select * from date_demo;

INSERT INTO DATE_DEMO VALUES('JAN-01-14'); -- will give error

ALTER SESSION SET nls_date_format='DD-MON-YY';

truncate table date_demo;

insert into date_demo values('01-jan-85');

insert into date_demo values('01-jan-15');

insert into date_demo values('01-jan-05');

yy FORMAT:

----------

IN YY FORMAT WHATEVER YEAR VALUE WE GIVES, IT WILL TAKE CURRENT CENTURY ONLY.

RR FORMAT:

----------

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR';

0-49       50-99

IN THE RR FORMAT CENTURY WILL BE DIVIDED INTO 50-50 YEARS.

WHEN WE ENTER DATE VALUE IN THE COLUMN, IT WILL COMPARE THE ENTERED YEAR WITH THE CURRENT YEAR.

IF CURRENT YEAR AND THE YEAR WHICH WE ENTER COMES IN THE SAME RANGE IT WILL TAKE CURRENT CENTURY.

IF THE CURRENT YEAR VALUE IS IN FIRST RANGE AND THE YEAR VALUE WHICH WE ENTER IS IN SECOND RANGE,

THEN IT WILL TAKE LAST CENTURY.

IF THE CURRENT YEAR VALUE IS IN SECOND RANGE AND THE YEAR VALUE WHICH WE ENTER IS IN FIRST RANGE,

THEN IT WILL TAKE NEXT CENTURY.

TRUNCATE TABLE DATE_DEMO;

INSERT INTO DATE_DEMO VALUES('12-JAN-05');

INSERT INTO DATE_DEMO VALUES('12-JAN-85');

INSERT INTO DATE_DEMO VALUES('12-JAN-1885');

ASSUME THAT MY CURRENT YEAR IS 19-FEB-60.

INSERT INTO DATE_DEMO VALUES('12-JAN-85');

HERE BOTH CURRENT YEAR AND THE YEAR WHICH I ENTER COMES IN SECOND RANGE, SO IT

WILL TAKE CURRENT CENTURY. 12-JAN-2085.

ASSUME THAT MY CURRENT YEAR IS 19-FEB-60.

INSERT INTO DATE_DEMO VALUES('12-JAN-15');

HERE CURRENT YEAR COMES IN SECOND RANGE AND THE YEAR WHICH I ENTERED COMES IN

FIRST RANGE, SO IT WILL TAKE NEXT CENTURY.  12-JAN-2115.

select to_char(hiredate, 'dd-mon-yyyy') from date_demo;

-- to create table in your schema you require CREATE TABLE privilege.

--SYS user will be giving this privilege.

To create table we will CREATE statement. CREATE statement is DDL statement.

                           

CREATE TABLE

UNLIMITED TABLESPACE

CREATE ANY TABLE

DROP TABLE STUDENTS1;

SYNTAX:

CREATE TABLE <TABLENAME> (<COLUMNNAME> DATATYPE(SIZE) [CONSTRAINT <CONSTRAINTNAME> CONSTRAINT TYPE]

[DEFAULT value], <COLNAME2 DATATYPE(SIZE)...............);

CREATE TABLE students1(sid NUMBER(4), sname VARCHAR2(20) DEFAULT 'xyz',

marks NUMBER(3) DEFAULT 0);

SELECT * FROM STUDENTS1;

DESCRIBE STUDENTS1;

DESC STUDENTS1;

SYNTAX:

INSERT INTO <TABLENAME> [(COLUMN NAMES)] VALUES (data for the columns);

INSERT INTO students1 VALUES(1001, 'john', 85);

INSERT INTO STUDENTS1 VALUES(1002, DEFAULT, DEFAULT);

INSERT INTO STUDENTS1 VALUES(1003, ' ', NULL);

INSERT INTO STUDENTS1(SID, SNAME) VALUES(1004, 'SKP');

SELECT * FROM students1;

INSERT INTO students1(SNAME) VALUES('AKP');

SELECT * FROM students1;

CREATE TABLE EMP_NEW AS SELECT * FROM EMP;

SELECT * FROM EMP;

SELECT * FROM EMP_NEW;

CREATE TABLE EMP_NEW2 AS SELECT * FROM EMP WHERE 1=2;

SELECT * FROM EMP_NEW2;

DESC EMP_NEW2;