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.
