SQL Interview Questions for IBM :
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
[Link] i have posted articles on SQL Interview Questions for Tech
Mahindra and Interview Questions for Oracle and Interview Questions on [Link] following
are most important SQL Interview Questions for IBM asked recently in IBM technologies.
[Link] 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;
[Link] 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 [Link]=[Link];
Answer:
Answer is ‘No Rows Found’
[Link] 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.
[Link] is inner join? Explain with Business use?
Answer:
When 2 tables are connected such that it should retrieve only the matching records in both
[Link] join select only the matching records between 2 [Link] can use Equal to(=)
operator or Inner join keyword to apply inner [Link] 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..
[Link] 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..
[Link] 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);
[Link] is subquery?
Answer:
Subquery is query within [Link] output of outer query is assigned to the column which is
used in where condition of outer [Link] subquery output is returning only one output
value and based on that output value the outer query is [Link] 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’);
[Link] is Correlated Subquery.
Answer:
Correlated Query is nothing but the subquery whose output is depending on the inner query
used in that [Link] query is the query which is executed after the outer query is
[Link] outer query is always dependent on inner [Link] approach of the
correlated subquery is bit different than normal [Link] 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 [Link] Subqueries always uses operator like Exist,Not Exist,IN,Not
IN.
“Correlated Queries are also called as Synchronized queries…”
[Link] Steps in Correlated subquery excecution.
Answer:
Execution Steps of Correlated Subqueries:
[Link] the outer Query
[Link] Each row of outer query inner subquery is executed once
[Link] result of correlated subquery determines whether the fetched row should be the part
of our output results
[Link] 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.
[Link] 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 [Link] 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…
[Link] 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 [Link] function is giving you ranking in
ordered [Link] Ties are assigned to the same values after using the order by
[Link] Rank function is not useful where same data is repeated again and [Link] 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);
[Link] is query to find the record no 15 from database?
Answer:
Select * from ( Select Employee.*, rownum Rn from Employee) Where Rn=15;
[Link] is Rownum in Oracle?
Answer:
1. ROWNUM is magical column in Oracle which assigns the sequence number to the rows
retreives in the table.
2. To limit the values in the table you can use rownum pseudocolumn
3. ROWNUM is nothing but logical sequence number given to the rows fetched from the table.
4. ROWNUM is logical number assigned temporarily to the physical location of the row.
5. You can limit the values in the table using rownum
6. ROWNUM is also unique temparary sequence number assigned to that row.
Click Here to get information on RowID and Rownum..
[Link] is faster in following queries?
select * from Employee;
select employee_num,Name from Employee;
Answer:
Select employee_num,Name from Employee is faster.
[Link] 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
[Link] is difference between Simple view and Complex view?
Answer:
[Link] View-Simple view is view created on single table
[Link] View-Complex view is view created on more than 1 tables.
CLICK HERE TO GET MORE INFORMATION ABOUT VIEW AND COMPLEX VIEW
[Link] is Materialized View?
Answer:
Materialized view is also a logical structure which is stored physically on the [Link] a
view in Materialized view we are using simple select statement to create [Link] should
have create materialized view privileges to create a materialized [Link] of
materialized view(called as MV) has been stored in [Link] views are useful
in Data-warehousing concepts.
[Link] Refresh options of Materialized view?
Answer:
[Link] on commit:
This option commited the data in materialized views immediately after data inserted and
commited in [Link] option is known as incremental refresh [Link] is not fully
refreshed with this option
[Link] 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.
[Link] is Query to Fetch last record from the table?
Answer:
Select * from Employee where Rowid= select max(Rowid) from Employee;
