Oracle Day 2 – SQL Types
SQL can be classified into 5 different languages
create table
customer (
cust_id number(6),
cust_name varchar2(50),
mobile_no number(10),
dob date,
city varchar2(30),
email_id
varchar2(30)
);
select * from customer;
insert into customer
(cust_id,cust_name,mobile_no,dob,city,email_id)
values(100000,'Arun', 9090909090,to_date('08/04/2000','mm/dd/yyyy'),
'Chennai','arun@gmail.com');
insert into customer
values(100001,'John', 9090909030,to_date('12/24/1986','mm/dd/yyyy'),
'Bangaluru','john@gmail.com');
insert into customer
values(100002,'Geon',to_date('12/24/1985','mm/dd/yyyy'), 'Delhi','Geon@gmail.com');
-- SQL Error: ORA-00947: not enough values
insert into customer
(cust_id,cust_name,dob,city,email_id)
values(100002,'Geon',to_date('12/24/1985','mm/dd/yyyy'), 'Delhi','Geon@gmail.com');
rollback;
commit;
update customer
set mobile_no =7878787878;
rollback;
update customer
set mobile_no =7878787878 where cust_id=100002;
commit;
-- add a column
alter table customer
add country VARCHAR2(50);
select * from customer;
-- drop a column
alter table
customer drop
column city;
update customer set country='India';
commit;
desc customer;
insert into customer
(cust_id,cust_name,mobile_no,dob,email_id,country)
values(1000000,'Dilip',9090909090,to_date('08/04/2000','mm/dd/yyyy'),'arun@gmail.com','india');
--ORA-01438: value larger than specified precision allowed for this column
CUST_ID NUMBER(6) number(8)
MOBILE_NO NUMBER(10) varchar2(15)
alter table customer
modify cust_id number(8);
alter table customer
modify cust_id number(6);
-- ORA-01440: column to be modified must be empty to decrease precision or scale
alter table customer
modify MOBILE_NO varchar2(15);
--ORA-01439: column to be modified must be empty to change datatype
-- one way
1. bkp a table
2. truncate base table
3. modify data type
4. Restore the data
5. drop bkp table
-- another way
1. Rename the table as bkp TABLE
2. create a main table with column data type
3. restore the data and VALIDATE
4. drop backup table
create table customer_bkp as select * from customer;
select * from CUSTOMER_BKP;
truncate table customer;
select * from customer;
rollback;
alter table customer
modify MOBILE_NO varchar2(15);
desc customer;
insert into customer (select * from customer_bkp);
commit;
select * from customer_bkp;
delete from customer_bkp;
rollback;
-- delete a partial record from table
delete from customer_bkp where cust_id=100001;
commit;
drop table customer_bkp;
desc customer;
dob date_of_birth
alter table customer
rename column dob to date_of_birth;
rename customer to customer_data;
desc customer_data;
create table customer_test(no1 number(3), no2 number(3));
insert into customer_test values
(1,2); savepoint a;
insert into customer_test values
(3,4); savepoint b;
insert into customer_test values
(5,6); savepoint c;
insert into customer_test values (7,8);
rollback to c;
select * from customer_test
rollback;
desc CUSTOMER_DETAILS;
select * from CUSTOMER_DETAILS;
999999 99999999
Modify a column
alter table
customer_details modify
cust_id number(8);
alter table customer_details
modify mobile_no varchar2(15);
-- ORA-01439: column to be modified must be empty to change datatype
bkp
truncate
modify
restore
create table customer_details_bkp as select * from customer_details;
select * from CUSTOMER_DETAILS_BKP;
create table customer_details_bkp1 as select * from customer_details where 1=2;
-- Truncate a table
truncate table customer_details;
-- Modify
alter table customer_details
modify mobile_no varchar2(15);
desc customer_details;
select * from customer_details;
insert into customer_details (select * from customer_details_bkp);
commit;
select * from customer_details_bkp;
-- Delete a record
delete from
customer_details_bkp where
cust_id=100002;
rollback;
drop table customer_details_bkp;
select * from CUSTOMER_DETAILS_BKP;
truncate table customer;
delete from customer;
commit;
create a table
insert 1
update
insert 1
alter -- add a column
update
insert 1
alter -- add a column
update
insert
insert
rollback;
how many records will be there in the table?
insert into hr.customer_details values (100003,'Vijay',sysdate,9080706050,896745);
select * from customer_details;
