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

Pentaho - JavaScript to find string position & find substring

var text = Field_000;

var start_pos = text.indexOf(" W=");

var end_pos = text.indexOf(", U=");

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

PENATHO - JOB EXECUTION SHELL COMMAND & LOG FILE WRITE ENABLE, LOCATION

 /home/abc/data-integration_8_1/kitchen.sh -file="/home/abc/myfolder/j_sample.kjb" –level:Basic -logfile:/home/abc/myfolder/Logfiles/j_sample_$(date +"%Y%m%d_%H%M%s").log

Postgresql - Add/ Subtract Hours/Minutes from Date/ Timestamp

SELECT now() - interval '5 minutes';

SELECT now() - interval '36 hours';

Pentaho job execution shell command & with log file enable option

/home/dev/data-integration_8_1/kitchen.sh -file="/home/dev/abc/j_dataload.kjb" –level:Basic -logfile:/home/dev/abc/Logfiles/j_dataload_$(date +"%Y%m%d_%H%M%s").log

MySQL find duplicate records

#1

SELECT offset, COUNT(offset)  

FROM log 

GROUP BY offset  

HAVING COUNT(offset) > 1;  

#2

SELECT A.id, A.ts, CONVERT(TIMEDIFF (B.ts,A.ts),CHAR) AS timedifference,a.offset,a.input_offset

FROM log A INNER JOIN log B ON B.id = (A.id + 1)

ORDER BY TIMEDIFF (B.ts,A.ts) DESC 

DISCARD MILLISECOND PART FROM TIMESTAMP

 SELECT now()::timestamp(0);

Create Index

 CREATE INDEX sync_idx_abc ON schemaname.tablename(columnname);

Drop Not Null Constraint

 ALTER TABLE schemaname.tablename ALTER COLUMN abc DROP NOT NULL;

Text To Timestamp Conversion

 TO_TIMESTAMP('1999-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')

Get the record count from all tables in a schema

select table_schema, 

       table_name, 

       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count

from (

  select table_name, table_schema, 

         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count

  from information_schema.tables

  where table_schema = 'test'

AND table_name IN ('abc') --<< change here for the schema you want

) t order by 3 DESC

---------------------------------------------

APPROACH 2 with WHERE CONDITION

---------------------------------------------

select table_schema, 

       table_name, 

       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count

from (

  select table_name, table_schema, 

         query_to_xml(format('select count(*) as cnt from %I.%I  WHERE updated > ''2022-10-01 00:00:00''', table_schema, table_name), false, true, '') as xml_count

  from information_schema.tables

  where table_schema = 'test'

AND table_name NOT IN ('abc')

AND table_name NOT LIKE ('ad_%') --<< change here for the schema you want

) t order by 3 DESC

Tuple

 

#Sample Tuple

thistuple = ("apple", "banana", "cherry")

print(thistuple)

('apple', 'banana', 'cherry')

#Create Tuple With One Item

#To create a tuple with only one item, you have to add a comma after the item, otherwise Python will not recognize it as a tuple.

​

thistuple = ("apple",)

print(type(thistuple))

​

#NOT a tuple

thistuple = ("apple")

print(type(thistuple)) 

<class 'tuple'>
<class 'str'>

#Change Tuple Values

#Convert the tuple into a list to be able to change it

x = ("apple", "banana", "cherry")

y = list(x)

y[1] = "kiwi"

x = tuple(y)

​

print(x) 

​

#Add Items

thistuple = ("apple", "banana", "cherry")

y = list(thistuple)

y.append("orange")

thistuple = tuple(y)

​

#Another Way

thistuple = ("apple", "banana", "cherry")

y = ("orange",)

thistuple += y

​

print(thistuple)

​

#Remove Items

thistuple = ("apple", "banana", "cherry")

y = list(thistuple)

y.remove("apple")

thistuple = tuple(y)

​

#The del keyword can delete the tuple completely

thistuple = ("apple", "banana", "cherry")

del thistuple

print(thistuple)

('apple', 'kiwi', 'cherry')
('apple', 'banana', 'cherry', 'orange')

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In [2], line 32
     30 thistuple = ("apple", "banana", "cherry")
     31 del thistuple
---> 32 print(thistuple)

NameError: name 'thistuple' is not defined

#Unpack Tuples

fruits = ("apple", "banana", "cherry")

​

(green, yellow, red) = fruits

​

print("green = ",green)

print("yellow = ",yellow)

print('red = ',red)

​

#Using Asterisk*

#If the number of variables is less than the number of values, you can add an * to the variable name and the values will be assigned to the variable as a list

fruits = ("apple", "banana", "cherry", "strawberry", "raspberry")

(green, yellow, *red) = fruits

print("Approach 1 : ",green)

print("Approach 1 : ",yellow)

print("Approach 1 : ",red)

​

fruits = ("apple", "mango", "papaya", "pineapple", "cherry")

(green, *tropic, red) = fruits

print("Approach 2 : ",green)

print("Approach 2 : ",tropic)

print("Approach 2 : ",red)

green =  apple
yellow =  banana
red =  cherry
Approach 1 :  apple
Approach 1 :  banana
Approach 1 :  ['cherry', 'strawberry', 'raspberry']
Approach 2 :  apple
Approach 2 :  ['mango', 'papaya', 'pineapple']
Approach 2 :  cherry

#Count

thistuple = (1, 3, 7, 8, 7, 5, 4, 6, 8, 5)

​

x = thistuple.count(5)

​

print(x) 

2

List

#Sample 

thislist = ["apple", "banana", "cherry"]

print("MyList : ",thislist)

​

thislist = ["apple", "banana", "cherry", "apple", "cherry"]

print(thislist)

MyList :  ['apple', 'banana', 'cherry']
['apple', 'banana', 'cherry', 'apple', 'cherry']

#Length of the list

thislist = ["apple", "banana", "cherry"]

print(len(thislist))

3

#Access List Items

thislist = ["apple", "banana", "cherry"]

print(thislist[1])

thislist = ["apple", "banana", "cherry", "orange", "kiwi", "melon", "mango"]

print(thislist[2:5])

thislist = ["apple", "banana", "cherry", "orange", "kiwi", "melon", "mango"]

print(thislist[:4])

thislist = ["apple", "banana", "cherry", "orange", "kiwi", "melon", "mango"]

print(thislist[2:])

banana
['cherry', 'orange', 'kiwi']
['apple', 'banana', 'cherry', 'orange']
['cherry', 'orange', 'kiwi', 'melon', 'mango']

#Check if Item Exists

thislist = ["apple", "banana", "cherry"]

if "apple" in thislist:

  print("Yes, 'apple' is in the fruits list") 

Yes, 'apple' is in the fruits list

#Change Item Value

thislist = ["apple", "banana", "cherry"]

thislist[1] = "blackcurrant"

print(thislist)

['apple', 'blackcurrant', 'cherry']

#Append Items

#To add an item to the end of the list, use the append() method

thislist = ["apple", "banana", "cherry"]

thislist.append("orange")

print(thislist)

['apple', 'banana', 'cherry', 'orange']

#Insert Items

thislist = ["apple", "banana", "cherry"]

thislist.insert(1, "orange")

print(thislist)

['apple', 'orange', 'banana', 'cherry']

#Extend List

#To append elements from another list to the current list, use the extend() method

thislist = ["apple", "banana", "cherry"]

tropical = ["mango", "pineapple", "papaya"]

thislist.extend(tropical)

print(thislist)

['apple', 'banana', 'cherry', 'mango', 'pineapple', 'papaya']

#Remove List Items

thislist = ["apple", "banana", "cherry"]

thislist.remove("banana")

print(thislist)

['apple', 'cherry']

#Remove Specified Index

#The pop() method removes the specified index

thislist = ["apple", "banana", "cherry"]

thislist.pop(1)

print(thislist)

['apple', 'cherry']

#Remove the last item

thislist = ["apple", "banana", "cherry"]

thislist.pop()

print(thislist)

['apple', 'banana']

#Delete the entire list

thislist = ["apple", "banana", "cherry"]

del thislist 

#Clear the List

#The clear() method empties the list.

#The list still remains, but it has no content.

thislist = ["apple", "banana", "cherry"]

thislist.clear()

print(thislist)

[]

#Loop Through a List

#You can loop through the list items by using a for loop

thislist = ["apple", "banana", "cherry"]

for x in thislist:

  print(x) 

apple
banana
cherry

#Loop Through the Index Numbers

#You can also loop through the list items by referring to their index number.

#Use the range() and len() functions to create a suitable iterable.

thislist = ["apple", "banana", "cherry"]

for i in range(len(thislist)):

  print(thislist[i]) 

apple
banana
cherry

#Using a While Loop

thislist = ["apple", "banana", "cherry"]

i = 0

while i < len(thislist):

  print(thislist[i])

  i = i + 1

apple
banana
cherry

#List Comprehension

fruits = ["apple", "banana", "cherry", "kiwi", "mango"]

newlist = []

​

for x in fruits:

  if "a" in x:

    newlist.append(x)

​

print(newlist) 

['apple', 'banana', 'mango']

#Sort List

thislist = ["orange", "mango", "kiwi", "pineapple", "banana"]

thislist.sort()

print(thislist)

​

thislist = [100, 50, 65, 82, 23]

thislist.sort()

print(thislist)

​

thislist = ["orange", "mango", "kiwi", "pineapple", "banana"]

thislist.sort(reverse = True)

print(thislist)

​

thislist = [100, 50, 65, 82, 23]

thislist.sort(reverse = True)

print(thislist)

['banana', 'kiwi', 'mango', 'orange', 'pineapple']
[23, 50, 65, 82, 100]
['pineapple', 'orange', 'mango', 'kiwi', 'banana']
[100, 82, 65, 50, 23]

#Customize Sort Function

def myfunc(n):

  return abs(n - 50)

​

thislist = [100, 50, 65, 82, 23]

thislist.sort(key = myfunc)

print(thislist)

[50, 65, 23, 82, 100]

#Copy List

thislist = ["apple", "banana", "cherry"]

mylist = thislist.copy()

print(mylist)

​

#Another Way

thislist = ["apple", "banana", "cherry"]

mylist = list(thislist)

print(mylist)

['apple', 'banana', 'cherry']
['apple', 'banana', 'cherry']

#List Count

fruits = ['apple', 'banana', 'cherry']

x = fruits.count("cherry")

print(x)

points = [1, 4, 2, 9, 7, 8, 9, 3, 1]

x = points.count(9)

print(x)

1
2