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

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

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

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

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

Mohammad salman

• Sep 29th, 2018

Its not working,,,,
error is that


Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword As.

sampra

 Profile Answers by sampra Questions by sampra

• Mar 6th, 2012

Create table emp as select * from student where 1=2;

Answer Question  Select Best Answer

Dheeraj Singh

• Sep 16th, 2018

You can try this one.I think this query can work..

Select * from tbl_regex Where REGEXP_LIKE(ENAME,^0123456789);

Note:- tbl_regex is table name and ENAME is column name that contain numeric and string type data.

Asish

• Jan 7th, 2018

Here the where clause will be either

Lower(fn) = upper(fn)

Or

Regexp_like(fn, ^[0-9]*$)

Answer Question  Select Best Answer

Where clause restricts rows, what does having clause restricts ?1. only group results.2. rows results.3. both rows and groups result.

Read Best Answer

Editorial / Best Answer

ajaysinghnegi  

 Profile Answers by ajaysinghnegi Questions by ajaysinghnegi

• Member Since Mar-2009 | Mar 27th, 2009

---

1) HAVING clause can only be used to filter out the aggegate functions whereas WHERE clause cannot filter out the aggegate functions.
2) HAVING clause can be used with GROUP BY function where as WHERE clause cannot be used with group by function.

gubba123

 Profile Answers by gubba123 Questions by gubba123

• Aug 8th, 2010

Example: Write a query to display the Managers who are working for more than one Departments:Select  emp_id, count(distinct dept_id)     From emp     Where...

Tartauris

 Profile Answers by Tartauris Questions by Tartauris

• Feb 16th, 2010

Having is used for agregate results while where is used for unagregated results.

Improve Answer

How to rename a column in a SQL table?

Read Best Answer

Editorial / Best Answer

sravee123  

 Profile Answers by sravee123 Questions by sravee123

• Member Since May-2008 | Jun 10th, 2008

---

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

Ex: ALTER TABLE employee RENAME COLUMN eno to empno;

srikanth.cherukupalli

 Profile Answers by srikanth.cherukupalli Questions by srikanth.cherukupalli

• Jan 12th, 2012

Code
 

karthickrfwin

 Profile Answers by karthickrfwin Questions by karthickrfwin

• Sep 22nd, 2011

Use this Script for nename particular ColumnName in SqlTable

sp_rename 'TableName.[ColumnNameOld]','[ColumnNameNew]','Column'

Improve Answer

Read Best Answer

Editorial / Best Answer

Srinivas4u  

 Profile Answers by Srinivas4u Questions by Srinivas4u

• Member Since Jul-2008 | Jul 16th, 2008

---

I am sure that a group by clause can have all columns which are there in that table,not sure if you are looking for anotehr answer

akshya dash

• Jul 25th, 2011

there can be n no of columns in a group by clause other than group functions for ex select ename, deptno max(sal) from emp group by ename,deptno; here max(sal) is a group function and ename and ...

sasanka

• Jul 21st, 2011

In GroupBy clause contains the no of columns in the select Statement to retrieve the data.

Improve Answer

'' Should be used before '%'.

Nazeera Jaffar

• Oct 6th, 2012

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

Code
 

Nazeera Jaffar

• Sep 30th, 2012

The below code will select the row having column as %

Code
 

Answer Question  Select Best Answer

VIJAI VEDHA

• Sep 28th, 2022

ITS syn NOT sys,

SuryaRS

 Profile Answers by SuryaRS Questions by SuryaRS

• Aug 29th, 2017

TAB will give tables, views whereas CAT will give tables, views, sequences. TAB is having clustered column.

Code
 

Answer Question  Select Best Answer

Bobby

• Apr 18th, 2018

Rowid is pseudo column and it can change internally especially on transactional table

himabindu

• Apr 16th, 2018

Why use rownum < 2?

Answer Question  Select Best Answer

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.

Read Best Answer

Editorial / Best Answer

Answered by: Vijay Raj Jaiswal

• Sep 20th, 2005

---

Hi this is modified answer ,pls post this instead of previous which i post

Primay Key                     Unique key

-----------                  ------------------

There is only one         there may be more than 1
Primary key for           Unique Key in table
1 table

It can not contain           It Can contain Null Value
Null value

Lakshmanan

• Jun 30th, 2017

Primary key is combination of not null and unique. Primary key does not insert a null value and insert value is unique. Unique is one time accepted null value and insert a data is uniquely

ankitagujrati28

 Profile Answers by ankitagujrati28 Questions by ankitagujrati28

• Apr 30th, 2010

Unique key means we have to enter unique value but we can enter null.But in case of Primary key we should have to enter Unique + Not Null value in that particular column.

Improve Answer

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 used to format query result, Set options, Edit SQL commands and PL/SQL.

qptopm

 Profile Answers by qptopm Questions by qptopm

• May 12th, 2016

PL/SQL provides the capability of the programming language and supports the above mentioned list. It also supports running of SQL statements inside the PL/SQL blocks. SQL provides access to the database for retrieving data from the objects. However SQL does not have the programming capabilities.

Das

• Nov 4th, 2015

Simply put, like a command prompt in Windows Operating system, we have SQL PLUS tool in Oracle DBA.

Answer Question  Select Best Answer