-
Explain psudo column Label with an example
g_sidhu
- Feb 7th, 2008
LEVELYou use LEVEL with the SELECT CONNECT BY statement to organize rows from a database table into a tree structure. LEVEL returns the level number of a node in a tree structure. The root is level 1, children of the root are level 2, grandchildren are level 3, and so on.
Tatyana
- May 5th, 2006
Sorry, sorry, I am wrong. Rowid is a pseudocolumn.
-
Explain what is mutation and what is mutating table and how this mutation problem is solved in a table
Nicsmart
- Mar 31st, 2010
A mutating table is a table that is currently being modified by an INSERT,UPDATE,DELETE statement or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential in...
Nasim
- Aug 24th, 2007
One solution also exist:create a row level trigger and insert/update the data and take it in collections( varray or nested table or associative array). Then write a after statement level trigger and fetch that data.
-
-
What is join? Types of Join & Difference between each.
sindhu
- Aug 2nd, 2007
Thanks for your answer.It helped me alot for my interview.
BinduJoseph
- Jan 22nd, 2007
First lets consider these two tables. AKey Data----------- ----------1 a 2 b BKey Data----------- ----------1 c 3 d ...
-
What is difference between Varchar2 and Nvarchar2?
Name
- Mar 23rd, 2015
Nope. Thats SQL server.
mukeshkoshym
- Nov 26th, 2009
In simpleVarchar2 works with 8 bit characters where as Nvarchar2 works ith 16 bit characters.If you have to store data other than english prefer Nvarchar2 or viceversa.
-
Hi1. State the order in which the trigger will get fired2.I have an doubt in trigger. I created two trigger. trigger name is only different but structure is same. I want to know the order in which these two trigger will get fired
krishnaindia2007
- Jun 22nd, 2008
1.Firing sequence of Database triggers on multiple rows Before statement trigger Before row trigger for first row After row trigger for first row Befo...
Prashanth
- Apr 24th, 2007
The one that is created last executes first
-
I have a customer table custid,Trade,Amount three fields are there custid dont have unique value, now trade B & S values. I want to Show sum of amount of custid, for both the trade in one line Like cust id,Btrade,Bamount,Strade,Samount1 B 12000 S 5000 This One i tried but problem in thisselect a.Custid,a.TRADEID,sum(a.AMOUNT),b.TRADEID,sum(b.AMOUNT) from custmer a,custmer bwhere a.Custid = b.Custid and A.TRADEID = 'B' and b.TRADEID = 'S' group by a.Custid,a.TRADEID,b.Custid,b.TRADEID
Imran_Javed
- May 9th, 2008
SELECT MIN(b.CUSTID) "cust id", MIN(b.TRADE) "Btrade", SUM(b.AMOUNT) "Bamount", MIN(s.trade) "Strade", MIN(s.amount) "Samount"FROM cust_t b, (SELECT MIN(CUSTID),MIN(TRADE) trade,SUM(AMOUNT) amount FROM cust_t WHERE trade ='s') s WHERE b.trade = 'b';
jamesravid
- Dec 10th, 2006
Hi Try this query,Select custid, 'B' as BTrade, sum(decode(tradeid,'B',amount,0) Bamount 'S' as Strade, sum(decode(tradeid,'S',amount,0) Samountfrom customergroup by custidRegards,James.
-
Where are dropped tables storedCan I have same table with the same name in one tablespace?
madhuk17
- Feb 18th, 2007
Tables once dropped are permanently removed from database and hence they are stored no where in database.
Radhi09
- Feb 15th, 2007
HiAs of now i dont think there is any thing of that sort in oracle 9i ,but 10g provides the option of flash backthanks
-
What is the Difference between stored procedures and external procedures?
sampra
- Feb 26th, 2008
An external procedure is simply a program unit that is not written in Oracle’s SQL or PL/SQL and resides outside of the database. External procedures must be stored as shared libraries at the op...
g_sidhu1
- Feb 21st, 2008
An external procedure is simply a program unit that is not written in Oracle’s SQL or PL/SQL and resides outside of the database. External procedures must be stored as shared libraries at the operating system level. In a Windows NT environment, the procedure would be compiled into a DLL.
-
How many null value a unique key constraint can have?
I think there can be only one null value for a unique key constraint but some say there can be more than one null value.
ammupriyaa
- Aug 22nd, 2008
Can have any number of NULL values, since oracle?maintains internally different values for each?NULL values.
-
How centralized DBMS differs from distributed DBMS .
ambika
- Oct 21st, 2013
Recovery in centralized dbms
su4surya
- Aug 24th, 2009
A centralized database has all its data at one place so there may occur problems of data avalability, and a system crash may lead to whole dataloss. In a distributed database, database is stored on se...
-
How to use "CASE" keyword in SELECT statement?
AVADHESH
- Sep 30th, 2011
Code- SELECT newsid, news,STATUS,
- case STATUS
- WHEN '1' THEN 'Active'
- WHEN '0' THEN 'Deactive'
- END AS fieldnm
- FROM news ORDER BY newsid;
sirisha.hari
- Jan 6th, 2008
hi,select sno,sname,(case when total>50then 'good'when total>35 and total<50then 'average'else 'fail'end)resultfrom student_result
-
SQL Query help
Needed a SQL query for this----------I have table with columns firstname, lastname and phone number and we have 2 entries in that table ...now I want to know the persons with this combination firstname of person = lastname of second person? ln phone----------------------------------john cubick 1223sam course 4556output:john course
geminine79
- Jan 3rd, 2008
I think you can use the cartesian product on the same table and try to get the data out of that. try this and let me know.
naveen.papineni
- Nov 26th, 2007
Select first_name ,last_name ,phone_number from table where first_name in (select last_name from table )I think this will work out.RegardsNaveen.
-
Oracle long datatype
I'm using oracle9i to create a table which stores lyrics in long datatype. But if i store a long string of text and display it from the table, it displays only 80 chars. So someone help me how to store the whole lyrics txt file
ramyaselvi16
- Jun 24th, 2008
Variable length character values up to 2 gigabytes. Only one LONG column is allowed per table. You cannot use LONG datatype in functions, WHERE clause of SELECT, in indexing and subqueries.
-
Constraint and Index
What is difference between constraint and index? How do use them in SQL query?
krishnaindia2007
- Jun 21st, 2008
Constraint is used to restrict invalid and inconsistent data entry into the table.Index is used for faster retrieval of data.Both are entirely different concepts.
srinivasa.dinesh
- Jan 29th, 2008
Constraints are rules applied to Table/Columns of a table. Constraints are one type of restriction which are useful while data is manipulated.Index are ordered list of rows of tables. Index are useful while faster retrival of data.Hope this anwsers to your question.Cheers,Srini.
-
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...
-
Retrieve data from two tables
How to retrieve data from two tables of different servers
Sujaya
- Jun 8th, 2012
SELECT Ename, DeptNo, Sal, Emp.DeptNo, Dept.Dname, Dept.Loc, Dept FROM
Emp, Dept
WHERE Emp.DeptNo = Dept.DeptNo;
-
Tune SQL Query
How can you tune the query?
Imran_Javed
- May 8th, 2008
First look at the explain plan if there is any full scan. Then add appropriate index on columns that are used in the query. Basically, always add index on foreign key and the date columns (Most of the application access tables based on dated)
-
DOS command
How to execute DOS Commands from SQL prompt?
vinodsahani
- Apr 12th, 2008
Either you can type HOST to go into DOS prompt, use DOS commands and then type EXIT to return back.OR, if you want to run only one DOS command you can type HOST Hope this will work for u.
-
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
Oracle SQL Interview Questions
Ans