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

Silpa

• Apr 18th, 2018

Select employee_id,salary, rownumber from (Select employee_id,salary,row_number() over (order by salary desc) as rownumber from hr.employees)
where rownumber = n;

VISHWA

• Mar 10th, 2018

Here for ROWNUM =1 it will work but for ROWNUM = 2 and so on it will not work.

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

muralir02  

 Profile Answers by muralir02 Questions by muralir02

• Member Since Mar-2006 | Mar 20th, 2006

---

select max(sal) from emp where sal<(select max(sal) from emp);

sampra

 Profile Answers by sampra Questions by sampra

• Mar 6th, 2012

Select salry from Hello h where 2=(select count (distinct salry) from hello where h.salry

srivathson

• Feb 20th, 2012

Code
 

Improve Answer

Read Best Answer

Editorial / Best Answer

Answered by: Mohan

• 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

Raj

• Mar 7th, 2019

These are following some advantages
1. Performance
2. Security
3. Easy and Reusable
4. Consistency and independent

Marcus

• Apr 6th, 2018

The view uses virtual space when in use, this is a temporary space on the server that goes away when not in use.

Improve Answer

Read Best Answer

Editorial / Best Answer

maverickwild  

 Profile Answers by maverickwild Questions by maverickwild

• Member Since Nov-2005 | Nov 15th, 2005

---

Try this

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

snehal Falke

• Jul 13th, 2012

Select * FROM emp ORDER BY sal ASC LIMIT 2

mohit

• Jun 28th, 2012

If you want to employee name then query is given below

Code
 

Improve Answer

There is a table having the following columns :-student id marks1 marks2 maxmarks1 10 20 202 25 30 303 30 10 304 35 25 355 20 40 40write a query to update column maxmarks such that maxmarks column contains whatever be the greater value among marks1...

indra.prasad

 Profile Answers by indra.prasad Questions by indra.prasad

• Jul 12th, 2010

CREATE TABLE aa(name varchar(10), marks1 int, marks2 int, maxmarks int);INSERT INTO aa VALUES ('a',10,20,null);INSERT INTO aa VALUES ('b',20,25,null);UPDATE aa SET maxmarks= CASE WHEN marks1 > marks2 THEN marks1 ELSE marks2 END;

Palash Biswas

 Profile Answers by Palash Biswas Questions by Palash Biswas

• May 20th, 2010

Declare @tbl_Test TABLE([Name] Nvarchar(255),Mark1 int,mark2 int,maxmark int) Insert into @tbl_Test([Name] ,Mark1 ,mark2) VALUES ('a',50,20) Insert into @tbl_Test([Name] ,Mark1 ,mark2) VALUES ('b',10,...

Answer Question  Select Best Answer

Arbind Kumar Singh

• Jul 23rd, 2012

sql query for retrieving last row particular column value depending upon its ID??? QUERY: select top 1 [column_name1],[column_name2] from [table_name] where ID=[ID_value] order by index desc here i...

Ambarish

• Apr 19th, 2012

Select last(column_name) from Table.

Answer Question  Select Best Answer

pinky

• May 31st, 2017

SELECT * FROM (SELECT DEPT_ID , DENSE_RANK(SALARY) OVER(PARTITION BY DEPT_ID ORDER BY SALARY ) DRANK FROM DEPARTMENTS ) WHERE DRANK = 2;

KAMLESH

• Dec 29th, 2016

Code
 

Answer Question  Select Best Answer

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.

bibek

• Jul 2nd, 2021

This is nice and simple way to solve.. thank you

kishanjha

 Profile Answers by kishanjha Questions by kishanjha

• May 20th, 2017

Code
 

Answer Question  Select Best Answer

Asish

• Jan 7th, 2018

Yes, if the view is derived from one base table. Here you can execute all the DML opperation.

No, if the view derived from multiple base table.

Sushil Tembhekar

• Dec 16th, 2017

In SQL it is impossible to update the View.
but, By using "Instead of Trigger" clause in PL/ SQL, it is possible to update the View.

Answer Question  Select Best Answer

Suresh Babu Nallamothu

• Nov 8th, 2018

No,Because only one primary key is available for the one table. If you want to use morethan one primary key in a table,it is called as composite primary key.

Code
 

Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• Sep 23rd, 2011

Yes, a primary key can contain more than one one column. Per EF Cobb's definition, a primary key is the sole candidate key on a relation variable (table) formed from one or more attribute values (c...

Answer Question  Select Best Answer

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 to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL statements...

Manjunath

• Sep 1st, 2015

Hello, Given example PL/SQL program is comes under Implicit, Bcz 1. if its Explicit then cursor has to be declared in declarative part along with SELECT statement to retrieve the data from Database ...

Justo

• Jul 15th, 2015

Its an implicit cursor.

Answer Question  Select Best Answer

I am trying to get second highest age from the student table.Student table:SQL> desc student; Name Null? Type ----------------------------------------- -------- ----------------- ROLLNO NUMBER NAME VARCHAR2(20) AGE NUMBERAnd...

Sunil Badam

• Jan 29th, 2016

We can use rank function with partition by. It is very simple to use.

Code
 

Neeraj

• Jan 7th, 2016

Code
 

Answer Question  Select Best Answer

Vamsikrishna Sudanagunta

• Nov 16th, 2022

Print first names and the last names of the artists who had zero sales or no sales?

Ekansh

• Jul 12th, 2022

Just add the ; in the end of quest :)

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: Praveena

• Dec 14th, 2005

---

Yes we can alter Column name in a table using the following query:

alter table <tablename> rename column <old Column Name> to <New Column Name>

Regards,

Praveena

 

 

dude_8550

 Profile Answers by dude_8550 Questions by dude_8550

• Jun 15th, 2011

U can change the column name and also column definition that is whether the data type and its size by the ALTER command.ALTER TABLE RENAME COLUMN <old column name> TO <new coumn name>;

Santhoshkandula

 Profile Answers by Santhoshkandula Questions by Santhoshkandula

• May 11th, 2011

   yes, it's possible to change the column name by using the following query in SQL server       SP_RENAME ' <TABLE_NAME.OLD_COLUMN_NAME> ' , ' NEW_COLUMN_NAME '   Thanks & Regards    K.Santhosh

Improve Answer

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

suganthi

• Jul 3rd, 2015

How to display data from table values without using select command?

kranthiswaroop

 Profile Answers by kranthiswaroop Questions by kranthiswaroop

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

Answer Question  Select Best Answer

Bharathiarun

• Aug 20th, 2015

Even if the Id is varchar, the rows are ordered by rownum.

Anand Prakash Joshi

• Aug 10th, 2015

Code
 

Answer Question  Select Best Answer

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 why?

sumanamara2016

 Profile Answers by sumanamara2016 Questions by sumanamara2016

• Mar 18th, 2016

TRUNCATE TRUNCATE does not return number of deleted rows. TRUNCATE TABLE always locks the table and page but not each row. DELETE When we have to use DELETE Command then the data deleted can be ...

Mohammad Mansoor

• Feb 24th, 2016

Truncate and Drop commands Rollback is not possible for Table, whereas Delete Command we rollback the Data

Answer Question  Select Best Answer

Maria

• May 16th, 2017

With GROUP BY statement.

Palak

• Apr 20th, 2017

By using Unique Command

Answer Question  Select Best Answer

How to display middle record in a given table?

Bibhudatta Panda

• Jun 18th, 2019

Suppose I want middle record 5-9 records

Code
 

shreyas varchasvi

• Oct 15th, 2018

In rownum we cant use >= operator, only < ,< = operator will work.

Answer Question  Select Best Answer

Bibhuprasad Behera

• Dec 4th, 2015

Can you please send me the example of the trigger?

Nivetha Thinakaran

 Profile Answers by Nivetha Thinakaran Questions by Nivetha Thinakaran

• May 5th, 2015

In an autonomous transactions, we can have commit inside trigger.

Answer Question  Select Best Answer