User:Nileshdgawali/sandbox

Sample Questions
/* To delete duplicates record in table */ select * from atul; delete from atul where emp_id = '6'; insert into atul values (10,'H');

delete FROM atul WHERE ROWID NOT IN     (SELECT   MIN (ROWID)      FROM atul      GROUP BY emp_id, name); select rowid, emp_id from atul;

/* To find out missing serial numbers in table */ select min_a - 1 + level from ( select min(emp_id) min_a,max(emp_id) max_a         from atul) connect by level <= max_a - min_a + 1 minus select emp_id from atul;

/* To find the nth salary from table */

1.   select employee_id, first_name,salary from (select e1.*, dense_rank over (order by salary desc) rnk         from employees e1) where rnk = 3; 2. Below query will also gives the nth salary by user input but like RANK function. select * from employees e1       where (&n1 - 1) = (select count(*) from employees e2                           where e1.salary < e2.salary); 3. SELECT COL1 FROM temp1 D2    WHERE 2/* Nth - 1 Here we are finding 3rd highest salary */ = (SELECT COUNT(*)               FROM temp1 D1                 WHERE D1.COL1 > D2.COL1);

/* Find Alternate Rows */

select * from (select e1.*, row_number over (order by employee_id) num1         from employees e1) where mod(num1,2)=1;

/* Output in one line */ 1. select item, wm_concat(loc) as location from item_loc group by item; 2. select stragg(item) from (select stragg(item)               from item_master                where item In ('100011997','018869','7046260188693')                order by item);