GeekInterview.com
Series: Subject: Topic:

Oracle SQL Interview Questions

Showing Questions 1 - 20 of 192 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: 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.

Answered by: Pooja Thakur on: Aug 11th, 2013

Adv- hide complexity

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: Jessica on: Feb 5th, 2014

Please read the question then answer it; post the answer after verify the results.

Answered by: vishwanath on: Jan 23rd, 2014

Truncate:- 1)The number of deleted rows are not returned .2)It is auto commit
Delete:- 1) The number of deleted rows are returned .3) It is not auto commi.

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: Ankur on: Jan 1st, 2014

truncate is faster than delete because truncate is not delete data only deal locate data and is exists still shrink the space ya overwrite the data.

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.

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 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: Kandavel Durairaj on: Nov 8th, 2013

Using LISTAGG. Read the oracle documentation for more info.

Answered by: Gayathri on: Aug 28th, 2013

we can convert rows into columns using Decode or Case function

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

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

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

Answered by: plsqlgeek on: Aug 21st, 2013

Explicit cursor. Here we do not require a cursor definition, open, fetch and close here because CURSOR FOR LOOP was used. You can use SQL%ROWCOUNT inside the loop to see how many rows it returns, if it were a implicit cursor. You will find NULL, thats because its an explicit cursor.

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: AZHAR on: Jun 27th, 2013

SELECT &COL FROM ODS_PRODUCT_DIM IF YOU RUN THE ABOVE QUERY IT WILL PROMPT YOU FOR THE COLUMN NAME FROM SPECIFIC TABLE. THIS WORKS

Code
  1. SELECT &COL
  2. FROM ODS_PRODUCT_DIM

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]

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: thilak on: Jun 17th, 2013

Insert values into table(col1(10,20,30),col2(20,30,60),col3(30,40,60));

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)

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

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

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

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.