DB - Interview Questions

DELETE DUPLICATE RECORD:

DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2

WHERE 

e1.NAME = e2.NAME

AND

e1.ID > e2.ID;

FIND THE 3 HIGHEST VALUE:

WITH CTE AS

(

SELECT 

NAME,SALARY,RN=ROW_NUMBER() OVER (ORDER BY SALARY DESC) 

FROM EMPLOYEE

)

SELECT NAME,SALARY FROM CTE WHERE RN = 3; 

FIND THE 2 HIGHEST VALUE:

SELECT 

MAX(SALARY) 

FROM EMPLOYEE

WHERE

SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)

GET EVEN/ODD ROWS: 

SELECT * FROM 

(SELECT *,ROW_NUMBER() OVER (ORDER BY id) AS ROW_ID FROM STUDENT)

WHERE ROW_ID %2 = 0

Postgresql - Extract YEAR/ MONTH from TIME STAMP

 SELECT 

EXTRACT(YEAR FROM created),

EXTRACT(MONTH FROM created),

COUNT(*) 

FROM 

schemaname.tablename

GROUP BY 

EXTRACT(YEAR FROM created),

EXTRACT(MONTH FROM created) 

ORDER BY 1,2

Excel - Subtract two time stamp columns/rows

Formula: 

=TEXT(A70-A69, "h:mm:ss")

Other samples,

Calculate hours between two times: 

=TEXT(B2-A2, "h")

Return hours and minutes between 2 times: 

=TEXT(B2-A2, "h:mm")

Return hours, minutes and seconds between 2 times: 

=TEXT(B2-A2, "h:mm:ss")

Pentaho - Regex to find a word in a line and replace the full line

 ^.*welcome.*$

Pentaho - Java Script example for if..else

 var out_record_count;

 if (start_pos == -1)

{

out_record_count=0

}

else if (end_pos == -1 )

{

out_record_count=0

}

else 

{

out_record_count=text.substring(start_pos+3, end_pos)

}

Pentaho Spoon - Unable to create the database cache

Couldn't read the database cache

org.pentaho.di.core.exception.KettleFileException 

The solution was to delete the db.cache* File in the user home dir

Linux: 

/home/abc/.kettle

db.cache-8.0.1-stable

Windows:

C:\Users\abc\.kettle

db.cache-8.3.0.0-371

Postgresql - Between two timestamp/date

SELECT * FROM schemaname.tablename

WHERE columnname between '2022-12-14 00:00:00' AND '2022-12-19 14:00:03' ORDER BY 1 DESC

NOTE: Includes both the dates 2022-12-14 & 2022-12-19