My preparation notes for Wells Fargo interview-Oracle II

select e.ename “Employee”,e.salary “Manager” from employee e,employee m where e.salary>m.salary;

select ct.ename,ct.email,at.accno,at.bal from customers_table ct inner join account_table at on ct.id = at.id;

create sequence sqrno increment by 3 start with 2 minvalue 2 maxvalue 9;
sqrno.currval
sqrno.nextval

auto increment:
must be added on key only

create table tabloid(size int primary key auto_increment,name varchar(20))auto_increment=11;
insert into tabloid values(‘A’);
insert into tabloid(name) values(‘B’);

create index abc on tabloid(name);
drop index abc;

alter table tabloid add(shape varchar(10));
alter table tabloid drop column shape;
alter table tabloid add primary key(name);
alter table tabloid drop primary key;
alter table tabloid modify shape varchar(20);
alter table tabloid add constraint c1 foreign key(name) references employee(name);

alter table tabloid drop constraint c1;

views:

create view aview as select * from students;
static view: contains order by,group by
cannot be updated

dynamic view:
can be updated

select ct.cname,ct.email,at.acno,at.bal from customers_table ct inner join accounts_table at on at.cid=ct.cid where at.attype=’Saving’;

nth highest

select e1.emp,e1.salary from employee e1 where n-1 = (select count(distinct(e2.salary)) from employee e2 where e2.salary >e1.salary)

select 3rd element

select emp from employee where rownum=3

first 3 element

select emp from employee where rownum <4

highest 3 element

select emp from(select * from employee order by salary desc) where rownum <4

alternate element

select emp from (select e.*,rownum as row_id from employee) where mod(row_id,2)=0;

last 3 elements

select * from employee where rownum >(select max(rownum)-3 from employee)

defragment check : acual size ->blocks*8
used size num_rows*avg_row_len

select table_name,round((blocks*8)/1024) as “Actual size”,round((num_rows*avg_row_len)/1024) as “Used sze in Kb” from dba_tables where round((blocks*8)/1024) > round((num_rows*avg_row_len)/1024)

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s