-
-
SET Operator
What is a SET operator? What are the types what is the difference between SET operators and JOINS?
Aparna
- Apr 5th, 2012
Union --> All rows/cols from 1st select and 2nd select with no duplicates Union All -->All rows/cols from 1st Select and 2nd Select with duplicates Intersect --> Rows common in both select statements...
dajjaal
- Sep 20th, 2010
The UNION operator is used to combine the result-set of two or more SELECT statements. SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.
-
Difference between nested query,sub query and nested query?
What is difference between nested query,sub query and nested query? explain clearly with an example?
Read Best Answer
Editorial / Best Answer
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
- 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.
-
Get 10 and 11th records in the output using SQL in 5 ways ?
I have to tables A and B. My records in this table are in such a way that
Table A Table B
10 15
11 16
12 10
13 11
Question :- I want only the 10 and 11 records as my output.
Can anyone help me out ( 5 ways... -
Which is more faster - IN or EXISTS?
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.
rohitosu
- Mar 9th, 2012
As a rule of thumb IN should be used when the sub query will return small result set. It should be kept in mind that the sub query should not return null values. EXISTS should be used when the result set returns large result set. Other factors that might affect query performance is the indexes
Vineet Arya
- Sep 17th, 2011
Which is more faster - IN or EXISTS? It totally depends upon the query. For some queries Exist is efficient and for some IN is efficient Exits is correlated sub query, in which the outer query run f...
-
LOCK Query
What is use of lock query in SQL Plus? Give its syntax and implementation?
sampra
- Mar 6th, 2012
Exclusive Lock: This is placed when Insert, Update or Delete command is performed. There can be only one exclusive lock on a record at a time.
hussasha
- Mar 17th, 2009
Locking protect table when several users are accessing the same table. Locking is a concurrency control technique in Oracle. It helps in data integrity while allowing maximum concurrency access to...
-
Aggregation Key
What is the Aggregation Key?
AnithaLetchumi
- Aug 10th, 2011
Aggregation Key is nothing but composite or concatenated key. It's the primary key having more than one column. For example different books may have identical titles, authors. In this case we can take...
-
SQL NULLIF Function
What is the use of NULLIF function in SQL?
sampra
- Mar 6th, 2012
In SQL, NullIf function is used to compare two expressions. There are 3 possible results for NULLIF. 1. If the values are same, NullIf returns NULL. Eg: NULLIF(Prem,Prem) returns NULL 2. If they are...
Prem_p
- Apr 28th, 2011
In SQL, NullIf function is used to compare two expressions. There are 3 possible results for NULLIF.1. If the values are same, NullIf returns NULL. Eg: NULLIF('Prem','Prem') returns NU...
-
What are the advantages of views?
sampra
- Mar 6th, 2012
1) To protect some of the columns of a table from other users.ie for security purpose.
2)To hide the complexity of a query.
3)To hide the complexity of calculations.vivekrajece
- May 19th, 2011
1) To protect some of the columns of a table from other users.ie for security purpose.2)To hide the complexity of a query.3)To hide the complexity of calculations.
-
Is there any other option other than desc query to describe the table
sampra
- Mar 6th, 2012
SELECT
column_name "Name",
nullable "Null?",
concat(concat(concat(data_type,(),data_length),)) "Type"
FROM user_tab_columns
WHERE table_name=TABLE_NAME_TO_DESCRIBE;sunildas4U
- Aug 24th, 2006
An alternative to the DESC command is selecting directly from the data dictionary - DESC MY_TABLEis equivalent to SELECT column_name "Name", nullable "Null?",concat(concat(concat(data_type,'('),data_length),')') "Type"FROM user_tab_columnsWHERE table_name='TABLE_NAME_TO_DESCRIBE';
-
How can we increase performance in a View?
-
How to truncate the table with cascade option?
I tried "truncate table cascadebut I got the error storage keyword is missing (says either drop storage or reuse storage) Can any one help me out.
vemurisasidhar
- Sep 23rd, 2007
If u want to delete a table with constraints i think u need to first disable the constraints on the table and the use truncate table ;alter table disable constraint primary key cascade;
-
What is the basic difference in function and store procedure.where we use function and where we need to use Store Procedure.could you please explain me in brief.
sampra
- Mar 6th, 2012
Procedure wont retrun any value but it can return many values on condition. but function always returns a values
shravanam
- Oct 28th, 2007
A procedure is used to perform a specific task. It can also return a value but its not mandatory.But a function is used to perform some calculations and it is mandatory to return atleast one value.
-
Unique Index and Simple Index
What is difference between unique index and simple index?is it possible, two rows can have the same unique index?
sampra
- Mar 6th, 2012
Index create on Primary key or unique key is Unique index where as index created on any other element is simple index
srinivasa.dinesh
- Jan 29th, 2008
Unique Index:If the index is for a primary or unique key then the owner, name, type, and columns cannot be modified. These properties are all derived from the key constraint. Changing the name of the ...
-
Describe table without DESC Command
How to desc table data without using desc command
saradajasti
- Mar 24th, 2008
Select column_name name,data_type typefrom user_tab_columnswhere table_name =
-
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)
-
Delete contents in two tables at a time
How to delete contents in two tables at a time? When you delete contents in one table then other table contents should also be deleted.
krishnaindia2007
- Jun 20th, 2008
You may also use triggers for this purpose. But specifying on delete cascade while defining foreign key constraint is better option.
-
Copy structure of a table
How can I copy the stucture of a table to a text file.
krishnaindia2007
- Jun 21st, 2008
You can copy the definition of objects to text file using dmbs_metadata package.This is the query to copy emp table definition to text file.spool textfilenameselect dbms_metadata.get_ddl('TABLE','EMP','CMSJAN') from dual;spool off;
-
Create table based on another table
-
Securing SQL Scripts
How to secure SQL scripts so that they cannot be seen by others but can only be executed.
Oracle SQL Interview Questions
Read Best Answer
Ans