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
No comments:
Post a Comment