Recently asked Most important SQL Queries in Cognizant

  • How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)
  • How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)
  • Count the totalsa  deptno wise where more than 2 employees exist.
SELECT  deptno, sum(sal) As totalsal
FROM emp GROUP BY deptno HAVING COUNT(empno) > 2

  • In emp table add comm+sal as total sal  .
select ename,(sal+nvl(comm,0)) as totalsal from emp;
  • Select  any salary <3000 from emp table. 
select * from emp  where sal> any(select sal from emp where sal<3000);
  • Select  all salary <3000 from emp table. 
select * from emp  where sal> all(select sal from emp where sal<3000);
  • Select all the employee  group by deptno and sal in descending order.
select ename,deptno,sal from emp order by deptno,sal desc;
  • How can I create an empty table emp1 with same structure as emp?
Create table emp1 as select * from emp where 1=2;
  • How to get 3 Min salaries ?
select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);
  • How to get nth max salaries ?
select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);
  • Select DISTINCT RECORDS from emp table.
select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno); 
  • How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno); 
  • Count of number of employees in  department  wise.
select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname; 
  • Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
select ename,sal/12 as monthlysal from emp;
  • Select all record from emp table where deptno =10 or 40.
select * from emp where deptno=30 or deptno=10;