SQL- Scenario Based Questions PART- 1

SQL- Scenario based questions Part 1.

                                   Now the recruiters are setting a new trend by asking a scenario based questions in an interview.  Keeping in this mind, we have come up with many standard scenario based questions.

Scenario: You have been given the following Table "Annualhike". This table contains the name of employee, salary, designation, CRR and the increment. You have to test the increment column of the table based on the below logic:

• Employee gets an increment of 15% of Salary if he/she gets CRR 1+.
• Employee gets an increment of 10% of Salary if he/she gets CRR 1.
• Employee gets an increment of 5% of Salary if he/she gets CRR 2.

You have to find out the records if there are any incorrect population in Increment field.

SELECT * FROM Annualhike

Solution

Method1

SELECT * FROM Annualhike A 
WHERE Increment <> (SELECT CASE WHEN A.CRR='2' 
                      THEN .05* SalaryPackage
      WHEN A.CRR='1'  THEN .1* SalaryPackage
      WHEN A.CRR='1+' THEN .15* SalaryPackage  
      END AS Increment)

Method 2

SELECT * FROM Annualhike

EXCEPT

SELECT Employee,SalaryPackage,Designation,CRR ,

              CASE WHEN CRR='2' THEN .05* SalaryPackage

                         WHEN CRR='1' THEN .1* SalaryPackage

                         WHEN CRR='1+' THEN .15* SalaryPackage  

              END AS Increment

 FROM Annualhike

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

SQL- Displaying all records from a table for last one year

Q. How to find all the records from a table for last one year ?

       A table named "Transactions" has the following transaction details.

Transactions

Suppose, we have to find all the records which has been transacted in the last one year. For this scenario we will make use of date field which is "DateOfTransaction" .The following below query in SQL Server will fetch the required results.

SELECT * FROM TRANSACTIONS 

WHERE DateOfTransaction > DATEADD(YYYY,-1,GETDATE())

Suppose we have to get total amount spent by each ID in last one year. The following below query will fetch the desired results.

SELECT ID,SUM(Amount) AS TotAmount FROM TRANSACTIONS 

WHERE DateOfTransaction > DATEADD(YYYY,-1,GETDATE())

GROUP BY ID

Similar scenario can be handled in ORACLE with slight changes in query.We will have to to use ADD_MONTHS instead of DATEADD and SYSDATE instead of GETDATE().

SELECT * FROM TRANSACTIONS
WHERE dateOftransaction > ADD_MONTHS(SYSDATE,-12);

SELECT ID,SUM(Amount) TotAmount FROM TRANSACTIONS
WHERE DateOfTransaction > ADD_MONTHS(SYSDATE,-12)
GROUP BY ID;