Question { Polaris, 16153 }

I have one Excel file with 1,50,000 Records. Now I need to
load that whole file into Oracle Database with same columns
in Excel sheet .
I need PLSQL Procedure or used by SQL PLUS

Answer

yes, We can also import data in db by creating external table.So in this case, we can use sql plus too.

Question { Oracle, 20299 }

Create table emp
(id number(9), name varchar2(20),salary
number(9,2));
The table has 100 records after table created.Now i nee to
change id's Datatype is to be Varchar2(15). now

Alter table emp modify(id varchar2(15),name varchar2(20),
salary number(9,2));

Whether it will work or returns error? post answer with
explanation.

Answer

They are asking change datatype from number to varchar2..So it is not possible if table have data..this will give error 'ORA-01439: column to be modified must be empty to change datatype'.You can alter table and modify that column in same data type.
See Example 
select * from a;
   N F
----- -------------------
    5
    1
    3
alter table a modify  (n varchar2(10))
                       *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

  1* alter table a modify  (n number)
SQL> /

Table altered.<<<<<<<<<<<<<<<<<

Question { Mind Tree, 26676 }

consider a table which contain 4 columns,ename,eno,sal and
deptno, from this table i want to know ename who having
maximum salary in deptno 10 and 20.

Answer

select last_name,salary from employees where (salary,department_id) in ( select max(salary),
department_id from employees where department_id in (10,20) group by 
department_id) 
/
Output
LAST_NAME                     SALARY
------------------------- ----------
Hartstein                      13000
Whalen                          4400

Question { Geometric Software, 12552 }

suppose I have two table one Emp and other is dpt.
Emp table has a field ,dept id,name ,sal and dpt table has a
field dept id,dept name.
Now I want to find out the emplyee list whose sal is between
2000-3000 from dept x.

Answer

select e.department_id,e.last_name,e.salary,d.department_name from employees e,
departments d 
where e.department_id=d.department_id and 
e.salary between 2000 and 10000 
and lower(d.department_name)='finance'
/
Out put
DEPARTMENT_ID LAST_NAME                     SALARY DEPARTMENT_NAME
------------- ------------------------- ---------- --------------------
          100 Faviet                          9000 Finance
          100 Chen                            8200 Finance
          100 Sciarra                         7700 Finance
          100 Urman                           7800 Finance

Question { L&T, 15266 }

I have a Employee table with columns
ename,eid,salary,deptno. How to retrieve sum of salary for
each deptno?

Answer

select department_id,sum(salary) Dept_wise_salary,job_id from employees group by department_id,job_id
/
Output 
DEPARTMENT_ID DEPT_WISE_SALARY JOB_ID
------------- ---------------- ----------
          110             8300 AC_ACCOUNT
           90            34000 AD_VP
           50            55700 ST_CLERK
           80           243500 SA_REP
          110            12000 AC_MGR
           50            36400 ST_MAN
           80            61000 SA_MAN
           50            64300 SH_CLERK
           20            13000 MK_MAN
           90            24000 AD_PRES
           60            28800 IT_PROG




DEPARTMENT_ID DEPT_WISE_SALARY JOB_ID
------------- ---------------- ----------
          100            12000 FI_MGR
           30            13900 PU_CLERK
          100            39600 FI_ACCOUNT
           70            10000 PR_REP
                          7000 SA_REP
           10             4400 AD_ASST
           20             6000 MK_REP
           40             6500 HR_REP
           30            11000 PU_MAN

Question { Tech Mahindra, 26320 }

how u can find the n row from a table?

Answer

select * from employees where rownum<=&nth_row
/

Question { Mind Tree, 18606 }

can we call a procedure from a function?

Answer

DECLARE
  FUNCTION my_func RETURN NUMBER IS
  BEGIN
    RETURN 2;
  END my_func;




  PROCEDURE my_proc IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(my_func + 1);
  END my_proc;




BEGIN  -- main
  my_proc;
END;    -- main
As shown above, with the function declared first you can call the function from the procedure. However, if you try something like the following (function declared before procedure, and function calls procedure):




DECLARE
  FUNCTION my_func RETURN NUMBER IS
  BEGIN
    my_proc;
    RETURN 2;
  END my_func;




  PROCEDURE my_proc IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('22');
  END my_proc;




BEGIN  -- main
  DBMS_OUTPUT.PUT_LINE(my_func);
END;    -- main
the compile will fail, because my_func cannot 'see' my_proc. To make it work you need to put in a 'prototype' declaration of my_proc, as follows:




DECLARE
  PROCEDURE my_proc;




  FUNCTION my_func RETURN NUMBER IS
  BEGIN
    my_proc;
    RETURN 2;
  END my_func;




  PROCEDURE my_proc IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('22');
  END my_proc;




BEGIN  -- main
  DBMS_OUTPUT.PUT_LINE(my_func);
END;    -- main

Question { IBM, 18213 }

i have a table
eno dno sal
1 10 200
2 10 150
3 10 100
4 20 75
5 20 100

i want to get sal which is less than the avg sal of thri dept.

eno dno sal
2 10 150
3 10 100
4 20 75

Answer

select department_id,salary from employees e 
where salary<(select trunc(avg(salary)) 
from employees where department_id=e.department_id;

Question { HCL, 13639 }

what is the correct way of selection statement
a. select/from/table_name/orderby/groupby/having
b. select/from/table_name/groupby/having/orderby

Answer

It's Ans(b).

Question { Infogain, 10840 }

How to find only %th Highest Sal

Answer

SELECT A.FIRST_NAME,
       A.SALARY
   FROM EMPLOYEES A
   WHERE 3 = ( SELECT COUNT(*) -- Replace 3 with any value of (N - 1)
               FROM EMPLOYEES B
               WHERE B.SALARY > A.SALARY)

Question { Saama Tech, 9845 }

If i insert record in table A and these record should update in table B by using Trigger.How to achieve this.

Answer

Table 1(u_register) Parent table
 Name                                      Null?                           Type
 ----------------------------------------- -------- ----------------------
 FIRST_NAME                                                             VARCHAR2(15)
 LAST_NAME                           NOT NULL                  VARCHAR2(15)
 DOB                                       NOT NULL                   DATE
 USER_NAME                           NOT NULL                 VARCHAR2(15)
 NEW_PASSWORD                   NOT NULL                 VARCHAR2(15)
 CONFIRM_PASSWORD           NOT NULL                 VARCHAR2(15)
 U_ID                                        NOT NULL                 NUMBER

table 2(login_detail) Child table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 ID                                        NOT NULL NUMBER
 USER_NAME                                          VARCHAR2(15)
 NEW_PASSWORD                                       VARCHAR2(15)

Now we will create trigger to insert column 'user_name','new_password' of u_register into column 'USER_NAME ','NEW_PASSWORD' of login_detail.We will use after insert trigger as 

create or replace 
trigger tgr_login
after insert on u_register
for each row
begin 
insert into login_detail
values (:new.u_id,:new.user_name,:new.new_password);
end tgr_login;

It will work for sure.

Question { HCL, 6561 }

Write query to fetch second maximum salary from employee table.

Answer

select a.last_name,a.salary from employees a where 3=(select count(*) from employees b where b.salary>a.salary) order by a.salary desc.
<<<<<<<<<[N-1]>>>>>>>>>>