PROCDURE TO GENERATE RECORDS IN ANY TABLE

Table having the columns of datatypes NUMBER,VARCHAR2,CHAR,DATE

DON'T KNOW THE NO: OF THE COLUMNS IN THE TABLE.

JUST PASS THE NAME OF THE TABLE AND THE NO: OF THE RECORDS TO GENERATE.


create or replace procedure populate(N number,T_name varchar2)
as
count_col number:=0;
col_id number;
str varchar2(100):=NULL;
d_t varchar2(10);
d_l number;
chr varchar(50);
num number;
dat date;
t_n varchar2(10);
str_1 varchar2(100);
d_p number;
d_s number;
begin
select table_name,count(*) into t_n,count_col from cols where table_name=upper(T_name) group by table_name;
for i in 1..N loop
col_id:=1;
str:=NULL;
for j in 1..count_col loop
select data_type,data_length,column_id,data_precision,data_scale into d_t,d_l,col_id,d_p,d_s from cols where table_name=upper(T_name) and column_id=col_id;
if (d_t = 'VARCHAR2' OR d_t='CHAR')
then
select dbms_random.string('U',d_l) into chr from dual;
str:=str','''''chr'''';
--dbms_output.put_line(d_l);
--dbms_output.put_line(str' & 'chr' & 'col_id);
elsif d_t ='NUMBER'
then
select substr(abs(dbms_random.random),1,(d_p-d_s)) into num from dual;
str:=str','num;
-- dbms_output.put_line(d_l);
--dbms_output.put_line(str' & 'to_char(num)' & 'col_id);
else
select TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2454071, 2454071+300)), 'J') into dat from dual;
--str:=str','dat;
str:=str','''''dat'''';
--dbms_output.put_line(d_l);
--dbms_output.put_line(str' & 'to_char(dat)' & 'col_id);
end if;
col_id:=col_id+1;

end loop;
str_1:=substr(str,2);
--dbms_output.put_line(to_char(str_1));
-- execute immediate 'insert into 't_n' values''('str')';
EXECUTE IMMEDIATE 'INSERT INTO ' t_n ' VALUES ''('str_1')';
-- insert into t_n values (str);
end loop;
execute immediate 'commit';
end;
/

Table Name...........Row_Count in a particular User

Getting Row_Count of all tables in a particular User

execute dbms_stats.gather_schema_stats ('scott');


select table_name, num_rows from user_tables order by table_name;

ORACLE SQL Queries

Running Total ……….

select empno,sal,deptno, sum(sal)over(partition by deptno order by sal ) Running_Total
from emp;

Any Highest Salary…..

select a.*, row_number() over(order by sal desc) Order_Sal from emp a;

Max Sal in Every department. :

select empno,deptno,sal from emp where (sal,deptno) in(select max(sal),deptno from
emp group by deptno)

Retrieving Duplicate Employeesss.......

select empno,deptno from emp where (empno, deptno) in (select empno,deptno from emp group by
deptno,empno having count(deptno)>1)

Deleting Duplicate Records………..

Delete from emp a where rowid not in (select min (rowid) from emp b where a.empno = b.empno);


Dispaly employee records who gets more salary than the average salary in their department?

select a.* from emp a,
(select deptno,avg ( sal) avg_sal from emp group by deptno) b
where a.deptno= b.deptno and a.sal > b.avg_sal

Write a query to display alternate records from the employee table?

Select * from emp where (rowid,1) in (select rowid,mod(rownum,2) from emp);

Write a query to display employee records having same salary?

Select a.empno,a.ename,a.sal from scott.emp a where a.sal in

(select sal from scott.emp group by sal having count(*)>1)




What is output of this SQL where current value of sequence is 3?

Select seq.curval,seq.Nextval,seq.Nextval from dual ? Give reason?

U r output wll be...

CURRVAL NEXTVAL NEXTVAL
----------- ----------- -----------
3 3 3

Coz , sequence are stored objects in oracle and Incremented only when they are use with in executable code, let Us C.....


How to compare the total salary of all employee with a individual employee salary for a particular department.

Select empno, sal/( select sum ( a.sal ) from emp a ) total_sal
from emp;

Or

Select empno, sal, sal/(sum(sal) over()) sum_sal from emp;


Reverse Pivoting in Oracle

select id, sum(a1_amt) a1_amt,sum(a2_amt) a2_amt,sum(a2_amt) a3_amt from (
select a.id,
decode(a.name,'a1',sum(a.amount))a1_amt ,
decode(a.name,'a2',sum(a.amount)) a2_amt,
decode(a.name,'a3',sum(a.amount)) a3_amt
from
reverse_pivot a group by a.id,a.name) group by id
/

Or…

select a.id,sum(decode(a.name,'a1',amount)) A1,
sum(decode(a.name,'a2',amount)) A2,
sum(decode(a.name,'a3',amount)) A3
from reverse_pivot a group by a.id




I want to first display the employees of deptid=30, then all the employees of all the departments in asceding order within the same query

select * from emp order by decode(deptno,30,0,deptno);

To Generate the series of characters from 'A' to 'Z' and 'a' to 'z'.

Select chr(l+64), chr(l+96) from (select level l from dual connect by rownum between 1 and 26)


Max Salary on basis of location

Select f1.* from ( select a11.*, row_number() over (partition by loc order by sal desc) r
from (
select distinct a.*,c.loc from emp a, (select deptno, max(sal) sal from emp group by deptno) b,
dup_dept c
where a.deptno = b.deptno
and
a.deptno = c.deptno) a11 ) f1
where f1.r = 1


Or

Select d.* from (select c.*, row_number() over(partition by loc order by sal desc) rn from
(select a.*,loc from (select max(sal) sal,deptno from emp group by deptno)a,dept b where a.deptno=b.deptno)c) d
Where d.rn=1
/

SCENARIOS for any ETL tool

Column having muliple values separated with ATTHERATE symbol

(b)DATA RECEIVED VIA FLATFILE (STRUCTURE)
Header :
REGNO,MAKE,YEAR ,DATES LEASED FROM,DATES LEASED TO,CUSTID

Ist Row :
SV53AYC,FORD,2003,28/10/06@22/10/06@12/10/06,30/10/06@27/10/06@20/10/06,HK113@TU141@BK121

IInd Row Data :

SV54KGH,HONDA,2004, 24/08/06@19/8/06, 28/08/2006@22/08/2006, BB141@TM186

IIIrd Row Data :

SV07XKY, TOYOTA, 2007, , ,

Iv Row Data :

SV06VWY,BMW, 2006, 11/03/07, 23/03/07, MK129

Plz Remove Header :, Ist Row Data :, II Row Data :, III Row Data :, Iv Row Data