UNIX Interview Questions | ETL

---

UNIX Commands for ETL Developer/ Tester/ Support

---

• LIST 

1) Unix command to list files in descending order

ls -ltr

2) Unix command to list files based on file size

ls -ls

3) Unix command to list hidden files 

ls -la | grep ^[.]

---

• KILL

1) UNIX command to kill background jobs

KILL $!

---

• PRINT

1) UNIX command to print the first line of the  file

HEAD -1 filename.txt
or 
SED '2$' filename.txt

2) UNIX command to print the last line of the  file

TAIL -1 filename.txt
or 
SED -n '$p' filename.txt

3) UNIX command to print an Nth line of the  file

SED -n '<n> $p' filename.txt

<n> - number of line

4) UNIX command to print a word in reverse

echo 
| rev

5) UNIX command print the last word of a line

echo 'C for Car' | rev | CUT -f 1 d ' ' | rev

---

• REMOVE

1) UNIX command to remove the first line of the file 

SED '1d' filename.txt

2) UNIX command to remove the last  line of the file 

SED  -i '$d' filename.txt

3) UNIX command to remove Nth line of the  file

SED  -i '<n> $d' filename.txt

<n> - number of line

4) UNIX command to remove the empty line from the file 

GREP -v ^"$" filename.txt

5) UNIX command to cut Nth word of a line

 

CUT -f <n> -d ' '

<n> - number of line

---

• COUNT 

1) UNIX command to find the number of lines in the file

filename.txt | wc -l

2) UNIX command to find the length of the Nth line in the file

SED -n '<n> p' filename.txt | wc -L

<n> - number of line

---

• OTHERS

1) View non-printable words in the file

vi filename.txt 

In vi mode you can view the non-printable words in the file

2) Remove BOM characters

SED -i '1s/^\xEF\xBB\xBF//' filename.txt

---

Cognizant Interview Process for Experienced | 2021 Virtual Onboarding

 CTS Interview Process 2021

---

Average the process takes 3 - 4 weeks, based on the interview scheduled date.

---

• Number of Rounds: 3 

1. Phone Screening by HR
2. Technical Round 
3. HR Discussion

---

• Detailed Interview Process : 

1. HR will contact you screening and schedule for the interview if your profile suits.

2. HeadUp!  If you received your invite for a technical Interview. Please be ready for the interview even if HR didn't follow up. Anyway, you will receive a reminder call from HR at least an hour for your technical evaluation.

Tips: Keep calm and collected during your interview. And try to answer the questions. Be confident about topics mentioned in your resume.

And brush up on the basics of all skills mentioned in your resume.

3. You will get the results of your technical interview in a week or two. If you have passed then you get an email from HR to fill CIS (Candidate Information Sheet) and to send your updated resume.

Try to send this ASAP as your HR discussion is based on the information and please be accurate.

Don't panic If you have sent some details wrong, you can always resend the CIS with corrected details.

Cognizant will not send any rejection emails. If you didn't get any mail, don't lose hope and try again after 3 months.

4. From 2 to 3 days after sending the CIS form and resume, you will get a call from HR regarding salary negotiation. 

Specify all your demands to HR. They are friendly.

5. After that, you will invite to fill out the CIS information online, if you have done it will attending the technical interview.

6. Soon in a day or 2 you will receive your offer letter along with an email to create an external cognizant Microsoft account.

The offer letter release date may vary depending on technology and requirement. There is no constant time period.

7. Once you create your account, accept the offer.

8. And then in a week you will receive mail to upload documents for Background verification.

Background verification will take 2 weeks.

9. Fill all the pre-joining formality forms before the mentioned to avoid conflicts on generating your boarding pass.

Without a boarding pass will be difficult on joining date. Boarding pass generation is one of the biggest challenges in Cognizant Virtual Onboarding.

So make sure you complete all the pre-joining formalities before the deadline and try to join the Cognizant by the mentioned joining date without postponing.

All the best for your career!!

---

SQL | Snowflake | IFNULL vs NULLIF function

---

 DIFFERENCE BETWEEN IFNULL AND NULLIF FUNCTION

---

These SQL functions are compatible with mysql, SQL Server, Power BI, Big query.

IFNULL FUNCTION: If the in-parameter is NULL then it returns the default out-parameter.

Syntax : IFNULL (<in-parameter>,<out-parameter>)

SQL Snowflake IFNULL( ) function

NULLIF FUNCTION: If the first in-parameter is equal/same to the second in-parameter it returns NULL. If the first in-parameter is not equal/same to the second in-parameter it returns the first in-parameter.

Syntax : NULLIF (<in-parameter-1>,<in-parameter-2>)

SQLSnowflake NULLIF ( ) function

IFNULL( ) VS NULLIF( ) FUNCTION:

Features	IFNULL	NULLIF
Syntax	IFNULL (<in-parameter>, <out-parameter>)	NULLIF (<in-parameter-1>, <in-parameter-2>)
Example	[1] IFNULL(NULL,28) [2] IFNULL(100, 28)	[1] NULLIF(28,28) [2] NULLIF(100,28)
Number of IN parameter	1	2
Number of OUT parameter	1	0
If IN parameter is NULL	Returns out-parameter value O/p : [1] Returns 28	NA
If IN parameter is NOT NULL	Returns in-parameter value O/p : [2] Returns 100	NA
If IN parameter 1 is same/equal to IN parameter 2	NA	Returns NULL O/p :[1] Returns NULL
If IN parameter 1 is not same/equal to IN parameter 2	NA	Returns in-parameter-1 O/p :[2] Returns 100

SQL Snowflake NULLIF vs IFNULL function

---

Read More :

• Difference between WHERE and HAVING clause

---

• Difference between NVL and NVL2 function

---

Error 403 Out of call volume quota | API

ERROR 403 OUT OF CALL VOLUME QUOTA

Snowflake NVL vs NVL2 function | SQL

DIFFERENCE BETWEEN  NVL AND NVL2 FUNCTION

---

 This post explains the difference between NVL and NVL2 functions in Snowflake.

The NVL and NVL2 functions are also compatible with other databases like Teradata, Mysql, Postgresql, Oracle, PLSQL, Amazon Redshift.

---

NVL FUNCTION

The NVL function will return the default value if the in- parameter is NULL.

Syntax: NVL ( <in-param>,<out-param>)

• If the in-param is NULL then it will return the out-param value.
• If the in-param is NOT NULL then it will return the in-param value itself.

Difference between NVL and NVL2 function

NVL2 FUNCTION

The NVL2 function will return the first out-parameter if the in-parameter is NOT NULL and return the second parameter if the in-parameter is NULL.

Syntax: NVL2( <in-param>,<out-param-1>,<out-param-2>)

• If the in-param is NULL then it will return the out-param-1 value .
• If the in-param is NOT NULL then it will return the in-param-2 value itself.

Difference between NVL and NVL2 function

NVL VS NVL2 FUNCTION

Features	NVL	NVL2
Syntax	NVL ( <in-param>,<out-param>)	NVL2( <in-param> ,<out-param-1> ,<out-param-2>)
Example	[1] NVL(NULL,'It is NULL') [2] NVL('99','It is NULL')	[1] NVL2('99','It is Not NULL' ,'It is NULL')
Number of IN parameter	1	1
Number of OUT parameter	1	2
If IN parameter is NULL	Returns <out-param> i.e [1] 'It is NULL'	Returns <out-param-1>  i.e [1] 'It is Not NULL'
If IN parameter is NOT NULL	Returns  <in-param>  i.e [2] '99'	Returns <out-param-2>  i.e [1] 'It is NULL'

 

NVL vs NVL2 function | SQL

---

Read More :

• What does count 1 mean in SQL?

---

• How to include a single quote in a SQL query?

---

• Difference between WHERE and HAVING clause

---

• Create a table from an existing table

---

Difference between WHERE and HAVING clause | SQL

WHERE AND HAVING CLAUSE WITH EXAMPLE

This post explains the difference between WHERE and HAVING clauses and how to use them with example scenarios.

---

WHERE:

• WHERE clause is used to filter only the desired records in the table.
• Records that don't meet the condition are removed prior to the grouping operation.

HAVING:

• HAVING clause is used as a secondary filter. If the value is calculated in your query and the value is not readily available in your table then you need use the HAVING clause.
• This filter is applied after your aggregate functions are executed in order to meet the mentioned HAVING condition from your resultant.

For example, let's consider you have a data set with ticket sales details of a game by team.

Scenario 1 :

The resultant here will be each team’s sum of sales from every game that had sales greater than 50,000.

SELECT 
team, 
SUM(sales_amt ) 
FROM 
games 
WHERE 
sales_amt > 50000 
GROUP BY 
team;

Scenario 2 :

The resultant here will be only teams that have total sales of greater than 5,00,000.

SELECT 
team, 
SUM(sales_amt ) 
FROM 
games 
GROUP BY 
team
HAVING 
SUM(sales_amt ) > 500000;

Scenario 3 :

The resultant here will be only teams that have grossed more than 5,00,000 from games that did more than 50,000 in sales.

SELECT 
team, 
SUM(sales_amt ) 
FROM 
games 
WHERE 
sales_amt > 50000 
GROUP BY 
team
HAVING 
SUM(sales_amt ) > 500000;

WHERE VS HAVING:

Features	WHERE	HAVING
Syntax	WHERE condition;	GROUP BY columns HAVING condition;
Example	WHERE sales_amt > 50000;	GROUP BY team HAVING SUM(sales_amt ) > 500000;
What value can be used condition	Direct value from table	Derived  aggregate value from table
When to use WHERE/HAVING clause	To filter data based on values available in the table.	To filter data based on aggregate values derived from values available in the table

---

Read More:

• What does count 1 mean in SQL?

---

• How to include a single quote in a SQL query?

---

• Create a table from an existing table

---

• Snowflake NVL vs NVL2

---

Convert SQL query output into HTML table | Python

FORMAT SQL QUERY OUTPUT INTO AN HTML TABLE 

From my own experience with python SQL connector. I think this is one of the easiest way to format the output SQL query output into HTML  table code.

Steps to convert SQL query output  into HTML table code:

• Connect to the database using SQL connector
• Create a cursor to execute your SQL query and fetch the resultant 
• Execute the SQL query and use the below code to convert the output into an HTML table.

Convert  SQL query output into HTML table | Python

For example, I want to convert the above SQL query output into HTML code and I am using Snowflake Connector.

snowflake = snowflake.connector.connect(
  user=SF_USER,
  password=SF_PASSWORD,
  account=SF_ACCOUNT,
  warehouse=SF_WAREHOUSE,
  database=SF_DATABASE,
  schema=SF_SCHEMA
)    
snowflake_cur = snowflake.cursor()	

 sHtml=""
    
sql = """ 
            SELECT CustomerID, CustomerName FROM Customers ORDER BY CustomerName ASC;
          """
snowflake_cur.execute(sql)

sHtml=sHtml+"""</br><table order="1"><tr><th>CustomerID</th><th>CustomerName</th></tr>"""

for row in snowflake_cur.fetchall():
    sHtml=sHtml+"<tr><td>"+str(row[0])+"</td><td style=""text-align:center"" >"+str( "{:,}".format(row[1]))+"</td></tr>"

sHtml=sHtml+"</table></br>"

---

Read More :

• Connect to Postgres database using AWS Secrets Manager and psycopg2

---

Create a table from an existing table | SQL

CREATE A NEW TABLE FROM THE EXISTING TABLE  WITH DATA OR WITHOUT DATA ALONG WITH FEW COLUMNS OR ALL THE COLUMNS USING ONE TABLE OR MULTIPLE TABLES

These SQL queries will create the basic table and its fields but not indexes, constraints, or triggers as it is in the existing table.

---

CREATE AN EMPTY TABLE FROM AN EXISTING TABLE:

Syntax:

CREATE TABLE new_table 
  AS (SELECT * 
      FROM old_table WHERE 1=2); 

Example:

CREATE TABLE suppliers 
AS (SELECT * 
 FROM companies 
 WHERE 1=2);

---

CREATE A TABLE FROM AN EXISTING TABLE WITH DATA:

Syntax:

CREATE TABLE new_table 
  AS (SELECT * 
      FROM old_table); 

Example:

CREATE TABLE suppliers 
AS (SELECT * 
 FROM companies 
 WHERE id > 1000);

---

CREATE A TABLE WITH ONLY A FEW COLUMNS FROM AN EXISTING TABLE WITH DATA:

Syntax:

CREATE TABLE new_table 
AS (SELECT column_1, column2, ... column_n 
FROM old_table); 

Example:

CREATE TABLE suppliers 
AS (SELECT id, address, city, state, zip 
FROM companies 
WHERE id > 1000); 

---

CREATE A TABLE WITH ONLY A FEW COLUMNS FROM AN EXISTING MULTIPLE TABLES WITH DATA:

Syntax:

CREATE TABLE new_table 
  AS (SELECT column_1, column2, ... column_n 
      FROM old_table_1, old_table_2, ... old_table_n); 

Example:

CREATE TABLE suppliers 
AS (SELECT companies.id, companies.address, categories.cat_type 
FROM companies, categories 

WHERE companies.id = categories.id 
AND companies.id > 1000); 

---

Read More:

What does count 1 mean in SQL?

How to include a single quote in a SQL query?

---