Views are created on the basis of base tablesWhat is the query to know the base table of the view? How to know the details of base table?

Prem_p

 Profile Answers by Prem_p Questions by Prem_p

• Apr 28th, 2011

Guys, you don't need a dba privilege for this.Run the query:select * from USER_DEPENDENCIES where  type='VIEW' and name=''This will give you the dependent object information (name , type of the obje...

Balakrishna12207

 Profile Answers by Balakrishna12207 Questions by Balakrishna12207

• Feb 7th, 2011

SELECT * FROM dba_views WHERE View_name=;Text column can contain "The details of view and table"

Answer Question  Select Best Answer

How to count number of records in a table without using COUNT function?

Sandipan

• Sep 6th, 2012

Select sum(1) from emp;

Code
SELECT sum(1) FROM emp;

smily08

 Profile Answers by smily08 Questions by smily08

• Aug 30th, 2012

Select max(rownum) from
(select ROW_NUMBER() over (order by col_name) as rowNum from table_name)
withRowNum

Answer Question  Select Best Answer

If you have a three columns table with one index on 2nd Column. In the SELECT statement you retrived column 1, can index be used or not?

amitsrivastava115

 Profile Answers by amitsrivastava115 Questions by amitsrivastava115

• Aug 2nd, 2011

Use index in the where clause , this way you can use it other wise not ...

geetha

• Jul 30th, 2011

Index will be used if the retrieval is based on an indexed column .if the where clause uses a column with index then the query will be executed based on that index.

Answer Question  Select Best Answer

Display details of employees having same char at the star and end postition of their namelike abishikathis name have last character and first character is same

yuvaevergreen

 Profile Answers by yuvaevergreen Questions by yuvaevergreen

• Jun 8th, 2011

In Teradata,sel * from employee where substr(name,1,1)=substr(name,length(trim(name)),1)

Miao Lin

 Profile Answers by Miao Lin Questions by Miao Lin

• May 9th, 2011

SELECT * from employee where SUBSTR(EmployeeName, 1,1) = SUBSTR(EmployeeName, -1,1);

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

nirmal1in  

 Profile Answers by nirmal1in Questions by nirmal1in

• Member Since Jan-2010 | Jan 17th, 2010

---

This can be accomplished in following way:

Example: table - emp

For First n rows:

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a < 5 --- say n is 5 display first 5 records

For last n rows:

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename) a
FROM emp) x
WHERE x.a < 5

FROM )WHERE .a<

This query can be used for finding nth row also say n = 5

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a = 5

deepthi

• May 17th, 2012

Code
SELECT rownum,a.*FROM(SELECT rownum,b.*FROM emp b ORDER BY rownum DESC) a WHERE rownum <&n;

Vasavi Katakam

 Profile Answers by Vasavi Katakam Questions by Vasavi Katakam

• Jul 18th, 2011

For First N Rows:

Code

1.  
2. SELECT top 5 * FROM table_name
3.  

For Last N Rows:

Code

1.  
2. SELECT * FROM
3. (SELECT top 5 * FROM
4. table_name
5. ORDER BY Columnname DESC)
6. ORDER BY Columnname ASC
7.  

Improve Answer

ngoudjou albert

• May 22nd, 2012

We use the selfjoin on the table employees to retrieve the job before thier managers

Code
SELECT * FROM employees e , employees m
WHERE e.manager_id=m.employee_id
AND e.hire_date < m.hire_date;

janardhan g

• Aug 7th, 2011

Select x.ename from emp x,emp y where x.mgr=y.empno and x.hiredate

Answer Question  Select Best Answer

farhan

• Apr 20th, 2012

Code
SELECT * FROM emp  WHERE sal >(SELECT avg(sal) FROM emp)

uma

• Mar 6th, 2012

Select * from emp a
where salary >(select avg(salary) from emp b where a.dept_no = b.dept_no);

Answer Question  Select Best Answer

Bobby

• Apr 18th, 2018

Rowid is pseudo column and it can change internally especially on transactional table

himabindu

• Apr 16th, 2018

Why use rownum < 2?

Answer Question  Select Best Answer

Shubham

• Oct 7th, 2016

No,
We can not give more then 32 column primary key

Somesh Menaria

 Profile Answers by Somesh Menaria Questions by Somesh Menaria

• Feb 22nd, 2016

Server process write data from datafiles to buffer cache

Answer Question  Select Best Answer

sampra

 Profile Answers by sampra Questions by sampra

• Mar 6th, 2012

Select * from hello h where 1=(select count(discnt amt) from hello where h.amt

nirmal1in

 Profile Answers by nirmal1in Questions by nirmal1in

• Dec 25th, 2010

Say for example below are data exist on EMP tableEMPNO  EMPNAME                DEPTNO      ...

Answer Question  Select Best Answer

JILEY SINGH

 Profile Answers by JILEY SINGH Questions by JILEY SINGH

• Aug 4th, 2013

Function can be called in select statement but procedure cant be called in select statement. function has return keyword but procedure not

Varun Tiwari

• Jul 4th, 2013

Function Procedure 1. Can be used as column Cant used in select statement in select statement 2. Can be used in where Cant used in where condition c...

Answer Question  Select Best Answer

 %  TYPE  provides  the data type of a variable or a database column to that variable. % ROWTYPE  provides the record type that represents a entire row of a table or view or columns selected in the cursor. The advantages are : I. Need not  know about variable's data typeii.  If  the  database  definition of a column in a table changes, the...

PRADEEP

• Oct 16th, 2012

%rowtype is associated entire table(if we want to declare all columns then we need to declare %rowtype).

%type is associated with one column .(if we want to declare entire column then we need to declare %type).

Yaseen

• Aug 9th, 2012

If using %type and %rowtype , no need to know the the data type of the table column . and once the development is finished , the type of column is changed it will not effect the coding.

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: NK

• Jun 29th, 2005

---

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle performs a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity. 
 
Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list. 
 
To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, in the case of the cost-based optimization approach, statistics for the tables. 
 

csbhaskar

 Profile Answers by csbhaskar Questions by csbhaskar

• Apr 7th, 2011

A join is a query that results in combining rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever names of multiple tables, views or materialized views a...

nirmal1in

 Profile Answers by nirmal1in Questions by nirmal1in

• Jan 3rd, 2010

Join is relation between more than one table. No of minimum join conditions required is equal to n-1 (where n is number of tables joined together)

Improve Answer

Answer posted by Scott on 2005-05-25 18:30:04: TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server. DELETE is a DML command and can be rolled back.  Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.

Read Best Answer

Editorial / Best Answer

Answered by: Prangya Sahu

• Nov 21st, 2005

---

1>TRUNCATE is a DDL command whereas DELETE is a DML command.

2>TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.

4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause.

Shahida Sultana

• Mar 6th, 2014

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesnt use as much undo space as a DELETE.

saravanan

• Sep 18th, 2012

Delete & truncate command will delete entries from a table.. where as we can delete a single entry using delete but incase of truncate it wont be possible..

using truncate will delete the log files,buffer storage in a table permanently but incase of delete it wont

Improve Answer