Top 20 SQL Interview Questions for Business Analyst

In my previous article i have given idea about different SQL interview questions for testers;In this article i would like to explain some basic SQL Interview Questions for Business Analyst with its answers.Business analyst also needs to know the SQL in detail.There are so many companies requires business analyst to know SQL.So I would like to explain this article which will give you SQL Interview Questions for Business Analyst with detailed answer.

Question 1 : Most asked SQL Interview Questions for Business Analyst

What is SQL and How it is beneficial for Business Analyst?

Answer :

1.SQL Stands for structured query language.
2.SQL is especially designed to communicate with databases. 3.SQL also pronounced as Sequel is very widely used language in most of the database management systems like Oracle,MySQL,PostgreSQL etc. 4.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.

Business Analyst is nothing but the bridge between Technical team and Customers. If they know the SQL and database structure which was designed by technical team it is easy for them to take decisions acccordingly. Its easy for them to understand design of the database.

Question 2 : 80% asked SQL Interview Questions for Business Analyst

What is database Normalization? Tell me with its advantages.

Answer :

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 3 : What are different system queries needs to know for Business Analyst?

Answer :

There are so many types of System queries needs to know every developer,Business Analyst E.t.c. I would like to visit following link which will give you information about different system tables :

What are Oracle System tables | Oracle System tables list with example

Question 4 :How to Find out the Constraint Information in SQL?

Answer :

To Find out the constraint information in SQL following 2 queries are important :

Queries :

SELECT * From User_Constraints;

SELECT * FROM User_Cons_Columns;

Question 5 : What is query to find out maximum and minimum marks in SQL?

Answer:

Select max (marks) from Student

Union

Select min (marks) from Student;

Business analyst will know the aggregate functions thoroughly.

Question 6 : Most asked SQL Interview Questions for Business Analyst

What are different 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. Union all
3. Intersect
4. Minus

Question 7 : Most asked SQL Interview Questions for Business Analyst

What are basic types of Joins ?

Answer :

There are two basic types of joins in SQL :

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

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

Question 8 : Most asked SQL Interview Questions for Business Analyst

What is Self join? Explain this with example.

Answer:

1.Self join is nothing but joining the table with itself.

2.User can join the multiple instances of same table together to complete the specific requirement.

3.Self-joins are used to compare values in a column with other values in the same column in the same table.

4.The Self join is used to obtain the running count and running totals.

5.Real Example:

The self join is used to find out the employee and his manager name in same employee table.

Query for the same is :

Select E.Employee_id,E.Name as ‘Employee Name’,F.Name as ‘Manager Name’ from Employee E,Employee F where E.Emp_jd=F.Mgr_id;

Question 9 : What is difference between joins and union?

Answer:

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.

Example :

SELECT * FROM Student_1

UNION

SELECT * FROM Student_2;

Question 10 : 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.

Question 11 : Explain Different kind of Joins?

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

Download SQL interview Questions for business analyst in PPT or PDF

Question 12 : What are different 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.

Question 13 : 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.

Question 14 : 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

Question 15 : 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 (‘Amit’);

Question 16 : Explain Rank function in detail with real life example.

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	Oracle	680000
2	Rokul	Oracle	550000
3	Rohit	COGNOS	430000

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

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

Question 17 : What is difference between unique and distinct?

Answer:

There is no difference between unique and distinct keywords apart from one difference.unique is applied before insertion and retrival.It consists  of non duplicate values.if unique constraint is given it does not take duplicate values.distinct is used in retrieval it gives the suppressed row(ex if two rows are same it will show single row and non duplicate row) therefore distinct is the combination of suppressed duplicate and non duplicate rows.Specify DISTINCT or UNIQUE if you want Oracle to return only one copy of each set of duplicate rows selected (these two keywords are synonymous). Duplicate rows are those with matching values for each expression in the select list.

So there is no functional difference between Unique and distinct both have same functionalities.

Question 18 : What will be the output of following Query?

Query :

select case when null=null then ‘Raman’ Else ‘Raghav’ from dual;

Answer:

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

Question 19 : What is Correlated Query?

Answer :

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 Materialized View?

Answer:

Materialized view is also a logical structure which is stored physically on the disc.Like a view in Materialized view we are using simple select statement to create it.You should have create materialized view privileges to create a materialized view.Definition of materialized view(called as MV) has been stored in databases.Materialized views are useful in Data-warehousing concepts.

These are some most important SQL Interview Questions for Business Analyst with its answers.I hope these queries will help you.If you like this article on SQL Interview Questions for Business Analyst or if you have any issues kindly comment in comments section.