SQL Interview Questions

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

    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.

    SANTHOSH KUMAR GUJJA

    • Nov 25th, 2017

    SEL STUDENTNAME,MARKS FROM TABLE
    QUALIFY ROW_NUMBER() OVER(PARTITION BY STUDENTNAME ORDER BY MARKS DESC)=2

    Rakesh

    • Aug 23rd, 2016

    Try to avoid row_number while solving this kind of data because you will get wrong data when the starting record are duplicate. Try to use dense_rank

  •  

    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

    Santhosh Kumar Gujja

    • Nov 27th, 2017

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

    nagendra

    • Nov 11th, 2017

    SELECT * from TableName order by Column Desc limit 1 offset 1 ; (Give value in n for n+1th highest value)

  •  

    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 25th, 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 25th, 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.

  •  

    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?

    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)

  •  

    Find second lowest salary from emp table

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

    Sudeep Mohan Nayak

    • Oct 12th, 2017

    Code
    1. SELECT MIN(Sal)
    2. FROM Emp
    3. WHERE Sal > (
    4.                      SELECT MIN(Sal)
    5.                      FROM Emp
    6.                     );

  •  

    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.

    pushpanshu kumar

    • Sep 13th, 2017

    Thanks for this...really useful with simple query, got my final output with help of this.

  •  

    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

    mukund

    • Sep 7th, 2017

    SELECT `First_Name`, SYSDATE(), `Date_Of_Birth`, DATEDIFF( SYSDATE(), `Date_Of_Birth` )/365
    FROM `members` WHERE (DATEDIFF( SYSDATE(), `Date_Of_Birth` )/365)>15;

    nisha

    • Jul 7th, 2017

    Select * from table where ((sysdate-DOB)/365)>30;

  •  

    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;

  •  

    What is the difference between SQL and SQL Server?

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

  •  

    How to retrieve uncommon fields from two different tables?

    How can i retrieve uncommon fields from two different tables along with the data??? say i have table A and table B and Tbl A has x,y,z fields and Tbl B has x,a,b fields and i have to retrieve y,z,a,b fields from two tables along with the data.

    Divya

    • Jun 5th, 2017

    You can try below example query using the 2columns in this query : Select ContactName,FirstName from Customers C, Employees E where C.ContactName not in( E.FirstName)

    Ade

    • Jun 27th, 2016

    Code
    1. SELECT Y,Z AS TableA FROM Tbl A A UNION SELECT a,b AS TableB FROM Tbl B

  •  

    Write a query for split one column to multiple columns

    I have a source table with one column of 15 characters and I want output like divide the 15 chars column into multiple of 3 columns like every column 5 chars

    source empnoorderprdct target empno order prdct

    Divya

    • Jun 5th, 2017

    You can use Substring function to split the column values to 3 columns

  •  

    Difference between VARCHAR and VARCHAR2?

    Star 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

    Soniya

    • May 17th, 2017

    VARCHAR: Variable-length, non-Unicode character data. The database collation determines which code page the data is stored using.
    NVARCHAR: Variable-length Unicode character data. Dependent on the database collation for comparisons.

    akhil

    • Mar 2nd, 2017

    If so what is the difference between Varchar and Char.
    As per your explanation varchar similar to char

  •  

    Correlated Sub Query

    Compose an SQL statement to show a list of two least expensive vendors (suppliers) for each raw material. In the result table, show Material ID, Material Description, Vendor ID, Vendor Name, and Supplier’s unit price. Sort the result table by Material_ID and unit price in ascending order. Note: if a raw material has only one supplier(vendor), that vendor should also be in the result (output) table...

  •  

    SQL Query to convert single row from multiple tables into single row in a table

    I have a scenario like Have 7 tables. All these 7 tables have same metadata structure and also all have 2 columns. In that, One column is same in all 7 tables (Joining column, But not a primary column) and another column has different values.

    Below have a example for 2 tables. How can we achieve for 7 tables?

    Tab1
    ====
    loc_name mem_name
    HYD ...

Showing Questions 1 - 20 of 244 Questions
First | Prev | Next | Last Page
Sort by: 
 | 
Jump to Page:

 

Have Interview Question?

Please select the most appropriate category and mention a brief question title along with clear question details.

Ask Question

Login to Ask Question or Register your free account   

Name:
Email:
Category :
Sub Category :
Question Title:
Question in Detail :
Job Role (Optional) :
Company (Optional) :
 

Optional Features

Register at GeekInterview

Register me.

Subscribe to GeekInterview Newsletter

Yes, Subscribe me to Interview & Career Tips