SQL Interview Questions for Cognizant :

I have studied lot of websites and data related to the SQL interview questions for Cognizant technologies and different companies. I come up with the set of questions and it answers which has been recently  asked in Cognizant Interview. I have added Infosys technologies interview questions as well as HCL interview questions which are useful. I will keep updating the questions weekly or in 15 days so that the users of this site will get very useful information about what kind of SQL questions asked in different companies and users will get proper idea and company wise bifurcation of the questions and answers. Following are some important SQL Interview Questions for Cognizant  :

1.What are Set operators in SQL?

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:

1. Union
2. Unionall
3. Intersect
4. Minus

Click Here to get more information about Set operators…

2.What is difference Between UNION and UNION All?(90 % asked in Interview Questions for Cognizant)

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.

3.What is DDL?

Answer:

DDL stands for Data Definition Language.Following are the different DDL statements:

Statement	Description
CREATE	Creates a new table, a view of a table, or other object in database
ALTER	Modifies an existing database object, such as a table.
DROP	Deletes an entire table, a view of a table or other object in the database.

4.What are different parameters to consider the database performance of Application?(80 % asked in Interview Questions for Cognizant)

Answer:

There are lot of parameters to consider the performance of application:

1.What size of images we are using in application.The images we are using on application should not be maximum size.

2.What is the data volume used to fetch the data

3.Data cardinality: The most important factor is data cardinality of the data in application.Data should be divided in proper manner and the database should be in well normalized form

4.Indexing done:Indexing should be done properly in database (Click here for index info)

5.What are indexes in SQL?(90 % asked in Interview Questions for Cognizant)

Answer:

“Index is optional structure associated with the table which may or may not improve the performance of Query”

In simple words suppose we want to search the topic in to book we go to index page of that book and search the topic which we want.Just like that to search the values from the table when indexing is there you need not use the full table scan.

6.How many long columns allowed in table?

Answer :

There is only one Long column allowed in table.

7.How to find that selected column is indexed or not?

Answer:

To find out the selected column is indexed or not there are following 2 ways

1.Selecting Explain plan:

Select that specific table and check the explain plan of the table.But if that select statement is not using the index then it will not show the column is indexed or not.

2.Using System tables:

You can use the system table all_ind_columns and all_indexes table to check that column is indexed or not.This is the best way to find out column is indexed or not.

Select * from all_ind_columns where table_name=’Name of the table’ and column_name=’Name of column’;

If the column is indexed then you will get the output.

8.What are different types of indexes?(80 % asked in Interview Questions for Cognizant)

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

9.What is difference between NVL,NVL2 and Nullif?

Answer:

1.NVL :

NVL function substitutes a value when a null value is encountered.

2.NVL2 :

NVL2 substitutes a value when a null value is encountered as well as when a non-null value is encountered.

3.NULLIF:

NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.

10.What is mean by Unique Indexes?(80 % asked in Interview Questions for Cognizant)

Answer:

1.To create unique index you must have CREATE ANY INDEX privilege.Here the concept is bit different.User needs to check the values of the table to create unique index.If table contains uniquely identified values in specified column then you should use unique index.

2.Especially while creating the table if we specify the primary key  then unique index is automatically created on that column.

3.But for Unique key constaint columns you separately need to do indexing.Kindly make sure that Unique key indexes created on the columns which has unique values only.

4.The unique indexes are also called as clustered indexes when primary key is defined on the column.

Example:

Create Unique index  Index_name on Table_name(Unique column name);

Example:

CREATE UNIQUE INDEX UI1_EMP on EMP(EMP_ID);

Click Here to get information on basics of Performance Tuning..

11.How to find duplicate rows from the table?

Answer:Click on Query for detailed explanation.

Select * from Employee a where row_id != select max(row_id) for Employee b where a.Employee_num=b.Employee_num;

12.Can table have 2 primary keys?

Answer:

Table does not have 2 primary key .Table can have 2 unique keys but only 1 primary key.

13.What is Optimizer?

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.

14.What is query to calculate second highest salary of the employee using analytical function?

Answer:

We need to use Dense_Rank function to calculate second highest salary:

select * from (Select Dense_Rank() over ( order by  salary desc) as Rnk,E.* from Employee E) where Rnk=2;

15.What do you know about Rank 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 ] );

Real Life Example:

Consider following table:

Employee_num	Employee_name	Department	Salary
1	Amit	OBIEE	680000
2	Rohan	OBIEE	550000
3	Rohit	COGNOS	430000

Question:Find out Rank of Employee whose Department is OBIEE and Name is rohan?

select RANK(Rohan, OBIEE) WITHIN GROUP (ORDER BY Name, Department) from employees;

The Above will return result as 2 if we consider the table given in example.

16.What is mean by Aggregate functions in SQL? Tell me with examples.

(90% asked in Interview Questions for Cognizant)

Answer :

SQL aggregate functions are the functions where user can give multiple values as input but function always returns single value as output.

Aggregate Functions are the Functions where the value of input is grouped together and fetches the output as a single value

Following is single line Explanation of the Aggregate functions:

• AVG() – Returns the average value
• COUNT() – Returns the number of rows
• MAX() – Returns the largest value
• MIN() – Returns the smallest value
• SUM() – Returns the sum

17.What is mean by SQL Scalar Functions?

Answer:

SQL scalar functions are the functions whose input range is one dimensional and which returns the single output for each row.SQL Scalar functions returns value of every row which we are used in query to process.

SQL Scalar Functions are also known as Single Row Functions..

Following is Single line Explanation of some of the useful Scalar Functions:

• UCASE() – Converts a field to upper case
• LCASE() – Converts a field to lower case
• SUBSTR() – Extract characters from a text field
• LEN()/LENGTH() – Returns the length of a text field
• ROUND() – Rounds a numeric field to the number of decimals specified

18.What is the Query to fetch last record from the table?(70% asked in Interview Questions for Cognizant)

Answer:

Select * from Employee where Rowid= sel

ect max(Rowid) from Employee;

19.How to get 3 Highest salaries records from Employee table?

Answer:

select distinct salary from employee a where 3 >= (select count(distinct salary) from employee b where a.salary <= b.salary) order by a.salary desc;

20.What is difference Between Intersect and Minus set operators?

Answer:

Intersect	Minus
1.Union Set operator is used to fetch the common records from 2 different tables .	1.Minus Operator is used to fetch the records from first table which eliminates common records.
2.Syntax: Select col1,col2…from table1; Intersect Select col1,col2…from table2;	2.Syntax: Select col1,col2…from table1; Minus Select col1,col2…from table2;
3.For Performance tuning Intersect operator is not preferable as it takes time to fetch duplicate records	3.Minus operator is preferable operator in Performance tuning.

21.What is latest Version of Oracle?

Answer:

Oracle 18c is latest version of oracle which is worlds first autonomous database.

22.What are advantages of Oracle 18c?

Answer :

Following are different advantages of Oracle 18c :

• Oracle 18c uses adaptive machine learning which will reduce the human errors.
• It reduces the complexity of database
• It is highly relible and secure database
• It reduces the operational cost
• Self Driving database
• Self Tuning database
• Most efficient consumption of resources
• Less human intervention

23.What is mean by operators? which are different type of operators ?

Answer :

“Operators are nothing but the the statement which are used to perform SQL Operations”

There are following different types of Operators in SQL:

1. Arithmetic Operators

2.Logical Operators

3.Comparison Operators

4.Negation Operators

24.What is mean by all and any operator in SQL?

Answer:

1.ALL Operator:

ALL Operator is used to compare all the values from another dataset and fetch the specific records from the table.

Example:

Select * from Student where 10000 >ALL (Select Fees from Student_Fees);

The above statement will display all the records from Student table where Student fees is > 10000 which we are fetching from different table named ‘Student_Fees’

2.Any Operator:

Any Operator is used to compare the values from Any value from another table with that condition.

Example:

Select * from Student where 10000 >ANY (Select Fees from Student_Fees);

The above statement will display ANY of the records from Student table where Student fees is > 10000 which we are fetching from different table named ‘Student_Fees’.

25.What is negation operator in SQL?

Answer :

Negation Operators are operators which has negation condition like ‘Not’ Condition.

NOT Operator:

Not operator is negation operator which reverses the meaning of logical operator.The Not operator is used before Logical operator like Not In,Not Exist etc.

Example:

Select * from Student where name not in (‘Divya’);

The above statement will fetch all the records from Student table where name is not Divya.

Hope everyone likes this article.If you like this article don’t forget to share it.