Showing posts with label WITH. Show all posts
Showing posts with label WITH. Show all posts

Jul 6, 2010

Interview Questions

 1) What is the difference between an INLINE VIEW And SQL WITH CLAUSE ?

  The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table.

A subquery in the FROM clause of a SELECT statement is also called an inline view
Inline view is not a schema object. It is a subquery with an alias (correlation name) that you can use like a view within a SQL statement.


2) To view last executed statement in your session

       select * from table(dbms_xplan.display_cursor);



3)  What is the difference between execute and call statements ?

The main difference between EXECUTE and CALL command is that EXECUTE is a SQL*Plus command
whereas CALL is a SQL command AND understands SQL data types only.
In CALL STATEMENT using  PL/SQL datatypes (ie: PLSQL tables)  will result an error.

4) Difference between Varchar2(Bytes) & Varchar2(Char)  ?

 If you define the field as VARCHAR2(11 BYTE), Oracle can use up to 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store , e.g. non-English characters. By defining the field as VARCHAR2(11 CHAR) you tell Oracle it can use enough space to store 11 characters, no matter how many bytes it takes to store each one. A single character may require up to 4 bytes  


5) Difference between Int & Number   ?
An integer is a "whole number". (Not just a display feature.) When you insert a number that has decimal places into an integer field, oracle is performing an implicition conversion from a number to an integer. (which removes the decimal places.) Any additional decimal places will be permanently lost

Feb 21, 2009

Instead of Stragg /// Allow Duplicate

SELECT
deptno,
LTRIM(SYS_CONNECT_BY_PATH(ename, ','), ',') employees
FROM (
SELECT deptno,
ename,
row_number() over(PARTITION BY deptno ORDER BY deptno) rnum,
count(*) over(PARTITION BY deptno) tot
FROM scott.emp
)
WHERE rnum=tot
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum -1 AND PRIOR deptno = deptno;



select tb.deptno , substr ( sys_connect_by_path( tb.EMPNO, ',' ) , 2) as string
from ( select deptno ,EMPNO ,row_number() over ( partition by deptno
order by EMPNO ) as val_index from
scott.emp WHERE EMPNO IS NOT NULL ) tb where
connect_by_isleaf = 1 connect by val_index = prior val_index + 1 and
deptno = prior deptno start with val_index = 1 ;



WITH tab AS
(
SELECT &strg str
FROM DUAL)
SELECT REGEXP_SUBSTR (str, '[^,]+', 1, LEVEL) RESULT
FROM tab
CONNECT BY LEVEL <=
((SELECT SUM (CASE
WHEN SUBSTR (str, LEVEL, 1) = ','
THEN 1
WHEN LENGTH (str) = LEVEL
THEN 1
ELSE 0
END
)
FROM tab
CONNECT BY LEVEL <= LENGTH (str)));


SELECT REPLACE(&strg ,',',CHR(13)) FROM DUAL;


FROM 10G ONWARDS

SELECT deptno,wmsys.wm_concat(ename) d
FROM scott.emp group BY deptno


------------------------------------------
To remove duplicate  from a SQL

SELECT JOB, ENAME FROM
(SELECT DISTINCT ENAME,JOB, row_number () OVER (PARTITION BY ENAME ORDER BY ENAME) rn
FROM scott.emp )
WHERE rn = 1