SQL important queries?

1.   Select FIRST n records from a table ( in dynamically)?

select * from employees where rownum <= &n;

Note: '&' used for Dynamic pompt , enter the value in dynamically. when runs the query , it wil ask enter n value. It also called temporary & substitution varaible.  If enter 1 value , we wil get single row data. If you want fisrt 50 rows ,we have to enter n value is 50.

For Ex: without enter 'n' value then use

Ø  select * from employees where rownum <= 1

Ø  select * from employees where rownum <= 50;

Ø  SELECT * FROM (select * from employees ORDER BY employee_id) abc WHERE rownum <= 5 ORDER BY rownum asc;

2.  Select LAST n records from a table

(select * from employees )

minus

( select * from employees where rownum <= (select count(*) - &n from employees));

3.   In employees table add comm+sal as total sal  ?

select ename,(sal+nvl(comm,0)) as totalsal from employees;

Note:.NVL() to convert a null value to another value. NVL(x, value) returns value if x is null; otherwise x is returns.

4.  How can I create an empty table emp1 with same structure as employees ?

Create table emp1 as select * from employees where 1=2.

Note: Here Structure only created ,no rows displayed

5.  How can I create an table emp2 with same structure as employees ?

Create table emp2 as select * from employees

Note:Here create the table with Structure and table . Hence structure with table data rows displayed

6. If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?

• (Select * from employees1) union (Select * from employees2) 
• (Select * from employees1) Intersect (Select * from employees2)
• Select * from employees1 a ,employees2 b where a.employees1=b.employees1
•  Select * from employees1 a inner join employees2 b on  a.employees1=b.employees2

7. How can I retrive all records of emp1 those should not present in emp2?

(Select * from emp1) Minus (Select * from emp2)

8. How to get nth max salaries ?(highest salary)?

select distinct hire_date ,salary from employees a where &n =

(select count(distinct salary) from employees b where a.salary >= b.salary);

Note : without using 'n' , we can get max salary -Different alternative solutions are

• select distinct hire_date ,salary from employees a where 57 =  (select count(distinct salary) from emp2 b where a.salary >= b.salary);
• select  max(salary) , max( hire_date)  from employees
• select count(distinct salary), max(salary) , max( hire_date) from employees

9. List dept no., Dept name for all the departments in which there are no employees in the department?

• select * from dept where department_id not in (select department_id from employees);
• select * from dept a where not exists (select * from employees b where a.department_id = b.department_id);
• select * from employees a, dept b where a.department_id(+) = b.department_id and employee_id is null;

10. How to delete duplicate rows in a table?

• delete from employees a where rowid != (select max(rowid) from employees b where  a.employee_id=b.employee_id);
• DELETE FROM employees WHERE rowid not in (SELECT MIN(rowid) FROM employees GROUP BY employee_id);

Note : checking for duplicates

SELECT  employee_id , COUNT(employee_id) nr FROM    employees GROUP BY employee_id HAVING  COUNT(employee_id) > 1

11. Display the names of employees who are working in the company for the past 5 years?

•  select fisrt_name,hire_date from employees where sysdate-hire_date > 5*365;
• select first_name from employees where to_char(sysdate,'YYYY')-to_char(hire_date,'YYYY')>=5;
• SELECT employee_id, last_name, hire_date, MONTHS_BETWEEN(SYSDATE, hire_date) "number of months employed" FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date) >= 60;
•  select first_name from employees where hire_date < add_months(sysdate,-60);

12.Display the names of employees working in department number 10 or 20 or 40 and working as CLERKS,SALESMAN or ANALYST?

select first_name, job_id from employees where department_id in(10,20,40) and job_id in(CLERKS ', SALESMAN ', ANALYST ');

13.List the emps who joined in any year but not belongs to the month of March?

•     select * from employees where  to_char (hire_date,’MON’) not in (‘MAR’);  (OR)
•    select * from employees where to_char (hire_date,’MON’)  !=  ‘MAR’; (OR)
•    select * from employees where to_char(hire_date,’MONTH’) not like ‘MAR%’ ;  (OR)
•    select * from employees where to_char(hire_date,’MON’)  <> ‘MAR’;

14.List the Empno, Ename, Sal, Dname of all the ‘MGRS’ and ‘ANALYST’ working in New York, Dallas with an exp more than 7 years without receiving the Comm asc order of Loc?

select e.empno,e.ename,e.deptno,e.sal,d.loc from emp e ,dept d
where e.deptno = d.deptno and d.loc in ('NEW YORK','DALLAS') and e.job_id in ('MGRS',ANALYST) and  (months_between(sysdate,e.hire_date)/12)>7  and  e.commission_pct is null