In my previous article I have given multiple SQL queries for multiple purpose. In this article I would like to list the SQL queries for Business Intelligence asked in interview. There are multiple queries which are important for Business Intelligence asked in interview. We require to fetch very complex data from multiple tables in Business intelligence. The queries format is quite different in Oracle BI and other BI tools. The Business intelligence is the set of multiple processes where you can fetch data from multiple heterogeneous sources and create the user identifiable data.
There are following BI queries asked in interview :
The Business intelligence queries are many used for reporting purpose. The most of the queries are select queries. Our main purpose over here is to fetch the data which is required to business users,
Query 1 : What is query to fetch the data from Customer table?
Answer :
Select Customer_Id,Customer_Name, Customer_Ph from Customer;
Query 2 : Suppose there are two tables Customer and Customer_1 which has same columns. Customer_1 is historical table. If you want to fetch the data from both tables with duplicate records and without duplicate records. What will be the query?
Answer :
Select * from Customer
union all
Select * from Customer_1;
There are so many real world scenarios where you require to use the union all operator.
Query 3 : If you require only common data from both the table kindly let me know the query .
Answer :
You can use intersect operator,
Select * from Customer
intersect
Select * from Customer_1;
Query 4 : How to find out departmentwise monthwise salary for employee.
Answer :
Here we require to use the group by keyword,
Select Department_Name, TO_CHAR (Hire_date,’Mon’) as Hire_Month from Employees group by Department_name, Hire_Month;
Query 5 : How to display count of employee where department are columnwise.
Answer :
Here we require to use the pivot in SQL. There are so many situations in BI where you require to use pivot.
Select * from
(Select Department_Name from Department)
PIVOT
(Count(Employee_name)
For Department_name IN (‘IT’,’BI’,’Marketing’)
);
Query 6 : What is query to write maximum and minimum salary of employees departmentwise where maximum and minimum salary are columns.
Answer :
There are requirements where you need the data in specified fashion . Here we require to use the pivot with multiple columns,
WITH
Table1
AS
(
SELECT
Department_name,
JOB_Title,
Salary
FROM
Employee
)
SELECT
*
FROM
Table1
PIVOT
(
MIN(Salary) AS Minimum_Salary,
MAX(Salary) AS Maximum_Salary
FOR
(JOB_Title)
IN
(
‘BI Developer’ AS BI_Developer,
‘Support Engineer’ AS Support Executive
)
)
ORDER BY
Department_Name;
Query 7 : Suppose there are three tables and we requie to find out the specific data from three tables. What will be the query?
Answer :
We will take the example of Employee and salary,department table,
SELECT e.emp_name, s.salary FROM Employee e JOIN Salary s ON e.emp_id=s.emp_id JOIN Department d ON s.emp_id=d.emp_id;
Query 8: How to fetch last record from Customer table?
Answer :
These kind of queries we always need while working with BI,
Select * from Customer where Rowid= select max(Rowid) from Customer;
Query 9 : How to convert seconds in time format.
Answer :
Some time we require more time queries like converting the seconds in to time format,
SELECT
TO_CHAR (TRUNC (2700/3600),’FM9900′) || ‘:’ ||
TO_CHAR (TRUNC (MOD (2700, 3600)/60),’FM00′) || ‘:’ ||
TO_CHAR (MOD (2700, 60),’FM00′)
FROM DUAL;
Query 10 : If you want to calculate last day of current quarter.
Answer :
SELECT ADD_MONTHS (TRUNC (SYSDATE,’Q’), 3)-1 FROM DUAL;
SELECT LAST_DAY (ADD_MONTHS (TRUNC (SYSDATE, ‘Q’), 2)) FROM DUAL;
Query 11 : How to find the Employees from employee table whos salary is more than average salary?
Answer :
This is tricky situation where you will get the error if you try to use group by. Here you require to use the subquery and with clause together,
With Employees_Temp(Salary)
As
(Select Avg(Salary) From Employees),
Select Roll_No,Name,Salary From Employees, Employees_Temp
Where Employees.Salary> Employees_Temp.Salary);
Query 12 : How to validate Email using simple SQL query?
Answer :
WITH T_validate AS
(SELECT ‘amiets@gmail.com’ email FROM dual)
SELECT * FROM T_validate WHERE REGEXP_LIKE (EMAIL, ‘^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$‘);
Query 13 : How to fetch the strings and its count where word= SQL.
Answer :
SELECT
regexp_count(Employee_details, ‘SQL’)
from Employees;
Query 14 : What is query to find out digits from Specific column string.
Answer :
SELECT
regexp_count(Employee_Details, ‘\d’)
from Employees;
Query 15 : How to calculate number of vowels.
Answer :
You require to use the REGEXP_INSTR function,
SELECT REGEXP_INSTR (‘Oracle is best database programa11 ming language 1 language ‘,’a|e|i|o|u’) regexp_instr FROM dual;
These are some important SQL queries for Business Intelligence asked in interview. If you like this article or if you have any issues with the same kindly comment in comments section.