SQL Interview Questions & Answer - Part 56 | Google SQL Question | Consulting Bench Time

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 17

Problem Statement:-

Order_Tbl has four columns namely ORDER_ID, PRODUCT_ID, QUANTITY and PRICE.

ORDER_Tbl Table

                            

                            

Write a SQL query that will explode the above data into single unit level records as shown below.

OUTPUT TABLE

                           

SOLUTION

 SELECT
    MT.Order_ID,
    MT.Product_ID,
    1 AS quantity
FROM
    ORDER_TABLE MT
INNER JOIN
    (
        SELECT 1 AS nbr UNION ALL SELECT 2 AS nbr UNION ALL
        SELECT 3 AS nbr UNION ALL SELECT 4 AS nbr UNION ALL SELECT 5 AS nbr

    ) N ON N.nbr <= MT.quantity

Using Recursive CTE

WITH  CTE_Order As
(
-- Anchor Query
Select Order_ID,Product_ID, 1 As Quantity,1 As Cnt
FROM ORDER_TABLE
UNION ALL
-- Recursive Part
Select A.Order_ID,A.Product_ID, B.Quantity,B.Cnt+1
FROM ORDER_TABLE As A INNER JOIN CTE_Order As B
ON  A.Product_ID=B.Product_ID WHERE B.Cnt+1 <= A.Quantity
)
Select Order_ID,Product_ID,  Quantity
FROM CTE_Order

ORDER BY Product_ID,Order_ID

Kindly refer to YouTube video for more details and Don't forget to like and subscribe.

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 16

Problem Statement:-

Order_Tbl has four columns namely ORDER_DAY, ORDER_ID, PRODUCT_ID, QUANTITY and PRICE

Order_Tbl Table        

                

PART A

Write a SQL query to get the highest sold Products (Quantity*Price) on both days.

OUTPUT TABLE

                 

PART B

Write a SQL query to get all product's total sales on 1st May and 2nd May adjacent to each other.

OUTPUT TABLE

                 

PART C

Write a SQL query to get all products day wise, that was ordered more than once.

OUTPUT TABLE

                

SOLUTION

PART A

SELECT A.ORDER_DAY,B.PRODUCT_ID ,A.Sold_Amount
FROM (
(SELECT ORDER_DAY, MAX(QUANTITY*PRICE)as Sold_Amount
FROM Order_Tbl GROUP BY ORDER_DAY) A
INNER JOIN
(SELECT ORDER_DAY ,PRODUCT_ID,QUANTITY*PRICE As Sold_Amount
FROM Order_Tbl ) B

ON A.ORDER_DAY =B.ORDER_DAY AND A.Sold_Amount=B.Sold_Amount)

PART B

SELECT PRODUCT_ID,
SUM(ISNULL(Sales_01,0)) As Total_Sales_01,
SUM(ISNULL(Sales_02,0)) As Total_Sales_02
FROM
(
SELECT PRODUCT_ID,
CASE WHEN ORDER_DAY ='2015-05-01' THEN Total_Sales END as 'Sales_01',
CASE WHEN ORDER_DAY ='2015-05-02' THEN Total_Sales END as 'Sales_02'
FROM(
SELECT ORDER_DAY,PRODUCT_ID, SUM(QUANTITY*PRICE) As Total_Sales
FROM  Order_Tbl
GROUP BY ORDER_DAY,PRODUCT_ID) A
)B

GROUP BY PRODUCT_ID

Using PIVOT Function

SELECT PRODUCT_ID,
ISNULL([2015-05-01],0) As Total_Sales_01,
ISNULL([2015-05-02],0) As Total_Sales_02
FROM
(
SELECT ORDER_DAY,PRODUCT_ID, QUANTITY*PRICE As Total_Sales
FROM  Order_Tbl )BaseTble
PIVOT(
     SUM(Total_Sales)
     FOR ORDER_DAY IN ([2015-05-01],[2015-05-02])

) As Pivot_Table

PART C

SELECT ORDER_DAY,PRODUCT_ID
FROM Order_Tbl
GROUP BY ORDER_DAY,PRODUCT_ID

HAVING COUNT(*) > 1

Kindly refer to YouTube video for more details and Don't forget to like and subscribe.

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 15

Problem Statement:-

Order_Tbl has four columns namely ORDER_DAY, ORDER_ID, PRODUCT_ID, QUANTITY, and PRICE

Order_Tbl Table

                               

PART A

Write a SQL query to get all the products that got sold on both the days and the number of times the product is sold.

    

OUTPUT Table

                             

PART B

Write a SQL query to get products that were ordered on 02-May-2015 but not on 01-May-2015.

OUTPUT Table

                             

SOLUTION

PART A

SELECT  PRODUCT_ID,COUNT(PRODUCT_ID) AS [COUNT],Count(distinct ORDER_DAY)

FROM Order_Tbl

GROUP BY PRODUCT_ID

HAVING Count(distinct ORDER_DAY) > 1

PART B

Using Subquery

SELECT DISTINCT(PRODUCT_ID) FROM Order_Tbl

WHERE ORDER_DAY = '2015-05-02'

AND PRODUCT_ID NOT in (

SELECT PRODUCT_ID from Order_Tbl where ORDER_DAY = 

'2015-05-01')

Using Join

SELECT A.PRODUCT_ID--,B.PRODUCT_ID

FROM (

(

SELECT PRODUCT_ID

FROM Order_Tbl WHERE ORDER_DAY='2015-05-02'

)A

LEFT JOIN

(

SELECT PRODUCT_ID

FROM Order_Tbl WHERE ORDER_DAY='2015-05-01'

)B

ON A.PRODUCT_ID=B.PRODUCT_ID

)

WHERE B.PRODUCT_ID IS NULL

Using EXCEPT Query

SELECT PRODUCT_ID

FROM Order_Tbl WHERE ORDER_DAY='2015-05-02'

EXCEPT

SELECT PRODUCT_ID

FROM Order_Tbl WHERE ORDER_DAY='2015-05-01'

Kindly refer to YouTube video for more details and Don't forget to like and subscribe.

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 11

SCENARIO:  

We have EMP_DETAILS table which contains employee details such as EMPID, GENDER,EMAILID and DEPT_ID columns. We want to display all emailid associated with a particular DEPT_ID to be concatenated with semicolon as shown below.

EMP_DETAILS Table

EMPID	GENDER	EMAILID	DEPT_ID
1111	M	YYYYY@gmaix.com	104
2222	M	ZZZ@gmaix.com	103
3333	F	AAAAA@gmaix.com	102
4444	F	PP@gmaix.com	104
5555	M	CCCC@yahu.com	101
6666	M	DDDDD@yahu.com	100
7777	F	E@yahu.com	102
8888	M	M@yahu.com	102
9999	F	SS@yahu.com	100

     

EXPECTED RESULT:

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 10

   Consider below two tables Table_First and Table_Second for answering the below questions. Basically you will be able to check your JOIN concepts. Take your time and ponder over and then try answer questions. It is easy but very conceptual.

SELECT * FROM Table_First;

SELECT * FROM Table_Second;

Table_First

Table_Second

Take pen and paper and then try to solve SQL query.

Q1.What will be the ouput if we are doing INNER JOIN between the above two tables as follows:
SELECT X,Y
FROM Table_First F
INNER JOIN Table_Second S
ON F.X=S.Y;

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 9

   Consider below Employee table and Department table for answering the questions:-

SELECT * FROM EMP_DETAILS;

SELECT * FROM DEPT_DETAILS;

EMP_DETAILS

DEPT_DETAILS

SQL- Scenario Based Questions PART- 8

SCENARIO:

Table Employee_Details has Gender column. In this Gender column M should be updated with F and F should be updated with M.

Employee_Details

SQL- Scenario Based Questions PART- 7

SCENARIO:  We have table EMPLOYEE_INFO which contains Employee information. We want to display all characters before '@' in EMAILID field as Name field. Please see below in details.

TABLE NAME: EMPLOYEE_INFO

Employee_info Table

EXPECTED RESULT:

SQL- Scenario Based Questions PART- 6

SCENARIO:  We have table COUNTRY_INFO which contains country names. Country_name field has some junk characters. Those junk characters are listed in Value_1 column. Those junk characters in Country_name  need to be replaced with value_2 values for all occurrences while displaying. Please see below the scenario in details.

TABLE NAME: COUNTRY_INFO

         COUNTRY_INFO Table

EXPECTED RESULT:

SQL- Scenario Based Questions PART- 5

SCENARIO:  We have table EMPLOYEE_INFO  which contains Employee information. We want the output table should display NAME field and it should contain EMPNAME field values leaving behind first and last character in it. Please see below in details.

TABLE NAME: EMPLOYEE_INFO

EMPLOYEE_INFO Table

EXPECTED RESULT:

SQL- Scenario Based Questions PART- 4

SQL- Scenario Based Questions PART- 4

SCENARIO:

Suppose there is a table which contains a column NetBalance which has both negative and positive values. Now you have to write a query to dispaly sum of all positive Balances and sum of all negative Balances.

 

Consider below BalanceInfo table for answering this scenario based questions:-

BalanceInfo Table

SQL- Scenario Based Questions PART- 3

Q. How to get distinct rows from SQL table without using DISTINCT keyword .

CREATE TABLE ProductDetail
( ProductID Int,
  ProductName Varchar(30),
  Price int)

  INSERT INTO ProductDetail Values(101,'Dove',55)
  INSERT INTO ProductDetail Values(101,'Dove',55)
  INSERT INTO ProductDetail Values(102,'Hamam',30)
  INSERT INTO ProductDetail Values(103,'Cinthol',35)
  INSERT INTO ProductDetail Values(103,'Cinthol',35)

ProductDetail

To achieve distinct records from a table, we can do by following two methods:

Solution#1: By using GROUP BY clause. By selecting all columns and then grouping by all columns gives distinct records.

  SELECT ProductID,ProductName,Price

  FROM ProductDetail

  GROUP BY ProductID,ProductName,Price

Solution#1_ProductDetail

Solution#2: By using UNION . We know that UNION always gives distinct record from a table. So By doing UNION between the same table will give unique records.

  SELECT ProductID,ProductName,Price

  FROM ProductDetail

  UNION

  SELECT ProductID,ProductName,Price

  FROM ProductDetail

Solution#2_ProductDetail

For other SQL Scenario Based Questions, please visit below link
http://itjunction4all.blogspot.com/2014/04/sql-scenario-based-questions-part-2.html

SQL- Scenario Based Questions PART- 2

   Consider below Employee table and Department table for answering the questions:-

Employee Table:

EMPLOYEE TABLE

Department Table:

DEPARTMENT TABLE

Q1.Write a SQL query to display Employee Name , Department Name and Location

SELECT EmpName,DeptName,Location FROM Employee E

INNER JOIN Department D 

ON E.DeptNo=D.DeptNo

Q2.Write a SQL query to display total number of Employee and maximum salary under each Department Name .

SELECT D.DeptName,
       Max(Salary)As MaxSalary,
       COUNT(EmpName) As TotEmp
FROM Department D
LEFT OUTER JOIN Employee E
ON E.DeptNo=D.DeptNo
GROUP BY D.DeptName

Q2.Write a SQL query to list maximum salary, minimum salary and average salary designationwise, for department 200 and display only those records which has average salary greater than 10000.

SELECT E.Designation,
       Max(Salary)As MaxSalary,
       Min(Salary)As MinSalary,
       AVG(Salary) As AvgSalary
FROM Employee E
INNER JOIN Department D
ON E.DeptNo=D.DeptNo
WHERE D.DeptNo=200
GROUP BY E.Designation
HAVING AVG(Salary) > 10000

If you like this post, please do share by clicking on G+1 Button below :