Importent Oracle & Sql Query

1) How delete duplicate records in a table
delete from emp where rowid not in (select max(rowid) from emp group by empno);

2) How to make a column into not null column.
Ans: alter table emp
modify (empno not null); ->this can be done only when all the values in empno are non null (i.e not empty)

?h To make a not null column into null column
alter table emp
modify (empno null);
The fallowing are the rules for

i. adding null or not null property
>You may change a column??s null property to not null only when that field does contain null values(i. It can??t be empty)
>At any time you may change a column??s not null property to null

ii. adding a colum to a table
>You may add a column at any time if NOT NULL isn??t specified (i.e when new column can accept null values )
>You may add a NOT NULL column in three steps.
a. Add the column without NOT NULL specified.
b. Fill every row in that column with data.
c. Modify the column to be NOT NULL.

iii. modifying a column
>You can increase a CHAR column??s width at any time.
>You can increase the number of digits and the number of decimal places in a NUMBER column at any time
>To CHANGE data types or to DECREASE column??s width the column should be null for every row

3)Write queries for the fallowing
ex. select * from emp_self;

EMP_NO EMPN_NAME SAL MGR DEPTID
--------- -------------------- ---------- ---------- ----------
400 jane 20000 110 20
102 Mary 19000 110 20
101 charles 8000 105 50
104 Linda 9000 100 10
110 john 25000 105 20
105 newton 2000 50
100 ALEN 15000 105 50
200 BORIS 3000 110 20
103 DAVID 10000 100 10
300 monica 7000 105 50
i) Query to get the employees who are working under mgr with salary > 10000
select emp_no,mgr from emp_self where mgr in (select emp_no from emp_self where sal > 10000);

ii) Query to get the employees who are getting salaries more than their managers
select a.emp_no from emp_self a,emp_self b where a.mgr =b.emp_no and a.sal > b.sal;
EMP_NO
-------
101
100
110
300
iii) Query to find the nth highest salary
select a.empn_name,a.sal from emp_self a where &n = (select count(*) from emp_self b where a.sal < b.sal);
When n=1 , sal =20000 ->second highest salary ; n=4 , sal =10000 ->fifth highest salary ;
iv) Query to find the second highest salary in different departments.
select a.deptid,min(a. sal) from emp_self a where 1 in (select count(*) from emp_self b
where a.sal < b.sal group by b.deptid) group by a.deptid;

DEPTID MIN(A.SAL)
------- ----------
10 9000
20 20000
50 8000
v) Query to find departments with total salary >25000

select deptid from emp_self having sum(sal) >25000 group by deptid;

DEPTID
------
20
50

4) study the fallowing pl/sql block and find the type of error ->syntax,semantic(logical) or precedence
begin
for i in 1..5 loop
update emp
set sal = 1000 where empno =100 ;
end loop;
end;


5) Difference between (MAX,MIN) and (GREATEST ,LEAST)
The functions max and min compares different rows . Whereas greatest and least work on a group of columns ,either actual or calculated values within a single row.

Ex. select * from emp_self;

EMP_NO EMPN_NAME SAL MGR
------ -------------------- ------- ----------
100 alen 9,000 105
200 boris 10,000 110
101 charles 8,000 105

SQL> select max(sal),min(sal) from emp_self;

MAX(SAL) MIN(SAL)
---------- ----------
10000 8000

SQL> select greatest(sal),least(sal) from emp_self;

GREATEST(SAL) LEAST(SAL)
------------- ----------
9000 9000
10000 10000
8000 8000