1. Can you use Spool Command inside a PL-SQL block?
Ans: No, it’s SQL*PLUS command.
2.what is difference between & and && ?
Ans: & — Lexical substitution variables
& –keeps on asking input again and again for SQL*PLUS variable.
&& –It preserves the input values.It prompts for input only for the first time.value is preserved until the variable is undefined.
3.How do you produce readable outputs for a query in SQL*PLUS ?
4.which of the following is better to find number of rows in table ?
Ans: both 1 and 3
5. select Count(*) from emp:
Select count(Comm) from emp:
Ans: Count(*) …..> Count all rows.
Count(Comm) ——->Count all not null
6.I have table COUNTRY with the following columns
a) Can i create a function with name “COUNTRY_NAME” same as a column “COUNTRY_NAME” in table COUNTRY ?
Ans: Yes ,column_name and function_name can be same Column name over rides function name.
So to call function you need to qualify the function with schema name
7. Can we have table name with same name as schema name?
8.what will be the result of
SELECT 5||10 from dual; ------->510
SELECT '5'+'10' from dual; ------->15
9.How do you filter group data in a query?
using Having Clause
10.Types of Queries?
- Single row sub Queries
- Multi row Sub Queries
- Inline views
- Multi Column Sub Queries
Queries used in from clauseThey are only type of views that can use order by
SELECT ename,job from(select ename,job from emp order by sal) where rownum<=3;
12.Can a data base function return boolean value?
13.How many types of constraints are available?
- Not null
- Foreign Key
14.what is difference between Unique and Primary key Constraint?
- Unique (Foreign,Check) allows null values but primary constraint doesn’t allow null values.
- A table can have more than one unique key constraint but have only one primary.
15.what is INSTEAD of triggers?
INSTEAD Of triggers provides a transparent way of modifying views that can’t be modified directly through SQL DML statements(INSERT,UPDATE,DELETE) .These triggers are called INSTEAD of triggers because ,unlike other types of triggers,Oracle fires the trigger instead of executing the triggering statement.The trigger performs update,insert or delete operation directly on the underlying tables.
16.Can we use sequence in sub queries?
17.Gaps in sequences?
2.system crash .If the sequence catches value in the memory,those are lost due to crash.
18.when to create index?
- when you have large table
- when the table used for queries
- when you query for few or distinct values
- when table has many null values
19.When not to Create Index?
when you have small table.
when the table is used for DMLs frequently.
20.what is bit-map index?when can we use that
use it when table has less distinct values.
21.Difference between SQL Truncate and SQL drop?
Drop table: deletes the table from database
Truncate table: empty the table,but leaves the structure
22.what is primary key?
The primary key is the column(s) used to identify unique row of each table.
23.Finding the Nth highest salary?
SELECT DISTINCT(a.sal) from EMP a where &N=(SELECT COUNT(DISTINCT(b.SAL)) from emp b where a.sal<>=b.sal)
We can find the rank by using below method also
FROM (SELECT ename, sal, DENSE_RANK () OVER (ORDER BY sal DESC) ranking
WHERE ranking = N
24.Can primary key is foreign key on same table?
25.Is it possible to add more than one primary key for the table?
No,It is not possible ,Only one primary key is possible for a table.it’s possible to create a primary key on more than one column in a table,up to 32 columns.it’s called composite key.
26.How to get first and last record from a table in Oracle?
SELECT * from table_name where rownum=1
SELECT * from table_name where rowid=(select max(rowid) from table_name);
27.How to achieve this problem I am having table with two columns like empno,gender.in gender column,I am having records male,female
SELECT sum(decode(gender,’male’,1)) as male,
Sum(decode(gender,’female’,1)) as female from emp;
28.what is null?
NULL value is neither zero nor it’s a blank space.It’s some unknown value which occupies 4bytes of space of memory in SQL.
29.difference between group functions and single row functions?
|Group function||Single row function|
|A group function operates on many rows returns single result||A single row functions returns one row|
|Not allowed in pl/sql procedure statements||Allowed in pl/sql procedure statements|
30.difference between decode and translate?
|Decode is value by value character replacement||Translate is character by replacement|
|Ex:select decode(‘ABC’,’A’,1,’B’,2,’ABC’,3) from dual; o/p:3||Ex:select Translate(‘ABCGH’,’ABCDEFGHIJ’,1234567899) from dual; o/p:12378|