-
What is reference cursor?
sandyl2
- Jun 22nd, 2011
A Normal Currsor is like a Constant but RefCursor is a Variable. RefCursor is a Dynamic Cursor. Main Advantage of RefCursor is same Cursor can be associated with different Select Statements.
-
How to write a query to unsort while retrieving the data. it should be niether in ascending nor descending order
malapati
- Oct 10th, 2008
By default the "select" statement retreives the data in "unsort" form. If we want to sort the data we need to use "ORDER BY" clause. eg: select * from < table name > order by < column name >;?if we specify "desc" after column name it sort out in desending order otherwise default is asending order
Lavu
- May 6th, 2007
If you are selecting the data from the table it will not sort the data. If you are using group by clause then it will sort the result in the ascending order other wise not.
-
How do you find the number of rows in a table?
-
TRUNCATE TABLE EMP;,DELETE FROM EMP;, Will the outputs of the above two commands?
Both will result in deleting all the rows in the table EMP.Answered by Scott on 2005-05-12 10:31:58: The difference is that the TRUNCATE call cannot be rolled back and all memory space for that table is released back to the server. TRUNCATE is much faster than DELETE and in both cases only the table data is removed, not the table structure.
yuvaevergreen
- Jun 14th, 2011
In Teradata,Drop table and delete table are the options. delete table is preferred.
vinod.km
- May 14th, 2009
CREATE TABLE ABC
(
Col1 INT
)INSERT INTO ABC VALUES (1)
BEGIN TRAN
TRUNCATE TABLE ABC
ROLLBACK TRAN
SELECT * FROM ABCIn this way, I am able to roll back truncate command. So in
SQL server we can Roll Back TRUNCATE Command. -
How to find out the 10th highest salary in SQL query?
yuvaevergreen
- Jun 14th, 2011
In Teradata, even if the duplicate salaries are present, below query can be used.sel b.* from (sel a.*,rank() over (order by a.salary desc) as rnk from(sel salary from samples.empgroup by salary ) as a) bwhere b.rnk=10
rohit1505
- May 21st, 2011
SELECT *FROM emp aWHERE &n = (SELECT COUNT(DISTINCT sal))FROM empbWHERE a.emp<=b.emp);
-
How many null values does a column accept if it is declared as a unique key?
Pritesh Modi
- Jun 2nd, 2011
There is only one null values accepted by a column if you assign a column as unique key constraint.thanking youpritesh
-
SQL Query to return only duplicate rows and count
I have a table with duplicate names in it. Write me a query which returns only duplicate rows with number of times they are repeated?
yuvaevergreen
- Jun 14th, 2011
sel name,count(*) from retail.yuvagroup by 1 order by 2 having count(*) >1
-
Single Row Function
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
- Jun 8th, 2011
In Teradata,sel * from employee where substr(name,1,1)=substr(name,length(trim(name)),1)
Miao Lin
- May 9th, 2011
SELECT * from employee where SUBSTR(EmployeeName, 1,1) = SUBSTR(EmployeeName, -1,1);
-
Import & Export Through OEM & SQL
Can any one guide me how can i import and export schema using SQL and OEM
luzaolai
- Apr 27th, 2009
In db2 three different format (ixf,del,asc) to export data:Export to tablename.ixf of ixf select * from tablename. When do import we can define if the original data should be kept or cleaned. Insert ...
-
There is a Eno & gender in a table. Eno has primary key and gender has a check constraints for the values 'M' and 'F'. While inserting the data into the table M was misspelled as F and F as M. What is the update statement to replace F with M and M with F?
CREATE TABLE temp(eno NUMBER CONSTRAINTS pk_eno PRIMARY KEY,gender CHAR(1) CHECK (gender IN( 'M','F')));INSERT INTO temp VALUES ('01','M');INSERT INTO temp VALUES ('02','M');INSERT INTO temp VALUES ('03','F');INSERT INTO temp VALUES ('04','M');INSERT INTO temp VALUES ('05','M');INSERT INTO temp VALUES ('06','F');INSERT INTO temp VALUES ('07','M');INSERT INTO temp VALUES ('08','F');COMMIT;UPDATE temp...
Elena
- Nov 8th, 2007
Update set gender=(case gender when 'F' then 'M' when 'M' then 'F' end)
-
What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
jpundalik
- Dec 30th, 2009
This is used enforce the constraints to delete the chaild records when corresponding parent records are deleted.This can be explained with below example- Parent table has department details creat...
Thanagdurai
- Jul 25th, 2007
Hi,Deletes all Foreign keys that reference the table to be dropped, then drops the table. RedsTahngam.
-
Correlated sub-query and nested sub-query examples
Please give some sub-query examples based on correlated sub-query and nested
Pallavi g rao
- Apr 21st, 2011
correlated:- outer query is executed first and result is sent to inner query for execution select * from emp e1 where e1.basicsal=(selcte max(basicsal) from emp e2 ...
nandlalseth
- Aug 14th, 2008
Correlated Sub Query:Select * from emp e where exists (select 1 from dept d where d.deptno?= e.deptno)Nested Sub Query:Select * from emp e where deptno in (select deptno from dept d)
-
Command to show SQL commands from buffer
Which command displays the SQL command in the SQL buffer, and then executes it?
sandeep.katta
- Feb 3rd, 2011
RUN Command...... Check by following exampleSQL> select sysdate from dual;SYSDATE---------03-FEB-11SQL> run 1* select sysdate from dualSYSDATE---------03-FEB-11
hemant.agarwal03
- Mar 17th, 2008
LIST and L is correct but you can also type "ED" also on sqlplus window and enter, it will open a notepad window for you to edit (if you required) otherwise you can press ALT + F4 and ENTER key.
-
Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language (DDL)
-
Pairwise and Non-Pairwise Comparisons
Explain the difference between pairwise and non-pairwise comparisons in multiple column sub-queries.
pravintule
- May 28th, 2010
In pairwise comparison the attributes included in where clause predicate are compered in same predicate and only single predicte is evaluated.where as in non-pairwise comparison the attributes are com...
yogesh.raut
- Mar 13th, 2010
Pairwise means we can put two checks at a time using subquery using ' in ' condition.. Non pairwise mean seprately we have to specifiy subquery for both conditons using equality operator.
-
How to retrieve record number 70 from a table of 100 records?
krishna
- Sep 26th, 2006
select * from emp where rownum<71minusselect * from emp where rownum<70;
-
Recent row entered
I have a very unusual question, how can one check which is the recent row added to a table?
Read Best Answer
Editorial / Best Answer
biswaranjanbehera- Member Since Jul-2008 | Aug 6th, 2008
We can select the recent row enrtered by using the query below.
suppose the table name is employee;
then.
select * from employee where rowid=(select max(rowid) from employee).
This works fine because rowid is assigned for each and every record and in accending order. So the recent record's rowid is the man (rowid). Please send feedback with any concern.bulat
- May 31st, 2010
"Although a rowid uniquely identifies a row in a table, it might change its value if the underlying table is an index organized table or a partitioned table." Also rowid stores information a...
dachi316
- Aug 26th, 2009
SCOPE_IDENTITY() would be a better option to get the most recently added row as the primary key for a table may not necessarily be an auto-incrementing variable. Therefore max(rowid) would not be the optimal solution in that case.
-
Delete distinct name from table
How could i delete the distinct name from the table. the rest record would be available as it is in the table?
pravesh28
- Nov 28th, 2010
delete from table_name where column_name in (select distinct(column_name) from table_name);
preethi
- Jan 9th, 2007
To remove distinct rows from a table use thisdelete from table_name where column_name in (select distinct(column_name) from table_name);If u want unique values select distinct (column_name) from table_name ;
-
Which date function is used to find the difference between two dates?
MONTHS_BETWEEN
Mahesh Deore
- Mar 16th, 2011
This is the inbuilt funcion of Sql server that gives you the difference between two diffrent dates as days "dd", month "mm" and year "yyyy" also at the place of formate s...
HussainExpect
- Nov 20th, 2008
In SQL 7.0, 2000Select days=datediff(dd,'1-1-2008','1-31-2008')Result 30 days
-
To access Excel data into sql
Dear All,Suppose i have Excel table and within that i have some records. Please tell me the SQL query so that i can create table and populate the Excel sheet data
SQL Interview Questions
Read Best Answer
Ans