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 1^st 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 1^st Friday of current month?

Ans – select next_day(add_month(last_day(sysdate) + 1,-1),’FRIDAY’) from dual;