In my previous articles I have given the different SQL Interview questions with Answers. In this article i would like to give you different SQL Related Interview Questions with its answers. I would like to bind all important SQL related interview questions from my different articles and will give you single article.These are all important SQL Interview questions with answers.I will explain mixed SQL interview questions for not only the database developer but also the tester and analyst.
Question 1 : What is SQL database Management System?
(100% asked SQL Related Interview Questions )
SQL Stands for Structured Query Language which is specially designed 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.this is one of the SQL Interview Question ever asked in interviews.
Question 2 : What is Null in SQL?(100% asked SQL Related Interview Questions )
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.
Question 3 :What is database Normalization? Tell me with its advantages.
Database Normalization is nothing but technique of designing the database in structured way to reduce redundancy and improve data integrity.
Following are advantages of normalization :
- To Eliminate the redundant or useless data
- To Reduce the complexity of the data
- To Ensure the relationship between tables as well as data in the tables
- To Ensure data dependencies and data is logically stored.
Question 4 : What are Set operators in SQL?
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
2. Union all
Question 5 : What is Self Join in SQL? Explain with Example.(100% asked SQL Related Interview Questions )
Self join is nothing but the table joins with itself. There are lot of tables which contains more than one functionality at that time the concept of self join comes to the picture.
SELECT a.column_name, b.column_name…
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;
If one table contains Employee and its position.If we need to fetch the Employees and its managers then self join is used.
Question 6 : Explain about Union Operator with Examples.
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.
Select Employee_Num,Employee_name,Department,Salary from Employee_PLSQL;
Select Employee_Num,Employee_name,Department,Salary from Employee_SQL;
Question 7 : What is difference between Varchar and Varchar2 ?
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.
Question 8 : What is mean by index and what are different types of indexes?
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.
Types of Indexes :
3.Bit Map Index
5.B-Tree Index(Oracle considered Normal indexes as B-Tree Indexes)
6.Function Based Index
Question 9 : What is Materialized View in SQL?(90 % asked in SQL Related Interview Questions )
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.
Question 10 : Explain Non Equi Join with example?(100% asked SQL Related Interview Questions )
Non Equi Join :
1.When two or more tables are joining without Equal to condition then that join is known as Non Equi join.
2.The use of non equi join is very rare in real life scenarios.You can join tables using any other attributes except equal to operator.
3.You can use any operator for non equi join that is <>,!=,<,>,Between.
Select b.Department_ID,b.Department_name from
Employee a,Department b where a.Department_id <> b.Department_ID;
Question 11 : What is mean by Nested Join?
In nested joins, for each tuple in the outer join relation, the system scans the entire inner-join relation and appends any tuple that match the join-condition to the result set.
Question 12 : Explain Merge join ?
1.Merge join If both join relations come in order, sorted by the join attribute(s), the system can perform the join trivially, thus: It can consider the current group of tuple from the inner relation which consists of a set of contiguous tuple in the inner relation with the same value in the join attribute.
2.For each matching tuple in the current inner group, add a tuple to the join result. Once the inner group has been exhausted, advance both the inner and outer scans to the next group.
Question 13 : What is difference between joins and union?
(100% asked SQL Related Interview Questions )
SQL Join :
SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.
SQL Union Operator :
UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables.
SELECT * FROM EMPLOYEE1
SELECT * FROM EMPLOYEE2;
Question 14 : What is difference between NVL,NVL2 and Nullif?( 80 % asked in SQL related Interview Question )
NVL function substitutes a value when a null value is encountered.
NVL2 substitutes a value when a null value is encountered as well as when a non-null value is encountered.
NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.
Question 15 : What is full outer join? Explain with example?
1.The Full Outer Join and Cartisian joins are less used joins in day to day applications,reporting,web applications.
2.These joins are less used because it deals with all the data from left table as well as right table.So if we want all the records from both left and right table we will directly use table no need to use the joins.
Select t1.col1,t2.col2….t ‘n’col ‘n.’.
from table1 t1 full join table2 t2
Select a.Department_ID,b.Employee_Name from
Employee b full outer join Department a
Question 16 : Write query to find the repeated characters from your name?
Select regexp_count (‘AmitA’,’A’) as Repeated_character from dual;
Question 17 : Explain Rank Function as Analytical function with Example.(70% asked SQL Related Interview Questions )
Rank function is used as analytical function in SQL/PLSQL/SQL server which is used to give the rank to the specific record in the table.Rank function is giving you ranking in ordered partitions.Means Ties are assigned to the same values after using the order by clause.So Rank function is not useful where same data is repeated again and again.It is useful in Unique data where user can make partition and order the data properly.
Syntax of Rank:
RANK () OVER (PARTITION BY expression ORDER BY expression)
SELECT Employee_Name,Department_No,Salary,RANK() OVER (PARTITION BY Department_No ORDER BY Salary) “Rank” FROM EMPLOYEE;
Question 18 : What is dense_rank Explain with examples.
Dense Rank analytical function is same as rank but it has assigned the consecutive rank for Tie values in the table.So Disadvantage of rank function has been overcome in Dense_Rank function.Dense rank function is useful to give the rank for the SQL values in the table.It is not repeating the rank values so these functions are really very useful in development of reports where we require actual rank values.
“Dense_Rank gives consecutive ranking for ordered partitions…”
Syntax of Dense_Rank:
Dense_RANK () OVER (PARTITION BY expression ORDER BY expression)
SELECT Employee_Name,Department_No,Salary,Dense_RANK() OVER (PARTITION BY Department_No ORDER BY Salary) “Rank” FROM EMPLOYEE;
Question 19 : What is Correlated Subquery.
Correlated Query is nothing but the subquery whose output is depending on the inner query used in that query.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.
“Correlated Queries are also called as Synchronized queries…”
Question 20 : What is difference between ‘Between’ operator and ‘In’ operator?
BETWEEN Operator :
The BETWEEN operator is used to fetch rows based on a range of values.
SELECT * FROM Students WHERE ROLL_NO BETWEEN 20 AND 30;
This query will select all those rows from the table Students where the value of the field ROLL_NO lies between 20 and 30.
IN Operator :
The IN operator is used to check for values contained in specific sets.
SELECT * FROM Students WHERE ROLL_NO IN (20,21,23);
Question 21 : How to represent comments in oracle?
There are following 2 ways for commenting in oracle:
1.Single Line comment: Two dashes (–) before begining of the line
2. Multi-line comment/Block comment:When user wants to comment multiple line /* */ operators are used.
Question 22 : What are functional Based indexes?Explain with Example
(100% asked SQL Related Interview Questions )
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.
Create index indexname on tablename(Function_name(column_name));
Create index FI_Employee on Employee(trunc(Hire_date));
Question 23 : What is visible/invisible property of index?
User can make the indexes visible and invisible by altering the indexes.Following statement is used to make indexes visible and invisible.
ALTER INDEX index_name VISIBLE;
Question 24 : What is difference between where and having clause?(90% asked SQL Related Interview Questions)
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.
Select * from Employees where name=’Amit’;
Select count(*) from Employees group by name having salary > 10000;
These are some important SQL Related Interview Questions with answers. If you like this article or if you have any issues with the same kindly comment in to comments sections.