-
Table and Column Level Locks
How to handle the table level locks and column level locks?
krishnaindia2007
- Jun 20th, 2008
When you are performing INSERT , DELETE , UPDATE operations on a table it implicity aquires row level locks. If a transaction obtains a row lock for a row, the transaction also aquires a table l...
-
Sub query and correlated sub query
What is the difference between sub query and correlated sub query
krishnaindia2007
- Jun 20th, 2008
In sub query the inner query is executed only once. Depeding upon the results of inner query , outer query is evaluated.In correlated subquery the inner query is evaluated once for each row processed by the parent statement or outer query.
tariq_oracle
- Apr 13th, 2008
subquery: required in those cases where requisted data are not known and always depend on inner qu...
-
Alias in Joins
Why do we use aliases during joining two tables?
krishnaindia2007
- Jun 20th, 2008
Alias name it temporary name given to a tablen or columns of a table.It has its existence until the satement is executed. For example in the following example instead of typing tablenames ...
-
Find all tables in a database
How to find all the tables existing in a database?
krishnaindia2007
- Jun 20th, 2008
To view tables in the current schemaSELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'To view all the tables that you have access SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT...
Imran_Javed
- May 8th, 2008
SELECT TABLE_NAME FROM All_TABLESOrder by TABLE_NAME;The above query will display all the table names in the database.SELECT TABLE_NAME FROM USER_TABLESOrder by TABLE_NAME;The above query will display all the table names in a particular schema.
-
Lock Row of a Table using SQL
How to lock a particular row of a table using SQL?
krishnaindia2007
- Jun 20th, 2008
INSERT , UPDATE, DELETE operations implicity lock rows of a table that satisfies the condition.Explicitly you may lock a table using for update of as followsSELECT * FROM EMPWHERE EMPNO = 7369FOR UPDATE OF SAL NOWAITIt will lock single row of emp table having employee number 7369.
-
Can we use aggregate function in another aggregate functionsuppose we got count of a column by using the group by clause so we get different counts in the same statement how to get the maximum of count
gkbiswal
- Sep 29th, 2007
We can use aggregate function inside another aggregate function.Example:SQL> select max(Count(*)) from emp group by deptno;MAX(COUNT(*))------------- 6DB Version: Oracle9i Enterprise Edition Release 9.0.1.1.1
-
Security for SQL Queries?
Can you provide any security for SQL queries? If Yes, How?
-
How to insert of only system time(hh:mi:ss) into table columnnot in the format of 'dd/mm/yyy hh:mi:ss' only system time not date.
Waseem Mehmood
- Nov 20th, 2007
CREATE TABLE table_name(col1 VARCHAR2(20));INSERT INTO table_nameVALUES(TO_CHAR(SYSDATE, 'HH:MI:SS'))While creating table you must keep in mind the datatype must be Varchar2 for required column
-
-
How can you see table in reverse order of columns using SELECT statement.
saibabu
- Jan 12th, 2007
hi select ..........col3,col2,col1 from tablename;just specify the columns in the reverse order i n the select statement.
-
Export data from SQL to Excel
How to export data from a SQL table to MS Excel?
sameers007
- Apr 6th, 2008
Spooling means just copying what ever we type in SQL prompt rit, I want to know whether any query exists for that
-
How to get fifth maximum salary from the table with out using TOP keywork(without any keywords) in sql server 2000Can you please guide me about this?
monika1985
- May 28th, 2008
Select sal from emp ewhere 4=(select count(distinct(sal) from emp where sal>e.sal)
Imran_Javed
- May 9th, 2008
SELECT * FROM (SELECT ename ,sal, dense_rank() over (ORDER BY sal DESC) max_sal FROM emp)WHERE max_sal= 5;
-
How to find errors of PROCEDURE at run time( not complie time) ?
satyam_Ora
- May 16th, 2008
Hi,what nachs has written is correct.Show error is use to find the compilation error not run time error.For runtime error of procedure we need to take help of exception Handling and that error may be ...
nachs
- Aug 22nd, 2006
Use the exception handlers in run timeuse sqlerrm, sqlcode functionsshow erroris used to find errors during compile time
-
What is a deadlock?
There is a table with Pluks('plk') and Zinks ('zin'). Each Pluk onlyresides on a single Zink and each Zink may contain multiple Pluks. Howdo you find how many Pluks are on each and every Zink? Please provide SQL.What is a deadlock?
krishnaindia2007
- May 16th, 2008
Dead lock is a unique situation in multiuser system that causes two or more users to wait indefinately for a locked resource. First user needs a resource locked by the second user and second user ...
g_sidhu
- Feb 6th, 2008
A deadlock occurs when two or more users are waiting for data locked by each other. When this happens, these users are stuck (deadly embraced) and cannot continue processing. Oracle automatically dete...
-
Establishing many to many relationship in SQL
How will you establish many to many relationship in SQL?Ramco Systems dt. 02 Feb' 2008
krishnaindia2007
- May 16th, 2008
Cartesian product is an example for many to many reolationship in SQL.For exampleselect a.ename, a.job, a.sal, b.dname from emp a, dept bresults in many to many relationship.
rakeshn
- Feb 20th, 2008
EmployeeDepartmentlets say one employee can work in multiple department.It needs many to many relationshipCreate another table EmpDeptMap.it will have foreign key with employee and Deptartment too.In this table (empid+deptid) is the primary key
-
What should be the return type for a cursor variable.Can we use a scalar data type as return type?
The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE
krishnaindia2007
- May 16th, 2008
No we can not use a scalar data type as return type.The return value of a strongly typed REF CURSOR must be a record which can be defined using the %ROWTYPE and %TYPE attributes or as a record structure.
-
How we can get the salary whose is earning more than one person and also i want his name also to display...Ex:-If there is 5 members are there who is earning more than the JonesOutput I want is:Jones 2500 hari 2600Jones 2500 king 5000Jones 2500 rakesh 6000Jones 2500 rajesh 6500Jones 2500 ramesh 7000
Imran_Javed
- May 9th, 2008
SELECT e.ename || ' '|| e.sal || ' ' || e1.ename || ' '|| e1.salFROM EMP E , emp e1WHERE e.sal < e1.sal
venkat2182
- Aug 9th, 2007
Hi....I hope u know about "self-join". The following query will give the above result.SELECT e1.name,e1.salary,e2.name,e2.salary FROM emp_test e1,emp_test e2WHERE e1.name='Jones' FRO...
-
How to obtain employees who earn sal greater than avg sal in the given deptment in classic emp, dept tables
Imran_Javed
- May 9th, 2008
SELECT e.EMPNO, e.ENAME, e.deptno, e. SAL, e1.avg_salFROM emp e, (SELECT AVG(sal) avg_sal, deptno FROM emp GROUP BY deptno ) e1WHERE e.deptno =e1.deptno AND e.sal >e1.avg_sal;
Yoga
- Sep 26th, 2007
Select ename, deptno, sal
from emp where sal > (select avg (sal) from emp) group by ename, deptno, sal -
Get fieldnames from Empty Table
How to get field names from empty table by using SELECT statement;
-
If i have 6 values in one coloumn that is 10,20 30,40 50,60. in this 10,30,50 are max(val),and other are min values. now i want some of max values is 90. write a query for that?
Imran_Javed
- May 12th, 2008
SELECT SUM(col_name)FROM ( SELECT ROWNUM rn, col_name FROM tab_name)WHERE MOD(rn,2)!=0;
Rohan Deshpande
- Jan 30th, 2007
Select sum(max(sal)) from table_name;
Oracle SQL Interview Questions
Ans