Use of Data Type
1.
create table
t1(name varchar2(23),sal number(6));
2.
insert into t1
values('Tinku',999.99);
3.
select * from t1;
4.
create table
t2(name varchar2(23),feedback long);
// Long variable length data upto 2 gigabyte//
5.
insert into t2
values('Tinku',1234567890);
6.
insert into t2
values('Tinku','i am oracle(OCA) nad i am bscit from smu');
7.
select * from t2;
8.
create table
t3(name varchar2(23),feedback clob);
// Clob character data upto 4 gigabyte//
9.
insert into t3
values('Tinku',12332146545644567890);
10.
insert into t3
values('Tinku','my name is tinku kumar and i am BSC(IT) from SMU');
11.
select * from t3;
12.
create table
t4(name varchar2(23),data raw(12)); //RAW
binary data upto 2000 //
13.
insert into t4
values('Tinku','125f8'); //supports
hexadecimal number in quotation//
14.
select * from t4;
15.
create table
t5(name varchar2(23),data long raw);
//LONG RAW binary data of variable length upto 2 GB//
16.
insert into t5 values('Tinku','fd125abcf8'); //supports
hexadecimal number in quotation//
17.
select * from t5;
18.
create table
t6(name varchar2(23),data blob); //BLOB
Binary data upto 4 GB//
19.
insert into t6
values('Tinku','12f34');
20.
select * from t6;
21.
create table
t7(name varchar2(23),data bfile); //BFILE
Binary data store in a file 4 GB//
22.
conn
system/oracle;
23.
create directory
data as '\c:\tinku.txt';
24.
grant read on
directory data to hr;
25.
conn hr/hr;
26.
insert into t7
values('Tinku',bfilename ('data','tinku.txt')); //this column can not
be viewed by sql plus or isql plus//
27.
select * from t7;
28.
create table
t8(name varchar2(23),address rowid);
29.
select rowid from employees;
30.
insert into t8
values('Tinku','AAAMg6AAFAAAABYABQ');
31.
select * from t8;
Enhancement to Group By Clause
1.
select max(salary),department_id from
employees group by department_id;
2.
select sum(salary),department_id,job_id from
employees group by department_id,job_id;
3.
select sum(salary),department_id,job_id from
employees group by department_id,having sum(salary) < 10000;
Group by
with Rollup and Cube Operations
4.
select sum(salary),department_id,count(employee_id) from employees group by rollup(department_id);
5.
select
sum(salary),department_id,count(employee_id),job_id
from employees group by rollup(department_id,job_id);
6.
select sum(salary),department_id,count(employee_id) from employees group by cube(department_id);
7.
select sum(salary),department_id,count(employee_id),job_id from employees
group by cube(department_id,job_id);
Grouping,Grouping
sets Function
8.
select
department_id,job_id,sum(salary),grouping(department_id),grouping(job_id) from employees group by rollup(job_id,department_id);
9.
select job_id,sum(salary),manager_id,department_id from
employees group by grouping sets (department_id,job_id,manager_id)
,(department_id,manager_id) , (job_id,manager_id);
Composite
Columns
10. select job_id,sum(salary),manager_id,department_id
from employees group by rollup(department_id,(job_id,manager_id));
Concatenated
Grouping
11. select job_id,sum(salary),manager_id,department_id
from employees group by department_id,rollup(job_id),cube(manager_id);
Hierarchical
Retrieval
12. select employee_id,last_name,manager_id from employees
start with employee_id=178 connect by prior manager_id=employee_id;
//prior is a keyword and check the previous condition//
13. select last_name|| '
report to ' ||prior last_name
from employees start with
last_name='Feeney' connect by prior manager_id=employee_id;
14. select employee_id,last_name,manager_id,job_id,level from employees start with last_name='Bell
15. select
lpad(substr(last_name,1,4),length(substr(last_name,1,4))+level,'*') from employees start with last_name='Feeney' connect by prior
manager_id=employee_id;
note:-
start with=178 chech the
condition(manager_id=employee_id) and show the manager upper and lower.
SQL*Plus Editing Command
1.
select * fr;
2.
a om
employees; //
append data to buffered command//
3.
/
4.
select * from
eeployees;
5.
change /eeployees/employees;
or c /eeployees/employees; //change
the command in the memory//
6.
/
7.
select * from
employees bday to you;
8.
c /bday to
you; // delete required text from
buffered command//
9.
/
10. clear buffer; or
cl buff; //clear all buffer//
11. select *
from
employees
where
salary >
20000;
12. del
13. del
14. list; [L] //list of command in the
buffer//
15. L; //
show line no-1//
16. run; or r; //run
the buffered command//
17. 3 where salary= //change
the text in line 3//
18. 0 select * from
//insert a line before line 1 //
19. select last_name,salary,job_id from employees;
20. save tinku; //
save the command in file named tinku //
21. get tinku; // show the command stores in tinku file //
22. start tinku; //
run the command //
23. @ tinku; //
run the command //
24. run tinku; //
run the command //
25. edit tinku; //change
the tinku file //
26. @ tinku;
27. exit or quit; //to come out of the
sql*plus environment//
28. show
pagesize;
29. show
linesize;
30. set linesize
200;
31. set pagesize
100;
32. set feedback
off;
33. select 'create table '|| table_name||'_test'||' as
select * from '|| table_name from user_tables;
Primary Key/Foreign Key/Composite Key
1.
create table
t1(sno number(2),name varchar2(14), constraint
sno_pk primary key(sno));
2.
desc t1;
3.
insert into t1
values(1,'tinku');
4.
insert into t1
values(2,'ku');
5.
insert into t1
values(3,'muku');
6.
select * from t1;
7.
create table
t2(sno number(2) references t1,sal
number(6));
8.
insert into t2
values(1,2000);
9.
insert into t2
values(2,5000);
10. insert into t2 values(3,7000);
11. insert into t2 values(4,9000); //error::parent key not
found //
12. select * from t1;
13. delete from t1 where sno=1; //can’t delete child record found //
14. delete from t2 where sno=1; //delete//
15. select * from t1;
16. select * from t2;
17. drop table t1; //
child: reference key exists //
18. drop table t2; //
droped //
19. select * from tab;
20. create table t2(sno number(4) references t1 on delete
cascade,sal number(5));
21. insert into t2 values(1,2000); (2,5000); (3,7000);
22. delete from t2 where sno=1; // record not deleted from
t1(master table) //
23. select * from t2;
select * from t1;
24. delete from t1 where sno=2; //record deleted from
t2(transaction table also)//
25. select * from t2;
select * from t1;
26. drop table t2; //
(droped) t1(master table) can not be deleted if the t2 (transaction table
exists//
27. create table t2(sno number(4),sal number(5), constraint sno_fk foreign key(sno) references t1(sno));
28. alter table t2 drop
constraint sno_fk;
29. alter table t2 add
constraint sno_fk foreign key(sno) references
t1(sno);
30. select table_name,constraint_name,column_name
from user_cons_columns where
table_name='T1' or table_name='T2';
31. alter table t1 drop
primary key; //foreign
key exists //
32. alter table t1 drop
constraint sno_pk; //foreign
key exists //
33. alter table t1 drop
primary key cascade; //deleted
primary key //
34. select table_name,constraint_name,column_name from user_cons_columns where table_name='T1'
or table_name='T2';
Composite
Key
35. create table comp(sno number(3),name varchar2(23), constraint comp_key primary key(sno,name));
36. create table tomp(sno number(3),name varchar2(23),sal
number(5), constraint tomp_key foreign key(sno,name) references comp(sno,name));
37. select table_name,constraint_name,column_name from user_cons_columns where
table_name='COMP' or table_name='TOMP';
38. insert into
comp values (1,'tinku');
39. insert into
comp values (2,'amit');(3,'suman'); (4,'kumar');
40. insert into tomp values (1,'tinku',1000); (2,'amit',2000);
41. insert into
tomp values (3,'kavita',3000); // parent key not found //
Creating
and Managing Table
42. create table t1(sno number(4),name varchar2(25),dept
number(4) not null,constraint t1_pk primary key(sno));
43. insert into t1 values(1,'tinku',40);
44. insert into t1 (sno,dept) values (6,80);
45. insert into t1 values(1,'tinku',40); //unique constrain (t1_pk)
violated //
46. insert into t1 values(3,'tinku'); insert into t1 values('tinku'); //
not enough values //
47. create table t2(sno number(4),name varchar2(25) not null, dob date constraint t2_dob_nn not null);
48. insert into t2 values(1,'tinku','2-oct-88');
49. insert into t2 values(1,'tinku','2-oct-88');
50. insert into t2 (name,dob) values ('tinku','2-oct-88');
51. insert into t2 values(1,'tinku'); (1,'2-oct-88'); ('tinku','2-oct-88'); //
not enough values //
52. create table t3(sno number(4),name varchar2(25),dept
number(4) not null,constraint t3_uni unique(sno));
53. insert into t3 values(1,'tinku',50);
54. insert into t3 values(1,'tinku',50); //unique constrain (t3_uni)
voileted //
55. insert into t3 values(2,'tinku',50);
56. insert into t3 values(3,'tinku'); // not enough values //
57. create table t4(dept_no number(5),loc varchar2(25),constraint t4_dept_fk foreign key (dept_no) references departments(department_id));
58. desc departments;
59. insert into t4 values(10,'tinku');
60. insert into t4 values(11,'tinku'); //
parent key not found //
61. insert into t4 (loc) values ('usa
62. insert into t4 (dept_no) values (20);
63. create table t5(sno number(4),name varchar2(25),sal
number(4) constraint sal_ch check
(sal>3000)); //
check is a keyword constraint when error comes time //
64. insert into t5 values(1,'tinku',5100);
65. insert into t5 values(1,'tinku',2000); // check constraint
(HR.SAL_CH) violated//
66. create table emq as select * from employees;
67. desc emq;
68. alter table emq add
constraint emq_pk primary key(employee_id);
constraint
deferrable enable novalidate/rename constraint
69. create table tinku(sno number(4),name varchar2(23));
70. insert into tinku values(1,'tinku');
71. insert into tinku values(1,'tinku');
72. alter table tinku add
constraint tinku_pk primary key(sno) deferrable
enable novalidate;
73. insert into tinku values(1,'tinku');
74. alter table tinku rename
constraint tinku_pk to tk_pk;
75. insert into tinku values(1,'tinku');
NOTE :-
constraint is a keyword who write show write(constraint
t1_pk,tinku_pk,t2_pk)
CREATE
INDEXES
76. create index
tinku_emp_ind on departments(department_name) pctfree 30 storage (initial
200k next 200k pctincrease 0 maxextents 50);
77.
select
index_name,column_name,column_position from user_ind_columns where table_name='DEPARTMENTS’;
78.
create bitmap index tinku_dep_ind on
departments(manager_id) pctfree 30 storage (initial 200k next 200k pctincrease
0 maxextents 50);
79.
select index_name,column_name,column_position
from user_ind_columns where
table_name='DEPARTMENTS';
80.
alter index tinku_dep_ind stroage(next 200k
maxeztents 60); //extent
parameters can be altered //
81.
conn /as sysdba;
82.
create tablespace
tinku datafile 'C:\oracle\product\10.2.0\oradata\orcl\tinku01.dbf' size 5m;
83.
create index
tinku_emp_ind on hr.locations(POSTAL_CODE) pctfree 30 storage (initial 200k
next 200k pctincrease 0 maxextents 50)tablespace tinku;
84.
alter index
tinku_emp_ind rebuild tablespace
tinku;
85.
analyze index
tinku_emp_ind validate structure;
86.
select
blocks,name,lf_rows from index_stats;
87.
drop index
tinku_emp_ind;
�Dr a ��5 ��4
style='mso-list:Ignore'>17.
delete from
sal_mgr;
18.
insert all
when sal<=5000 then
into sal_date values (sno, h_date,sal)
when
sal<=5000 then
into
sal_mgr values (sno,mgr ,sal)
select employee_id sno,hire_date h_date,salary
sal,manager_id mgr from
employees;
19.
select * from sal_date;
20.
select *
from sal_mgr;
21.
delete from
sal_date;
22.
delete from
sal_mgr;
23.
insert first
when sal<=5000 then
into
sal_date values (sno, h_date,sal)
when
sal<=5000 then
into
sal_mgr values (sno,mgr ,sal)
select employee_id sno,hire_date h_date,salary
sal,manager_id mgr from
employees;
24.
select * from sal_date;
25.
select *
from sal_mgr;
26.
create table
sales(emp_id number(4),week_id number(5),sale_mon number(5),sale_tue
number(5),sale_wed number(5),sale_thu number(5),sale_fri number(5));
27.
insert into sales
values(100,1,2000,3000,4000,5000,6000);
28.
select * from sales;
29.
create table
sales_info(emp_id number(4),week number(5),sales number(5));
30.
insert all
into
sales_info values(emp_id,week,sale_mon)
into
sales_info values(emp_id,week,sale_tue)
into
sales_info values(emp_id,week,sale_wed)
into
sales_info values(emp_id,week,sale_thu)
into
sales_info values(emp_id,week,sale_fri)
select
emp_id,week_id week,sale_mon,sale_tue,sale_wed,sale_thu,sale_fri
from
sales;
31.
select * from sales_info;
No comments:
Post a Comment