SQL-Interview-Questions - Open Source Guru Ji

These below following top questions were taken from various interview and explained the how to answer to interviewer. SQL is basic Structure programing and easy to understand and Answer to all questions.

Table of Contents

SQL-Interview-Questions.

Q1. Write the SQL statement to find the 2nd & 3rd maximum salary of an employee.

Answer:

Please find the below DB table:

Select first name and salary from employee table.

select first_name, salary from employees;

You can see three results of employee table with column first_name and Salary.

S.NO	FIRST_NAME	SALARY
1	Steven	24000
2	Neena	17000
3	Lex	17000
4	John	14000

Top Salary Result Query Using Dense rank.

select * from (select first_name, salary, dense_rank() over (order by salary desc) top_rank from employees) 
where top_rank in (2,3);

S.NO	FIRST_NAME	SALARY	TOP_RANK
1	Neena	17000	2
2	Lex	17000	2
3	John	14000	3

You can watch this videos to understand more about it.

Q2. There are a person table which has Name and Gender column. Please look at below table and it’s Column. Below gender data is wrongly update where Male should be Female and Female should be Male. Could you please write single SQL query to correct the gender data.

Answer:

Table – Person.

S.NO	Name	Gender
1	Pappu	Female
2	Sharmila	Male
3	Padma	Male
4	Narishima	Female
5	Kamini	Male

Answer :

update person set gender=case gender when 'Male' then 'Female' when 'Female' then 'Male' else gender end;

Demo:

select * from person;

	NAME	GENDER
1	Pappu	Female
2	Sharmila	Male
3	Padma	Male
4	Narishma	Female
5	Kamini	Male

Once you will update the below update SQL script . The all the result set of female will be become male and male will be become female.

update person set gender=case gender when ‘Male’ then ‘Female’ when ‘Female’ then ‘Male’ else gender end;

select * from person;

	NAME	GENDER
1	Pappu	Male
2	Sharmila	Female
3	Padma	Female
4	Narishma	Male
5	Kamini	Female

You can watch this videos to understand more about it.

Q3. Explain the difference between CHAR, VARCHAR and VARCHAR2 datatype in SQL?

Answer:

Char, Varchar and Varchar2 all three datatype are character datatype and those used to store character values.

Char : it’s store maximum length of 2000 bytes of characters. This datatype store normal characters and alphanumeric characters. This is a fixed length datatype, mainly its lead to memory wastage because the length is fixed and can not change during execution. if size will not declare during execution in program declaration section then oracle will by default assign the 1 byte. Example – person_name char(20), suppose if person name vicky is passing through this variable which is only a 5 character then such case 15 padded value in right side which is leading a memory leakage/wastage.

VARCHAR: it’s a dynamic data type, the value can be change during execution. The varchar can store maximum 4000 bytes of character strings. this data type is not recommended by Oracle to use in written a code. This datatype will be change in future. it’s mainly a ANSI standard. This datatype store normal characters and alphanumeric characters.

VARCHAR2: There are no difference in between Var char and Varchar2 rather than one is ANSI standard and another is Oracle. It’s also called dynamic data type. as this is dynamic, there are no leakage of memory.This datatype store normal characters and alphanumeric characters. oracle always recommended to use varchar2.