Advanced SQL Injection Interview Questions and Mitigation with Example
Q.1- How we can update gender column data from 'M' to 'F' and 'F' to 'M' in emp table?
Ans:- By using decode or case function
UPDATE EMP SET gender=DECODE(gender,'M','F','F','M');
OR
UPDATE EMP SET gender = CASE WHEN gender='M' THEN 'F' ELSE 'F' END;
Q.2-How we can get 2nd highest salary without using Analytical Function?
Ans:-select max(sal) from emp where sal<>(select max(sal) from emp);
Q.3-How we can get the employee details of 2nd highest salary without using Analytical Function ?
Ans:- select * from emp where sal=(select max(sal) from emp
where sal<>(select max(sal) from emp));
Q.4- Find the employee details whose salary is greater than avg salary of his department ?
Ans:-select * from emp a where a.sal>(select avg(sal) from emp b where a.deptno=b.deptno);
Q.5- Find the employee details whose salary is greater than avg salary of employees with same job ?
Ans:-select * from emp a where a.sal>(select avg(sal) from emp b where a.job=b.job) ;
Q.6- Write a query for deleting duplicate records on the basis of empno & ename ?
Ans:-delete from emp where rowid not in (select max(rowid) from emp
group by empno,ename) ;
Ans:- By using decode or case function
UPDATE EMP SET gender=DECODE(gender,'M','F','F','M');
OR
UPDATE EMP SET gender = CASE WHEN gender='M' THEN 'F' ELSE 'F' END;
Q.2-How we can get 2nd highest salary without using Analytical Function?
Ans:-select max(sal) from emp where sal<>(select max(sal) from emp);
Q.3-How we can get the employee details of 2nd highest salary without using Analytical Function ?
Ans:- select * from emp where sal=(select max(sal) from emp
where sal<>(select max(sal) from emp));
Q.4- Find the employee details whose salary is greater than avg salary of his department ?
Ans:-select * from emp a where a.sal>(select avg(sal) from emp b where a.deptno=b.deptno);
Q.5- Find the employee details whose salary is greater than avg salary of employees with same job ?
Ans:-select * from emp a where a.sal>(select avg(sal) from emp b where a.job=b.job) ;
Q.6- Write a query for deleting duplicate records on the basis of empno & ename ?
Ans:-delete from emp where rowid not in (select max(rowid) from emp
group by empno,ename) ;
0 comments:
Post a Comment