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

Problem Statement:-

Student Table has three columns Student_Name, Total_Marks and Year. User has to write a SQL query to display Student_Name, Total_Marks, Year,  Prev_Yr_Marks for those whose Total_Marks are greater than or equal to the previous year   

Student Table

OUTPUT Table

SOLUTION

SELECT Student_Name,Total_Marks,Year,Prev_Yr_Marks

FROM

(

SELECT Student_Name,Year,Total_Marks,Prev_Yr_Marks ,

CASE WHEN Total_Marks > = Prev_Yr_Marks Then 1 Else 0 END as Flag

FROM

(

SELECT Student_Name,Year,Total_Marks,

LAG(Total_Marks) OVER(PARTITION BY Student_Name ORDER BY Year )

AS Prev_Yr_Marks

FROM Student)A

) B

WHERE Flag=1

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

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

Problem Statement:-

Given below table Emp as Input which has two columns ‘Group’ and ‘Sequence’, Write a SQL query to find the maximum and minimum values of continuous ‘Sequence’ in each ‘Group’

Emp Table

OUTPUT

SOLUTION :

SELECT [Group],

MIN([Sequence]) As Min_Seq,

MAX([Sequence]) As Max_Seq

FROM

(

SELECT [Group],

[Sequence],

[Sequence] - ROW_NUMBER() OVER(Partition BY [Group] ORDER BY [Sequence]) as [Split]

From Emp

) A

GROUP BY [Group],[Split]

ORDER BY [Group]

Above solution has been explained in below video

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

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

Problem Statement:-

Transatcion_tbl Table has four columns CustID, TranID, TranAmt, and  TranDate. User has to display all these fields along with maximum TranAmt for each CustID and ratio of TranAmt and maximum TranAmt for each transaction.

Transaction_Tbl

Output

Solution 1: By using Subquery

SELECT A.CustID,TranID,A.TranAmt,MaxTranAmt,(TranAmt/MaxTranAmt) AS Ratio,TranDate
FROM Transaction_Tbl A
INNER JOIN(SELECT CustID, Max(TranAmt) As MaxTranAmt FROM Transaction_Tbl
GROUP BY CustID) B
ON A.CustID=B.CustID

Solution 2: By using CTE (Common Table Expression )

WITH CTE (CustID, TranID, TranAmt) AS(SELECT CustID, TranID, TranAmt FROM Transaction_Tbl
),CTE_MaxTran(CustID, MaxTranAmt) AS(SELECT CustID, Max(TranAmt) As MaxTranAmt FROM Transaction_Tbl
GROUP BY CustID
)SELECT A.TranID,A.TranAmt,MaxTranAmt,(TranAmt/MaxTranAmt) AS Ratio
FROM CTE A
INNER JOIN CTE_MaxTran B
ON A.CustID=B.CustID

Above solution has been explained in below video.

Please do like , comment , share and subscribe my youtube channel

UNIX - Basic UNIX commands with Examples

Below are the basic UNIX commands which are frequently used in ETL Testing and are often asked in interviews :-

1.)  ls
ls command is used for listing of files in a directory.

Example:

2.) cat
cat command is used for displaying the contents in a file.

Example:

3.) cd
cd command is used for changing from one directory to another directory.

Example:

4.) touch
touch command is used for creating zero byte file or empty file.

Example:

5.) cp
cp command  is used to copy the contents of source file into target file.

Example:

6.) mv
mv command is used to move files from one directory to another directory. It is also used for renaming files

Example:

7.) rm
rm command is used to remove or delete files.

Example:

8.) mkdir
mkdir command is used to create directories for organizing files.

Example:

9.) head
head command is used to print first n number of lines from the file.  By default, it displays first 10 lines from each file.

Example:

10.) tail
tail command is used to print last n number of lines from the file.  By default, it displays last 10 lines from each file.

Example:

11.) wc
wc command is used to find number of lines, words and characters in a file.

Example:

12.) grep
grep command print lines in files that match patterns.

Example:

13.) pwd
pwd command prints current working directory

Example:

14.) chmod
chmod command is used to change the permission of file or directory.

Example:

15.) man
man command displays the manual page for a given command.

Example: man ls