How to find all the tables existing in a database?

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by 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

 Profile Answers by Imran_Javed Questions by 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.

Answer Question  Select Best Answer

What is difference between nested query,sub query and nested query? explain clearly with an example?

Read Best Answer

Editorial / Best Answer

arpitapatel  

 Profile Answers by arpitapatel Questions by arpitapatel

• Member Since Sep-2008 | Sep 24th, 2008

---

Query inside the query is nested query.
It is also called as sub 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.

rams

• Apr 3rd, 2012

Both are same

katgeektalk

 Profile Answers by katgeektalk Questions by katgeektalk

• Aug 22nd, 2010

The subquerry is often reffered to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally excuted first, and its output is used to complete the query condition for the main (or outer) query.

Improve Answer

Which one of the following is descriptive of the effect of an Intent Shared Lock on other transactions? CHOICE1: A Read Lock that prevents writes CHOICE2: A Read Lock that prevents other reads CHOICE3: A Read Lock that prevents more restrictive Locks CHOICE4: A Read Lock that allows other reads CHOICE5: A Read Lock that allows writes (Dirty Reads)

manimehra905

 Profile Answers by manimehra905 Questions by manimehra905

• May 18th, 2010

Read lock that allows other read as it has been made the shared lock intentionally.

hmounir

 Profile Answers by hmounir Questions by hmounir

• May 3rd, 2010

A Read Lock that allows other reads  Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control.No other transactions can modify the data whi...

Answer Question  Select Best Answer

How to display current system time in SQL.

g.sreemayee

 Profile Answers by g.sreemayee Questions by g.sreemayee

• May 19th, 2010

select to_char(sysdate,'HH24:MM:SS') from dual;select current_timestamp from dual;These two will give the current time of the system.1. ll give the time only.2. ll give the time with the date.

Waseem Mehmood

 Profile Answers by Waseem Mehmood Questions by Waseem Mehmood

• May 7th, 2010

You can get the time asSELECT TO_CHAR(SYSDATE, 'HH24:MI:SS')FROM DUAL;

Answer Question  Select Best Answer

How can we retrieve some rows from the database without using select clause?

Ehsan Rehmani

• Jan 31st, 2013

Select clause is must for retrieving data from the database. You cant retrieve data without select clause.

Ehsan Rehmani

• Jan 2nd, 2013

Impossible! Rows will not be rerieved without select keyword.

Answer Question  Select Best Answer

What are the data types in oracle?

MahanamShakyamuni

 Profile Answers by MahanamShakyamuni Questions by MahanamShakyamuni

• Nov 19th, 2010

In SQL, there are mainly varchar2, char, number, date...then each of them has subtypes like number has integer, real etc..Following website gives all details:For SQL datatypes refer:http://download....

pradeepnagaraja

 Profile Answers by pradeepnagaraja Questions by pradeepnagaraja

• Nov 18th, 2010

Different data type used in Oracle are number, char, varchar, varchar2, clob, blob, long.

Answer Question  Select Best Answer

for example i have 50 columns i filter the nulls in FBE.in filter by exp a parameter select_exp .what function is used .how to write the condition .i take a example on emp file(empno,ename,sal......).how to write the condition in filter by expression?

G

• Jan 13th, 2012

U can use : " " to filter null values using code.
Eg:
with worksheet.range("A1:B10")
.autofilter 2, criteria1: ""
end with

shiv

• Jan 2nd, 2012

IS NOTNULL(salary)

Answer Question  Select Best Answer

How to convert rows in column using SQL in Oracle;
for example
sl_no
1
2
3
4
output is 1234;

How to convert one column value into rows using SQL
for example
employee_name
Kumar

output
K
u
m
a
r

Megamind

 Profile Answers by Megamind Questions by Megamind

• Apr 14th, 2014

We can do it via pivot keyword..... it will change the row into column.. its simply a pseudo column

Kandavel Durairaj

• Nov 8th, 2013

Using LISTAGG. Read the oracle documentation for more info.

Answer Question  Select Best Answer

Hi guys try this query

If any student fails in one subject also his result should be Faile r else Pass
condtion is(marks>=40 "PASS" marks

nancy

• Jul 19th, 2016

Code
 

SOnali

• Jun 29th, 2016

Select Name,
when Min(Mark)>40
Then Pass
Else Fail
End
From TableName
Group By Name

Answer Question  Select Best Answer

Self join-Its a join foreign key of a table references the same table. Outer Join--Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don't satisfy the join condition.Equi-join--Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.

Santhoshkandula

 Profile Answers by Santhoshkandula Questions by Santhoshkandula

• May 11th, 2011

        There are 5 types of joins. Those  are   1. Equi Join /  Inner Join    2. Non-Equi Join   3. Outer Join ( Left Outer Join, Right Outer Join, Full Outer Join )   4. Self Join   5. Cross Join

thirupathireddy

 Profile Answers by thirupathireddy Questions by thirupathireddy

• Feb 2nd, 2010

Two join conidtion we can use for subqueries: Equi Join,Self Join

Answer Question  Select Best Answer

select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=&upto minus select rowid from emp where rownum<&Start);

patilpravin_1981

 Profile Answers by patilpravin_1981 Questions by patilpravin_1981

• Jan 30th, 2008

Select * from employees where salary between(1000 and 5000);

LRANI

 Profile Answers by LRANI Questions by LRANI

• Jul 31st, 2007

Hi,minus is used for finding the missing records between 2 tables and not finding the records between the range.It is BETWEEN operator that displays the records between 2 ranges from single table.

Answer Question  Select Best Answer

Nicsmart

 Profile Answers by Nicsmart Questions by Nicsmart

• Mar 31st, 2010

The SELECT....FOR UPDATE [OF column_reference ][NOWAIT] statement identifies the rows that will be updated or deleted, then locks each row in the result set. This is useful when we want to base an upd...

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 6th, 2008

The SELECT ... FOR UPDATE statement identifies the rows that will be updated or deleted, then locks each row in the result set. This is useful when you want to base an update on the existing values in...

Answer Question  Select Best Answer

Vijay

• Mar 20th, 2013

By using merge statement we can do it simply.

smi

• Jul 30th, 2012

Use merge stat

Answer Question  Select Best Answer

Nasim

• Dec 19th, 2006

Maximum we can write 28 triggres on table3 -> insert, update and delete4 -> combination of insert, update and delete2 -> trigger type (for each row and statement)2 -> trigger event (before and after)(3+4)*2*2 = 28

Girdhar Singh

• Aug 22nd, 2006

  total 12 trigger we created but we write trigger how much we want

Answer Question  Select Best Answer

vaibhav Tyagi

 Profile Answers by vaibhav Tyagi Questions by vaibhav Tyagi

• Oct 21st, 2007

Create proc EarnMoneyProcasdeclare @earn int,@total int,@tempday int,@TotDays int set @earn=1set @tempday=1set @total=0set @Totdays = 50*365print @Totdayswhile(@tempday <= @Totdays  )beginset @earn = @earn + 2;set @tempday =@tempday+1print @earnendprint @total

Srinivasa Pavan

• Jan 9th, 2007

hai allam a new to this site and i can give a solution to this Question and here is the coding for that.declare result number :=1;inc number ?:=2;year number :=1;i number;beginwhile??(?year?<=50?)?...

Answer Question  Select Best Answer

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Jun 22nd, 2008

%tye and %rowtype provide data independence and allows programs to adapt to database changes due to new business requirement.%type is used to declare a variable with same datatype as that of...

BASVARAJ KOLUR

• Mar 9th, 2007

the %type will return datatyp attached to refernced column or itemex: v_a emp.empid%type;it will return datatype attched to empid of emp table. the %rowtype will return all datatype attached ever...

Answer Question  Select Best Answer

harshasp

 Profile Answers by harshasp Questions by harshasp

• Jul 16th, 2008

To join ntable we need n-1 join conditions....And whenever we are not able to retrieve the data using Joins..we use Subqueries...Writing the conditions to query throught join is difficult but through subquery its easy.and performance wise Joins are good...

krishnaveni_g

 Profile Answers by krishnaveni_g Questions by krishnaveni_g

• Jul 16th, 2008

we want to join n table we need n-1 join conditions.  There is no limitation. Some queries not solved by using the joins we can go for subqueries.  Performance wise joins is better.

Answer Question  Select Best Answer

vinoth.jeevanandam

 Profile Answers by vinoth.jeevanandam Questions by vinoth.jeevanandam

• Mar 3rd, 2009

You cannot use =, >, >= on rownum operator. Instead you can have,select employee_id from (select row_number() over(order by employee_id asc) as rn, employee_id from employees) where rn=2;select employee_id from (select rownum as rn, employee_id from employees) where rn=2;

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Jun 22nd, 2008

We should not use >, >=,  =  with rownum.It do not show any error message but returns 0 rows.Here select ename,job,salfrom emp where rownum = 2;Rownum always assigns rownum 1 to first ...

Answer Question  Select Best Answer

Sujata Bisoi

• Mar 8th, 2007

 i think it will perfectly work:-            delete empname from emp A where  rowid > (select min(rowid) from emp B where A.ename = B.ename);

Pawan Ahuja

• Dec 3rd, 2006

Delete from where rowid not in (select min(rowid) from group by col1,col2....)RegardsPawan Ahuja

Answer Question  Select Best Answer

YASODHA

• Aug 29th, 2011

Insert is faster than delete because insert just add values to the table. but delete want to store the deleted data in the memory for future references

Rakesh

• Jul 22nd, 2011

Both insert and delete are used for different purposes. Also inserts are not always faster than delete. If the table has got indexes then insert will be slow and delete will be fast (if the WHERE cla...

Answer Question  Select Best Answer