Find out nth highest salary from emp table?

Find out nth highest salary from emp table?
by

1 Answer

Answer :

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: 2
SAL
---------
3700
by

Related questions

maxvalue.sql Select the Nth Highest value from a table?

Description : maxvalue.sql Select the Nth Highest value from a table?

Last Answer : select level, max('col_name') from my_table where level = '&n' connect by prior ('col_name') > 'col_name') group by level; Example: Given a table called emp with the following columns: -- id number -- name ... , max(sal) from emp -- where level=2 -- connect by prior sal > sal -- group by level

minvalue.sql Select the Nth lowest value from a table?

Description : minvalue.sql Select the Nth lowest value from a table?

Last Answer : select level, min('col_name') from my_table where level = '&n' connect by prior ('col_name') < 'col_name') group by level; Example: Given a table called emp with the following columns: -- id number -- name ... , min(sal) from emp -- where level=2 -- connect by prior sal < sal -- group by level

Can one export a subset of a table? (for DBA

Description : Can one export a subset of a table? (for DBA

Last Answer : From Oracle8i one can use the QUERY= export parameter to selectively unload a subset of the data from a table. Look at this example: exp scott/tiger tables=emp query=\"where deptno=10\"

How can you gather statistics on a table?

Description : How can you gather statistics on a table?

Last Answer : The ANALYZE command.

How would you determine who has added a row to a table?

Description : How would you determine who has added a row to a table?

Last Answer : Turn on fine grain auditing for the table.

Explain the use of table functions.

Description : Explain the use of table functions.

Last Answer : Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Description : A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Last Answer : Disable the foreign key constraint to the parent, drop the table, re- create the table, enable the foreign key constraint.

What type of index should you use on a fact table?

Description : What type of index should you use on a fact table?

Last Answer : A Bitmap index.

Compare and contrast TRUNCATE and DELETE for a table.

Description : Compare and contrast TRUNCATE and DELETE for a table.

Last Answer : Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just ... other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

Give two examples of how you might determine the structure of the table DEPT.

Description : Give two examples of how you might determine the structure of the table DEPT.

Last Answer : Use the describe command or use the dbms_metadata.get_ddl package.

How many columns can table have?

Description : How many columns can table have?

Last Answer : The number of columns in a table can range from 1 to 254.

Deleting the Duplicate rows in the table

Description : Deleting the Duplicate rows in the table

Last Answer : We can delete the duplicate rows in the table by using the Rowid

Table Space,Data Files,Parameter File, Control Files

Description : Table Space,Data Files,Parameter File, Control Files

Last Answer : Table Space :: The table space is useful for storing the data in the database.When a database is created two table spaces are created. a) System Table space :: This data file stores all the tables ... They contain the Db name, name and location of dbs, data files ,redo log files and time stamp.

Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

Description : Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

Last Answer : SELECT customer_no, payments from customer C1 WHERE 3

What are the data types allowed in a table ?

Description : What are the data types allowed in a table ?

Last Answer : CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW.

If a View on a single base table is manipulated will the changes be reflected on the base table ?

Description : If a View on a single base table is manipulated will the changes be reflected on the base table ?

Last Answer : If changes are made to the tables which are base tables of a view will the changes be reference on the view.

How many LONG columns are allowed in a table ? Is it possible to use LONG columns in WHERE clause or ORDER BY ?

Description : How many LONG columns are allowed in a table ? Is it possible to use LONG columns in WHERE clause or ORDER BY ?

Last Answer : Only one LONG columns is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

What is the fastest way of accessing a row in a table ?

Description : What is the fastest way of accessing a row in a table ?

Last Answer : Using ROWID.CONSTRAINTS

How do you find the numbert of rows in a Table ?

Description : How do you find the numbert of rows in a Table ?

Last Answer : A bad answer is count them (SELECT COUNT(*) FROM table_name) A good answer is :- 'By generating SQL to ANALYZE TABLE table_name COUNT STATISTICS by querying Oracle System Catalogues (e.g ... utility which Oracle released which makes it unnecessary to do ANALYZE TABLE for each Table individually.

How will you delete duplicating rows from a base table?

Description : How will you delete duplicating rows from a base table?

Last Answer : delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); or delete duplicate_values_field_name dv from table_name ta where rowid

Write the order of precedence for validation of a column in a table ?

Description : Write the order of precedence for validation of a column in a table ?

Last Answer : I. done using Database triggers. ii. done using Integarity Constraints.?

How many types of database triggers can be specified on a table ? What are they ?

Description : How many types of database triggers can be specified on a table ? What are they ?

Last Answer : Insert Update Delete Before Row o.k. o.k. o.k. After Row o.k. o.k. o.k. Before Statement o.k. o.k. o.k. After Statement o.k. o.k. o.k. If FOR EACH ROW clause is ... for each Row affected by the statement. If WHEN clause is specified, the trigger fires according to the returned Boolean value.

Where can one find I/O statistics per table? (for DBA

Description : Where can one find I/O statistics per table? (for DBA

Last Answer : The UTLESTAT report shows I/O per tablespace but one cannot see what tables in the tablespace has the most I/O. The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and ... For more details, look at the header comments in the $ORACLE_HOME/rdbms/admin/catio.sql script.

Committed block sometimes refer to a BASE TABLE ?

Description : Committed block sometimes refer to a BASE TABLE ?

Last Answer : False

How does Space allocation table place within a block ?

Description : How does Space allocation table place within a block ?

Last Answer : Each block contains entries as follows Fixed block header Variable block header Row Header,row date (multiple rows may exists) PCTEREE (% of free space for row updation in future)

How will you swap objects into a different table space for an existing database ?

Description : How will you swap objects into a different table space for an existing database ?

Last Answer : Export the user Perform import using the command imp system/manager file=export.dmp indexfile=newrite.sql. This will create all definitions into newfile.sql. Drop necessary objects. Run the script newfile.sql after altering the tablespaces. Import from the backup for the necessary objects.

What is the mechanism provided by ORACLE for table replication ?

Description : What is the mechanism provided by ORACLE for table replication ?

Last Answer : Snapshots and SNAPSHOT LOGs

Where can one find the high water mark for a table? (for DBA)

Description : Where can one find the high water mark for a table? (for DBA)

Last Answer : There is no single system table, which contains the high water mark (HWM) for a table. A table's HWM can be calculated using the results from the following SQL statements: SELECT BLOCKS FROM ... : You can also use the DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.

What is Table ?

Description : What is Table ?

Last Answer : A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

What is Oracle table?

Description : What is Oracle table?

Last Answer : A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

What is a Table?

Description : What is a Table?

Last Answer : A Table is the primary unit of physical storage in a database. Usually a database contains more than one table.

What is an Index Organized Table?

Description : What is an Index Organized Table?

Last Answer : An index-organized table (IOT) is a type of table that stores data in a B*Tree index structure. Normal relational tables, called heap-organized tables, store rows in any order (unsorted). In contrast ... all the columns of the table (an exception to this rule - is being called the overflow area).

Can you Redefine a table Online?

Description : Can you Redefine a table Online?

Last Answer : Yes you can. In any database system, it is occasionally necessary to modify the logical or physical structure of a table to: ▪ Improve the performance of queries or DML ▪ Accommodate application ... space used by the table being redefined. More space may be required if new columns are added.

A table is having few rows, should you create indexes on this table

Description : A table is having few rows, should you create indexes on this table

Last Answer : Small tables do not require indexes; if a query is taking too long, then the table might have grown from small to large. You can create an index on any column; however, if the ... , creating an index on the column does not increase performance and the index takes up resources unnecessarily.

How do you collect statistics for a table, schema and Database?

Description : How do you collect statistics for a table, schema and Database?

Last Answer : Using DBMS_STATS package to gather Oracle dictionary statistics.

How often you should collect statistics for a table?

Description : How often you should collect statistics for a table?

Last Answer : Analyse if it's necessary! - Refresh STALE statistics before the batch processes run but only for tables involved in batch run, - Don't do it if you don't have to. - Oracle ... has default, scheduled job "gather_stats_job" that analyses stats on a daily basis during the maintenance window time.

Will a user be able to modify a table with SELECT only privilege?

Description : Will a user be able to modify a table with SELECT only privilege?

Last Answer : He won’t be able to UPDATE/INSERT into that table, but for some reason, he will still be able to lock a certain table.

In which view can you find information about every view and table of oracle dictionary

Description : In which view can you find information about every view and table of oracle dictionary

Last Answer : DICT or DICTIONARY

How can you check the structure of a table from sqlplus?

Description : How can you check the structure of a table from sqlplus?

Last Answer : DESCRIBE or DESC

Out of 38 ATP molecules produced per glucose, 32 ATP molecules are formed from NADH/FADH2 in (a) respiratory chain (b) Krebs’ cycle (c) oxidative decarboxylation (d) EMP.

Description : Out of 38 ATP molecules produced per glucose, 32 ATP molecules are formed from NADH/FADH2 in (a) respiratory chain (b) Krebs’ cycle (c) oxidative decarboxylation (d) EMP.

Last Answer : (a) respiratory chain

How protected are electric cars against EMP attacks?

Description : How protected are electric cars against EMP attacks?

Last Answer : They aren't any more protected than cars that rely on an internal combustion engine (which would also be affected). But while the threat is not completely insignificant, it's apparently not as big a ... more vehicles than are damaged by the EMP, the consequent loss of life, and multiple injuries.

Is North Korea really planning an EMP attack (More details)

Description : Is North Korea really planning an EMP attack (More details)

Last Answer : I'm sorry to make you worry but, yes, it is possible. However . most important integrated circuits are properly designed and rad. hardened to a certain level so they can take EMP of a certain level. ... frame? It's toast. Car engine controller? Most will be fine. Military stuff? EMP? What EMP?

Is there a positive charge EMP? Can electricity be contained?

Description : Is there a positive charge EMP? Can electricity be contained?

Last Answer : EMP stands for electro-magnetic pulse. Under those conditions the circuits are exposed to both electrical and magnetic fields. When a circuit and housing are designed you build them to satisfy ... so the device will still be working when only cockroaches and tardigrades are walking the earth.

Encode a message in an EMP burst?

Description : Encode a message in an EMP burst?

Last Answer : There is a form of communication called meteor burst or meteor backscatter, You can bounce your radio signals off the ionized trails left by the meteor passing through the atmosphere. It works.

If another country were to attack the United States with something like EMP (Electromagnetic Pulse) bombs and they effectively rendered most computers, automobiles, and computer assisted devices useless, would it spell complete doom for the country?

Description : If another country were to attack the United States with something like EMP (Electromagnetic Pulse) bombs and they effectively rendered most computers, automobiles, and computer assisted devices useless, would it spell complete doom for the country?

Last Answer : I would actually love to see this happen. It isn’t going back to the “dark ages.” It is going back to 1940.

Does any one know where can I get blueprints of an emp device?

Description : Does any one know where can I get blueprints of an emp device?

Last Answer : No no no. You just don't do this. You will be charged if you are found out. This is exactly the same thing as going into their house with a baseball bat. The mature and responsible ... the police and file noise pollution complaints every single time it happens. The squeaky wheel gets the grease.

A ginormous EMP hits the entire world, internet and every digital bit gets wiped. What would be the very first thing you usually do you’ll miss and the one you won’t?

Description : A ginormous EMP hits the entire world, internet and every digital bit gets wiped. What would be the very first thing you usually do you’ll miss and the one you won’t?

Last Answer : My vinyls still play

What countries, if any, have plans in place for EMP bursts or attacks to protect society and banking from going into apocalyptic scenarios?

Description : What countries, if any, have plans in place for EMP bursts or attacks to protect society and banking from going into apocalyptic scenarios?

Last Answer : Need Answer

What countries, if any, have plans in place for EMP bursts or attacks to protect society and banking from going into apocalyptic scenarios?

Description : What countries, if any, have plans in place for EMP bursts or attacks to protect society and banking from going into apocalyptic scenarios?

Last Answer : Need Answer

What is EMP Pathway? -Biology

Description : What is EMP Pathway? -Biology

Last Answer : answer: