GeekInterview.com
Series: Subject: Topic:

Oracle SQL Interview Questions

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

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

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

Answered by: Sanjay salunkhe on: Mar 13th, 2014

You can perform DML operation on view.
All views based on select statement,View is compiled format of select statement.
If table gets deleted then view remains same and if table get drop then view gets deleted.

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;

How centralized DBMS differs from distributed DBMS .

Asked By: dharmendra | Asked On: Oct 9th, 2007

Answered by: ambika on: Oct 21st, 2013

Recovery in centralized dbms

Answered by: su4surya on: Aug 24th, 2009

A centralized database has all its data at one place so there may occur problems of data avalability, and a system crash may lead to whole dataloss. In a distributed database, database is stored on se...

Is there any function in Oracle to check whether a value is numeric or not. I have a column which contains both numeric and alphanumeric data. I want to fetch only the numeric data from that column. Can...

Asked By: anu_dw | Asked On: Sep 14th, 2006

Answered by: Breta on: Aug 28th, 2013

It is faster, but do not solve case when value is "123 456" which is not numeric, but will pass your condition.
Cheers

Answered by: me on: Jul 3rd, 2012

Hello,

Using the upper/lower functions is faster than the regular expression approach.

Cheers

When do you use where clause and when do you use having clause?

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

Having clause is used when you want to specify a condition for a group function and it is written after group by clause. The where clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before group by clause if it is used.

Answered by: Mushtaque on: Aug 25th, 2013

WHERE clause can contains condition that must be met and should directly follow the from clause. And HAVING clause can precede GROUP BY clause,but it is more logical to declared in after GROUP BY clau...

Answered by: Santhoshkandula on: May 11th, 2011

   we have to apply the condition Before grouping the records then you have to use WHERE clause.   we have to apply the condition after grouping the records then you have to apply HAVING clause.  Thanks & Regards    K.Santhosh

What is set transaction command in SQL? What is its use. Explain with examples

Asked By: ramprasadkala | Asked On: Jan 25th, 2013

Answered by: Ehsan Rehmani on: Aug 6th, 2013

The set transaction statement used to establish the current transaction as read only or read write, establish its isolation level or assign it to a specified rollback segment or when someone use DDL statement.

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.