Common SQL DDL Clauses

SQL data definition clauses that appear in multiple
SQL statements.

allocate_extent_clause
constraints
deallocate_unused_clause
file_specification
logging_clausen parallel_clause
physical_attributes_clause
storage_clause



allocate_extent_clause
Purpose
Use the allocate_extent_clause clause to explicitly allocate a new extent for a
database object.
Explicitly allocating an extent with this clause does not change the values of the
NEXT and PCTINCREASE storage parameters, so does not affect the size of the next
extent to be allocated implicitly by Oracle.

SQL Questions

1)What are the key differences between a corelated query and sub query?

In the Corelated query for every instance of outer query,inner query is executed and based on the result of the inner query outer query displays the records. In the sub query depending upon the inner query the outer query will be executed.

2)What is a dead lock?

If two or more users are accessing the same data source with different operations then all the user resources are in dead lock.

3)What are indexes ? Name the various types of Indexes available along with the usage (i.e where and which type of index has to be used?)

Index provides direct access to the data.
The Various types of indexes are Btree Index,Function-Based Index,Bitmap Index,Domain Index

4)What are Autonomous transaction ?

Autonomous is an independent transaction which has to be commited or rollbacked before passing the control to the parent transaction

5)What is mutation error and how to overcome this?

The mutation error is occurred when the row-level trigger access the same table while executing

6)What is a REF cursor?

REF cursor is used to return Record set to be returned from stored procedures,functions and packages.
ref cursor is a simple cursor but it is a datatype that allow developers to declare cursor variable

ref cursor has 2 types:-

strong ref cursor where we mention the return type with rowtype.
weak cursor where we don't mention return type.the advantage of this is that we can use weak cursor with any query.it is not rowtype bounded.
for ex:-strong ref cursor
type curvar_type is ref cursor return emp%rowtype;
weak cursor:-
type curvar_type is ref cursor is


7)What is a materialized view? Where should this be used and what are the advantages of using this?

Materialized views can hold data.They are used to update the remote data base.

8)What is a bulk collect?

Bulk collect facilitates high-speed retrieval of data.Bulk collect is used to fetch multiple rows into one or more collections,rather than individual variables or records.

9)What is dynamic SQL? How does a dynamic sql get fired?

Dynamic SQL is one which is executed at run time.

10)What is rowchaining?

Row Chaining is the storage of data in a chain of blocks.This primarily occurs in lob,clob,blob or big varchar2 data types.

11)what a table is dropped, what are the associated objects that get invalidated / dropped?

Objects which get invalid are Views,Synonyms. Objects which get droped are triggers,indexes,constaints

12) Difference between POST-TEXT-ITEM and WHEN-VALIDATE-ITEM?

Post-text-item will fire every time your cursor moves out of the field. It doesn't matter whether a value is newly entered or is being queried from database. This will fire in both cases.

When-validate-item will fire only when you have entered new value or you change an exixting value of an item. This means that you this will fire only when the status is CHANGED or NEW.


13) This Question is for diff b/w POST-TEXT-ITEM and WHEN-VALIDATE-ITEM?
Suppose you have two fields 'A' and 'B'
A will Have initial value --> 100
Now u want go to change the Value of A to ---> 200 (WVI will be fire)It will not give any error
And in When Validate Item u give a condition ---> If a<500
And u r assigning the value to 'A' in POST-TEXT-ITEM --> now the POST-TEXT-ITEm will fire and the value of 'A' should be change to 700.

Now the Question is WHEN-VALIDATE-ITEM will raise error or not?
Yes, It will fire and it will give an error message. for when validate item there is no need to change the value if it is change as internally then also WHEN-VALIDATE-ITEM will fire.

Question: Why are PL/SQL ref cursors important to PL/SQL performance?

Answer: The ref cursor is a "pointer" data types that allows you to quickly reference any cursor result (usually an internal PL/SQL table array) with data values kept in super-fast RAM.

Definition of a ref cursor
The name "ref cursor" can be confusing because a ref cursor variable is not a cursor, but a variable that points to a cursor. In essence, a ref cursor allows you to store the result of a "bulk collect" from a table (or any PL/SQL cursor return set) into a PL/SQL table (RAM array) and then reference the array with the ref cursor as a pointer. Here is a simple example of using a ref cursor.

Once you load a PL/SQL table (an in-RAM array), a ref cursor allows you to reference the array within your PL/SQL code without having to pass the array to a new procedure or function. Instead of passing the "baggage" of the actual RAM array, you simple pass the ref cursor, which points to the array values.

Important Sql Queries

1)How to display "Experience of employee"?e.g. 3 years 4 months 19 days?

select floor((trunc(sysdate) - to_date('01-JAN-01'))/365) years,
floor(mod((trunc(sysdate)- to_date('01-JAN-01')),365)/30 ) months ,
mod(mod((trunc(sysdate)- to_date('01-JAN-01')),365),30) days from dual;

2) How to delete a duplicate records in a table without using rowid?

delete from student
where id=(select max(id) from student group by id
having count(id||name)>1);

for emp table:

delete from emp
where empno=(select max(empno) from emp group by empno
having count(empno||ename)>1)

3)How to retrieve record number 70 from a table of 100 records?

(select * from emp
minus
select * from emp where rownum < 70)
intersect
select * from emp where rownum <=70;

4)What is Materialized View?

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely or used to create summary tables based on aggregation of a tables data. Materialized views which store data based on the remote tables are also know as snapshots.

5) What is normalazation,types with examples. with queries of all types?

definition :1) normalization is organizing data in database.

2) Normalization is a process of eleminating the redundancy and increasing the integrity.

There are 5 normal forms. It is necessary for any database to be in the third normal form to maintain referential integrity and non-redundance.

First Normal Form:

Every field of a table (row,col) must contain an atomic value

Second Normal Form:

All columns of a table must depend entirely on the primary key column.

Third Normal Form:

All columns of a table must depend on all columns of a composite primary key.

Fourth Normal Form:

A table must not contain two or more independent multi-valued facts. This normal form is often avoided for maintenance reasons.

Fifth Normal Form:

is about symmetric dependencies.
Each normal form assumes that the table is already in the earlier normal form.

6)Check Constraint

CREATE TABLE temp(
eno NUMBER CONSTRAINTS pk_eno PRIMARY KEY,
gender CHAR(1) CHECK (gender IN( 'M','F')));

7)Whats the back end processes when we type "Select * from Table"?

First it will look into the System Global Area (SGA) weather the query is been exectued earlier.
If it exist, it would retrive the same output present in memory.
If not the query we typed is complied and the resulting parse tree and excution plan is been stored in SGA. Then query gets executed and output is given to the application.

8) PRE-QUERY

The Pre-Query trigger fires just before Form Builder issues the SELECT statement to the database, after the operator has defined the example record by entering query criteria in Enter Query mode.

9) POST-QUERY

The Post-Query trigger fires after the query has executed, when Form Builder is fetching records into the form. Post-Query fires once for each record retrieved into the form, which allows you to read and set the values of items in a fetched record before the operator sees them displayed in the block.

10) With out Using Like Function

substr(name,1,1)='S' and
substr(name,-1)='N'

Instr(name,'S')= 1 and
Instr(name,'N')= length(name)

'S'||Substr(name,2,length(name)-1)||'n' = name

11) How to find Duplicate Records

select * from emp where rowid not in ( select min(rowid) from emp group by ename);


12) Pivote table ( change rows as columns)

select decode( r, 1, to_char(sal), 2, sno)
from salary, (select rownum r from all_objects where rownum <= 2 )
where sno = 1

select stno,
max(decode(rn,1,course_code)) s1,
...
max(decode(rn,N,course_code)) sN
from ( select stno, course_code,
row_number() over (partition by stno order by course_code )
from t )
group by stno

13) Generating the Sequence Number without using Sequence

SELECT * FROM (SELECT LEVEL FROM dual CONNECT BY LEVEL < 10);

14)

SELECT EMPNO_ACN,EMPNO_DES FROM
(SELECT EMPNO EMPNO_ACN, RANK() OVER (ORDER BY EMPNO DESC) R FROM EMP ) A,
(SELECT EMPNO EMPNO_DES, RANK() OVER (ORDER BY EMPNO) X FROM EMP ) B
WHERE A.R = B.X

15)

What is the Difference between DBMS and RDBMS ?

Ans :


1. dbms is a single user system rdbms is a multiuser system
2. dbms do not store data in the form of tables while rdbms stores it in the form of tables.
3. most of dbms are not supporting client/server architecture as it is supported by rdbms.
4. most dbms do not support distributed databases
5. In DBMS store and retrieve small amount of Data In RDBMS store and retrieve large amount of Data
6. RDBMS is more secure than DBMS
7. in rdbms is relation is more important than object itself while dbms entity is more important
8. In DBMS tables are not related. In RDBMS tables are related

16)

Is there any function in oracle to check whether a value is numeric or not. I have a column which contains both numeric and alphanumeric data. I want to fetch only the numeric data from that column. Can anyone tell me how to do it?


Hi Anu,

There does not exists any build in function which checks for a numeric value instead you can write your own function as follows.

CREATE OR REPLACE FUNCTION Isnumber(p_num VARCHAR2) RETURN NUMBER
AS
a NUMBER;
BEGIN
a := p_num;
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;
/


This function can now be called in a SQL query which will retum 1 if the value passwd is numeric else it will return 0.

OR

select * from test where translate(a,'_0123456789','_') is null;
=====================================
Check Constraint
--------------------
ALTER TABLE suppliers
add CONSTRAINT check_supplier_name
CHECK (supplier_name IN ('IBM', 'Microsoft', 'Nvidia'));

How to see the Difference between two table coulmns:-
========================================================
scott@ORA817.US.ORACLE.COM> create table t1 as select * from emp where 1=0;

Table created.

scott@ORA817.US.ORACLE.COM> create table t2 as select * from t1;

Table created.

scott@ORA817.US.ORACLE.COM>
scott@ORA817.US.ORACLE.COM> alter table t2 drop column ename;

Table altered.

scott@ORA817.US.ORACLE.COM> alter table t2 modify job varchar2(10);

Table altered.

scott@ORA817.US.ORACLE.COM>
scott@ORA817.US.ORACLE.COM> column data_type format a10
scott@ORA817.US.ORACLE.COM> (
2 select 'IN T1, NOT T2', column_name,data_type,data_length
3 from user_tab_columns
4 where table_name = 'T1'
5 MINUS
6 select 'IN T1, NOT T2', column_name,data_type,data_length
7 from user_tab_columns
8 where table_name = 'T2'
9 )
10 UNION ALL
11 (
12 select 'IN T2, NOT T1', column_name,data_type,data_length
13 from user_tab_columns
14 where table_name = 'T2'
15 MINUS
16 select 'IN T2, NOT T1', column_name,data_type,data_length
17 from user_tab_columns
18 where table_name = 'T1'
19 )
20 /

'INT1,NOTT2' COLUMN_NAME DATA_TYPE DATA_LENGTH
------------- ------------------------------ ---------- -----------
IN T1, NOT T2 ENAME VARCHAR2 10
IN T1, NOT T2 JOB VARCHAR2 9
IN T2, NOT T1 JOB VARCHAR2 10

scott@ORA817.US.ORACLE.COM>
===============================================================
20)How to find out department wise second maximum salary.

select distinct d1.deptno, e1.sal

from emp e1, dept d1

where 2 = (select count(distinct e2.sal)

from emp e2, dept d2

where e2.sal >= e1.sal

and d2.deptno = e2.deptno

and d1.deptno = d2.deptno)

and d1.deptno = e1.deptno
=================================
21) In a cursor going we have 10 records now i want go to exactly for only 5th record.is it possible??
Ans:-
Yes, It is possible u can use the rowcount after fethchin
Declare
cursor cur_t is select * from t4;
rec_t t4%rowtype;
i number(2);
begin
open cur_t;
for i in 1..10
LOOP
fetch cur_t into rec_t;
if cur_t%rowcount in (5) --or IF i in(5) also we can use
then
dbms_output.put_line('no: ' || rec_t.no || ', name: ' || rec_t.name);
end if;
end loop;
end;
=======================================================
Mutating table Example:-
-----------------

Example 1

Select in a mutating table from a row trigger (Tested on Oracle 8.1.7)

We want to explain this situation on an example. We have two tables "A" and "B". "A" is the master table and "B" the detail table. We specified a foreign key between "B" and "A" with the CASCADE DELETE option.



Here are the CREATE statements

drop table B;
drop table A;

create table A (
ida number not null,
vala varchar2(10),
primary key(ida));

create table B (
idb number,
valb varchar2(10),
foreign key (idb) references A (ida) on delete cascade)
/

create or replace trigger b_br
after delete on B
for each row
declare
n integer;
begin
select count(*) into n from A;
dbms_output.put_line('there are ' || n || ' rows in A');
dbms_output.put_line('after statment on B');
dbms_output.new_line;
end;
/

insert into A values(1,'Table A');
insert into A values(2,'Table A');
insert into B values(1,'Table B');
insert into B values(1,'Table B');
commit;

set serveroutput on;
delete from A where idA = 1;

ERROR at line 1:
ORA-04091: table SCOTT.A is mutating, trigger/function may not see
ORA-06512: at "SCOTT.B_BR", line 4
ORA-04088: error during execution of trigger 'SCOTT.B_BR'

1) Retrive the last 10 rows from a table

select rownum from emp
group by rownum
having rownum >=(select max(rownum)-10 from emp)
(or)
select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm)
where a > ( select (max(rownum)-10) from clm)

2) How to Retrive the Duplicate rows from a table

SELECT <COLUMN_NAME> , COUNT(*) FROM TABLE.NAME
GROUP BY <COLUMN_NAME>
HAVING COUNT(*) > 1

3) Department wise 2nd heighest Salary

select distinct d1.deptno, e1.sal
from emp e1, dept d1
where 2 = (select count(distinct e2.sal)
from emp e2, dept d2
where e2.sal >= e1.sal
and d2.deptno = e2.deptno
and d1.deptno = d2.deptno)
and d1.deptno = e1.deptno
===================================================
Select max(e1.sal),e1.deptno
from emp e1
where sal <=(select max(sal)
from emp e2
where e2.deptno = e1.deptno)
group by e1.deptno

Hewitt Associates(26-JUN-07):-
==============================

1)To get the balance based on DRCR

select SUM(DECODE(DRCR,'C',amount,0))-SUM(DECODE(DRCR,'D',amount,0)) from account

2)
Column
=========
3
2
1
A001
A002
A003
1001
1002
1003

Using the Order by Whats the Order will come

Answer is
SNO -->VARCHAR2 field
----
1
1000
1001
2
3
A001
A002
A003
a004
a200

It will Give the Assending/desending Order Based on Ascii Value...

Inline View:-
===============

SELECT ename,deptno
FROM (SELECT ename,deptno
FROM emp)a
(SELECT deptno
FROM emp)b
WHERE a.deptno = b.deptno

--->select sal,sum(sal) over (order by sal) TOTALSALARY from salary;
SAL TOTALSALARY
---------- -----------
1000 1000
2000 3000
3000 6000
4000 10000

checking that the string does not contain any special character

checking that the string does not contain any special character other then '_':

SELECT NVL(LENGTH(LTRIM(RTRIM(TRANSLATE('RAGHA **NDRA','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890_',' ')))), 0)
FROM DUAL;

other way:

op_type1 := replace(:blk_cgtm_op_type.op_type,' ','*');

FOR i IN 1 .. length(op_type1)
LOOP
op_char := ASCII(SUBSTR(op_type1,i,1));


IF OP_CHAR not BETWEEN 48 AND 57
and OP_CHAR not BETWEEN 65 AND 90
and OP_CHAR not BETWEEN 97 AND 122
then
l_found := 1;
end if;
END LOOP;

IF L_FOUND = 1
THEN
alert_return := ovpkcs.fn_dispmsg('CG-MNT18',';', ';');
Raise FORM_TRIGGER_FAILURE;

end if;
*********************************