In my previous articles I have explained the interview questions for HCL technologies as well as most important 20 sql optimization interview questions. I have studied lot of websites and with my interview experience with Infosys technologies I come up with the set of different important interview questions which may be asked in Infosys interviews. In this article i will explain Interview Questions for Infosys Technologies which may ask in SQL interviews. You can check my previous articles where i have given interview questions for tech mahindra.
Following are some most important Interview Questions for Infosys:
1.What is SQL ? Explain Advantages of SQL?
Answer :
SQL stands for Structured Query Language which is a relational database language used for managing data.SQL language is used to perform different operations on database like insertion in the table,table update.SQL is used by database administrators as well as database programmer for adding the business logic at database level.
Following are advantages of SQL :
1.High Speed
2.Well Defined Standards
3.Most databases uses SQL (Oracle,Postgresql,SQL server)
4.Simple Syntax
5.User Friendly
6.Less Coding
2.What is the latest version of Oracle?
Answer :
Oracle has announced the new version which will come in december 2017 which is Oracle 18c. Oracle 18c is worlds first autonomous database.
3.What are advantages of Oracle 18c?(Latest Interview Questions for Infosys)
Answer :
Oracle CTO has announced that new database version of Oracle will come till December 2017 which is Oracle 18c.Oracle 18c is worlds first autonomous database which will do lot of tasks will be automated using Artificial Intelligence Engine.
Following are some advantages of Oracle 18c :
CLICK HERE TO CHECK ORACLE 18C Advantages and Disadvantages
4.What are different database Environments used in any project?
Answer:
The Project to project database environment varies.But the following is basic environment structure used for projects.
1.Development Environment:
In Development Environment all developer works and development work is been done on development environment.
2.Test Environment:
Developers does not have access of test environment.After development is done the code is migrated to Test Environment.Testing team is working on Test environment and execute black box as well as white box test cases on this Environment.Sometimes System Integration Testing (SIT) is also done on this Environment.
3.UAT Environment:
UAT stands for User Acceptance Testing.On this Environment the Customer side testers tests the software and executes User Acceptance Test Cases.
4.Performance Testing Environment:
On this environment the performance tester tests all performance related issues on this environment. This environment contains very huge data and performance tester will try to break the system using that big data.
5.Production Environment:
On this Environment actual user works and uses the software.
5.What is Unique key constraint?(90% asked in Interview Questions for Infosys)
Answer:
The UNIQUE Constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY Constraints both provide a guarantee for Uniqueness for a column or set of columns.
A PRIMARY KEY Constraint automatically has a UNIQUE Constraint defined on it.
Note: You can have many UNIQUE Constraints per table, but only one PRIMARY KEY Constraint per table is allowed.
CLICK HERE TO CHECK TECH MAHINDRA INTERVIEW QUESTIONS
6.What is mean by Sequence in database?(80 % asked in Interview Questions for Infosys)
Answer:
Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.
When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back.
Once a sequence is created, you can access its values in SQL statements with the CURRVAL Pseudo Column, which returns the current value of the sequence, or the NEXTVAL Pseudo Column, which increments the sequence and returns the new value.
7.What is difference between Union and Union all Operators?
Answer:
| Union | Union ALL |
| 1.Union Set operator is used to fetch the records from 2 different tables which eliminates the duplicate records | 1.Union all Set operator is used to fetch the records from 2 different tables which does not eliminates the duplicate records |
| 2.Syntax:
Select col1,col2…from table1; Union Select col1,col2…from table2; |
2.Syntax:
Select col1,col2…from table1; Union Select col1,col2…from table2; |
| 3.For Performance tuning Union operator is not preferable as it takes time to eliminate duplicate records | 3.Union all is preferable operator in Performance tuning. |
8.What is subquery and what are different types of subqueries?
Answer:
Subquery is query within a query.There are 2 or more types of queries used to fetch output of the query.One is query used inside the query which is known as inner query and the output of query is given to the query which is used outside.The query used outside is called as outer query.Subquery is made up of combining two types of Queries the first type is outer query and other is inner query.
Following are different types of subqueries:
1.Single Row Subquery
2.Multi row Subquery
3.Co-Related Subquery
4.Scalar Subquery
5.Nested SubQuery.
Click Here to get More Information About Subqueries….
Example of Subquery:
Select e.Eno,e.Ename from Employee e where (This is outer query) Ename=
(Select E2.Ename from Employee E2 where E2.Name=’Amit’)(Inner Query Executes first);
9.What is correlated Subquery?(80 % asked in Interview Questions for Infosys)
Answer:
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. (Source-click here)
Example:
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. (Source-Click here)
10.What is difference between varchar and varchar2 datatype?
Answer:
Varchar can store up to 2000 bytes and varchar2 can store up to 4000 bytes of memory space.Varchar will occupy the space for null values whereas varchar2 can not occupy the space for null values.So varchar2 is good to use not to face performance related problems.varchar2 is faster than varchar datatype.
11.What are Set operators in SQL?(70% asked in Interview Questions for Infosys)
ANSWER:
Set operators are nothing but the operators which are used to connect two tables and fetch the records from the two tables.We need to follow one condition that the table set 1 columns and table set 2 columns are same and its datatype must be same.SQL Set Operators combines the result of 2 queries or components on to the single result.
Following are Set Operators in SQL:
Click Here to get more information about Set operators…
12.What is Union Operator?(90 % asked in Interview Questions for Infosys)
Answer:
Union Operator combines the result of 2 or more tables and fetches the results of two select statements.Union operator eliminates the duplicates from the table and fetches the result.For each duplicate row in table only one row is displayed in the result.By considering the performance of SQL using union is not preferable option but if there is situation where user wants to remove the duplicate data from two or more table the use of Union is preferable.
Example:
Select Employee_Num,Employee_name,Department,Salary from Employee_OBIEE;
Union
Select Employee_Num,Employee_name,Department,Salary from Employee_COGNOS;
13.What is Optimizer?(60% asked in Interview Questions for Infosys)
Answer:
Optimizer is nothing but the execution of query in optimum manner.Optimizer is most efficient way of processing the query.SQL parser ,SQL Optimizer and source code generator compiles the SQL statement.
Click Here to get more information about Optimizer..
14.What are 3 different imporatant features of Oracle 18c?
Answer :
1. Self Driving Database :
1.1. Oracle 18 c is self driving database in which patching,upgrades and backups can be done automatically.
1.2.No delay waiting for human process and downtime.
1.3.Automated treat detection and remediation.
1.4. All database maintenance tasks will be done without human interventions.
2. Reliable :
2.1. It is very reliable database as it has self recovering capability of detecting and applying corrective actions.
2.2.Oracle Autonomous Database Cloud automatically implements Oracle Real Application Cluster(RAC).
2.3.No downtime required for upgrades,patching or adding storage capacity.
3.Lower Cost :
3.1.It eliminates the costly downtime
3.2.Self Tuning uses adaptive machine learning which automatically activates caching,indexing,storage of indexes.
3.3.It avoids costly overprovisioning.
3.4. It also helps to cut the labour cost as Oracle 12c is automated self driving database.
3.5. Oracle 18c is 5x to 13x less expensive than AWS (Amazon Web Services).
CLICK HERE TO CHECK ABOUT DBA IMPACT OF ORACLE 18c
15.What is mean by implicit index.Explain with example.
Answer:
Whenever we define unique key or primary key constraints on the table the index will automatically create on the table.These indexes are known as implicit indexes because these are created implicitly whenever the constraint has been applied to the table.These indexes are normal indexes not unique indexes.The indexes are normal because the columns already have defined as unique so uniqueness is already been applied.
Example:
Create table Employee
(Employee_ID varchar2(20) primary key,
Employee name varchar2(50),
salary number(10,0) not null);
If We check description of table:
Desc Employee;
Name Null Type
——————————————–
Employee_ID not null varchar2
Employee_name varchar2
Salary not null number
Here you will see index is already created for Employee_ID as it has defined primary key.
Click Here to get information about indexes….
16.What are different types of indexes?
Answer:
There are following types of indexes:
1.Normal Indexes
2.Bit map indexes
3.B-tree Indexes
4.Unique Indexes
5.Function Based Indexes
17.What are functions of Parser?
1.Syntax Analysis:
The parser checks for SQL statement syntaxs.If the syntax is incorrect then parser gives the incorrect syntax error.
2.Semantic Analysis:
This checks for references of object and object attributes referenced are correct.
18.What are functional Based indexes?Explain with Example
Answer:
1.Function based indexes allows us to index on the functional columns so that oracle engine will take the index and improves the performance of the query.
2.As per requirements we are using lot of SQL functions to fetch the results.Function based indexs gives ability to index the computed columns.
3.Function based indexes are easy to implement and it also provides immediate value.These indexes speeds up the application without changing application code or query.
Example:
Syntax:
Create index indexname on tablename(Function_name(column_name));
Example:
Create index FI_Employee on Employee(trunc(Hire_date));
19.What is Bit-map index?Explain with Example.(90 % Asked in Interview Questions of Infosys)
Answer:
1.If Table contains the distinct values which are not more than 20 distinct values then user should go for Bit map indexes.
2.User should avoid the indexing on each and every row and do the indexing only on distinct records of the table column.You should able to check drastic change in query cost after changing the normal index to Bit map index.
3.The bit map indexes are very much useful in dataware housing where there are low level of concurrent transactions.Bit map index stores row_id as associated key value with bitmap and did the indexing only distinct values.
4.Means If in 1 million records only 20 distinct values are there so Bitmap index only stores 20 values as bitmap and fetches the records from that 20 values only.
Syntax:
Create bitmap index Index_name on Table_name(Columns which have distinct values);
Example:
CREATE BITMAP index BM_DEPT_NAME on DEPT(Department_name);
20.What is composite index?
Answer:
When 2 or more columns are related to each other in the table and the same columns are used in where condition of the query then user can create index on both columns.These indexes are known as composite indexes.
Example:
Create index CI_Employee on Employee(Eno,Deptno);
21.What is Null in SQL?
Answer:
A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.
It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.
22. What is not null constraint?
Answer:
By default, a table column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values.
The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.
23.How to find all details about Constraint?
Answer:
To find details about constraint following query is used:
1.Select * from User_constraints;
2.Select * from User_cons_columns;
24.What is Query to display last 5 Records from Employee table?
Answer:
Select * from Employee e where rownum <=5
union
select * from (Select * from Employee e order by rowid desc) where rownum <=5;
Click Here to get What is Union?
25.What is Query to display Nth Record from Employee table?
Answer:
Select * from Employee where rownum = &n;
These are above most important 25 interview questions which will ask in Infosys interview.Hope you like this article.
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…