Difference between DCL and DML commands?
DCL commands will commit implicitly where in DML commands we have to
commit explicitly.
2.
Table, Rowid, Rownum
Table is a database object, which is
having more than one column associated with its data type.
Rowid is the unique binary address of
the row in a table
Rownum it is a temporary number in
a memory and was assigned to each row
selected by the statement.
3. Pseudo-Columns
Columns that
are not created explicitly by the user and can be used explicitly
in queries. The pseudo-Columns are rowid, rownum,
currval, nextval, sysdate, and level
4. What is a View?
View is Virtual
Table, which hold the data at runtime
5. Difference between ordinary view and
complex view?
Simple views can
be modified easily and these cant
hold the data where as complex views cant
be modified directly and complex view
can hold the data to modify a complex
view we have to use INSTEAD OF TRIGGERS.
6. Forced view
Creating a view on
a table, which is not there in the database.
7. Inline view
Inline view is
basically a subquery with an alias that you can use like
a view inside a SQL statement.
8. What is an INDEX and what are the
types?
INDEX is a database
object used in oracle to provide quick access to rows.
9. Synonym
Synonym is
an alias name for any database object like
tables, views, functions, procedures.
10. SELECT statement syntax?
SELECT From
Where
Group by
Having
Connect prior
11. What is Constraint? Different
Constraints?
Constraints are representators of the columns to enforce data entity and
consistency. UNIQUE, NOT NULL, Primary key, foreign key, Check.
12. Difference between Primary key and
Unique + Not Null constraints?
Unique + Not Null
is a combination of two constraints and we can use more than
one Unique + Not Null in any table. Primary Key is a single constraint we can
use only one time for a table. It can be a referential key for any column in any
table.
13. What is NULL?
Default Value.
14. Dual Table
It is a one row,
one column table with value X.
15. Difference between Truncate and
Delete?
Truncate will delete all
the rows from the table with out
any condition. It will commit automatically when
it fires Where delete will delete all
or specified rows based upon the condition here we have
to commit explicitly.
16.Difference between Char and
Varchar2?
Varchar2 is similar to char
but can store available number of characters and while querying the
table varchar2 will trims the extra spaces and
fetches the rows that exactly match the criteria.
17.Difference between LOB and
LONG data types?
The maximum size of an LOB
is 4GB. It will support random access to data where in LONG maximum size is
2GB. It will support sequential access to data.
18.Single Row functions: It will work on single row and give
result for all the rows.
Ex: to_char, to_date etc.
19.Group Functions: It will work on group of rows
in a table and gives a single row result. Ex: Sum(),
Avg(), min(), max().. Etc.
20.String Handling Functions?
Instr – it returns
the position of the string where it occur according to the
parameters.
Instrb – instr and instrb returns same
but in the form of bytes.
Substr – It returns the portion of a
string depending on the parameters from and to.
Substrb – Substr and Substrb returns
the same thing but Substrb returns in the form of bytes
21.Sign: Sign is a function it will take
numbers, as inputs and it will give
i. 1
for positive integer
ii. -1
for negative integer
iii. 0 for
ZERO
SQL> Select sign(-1234) from
dual; O/P:
-1
22.Differences between UNION and UNION
ALL?
Union: The values of the
first query are returned with the values of the second
query eliminating the duplicates.
Union All: The values of the first
query are returned with the values of the second query including the
duplicates.
23.Difference between NVL and NVL2
functions?
NVL is used to fill a
NULL value to known value. NVL2 will identify the NULL
values and Filled values it returns exp3 if
it is null otherwise it returns exp2. We have
to pass 3 parameters for NVL2 and 2 parameters for NVL.
24.How can we compare range of values
with out using the CASE?
By using Decode with in Decode.
25.Can we Decode with in a Decode?
YES
26.Decode and Case Difference?
Case compares a Range of values
and Decode will work as if else statement.
27.Difference between Replace and
Translate?
Replace is used to replace the
whole string and we can pass null values in
replace. Translate is used to translate character-by-character
here we have to pass the three parameters.
28.Difference between where and having
clause?
Where used to specify condition and
used to restrict the data. Having used to
specify the condition on grouped results and used to filter the data.
29.Difference between IN and EXISTS
clause?
EXISTS gives the status of the inner
query. If the inner query is
success then it returns true other wise it returns false
and IN will compare the list of values.
30.Difference between subquery and
correlated subquery?
Query with in a query is
subquery. Inner query will executes first and
based on the result the outer query will be
displayed. Correlated subquery outer query
will executes first and then inner query will be executed.
