SQL Interview Questions
Posted by SQL Interview Questions
I have studied lot of websites and data related to the SQL interview questions for Tech Mahindra and different companies.I come up with questions and it answers which have been recentlly asked in Tech mahindra Interview.I have added IBM Interview Questions also which are useful questions for IBM Company.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 companywise bifircation of the questions and answers.Following are some important SQL Interview Questions for Tech Mahindra :
1.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.
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);
2.What is correlated Subquery?
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)
3.What is faster. In Operator in SQL or Exist in SQL?
Answer:
In Operator is slower than Exist operator.In Operator scans full table and checks the each and every value of the table.Exist operator only checks whether the value is present in the table or not.
4.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;
5.How Can i create table with same structure of Employee table?
Answer:
Following query used to create table with same structure of Employee table:
Create table Employee_1 as Select * from Employee where 1=2;
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:
- Union
- Unionall
- Intersect
- Minus
7.What is Union Operator?
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;UnionSelect Employee_Num,Employee_name,Department,Salary from Employee_COGNOS;
8.What is difference Between UNION and UNION All?
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. |
9.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.
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 performace related problems.varchar2 is faster than varchar datatype.
11.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.
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.Explain different Joins in SQL?
Answer:
Join is nothing but connecting 2 tables to fetch the records from 2 or more different tables.There are following types of joins in SQL:
1.Inner join:
Inner join retreives the records which are common between 2 or more tables.
2.Outer join:
Outer join retreives the common records from the table as well as uncommon records from Left or right table.
2.1.Left outer join:
When user needs to fetch all data from left table and common records from left and right table then the join is called as left outer join.
2.2.Left outer join:
When user needs to fetch all data from right table and common records from left and right table then the join is called as right outer join.
2.3.Full Outer Join:
When user needs to fetch the data from both the tables and common records from both of the tables.
3.Cross join/Cartesian join:
When each and every record is connected to each and every record from other table then it is called as cross join or Cartesian join.
14.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.
15.What is Index?What is use of index in SQL?
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.
Indexes are used to improve the performance of the query.
16. What are types of Indexes?
Answer:
1.Normal index
2.Unique Index
3.Bit Map Index
4.Composite Index
5.B-Tree Index(Oracle considered Normal indexes as B-Tree Indexes)
6.Function Based Index
7.Clustered Index
8.Non-Clustered Index.
17.Can you truncate the records using where condition?
Answer:
We can not use where condition with trucate.
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.
19.What are views in SQL?Explain types of Views in SQL?
Answer:
Views:Views are nothing but the logical structure of the table where we can fetch the data from different tables or same table.
There are 2 types of views in Oracle:
1.Simple View:Simple view has been created on only a single table.
2.Complex view:Views which are created using more than 1 table which has joins clauses are known as complex views.
20.What is Materialized View in SQL?
Answer:
Materialized view is also logical structure of one or more table in which data is stored physically in the view.Data has been stored physically in materialized view so data retrieval is faster as compare to simple view.
Hope You will like this article on Tech Mahindra interview Questions.If you want PDF of this article Kindly comment in comment section..
Comments
Post a Comment