Literals,data type

Literals:

=========

SELECT SAL FROM EMP;

SELECT SAL * 12 FROM EMP;

Literal Values are the values given by the user which is not taken from the database.

Literal values of three types. 1. Character literals, 2. date literals, 3. Number Literals.

1. Character literal(should be enclosed in single quotes and it is case sensitive) - eg, 'John'

2. Date literal (should be enclosed in single quotes and it is format sensitive)- eg, '22-Oct-13'

3. Numeric literal(should not be enclosed in single quotes) eg, 10000

SELECT IT_AMERICA FROM emp; -- invalid

SELECT 'IT_AMERICA' FROM emp;

SELECT 'IT_AMERICA' FROM DUAL;

select sysdate from dual;

SELECT 'IT_AMERICA' AS NAME FROM DUAL;

desc dual;

SELECT * FROM DUAL;

DUAL: is the dummy table, which is having only one row and one column.

      sys user is the owner of the dual  table, but other users also can use it.

      generally dual table we will be using with the literal values.

SELECT '21-JAN-2014' FROM DUAL;

SELECT 21-JAN-2014 FROM DUAL; -- invalid

SELECT '21-JAN-2014' FROM EMP;

SELECT 10000 FROM DUAL;

DD-MON-RR

'29-DEC-2013' -> accepted as date literal

'09/06/2013' -> not accepted as date, hence it is character literal

100 -> numeric literal

Datatypes

=========

DATE -> useful to store date value(1 jan 4712 B.C to 31 Dec 9999 A.D).

DEFAULT FORMAT OF THE DATE VALUE IS DD-MON-RR

create table date_demo1(today_date  DATE);

insert into date_demo1 values('12-jan-2014');

default date format is DD-MON-RR.

select * from date_demo1;

TIMESTAMP(n)

TIMESTAMP(n) WITH TIME zone

TIMESTAMP(n)WITH local TIME zone

CREATE TABLE ts(col1  DATE, col2 TIMESTAMP(3), col3 TIMESTAMP(3) WITH TIME ZONE,

col4 TIMESTAMP(3) WITH LOCAL TIME ZONE);

INSERT INTO TS VALUES(sysdate, sysdate, sysdate, sysdate);

SELECT * FROM ts;

ALTER session SET time_zone='-5:00';

SELECT * FROM ts;

NUMBER -> useful to store number value along with decimals(precision->1 to 38,

scale - -84 to 127) eg, NUMBER(8,2) here, 8 is precision and 2 is scale

(precision = integer + decimals and scale = decimals).

create table num_demo1(sal number(4));

insert into num_demo1 values(58989);

insert into num_demo1 values(5898);

insert into num_demo1 values(589);

create table num_demo2(salary number(6,2));

insert into num_demo2 values(5678.4567);

insert into num_demo2 values(56788.4567);

select * from num_demo2;

create table num_demo3(tax number(2,2));

insert into num_demo3 values(33.33);

insert into num_demo3 values(.33);

select * from num_demo3;

insert into num_demo3 values(33);

insert into num_demo3 values(.5678);

select * from num_demo3;

CHAR - > useful to store fixed length character values (1 TO 2000 bytes) eg,char(8)

If i store 'John' in this datatype column it will occupy all the 8 bytes eventhough

if we less characters, in this example it will occupy 4 bytes for john and remaining

4 bytes is free but still it will occupy it will not release.

CREATE TABLE PERSON(EMPID NUMBER(4), SNAME CHAR(8));

INSERT INTO PERSON VALUES(100, 'RAM');

SELECT * FROM PERSON;

SELECT EMPID, LENGTH(SNAME) FROM PERSON;

SELECT EMPID, SNAME, LENGTH(SNAME) FROM PERSON;

VARCHAR -> useful to store variable length character values(2000 bytes) eg,

varchar(8) if i store the value 'john' it will occupy 4 bytes and remaining 4 bytes

it will release.

VARCHAR2 - > useful to store variable length character values(4000 bytes), it is

same as varchar but its size is double.

abc -> 3 bytes

dname char(10)

accounting -> 10

research -> 10

sales -> 10

drop table char_demo;

CREATE TABLE CHAR_DEMO(DNAME  CHAR(10));

INSERT INTO CHAR_DEMO VALUES('SALES');

SELECT * FROM CHAR_DEMO;

SELECT LENGTH(DNAME) FROM CHAR_DEMO;

drop table varchar_demo;

CREATE TABLE VARCHAR_DEMO(DNAME VARCHAR2(10));

INSERT INTO VARCHAR_DEMO VALUES('SALES');

SELECT * FROM VARCHAR_DEMO;

SELECT LENGTH(DNAME) FROM VARCHAR_DEMO;

dname varchar2(10)

accounting -> 10

research -> 8

sales -> 5

varchar(2000) -> max is 2000 bytes

varchar2(4000) -> max is 4000 bytes

ASCII -> American Standard Code for Information Interchange

a-97(decimal number) -> binary number (0's and 1's)

0->1 bit

1->1 bit

97 -> binary equivalent is -> 01100001 -> 8 bits -> 1byte

 2  97

    48 - 1

    24 - 0

    12 - 0

    6 - 0

    3 - 0

    1 - 1

   

    01100001

   

    1 english alphabet occupies 1 byte

    1 byte = 8 bits -> in each bit each binary value is stored.

    1 kb = 1024 bytes

    1 mb = 1024 kb

    1 gb = 1024 mb

Table named: Product

column: product_name

Date type: char(20)

Sample product name: computer

varchar(2000)

varchar2(4000)

varchar  -> upto 2000 bytes

varchar2 -> upto 4000 bytes

Number(4,2)  -> allows upto 4 digits (either positive or negative)

if you enter any decimal value like 4.2 or 9.8 -> will round and insert value

, but do not reject

DROP TABLE DEMO_TABLE;

CREATE TABLE demo_Table(sal NUMBER(4));

INSERT INTO demo_table VALUES(9.8);

SELECT * FROM DEMO_TABLE;

INSERT INTO demo_table VALUES(4.2);

INSERT INTO demo_table VALUES(4.5);

select * from demo_table;

number(3,2) -> allows upto 1 digit before decimal and 2 digits after decimal

3 -> precision (can range from 1 to 38)

2 -> scale (can range from -84 to 127)

drop table number_demo;

CREATE TABLE NUMBER_DEMO(SAL NUMBER(3,2));

INSERT INTO NUMBER_DEMO VALUES(9.99);

INSERT INTO NUMBER_DEMO VALUES(4.2799);

INSERT INTO NUMBER_DEMO VALUES(124.88);

SELECT * FROM NUMBER_DEMO;

9.99 -> allowed

4.2799 -> rounded to 4.28 and inserted

124.88 -> it will not store