IBMlogo
I am posting this article by studing different parameters like Website study,IBM company study and come up with 20 most important SQL interview questions for IBM that may ask in IBM.Earlier i have posted articles on SQL Interview Questions for Tech Mahindra and Interview Questions for Oracle and Interview Questions on TCS.The following are most important SQL Interview Questions for IBM asked recently in IBM technologies.
1.How to create a clone table with same structure of other table?
Answer:
We can create clone table using following command:
Create table Employee1 as select * from Employee where 1=2;
2.If table named Employee has 50 records and Employee2 has 0 records what will be the output of following query?
Select e1.* from Employee e1,Employee e2 where e1.empno=e2.empno;
Answer:
Answer is ‘No Rows Found’
3.What will be the output of following query?
Select * from (select ‘a’ union all select ‘b’) Q;
Answer:
It will throw error because no values are selected in Subquery.
Error code-ORA-00923 from keyword not found expected values.
4.What is inner join? Explain with Business use?
Answer:
When 2 tables are connected such that it should retrieve only the matching records in both tables.Inner join select only the matching records between 2 tables.You can use Equal to(=) operator or Inner join keyword to apply inner join.This join is most widely used joins in real life applications,reporting,webapps,android apps.
Inner join is nothing but fetching common records from two or more tables.
Click Here to get information about Joins..
5.What is query to find distinct records without using distinct keyword?(Asked 90 % of Interview Questions for IBM)
Answer:
select * from Employee a where rowid = (select max(rowid) from Employee b where a.Employee_no=b.Employee_no);
CLICK HERE TO GET 20 COMPLEX INTERVIEW QUESTIONS..
6.What is query to display first 50% records from table?
Answer:
Select rownum,E.* from Employee E
minus
Select rownum,E.* from Employee E where rownum<=(Select count(*/2) from Employee);
7.What is subquery?
Answer:
Subquery is query within query.The output of outer query is assigned to the column which is used in where condition of outer query.The subquery output is returning only one output value and based on that output value the outer query is executed.Subqueries are used in various real life scenarios like report development,Application logic development,Performance tuning of query.
Example:
Select * from Employee where dept_no In (Select dept_no from department where department_name=’Oracle’);
8.What is Correlated Subquery.
Answer:
Correlated Query is nothing but the subquery whose output is depending on the inner query used in that query.Correlated query is the query which is executed after the outer query is executed.The outer query is always dependent on inner query.The approach of the correlated subquery is bit different than normal subqueries.In normal subqueries the inner queries are executed first and then the outer query is executed but in Correlated Subquery outer query is always dependent on inner query so first outer query is executed then inner query is executed.Correlated Subqueries always uses operator like Exist,Not Exist,IN,Not IN.
“Correlated Queries are also called as Synchronized queries…”
9.Explain Steps in Correlated subquery excecution.
Answer:
Execution Steps of Correlated Subqueries:
1.Executes the outer Query
2.For Each row of outer query inner subquery is executed once
3.The result of correlated subquery determines whether the fetched row should be the part of our output results
4.The Process is Repeated for all Rows
“It is not recommended to use Correlated Subqueries as it slows down the performance”
CLICK HERE TO GET INFORMATION ON CORRELATED QUERIES
10. Explain example of correlated subquery.(Asked 80 % of Interview Questions for IBM)
Answer:
Fetch the Employees who have not assigned a single department.
Select * from Employee E where Not exist
(Select Department_no From Department D where E.Employee_id=D.Employee_ID);
Execution of query:
Step 1:
Select * from Employee E ;
It will fetch the all employees
Step 2:
The First Record of the Employee second query is executed and output is given to first query.
(Select Department_no From Department D where E.Employee_id=D.Employee_ID);
Step 3:
Step 2 is repeated until and unless all output is been fetched.
11.What is Rank function as aggregate function?
Answer:
Rank function is used as aggregate function to return the rank of rows in the table within group of rows.If someone needs to find out the rank of specific row in the table then we will use the rank function.
Rank Function Syntax:
RANK( expr1 [, expr2, … expr_n ] ) WITHIN GROUP ( ORDER BY expr1 [, expr_2, … expr_n ] );
Click here to get Real life Example of Rank Function…
12.What is Rank as analytical function in SQL?(Asked 80 % of Interview Questions for IBM)
Answer:
Rank function is used as analytical function in SQL/PLSQL/SQL server which is used to give the rank to the specific record in the table.Rank function is giving you ranking in ordered partitions.Means Ties are assigned to the same values after using the order by clause.So Rank function is not useful where same data is repeated again and again.It is useful in Unique data where user can make partition and order the data properly.
Syntax of Rank:
RANK () OVER (PARTITION BY expression ORDER BY expression);
13.What is query to find the record no 15 from database?
Answer:
Select * from ( Select Employee.*, rownum Rn from Employee) Where Rn=15;
14.What is Rownum in Oracle?
Answer:
Click Here to get information on RowID and Rownum..
15.What is faster in following queries?
select * from Employee;
select employee_num,Name from Employee;
Answer:
Select employee_num,Name from Employee is faster.
16.Using Distinct keyword is good practice or not by considering the Performance?
Answer:
Using distinct keyword is not a good practice because it scans all table to fetch distinct records from the table.
CLICK HERE TO GET BASIC SQL PERFORMANCE TUNING
17.What is difference between Simple view and Complex view?
Answer:
1.Simple View-Simple view is view created on single table
2.Complex View-Complex view is view created on more than 1 tables.
CLICK HERE TO GET MORE INFORMATION ABOUT VIEW AND COMPLEX VIEW
18.What is Materialized View?
Answer:
Materialized view is also a logical structure which is stored physically on the disc.Like a view in Materialized view we are using simple select statement to create it.You should have create materialized view privileges to create a materialized view.Definition of materialized view(called as MV) has been stored in databases.Materialized views are useful in Data-warehousing concepts.
19.Explain Refresh options of Materialized view?
Answer:
1.Refresh on commit:
This option commited the data in materialized views immediately after data inserted and commited in table.This option is known as incremental refresh option.View is not fully refreshed with this option
2.Refresh on Demand:
Using this option you can add the condition for refreshing data in materialized views.
You can refresh the data using fast (incremental approach),Complete,Force options.
20.What is Query to Fetch last record from the table?
Answer:
Select * from Employee where Rowid= select max(Rowid) from Employee;
Hope Everybody like this article.Please comment here if you like the article.If you want this article in PDF Format kindly comment here.
SEARCH YOUR IBM JOB HERE:
In my previous article I have given details about application support engineer day to day…
In my previous articles I have given the roles and responsibilities of L1,L2 and L3…
In my previous articles i have given the hierarchy of production support in real company…
In this article i would like to provide information about production support organization structure or…
In my previous article I have given roles for L1 and L2 support engineer with…
I have started this new series of how to become application support engineer. This article…