Interview Questions: - (SQL)

SQL: -

Q) How you take back up/copy of a table?

Ans – create table <backup_table_name> as select * from <table_which_to_be_back_up>;

 

Q) How to copy structure of a table?

Ans – create table <copied_table_name> as select * from <table_from_which_structure_copied> where 1=2;

 

Q)  What is self join with example?

Ans – Joining of a table happens with itself is called self join.

In this case, foreign key of a table refers to primary key of same table.

Example –

Select e.ename Employee_Name, m.ename Manager_Name from emp e, emp m

Where e.mgr = m.empno;

 

Q) What is equi  join with example?

Ans – Equi join between two tables return matching records from both table based upon common columns of both table.

Example –

Select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;

 

Q)  Difference between clustered index and non-clustered index?

Ans –

Clustered Index –

·         Clustered Index is a type of index that sort the data rows in a table on their key values.

·         Cluster Index doesn’t require additional disk space.

·         Cluster Index offers faster data accessing.

·         Clustered Index creates on Primary key column.

Non – Clustered Index –

·         Non - clustered Index is a type of index that stores the data rows at one location and indices at another location.

·         Non - cluster Index requires  additional disk space.

·         Non - cluster Index offers lesser data accessing speed than clustered index.

·         Non - clustered Index creates on unique column.

 

Q) What is difference between char and  varchar data types?

Ans –

Char – (Characters)

·         It stores maximum 2000 bytes of characters data.

·         Character data type is used to store strings of fixed length.

·         Char data type uses static memory location.

·         Char data type can be used when we expect the data values in a column to be of same length.

Varchar – (Variable Characters)

·         It also stores maximum 2000 bytes of characters data.

·         Varchar data type is used to store strings of variable length.

·         Varchar  data type uses dynamic memory location.

·         Varchar data type can be used when we expect the data values in a column to be of variable length.

 

Q) What is difference between varchar and varchar2?

Ans –

Varchar –

·         It stores maximum 2000 bytes of characters data.

·         Varchar can identify NULL and empty string separately.

·         Varchar is ANSI SQL Standard.

Varchar2 –

·         It stores maximum 4000 bytes of characters data.

·         Varchar2 can identify NULL and empty string same.

·         Varchar2 is Oracle Standard.

 

Q) Write a SQL query to find sum of positive and negative values from a column in separate columns for a table?

Ans –

select accountid,

   sum(case when amount>0 then amount else 0 end) as Positive_Value,

   sum(case when amount<0 then amount else 0 end) as Negative_Value

   from account_master

   group by accountid;

Here accountid and amount are two columns of account_master table.

amount column has some positive and negative values.

 

Q) What is version of Oracle you are using?

Ans – Oracle 10g

 

Q) How to find version of Oracle database you are using?

Ans – select * from v$version;

 

Q) Could you please tell me which is better sub query or join?

Ans – Join is better than sub query as join helps query optimization and faster data retrieval time than subquery.

 

Q) What s NVL and NVL2 function and difference?

Ans – NVL converts null value to actual value where as NVL2 converts both null and not null values to actual Values.

NVL(COMM,0) -> If comm is null, then it converts that null value to 0.

NVL2(COMM,COMM+100,100) -> if comm is null, then NVL2 converts that null value to 100, and if it is not null, then comm is comm.+100.

 

Q) If a job is running long or any database query is long running, then what action you are taking?

Ans –

If a job is running long or any database query is long running, then we have to escalate the issue with database team.

DBA has to fix the issue by adding hints in view or rebuilding indexes or optimize the query.

 

Q) What is table space and how to find out?

Ans –

The table space is where tables get stored.

It links the physical storage layer (files on disks) and the logical storage layer (tables, indexes).

Query – (to get table space name)

select tablespace_name from all_tables where owner = 'SCOTT' and table_name = 'EMP';

 

Q) What is lock and deadlock?

Ans –

Lock occurs when one process accesses a piece of data where there is a chance that another concurrent process will need this piece of data as well at the same time. By locking the piece of data we ensure that we are able to action on that data the way we expect.

Deadlock occurs when one process is blocked and waiting for a second process to complete its work and release locks, while the second process at the same time is blocked and waiting for the first process to release the lock.

 

Q) What is the use of where clause and having clause?

Ans –

·         WHERE is used to filter rows before grouping, and HAVING is used to exclude records after grouping.

·         Where clause is used before group by clause and action on non aggregate columns.

·         Having clause is used after group by clause and actions on aggregate function.

 

Q) One SQL Query runs usually 5 mins, but today it ran more than 15 mins why?

Ans –

Might be we have received more records in the file from source than normal days.

If it’s again long running, then we have to escalate the issue with database team. 

 

Q) A code is developed by developer in DEV environment and its working fine in DEV environment.

If you will push the code in Production environment, then there might be failure which impact users as well as production. What action you will take?

Ans – Before moving a code into production, it should be test in similar PROD environment like UAT  environment and if successful, then move it to production and if its fail in UAT environment, then Dev team will fix it.

 

Q) What is DR activity?

Ans - Disaster recovery is an organization's method of regaining access and functionality to its IT infrastructure after events like a natural disaster, cyber attack, or even business disruptions related to the COVID-19 pandemic.

 

Q) What is incident and Problem?

Ans –

Any interruption to normal process is called incident.

If the incident happens frequently, then it called problem and then we have to involve different teams to fix the issue permanently.

 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;

 Interview Questions:  - (Unix)

Unix :  -

Q) Write a single command to print your name one time?

Ans –

$ echo “Baishnab Charan Patra”

 

Q) Write a script to print your name 7 times?

Ans –

#! /bin/sh

a=0

while [ $a -lt 7 ]

do

echo “Baishanb Charan Patra”

a=`expr $a + 1`

done

 

Q) Write a script if a file is file type or not?

Ans –

#! /bin/sh

echo “Enter  a file name : “

read fn

if  [ -f $fn ]

then

echo “$fn is file type.”

else

echo “$fn is not file type.”

Fi

 

Q) What is the difference between $* and $@?

Ans –

$* -> Print all command line arguments passsed in a script.

$@ -> Print all command line arguments within double quotes individually passes in a script.

 

Q) What is $?, $# and $0? 

Ans –

$? ->  the exit status of last command.

$# -> total number of command line arguments

$0 -> the current script file name

 

Q) Top 5 processes using high CPU utilisation?

Ans –

$ top –c –b | head -5

Where –b = batch mode

Here you will see processes with command sorted as per their CPU usage.

or

$ top –b | head -5

Here you will see processes with commands along with their paths as per CPU usage.

-top command refresh in every 5 seconds.

or

$ ps -eo pid,ppid,%mem,%cpu,cmd --sort=-%cpu | head -5

where 

ps : To find process

-e : Select all processes.

-o : To customize a output format.

–sort=-%cpu : Sort the ouput based on CPU usage.

PID : Unique ID of the process.

PPID : Unique ID of the parent process.

%MEM : The percentage of RAM used by the process.

%CPU : The percentage of CPU used by the process.

Command : Name of the process.

 

Q) Top 5 processes using high memory utilisation?

Ans –

$ ps -eo pid,ppid,%mem,%cpu,cmd --sort=-%mem | head -5

ps : To find process

-e : Select all processes.

-o : To customize a output format.

–sort=-%mem : Sort the ouput based on memory usage.

PID : Unique ID of the process.

PPID : Unique ID of the parent process.

%MEM : The percentage of RAM used by the process.

%CPU : The percentage of CPU used by the process.

Command : Name of the process.

 

Q)  Command to print all directories inside a directory?

Ans –

$ ls –d

Or  (for long listing)

$ ls –ld

 

Q) Command to print the sum of file sizes in a directory?

Ans –

$ du –sch

Where s and c used to summarize and print a total size of all files recrusively.

h -> print in human readable format by default GB.

Or

$ du –scb

Where s and c used to summarize and print a total size of all files recrusively.

b -> prints in byte format.

 

Q) How to check if f1 and f2 are same file or not?

Ans –

$ diff f1 f2

If no output -> f1 and f2 are same.

If some output -> f1 and f2 are not same.

 

Q) How to check if last command/script we have ran, it went successful or not?

Ans –

$ echo $?

If output is zero -> command/script ran successfully.

If output  is non zero -> command/script ran unsuccessfully.

 

Q) How to remove duplicates in the file?

Ans –

$ awk ‘!a[$0]++’ file_name.txt > dont_dup.txt

 

Q) How to find largest size length sentence from a file?

Ans –

$ awk '{print NF, $0}' file_name | sort -nr | head -1

 ---------------------------AWK Command------------------------------

Q) How to create a file?

Ans - $ cat > test.txt

Hiranya Patra

Rajesh Dash

Suranjan Dash

Sagar Pradhan

Sudipta Mohapatra

Hiranya Kumar Patra

Rakesh Kumar Dash

Q) Print 2^nd field values and then 1^st field values in the above test.txt file with separated by space?

Ans –

 

Q) Print 3^rd field values in above file test.txt?

Ans –

Q) Print all lines in above file test.txt?

Ans –

Q) Print 2^nd and 1^st field values with concatenation in above test.txt file?

Ans –

Q) What is the output of the below command?

awk '{print $2 $2}' test.txt

Ans –

Q) Write a command to see the content of above test.txt file by comma separated?

Ans –

Q) Write a command to show total number of fields present in line first and then the line in above

file test.txt?

Ans –

Q) How to search pattern ‘atra’ in above file test.txt?

Ans –

 or (print pattern line with total number of words 1^st of each line)

Q) How to search patterns ‘atra’ and ‘Sagar’ in above file test.txt?

Ans –

or (print pattern line with total number of words 1^st of each line)

Q) How to write patterns ‘atra’ and ‘Sagar’ first of each line in above file test.txt?

Ans –

Q) Write a command to print those lines which having total number of words 3 in above

file test.txt?

Ans –

 

Q) I want to save a print command of awk in a file and as per need I use the file

to print output whenever necessary?

Ans –

Step – 1: - (Create a file and save the print statement in the file)

Step – 2: - (then use the file in awk –f command)

$ awk –f <Command File><File Name for which the command to run>

Q) What is the output of the below command?

$ cat test1.txt

one two three

five

six,two,three

$ awk '{print $2}' test1.txt

Ans –

As it considers six,two,three as one word.

Q) What is the output of the below command?

$ cat test1.txt

one two three

five

six,two,three

$ awk –F , '{print $2}' test1.txt

Ans -

 As it considers one two three as one word.