Case: 1.it is ANSI standard, it can be used by any database 2.It will not process NULL value 3.It can process inequality 4.It is a expression 5.it can allow multiple rows 6.Eg: select case when null is null then 1 else 0 end from dual; Output: 0 7.Whereas case is faster then decode, Because it check conditions one by one if matched first one then display the result than terminate the case expression. 8.Case can be used in both SQL and PL/SQL It give more effiency 9.Whereas case is data type consistency
Eg: select case 2 when 1 then '1' 2 when '2' then '2' 3 else '3' 4 end 5 from dual; when '2' then '2' * ERROR at line 2: ORA-00932: inconsistent datatypes: expected NUMBER got CHAR 10.CASE complies with ANSI SQL. DECODE is proprietary to Oracle. 11.It is lengthily then decode
DECODE: 1.It is oracle standard,so it used by only oracle database 2.It can accept NULL value 3.Decode cannot process inequality 4.It is a function 5.It can’t allow multiple rows 6.Eg: select decode( null,null,1,0) from dual; Output 1 7.It is slower than case, Because it will check all conditions and then display matched result 8.Pl/sql doesn’t allow decode function It used in SQL statement only 9.It can accept any data type
Eg: elect decode(2,1,1, 2 '2','2', 3 '3') t 4 from dual;
T ---------- 2 10.It run by oracle 11.It is simple and easy to understand
we use alias to make the heading of the result to be understandable
if u find value for using some sub queries without alias ...u can see the sub queries in output.so,u cant understand the exact function of that result. if u give alias name then u can easily understand
What are 'anchored declarations'? '%TYPE'and'%ROWTYPE' are called anchored declarations. It is so called as we are anchoring our datatype declarations to the column definitions in the database.
To display maximum sales of productname in product table. create table product( pid number, pname varchar(20), price number);
insert into product values(1,'laptop',10000); insert into product values(2,'desktop',20000); insert into product values(3,'laptop',10000); insert into product values(4,'pendrive',40000); insert into product values(5,'ipod',10000); insert into product values(6,'dekstop',20000); insert into product values(7,'projector',15000);
select * from product;
select pname,sales maxsales from (select pname,count(pname) sales from product group by pname) where sales=(select max(sales) from(select pname,count(pname) sales from product group by pname) );
Query to retrieve the employee details who earns more than or equal to average salary with respect to their department.
select e1.firstname,e1.deptid,e1.salary from employees e1,(select deptid,avg(salary) from employees group by deptid) as e2 where e1.deptid=e2.deptid and e1.salary>=e2.salary order by e1.deptid;
sir, why we are using alaias?
ReplyDeleteA REF CURSOR have a return type and it as 2 type Strongly Typed Cursor and Weakly Typed Cursor
ReplyDeletebut Cursor doesn't have return type
difference Between case and decode
ReplyDeleteCase:
1.it is ANSI standard, it can be used by any database
2.It will not process NULL value
3.It can process inequality
4.It is a expression
5.it can allow multiple rows
6.Eg: select case when null is null then 1 else 0 end from dual;
Output:
0
7.Whereas case is faster then decode,
Because it check conditions one by one if matched first one then display the result than terminate the case expression.
8.Case can be used in both SQL and PL/SQL
It give more effiency
9.Whereas case is data type consistency
Eg:
select case 2 when 1 then '1'
2 when '2' then '2'
3 else '3'
4 end
5 from dual;
when '2' then '2'
*
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
10.CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
11.It is lengthily then decode
DECODE:
1.It is oracle standard,so it used by only oracle database
2.It can accept NULL value
3.Decode cannot process inequality
4.It is a function
5.It can’t allow multiple rows
6.Eg: select decode( null,null,1,0) from dual;
Output
1
7.It is slower than case,
Because it will check all conditions and then display matched result
8.Pl/sql doesn’t allow decode function
It used in SQL statement only
9.It can accept any data type
Eg:
elect decode(2,1,1,
2 '2','2',
3 '3') t
4 from dual;
T
----------
2
10.It run by oracle
11.It is simple and easy to understand
we use alias to make the heading of the result to be understandable
ReplyDeleteif u find value for using some sub queries without alias ...u can see the sub queries in output.so,u cant understand the exact function of that result.
if u give alias name then u can easily understand
What are 'anchored declarations'?
ReplyDelete'%TYPE'and'%ROWTYPE' are called anchored declarations. It is so called as we are anchoring our datatype declarations to the column definitions in the database.
Difference between Cursor and Refcursor
ReplyDeleteCursor 1. It is a sql private work area.
Refcur 1. It is a data type
Cursor 2. Where ever we use cursor we need to declare it again.
Refcur 2. Once if we declare we can use anywhere in package.
Cursor 3. We can not print it directly.
Refcur 3. We can print it directly.
Cursor 4. It has 4 attributes.
Refcur 4. It has no attributes.
Cursor 5. It stores and process the information of query temporarily.
Refcur 5. It stores the output of the query temporarily.
Cursor 6. We can not pass it as an argument.
Refcur 6. We can pass it as an argument.
Cursor 7. It uses open and close keywords.
Refcur 7. It uses only open keyword.
Cursor 8. It is of two types explicit and implicit.
Refcur 8. It is only one type.
Cursor 9. It can be declared by oracle if the output has single row.
Refcur 9. User needs to declare it.
Cursor 10. It should be assigned to another variable.
Refcur 10. No need of assigning to another variable.
eg:
Print (refcusor variable name)
optimizing sql stmts
ReplyDeletediff b/w 9i/10g/11g
10 hints
Indexes
Bit map
b tree
function based
Ref cursor
Bulk collect
Views
Procedures and Functions
PLSQL Collections
Adv of packages
Triggers
Mutating table error
Union and Union All
Normalization
de-normalisation
how to connect Oracle and SQL SERVER DB
Materialized Views
Truncate and Delete
Oracle Architecture
SQL loader / External tables
UTL_FILE
DBMS_SCHDULER
DBMS_PROFILER
CURSOR / CURSOR FOR LOOP
CURSOR WITH PARAMETERS
EXCEPTIONS
PRAGMA AUTONOMOUS_TRANSACTION
OTHERS PRAGMAS
WHAT IS PRAGMA?
HOW TO WRITE HIERARCHICAL QUERIES?
START WITH CONNECT BY
Analytical functions
with clause in oracle
global temporary table
table partitioning
Unix commands
where did u use shell scripting?
To display maximum sales of productname in product table.
ReplyDeletecreate table product(
pid number,
pname varchar(20),
price number);
insert into product values(1,'laptop',10000);
insert into product values(2,'desktop',20000);
insert into product values(3,'laptop',10000);
insert into product values(4,'pendrive',40000);
insert into product values(5,'ipod',10000);
insert into product values(6,'dekstop',20000);
insert into product values(7,'projector',15000);
select * from product;
select
pname,sales maxsales
from
(select pname,count(pname) sales
from product
group by
pname)
where
sales=(select max(sales)
from(select
pname,count(pname) sales
from product
group by
pname)
);
A REF CURSOR have a return type and it as 2 type Strongly Typed Cursor and Weakly Typed Cursor
ReplyDeletebut Cursor doesn't have return type
Ex:
TYPE ref_type_name IS REF CURSOR RETURN return_type;
return_type represents a record in the database
DECLARE TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE
Query to retrieve the employee details who earns more than or equal to average salary with respect to their department.
ReplyDeleteselect e1.firstname,e1.deptid,e1.salary from employees e1,(select deptid,avg(salary) from employees group by deptid) as e2 where e1.deptid=e2.deptid and e1.salary>=e2.salary order by e1.deptid;
Query to retieve first 'n' and last 'n' records from a table:
ReplyDeleteselect * from employees where rownum<=3
union all
select * from(select * from employees order by rowid desc) where rownum<=3