Given a table with 3 columns StudentName,Subject and Marks.Write a sql to retrieve second highest Marks Obtained for each student.

Zienab

• Mar 26th, 2022

Select Z.first_name, Z.last_name, Z.salary From (SELECT EMP.first_name, EMP.last_name, EMP.salary, RANK() OVER( Order by EMP.salary DESC) as Ranking FROM employees EMP ) Z Where Z.Ranking = 2;

Code
 

Rachana J K

• Jan 10th, 2022

Select student_name, marks from Student order by marks desc limit(2,1) group by sub-name;

Answer Question  Select Best Answer

Students table contains studentid, subjectid and marks. Where there are 10 subjects and 50 students. what is the Query to find out the Maximum marks obtained in each subject.

Pendo

• Jul 17th, 2021

SELECT subjectID, MAX(Marks) as Max_Mark FROM Students
GROUP BY subjectID

subh

• Apr 25th, 2020

No output display

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: Ranjeet

• Feb 27th, 2006

---

Emp_name varchar(10) -  if you enter value less than 10 then remaining space can not be deleted. it used total 10 spaces.

Emp_name varchar2(10) - if you enter value less than 10 then remaining space is automatically deleted

Krishna Kant Kumar

• Mar 12th, 2019

VARCHAR is going to be replaced by VARCHAR2 in next version. So, Oracle suggests the use VARCHAR2 instead of VARCHAR while declaring data type. VARCHAR can store up to 2000 bytes of characters while ...

Prabhat Sahu

 Profile Answers by Prabhat Sahu Questions by Prabhat Sahu

• Oct 30th, 2018

CHARACTER Datatype: CHAR, VARCHAR both are of CHARACTER Datatype in oracle. CHAR is fixed length datatype and VARCHAR is Variable length datatype to store character data. i.e. A-Z , a-z , 0-9 and key...

Improve Answer

I am new to Oracle. I have created one table in that one column name salary, while creating the table I have given the datatype for that column is varchar2 and also I have given not null constraint. Now I want to change the datatype varchar2 to number, Is it possible to alter the datatype when having constraint in table?

DevaNaidu

 Profile Answers by DevaNaidu Questions by DevaNaidu

• Mar 7th, 2018

If you want change only varchar2 to number you can change by using ALTER, but you also defined NOT NULL constraints..so if you need to change a datatype along with Not null..you must use MODIFY key word

Abhimanyu eati

• Nov 17th, 2017

Yes we can change datatype but make sure about size if you have created varchar(5) then alter to number(5)

Answer Question  Select Best Answer

How to retrieve a second highest salary from a table?
Note:Suppose salaries are in duplicate values
eg:
Name Sal
Malli 60000
Pandi 60000
Rudra 45000
Ravi 45000

Pankaj Yadav

• Feb 23rd, 2018

Code
 

Santhosh Kumar Gujja

• Nov 27th, 2017

SEL SAL FROM(
SEL DISTINCT SAL FROM TABLE
) O QUALIFY ROW_NUMBER() OVER(ORDER BY SAL DESC)=2

Answer Question  Select Best Answer

Co-related sub query is one in which inner query is evaluated only once and from that result outer query is evaluated.Nested query is one in which Inner query is evaluated for multiple times for gatting one row of that outer query.ex. Query used with IN() clause is Co-related query. Query used with = operator is Nested query

Abdul Rahman

• Jul 18th, 2017

A nested sub query is one sub query inside another sub query. A correlated sub query is a sub query that references a column from a table which appears in the parent statement.

qptopm

 Profile Answers by qptopm Questions by qptopm

• Oct 13th, 2016

Co-Related Vs Nested-SubQueries. Technical difference between Normal Sub-query and Co-related sub-query are: 1. Looping: Co-related sub-query loop under main-query; whereas nested not; therefore...

Answer Question  Select Best Answer

When we give SELECT * FROM EMP; How does oracle respond?

Abdul Rahman

• Jul 18th, 2017

All rows from the EMP table are returned (output).

Sandip Muke

• Jul 29th, 2016

First it will do the syntax/ semantic check and if it is correct then an only that statement forward to the Oracle RDBMS server. After that Oracle will check whether that user has privilege or not. Th...

Answer Question  Select Best Answer

Question: student marks stable "sp_report"
sid maths chem phy engl
1 33 45 54 48
2 55 32 50 37
3 10 20 30 40

The required o/p is (SQL QUERY TO GET THE BELOW O/P)
sid highest_marks
1 54
2 55
3 40

Paresh

• Jun 24th, 2016

Code
 

Ade

• Jun 23rd, 2016

Code
span style="color: #ff0000;">"highest_marks"

Answer Question  Select Best Answer

I have table with one column. type varchar..

PHONE
---------------
99samn44423m44
8034skdf3453a2
909090abc4567d

i want to get numeric values.. i mean my output will be

PHONE
---------------
994442344
80343453a2
9090904567

thanks in advance..
Sateesh

Ganezh

• Apr 20th, 2016

Code
 

Krishna

• Mar 30th, 2016

Code
 

Answer Question  Select Best Answer

How to write a query to list the items in ascending order of Name and within Name, in descending order of Salary

vaibhav

• Jan 11th, 2016

Code
 

cocoole

 Profile Answers by cocoole Questions by cocoole

• Jan 7th, 2016

Order by name, sal desc

Answer Question  Select Best Answer

How to delete duplicate records without using rownum/rowid concept?

cocoole

 Profile Answers by cocoole Questions by cocoole

• Jan 7th, 2016

Delete rowid not in (select the max rowid and group by each col {find the no duplicate table})

Answer Question  Select Best Answer

What is the use Index Organized Table (IOT)?

Vishal BHOSALE

• Nov 16th, 2015

Index-organized tables have full table functionality. They support features such as constraints, triggers, LOB and object columns, partitioning, parallel operations, online reorganization, and replica...

Answer Question  Select Best Answer

abid

• Jul 30th, 2015

ALTER USER ABC IDENTIFIED BY XYZ; if user does not exist it will create user

laxmi

• Apr 28th, 2015

Yes, create user identify by
Its created by DBA

Answer Question  Select Best Answer

Vipin

• Jul 7th, 2015

It is always a good practice to create a Clustered Index on the Primary key and make sure that the Primary key is sequential. A table can have only one clustered index where as a table can have unlimi...

saira banu

• Jun 10th, 2015

Table

Answer Question  Select Best Answer

If i have two table T1 having data of column C1,C2,C3 and C4 and T2 having data of column C5,C6,C7 and C8,How to copy Data of T2 to T1 or T1 to T2

Aakash

• Mar 22nd, 2015

INSERT INTO T1
(
SELECT
C5,
C6,
C7,
C8
FROM T2)

smamish

 Profile Answers by smamish Questions by smamish

• Mar 22nd, 2015

Code
 

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: Sushant

• Oct 25th, 2006

---

The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

Sameer

• Nov 1st, 2012

Now SQL SERVER 2008 onwards we can have 1 cluster index and 999 non cluster index on one table.

primarykey2000

 Profile Answers by primarykey2000 Questions by primarykey2000

• Apr 22nd, 2008

clustered index  - the index order matches the order of physically stored data.cluster index can be used on table that doent get changes frequentlyusing DML statement on cluster index column have...

Improve Answer

Read Best Answer

Editorial / Best Answer

nirmal1in  

 Profile Answers by nirmal1in Questions by nirmal1in

• Member Since Jan-2010 | Jan 17th, 2010

---

This can be accomplished in following way:

Example: table - emp

For First n rows:

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a < 5 --- say n is 5 display first 5 records

For last n rows:

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename) a
FROM emp) x
WHERE x.a < 5

FROM )WHERE .a<

This query can be used for finding nth row also say n = 5

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a = 5

deepthi

• May 17th, 2012

Code
 

Vasavi Katakam

 Profile Answers by Vasavi Katakam Questions by Vasavi Katakam

• Jul 18th, 2011

For First N Rows:

Code

1.  

For Last N Rows:

Code

1.  

Improve Answer

Read Best Answer

Editorial / Best Answer

Answered by: MANAS KUMAR ROUT ( USA ) (TCS) (Eli LILLY ) - PFIZ

• Jul 6th, 2007

---

Hey Ramesh,

I understand your interviewer's enthusiasm to know about the Foreign Key.? See foreign key definitions says its states the relationship of one attribute(Column) of one table to the corresponding column on the other table.

So one os a parent and other is child. PARENT is that table which holds the primary key and CHILD is table which holds the referential key. Hence every entry in the PRIMARY table is unique where as same entry might be repeating in the child table. BUT that same has to be present in the PARENT table (Primary KEY).

Now Going by definition Foreign key can be of two types :
========================================
1.? ON DELETE SET NULL
2.? ON DELETE SET CASCADE

DETAILS:
=======
On delete set null :
=======
When a foreign key is created by on delete set null definition then when you delete one row from the primary?column (of parent table ) , then the corresponding entry in the foreign key table (Child table ) have the value "NULL" for that particular column.


ON Delete Set Cascade:
================
When the foreign key is created by this definition then when you delete the primary column (any one row -unique data) , then the Child table forcefully deletes all the rows in the child table having same value for that particular column.


I hope u got the crux of the Foreign key
Thanks
Manas Kumar Rout
SAS Consultant
Oracle Certified Associate
Unix Consultant

shweta dhumal

• Sep 8th, 2011

foreign key is primary key of other table. for example: 'STUDENT' is one table which stores information about students and 'SID' is primary key in that table and 'MARKS' is another table which stor...

shweta dhumal

• Sep 8th, 2011

The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted. For example, say we have two tab...

Improve Answer

What is dual table which is used as a table in sql...

Read Best Answer

Editorial / Best Answer

svsraju81  

 Profile Answers by svsraju81 Questions by svsraju81

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

---

Dual table is a temporary table, it contains a single row and single column. If we return a data from dual table it gives a single row.

sudipta kumar dhal

• Sep 5th, 2011

Dual table is called a dummy table because it is not meant for storing any useful data.

d_pranjal

 Profile Answers by d_pranjal Questions by d_pranjal

• Sep 5th, 2010

Dual table is owned by SYS. Data dictionary come under SYS, dual is part of data dictionary. Dual table consist only one row and only one column containing value x

Improve Answer

I have a very unusual question, how can one check which is the recent row added to a table?

Read Best Answer

Editorial / Best Answer

biswaranjanbehera  

 Profile Answers by biswaranjanbehera Questions by biswaranjanbehera

• Member Since Jul-2008 | Aug 6th, 2008

---

We can select the recent row enrtered by using the query below.
suppose the table name is employee;
then.
select * from employee where rowid=(select max(rowid) from employee).
This works fine because rowid is assigned for each and every record and in accending order. So the recent record's rowid is the man (rowid). Please send feedback with any concern.

bulat

 Profile Answers by bulat Questions by bulat

• May 31st, 2010

"Although a rowid uniquely identifies a row in a table, it might change its value if the underlying table is an index organized table or a partitioned table." Also rowid stores information a...

dachi316

 Profile Answers by dachi316 Questions by dachi316

• Aug 26th, 2009

SCOPE_IDENTITY() would be a better option to get the most recently added row as the primary key for a table may not necessarily be an auto-incrementing variable. Therefore max(rowid) would not be the optimal solution in that case.

Improve Answer