SQL Interview Questions - 1

Q.1: What is NVL function?

Answer: NVL function is used to replace null value with another value.

NVL(exp1,exp2)

Q.2: What is the Cartesian product?

Answer: A Cartesian product is the result of joining every row in one table with every row in another table. This occurs when there is no WHERE clause to restrict rows. (Cross Join)

Q.3: Delete the all duplicate records from below table data-

T1:

C1
1
2
2
3
3
3
4
4
4
4

 

Answer: delete from T1 where rowid not in (select min(rowid) from T1 group by C1);

Q.4: What is LIST_AGG() function?

Answer:

Syntax: LIST_AGG(exp1 ,’[delimiter]’) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

Example: select country, LISTAGG(first_name, ', ') within group (order by first_name) as Name_List from customer group by country order by country;

Definition 1: LISTAGG() function is used to transform the data from multiple rows into single list of values separated by a specified delimiter.

 

Definition 2: LISTAGG function is used to aggregate a set of string values within a group into a single string by appending the string-expression values based on the order that's specified in the 'WITHIN GROUP' clause. As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.

Q.5: What is the use of LEAD() and LAG() function?

Answer: Oracle LEAD() is an analytic function that allows you to access the following row from the current row without using a self-join.

Syntax:

LEAD(expression [, offset ] [, default ])

OVER (

       [ query_partition_clause ]

       order_by_clause

)

 

Oracle LAG() is an analytic function that allows you to access the row at a given offset prior to the current row without using a self-join.

Syntax:

LAG(expression [, offset ] [, default ])

OVER (

            [ query_partition_clause ]

            order_by_clause

)

Q.6: How to store PDF file in Database table?

Answer: To store a PDF file in a database table, you typically use a BLOB (Binary Large Object) column, which is designed to hold binary data like images, documents, or multimedia files.