Interview Questions for TCS | SQL Interview Questions for TCS

Interview Questions for TCS:

I have studied lot of stuff related to interview questions for TCS and i am presenting the set of interview questions which are most frequently asked in TCS interviews.In my previous articles i have added the most frequently asked interview questions for Tech Mahindra,Oracle corporation and IBM. In This article i will explain the Interview Questions for TCS.These interview questions are very important for preparation for any interview but most of the questions are asked in TCS interview.

Following are Some most important questions that may ask in TCS interviews:

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

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

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

CLICK HERE TO GET MORE INFORMATION ABOUT DIFFERENT CONSTRAINTS OF SQL

4.What is Unique key constraint?(90% asked in Interview Questions for TCS)

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.

5.What is the output of following Query?

select case when null=null then ‘Amit’ Else ‘Pradnya’ from dual;

Answer:

In SQL null value is not equal to itself.So null=null is false and the output of above query is ‘Pradnya’.

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

7.Explain Union Operator in detail?

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.

8.How to select first 5 characters from First name in Employee table?

Answer:

Oracle Query:

Select Substr(First_name,0,5) from Employee;

MS SQL:

Select Substr(First_Name,1,5) from Employee;

MySQL:

Select Substr(First_Name,1,5) from Employee;

9.How to fetch the name of the Employee by removing white spaces?

Answer:

Select trim(Employee_name) from Employee;

10.How to fetch all the records from Employee whose joining year is  2017?

Answer:

Oracle:

select * from Employee where To_char(Joining_date,’YYYY’)=’2017′;

MS SQL:

select * from Employee where substr(convert(varchar,Joining_date,103),7,4)=’2017′;

CLICK HERE TO GET 20 Most important Complex SQL Queries..

11.What is mean by Sequence in database?(80 % asked in Interview Questions for TCS)

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.

12.What is first and last function in SQL?

Answer:

The FIRST and LAST functions can be used to return the first or last value from an ordered sequence. Say we want to display the salary of each Employee, along with the lowest and highest within their department we may use something like.

Example:

SELECT EmpNo, DeptNo, Sal ,MIN (Sal) KEEP (DENSE_RANK FIRST ORDER BY Sal) OVER (PARTITION BY DeptNo)”Lowest”, MAX (Sal) KEEP (DENSE_RANK LAST ORDER BY Sal) OVER (PARTITION BY DeptNo) “Highest”FROM   EMPLOYEE ORDER BY DeptNo, Sal;

 13.What is join in SQL?(100% asked in Interview Questions for TCS)

Answer:

The most used concept in real life scenarios are nothing but SQL Joins.Although in reporting,stand alone applications development,Web application development the concept of join is really important.

Joins are nothing but Combining the records from two or more tables.

There are following 2 types of joins:

1.Joins using Operators -> Equi Join,Non Equi Join

2.Joins using Concept-> Inner Join,Outer Join,Cross Join,Self Join.

CLICK HERE TO GET MORE INFORMATION ABOUT SQL JOINS

14.What is view in SQL?(100% asked in Interview Questions for TCS)

Answer:

Views in SQL is nothing but the logical table created from one or more tables.We can use the views to fetch the columns from one or more different tables at a time.In real life specifically views are used in Reporting purpose.To create a report we need data from different tables and need to show it on a single dashboard so we are using the views to fetch the data from different tables.View can contain all rows from the table or selected rows from the table.

CLICK HERE TO GET INFORMATION ABOUT SQL VIEWS in Detail

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

16.Which is faster operator Union or union all?

Answer:

Union all is faster than union because it does not have sorting program running inside.The union operator is slower than union all because it removes duplicates from the data.To Remove Duplicate compiler needs to run sorting program and table is scanned full.

So Union all is faster than union operator.

17.What is difference between Row_id and Rownum?

Answer:

ROWID is nothing but the physical address given to that row which is in hexadecimal format.ROWNUM is nothing but the logical sequence given to the row of that column.

18.How to Find table name and its owner?

Answer:

To Find table name and its owner following query is used:

Select table_name,Owner from All_tables order by table_name,owner;

CLICK HERE TO GET MORE INFORMATION ABOUT ORACLE SYSTEM TABLES

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

20.How to Find the Sequence information?

Answer:

To Find the Sequence information following query is used:

Select * from User_Sequences;

Click on Topic You want to learn:

1. History of SQL
2. SQL Create Table(DDL in SQL)
3. SQL DML Statements(INSERT,UPDATE,DELETE)
4. SQL Select Statement Execution
5. Operators in SQL
6. Views in SQL
7. Materialized View in SQL
8. Joins in SQL
9. Inner Join / Outer Join
10. Full Outer Join / Cartesian Join
11. Union and Union ALL
12. Intersect and Minus
13. Indexing in SQL
14. Rank and Dense Rank
15. SubQueries and Correlated Subqueries
16. Parser and Optimizer
17. Oracle 11 G new Features
18. SQL Functions List
19. Constraints in SQL
20. Database Normalization
21. Table Partitioning

Hope you will like this article on Interview Questions for TCS.If you want PDF document for this aritcle or if you like this article kindly comment in comment box.

HOME