GeekInterview.com
Series: Subject: Topic:

Oracle SQL Interview Questions

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

How to find nth largest or nth smallest data from Oracle table, for ex..5th highest salary from employees

Asked By: vidhyalakshmi | Asked On: Sep 16th, 2006

Answered by: kitty on: Sep 8th, 2014

Can u plz explain me in detail about the answer you have provided with correlated sub query.

Thnks in advance !

Answered by: yagneswarasankar on: Dec 2nd, 2010

4th Highest Salary  1*  select a.last_name,a.salary from (select b.*,rownum rn from (select last_name,salary from employees order by salary desc)b)a where rn = &nSQL> /Enter value for...

Studetnt result should get pass (or) fail

Asked By: Rakesh057 | Asked On: Sep 9th, 2014

Hi guys try this query if any student fails in one subject also his result should be faile r else pass condtion is(marks>=40 "pass" marks

The student who got >=40 student is passes and

Asked By: Rakesh057 | Asked On: Sep 8th, 2014

S_name marks ramu 60 ramu 40 ramu 50 karan 30 karan 96 karan 46 out put: ramu "pass" karan "fail"

What is cartesian product in the SQL?

Asked By: Sameer Yajurvedi | Asked On: Jan 8th, 2007

Answered by: jomarie on: Sep 2nd, 2014

This is formed when join conditions is omitted or invalid

Answered by: sampra on: Feb 26th, 2008

When a join condition is omited when getting  result from two tables then that kind of query gives us Cartesian product, in which all combination of rows displayed. All rows in the first table is joined to all rows of second table......

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: damodhar on: Sep 1st, 2014

View is virtual tables unlike tables that contain data,views contain queries that dynamically retrieve data when used. Materialized view: Materialized views is also a view but are disk based.materiali...

Answered by: mohan on: Jul 8th, 2014

You can perform DML on view but only one simple view (one table) , If view would join more then one table data you may not perform any DML operations.

How can a trigger be actiVATed on demand?

Asked By: Akhilesh | Asked On: Aug 12th, 2007

Answered by: AiM on: Aug 23rd, 2014

Trigger code to create a trigger in disable mode starting 11g "oracle create or replace trigger prac_trig_emp1 before insert or update OR DELETE ON emp1 REFERENCING NEW AS NEW OLD...

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

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

Answered by: Gaurav gg on: Aug 20th, 2014

SELECT * FROM tab_name WHERE mod(primary_key_col,2)=0; -- for even records

SELECT * FROM employees WHERE mod(employee_id,2)=0; -- for even records

SELECT * FROM employees WHERE mod(employee_id,2)=1; -- for odd records

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

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: Abhilash on: Aug 7th, 2014

Code
  1. SELECT age FROM (SELECT b.age, rownum rm FROM (SELECT DISTINCT(a.age) FROM student a ORDER BY a.age DESC) b WHERE rownum<=2) WHERE rm>=2;

Answered by: Sujatha N on: May 15th, 2014

Try this way:



SELECT MIN(AGE)
FROM (SELECT DISTINCT AGE FROM STUDENT ORDER BY AGE DESC)
WHERE ROWNUM

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: Rs on: Aug 6th, 2014

No need to use group by to get second highest salary.. use simple code as shown below...

select max(salary) from emp where salary < (select max(salary) from emp)

Answered by: kumar on: May 29th, 2014

Use join to join with dept table for desired result

Code
  1. SELECT MAX(A.SAL),A.DNO FROM EMP A,(SELECT MAX(SAL) MSA,DNO FROM EMP GROUP BY DNO) B
  2. WHERE A.SAL <> MSA AND A.DNO = B.DNO
  3. GROUP BY A.DNO;

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: VEERA on: Jul 26th, 2014

Implicit cursors are controlled by the system and explicit cursors are controlled by the user.

Answered by: Deepesh on: Aug 21st, 2013

Its an explicit cursor not only because it returns more than one row as a result, but because it is declared explicitly. Visually you wont see the orthodox CURSOR cursor_name IS ........ etc kind of d...

Is it possible to update views? If yes, how, if not, why?

Asked By: nagurtilak | Asked On: Dec 13th, 2006

Answered by: gauss786 on: Jul 11th, 2014

Use trigger INSTEAD OF on UPDATE of the view

Answered by: ajay goel on: Jun 23rd, 2014

We cant execute DML on complex views but can execute on simple views ..however exceptions are always there. also sometimes we cant even update simple views like if we are updating a row and constrain...

How do you view the last record added to a table?

Asked By: t_ahwaz | Asked On: Aug 25th, 2007

Answered by: kranthiswaroop on: Jun 16th, 2014

Hi ahwaz,

In order to get Last record added to the table, you should first get an idea on table dattype..
1) Whether any sequence no in any column defined which will update when ever row is inserted.
2) will get by retrieving max rowid from the table

Answered by: udit on: Jun 16th, 2014

Your answer is wrong because min should be replaced with max.

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: kranthiswaroop on: Jun 16th, 2014

Hi Neha,

According to me, As Commit is executed on both session though for truncate its not required. Current no data present in table, so both will take same time while executing select stmt.

Answered by: rahulnanda on: Jun 7th, 2014

Truncate is permanently remove from database so once u commit after truncate the data.u cant rollback.but even after deleting the data using delete command.even after commit we can get the data

Write SQL query to get the below required output

Asked By: hraju_tt | Asked On: Jun 13th, 2013

I have table with col1 values , col2 1st row value should be same col1 1st row, then col 2 row onwards row each should added each row values eg: col1 values 10,20,30. Etc, in col2 values display like 10, 30,60 etc. How we can write SQL query ?

Answered by: kranthiswaroop on: Jun 16th, 2014

Hi Hraju, Please check this code given, its works as per your scenario. SELECT s, CASE WHEN rnk = 1 THEN s WHEN rnk > 1 THEN ...

Answered by: kranthiswaroop on: Jun 16th, 2014

/* Formatted on 6/16/2014 5:08:51 PM (QP5 v5.114.809.3010) */ SELECT s, CASE WHEN rnk = 1 THEN s WHEN rnk > 1 THEN ...

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: kranthiswaroop on: May 6th, 2014

Hi Sanjoy, Its a simple logic that, one should have complete idea about the Table , Table fields and data types used.. before applying any DML DCL DDL Commands, a programmer shoud know this. A progra...

Answered by: Megamind on: Apr 14th, 2014

Select column_name from all_tab_columns where table_name=megamind;

How to convert rows in column using SQL in Oracle

Asked By: Sukanta2013 | Asked On: Aug 14th, 2013

How to convert rows in column using SQL in Oracle; for example sl_no 1 2 3 4 output is 1234; how to convert one column value into rows using SQL for example employee_name kumar output k u m a r

Answered by: Megamind on: Apr 14th, 2014

We can do it via pivot keyword..... it will change the row into column.. its simply a pseudo column

Answered by: Kandavel Durairaj on: Nov 8th, 2013

Using LISTAGG. Read the oracle documentation for more info.

Can we have a commit statement inside a trigger? If no why cant we?

Asked By: suresh | Asked On: Oct 21st, 2005

Answered by: Gangadhar on: Apr 1st, 2014

Because of its violation.

Answered by: Arun Maity on: Feb 17th, 2014

No we cannot have commit or rollback statement in trigger because its a part of a transaction.

And trigger is not auto commit.

Literal meaning of SQL

Asked By: ssrao1985 | Asked On: Jan 28th, 2008

What is the literal meaning of SQL

Answered by: bhagare on: Feb 5th, 2014

Structure quary launguge

Answered by: nagarjuna on: Nov 21st, 2013

The terms literal and constant value are synonymous and refer to a fixed data value.For example, JACK, BLUE ISLAND, and 101 are all character literals

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: srini on: Dec 6th, 2013

Using PIVOT we can do this:

http://www.dba-oracle.com/t_pivot_examples.htm

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;

First | Prev | | Next | Last Page

 

 

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

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.