Common Database Interview Questions

SQL query to delete duplicate rows

Delete duplicate rows using Common Table Expression(CTE)

   WITH CTE AS

(
SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
FROM MyTable
)

DELETE FROM CTE WHERE RN<>1

 

Ref: http://www.besttechtools.com/articles/article/sql-query-to-delete-duplicate-rows

strongly recommend to follow this article ::http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/

nth highest salary in mysql

Solution to finding the 2nd highest salary in SQL
Now, here is what the SQL will look like:

SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )

SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

Ref : https://www.programmerinterview.com/database-sql/find-nth-highest-salary-sql/


Display Nth Record from Employee table

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

OR

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
            *
        FROM
            Table_1
    ) sub
WHERE
    RowNum = 23


ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

SELECT department_id, last_name, employee_id, ROW_NUMBER()
   OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
   FROM employees;

DEPARTMENT_ID LAST_NAME                 EMPLOYEE_ID     EMP_ID
------------- ------------------------- ----------- ----------
           10 Whalen                            200          1
           20 Hartstein                         201          1
           20 Fay                               202          2
           30 Raphaely                          114          1
           30 Khoo                              115          2
           30 Baida                             116          3
           30 Tobias                            117          4
           30 Himuro                            118          5
           30 Colmenares                        119          6
           40 Mavris                            203          1
. . .
          100 Popp                              113          6
          110 Higgins                           205          1
          110 Gietz                             206          2

The following inner-N query selects all rows from the employees table but returns only the fifty-first through one-hundredth row:

SELECT last_name FROM
   (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees)
   WHERE R BETWEEN 51 and 100;

Ref : https://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table#42765
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm


Display first 5 Records from Employee table

will work with above query with rownum<6

Find duplicate rows in table.

option 1 :

SELECT
    a,
    b,
    COUNT(*) occurrences
FROM t1
GROUP BY
    a,
    b
HAVING

    COUNT(*) > 1;

option 2 :

Using common table expression  (Read this fir CTE https://www.sqlservertutorial.net/sql-server-basics/sql-server-cte/)

WITH cte AS (
    SELECT
        a,
        b,
        COUNT(*) occurrences
    FROM t1
    GROUP BY
        a,
        b
    HAVING
        COUNT(*) > 1
)
SELECT
    t1.id,
    t1.a,
    t1.b
FROM t1
    INNER JOIN cte ON
        cte.a = t1.a AND
        cte.b = t1.b
ORDER BY
    t1.a,
    t1.b;

Option 3 :

Using ROW_NUMBER

WITH cte AS (
    SELECT
        col,
        ROW_NUMBER() OVER (
            PARTITION BY col
            ORDER BY col) row_num
    FROM
        t1
)
SELECT * FROM cte
WHERE row_num > 1;

Ref : https://www.sqlservertutorial.net/sql-server-basics/sql-server-find-duplicates/


How to find Nth highest salary from a table

DENSE_RANK
DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER.

https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-dense_rank-function/



Option 1 :
Using DENSE_RANK()

select * from(
select ename, sal, dense_rank()
over(order by sal desc)r from Employee)
where r=&n;

https://www.geeksforgeeks.org/find-nth-highest-salary-table/



SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) rn FROM Employee e ) WHERE rn = N; /*N is the nth highest salary*/

Read more: https://javarevisited.blogspot.com/2016/01/4-ways-to-find-nth-highest-salary-in.html#ixzz6A4ttYeVW