-
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
-
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);
-
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 do you find the number of rows in a table?
-
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.
-
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.
-
Inserting the image
How can I insert an image into oracle database?
-
What is Dual table?
What is dual table which is used as a table in sql...
Read Best Answer
Editorial / Best Answer
svsraju81- Member Since Jul-2008 | Jul 24th, 2008
Dual table is a temporary table, it contains a single row and single column. If we return a data from dual table it gives a single row.
sudipta kumar dhal
- Sep 5th, 2011
Dual table is called a dummy table because it is not meant for storing any useful data.
d_pranjal
- Sep 5th, 2010
Dual table is owned by SYS. Data dictionary come under SYS, dual is part of data dictionary. Dual table consist only one row and only one column containing value x
-
What operator performs pattern matching?
saurabh
- Sep 5th, 2011
Underscore operator (_) for single character matching n wildcard (%) for any length string matching...
example a column having abhi,abhaaa, bhaaa,bmddd,cmmm;and i want all those name that start with a then use like 'a%'; -
DROP command and VIEW
How does dropping a table affects its view?
sivaraj6389
- Sep 7th, 2011
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Table Name'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'view Name' because of binding errors. -
What is the main use of FOREIGN KEY?
Read Best Answer
Editorial / Best Answer
Answered by: MANAS KUMAR ROUT ( USA ) (TCS) (Eli LILLY ) - PFIZ
- Jul 6th, 2007
Hey Ramesh,
I understand your interviewer's enthusiasm to know about the Foreign Key.? See foreign key definitions says its states the relationship of one attribute(Column) of one table to the corresponding column on the other table.
So one os a parent and other is child. PARENT is that table which holds the primary key and CHILD is table which holds the referential key. Hence every entry in the PRIMARY table is unique where as same entry might be repeating in the child table. BUT that same has to be present in the PARENT table (Primary KEY).
Now Going by definition Foreign key can be of two types :
========================================
1.? ON DELETE SET NULL
2.? ON DELETE SET CASCADE
DETAILS:
=======
On delete set null :
=======
When a foreign key is created by on delete set null definition then when you delete one row from the primary?column (of parent table ) , then the corresponding entry in the foreign key table (Child table ) have the value "NULL" for that particular column.
ON Delete Set Cascade:
================
When the foreign key is created by this definition then when you delete the primary column (any one row -unique data) , then the Child table forcefully deletes all the rows in the child table having same value for that particular column.
I hope u got the crux of the Foreign key
Thanks
Manas Kumar Rout
SAS Consultant
Oracle Certified Associate
Unix Consultant
shweta dhumal
- Sep 8th, 2011
foreign key is primary key of other table. for example: 'STUDENT' is one table which stores information about students and 'SID' is primary key in that table and 'MARKS' is another table which stor...
shweta dhumal
- Sep 8th, 2011
The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted. For example, say we have two tab...
-
What is the use of Parametric Cursor ?
dinesh.smhdr
- Sep 13th, 2011
"oracle8 declare cursor c_emp (cin_No NUMBER) is select count(*) from employee where id = cin_No; v_deptNo employee.id%type:=10; v_countEmp NUMBER; begi...
-
What is difference between Oracle and MS Access? What are disadvantages in Oracle and MS Access? What are features&advantages in Oracle and MS Access?
Jee Va
- Sep 17th, 2011
Hi! everyone, I've to develop a small retail store management application for our Store. I've selected the front end as JAVA and back end is ??? (Still confusing) to use 1.Using File Handling(but i do...
ywaikar
- Jul 17th, 2009
Both Oracle and MS Access are RDBMS, Oracle followed Client-Server architecture while MS Access is does not.We can have Login Security, command level security in Oracle.Can define relationship at time of table creation.
-
-
LOB Segment
What do you mean by LOB segment? Explain LOB segment
smonroy
- Sep 27th, 2011
You are talking about oracle, LOB is simply a pointer. It points to an index.
-
Remove redundant rows
How to remove redundant rows without any distinct columns in the table itself ?
smonroy
- Sep 27th, 2011
Hi my Friend this is the best way to you issue DELETE FROM table_name where camp1 in (SELECT camp1 FROM table_name GROUP BY camp1 HAVING Count(camp1)>1) Example: DELETE FROM ubication where serial...
Allan Hunter
- Sep 22nd, 2011
You're question is vague, but from what I understand it to mean is to remove duplicate rows wihle keeping the columns in tact. The easiest way I know is to make your query based on what makes the dat...
-
INSERT Statement Optimization
A script is inserting data into one table, it was taking 15 min to complete before. Today it is taking more than 2 hours. What kind of action you will take?
smonroy
- Sep 28th, 2011
Also you can do the below steps
1.- Normalization your DB
2.- Break the INSERT in some INSERTs instead have one
3.- You need to create indexes on the tablearindam_0006in
- Aug 11th, 2010
Do not paste in sql client and run the script. Use sql administration console. First analyze it and browse the sql dump file and execute it. Think there will be no issue.
-
HAVING Clause
Given a SELECT statement that has a GROUP BY clause.The HAVING clause uses the same syntax as which other clause?A. WHEREB. UNIONC. SUBQUERYD. ORDER BY
Ksudhir_Kumar
- Sep 5th, 2010
Having clause is used filter data which is retrieve by group by clause.for example: if you try to use having to filter data instead of where clause then it fires error.the style of query is:- 1...
-
Employee Query question
Suppose im having employee table with fields, eno, ename, dept, address1, address2, address3. In address field employee can fill only address1 or address2 or address3... at a time he can fill three address fields. now i want all employee names who filled only one address field..
Inner join and Outer join
What is the difference between inner join and outer join?
Mark Haynes
- Sep 30th, 2011
First to define what Inner Join and Outer Join mean. Inner Join: A join between two tables were only those records in both tables that meet the conditions of the join expression are returned. In most...
Read Best Answer
Ans