Oracle SQL Interview Questions

View when called always contacts the base table, on which it is built to get the data .so it always goes to the server thats why it degrades the performance of the server.

DML operation can performed on views.

SELECT * FROM (SELECT ROLLNO, AGE, ROWNUM AS RN FROM STUDENT) WHERE RN=2

Answer

Code
SELECT max(age )
FROM TABLE
WHERE age<(SELECT max(age)
                  FROM TABLE)

Code
SELECT e.DeptNo, MAX(e.Sal),d.DeptName Salary
FROM Emp e left outer join dept d ON e.DeptNo=d.DeptNo
WHERE e.Sal <
(SELECT MAX(Sal)
 FROM Emp
 WHERE DeptNo = e.DeptNo)
GROUP BY e.DeptNo,d.DeptName

Pls. use the below "plsql SELECT T1.DEPTNO, T1.DNAME,MIN(T1.SAL) AS SECOND_MAX_SAL FROM (SELECT E.FIRST_NAME, E.SAL, D.DEPTNO,D.DNAME, DENSE_RANK() OVER (PARTITION BY D.DEPTNO, D....

You can Apply This.

Select * from tablename where signin value("serverhost" "root" "viky")
$email[eml]
$pass[pass]
iffisset[login value-submit]

1. Describe tablename
2. Desc tablename
3. select * from tablename

Can we convert a number in words without using direct function/method?

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

ur r absolultely right thanks for answer

SQL is a Structure Query language. Where we build queries to communicate with Oracle server to Access the Data. and SQL * Plus is Command Line Tool or Interface which will reads the query or recogn...

This will work for only highest salary n=1 but not work for 2nd and rest nth salary...
Did you have tried this?

Code
 SELECT * FROM  (SELECT employee_id, salary, dense_rank() OVER ( ORDER BY salary DESC)  r 
  FROM employees) a 
  WHERE a.r =3 ;

The below code selects the column with a substring % (e.g.) reg%istration

Code
SELECT * FROM game WHERE name LIKE %\%% escape ;

The below code will select the row having column as %

Code
SELECT * 
FROM table_name
WHERE column_name LIKE !%escape! 
 

Answer

Code
SELECT rownum
FROM TABLE
GROUP BY rownum
HAVING mod(rownum,2)=0

Code
SELECT * FROM emp WHERE ROWID IN (SELECT DECODE(MOD(ROWNUM,2),0,ROWID, NULL) FROM emp);

How to get the old manager name ,if the employee has been changed to other manager?

Code
SELECT a.employee_id, a.manager_id, A.FIRST_NAME Employee_Name, B.FIRST_NAME Manager_Name
    FROM employees a
    INNER JOIN employees b 
    ON A.MANAGER_ID = B.EMPLOYEE_ID 
    ORDER BY A.EMPLOYEE_ID ;

Code
 
SELECT SEQNO "PRIME NUMBERS"
FROM(
     SELECT SEQNO
     FROM( 
          SELECT ROWNUM SEQNO
          FROM ALL_OBJECTS
          GROUP BY ROWNUM
          HAVING ROWNUM <= &GIVENNUM
         ),
         (
          SELECT ROWNUM SEQNO2
          FROM ALL_OBJECTS
          WHERE ROWNUM <= &GIVENNUM
         ) A2
    GROUP BY CUBE(SEQNO,SEQNO2)
    HAVING GROUPING_ID(SEQNO,SEQNO2) = 0 AND SEQNO >= SEQNO2 AND
    MOD(SEQNO,SEQNO2) = 0
    )
GROUP BY SEQNO
HAVING COUNT(*) IN(1,2)
ORDER BY SEQNO;

"plsql n number; i number; counter number; begin n:=&n; i:=1; counter:=0; if n=1 then dbms_output.put_line('1 is a prime No.'); else if n=2 ...

SELECT SYS_CONNECT_BY_PATH(DEPTNO, )I ,
SYS_CONNECT_BY_PATH(DNAME, ) II,
SYS_CONNECT_BY_PATH(LOC, ) III
FROM DEPT
WHERE LEVEL = &TOTALDEPARTMENTS
START WITH DEPTNO = 10
CONNECT BY PRIOR DEPTNO < DEPTNO;

The code seemed to have been messed up there, here it is again formated: SELECT CASE WHEN Colmn = 'DeptId' THEN (SELECT RTRIM(XMLAGG(XMLELEMENT(e, DeptId || ' '))....

Using pagination

Code
SELECT *
    FROM (
    SELECT empno, sal,
  row_number()
         over (ORDER BY empno ASC) rn
    FROM emp
    )
    WHERE rn BETWEEN 1 AND 5
    ORDER BY sno ASC;

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

- Truncate faster than delete;
- Truncate apply of whole table but one can filter the records in delete command
- We can rollback delete but truncate is auto commit, Once the data has been truncated the data could not be recovered
- Delete is DML but truncate is DDL.

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

Code
SELECT * FROM (SELECT e.*,rownum rnm FROM (SELECT * FROM emp e ORDER BY sal DESC) e) WHERE rnm=10;

Select emp_name from (select *,Dense_RANK()over (order by Emp_sal) SalayRank from Emp_a)A
where SalayRank=10

Whenever a DML operation done there is a implicit cursor present. Explicit cursor is defined by user. Whenever a query runs implicit cursor automatically runs. User have no control on it and can not o...

A) A cursor is a pointer to the results of a query run against one of more tables in the database. IMPLICIT CURSOR : PL/SQL declares and manages an implicit cursor every time you execute a SQL DML s...

"sql select e1.ename,e1.sal,count(*) person_having_same_sal from emp e1,emp e2 where e1.sal=e2.sal group by e1.sal,e1.ename having count(*)>1; OR "s...

Code
SELECT eMployee_Id,First_Name,Last_Name,Salary FROM(SELECT Employee_Id,First_Name,Last_Name,Salary,Count(*) Over (Partition BY Salary ORDER BY Salary) Cnt 
FROM Employees) WHERE Cnt>=2;

Hello leelakrishna302 ... Your query ...

Code
 
SELECT * FROM emp e 
WHERE rowid IN (SELECT max(rowid) FROM emp); 
 

select * from emp e where rowid in( select max(rowid) from emp );

You cant directly use commit in triggers. You have to use PRAGMA Autonomous_Transaction or you can call other procedure or function, but other procedure or function should also have PRAGMA Autonomous_Transaction in it.

Yes, by using autonomous transaction

Select * FROM emp ORDER BY sal ASC LIMIT 2

If you want to employee name then query is given below

Code
SELECT ename,sal FROM (SELECT * FROM emp ORDER BY sal ASC)
WHERE rownum<=2;