SQL Tutorial – Day – 01

Welcome to Oracle SQL sessions [Please watch the YouTube session to understand the below notes and concepts completely]

 

Oracle
MS SQL Server
Teradata
IBM DB2
Sybase
Natezza

SQL — Structured Query Language

ANSI

—————————————————

1.Numeric

int
decimal
float
double
number

number(6) 999999

number(8,2) 999999.99

number(2,2) 0.99

2. Character

char 2000
varchar 2000
varchar2 4000

first_name char(10) ARUN + 6 char (reserved)

first_name varchar2(10) ARUN + 6 char released

 

3. Date

date

insert — format mm/dd/yyyy
dd/mm/yyyy

4. LOB

CLOB — GB

create table test
(
cust_id number(2,2),
cust_name char(10),
cust_name2 varchar(10)
);

drop table test;

insert into test values(0.89,’Arun’,’john’);

desc test;

select * from test;

select length(cust_name),length(cust_name2) from test;

——————————————————————-

SQL

DDL DML DRL TCL DCL

 

DDL – Data Definition Language — Auto Commit

create
alter
rename
truncate
drop

DML – Data Manipulation Language — User commit

Insert
update
delete
Merge

DRL – Data Retrival language:

select

 

TCL – Transaction Control Language

commit
rollback
savepoint

 

DCL – Data Control Language (DBA)

Grant
Revoke

——————————————————————–

create table table_name
(
column_1 data_type,
column_2 data_type,
column_3 data_type,
.
.
.
column_n data_type
);

create table customer
(
cust_id number(6),
cust_name varchar2(30),
mobile_no number(10),
dob date,
city varchar2(100),
email_id varchar2(100)
);

 

insert into table_name
(column1,column2,column3)
values
(value1,value2,value3);

 

create table customer
(
cust_id number(6),
cust_name varchar2(30),
dob date,
mobile number(10),
address varchar2(100)
);

select * from customer;

insert into customer
(CUST_ID,cust_name,dob,mobile,address)
values
(100000,’Arun’,to_date(’09/12/1992′,’mm/dd/yyyy’),9090909090,’Chennai’);

select * from customer;

rollback;

commit;

insert into customer
values
(100001,’Kannan’,to_date(’09/11/2000′,’mm/dd/yyyy’),8132437493,’Chennai’);

insert into customer
values
(100002,’Radha’,to_date(’09/24/2012′,’mm/dd/yyyy’),1348374989);
–SQL Error: ORA-00947: not enough values

insert into customer
(CUST_ID,cust_name,dob,mobile)
values
(100002,’Radha’,to_date(’09/24/2012′,’mm/dd/yyyy’),1348374989);

commit;

update table_name
set column_name=value
where condition;

update customer
set address=’Hydrabad’;

rollback;

select * from customer;

update customer
set address=’Hydrabad’
where cust_id=100002;

commit;

— Add a column

alter table table_name
add column_name data_type;

 

alter table customer
add zip number(6);

select * from customer;

–drop a column

alter table table_name
drop column column_name;

alter table customer
drop column address;

 

— Rename a table

rename old_table_name to new_table_name;

rename customer to customer_details;

select * from customer_details;

 

— rename a column

alter table table_name
rename column old_name to new_name;

alter table customer_details
rename column mobile to mobile_no;

——————————————————————–

1. bkp a table

2. truncate base table

3. modify data type

4. Restore the data

5. drop bkp table

———————————————————————