HCL
SQL Interview Questions SQL Interview Questions For Freshers

Interview Questions for HCL | What are SQL Interview Questions for HCL

Amit S April 5, 2017

HCL Interview Questions:

I have studied different websites and data related to the SQL interview questions for HCL and different companies.I come up with the set of the questions which is been asked as HCL Interview Questions.I have added IBM Interview Questions,Tech Mahindra Interview Questions,TCS Interview Questions and Interview Questions for Oracle in my previous articles.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 HCL Interview Questions

HCL Interview Questions

20 HCL Interview Questions :

1.What is SQL?How SQL is helpful in your project?

Answer:

SQL Stands for Structured Query Language which is specially desinged to communicate with databases.SQL pronounced as Sequel is very widely used language in most of the database management systems like Oracle,Mysql,Postgresql etc.SQL provides us  a simple and efficient way of reading,writing,executing the data from the system.

SQL is helpful from starting of development to testing in our project. We are using backend technology as oracle so we need to use SQL for any data management.So that SQL is used in every SDLC(Software Development Life Cycle Steps) except requirement gathering phase in our project.

CLICK HERE TO GET INFORMATION ABOUT OPERATORS IN SQL

2.Suppose I want Employee named Rahul.Suppose i want to get position of U in Rahul.Kindly tell me Queries in MySQL,MSSQL,Oracle?

ANSWER:

For Oracle:(We are using INSTR function in oracle)

Select Instr(name,’u’) as Position from Employee where name=’Rahul’;

For MSSQL:(We are using CHARINDEX function in SQL server)

Select CHARINDEX(‘u’,name,0) as position from Employee where name=’Rahul’;

For MySQL:(We are using Locate function in Mysql):

Select Locate(‘u’,name) as position from Employee where name=’Rahul’;

CLICK HERE TO GET 20 MOST IMORTANT COMPLEX SQL QUEREIS

3.What is difference between where and having clause?(90% asked HCL Interview Questions)

Answer:

Both Where Clause and Having clause are filters but these two filters are used for two different purposes.Where clause is used to filter the non aggregated values and Having clause is used for Aggregated values.Simply Where clause is used in Static non aggregated values and having clause is used in aggregated values where you are using aggregated functions like Count,Sum,Avg.etc.Having Clause always come with group by clause,you can not use having clause directly.

Example:

Select * from Employees where name=’Amit’;

Select count(*) from Employees group by name having salary > 10000;

4.What is difference between joins and union?

Answer:

SQL joins are nothing but fetching the records where 2 or more tables having the similar condition.

Example:

Suppose we want to fetch Employee name from Employee table and Department name from department table where common condition is dept_no.

Select E.Employee_name,D.Department_name from Employee E,Department D where E.dept_no=d.dept_no;

CLICK HERE TO GET MORE INFORMATION ABOUT JOINS IN SQL

Union is used to create distinct dataset from two similar datasets.Union does not require any joining condition but the basic required thing is similar kind of dataset where all columns from 2 datasets  have same datatype and same number of columns.

CLICK HERE TO GET INFORMATION ABOUT UNION

Example:

Select * from Employee1;

Union

Select * from Employee2;

5.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;

6.How to get unique department from employee table?

Answer:

Select distinct Department_name from Employee;

7.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.

CLICK HERE TO GET MORE INFORMATION ABOUT CONSTRAINTS

8.What is Self join?Why it is required?

Answer:

Self join is nothing but Act of joining one table with itself.

Self join is useful in converting hierarchical data structure to flat data structure.

Using Self join you may achieve 2 different functionalities at a same time.

Real Life Example:

The simple and most common example is if in same Employee table we are storing manager id also.We need to fetch the Employee and its manager name then following query is used:

Select E.Employee_name,M.Employee_name as Manager_name

From Employee E,Employee M

where E.Manager_ID=M.Manager_ID;

9.Write a query to delete Employee data from Employee table who got incentive in which is stored into incentive table?

Answer:

Delete from Employee where Employee_id In(Select Employee_ID from Incentive);

10.How to select first five records from the table?(Most commonly asked in HCL Interview Questions)

Answer:

Different DBMS have different ways to select first five records from the table

Oracle:

Select * from Employee where rownum <=5;

SQL Server:

Select TOP 5 * from Employee;

Postgresql:

Select * from Employee limit 5;

11.What is difference between Union and Union all?

Answer:

Operators in SQL

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 all

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.

12.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.

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.

Click Here to get more information about Optimizer..

14.How to insert name with single Quote in Employee table?

Answer:

We need to use  another single Quote before special character.

Insert into Employee (Name) values(‘Amit”’);

15.Fetch the employees who has joined in January month?

Answer:

Select * from Employee where To_Char(Joining_Date,’MM’)=’01’;

Select * from Employee where To_Char(Joining_Date,’MON’)=’JAN’;

16.How to set NLS_Date_Format in SQL?

Answer:

Alter session NLS_DATE_FORMAT=’HH24:MI:SS’;

17.Select 20% salary of Amit and 10% salary of Pradnya from Employee Table?

Answer:

Select Name,

Case when name=’Amit’ then 0.2*salary end as ‘Amit Salary’,

case when name=’Pradnya’ then 0.1*salary  end as ‘Pradnya Salary’ from Employee;

18.What is unique index?

Answer:

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.Especially while creating the table if we specify the primary key  then unique index is automatically created on that column.But for Unique key constraint columns you separately need to do indexing.Kindly make sure that Unique key indexes created on the columns which has unique values only.

CLICK HERE TO GET INFORMATION ON INDEXES..

19.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…

20.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;

Check  out your SQL topics :

Unix Tutorials :

Oracle Business Intelligence Tutorial :

Click Here for SQL interview Questions

Hope everyone get idea about the interview questions asked in HCL technologies.If you like this article dont forget to comment here.

HOME