# SQL Interview Questions

Showing Questions 1 - 20 of 246 Questions
First | Prev | Next | Last Page
Sort by:
|
•

#### SQL - Find top two salary for each department

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;

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

•

#### Sql query to obtain 2nd highest mark in each subject for a student

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;

•

#### Query to find the list of employees age > 30

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

`CodeSELECT * FROM USER WHERE (YEAR(CURDATE()) - YEAR(DOB))>30;`

•

#### SQL Query to find out the Maximum marks for each subject

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

•

#### Find second lowest salary from emp table

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

•

#### How to list employees names hired AFTER a certain date

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;

•

#### 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

•

#### Difference between VARCHAR and VARCHAR2?

• 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

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

•

#### Use of VIEW

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

•

#### What is the value of comm and sal after executing the following query if the initial value of ‘sal’ is 10000?

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

• Apr 26th, 2018

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

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

•

#### Question on Constraints

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

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

•

#### How to retrieve a second highest salary from a table?

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

• Feb 23rd, 2018

`CodeSELECT 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

•

#### Find the Latest Salary of each Employee in Employer table

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

•

#### Alternative NULL values

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

•

#### Display Column as Rows

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.

•

#### Query to Display Employee Hike Details

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

•

#### Reverse Duplicate

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;

•

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.

•

#### What is difference between Co-related sub query and nested sub query?

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

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

•

#### How Select query works in Oracle

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

Showing Questions 1 - 20 of 246 Questions
First | Prev | Next | Last Page
Sort by:
|