Register Now

Login

Lost Password

Lost your password? Please enter your email address. You will receive a link and will create a new password via email.

Login

Register Now

It will take less than 1 minute to register for lifetime. Bonus Tip - We don't send OTP to your email id Make Sure to use your own email id for free books and giveaways

10 Questions, 10 Minutes – 4/100

1.How would you convert a string into an int in Python?

If a string contains only numerical characters, you can convert it into an integer using the int() function.

>>> int(‘227’) 227

Let’s check the types: 1. >>> type(‘227’)

<class ‘str’>
1. >>> type(int(‘227’))

<class ‘int’>

2.What is difference between unique and distinct?(90% asked Advanced SQL Interview Questions )

There is no difference between unique and distinct keywords apart from one difference.unique is applied before insertion and retrival.It consists of non duplicate values.if unique constraint is given it does not take duplicate values.distinct is used in retrieval it gives the suppressed row(ex if two rows are same it will show single row and non duplicate row) therefore distinct is the combination of suppressed duplicate and non duplicate rows.Specify DISTINCT or UNIQUE if you want Oracle to return only one copy of each set of duplicate rows selected (these two keywords are synonymous). Duplicate rows are those with matching values for each expression in the select list. 


3.What will be the output of following Query?

Query :
select case when null=null then ‘Amit’ Else ‘Pradnya’ from Table_Name;

In SQL null value is not equal to itself.So null=null is false and the output of above query is ‘Pradnya’.

4. Which are different Set operators in SQL?(100% asked Advanced SQL Interview Questions )

Set operators are nothing but the operators which are used to connect two tables and fetch the records from the two tables.We need to follow one condition that the table set 1 columns and table set 2 columns are same and its datatype must be same.SQL Set Operators combines the result of 2 queries or components on to the single result.

Following are Set Operators in SQL:

1. Union

2. Unionall
3. Intersect
4. Minus

5. How to select first 5 characters from First name in Employee table?

Oracle Query:
Select Substr(First_name,0,5) from Employee;

MS SQL:
Select Substr(First_name,1,5) from Employee;

MySQL:
Select Substr(First_name,1,5) from Employee;

6. What will be the output of following query? Query :Select * from (select ‘a’ union all select ‘b’) Q;

It will throw error because no values are selected in Subquery.

7. Explain co-related sub-query with example.

Fetch the Employees who have not assigned a single department.

Select * from Employee E where Not exist

(Select Department_no From Department D where E.Employee_id=D.Employee_ID);

Execution of query:

Step 1:

Select * from Employee E ;

It will fetch the all employees

Step 2:

The First Record of the Employee second query is executed and output is given to first query.

(Select Department_no From Department D where E.Employee_id=D.Employee_ID);

Step 3:
Step 2 is repeated until and unless all output is been fetched.

8. What is difference between NVL,NVL2 and Nullif?

1.NVL :

NVL function substitutes a value when a null value is encountered.

2.NVL2 :

NVL2 substitutes a value when a null value is encountered as well as when a non-null value is encountered.

3.NULLIF:

NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.

9. What is Index?What is use of index in SQL?

Index is optional structure associated with the table which may or may not improve the performance of Query.In simple words suppose we want to search the topic in to book we go to index page of that book and search the topic which we want.Just like that to search the values from the table when indexing is there you need not use the full table scan.

Indexes are used to improve the performance of the query.

10. What is the difference between Having and Where clause?

Where clause is used to fetch data from a database that specifies particular criteria whereas a Having clause is used along with ‘GROUP BY’ to fetch data that meets particular criteria specified by the Aggregate functions. Where clause cannot be used with Aggregate functions, but the Having clause can.

About TheDataMonkNewbie

I am the Co-Founder of The Data Monk. I have a total of 4+ years of analytics experience with 3+ years at Mu Sigma and 1 year at OYO. I am an active trader and a logically sarcastic idiot :)

Follow Me

Leave a reply