-
What are the types of SQL Statement ?
Data Definition Language : CREATE,ALTER,DROP,TRUNCATE,REVOKE,NO AUDIT & COMMIT.Data Manipulation Language: INSERT,UPDATE,DELETE,LOCK TABLE,EXPLAIN PLAN & SELECT.Transactional Control: COMMIT & ROLLBACKSession Control: ALTERSESSION & SET ROLESystem Control : ALTER SYSTEM.
gdurga
- Jun 15th, 2009
DDL: Data Defintion LanguageDML: Data Manipulation LanguageTCL: Transaction Control LanguageDCL: Data Control Language
-
What is correlated sub-query
Read Best Answer
Editorial / Best Answer
rampratap409- Member Since Sep-2006 | Dec 7th, 2006
when a sub query executes for each row its called a corelated sub query, in other words when a sub query takes the input from outer query for executation then execute and passes the result to outer query then outer query executes.
sravan1
- Dec 13th, 2006
In correlated subqueries, the information from the outer select statement participates as a condition in the inner select statement.Eg : Select deptno, ename, sal from emp a where sal> (select avg(sal) from emp b where a.deptno =b.deptno)order by deptno;
rampratap409
- Dec 7th, 2006
when a sub query executes for each row its called a corelated sub query, in other words when a sub query takes the input from outer query for executation then execute and passes the result to outer query then outer query executes.
-
Explain UNION, MINUS, UNION ALL and INTERSECT
Answer posted by Scott on 2005-05-25 18:32:51: UNION - the values of the first query are returned with the values of the second query eliminating duplicates. MINUS - the values of the first query are returned with duplicates values of the second query removed from the first query. UNION ALL - the values of both queries are returned including all duplicates INTERSECT - only the duplicate...
Read Best Answer
Editorial / Best Answer
rampratap409- Member Since Sep-2006 | Dec 7th, 2006
UNION: Take the common record once( No Duplicate)
UNION ALL : Takes Duplicates also
Minus: Result of first query - result of second query
examle
select * from emp where rownum<=9;
minus
select * from emp where rownum<9;
this will display 9th row of emp
Intersect: common result of both query
rampratap409
- Dec 7th, 2006
UNION: Take the common record once( No Duplicate)UNION ALL : Takes Duplicates alsoMinus: Result of first query - result of second queryexamleselect * from emp where rownum<=9;minusselect * fr...
Atal
- Sep 16th, 2005
UNION: Take the common record once( No Duplicate)UNION ALL : Takes Duplicates alsoExampl : Say there is EMP having 14 reconds then...select count(1) from (select ename from emp) returns 14select...
-
Is it possible to use LONG columns in WHERE clause or ORDER BY
Murugavel
- Feb 6th, 2007
Only one long col allowed in a table.
rampratap409
- Dec 8th, 2006
NO, Long column can not used in where, order by clause A table can have only one long column
-
What are the pre-requisites to modify datatype of a column and to add a column with NOT NULL constraint
Mukul
- Jun 19th, 2007
We can update the datatype of?column?with ALTER function?but the constraints cant be added with the ALTER function if there is data in the table.
rampratap409
- Dec 7th, 2006
to modify the datatype of a column that should be empty, but if increasing the width then it can be done even it holds the value.to add then not null column the table must be empty if not then add the column as nullable then modify that that column as not null.
-
If a view on a single base table is manipulated will the changes be reflected on the base table
sanch167
- Dec 29th, 2010
A view on a single table can be modified, however the same cannot be done on multiple tables.
rampratap409
- Dec 7th, 2006
if a view based on one table or multiple table matter is it should contain all not null columns and additionaly no calculated column then ant DML statement will cause changes in base table.
-
-
Implicit Cursor attributes
Haraprasad
- Nov 3rd, 2008
SQL%ISOPEN exists. Oracle always evaluates this to FALSE as PL/SQL closes implicit cursors immediately after they are executed.
rampratap409
- Dec 7th, 2006
sql%isopen is there but it will always return "false" for implicit cursor
-
What are the different tablespaces in database
Neelkanth
- Sep 21st, 2006
There are only three tablespaces as follows:-1.Permanent tablespace2.Undo tablespace3.Temperory tablespace
na123
- Mar 24th, 2006
A tablespace is a collection of one or more datafiles.all database objects are stored in tablespaces.diff types of tablespaces:-1)system tablespace.2)temp tablespace.3)tools tablespace4)users tablespace.5)rollback tablespace.6)data and index tablespace.
-
-
What is correlated sub-query ?
Correlated sub query is a sub query which has reference to the main query.
globalanil18
- Mar 31st, 2007
Hi Guys,The Oracle Server performs a correlated subquery when the subquery references a column from a table reffered to in the parent statement. A correlated subquery is evaluated once for each row pr...
rampratap409
- Dec 7th, 2006
when a sub query executes for each row its called a corelated sub query, in other words when a sub query takes the input from outer query for executation then execute and passes the result to outer query then outer query executes.
-
What is ON DELETE CASCADE ?
When ON DELETE CASCADE is specified ORACLE maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.
bhawesh
- Jul 17th, 2007
Does not consider parent-child relationship.On deletion of parent record child records are deleated automatically but vise versa is'nt true
rampratap409
- Dec 8th, 2006
for deleteing master record with out deleteing the child records on delete cascade is used. in this case when ever master record is deleted all the child record will be deleted automatically.
-
-
How to use comand "COMMIT"and "ROLLBACK" in a transacsion?
joshmohit
- Oct 26th, 2010
Commit;commit work;commit work 'comment';rollback;rollback work;rollback to savepoint ;
narendra kumar
- Dec 6th, 2006
COMMIT:it is used to make the manipulations ENABLE permanently on the table where as ROLLBACK is used to DISABLE the effect of the queries(which were typed a bit above it) on ...
-
Where the integrity constraints are stored in data dictionary
Answered by Scott on 2005-05-12 10:18:34: Constraints are found under the USER_CONSTRAINTS table.
amol shinde
- Apr 15th, 2016
Constraints are stored in data dictionary
-
What are the advantages of VIEW
pratima
- Dec 26th, 2006
Hi Rampratap, U r telling abt the efficiency of view @ mutating table concept. can u pls explain it with an example?Thanks Pratima
rampratap409
- Dec 7th, 2006
The advantages of View are:security: only selected column from a table made available to view.Complaxity: write a complex query and assign this query to a view so next time no need to write that...
-
How do I display row number with records
-
To view installed Oracle version information
Answer posted by Scott on 2005-05-25 18:41:26: From the SQL> prompt, type the following: select * from v$version;
-
What are the different types of SQL
gdurga
- Jun 18th, 2009
1. DDL: Data Definition Language2. DML: Data Manipulation Language3. TCL: Transaction Control Language4. DCL: Data Control Language5. DQL: Data Query Language
-
What is difference between CHAR and VARCHAR2 ? What is the maximum SIZE allowed for each type ?
CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.
Shambhu Kumar
- May 7th, 2008
The break keyword halts the execution of the current loop and forces control out of the loop. The continue is similar to break, except that instead of halting the execution of the loop, it starts the next iteration.
Shambhu Kumar
- May 7th, 2008
The break keyword halts the execution of the current loop and forces control out of the loop. The continue is similar to break, except that instead of halting the execution of the loop, it starts the next iteration.
SQL*Plus interview Questions
Read Best Answer
Ans