EXCELLENT SQL QUERIES(www.intradaytrends.com)

[bala krishna]

SQL Queries,
Definitions,
PL/SQL Scripts



















1.Find the name of department which is not allocated to clerk

select dname from dept d where dname not in(select job from emp where
job='CLERK');

2.Find the second highest salary

select distinct sal from emp e where 2=(select
count(distinct sal) from emp e1 where e.sal<=e1.sal );

3.Find the third highest paid and third lowest paid
salary

select distinct sal from emp e1 where 3=(select
count(distinct sal) from emp e2 where e1.sal<=e2.sal)
union
select distinct sal from emp e3 where 3=(select
count(distinct sal) from emp e4 where e3.sal>=e4.sal);

4.Display the alternet row from the table.


select rownum,empno,ename from emp group by
rownum,empno,ename having mod(rownum,2)=0;


OR

select * from emp where rowid in(select
decode(mod(rownum,2),0,rowid) from emp)

. 5Find the job which is not allocated to any working
at ‘bosten’

select job from emp where deptno not in(select
deptno from dept where loc=’BOSTEN’);

6.Display the first three and last three row from
table.

Select * from emp e1 where 5>(select
Count(rowid) from emp e2 where e1.rowid<e2.rowid)
Union
Select * from emp e3 where 5>(select
count(rowid) from emp e4 where e3.rowid>e4.rowid);

7.Delete duplicate row from the table

DELETE FROM EMP WHERE ROWID NOT IN(SELECT MAX(ROWID)
FROM EMP GROUP BY EMPNO);

Or
DELETE FROM EMP WHERE ROWID NOT IN(SELECT MIN(ROWID)
FROM EMP GROUP BY EMPNO);






8.Delete alternate row from the Table


DELETE FROM EMP WHERE ROWID IN(SELECT ROWID FROM EMP
GROUP BY ROWNUM,ROWID HAVING MOD(ROWNUM,2)=0);


OR
DELETE FROM EMP WHERE ROWID IN(SELECT
DECODE(MOD(ROWNUM,2),0,ROWID) FROM EMP );

9.Find the employee name who are having same hiredate

select ename from emp where hiredate in(select
hiredate from emp group by hiredate having
count(hiredate)>1);

10.Find the date of last Thursday of the Month

select next_day(last_day(sysdate)-7,’thursday’) from
dual;

11.Update multiple row in using single update
statement.

Update emp set sal=
Case job
When ‘CLERK’ then sal+500
When ‘SALESMAN’ then sal+600
When ‘MANAGER’ then sal+2000
Else sal
End;

OR
Update emp set sal=
Decode(job,’CLERK’,sal+200,’SALESMAN’,sal
+300,’MANAGER’,sal+500);

12.Display the top n highest salary

select sal from(select distinct sal from emp
order by sal desc) where rownum<=&n;

13 Display the top n lowest salary

select sal from(select distinct sal from emp
order by sal) where rownum<=&n;

14 Display the nth and mth row from table

select * from(select rownum
r ,empno,ename,sal,deptno from emp) where r=&n
union
select * from(select rownum
r ,empno,ename,sal,deptno from emp) where r=&m;

OR

Select * from emp where rownum<&n
Minus
Select * from emp where rownum<&m;

(Note:-n must be greater than m)



OR

Select * from emp where rowid in(select
decode(rownum,&n,rowid,&m,rowid) from emp);


15.Find the no. of column in the particular
table.

select count(column_name) from cols where
table_name='EMP';


16 Delete the nth and mth row from table

delete from emp where rowid in(select
decode(rownum,&n,rowid,&m,rowid) from emp);


17.DISPLAY ALL THE DETAILS WHERE SAL> LOWEST
SAL OF EMPLOYEES IN DEPTNO 20

SELECT * FROM EMP WHERE SAL> ANY(SELECT SAL FROM EMP WHERE
DEPTNO=20);

18. DISPLAY DETAILS OF ALL EMPLOYEES WHOSE SAL IS GREATER THAN
AVG(SAL) OF EMPLOYEES IN RESPECTIVE DEPTS

SELECT * FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP);

OR
SELECT * FROM EMP WHERE SAL IN (SELECT AVG(SAL) FROM EMP GROUP BY
DEPTNO);


19.Multiple row subquery

SELECT ENAME FROM EMP WHERE SAL=ANY(SELECT AVG(SAL) FROM EMP
GROUP BY DEPTNO);

OR

SELECT ENAME FROM EMP WHERE SAL<ANY(SELECT AVG(SAL) FROM EMP
GROUP BY DEPTNO);

OR

SELECT ENAME FROM EMP
WHERE SAL>ANY(SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO);

20.Subquery Returning Multiple column.


SELECT ENAME,JOB,MGR FROM EMP WHERE (JOB,MGR) IN(SELECT JOB,MGR From
EMP WHERE EMPNO=7788);

21. EXPRESSIONS WITH SUBQUERIES.


SELECT * FROM EMP WHERE SAL=(SELECT SAL+50 FROM EMP WHERE
ENAME='JONES');

22.CREATE EXTERNAL TABLE

CONN SYS/SYS AS SYSDBA;

CREATE DIRECTORY DIR AS ‘C:\’;

CREATE TABLE OLDEMP(
EMPNO NUMBER,EMPENAME CHAR(20),BIRTHDATE DATE)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER DEFAULT DIRECTORY EXT ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE BADFILE 'BAD_FILE' LOGFILE 'LOG_FILE'
FIELDS TERMINATED BY ','
(EMPNO CHAR,EMPENAME CHAR,BIRTHDATE CHAR DATE_FORMATE MASK "DD-MON-
YYYY"))
LOCATION('EMP_EXT.TXT'))
PARALLEL 5
REJECT LIMIT 200;

23.


SELECT ENAME,DECODE(COMM,NULL,'NO COMMISION') FROM EMP WHERE COMM IS
NULL;

OR
SELECT ENAME,NVL(TO_CHAR(COMM),'NO COMMISSION') FROM EMP WHERE COMM IS
NULL;


24. UPDATE EMP SET JOB=DECODE(JOB,'MANAGER','M','SALESMAN','S',JOB);


1.Find out the ename and its corresponding manager name from emp
table

select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;

2.Display the 3rd,4th,9th row from the table


select * from emp where rowid in(select
deocode(rownum,&n,rowid,&m,rowid
,&o,rowid) from emp)

3.Delete the nth,mth row from the table

delete from emp where rowid in(select
decode(rownum,&n,rowid,&m,rowid) from emp)

4.Display the nth highest paid salary from emp table.

select distinct sal from emp e1 where &n=(select count(distinct
sal) from emp e2
where e1.sal<=e2.sal)

or

select sal from emp where rowid in(select
decode(rownum,&n,rowid)
from(select * from emp order by sal desc))

5.Display the nth lowest paid salary from emp table.

select distinct sal from emp e1 where &n=(select count(distinct
sal) from emp e2
where e1.sal>=e2.sal)

6.Display the top nth highest salary from emp table

select * from(select distinct sal from emp order by sal desc )
where rownum<=&n;

7.Display the top nth lowest salary from emp table.

select * from(select distinct sal from emp order by sal) where
rownum<&n;

8.Display the top nth row from table

select * from emp e1 where &n>=(select count(rowid) from emp e2
where
e1.rowid>=e2.rowid)

or

select * from(select * from emp order by rowid) where
rownum<=&n;


9.Display the bottom nth row from table.

select * from emp e1 where &n>=(select count(rowid) from emp e2
where
e1.rowid<=e2.rowid)

or

select * from(select * from emp order by rowid desc) where
rownum<=&n;

10.You have a five million record and that table have one column
gender
by mistake data loader load the 'M' in the place of 'F' and
'F'in the
the place of 'M' in the gender column.Now we have to write query
replace
'M' with 'F', and 'F' with 'M'.

update gender set gender=decode(gender,'M','F','F','M')

11.Find the department which is not allocated to the clerk

select deptno from emp where deptno not in(select deptno from emp
where job='CLERK');


12 Display the alternate row from the table.

select * from emp where rowid in(select rowid from emp group by
rowid,rownum having
mod(rownum,2)=0);

or

select * from emp where rowid in(select decode(mod(rownum,2),
0,rowid) from emp);


13.Delete the alternate row from the table.

delete from emp where rowid in(select rowid from emp group by
rowid,rownum having
mod(rownum,2)=0);

or

delete from emp where rowid in(select decode(mod(rownum,2),
0,rowid) from emp);



14 Find the hiredate who are having same hiredate.

select ename from emp where hiredate in(select hiredate from emp
group by
hiredate having count(hiredate)>1);

15.find the date of last thursday of the month.

select next_day(last_day(sysdate)-7,'thursday') from dual;

16.update multiple row using update statement.

update emp set sal=
case job
when 'SALESMAN' THEN SAL+200
WHEN 'CLERK' THEN SAL+300
WHEN 'MANAGER' THEN SAL+400
ELSE SAL+100
END;


OR

UPDATE EMP SET SAL=
DECODE(JOB,'CLERK',SAL+300,'SALESMAN',SAL+200,'MANAGER',SAL
+400);

17 Display the employee detail where sal>lowest sal of employee in
deptn 20.


select * from emp where sal>(select min(sal) from emp where
deptno=20);

18. Display the employee details whose sal is greater than avg(sal)
of employees
in their respective dept.

select * from emp where sal>(select avg(sal) from emp e where
e.deptno=emp.deptno group by e.deptno);


19.Display the ename and dayname on which day the maximum
employee hired


select ename,to_char(hiredate,'day') from emp where
to_char(hiredate,'day') in
(select to_char(hiredate,'day') from emp group by
to_char(hiredate,'day')
having count(to_char(hiredate,'day'))=(select
max(count(to_char(hiredate,'day')))
from emp group by to_char(hiredate,'day')))



20.Display the ename which is start with j,k,l,or m

select ename from emp where ename between 'J' AND 'M';


or

SELECT ENAME FROM EMP WHERE SUBSTR(ENAME,1,1) IN('A','F','R');




21 Sho all data of the clerk who have been hired after the year
1999


select * from emp where to_char(hiredate,'yyyy')>1999 and
job='CLERK';




22.Show the ename of all employees togther with the number of
completed
months that they have been employed.


SELECT ENAME,TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) YEAR,
MOD(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)),12) MONTH FROM EMP

23.How many employees have taht ends with an n ,create two
possible method.


SELECT COUNT(*) FROM EMP WHERE ENAME LIKE '%S';

or

SELECT COUNT(*) FROM EMP WHERE SUBSTR(ENAME,LENGTH(ENAME))='N';

OR
SELECT COUNT(*) FROM EMP WHERE SUBSTR(ENAME,-1)='N';






24. Sho the names and locations for all departments and the number of
employees
working in each department.Make sure taht deppartments without
employees are included as well.


select d.deptno,d.dname,d.loc,count(e.empno) from emp e
full outer join dept d on d.deptno=e.deptno
group by d.deptno,d.dname,d.loc


25.Sho all employees who were hired the first half of the month.


select ename,hiredate from emp where
to_char(hiredate,'dd')<=15;


26.Display the three record in the first row and two record in the
second
row and one record in the third row in a single sql statement.


SELECT job,DEPTNO,SUM(SAL) FROM EMP WHERE DEPTNO=10 AND
JOB='CLERK'
GROUP BY ROLLUP(DEPTNO,JOB);

27.Display the following output:

DNO ASAL ECOUNT ENAME SAL JOB
---- ---------- ---------- ---------- ---------- ---------
10 2916.66667 3 CLARK 2450 MANAGER
KING 5000 PRESIDENT
MILLER 1300 CLERK

20 3614.8 5 SMITH 4000 CLERK
ADAMS 1100 CLERK
FORD 3000 ANALYST
SCOTT 6999 ANALYST
JONES 2975 MANAGER

30 1400 6 ALLEN 600 SALESMAN
BLAKE 2850 MANAGER
MARTIN 1250 SALESMAN
JAMES 950 CLERK
TURNER 1500 SALESMAN
WARD 1250 SALESMAN





SOLUTION:


BREAK ON DNO ON ASAL ON ECOUNT SKIP 1



select d.deptno dno,avg(d.sal) asal,count(d.ename) ecount,
e.ename,e.sal,e.job from emp d,emp e where d.deptno=e.deptno
group by d.deptno,e.ename,e.sal,e.job


OR

SELECT DNO,ASAL,ECOUNT,E.ENAME,E.SAL,E.JOB FROM (SELECT DEPTNO
DNO,
AVG(SAL) ASAL,COUNT(ENAME) ECOUNT FROM EMP GROUP BY DEPTNO),EMP E
WHERE DNO=E.DEPTNO;

28.Display Isqlplus Environment .

http://wipro:7778/isqlplus

29.Display the first and second higest sal from each deprtment

select deptno,max(sal) from emp group by deptno
union
select deptno,max(sal) from (select deptno,sal from emp minus
select deptno,max(sal) from emp group by deptno) group by deptno


or

select sal from emp e1 where &n>=(select count(distinct sal) from emp
e2
where e1.sal<=e2.sal and e1.deptno=e2.deptno)

or
30.Date related Query.

Find out the current date:

Select sysdate from dual;



Find out the date of the next date

Select sysdate+1 from dual;

Find out the one hour from now

Select to_char(sysdate+1/24,’hh’) from dual;

Find out the three hours from now

Select to_char(sysdate+3/24,’hh’) from dual;

Find out the half an hour from now

Select to_char(sysdate+1/48,’hh:mi’) from dual;

Find out the 10 minutes from now

Select to_char(sysdate+10/1440,’mi’) from dual;

Find out the 10 second from now

Select to_char(sysdate+10/86400,’SS’) from dual;

Find out the tomorrow at the midinight 12:00

Select to_char(trunc(sysdate+1),’dd-mm-yy-/hh:mi:ss’) from dual;

Find the date of tomorrow at 8:00 A.M.

Select to_char(trunc(sysdate+1)+8/24) from dual;

Find out the next Monday at 12:00 noon

Select next_day(trunc(sysdate),'Monday')+12/24 from dual;

Find out the first day of next month at 12 mid night

Select trunc(last_day(sysdate)+1) from dual;

Find out the first day of current month

Select last_day(add_months(sysdate,-1))+1 from dual;





31.How to implement the if-then-else in a select statement.

select ename,sal,case
when sal<1000 then 'MIN Sal'
when sal>1000 and sal<2000 then 'avg sal'
else 'good sal'
end sal_grade from emp;

or

select ename,sal,decode(sal,800,'lowesr_sal',
5000,'Highest_sal','avg_sal') sal_grade
from emp;

32.Count the job of the each dept.

SELECT JOB,COUNT(DECODE(DEPTNO,10,1)) DEPT10,
COUNT(DECODE(DEPTNO,20,1)) DEPT20,
COUNT(DECODE(DEPTNO,30,1)) DEPT30
FROM EMP GROUP BY JOB

33.Display the bytes occupied by each ename in the emp table.

Select vsize(ename) from emp;

34.Display the first and second lowest sal from emp table

select sal from emp e1 where &n>=(select count(distinct sal) from emp
e2
where e1.sal>=e2.sal and e1.deptno=e2.deptno)

35.Select the year and the no of emp for the year in which more than
one emp was hired.

select to_char(hiredate,'yyyy') year,count(*) hired_count from emp
group by to_char(hiredate,'yyyy')

36.Diplat the our age in days.

select to_date(sysdate)-to_date('14-Aug-1980') Days from dual;

37. Display the our age in months.

Select trunc(months_between(sysdate,’14-aug-80’)) Months from dual;


38. Display the our age in year.

select trunc(trunc(months_between(sysdate,'14-aug-80'))/12) year from
dual;

39.Display the emp whose sal is greater than their manager.

select e.ename from emp e,emp m where e.mgr=m.empno and e.sal>m.sal;

40.Display the emp who are working in the same dept where his manager
is working.

select e.ename from emp e,emp m where e.mgr=m.empno and
e.deptno=m.deptno;

41.Display the ename whose manager is blake.

select e.ename from emp e,emp m where e.mgr=m.empno and
m.ename=’BLAKE’;


42.Display the manager name who mange the maximun no of employees.

select m.ename,count(m.ename) from emp e,emp m where e.mgr=m.empno
group by m.ename
having
count(m.ename)=
(select max(count(m.ename)) from emp e,emp m where e.mgr=m.empno group
by m.ename)


or

select ename from emp where empno=(select mgr from emp group by mgr
having count(empno)
=(select max(a) from (select count(empno) a from emp group by mgr)))

or
select ename from emp where empno=
(select * from (select mgr from emp group by mgr order by count(*)
desc)
where rownum=1);


43. Find the date for nearest Saturday after current date.

Select next_day(sysdate,’saturday’) from dual;

44.select common job for dept 10,20

select job from emp where deptno=10 intersect(select job from emp
where deptno=20);

45.select employee who are manger.

select ename from emp where empno in(select mgr from emp);



46. select employee who are not manger.

select ename from emp where empno not in(select nvl(mgr,0) from emp);

47 Display the employee without manager.

Select ename from emp where mgr is null;
48 Display the mgr and the no of employee working undr him.

select m.ename,count(*) from emp e,emp m where e.mgr=m.empno group
by m.ename;

49.Display the mgr who have maximum number of emp.

select mgr from emp group by mgr having count(*)>=
all(select count(*) from emp group by mgr)

50.Display the emp who joined after 15-mar-1981

select ename from emp where hiredate>to_date(’15-mar-1981’);

51.Diplay who are mgr using set operator.

select empno from emp intersect(select mgr from emp);

52.Display the ename who are manager.

Select ename from emp where empno in(select empno from emp
intersect(select mgr from emp));

53.Calculate the length of time any emp has been with the company.

select ename,to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy') year
from emp

54.Find out emp who earned the highest sal in each job type sort by
desc sal.

select ename,job,sal from emp where sal in(select max(sal) from emp
group by job) order by sal



55.Display the enae,sal,avg(sal) of their respective dept

select a.ename,a.deptno,(select avg(sal) from emp b where
a.deptno=b.deptno)
avg_sal from emp a order by a.deptno;

56.Delete second last row from the table.


delete from emp where rowed in(select rowid from emp e1 where &n=
(select count(rowid) from emp e2
where e1.rowid<=e2.rowid));


or

delete from emp where rowid in(select decode(rownum,&n,rowid) from
(select rowid from emp order by rowid desc))

57.Delete the first nth row from the table.

delete from emp where rowid=(select rowid from emp e1 where &n=
(select count(rowid) from emp e2
where e1.rowid>=e2.rowid));



or

delete from emp where rowid in(select decode(rownum,&n,rowid) from
(select rowid from emp order by rowid desc))


58.Write a stored procedure to load the Time Dimension.

create table time_dim(
v_pm number(8),
V_DATE DATE,
DAY VARCHAR2(20),
DAY_NO_OF_MONTH NUMBER(4),
WEEK_OF_MONTH NUMBER(4),
MONTH_NO NUMBER(4),
MONTH_NAME VARCHAR2(20),
WEEK_OF_YEAR NUMBER(4),
DAY_NO_OF_YEAR NUMBER(4),
YEAR NUMBER(4));





create sequence seqd
start with 1
increment by 1
maxvalue 15000
minvalue 1
nocache
nocycle
/



CREATE OR REPLACE procedureproc_time_dim(i_date,date,l_date
date)
is
v_date date:=i_date;
v_day varchar2(20);
v_day_no_of_month number(4);
v_week_no_month number(4);
v_month_no number(4);
v_month_name varchar2(20);
v_week_of_year number(4);
v_day_no_of_year number(4);
v_year number(4);
v number(8):=1;
begin
v_date:=i_date;
while(l_date>=v_date)
loop
v_day:=to_char(V_date,'Day');
v_day_no_of_month:=to_number(to_char(V_date,'dd'));
v_week_no_month:=to_number(to_char(V_date,'w'));
v_month_no:=to_number(to_char(V_date,'mm'));
v_month_name:=to_char(V_date,'Month');
v_week_of_year:=to_number(to_char(V_date,'ww'));
v_day_no_of_year:=to_number(to_char(V_date,'ddd'));
v_year:=to_number(to_char(V_date,'yyyy'));
insert into time_dim values(
seqd.nextval,
v_date,
v_day,
v_day_no_of_month,
v_week_no_month,
v_month_no,
v_month_name,
v_week_of_year,
v_day_no_of_year,
v_year);
v_date:= v_date+1;
end loop;
end proc_time_dim;
/


truncate table time_dim;

EXEC PROC_TIME_DIM('01-JAN-04','01-JAN-05');


SELECT * FROM TIME_dIM;


59. Find out the second highest sal of deptno 10;

select sal from emp e where e.deptno=10 and &n=(select count(distinct
sal) from emp e1
where e.sal<=e1.sal and e.deptno=e1.deptno)

60. Find out the second lowest sal of deptno 10;

select sal from emp e where e.deptno=10 and &n=(select count(distinct
sal) from emp e1
where e.sal>=e1.sal and e.deptno=e1.deptno)







62.Unconditional Insert(pivoting Operation)







62.create a materialized view.

a.With aggregate.

CREATE MATERIALIZED VIEW product_sales_mv
PCTFREE 0 TABLESPACE demo
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT
p.prod_name,
SUM(amount_sold) AS dollar_sales
FROM sales s, products p
WHERE s.prod_id = p.prod_id
GROUP BY p.prod_name;


b.With Join.


CREATE MATERIALIZED VIEW detail_sales_mv
PARALLEL BUILD IMMEDIATE
REFRESH FAST
AS
SELECT
s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid",
c.cust_id, c.cust_last_name, s.amount_sold,
s.quantity_sold, s.time_id
FROM sales s, times t, customers c
WHERE s.cust_id = c.cust_id(+) AND
s.time_id = t.time_id(+);


c.Nested mv.


CREATE MATERIALIZED VIEW sum_sales_cust_time
REFRESH FAST ON COMMIT
AS
SELECT COUNT(*) cnt_all, SUM(amount_sold) sum_sales,
COUNT(amount_sold)
cnt_sales, cust_last_name, day_number_in_week
FROM join_sales_cust_time
GROUP BY cust_last_name, day_number_in_week;


64.Create matewrialized view Log.

Materialized view logs are required if you want to use fast refresh.
They are defined
using a CREATE MATERIALIZED VIEW LOG statement on the base table that
is to be changed. They are not created on the materialized view. For
fast refresh of
materialized views, the definition of the materialized view logs must
specify the
ROWID clause. In addition, for aggregate materialized views, it must
also contain
every column in the table referenced in the materialized view, the
INCLUDING NEW
VALUES clause and the SEQUENCE clause.
An example of a materialized view log is shown as follows where one is
created on
the table sales.

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID
(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold,
amount_sold)
INCLUDING NEW VALUES;

Oracle recommends that the keyword SEQUENCE be included in your
materialized
view log statement unless you are sure that you will never perform a
mixed DML
operation (a combination of INSERT, UPDATE, or DELETE operations on
multiple
tables).
The boundary of a mixed DML operation is determined by whether the
materialized view is ON COMMIT or ON DEMAND.

. For ON COMMIT, the mixed DML statements occur within the same
transaction
because the refresh of the materialized view will occur upon commit of
this
transaction.
. For ON DEMAND, the mixed DML statements occur between refreshes. The
following example of a materialized view log illustrates where one is
created on
the table sales that includes the SEQUENCE keyword:

CREATE MATERIALIZED VIEW LOG ON sales
WITH SEQUENCE, ROWID
(prod_id, cust_id, time_id, channel_id, promo_id,
quantity_sold, amount_sold)
INCLUDING NEW VALUES;









65.Multi Table Insert.
a.Unconditional Insert:

INSERT ALL
INTO sales VALUES (product_id, customer_id, today, 'S', promotion_id,
quantity_per_day, amount_per_day)
INTO costs VALUES (product_id, today, product_cost, product_price)
SELECT TRUNC(s.sales_date) AS today,
s.product_id, s.customer_id, s.promotion_id,
SUM(s.amount_sold) AS amount_per_day, SUM(s.quantity)
quantity_per_day,
p.product_cost, p.product_price
FROM sales_activity_direct s, product_information p
Transformation Mechanisms
AND trunc(sales_date)=trunc(sysdate)
GROUP BY trunc(sales_date), s.product_id,
s.customer_id, s.promotion_id, p.product_cost, p.product_price;

b.Conditional all.

INSERT ALL
WHEN promotion_id IN (SELECT promo_id FROM promotions) THEN
INTO sales VALUES (product_id, customer_id, today, 'S', promotion_id,
quantity_per_day, amount_per_day)
INTO costs VALUES (product_id, today, product_cost, product_price)
WHEN num_of_orders > 1 THEN
INTO cum_sales_activity VALUES (today, product_id, customer_id,
promotion_id, quantity_per_day, amount_per_day,
num_of_orders)
SELECT TRUNC(s.sales_date) AS today, s.product_id, s.customer_id,
s.promotion_id, SUM(s.amount) AS amount_per_day, SUM(s.quantity)
quantity_per_day, COUNT(*) num_of_orders,
p.product_cost, p.product_price
FROM sales_activity_direct s, product_information p
WHERE s.product_id = p.product_id
AND TRUNC(sales_date) = TRUNC(sysdate)
GROUP BY TRUNC(sales_date), s.product_id, s.customer_id,
s.promotion_id, p.product_cost, p.product_price;

c.Conditional first.

INSERT FIRST
WHEN cust_credit_limit >= 4500 THEN
INTO customers
INTO customers_special VALUES (cust_id, cust_credit_limit)
ELSE
INTO customers
SELECT * FROM customers_new;



DDL Queries.

66.Rename the column name from the table.

alter table ddl rename column empno to eno;

67.Rename the table name

rename ddl to emp;

68.Drop the column from any table

alter table ttl drop column sal

69 Add the column in the table.

Alter table ttl add(sal number(4));

70 Add constraint in the table.

Alter table ttl add(empno number primary key);

71 Count the No. of Column from particular table.

select count(*) from cols where table_name='EMP';

72.Insert data from one table to another table.

Insert into emp1 select * from emp;

73.create table structure from another table.

Create table emp1 as select * from emp;
74.Create index command

a. create index i1 on ttl(empno);
b. create bitmap index bi on ttl(mgr);
c. create index i2 on ttl(job) online;


75 Create cluster command.

Create cluster c1 (empno number);


76.Multi table update using trigger.

create or replace trigger multi_table_update
after update of sal on emp
for each row
begin
update emp1 set sal=:new.sal where
empno=7369;
update emp2 set sal=:new.sal where
empno=7369;
end;
/


77.PL/sql user defined datatypes or userdifined datatypes.


pl/sql user defined data types
=======================


1.Record type
2.Object type
3.collection type

There are two types of collection
----------------------------------

a.Table type
b.Varray

There are two types of table
-------------------------------

aa.Index-by-table or (pl/sql table) or (assosciative array)
bb.Nested Table





Implementation:
============


Record Type:
==========

Unlike varray,table types, record type cannot be created and stored
in the database.


Ex1:-

declare
type deptrec is record(
dept_id dept.deptno%type,
dept_name varchar2(14),
dept_loc varchar2(13));


ex2:-


declare
type stockitem is record(
item_no integer(30),
description varchar2(50),
quantity integer,
price real(7,2));

ex3:-


declare
type emprec is record(
emp_id emp.emno%type,
last_name varchar2(20),
job_title varchar2(9),
salary number(7,2));













Object types
=============

ex1:-
------

create type coplex as object
(
rpart real, --attribute
ipart real,
member function plus (c complex) return comples, --method
member function less (x complex) return complex,
member function times(x complex) return complex,
member function divby(x comples) return complex

);


create type body complex as

member function plus (x complex) return complex is
begin
return complex(rpart+x.rpart,ipart+x.ipart);
end plus;

member function less (x complex) return comples is
begin
return complex(rpart-x.rpart,ipart-x.ipart);
end less;

member function times (x complex) return complex is
begin
return complex(rpart*x.rpart-ipart*x.ipart,
rpart*s.ipart+ipart*x.ipart);
end times;

member function divby (x complex) return complex is
z real:=x.rpart**2+x.ipart**2;
begin
return complex((rpart*x.rpart+ipart*x.ipart)/z,
(ipart*x.rpart-rpart*x.ipart)/z);
end divby;
end;
/







ex2:-
-------

create type Stack as object
(
max_size integer,
top integer,
position intarray, --intarray is varray type
member procedure initialize,
member function full return boolean,
member function empty return boolean,
member procedure push (n in integer),
member procedure pop (N OUT INTEGER)
);

CREATE TYPE BODY Stack AS
MEMBER PROCEDURE initialize IS
BEGIN
top := 0;
position := IntArray(NULL);
max_size := position.LIMIT; -- get varray size constraint
position.EXTEND(max_size - 1, 1); -- copy element 1 into 2..25
END initialize;

MEMBER FUNCTION full RETURN BOOLEAN IS
BEGIN
RETURN (top = max_size); -- return TRUE if stack is full
END full;

MEMBER FUNCTION empty RETURN BOOLEAN IS
BEGIN
RETURN (top = 0); -- return TRUE if stack is empty
END empty;

MEMBER PROCEDURE push (n IN INTEGER) IS
BEGIN
IF NOT full THEN
top := top + 1; -- push integer onto stack
position(top) := n;
ELSE -- stack is full
RAISE_APPLICATION_ERROR(-20101, ’stack overflow’);
END IF;
END push;
MEMBER PROCEDURE pop (n OUT INTEGER) IS
BEGIN
IF NOT empty THEN
n := position(top);
top := top - 1; -- pop integer off stack
ELSE -- stack is empty
RAISE_APPLICATION_ERROR(-20102, ’stack underflow’);
END IF;
END pop;
END;
/


Collection Type
==================


Index by table
----------------

ex1:
----


DECLARE

TYPE associative_array_type IS TABLE OF NUMBER
INDEXED BY BINARY_INTEGER;
v1 associative_array_type;

ex2:-
-----


DECLARE
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
continent_population population_type;
howmany NUMBER;
which VARCHAR2(64)
BEGIN
country_population(’Greenland’) := 100000;
country_population(’Iceland’) := 750000;
howmany := country_population(’Greenland’);
continent_population(’Australia’) := 30000000;
continent_population(’Antarctica’) := 1000; -- Creates new entry
continent_population(’Antarctica’) := 1001; -- Replaces previous
value
which := continent_population.FIRST; -- Returns ’Antarctica’
-- as that comes first alphabetically.
which := continent_population.LAST; -- Returns ’Australia’
howmany := continent_population(continent_population.LAST);
-- Returns the value corresponding to the last key, in this
-- case the population of Australia.
END;
/



Nested Table
-------------

ex1:-
------

CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type
/
CREATE TYPE Student AS OBJECT ( -- create object
id_num INTEGER(4),
name VARCHAR2(25),
address VARCHAR2(35),
status CHAR(2),
courses CourseList) -- declare nested table as attribute
/



varray
--------

CREATE TYPE ProjectList AS VARRAY(50) OF VARCHAR2(16);
/

CREATE TABLE department ( -- create database table
dept_id NUMBER(2),
name VARCHAR2(15),
budget NUMBER(11,2),
-- Each department can have up to 50 projects.
projects ProjectList)
/

78.Perform scd type1 using pl/sql

CREATE OR REPLACE PROCEDURE PROCEDURE_SCD1
IS
BEGIN
MERGE INTO PROC_SCD1 T
USING EMP S
ON(T.EMPNO=S.EMPNO)
WHEN MATCHED THEN
UPDATE SET
T.SAL=S.SAL
WHEN NOT MATCHED THEN
INSERT
VALUES(S.EMPNO,S.ENAME,S.JOB,S.MGR,S.HIREDATE,S.SAL,S.COMM,S.DEPTNO);
END;
/


79.perform scd2 through pl/sql

CREATE OR REPLACE PROCEDURE PROCEDURE_SCD2
IS
BEGIN
INSERT ALL
WHEN PK IS NULL
THEN
INTO PROC_SCD2
VALUES(S1.NEXTVAL,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,1000)
WHEN PK IS NOT NULL AND SAL<>TGT_SAL THEN
INTO
PROC_SCD2
VALUES(S1.NEXTVAL,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,VERSION
+1)

SELECT PK,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,
TGT_EMPNO ,TGT_ENAME ,TGT_JOB ,TGT_MGR ,TGT_HIREDATE,
TGT_SAL ,TGT_COMM ,TGT_DEPTNO,VERSION FROM EMP
LEFT OUTER JOIN
( SELECT * FROM PROC_SCD2 WHERE ROWID IN
( SELECT MAX(ROWID) FROM PROC_SCD2 GROUP BY TGT_EMPNO))
ON (TGT_EMPNO=EMPNO);
END ;
/



DROP SEQUENCE S1;
CREATE SEQUENCE S1
START WITH 1
INCREMENT BY 1
MAXVALUE 15000
MINVALUE 1
NOCACHE
NOCYCLE;

EXEC PROCEDURE_SCD2;

SELECT * FROM PROC_SCD2;










80.Write a procedure to load the time dimension.

create table time_dim(
v_pm number(8),
V_DATE DATE,
DAY VARCHAR2(20),
DAY_NO_OF_MONTH NUMBER(4),
WEEK_OF_MONTH NUMBER(4),
MONTH_NO NUMBER(4),
MONTH_NAME VARCHAR2(20),
WEEK_OF_YEAR NUMBER(4),
DAY_NO_OF_YEAR NUMBER(4),
YEAR NUMBER(4));




drop sequence seq;
create sequence seq
start with 1
increment by 1
maxvalue 150000
minvalue 1
nocache
nocycle
/

CREATE OR REPLACE procedure proc_time_dim(i_date date,l_date
date)
is
v_date date;
v_day varchar2(20);
v_day_no_of_month number(4);
v_week_no_month number(4);
v_month_no number(4);
v_month_name varchar2(20);
v_week_of_year number(4);
v_day_no_of_year number(4);
v_year number(4);
v number(8):=1;
begin
v_date:=i_date;
while(l_date>=v_date)
loop
v_day:=to_char(V_date,'Day');
v_day_no_of_month:=to_number(to_char(V_date,'dd'));
v_week_no_month:=to_number(to_char(V_date,'w'));
v_month_no:=to_number(to_char(V_date,'mm'));
v_month_name:=to_char(V_date,'Month');
v_week_of_year:=to_number(to_char(V_date,'ww'));
v_day_no_of_year:=to_number(to_char(V_date,'ddd'));
v_year:=to_number(to_char(V_date,'yyyy'));
insert into time_dim values(
seq.nextval,
v_date,
v_day,
v_day_no_of_month,
v_week_no_month,
v_month_no,
v_month_name,
v_week_of_year,
v_day_no_of_year,
v_year);
v_date:= v_date+1;
end loop;
end proc_time_dim;
/


truncate table time_dim;

EXEC PROC_TIME_DIM(2003,2003);


SELECT * FROM TIME_dIM ORDER BY V_PM;

81. Storage Units.


Storage Units
================

2 bit=1 b


1024 b=1kb
1024 kb=1mb =1024*1024b
1024 mb=1gb =1024*1024*1024b
1024 gb=1tb =1024*1024*1024*1024b
1024 tb=1zb =1024*1024*1024*1024*1024b



where b=byte,
kb=kilo byte,
mb=mega byte,
gb=giga byte,
tb=tera byte,
zb=zega byte


------------------------------------------------------------




Amount measurement Units
============================

10 lakhs=one million=1,000,000

1 arab=one billion=1,000,000,000

10 kharab=one trillion=1,000,000,000,



Definition


What is Data?

Plural form of Datum. (Hence the question should be “What are data?”)
Facts concerning people, objects, events, etc.,
Examples:
Narendra 10878
Venkatesan 66175
Jyothi 52179


What is Information?

Data that has been processed and presented in a meaningful format.
Increases our sense of awareness.
Examples:
Narendra’s employee number is 10878
Venkatesan’s monthly salary is 66175
Jyothi’s telephone extension is 52179


Materialized view:

Materialized views improve query performance by precalculating
expensive join
and aggregation operations on the database prior to execution and
storing the
results in the database.




Index :

An index provides pointers to the rows in a table that contain a given
key value.

Bitmap Index;

A regular index stores a list of rowids for each key corresponding to
the rows with
that key value. In a bitmap index, a bitmap for each key value
replaces a list of
rowids.

Cluster:

A cluster provides an optional method of storing table data. A cluster
is made up of
a group of tables that share the same data blocks. The tables are
grouped together
because they share common columns and are often used together. For
example, the
emp and dept table share the deptno column. When you cluster the emp
and dept
tables (see Figure 18–1), Oracle physically stores all rows for each
department from
both the emp and dept tables in the same data blocks.
Because clusters store related rows of different tables together in
the same data
blocks, properly used clusters offer two primary benefits:
n Disk I/O is reduced and access time improves for joins of clustered
tables.
n The cluster key is the column, or group of columns, that the
clustered tables
have in common. You specify the columns of the cluster key when
creating the
cluster. You subsequently specify the same columns when creating every
table
added to the cluster. Each cluster key value is stored only once each
in the
cluster and the cluster index, no matter how many rows of different
tables
contain the value.
Therefore, less storage might be required to store related table and
index data in
a cluster than is necessary in non-clustered table format. For
example, in
Figure 18–1, notice how each cluster key (each deptno) is stored just
once for
many rows that contain the same value in both the emp and dept tables.
After creating a cluster, you can create tables in the cluster.
However, before any
rows can be inserted into the clustered tables, a cluster index must
be created. Using
clusters does not affect the creation of additional indexes on the
clustered tables;
they can be created and dropped as usual.









You should not use clusters for tables that are frequently accessed
individually.

Views:

a view, which is a logical table based on
one or more tables or views. A view contains no data itself. The
tables upon which a
view is based are called base tables.