1) Take one table is t1 and in that column name is f1 f1 column values are 200 5000 3000 7000 300 600 100 400 800 400 i want display the values asc and desc in a single output. sample output is f1.a 100 200 300 400 500 600 etc...... and f1.d is 5000 4000 3000 2000 1000 etc...
Ans) select f1.a, f2.d from
(Select f1 as a from t order by 1) f1,
(Select f1 as d from t order by 1 desc)f2; [or]
Create table as 'f_order' with coulmn f1.
2) Run the following statement to get the above quesion's
Results.
DECLARE @a INT, @b int
DECLARE @f1 CURSOR, @f2 CURSOR
Print ‘Ascending’
SET @f1 = CURSOR FOR
SELECT f1
FROM f_order order by 1 asc
OPEN @f1
FETCH NEXT
FROM @f1 INTO @a
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @a
FETCH NEXT
FROM @f1 INTO @a
END
CLOSE @f1
Print ‘Descending’
SET @f2 = CURSOR FOR
SELECT f1
FROM f_order order by 1 desc
OPEN @f2
FETCH NEXT
FROM @f2 INTO @b
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @b
FETCH NEXT
FROM @f2 INTO @b
END
CLOSE @f2
DEALLOCATE @f1
DEALLOCATE @f2
3) how can stop the sequence with mention the max value and with out mention the max value?.
take one schema in that t1,t2,.....tn tables and you don't no the table name also. write a procedure if enter columns name then display the maching columns .otherwise display the unmatch columns.
write a pl/sql function if enter a value=0 then output value=1 and vise verse with out using if and case statements.
TABLE A TABLE B
EMPNO ENAME EMPNO ENAME
1 A 1 A
2 B 2 B
3 C 3 C
4 D 4 D
5 E 5 E
6 F
7 G
4) HOW TO GET THE UNMATCHED RECORDS IN SQL QUERY?
Ans) select e2.empno, e2.ename from A e1, B e2
where e1.empno!=e2.empno; [or]
select * from B minus select * from A;
6)I have one table :EID,Depid,ename,sal I want to have max(sal) in each department.
select depid,max(sal) from group by depid; 7) How to delete same emp id in sql query for exmaple in emp table emp id, empname, emp address. for example emp id =5, repeated in two times in rows same id how to delete same empid please any one of the write query send in my id Ans) delete from emp where rowid not in (select max(rowid) from emp group by empno) [or] SELECT ROWID( Emp_id ) FROM EMP WHERE Emp_id = 5; 8) sql query to get zero records from a table having n no of records Select * From Table_Name Where 1 = 2;[or] select * from emp minus select * from emp; 9) What cursor type do you use to retrieve multiple recordsets? Ans) Explicit cursor 10)FindOut 3rd Highest Salary? select a.sal from emp a where 3=(select distinct(count(b.sal)) from emp b where a.sal<=b.sal) 9) What are Global Temporary tables Global Temporary tables are session dependant tables which could be used as temporary storage for calculations, sortingetc. What I mean by Session dependant is, the data being stored in the Global Temporary table is not written into thedatabase or stored anywhere. Once the session ends (in whichthe Global Temporary table is used), the data also vanishes. However the structure would still be available even after the Session is logged out. Also, the structure is available to other sessions even when one session is using it, but not the data. i.e multiple sessions could use the same Global Temporary table without interfering the data. Each sessioncould insert/update/delete their own data into the same Global Temporary table as if the table is available to only that session. Any data inserted in one session is not available to another. Now, why do we need Global Temporary tables? Well, imagine a requirement where you need to fetch some data from the database, do some kind of calculations, aggregations and provide the Result Set (many records) to a Front End. Again, in the Front End, you need to fetch the Result set may times, for some purpose. Then you could make use of the Global Temporary table. Until the user gets disconnected from that Database session, the data is available for him in the memory. [or] Global temporary tables belongs to that session only create global temporary table test_gbl ( l_number_no number, l_char_vc varchar2(100) ) [on commit delete rows] ON COMMIT DELETE ROWS:- It's a default one If any commit will issued that total data of a table will losses. But table is exit To overcome this we have option ON COMMIT PRESERVE ROWS:- means, If commit will issue the data of a table willn't loss up to end of the session. Is session ends the data will losses. 10) how to get the third quarter of employee details from emp? select * from emp where rownum <=(select round((count(*)/4) *3) from emp) minus select * from emp where rownum<=(select round((count(*)/4) *2) from emp) 11) can we call a procedure from a function? 12) why use cursors? Cursor : It's a private SQL worksheet area where we can execute SQL commands and processing information. The purpose of the Cursor is, PL/SQL execution block will process only one records means it will return only one records.If we want to retrieve or process more number of records we use the "Cursors". These are of two types. Implicit Cursor 2. Explicit cursors. 13) How to retrieve Duplicate Rows only in a Table? Suppose if a Table Name is "Education". It consists of multiple columns. Then if we insert rows into this table with duplicate records then how can we retrieve only duplicate records from that table? select * from emp a ,(select empno,count(*) from emp group by empno having count (*) > 1) b where a.empno = b.empno 14) can we delete the trigger in a view? if yes why if not why? As per my knowledge we can drop trigger crated on view see below exp CREATE OR REPLACE FORCE VIEW "SCOTT"."TEST_VIEW" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") AS select "EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", " COMM", "DEPTNO" from emp; SQL> create or replace TRIGGER SCOTT.VIEW_TIG INSTEAD OF INSERT OR DELETE OR UPDATE ON TEST_VIEW BEGIN NULL; END; / Trigger created. SQL> drop trigger VIEW_TIG ; Trigger dropped. 15) scope of exception handling in plsql?. Scope of exception in plsql is handled by Exception Block.. this is depends on the code you had written. suppose u have created one more plsql block inside a plssql block,if the inner block is not handled any exception then the outer block will handle the exception by defining the situation/even like "when others then / when value_error then... etc " 16) How to Remove the 3rd highest salary person record from table? delete from employees where employee_id in (select employee_id from employees where 3 =(select count(distinct e.salary) from employees e where e.salary >= employees.salary)); 17) Query to get max and second max in oracle in one query ? SELECT ENAME,SAL FROM (SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=2 18)why you need store procedure ? where do we use it in a Java project? can you get the code for as store procedure using in Java? Ans)Stored procedure nothing but the set of SQLs which is executed an controlled by database itself.Its useful when you want to use the data from tables to be access frequently.yes, we can get the same SQLs in java code as well. Infact using Callable , stored procedures can be called. 19)difference between join and subquerry there should be a relationship between the tables then only we can use join conditions.... But the sub query can be used to retrieve data from one or more table if there is no relation between the tables.... 20) what is the difference between sql& oracle? sql is language used to interact with database WHILE oracle is database system/platform uses sql to interact with its object [or] SQL is Structured Query Language. Oracle is a Database.SQL is used to write queries against Oracle DB. 21) what is the difference between authorization and authentication? authorization means being capable to access some data or application authentication is a process of being identified by certain condition to become authorized...... but at the same time authentication might not be the only criterion to become authorized...... There is a narrow gap between these two terms..... Ex: to enter into your e-mail account, you should be authenticated by user id and pwd... however it doesn't mean that you are fully authorized to access the whole page. 22) can we insert any row to dual table if i got the permission from DBA? No, its a system table defined by oracle. Even the DBA has read privileges for dual table [or] # 2 Yes, first I connect the as sysdba after that we insert data in the dual table as sql>conn / as sysdba connected sql>desc dual; insert into dual values('A'); (1)row inserted but I found some problems after that ,We use sql> seelct * from dual; Only one value displayed that was default 'x' as system defined data not displayed other data a like 'A'. Dummy ------- x Only x record display in the dual table. 23)What Is Partition? 24) Using a set operator, display the creditor number of all creditors who have ever been paid. select * from TN where bal is not null; intersect select * from TN where bal is null; 25) what is the difference between dbms and rdbms? DBMS - Database management system is a database where it has no relationship with the tables. RDBMS - Relational Database Management System is database where the tables has relationship and also it should support the 12 boyscodd rules 26) what is view? In sql View called the virtual table.Physically it has no existance, so that it don't occupy the space.It retrives the data from original table at runtime. Views are stored in ORACLE system catalog. Basically, in realtime environment views are used to protect the main table for operation.Besides for security purpose view must be used.i.e. one user can hide the main tale from other user for creating view. On basic of creation of view, it is divied into two pars.... 1. Simple view: In that case columns for same table(single table) are fetched for creating a view. Syntax: SQL>create view
FOR EACH ROW
BEGIN
CALL PROCEDURE_NAME();
END;
/
50) find out the third highest salary?
SELECT MIN(sal) FROM emp WHERE sal IN (SELECT distinct TOP 3 sal FROM emp ORDER BY sal DESC);
51) SQLERRM is a
a. Constraint
b. Pre Defined Exception
c. Pseduocolumn
d. Constant
e. None of Above.
Pre Defined Exception .
It works with exception handling section.
52) which language is used to develop the oracle........? Ans) C-Language.
53) What is self-referential integrity constraint ?
If a foreign key reference a parent key of the same table is called self-referential integrity constraint.
54) what is shell?
A shell is a executable program, where all intereactive
unix commands are stored and executed giving final output. (like DOS batch program).
55) which statement is running fastly ie insert or delete?
Its definitely Delete.
Because When Delete operation is being performed then Oracle doesnot actualy permanently remove the data from data block but rather marks that particular data block as unusable.Whereas when concerned to Insert Oracle needs to insert the new values into Datablocks.
56) Difference between sub query and nested query ?
Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.
Nested subquery runs only once for the entire nesting
(outer) query. It does not contain any reference to the
outer query row.For example, Correlated Subquery:
select e1.empname, e1.basicsal, e1.deptno from emp e1 where
e1.basicsal = (select max(basicsal) from emp e2 where
e2.deptno = e1.deptno)
Nested Subquery:
select empname, basicsal, deptno from emp where (deptno,
basicsal) in (select deptno, max(basicsal) from emp group
by deptno)
57) Display the item number and total cost for each order line (total cost = no of items X item cost). Name the calculated column TOTAL COST.
SELECT ITEM_NO , NO_OF_ITEMS*INDIVIDUAL_ITEM_COST AS "TOTAL COST"
FROM ITEM_TABLE
ITEM_NO TOTAL COST
1001 30000
1002 40000
1003 72000
1004 100000
1006 40000
1007 60000
1008 80000
1009 -
1010 56000
I have assumed that ITEM_NO, NO_OF_ITEMS &
INDIVIDUAL_ITEM_COST columns are in a single table ITEM_TABLE
58) what is trigger?
Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time when database modification done.
Triggers are executed automatically on occurance of one of the data-modification operations.
A trigger is a database object directly associated with a
particular table. It fires whenever a specificstatement is issued against that table.The types of statements are insert,update,delete and query statements.
59) why pl sql doesn't support retrieving multiple records?.
pl/sql supports retriving multiple records but u need to
use of cursors. using cursor you can retrieve multiple
records
60) Display the client name in upper case only and in lower case only?.
select lower(ename), upper(ename) from emp;
Subscribe to:
Post Comments (Atom)
change the background color mate.its quite irritating to read.
ReplyDelete