Top 100 SQL Realtime scenarios-based interview questions.

 Q1) Write an query to print the salaries of employees above 3000 as high salary or low salary.

Ans select ename,sal,case when sal>3000 THEN 'High Salary'

ELSE 'Low Salary'

END AS salary_category

from emp;

Q2) Write a query to retreive the last five records from the employee table based on the empno.

Ans    select * from emp order by empno desc 

             fetch first 5 rows only;

select *

from

(

select * from emp

order by empno desc

)

where rownum<=5;

Note: In oracle, limit clause is not used. You can use FETCH first n rows or ROWNUM to achieve the result.

Q3) Write a query to fetch employees who earn more than the average salary.

Ans select * from emp where sal>(select avg(sal) from emp);

Q4) How do you find the second highest salary from the employee table.

Ans select max(sal) from emp where sal<(select max(sal) from emp);

Q5) How to find all the duplicate records in the employee table taking all the columns.

Ans select * from emp

group by empno,ename,job,mgr,hiredate,sal,comm,deptno

having count(*)>1;

Q6) Write the query to select only the even or odd rows from the table based on empno.

Ans  select * from emp where mod(empid,2)=0;

       select * from emp where mod(empid,2)=1;

To select even or odd rows from the emp table based on empid, MOD function is used to check the empid is divisible by 2. If empid is divisible by 2, then it is even rows otherwise odd rows of the table emp.

Note:Here,empid should be the numeric field. If the empid is not sequential or have gaps then empid will be directly selected as even or odd based on the divisibility by 2.

Q7) Write a query to select the top 2 salaries from each department in the employee table.

Ans.  select * from(

select ename,deptno,sal,

row_number() over(partition by deptno order by sal desc) as rank

from emp

)ranked

where rank<=2;

Q8) How to update multiple records of empid using procedure in the employee table?

Ans. Begin

update emp set empid=1 where empno=7369;

update emp set empid=2 where empno=7499;

update emp set empid=3 where empno=7521;

update emp set empid=4 where empno=7566;

update emp set empid=5 where empno=7654;

update emp set empid=6 where empno=7698;

update emp set empid=7 where empno=7782;

update emp set empid=8 where empno=7788;

update emp set empid=9 where empno=7839;

update emp set empid=10 where empno=7844;

update emp set empid=11 where empno=7876;

update emp set empid=12 where empno=7900;

update emp set empid=13 where empno=7902;

update emp set empid=14 where empno=7934;

end;

Q9) How do you convert seconds into time format?

Ans There is no direct built in function like SEC_TO_TIME.You can convert seconds into the HH:MI:SS time format by using the interval arithmetic.TO_CHAR with a DATE datatype is used.

Using NUMTODSINTERVAL:

SELECT NUMTODSINTERVAL(3661,'SECOND') AS time_format from dual;

Explanation:

NUMTODSINTERVAL(n,'unit') : It is an oracle built in function that converts the number into an INTERVAL DAY TO SECOND data type. It is used when you want to represent the duration of time not the date.

3661:The number of seconds which we are going to convert.

3661 seconds=1 hour,1 minute and 1 second.

SECOND: It tells the number in seconds.

FROM dual: It is a special one row and one column used for selecting expressions without needing an actual table.

SELECT TO_CHAR(TRUNC(SYSDATE) + (3661 /864000),'HH24:MI:SS') AS time_format from dual;

Explanation:

3661/86400:It converts second to a fraction of a day.

TRUNC(SYSDATE): It ensures that starting from midnight to avoid current time.

TO_CHAR(...,'HH24:MI:SS'): It extracts the formatted time.

Q10) Write the query to display the number of weekends in the current month?

Ans  select count(*) as number_of_weekend_days_month

FROM

(

select TRUNC(SYSDATE, 'MM') + LEVEL-1 AS day

FROM dual

CONNECT BY LEVEL <=TO_CHAR(LAST_DAY(SYSDATE),'DD')

)

WHERE TO_CHAR(day,'DY','NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT','SUN');

Explanation:

TRUNC(SYSDATE,'MM'): It will display the first day of the month.

LAST_DAY(SYSDATE): It will display the last day of the current month.

LEVEL: It will generate a list of days from 1 to the number of days in the month.

TO_CHAR (...,'DY'): It will get the 3-letter abbreviation for the day i.e SAT & SUN.

Q11) Write the query to display the common two records in the two tables which cannot be joined?

Ans If the two tables cannot be joined directly which means that they don't share the direct foreign key or have no referential integrity constraints.

Matching based on common column email

create table tableA(email varchar2(50),name varchar(20));

INSERT ALL

  INTO tableA(email, name) VALUES ('rahul123@internettutors.com', 'Rahul')

  INTO tableA(email, name) VALUES ('ram34@internettutors.com', 'Ram')

  INTO tableA(email, name) VALUES ('Mohan123@internettutors.com', 'Ganesh')

SELECT * FROM dual;

create table tableB(email varchar2(50),age number);

INSERT ALL

  INTO tableB(email, name) VALUES ('rahul123@internettutors.com', '35')

  INTO tableB(email, name) VALUES ('ram34@internettutors.com', '30')

  INTO tableB(email, name) VALUES ('Mohan123@internettutors.com', '40')

SELECT * FROM dual;

select a.email from tableA a where a.email IN(select b.email from tableB b);

select a.email from tableA a where exists(select 1 from tableB b where b.email=a.email);

INTERSECT METHOD IS TO FIND MATCHING ROWS OR COLUMNS:

select email

from tableA

INTERSECT

select email

from tableB;

Q12) Write the query to display the third last record in the table?

Ans. In order to display the third last record in the table, you can use the ROWNUM or ROW_NUMBER