SOLUTIONS -INTERVIEW QUESTIONS
1. Difference between CHAR and VARCHAR?
CHAR – It is a fixed length datatype.
Memory will be wasted
It stores only 255 characters
VARCHAR is a variable length datatype.
Memory wastage is not done
It stores 65,535 characters.
Performance is better in Char than Varchar
2. Difference between Drop, Delete and Truncate
DROP: It removes complete table from database
Delete: It deletes particular row data from table according to
the conditions provided.
Truncate: It removes complete data from the database but
structure of the table remains same
3. How to add new column in the already created table
Sol: Alter table tbname
Add column colname
4. What is Range and Membership operators
Sol: Range operator also called as between operator Used to
retrieve the data within a specified range of values
Membership operator also called as IN operator
Used to retrieve the data that matches a list of values
5. How to display top 50 sales?
Sol: Select * from tbname
Order by colname desc
limit 50
6. By using PK defined column, fetch the records from 5 to 8
Sol: We can solve this by using the Between operator and LIMIT
clause.
By using Between we can write as
Select * from tablename
Where column name between 5 and 8
By using LIMIT clause
In any column if we define Primary Key, by default
Cluster index will be created.
Cluster index stores the data in sorted manner i.e sequential
order
So, we can expect that in ID column our data will be from 1 to
10 in sequence. So, we can use limit as
Select * from tablename
Limit 4,4
By writing limit 4,4 – it skips 1st 4 rows and display next 4 rows
i.e from id 5,6,7,8.
NOTE: To understanding usage of limit clause I gave this
example but again if we have all the values from 1 to 10 then
only limit works
Suppose, in that column though we declared PK, because of
Cluster index though the data stored in sequential order if I
don’t have any value in that data. Eg; in ID column if I have data
like this
1,2,3,5,6,7,8,9,
i.e 4 is not there in ID column.
Here also limit skips 1st 4 records and displays next 4 records
So, I will get data from 6 to 9 here not 5 to 8. So, we need to
understand the concept here
Just to check your knowledge on indexes interviewers may trick
you with this kind of questions
Or else if we only try to talk about these 2 ways in interview,
they will get impressed.
7. By using PK defined column, display alternative rows from any
table.
Again, PK column in the sense by default
Cluster index will be created in that primary key defined column
So, data will be stored in sequential order
Here to display alternative rows, we use MOD function
Alternative rows in the sense
either i can display odd alternate rows i.e 1,3,5,7
or even alternate rows - 2,4,6,8
MOD() – displays the remainder.
i.e mod(4,2) – remainder will be 0
mod(5,2) – remainder will be 1
so, I can say, whenever my remainder is 0, I get will even
numbers and if my remainder is 1, I will get odd numbers.
Syntax will be:
SELECT * FROM tablename
WHERE MOD(id, 2) = 1 - displays odd alternatives
SELECT * FROM tablename
WHERE MOD(id, 2) = 0 - it displays even alternatives
8. What is NOW()
Now() – displays current date and time
9. Display the data from table in which the last name column
containing ‘ra’ at any position
By using LIKE operator:
Select * from tablename
Where Lastname like '%ra%'
By using REGULAR EXPRESSIONS:
Select * from tablename
Where Lastname regexp 'ra'
10.Display the records from the city table with country containing
a total of 7 characters
Select * from tablename
Where country like ‘_______’
i.e we use 7 underscores here.
In like operator, underscore (_) is a single character
11.What is ambiguous Error
In joins, whenever we want to display a common column name, if
we use common column name normally SQL will get confused
about from which table it should display that common column.
And it throws ambiguous error.
To avoid this error, we use either table name or alias name before
common column name
12. Difference between where clause and Having clause
Where clause filters data in row level
Having clause filters data whenever we use aggregate functions or
group by clauses.
13. What is the major difference between Correlated sub query
and non-correlated sub query
In non-correlated sub queries we can execute sub query/inner
query separately, we will get output
but in correlated we cannot execute sub query /inner query
separately. this is the main difference
14. How can you see all indexes defined for a table
SHOW INDEX FROM <tablename>;
15. What is difference between Rank and Dense Rank
Rank – If we use Rank function, it will skip the ranks
Dense rank - It will not skip the ranks
Syn:
select *, dense_rank() over(order by score desc) from tbl_score
select *, rank() over(order by score desc) from tbl_score
16. What is Query Optimization. How we will achieve it
Query optimization is the process of finding the most efficient way
to execute a query against a database.
Query optimization techniques help SQL to improve performance
and speed up the time of displaying the results/ output.
One of the best techniques of Query optimization is using Index
On tables.
By using indexes, i.e cluster index and non – cluster index sorts the
data and stores the data in sequential order by using which SQL
can able to execute the queries faster and improves performance
also.
17. Difference between Temporary table and CTE?
Temporary table – The scope of the temporary table existence is
only till the session is opened, once the workbench is closed it
gets deleted.
CTE - The scope of the CTE is, it has the existence only for that
particular query
