Interview Questions:
- (SQL)
SQL : -
Q) To print nth highest sal?
Ans - Select * from (select a.*,dense_rank() over (order by
sal desc) r from emp a) where r=n;
Here n means the number which you want to select highest
salary
Ex: r=2 for second highest salary
Q) To print nth
lowest sal?
Ans - Select * from (select a *,dense_rank() over(order by sal
asc) r from emp a) where r=n;
Here 'n' means the number which you want to select lowest
salary
Ex: r=5 for fifth lowest salary
Q) To print top 3 highest
sal?
Ans - Select * from (select a.*,dense_rank() over (order by sal
desc) r from emp a) where r <= 3;
Q) To print top 4 lowest
sal?
Ans - Select *
from (select a.*,dense_rank() over(order by sal asc) r from emp a) where r <= 4;
Q) To print only 5th highest sal?
Ans - Select * from (select a.*,dense_rank() over(order by sal desc) r from emp a) where r=5;
Q) To print first and
last record in a table?
Ans –
-- the below query is for only first record
Select * from emp where rowid=(select min(rowid) from emp);
--the below query is for only last record
Select * from emp where rowid=(select max(rowid) from emp);
Q) To print even
records?
Ans – Select * from (select rownum r,emp.* from emp) where mod(r,2)=0;
Q) To print odd
records?
Ans - Select * from (select rownum r,emp.* from emp) where
mod(r,2)=1;
Q) To find all tables with their number of rows in a schema?
Ans - Select table_name,num_rows from user_tables;
Q) To find number of
columns for specific table in a schema?
Ans - Select count(*) from user_tab_columns where
table_name='emp';
Q) To insert same data from one table to another table?
(Provided that columns name and number will be same in both
tables)
Ans - Insert into emp select * from account_owner;
Q) To print duplicate records?
Ans – select * from
emp where rowid not in (select max(rowid) from emp group by empno);
Q) To delete duplicate records?
Ans - delete from emp where rowid not in (select max(rowid)
from emp group by empno);
Q) How to get 10th record from a table?
Ans – Select * from emp where rownum<11 minus select *
from emp where rownum<10;
Q) How to get 18th record from a table?
Ans – Select * from emp where rownum<19 minus select * from
emp where rownum<18;
Q) To print 1 to 100 numbers sequentially?
Ans - Select level from dual connect by level <=100;
Q) Write a query to print the name which is more than one
time and number of rows present in the table for that name?
Ans –
select ename, count(*) from emp
group by ename
having count(*) > 1;
Q) Write a query to print the salary amount which is more
than three times and number of times present in the table?
Ans –
select sal, count(*)
from emp
group by sal
having count(*) > 3;
Q) What is dual table?
Ans –
dual is a dummy table.
Owner of the table is system.
It has one row and one column.
Q) Write a query to print your name one time?
Ans – select “Baishnab Charan Patra” from dual;
Q) Write a query to find sum of 145 and 55?
Ans – select 145 + 55
from dual;
Q) Difference between delete, truncate, and drop?
Ans –
Delete: -
It is DML statement.
It is not auto commit.
It deletes one record or entire record or multiple records
from the table.
It is less faster
than truncate.
DBA can rollback the data after delete statement.
Truncate : -
It is DDL statement.
It is auto commit.
It deletes entire record from table.
It is faster than delete.
We cannot rollback data after truncate.
Drop : -
It is DDL command.
It delete both record and structure of the table from the
database.
1.
To rollback the table, we can use,
Flashback table table_name before drop;
2.
To delete complete the table from database, we
can use,
Purge table table_name;
Q) Why delete is less faster than truncate?
Ans – delete scans one record at one time from table and
delete it, where as truncate doesn’t scan every record before removing it.
Truncate takes less transaction space than delete.
Q) What is the difference between Union and Unionall?
Ans –
Unionall selects all values from both tables without
removing duplicates, whereas union selects all unique values from both tables.
TableA: -
Name- (Coulmn)
Hiranya
Baishnab
Ram
TableB: -
Name – (Column)
Hiranya
Baishnab
John
Results: -
Unionall –
Name –
Hiranya
Baishnab
Ram
Hiranya
Baishnab
John
Union –
Name –
Hiranya
Baishnab
Ram
John
Q) What is index and
types of index?
Ans –
Index is a schema object which is used to improve
performance of data retrieval.
Index will improve performance of select, update and delete
queries.
Indexes are created on columns.
Syntax –
Create index index_name on table_name (column_name);
Types –
1.
Simple Index – index created on one column
Syntax –
Create index index_name on table_name (column_name);
2.
Composite Index – index created on more than one
columns
Syntax –
Create index index_name on table_name (column_name1,
column_name2);
3.
Unique Index – unique index doesn’t allow
duplicate values on the column on which its created. Index won’t create on
primary key column as system creates a unique index automatically on this
column.
Syntax –
Create unique index index_name on
table_name (column_name);
4.
Bitmap index – indexes created on low
cardinality columns.
Syntax –
Create bitmap index index_name on
table_name (column_name);
5.
Function based index – indexes created on
function.
Syntax –
Create index index_name on table_name (function_name(column_name));
Q) What is view and types of view?
Ans –
View is a virtual table and its doesn’t occupy space in
database.
The query associated in the view will be stored in the
database, not the results so its called stored query.
Its help us in security and simplicity.
Syntax –
Create view view_name
as
select column_names from table_names
where condition
with check option
with ready only;
Example –
Create view v1 as select empno,ename,job,sal from emp where
job = ‘CLERK’ with check option;
insert into v1 values (234,’AMP’,’MANAGER’4000);
ERROR
As its allow only JOB = ‘CLERK’, but we are inserting job =
‘MANAGER’ so violates.
If a view is created with ready only option, then no DML
operations would allow.
Q) Difference between simple and complex view?
Ans –
Simple View –
View should create on one table.
It is updatable.
DML operation is allowed.
Example –
Create view v4 as select ename,empno,sal,job from emp where
job = ‘CLERK’;
Complex View –
View should create on more than one table.
It is not updatable.
DML operation is not allowed.
Example –
Create view v2 as select empno,ename,dname from emp, dept
where emp.deptno=dept.deptno;
Q) What is materialized view?
Ans –
Materialized view is physical table which stores query as
well as results in database.
Example –
Create materialized view mv1 as select deptno,sum(sal) from
emp gropu by deptno;
Refresh –
Execute dbms_mviews.refresh (mv1);
Q) Difference between view and materialized view?
Ans –
|
BASIS FOR COMPARISON
|
VIEW
|
MATERIALIZED VIEW
|
|
Basic
|
A View is never stored it is only displayed.
|
A Materialized View is stored on the disk.
|
|
Define
|
View is the virtual table formed from one or more base
tables or views.
|
Materialized view is a physical copy of the base table.
|
|
Update
|
View is updated each time the virtual table (View) is
used.
|
Materialized View has to be updated manually or using
triggers.
|
|
Speed
|
Slow processing.
|
Fast processing.
|
|
Memory usage
|
View do not require memory space.
|
Materialized View utilizes memory space.
|
Q) Difference between rank and dense_rank?
Ans –
RANK will skip the next available ranking value whereas
DENSE_RANK would still use the next ranking value.
RANK –
SAL: - RANK : -
1000 1
2000 2
3000 3
3000 3
4000 5
5000 6
5000 6
5000 6
6000 9
DENSE RANK –
SAL: - DENSE RANK : -
1000 1
2000 2
3000 3
3000 3
4000 4
5000 5
5000 5
5000 5
6000 6
Q) Difference between rowid and rownum?
Ans – rowid is static value for each row, where as row num
is dynamic value from each row.
Rowid helps us to get a particular row from the table, where
row num doesn’t help us to get particular row from table.
Both are pseudo columns.
Q) How to find 1st day of current month?
Ans – select add_month(last_day(sysdate) + 1,-1) from dual;
Q) Write a query to print those names which contains 3 M in
his/her name?
Ans – select ename from emp where length(ename) –
length(replace(ename,M,’’)) = 3;
Q) Write a query to print those names who are getting
highest salary in their respective departments?
Ans –
Select ename from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);
Q) Write a query to print those names which name start with
S? (two ways)
Ans –
Select * from emp
where substr(ename,1,1) = ‘S’;
or
Select * from emp where ename like ‘S%’;
Q) What are types of constraints?
Ans –
1.
Unique
2.
not null
3.
primary key
4.
check
5.
foreign key
6.
default
Q) Difference between primary key and foreign key?
Ans –
Primary Key –
It allows unique values without duplicate and null.
Foreign key –
It points to primary key of different table or same table.
It holds all values along with null and the values should be
part of primary key values.
A table has one primary key and multiple foreign keys.
Q) Difference between primary key, unique and not null?
Ans –
Unique –
Unique constraint holds unique values with null.
Not null –
Not null constraint holds all values without null.
Q) Difference between left outer join and right outer join?
Ans –
Left outer join –
It compares both tables and returns matching records from
both tables and non matching records from left table based on common column of
both tables.
Right outer join -
It compares both tables and returns matching records from
both tables and non matching records from right table based on common column of
both tables.
Q) What is equi/inner join and outer join?
Ans –
Equi Join –
It compares both tables and returns matching records from
both table based on common column of both tables.
Outer Join –
It compares both tables and returns both matching and non
matching records from both table based on common column of both tables.
Q) Write a query to print your name 9 times?
Ans –
Select repeat ("Baishnab Charan Patra", 9);
Example –
Select repeat (ename,3) from emp;
Q) Display 1st Friday of current month?
Ans – select next_day(add_month(last_day(sysdate) +
1,-1),’FRIDAY’) from dual;
