SQL Questions

Q.no- Can we use Rank function without group by ?

Ans- No.

Q.no- Difference between group by and partition by ?

Ans- Partition by is an Analytical function which can be used to aggregate result but they do not group the result set. It will return the same no of rows.

In select we can use N no. of columns. No restrictions.

While group by is used to aggregate the result based on the group set.

In select we need to use only columns which are used in group by. But we can use aggregate functions.

Q.no- Select LAST n records from a table?

Ans- select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);

Q.no- How to fetch only common records from two tables emp and emp1?

Ans- (Select * from emp) Intersect (Select * from emp1)

The SQL INTERSECT clause/operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This means INTERSECT returns only common rows returned by the two SELECT statements.

USE of SUBSTR

Q.no- Select first 3 characters of FIRST_NAME from EMPLOYEE

Ans- select substr(FIRST_NAME,0,3) from employee 

USE of INSTR

Q.no- Get position of 'o' in name 'John' from employee table?

Ans- Select instr(FIRST_NAME,'o') from employee where first_name='John'.

Q.no- How to display leap years from 2000 to 3000 years ?

Ans- select year from (select 2000+level-1 year from dual connect by level <=1001) where mod(year,400)=0 or (mod(year,4)=0 and mod(year,100)!=0);

Q.no- How to display all dates of year(2016) ?

Ans- select to_date((lpad(level,3,0)||'2016'),'ddd-yyyy') from dual connect by level <366;

Q.no-9. What is the difference between the NVL and the NVL2 functions?
Ans- The NVL(exp1, exp2) function converts the source expression (or value) exp1 to the target expression (or value) exp2, if exp1 contains NULL. The return value has the same data type as that of exp1.

The NVL2(exp1, exp2, exp3) function checks the first expression exp1, if it is not null then, the second expression exp2 is returned. If the first expression exp1 is null, then the third expression exp3 is returned.

Q.no-Discuss the syntax and use of the COALESCE function?
Ans- The COALESCE function has the expression COALESCE(exp1, exp2, …. expn)

It returns the first non-null expression given in the parameter list.

Q.no- What is the use of the NULLIF function?
Ans- The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the first expression is returned.

Q.no- Can we perform DML operations while the index build is taking place on the same table ?
Ans- Yes, we can update table. We need to create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.

The following statements illustrate online index build operations:
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

Q.no- Can we use DDL operation in PL/SQL block ?
Ans- Yes, we can achieve that by using dynamic SQL. DDL commands are not allowed as PL/SQL constructs in PL/SQL blocks. Using DBMS_SQL or EXECUTE IMMEDIATE, we can execute create table, drop, alter, analyze, truncate and other DDL's too.


Q.no: Delete duplicate rows from Oracle tables ?
Ans: delete from customer where rowid in  (select rowid from (select rowid,row_number() over (partition by custnbr order by custnbr) dup
 from customer) where dup > 1);

Q.no: Find Nth highest salary in Oracle table ?
Ans: select * from (select Emp.*, row_number() over (order by Salary DESC) rownumb from Employee Emp) where rownumb = n;