Removing
rows from Table
1.
create table empo
as select * from employees;
2.
select * from
empo;
3.
delete from empo; //all the records will be
deleted but structure not deleted//
4.
desc empo;
5.
select * from
empo;
6.
delete from empo
where employee_id=(select employee_id from employees where last_name='Bell
Merge
Statement
7.
create table
emp_2 as select employee_id,last_name,salary from employees;
8.
select * from
emp_2;
9.
delete from emp_2
where salary < 5000;
10.
merge into emp_2
q using employees e on (q.employee_id=e.employee_id)
when matched then
update set q.last_name=e.last_name, q.salary=e.salary
when not matched then
insert values(e.employee_id,e.last_name,e.salary);
Controlling
Transactions
11.
rollback; //deleted previous
return//return previous record//
12.
commit; //to save the record//
13.
insert into emp_2
values(1002,’Amit’,2300);
14.
select * from
emp_2;
15.
rollback;
16.
savepoint up;
17.
update emp_2 set
last_name='Tinku' where last_name='Amit';
18.
savepoint inst;
19.
insert into emp_2
values(2033,'Chandan',3400)
Views
1.
create table
emp_2 as select * from employees;
2.
create view vu1 as select * from emp_2;
3.
select * from
vu1;
4.
create or replace view vu1 as select * from
emp_2 where salary< 5000;
5.
select * from
vu1;
6.
delete from vu1
where salary< 4000;
7.
select * from
vu1; //view have no own record//
8.
select * from
emp_2; //record from emp_2 will also be deleted//
9.
delete from vu1
where salary< 35000;
10. create table emp_1 as select * from employees;
11. create view
vu2(Naam,Annual_sal,Profile) as select last_name,salary*12,job_id from emp_2;
12. select * from vu2;
13. delete from vu2 where salary< 35000; //wrong
statement, salary column doesn’t exits in vu2;//
14. delete from vu2 where annual_sal< 35000; //deleted
record from table also//
15. create or replace view vu2 as select
last_name,salary*12,job_id from emp_1; //alias required//
16. create or replace
view vu2 as select last_name,salary*12 ann_sal,job_id
from emp_1;
17. drop table emp_1;
18. select * from vu2;
//view has an error//
19. drop view vu2;
20. create view
vu2 as select * from employees where department_id=50;
21. select * from vu2;
22. update vu2
set department_id=10 where last_name='Vollman'; //row updated
23. create or replace view vu2 as select * from employees
where department_id=50 with check option
constraint vu2_chk;
24. update vu2 set department_id=10 where
last_name='Feeney';
25. create or replace view vu2 as select * from employees
where department_id=50 with read only;
26. delete from vu2 where last_name='Feeney'; //cannot be deleted//
Inline
Views
27. select
e.last_name,e.job_id,d.location_id,d.department_id from employees e, (select
location_id,department_id from departments) d where
e.department_id=d.department_id;
28. select
e.last_name,e.job_id,d.location_id,d.department_id from employees e, departments d where
e.department_id=d.department_id;
Top N
Analysis (Use of Rownum)
29. select rownum
from employees ; //rownum count the row
30. select rownum
from employees where last_name='Feeney';
31. select rownum
from employees where salary < 5000;
32. select rownum,last_name,salary
from (select last_name,salary from employees order by salary desc) where rownum <=3;
Data Base Objects
Sequence
1.
create sequence seq increment by 1 start with
101 maxvalue 500 nocache nocycle;
2.
create table t1
(sno number(4),name varchar2(24));
3.
insert into t1
values (seq.nextval,'tinku');
4.
insert into t1
values (seq.nextval,'Vishal');
5.
select * from t1;
6.
insert into t1
values (seq.currval,'amit');
7.
select * from t1;
8.
select
sequence_name,max_value,increment_by,last_number from user_sequences;
9.
alter sequence seq increment by 1 maxvalue
105 nocache cycle;
10. select sequence_name,max_value,increment_by,last_number
from user_sequences;
11. insert into t1 values (seq.nextval,'raj'); //run the command multiple times
again//
12. drop sequence seq;
13. Create sequence
seq increment by 1 start with 100 maxvalue 101 minvalue 50 nocache cycle;
14. insert into t1 values (seq.nextval,'mohan'); //run the command multiple
times again//
Index
15. create index
emp_ind on employees (last_name,salary,job_id);
16. select index_name,column_name,column_position from user_ind_columns where
table_name='EMPLOYEES';
17. col column_name format a20;
18. select index_name,column_name,column_position from
user_ind_columns where table_name='EMPLOYEES';
19. drop index
emp_ind;
Synonym
20. create synonym
e for employees;
21. select * from e;
22. select * from employees;
23. select * from user_synonyms;
24. drom synonym
e;
Using SET
Operators
25. create table set1 (name varchar2(10),sal number(4));
26. insert into set1 values('&name',&sal);
27. insert into set1 values('Tinku',5222);
28. select * from set1;
29. create table set2 as select * from set1;
30. insert into set2 values('yamu',6622);
31. select * from set2;
32. select * from set1 union
select * from set2; //show value from both table,eliminate duplicate
values//
33. select * from set1 union
all select * from set2; //show duplicates also//
34. select name,sal from set1 union all select name,sal from set2 order by sal;
35. select name,sal from set1 intersect select name,sal from set2; //show common values//
36. select name,sal from set2 minus select name,sal from set1;
37. select name,sal from set1 union select name,0 from set2;
38. select name,sal from set1 union select 'banana',sal from set2;
39. select name,sal from set1 union select name from set2;
//Error//
Date Time functions
1.
select tz_offset(‘us/eastern’) from dual;
2.
select tz_offset('asia/calcutta') from dual;
3.
select tzname
from v$timezone_names;
4.
select distinct
(tzname) from v$timezone_names;
5.
select tzname
from v$timezone_names where tzname
like 'Asia %';
6.
select sessiontimezone,current_date from dual;
7.
alter session set
nls_date_format='dd-mon-yyyy
hh24:mi:ss';
8.
select current_date from dual;
9.
select sysdate from dual;
10.
select current_timestamp from dual;
11.
select dbtimezone from dual;
12.
select sessiontimezone from dual;
13.
select extract(year from sysdate),extract (month from sysdate),extract(day from sysdate) from dual;
14.
select from_tz(timestamp '2010-04-01
08:44:56','5:30') from dual;
15.
select to_timestamp('2002-14-05
11:44:56','yyyy-dd-mm hh:mi:ss') from dual;
16.
select sysdate+to_yminterval('01-07') from
dual;
Date Time
Data Type
17.
create table
t1(sno number,dob timestamp); //date with time with
fraction of seconds//
18.
select * from t1;
19.
create table t4
(sno number,dob timestamp with time zone);
20.
insert into t4
values(102,'04-oct-1985 8:50:45 AM 5:30');
21.
select * from t4;
22.
create table t5
(sno number,dob timestamp with local
time zone);
23.
insert into t5
values(102,'04-oct-1985');
24.
select * from t5;
25.
create table
t2(sno number,loan_duration interval
year to month);
26.
insert into t2
values(102,interval '120' month(3)); //120 month and 3 day//
27.
select * from t2;
28.
insert into t2
values(103,interval '36-2' year(3) to
month); //36 years 2 month//
29.
select
to_char(sysdate+loan_duration,'dd-mon-yyyy') from t2;
30.
create table t3
(day_duration interval day(3) to second);
31.
insert into t3
values(interval '180' day(3));
32.
select
sysdate+day_duration from t3;
Creating and Managing Tables
1.
Create table
t2(sno number(4),name varchar2(20),sal number(5),DOB date);
2.
Create table
t3(sno number(4),name varchar2(20),sal number(5),DOB date default sysdate);
3.
select table_name
from user_tables; //all
table will be show//
4.
select distinct
object_type from user_objects; //all
sequence, procedure, trigger,function,index,view,synonym//
5.
select * from user_catalog; (select
* from tab;)
Altering
a Table
6.
desc t2;
7.
alter table t2 add(deptno number(4));
8.
desc t2;
9.
alter table t2 modify(deptno number(10));
10.
alter table t2 drop column dob;
11.
alter table t2 set unused column sal;//if
any column not used then you can use unused//
12.
alter table t2
drop unused column; //drop the unused column//
13.
select * from user_unused_col_tabs; //to
see unused column//
14.
rename t2 to
t4; //rename table name//
15.
drop table
t4; //structure and data are
deleted on rollback//
16.
truncate
table tinku; //data are deleted but structure are not delate on
rollback//
17.
delete empo; //data
and structure are not deleted om rollback//
18.
comment on
table empo is 'duplicate record of employees'; //to write own comment
on any table//
19.
select * from user_tab_comments where
table_name='EMPO'; //to see comment own any table//
Creating
and Managing Tables
Advanced sub Queries
1.
select * from
employees where salary=(select salary from employees where last_name=’Feeney’);
Multiple
column sub_queries
2.
select
employee_id,department_id,last_name,job_id from employees where
(manager_id,department_id) in (select manager_id,department_id from employees
where employee_id=150);
3.
select
employee_id,department_id,last_name,job_id from employees where
manager_id=(select manager_id from employees where employee_id=150) and
department_id=(select department_id from employees where employee_id=150);
Scalar
Subqueries
4.
select
employee_id,last_name,(case when
department_id=(select department_id from departments where
department_NAME='Shipping') then 'Seattle ' else
'London
5.
select
last_name,job_id from employees e order
by (select department_id from departments d where e.department_id=d.department_id);
Correlated
Subqueries
6.
select
last_name,job_id from employees e where salary > (select avg(salary) from
employees where department_id=e.department_id) order by department_id;
7.
select
last_name,job_id from employees e where exists
(select 'x' from employees where manager_id=e.employee_id);
8.
select
last_name,job_id from employees e where not
exists (select 'x' from
employees where manager_id=e.employee_id);
Correlated
Update
9.
create table t1
as select * from employees;
10.
alter table t1 add (d_name varchar2(25));
11.
select * from t1;
12.
update t1 t set
d_name=(select department_name from departments d where
t.department_id=d.department_id);
Extension
to DDL,and DML statememts
13.
create table
sal_date (sno number(4),h_date date,sal number(6));
14.
create table sal_mgr
(sno number(4),mgr varchar2(23),sal number(6));
15.
insert all
into
sal_date values(sno,h_date,sal)
into
sal_mgr values(sno,mgr,sal)
select
employee_id sno,hire_date h_date,salary sal,manager_id mgr from
employees
where salary<=12000;
16.
delete from
sal_date;
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