GeekInterview.com
Series: Subject: Topic:

Oracle SQL Interview Questions

Showing Questions 1 - 20 of 190 Questions
First | Prev | | Next | Last Page
Sort by: 
 | 

What are the advantages and disadvantages of view?

Asked By: sbagai2001 | Asked On: May 26th, 2006

Star Read Best Answer

Editorial / Best Answer

Answered by: Mohan

Answered On : Jun 2nd, 2006

Hi,

Advantages of views:

1. View the data without storing the data into the object.

2. Restict the view of a table i.e. can hide some of columns in the tables.

3. Join two or more tables and show it as one object to user.

4. Restict the access of a table so that nobody can insert the rows into the table.

Disadvatages:

1. Can not use DML operations on this.

2. When table is dropped view becomes inactive.. it depends on the table objects.

3. It is an object, so it occupies space.

Pls. add , if I miss any of them.

Thanks,

Mohan

Answered by: soumya on: May 17th, 2013

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.

Answered by: purna chandrudu on: May 13th, 2013

DML operation can performed on views.

To get second highest age from the student table

Asked By: technofreek | Asked On: May 18th, 2011

I am trying to get second highest age from the student table.Student table:sql> desc student; name null? Type ----------------------------------------- -------- ----------------- rollno number name ...

Answered by: DEEPU on: May 16th, 2013

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

Answered by: Nazeera Jaffar on: Sep 30th, 2012

Answer

Code
  1. SELECT max(age )
  2. FROM TABLE
  3. WHERE age<(SELECT max(age)
  4.                   FROM TABLE)

How to retrieve 2nd highest sal in each departement from emp and dept tables using group by?

Asked By: abhiecstatic | Asked On: Oct 21st, 2012

Emp table (empno,deptno,sal) dept table(deptno,dname) i need deptno, dname, 2nd_highest_sal_in_dept in output. i can easily do this using row_number, rank,dense_rank etc but I am unable to do this using group by. please suggest if this can be done using group by.

Answered by: Rakesh Protihar on: May 17th, 2013

Code
  1. SELECT e.DeptNo, MAX(e.Sal),d.DeptName Salary
  2. FROM Emp e left outer join dept d ON e.DeptNo=d.DeptNo
  3. WHERE e.Sal <
  4. (SELECT MAX(Sal)
  5.  FROM Emp
  6.  WHERE DeptNo = e.DeptNo)
  7. GROUP BY e.DeptNo,d.DeptName

Answered by: Deepu on: May 16th, 2013

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....

Select from table without using column name

Asked By: sanjoy.dubey | Asked On: Jun 12th, 2008

How can I select column values from a table without knowing the column name ?Suppose , select employee_id from employees , now I don't know the column name and I want to select the column.Please let me know the answer

Answered by: Geoffry on: Apr 23rd, 2013

You can Apply This.

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

Answered by: NSingh on: Sep 27th, 2010

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

Display the number value in words?

Asked By: Interview Candidate | Asked On: Aug 30th, 2005

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))from emp;the output like,sal (to_char(to_date(sal,'j'),'jsp'))--------- -----------------------------------------------------800 EIght hundred1600 one thousand six hundred1250 one thousand two hundred fiftyif you want to add some text like, rs. Three...

Answered by: Talal Haider on: Mar 16th, 2013

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

Answered by: boobal on: Mar 21st, 2012

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

What is difference between SQL and SQL*plus?

Asked By: Interview Candidate | Asked On: Aug 29th, 2004

SQL*plus is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(dml,dcl,ddl). SQL*plus commands are...

Answered by: Nitisha Agarwal on: Nov 5th, 2012

ur r absolultely right thanks for answer

Answered by: Kranthi swaroop on: Dec 2nd, 2011

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...

Find out nth highest salary from emp table

Asked By: Interview Candidate | Asked On: Aug 29th, 2004

Select distinct (a.Sal) from emp a where &n = (select count (distinct (b.Sal)) from emp b where a.Sal < = b.Sal);for eg:-enter value for n: 2sal---------3700

Answered by: bhavikgore on: Oct 27th, 2012

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

Answered by: rohitosu on: Jul 30th, 2012

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

There is a % sign in one field of a column. What will be the query to find it?

Asked By: Interview Candidate | Asked On: Aug 31st, 2005

'' should be used before '%'.

Answered by: Nazeera Jaffar on: Oct 6th, 2012

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

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

Answered by: Nazeera Jaffar on: Sep 30th, 2012

The below code will select the row having column as %

Code
  1. SELECT *
  2. FROM table_name
  3. WHERE column_name LIKE !%escape!
  4.  

Write a query to display alternate records from the employee table?

Asked By: kowmudiswarna | Asked On: Sep 14th, 2007

Answered by: Nazeera JAffar on: Sep 30th, 2012

Answer

Code
  1. SELECT rownum
  2. FROM TABLE
  3. GROUP BY rownum
  4. HAVING mod(rownum,2)=0

Answered by: Bhaskara Vamsi on: Aug 15th, 2012

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

Find manager for employee

Asked By: rajaramanv | Asked On: May 24th, 2008

Id employee department manager----------------------------------------------1 suresh c++ null 2 suresh c++ null3 suresh c++ 25 sarathy testing 26 rajaraman c# 17 joe flash ...

Answered by: poojamohan on: Sep 25th, 2012

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

Answered by: rohitosu on: Jul 31st, 2012

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

How to get the prime number rows from table ie like1,3,5,7,11

Asked By: rajesh | Asked On: Sep 14th, 2005

Answered by: VIJAY SHARMA on: Sep 24th, 2012

Code
  1.  
  2. SELECT SEQNO "PRIME NUMBERS"
  3. FROM(
  4.      SELECT SEQNO
  5.      FROM(
  6.           SELECT ROWNUM SEQNO
  7.           FROM ALL_OBJECTS
  8.           GROUP BY ROWNUM
  9.           HAVING ROWNUM <= &GIVENNUM
  10.          ),
  11.          (
  12.           SELECT ROWNUM SEQNO2
  13.           FROM ALL_OBJECTS
  14.           WHERE ROWNUM <= &GIVENNUM
  15.          ) A2
  16.     GROUP BY CUBE(SEQNO,SEQNO2)
  17.     HAVING GROUPING_ID(SEQNO,SEQNO2) = 0 AND SEQNO >= SEQNO2 AND
  18.     MOD(SEQNO,SEQNO2) = 0
  19.     )
  20. GROUP BY SEQNO
  21. HAVING COUNT(*) IN(1,2)
  22. ORDER BY SEQNO;

Answered by: KARRTHIK on: Dec 3rd, 2011

"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 ...

Question is been asked in the interview., I have table department with details likedeptid dname dlocation10 finance del20 sales mum30 marketing blorethe output should...

Asked By: hamsa | Asked On: Nov 5th, 2007

Answered by: VIJAY SHARMA on: Sep 24th, 2012

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;

Answered by: Mark Haynes on: Sep 22nd, 2011

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 || ' '))....

How to get first 5 records then next 5 records till end of row count in SQL -Oracle

Asked By: Dev | Asked On: Feb 6th, 2006

Answered by: Naazneen on: Sep 6th, 2012

Using pagination

Code
  1. SELECT *
  2.     FROM (
  3.     SELECT empno, sal,
  4.   row_number()
  5.          over (ORDER BY empno ASC) rn
  6.     FROM emp
  7.     )
  8.     WHERE rn BETWEEN 1 AND 5
  9.     ORDER BY sno ASC;

Answered by: ananymous on: Jan 6th, 2012

The best ans given above is

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

Truncate and delete concept

Asked By: NehaBindaas | Asked On: Feb 1st, 2012

Hi, let us take a scenario such that I issue a delete on table emp and truncate on table emp1 which is exact replica (in terms of both structure and data) of emp table.Now , I issue a commit on both sessions. if I do a select * from emp and select * from emp1 now, which one should execute faster and...

Answered by: Waseem Mehmood on: Sep 4th, 2012

- 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.

Answered by: Johny on: May 18th, 2012

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

Suppose there are two fields in table(employee) say name and salary and there are in total 100 records in table. Now my query gives the name of an employee whose salary is 10th among 100 salaries....

Asked By: Interview Candidate | Asked On: Aug 30th, 2005

Answered by: manjari_ghosh12 on: Sep 1st, 2012

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

Answered by: abhikum.402 on: Aug 13th, 2012


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

Difference between an implicit & an explicit cursor.

Asked By: Interview Candidate | Asked On: Aug 29th, 2004

PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor for loop.Explicit cursor is a cursor in which the cursor name is explicitly assigned...

Answered by: manjari_ghosh12 on: Sep 1st, 2012

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...

Answered by: rohitosu on: Jul 31st, 2012

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...

Write a query to display employee records having same salary?

Asked By: kowmudiswarna | Asked On: Sep 13th, 2007

Answered by: manjari_ghosh12 on: Sep 1st, 2012

"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...

Answered by: vikash kumar singh on: Aug 4th, 2012

Code
  1. 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
  2. FROM Employees) WHERE Cnt>=2;

How to select the recently updated records from the table?

Asked By: sat.inn | Asked On: Feb 22nd, 2007

Answered by: Ayyappa on: Aug 24th, 2012

Hello leelakrishna302 ... Your query ...

Code
  1.  
  2. SELECT * FROM emp e
  3. WHERE rowid IN (SELECT max(rowid) FROM emp);
  4.  

gives only latest inserted records not the latest updated records..

Answered by: leelakrishna302 on: Jun 15th, 2012

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

Can you use a commit statement within a database trigger?

Asked By: Interview Candidate | Asked On: Jul 27th, 2005

No.

Answered by: Shantanu on: Aug 17th, 2012

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.

Answered by: Jagan on: Aug 21st, 2011

Yes, by using autonomous transaction

How to find the two minimum salaries ?

Asked By: fareed | Asked On: Oct 14th, 2005

Star Read Best Answer

Editorial / Best Answer

Answered by: maverickwild

View all answers by maverickwild

Member Since Nov-2005 | Answered On : Nov 15th, 2005

Try this

select sal from (select * from order by sal asc) where rownum < 3

Answered by: snehal Falke on: Jul 13th, 2012

Select * FROM emp ORDER BY sal ASC LIMIT 2

Answered by: mohit on: Jun 28th, 2012

If you want to employee name then query is given below

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

First | Prev | | Next | Last Page

 

 

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Ads

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.