Oracle 11 g

 

Q Describe  Oracle Data Types?

Oracle String data types    

NCHAR(size):-          It is used to store national character data within the predefined length. It can be stored up to 2000 bytes.

VARCHAR2(size):-  It is used to store variable string data within the predefined length. It can be stored up to 4000 byte.

VARCHAR(SIZE):- It is the same as VARCHAR2(size). You can also use VARCHAR(size), but it is suggested to use VARCHAR2(size)

NVARCHAR2(size):-           It is used to store Unicode string data within the predefined length. We have to must specify the size of NVARCHAR2 data type. It can be stored up to 4000 bytes.

Oracle Numeric Data Types

NUMBER(p, s)	It contains precision p and scale s. The precision p can range from 1 to 38, and the scale s can range from -84 to 127.
FLOAT(p)	It is a subtype of the NUMBER data type. The precision p can range from 1 to 126.
BINARY_FLOAT	It is used for binary precision( 32-bit). It requires 5 bytes, including length byte.
BINARY_DOUBLE	It is used for double binary precision (64-bit). It requires 9 bytes, including length byte.

Oracle Date and Time Data Types

DATE	It is used to store a valid date-time format with a fixed length. Its range varies from January 1, 4712 BC to December 31, 9999 AD.
TIMESTAMP	It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss format.

 

Q How many types of  SQL commands?

DDL (Data Definition Language) = CREATE, DROP, ALTER, TRUNCATE, RENAME

DML (Data Manipulation Language) = INSERT, UPDATE, DELETE, MERGE, UPSERT

DCL (Data Control Language) = GRANT, REVOKE

DQL (Date Query Language) = SELECT

DTL (Data Transaction Language) = COMMIT, ROLLBACK, SAVEPOINT

SQL Statements for creating a table

Syntax:-

Sql>create table <table_name> (  <column_name1> <data_type> <(size)>, column_name2> <data_type> <(size)>, column_name3> <data_type> <(size)> );

 

Sql>create table student ( s_roll (2), s_name varchar2 (20), s_dob  date,s_address varchar2(30) );

View the structure of a table:-

Sql>desc student;

SQL statements for inserting record in a table

Sql>insert into student(s_roll,s_name,s_addres) values(1,’Raju’,’Bettiah’);

SQL statements for updating records in a table

Sql> update student set s_name=’Satyam Kumar’ where s_roll=2;

SQL statement for deleting records in a table

sql>delete from student where s_roll=2;

SQL statement for accessing records from a table

Sql>select * from student where s_roll=1;

SQL statements for adding new column in a existing table

Sql>alter table student add(s_address varchar2 (23)));