1. top 2 salary in each dept2. 2nd maximum sal in each dept3. 2nd minimum sal in each dept.

Prasanth

• Sep 12th, 2022

SELECT d.department_id, d.salary, d.employee_id FROM
(SELECT employee_id, department_id, salary, dense_rank() over(partition by department_id order by salary desc ) as salary_DenseRank from employees) d
WHERE d.salary_DenseRank =2;

ashadevi21

 Profile Answers by ashadevi21 Questions by ashadevi21

• Sep 27th, 2011

The Same can be modified and can be used for 2nd maximum and 2nd minimum as well For 2nd Maximum =========== SELECT dept_id, salary FROM (SELECT dept_id, salary ...

Answer Question  Select Best Answer

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

Write a query to find the list of employees whos age is greater than 30 or so given a date of birth column

Shailesh Bhilare

• Dec 6th, 2021

There is a table employee (empid,empname,DOB). Get the emp count against the age bracket of less than or equal to 30 and above 30.

Could you pls give the oracle syntax for tge above Query..?

Piyush Kumar

• May 4th, 2019

Curdate() will give you the current date. here we are subtracting from dob to current date(year).

Code
SELECT * FROM USER WHERE (YEAR(CURDATE()) - YEAR(DOB))>30;

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

Display the second lowest salary from emp table without using min & all

Pendo

• Jul 17th, 2021

SELECT Top (1) Salary FROM Employee ORDER BY Salary

Code
 

satyam singh

• Apr 22nd, 2020

"HAVING" clause is required. It works with "GROUP BY" clause

Answer Question  Select Best Answer

I need to list the employees hired after any random date, lets say 10th December 2010 in ascending order and minus their salary by 100% how would I do this?

Pendo

• Jul 17th, 2021

SELECT * FROM Employees WHERE CAST(DATE AS DATE)>20101210

Pratiksha Chacan

• Aug 24th, 2019

Select * from employee e where to_char(hiredate, YYYY)=2019;

Answer Question  Select Best Answer

Pendo

• Jul 17th, 2021

WITH Xlist as (
SELECT salary ,rank() over( order by salary desc ) as rnk
FROM salary)
SELECT salary from Xlist where rnk =2;

Akila

• Oct 11th, 2018

SELECT *
FROM
(SELECT personid ,row_number() over (order by PersonID desc) as row_num FROM
(SELECT DISTINCT personid FROM persons))
WHERE row_num = 2

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

Describe use of VIEW in SQL Server?

Krishnaveni

• Aug 10th, 2018

A view is virtual table created using the real table. End users can easily access the data with out altering any table definition by using views. We can create, replace or drop the view depending on ...

Answer Question  Select Best Answer

UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;sal = 11000, comm = 1000

Madhumita

• Apr 26th, 2018

It will give
comm=1200 and sal=1000
I have executed and got the result in MySQL

souravbhattacharya1

 Profile Answers by souravbhattacharya1 Questions by souravbhattacharya1

• Aug 22nd, 2011

This Query will work fine. SQL> select * from emp where sal=10000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GENDER ----- ---------- --------- ----- ----------- -------...

Answer Question  Select Best 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
SELECT max(salary) FROM employee WHERE salary <> (SELECT max(salary) FROM employee)

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

How to find the latest salary of each employee in the Employer table?
emp.id sal year month
1001 5000 2015 3
1001 3000 2014 4
1002 4000 2013 3
1003 2000 2013 2

SANTHOSH KUMAR GUJJA

• Nov 26th, 2017

SEL EMP.ID,SAL FROM
EMPLOYER QUALIFY ROW_NUMBER() OVER(PARTITION BY EMP.ID ORDER BY YEAR,MONTH DESC)=1

anagogia

• Sep 29th, 2016

The query has to be a nested query because one employee could have the max (year||month) = 201503 where he had max salary and other could have max(year||month) = 201603 where he had the max salary ......

Answer Question  Select Best Answer

Source:
col1 col2
1 Will
2 John
3 Josh
4 Devin

Target:
col1 col2
1 Will
2 NULL
3 John
4 NULL
5 Josh
6 NULL
7 Devin
8 NULL

I want the SQL query for the...

SANTHOSH KUMAR GUJJA

• Nov 26th, 2017

SEL CASE WHEN COL1=1 THEN COL1 ELSE COL1+MIN(COL1) OVER(ORDER BY COL1 ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) END AS
COL1,COL2 FROM TABLE
UNION
SEL COL1,NULL AS COL2 FROM TABLE WHERE COL11

Rakesh

• Mar 2nd, 2017

SELECT COL1, CASE
WHEN MOD(COL1,2) = 0 THEN NULL
ELSE
COL2
END CASE
FROM G2

Answer Question  Select Best Answer

There is a table T with two columns C1 and C2.
The data is as below:
C1 C2
1 4
2 5
3 6

display the result as :

1 2 3
4 5 6

Santhosh Kumar Gujja

• Nov 27th, 2017

Sel max(case when rn=1 then c1 end),max(case when rn=2 then c1 end),max(CASE WHEN RN=3 THEN c1 end)
union all
sel max(case when rn=1 then c2 end),max(case when rn=2 then c2 end),max(CASE WHEN RN=3 THEN c2 end)2
(
SEL c1,c2,row_number() over(order by C1) as rn
)O

vikas bose

• Nov 3rd, 2017

In MySQL, we have one function GROUP_CONCAT(field_value, separator). It will give single row.
SELECT GROUP_CONCATE(field_Value, ,) FROM table_name where 1 group by field_name.

Answer Question  Select Best Answer

EID ENAME SAL SALMONTH
101 xyz 100 Jan 2015
101 xyz 200 Feb 2015
102 Abc 300 Feb 2015

I want output like this
Eid Ename hike
101 xyz 100
102 Abc 0

Akash

• Aug 15th, 2017

Can you elaborate what you are solving its not about duplicates

dan

• Jul 31st, 2015

The question is poorly stated, perhaps partially stated as well. But it is good that basic sample data is given. Does the table have data only for Jan 2015 and Feb 2015.? What if the salary changes we...

Answer Question  Select Best Answer

How to delete reverse duplicate in oracle.
e.g i/p
col1 col2
hyd blr
mum del
del blr
blr hyd
blr del

i want in o/p
col1 col2
hyd blr
mum del
del blr

Priyanka Jaiiswal

• Aug 17th, 2017

SELECT a.col1 ,a.col2 from t1 a
where a.col1 < = a.col2

manish

• Feb 7th, 2017

SELECT a.mem_name,a.loc_name
FROM TAB4 a
WHERE a.loc_name = a.mem_name;

Answer Question  Select Best Answer

Not known

Abdul Rahman

• Jul 18th, 2017

Sql Server is the Database offering/ product of Microsoft. Examples of other database products are Oracle, MySql, etc. SQL is the query language that is used to handle/ manipulate data in the database...

amit

• Jun 12th, 2017

SQL is a command language to perform operations on database. SQL server in the underlying software that will accept the SQL commands given to it to perform the operations in data handled/ stored by server.
e.g. .net is language and Visual studio is software to run it.

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