Oracle SQL Interview Questions

select r, ename from(select rownum r,ename from emp) where mod(r,2)=1;

for even rows:-

select ename,empno, job, rownum from emp group by ename ,rownum ,empno,job having mod(rownum,2)=0


for odd rows please replace '0' with 1 then u get odd rows:-

First arrange the student ages from student table in the descending order then query with row number condition:

select * from (select rownum as a ,age from student order by age desc)
where a=2;

To find out any(1st,2d,3d,....) minimum/maximum salary
you can use either sub query or co-related sub query..
using sub query

select min(sal) from emp where sal <(select min(sal) from emp);(gives 2nd minimum salary).
select min(sal) from emp where sal <(select min(sal) from emp where sal < (select min(sal) from emp)); (3rd)
(similar is true for maxsal)
using co-related sub query
select distinct a.sal from emp a where &n = (select distinct(count b.sal) from emp b where a.sal <=b.sal);
in co-related sub query you are actually comparing values of your main query i.e(a.sal) with (b.sal)
so ,
your sub query has a relationship whit main query .(sub query in not independent)

We use the selfjoin on the table employees to retrieve the job before thier managers

Code
SELECT * FROM employees e , employees m
WHERE e.manager_id=m.employee_id
AND e.hire_date < m.hire_date;

select x.ename from emp x,emp y where x.mgr=y.empno and x.hiredate

Truncate will not delete the structure of the table. That retains the table, but delete every row. The "DROP" command, will delete everything including the structure

1.We can filter data in delete but in truncate we cant
2.Truncate faster than delete;
3.We can rollback delete but truncate is auto commit.
4.Delete is DML but truncate is DDL.

Code
SELECT rownum,salary,last_name 
FROM (SELECT salary,last_name FROM employees ORDER BY salary DESC) 
WHERE rownum<=n;

this is the simplest of all :)

Code
SELECT * FROM(SELECT ROWNUM AS a,salary FROM employees ORDER BY salary DESC) WHERE a = "enter your nth value here";

Code
SELECT * FROM emp  WHERE sal >(SELECT avg(sal) FROM emp)

select * from emp a
where salary >(select avg(salary) from emp b where a.dept_no = b.dept_no);

Using regexp_replace function

Code
SELECT to_number(regexp_replace(abc123efg456kjhdf,[[:alpha:]])) 
FROM dual;

Code
SELECT * FROM table_name WHERE regexp_like (column_name, ^[0 - 9] + $);

Only data will be deleted for first session and for second session data will be available because there is no commit in first session.

Yes,Abhishek is right.I already opened 2 sessions & did this exercise.In the 1st window,i issued delete from emp & went to 2nd window.I was able to see all the rows in the 2nd session.THis is ...

I just took employees tables last_name. See the code.

Code
SELECT substr(last_name,4,1)||substr(last_name,4,2)||substr(last_name,2,1)||substr(last_name,1,1) 
"Reversing Name" FROM employees
WHERE employee_id=100

Union --> All rows/cols from 1st select and 2nd select with no duplicates Union All -->All rows/cols from 1st Select and 2nd Select with duplicates Intersect --> Rows common in both select statements...

The UNION operator is used to combine the result-set of two or more SELECT statements.   SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

Both are same

The subquerry is often reffered to as a nested SELECT, sub-SELECT, or inner SELECT statement.
The subquery generally excuted first, and its output is used to complete the query condition for the main (or outer) query.

Code
SELECT * FROM emp WHERE (rowed,0) IN (SELECT rowid,mod(rownum,5) FROM emp);

Method 1

Code
SELECT a,employee_id,last_name,first_name,salary FROM
(SELECT ROWNUM AS a,employee_id,last_name,first_name,salary FROM employees)
WHERE a BETWEEN 10 AND 11

This answer is no different to
select to_char(to_date(1290,j),jsp) from dual;

In order to display the number values , the best it can be done is with the julian i.e. j and jsp.

however, using the translate also returns the same value for whatever value to display.

select to_char(to_date(1920,'j'),'jsp') from dual;

As a rule of thumb IN should be used when the sub query will return small result set. It should be kept in mind that the sub query should not return null values. EXISTS should be used when the result set returns large result set. Other factors that might affect query performance is the indexes

Which is more faster - IN or EXISTS? It totally depends upon the query. For some queries Exist is efficient and for some IN is efficient Exits is correlated sub query, in which the outer query run f...

Code
SELECT *FROM ( SELECT * FROM table_name ORDER BY dbms_random.value ) WHERE rownum <=5;

It will work--No error
select salary,num from (select salary,rownum num from employees order by salary desc)
where num=2;

Hiii to userSELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal); WITH THIS RESULT U GOT ANY NUMBER OF SALARY IN A GIVEN TABLE PLZ TRY IT;S BEST WAY TO FIND ANY NUMBER OF SALARY IN GIVEN TABLE....................... ONLY SIMPLY ENTER THE VALUE OF '" N " &GET ANY POSTION OF SALARY ,....EXAM... IF U WANT 5th SALARY THEN PUT .........N=5... OR U WANT 7th SALARY FROM TABLE PUT ONLY N=7....................and so on .............

Code
SELECT min(age) FROM student WHERE age IN (SELECT DISTINCT top 2 age FROM student orderby age DESC)

select rollno,name,age from
(select rollno, name,age, dense_rank() over ( partition by null order by age desc ) rnk
from student)
where rnk = 2

U can use : " <>" to filter null values using code.
Eg:
with worksheet.range("A1:B10")
.autofilter 2, criteria1: "<>"
end with

IS NOTNULL(salary)

The code below retrieves the manager name. Note that employees who do not have manger is also listed. Select Emp1.Employee_Id, Emp1.first_name, Emp1.last_name, Emp1.Manager_id, Emp2.first_name...

SELECT e.ename as Manager, m.ename as Employee FROM emp e, emp m WHERE e.MGR = m.EMPNO

The best ans given above is

Code
 
SELECT * FROM (SELECT ROWNUM R ,CUST_NO FROM S1_ETL.CA_STTC_DETS_1) WHERE R BETWEEN &F AND &L

SELECT * FROM (SELECT ROWNUM R ,ENO FROM EMP) WHERE R BETWEEN &F AND &L
/