Tech Point Fundamentals

Friday, July 29, 2022

Home / SQL-Interview-Questions / sql-query-interview-questions / SQL Interview Questions and Answers - Part 10

SQL Interview Questions and Answers - Part 10

by on in ,

SQL Interview Questions and Answers - Part 10

sql-interview-questions-answers

Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see Top 100+ SQL Interview Questions with Answers. 

Please visit our YouTube Channel for Interviews and other videos by below link:




Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.




Introduction


This is the 10th part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.

I will highly recommend to please read the previous parts over here before continuing the current part:





SQL Interview Questions and Answers - Part 10


Q79. How can you get the 3rd highest salary in SQL?

There are so many ways to get the 3rd highest salary in SQL:






Getting 3rd Highest Salary using SubQuery:

SELECT TOP 1 Salary FROM
(
SELECT DISTINCT TOP 3 Salary FROM Payroll
ORDER BY Salary DESC
) SubQueryResult
ORDER BY Salary;








Getting 3rd Highest Salary using CoRelated SubQuery:

SELECT  TOP 1 Salary FROM Payroll  OuterSubQuery
 WHERE 2 = (
                SELECT COUNT(DISTINCT InnerSubQuery.Salary) 
      FROM Payroll InnerSubQuery 
              WHERE InnerSubQuery.Salary > OuterSubQuery.Salary);

Please watch the complete video of SubQuery and CoRelated SubQuery here.


Getting 3rd Highest Salary using Derived Table:

SELECT TOP 1 Salary FROM
(
SELECT Salary,  
  DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DenseRank 
FROM Payroll
) DT
WHERE DenseRank = 3;

Please watch the complete video of Derived Table here.







Getting 3rd Highest Salary using CTE:

WITH CTE AS
(
  SELECT Salary,  
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DenseRank 
  FROM Payroll
) 
SELECT Salary FROM CTE WHERE DenseRank = 3;

Please watch the complete video of CTE here.


Getting 3rd Highest Salary using OFFSET and FETCH:

SELECT 
            DISTINCT Salary 
FROM Payroll 
Order By Salary DESC 
OFFSET 2 ROWS 
FETCH NEXT 1 ROWS ONLY;



Getting 3rd Highest Salary using IN Operator:

 SELECT 
        DISTINCT TOP 1 Salary 
FROM [dbo].[Payroll] 
WHERE Salary NOT IN 
   (
        SELECT DISTINCT TOP 2  Salary 
        FROM [dbo].[Payroll] 
        ORDER BY Salary DESC
);

Please watch the complete video of IN vs NOT IN here.





Please watch the complete video of IN vs NOT IN here.


Getting 3rd Highest Salary using EXCEPT or MINUS Operator:

SELECT * FROM
(SELECT DISTINCT TOP 3  Salary FROM Payroll Order By Salary DESC) Tmp
EXCEPT
SELECT DISTINCT TOP 2  Salary FROM Payroll Order By Salary DESC;


Please watch the complete video of EXCEPT vs MINUS here.

Please visit this article for different ways to get the 3rd highest salary in SQL. You can also watch the video for the same here.








Q80. How can you delete the duplicate records from a table? 

There are so many different ways to check and delete duplicate records as well. But there is a difference between delete and truncate.






For each DELETE query here I have kept the first inserted record as the original record. If you want to keep the last inserted record as the original record, you can change the condition accordingly.






Deleting Duplicate Records using IN Clause:

DELETE FROM Customer
WHERE ID NOT IN
(
SELECT MIN(ID) AS MinRecordID
FROM Customer
GROUP BY Name, Email
);

Please watch the complete video of IN Clause here.


Deleting Duplicate Records using CoRelated SubQuery:

DELETE C1 FROM Customer C1 
WHERE ID > 
( 
SELECT MIN (ID) FROM Customer C2 WHERE C1.Email = C2.Email
);

Please watch the complete video of CoRelated Subquery here.


Deleting Duplicate Records using JOIN:

DELETE C FROM Customer C
INNER JOIN
(   SELECT MIN(ID) AS MinID, Name, Email,
COUNT(1) AS TotalRecordCount
FROM Customer
GROUP BY Name, Email
HAVING COUNT(1) > 1 
) TMP ON C.ID > TMP.MinID AND (C.Name = TMP.Name AND C.Email = TMP.Email);

Please watch the complete video of INNER JOIN here.







Deleting Duplicate Records using RANK Function:

DELETE C FROM Customer C
INNER JOIN
(   SELECT  
ID, 
RANK() OVER(PARTITION BY Name, Email ORDER BY ID) AS RankNo
FROM Customer
) TMP ON TMP.ID = C.ID
WHERE TMP.RankNo > 1;

Please watch the complete video of the RANK vs ROW NUMBER Function here.







Deleting Duplicate Records using ROW NUMBER Function:

DELETE C FROM Customer C
INNER JOIN
(   SELECT  
ID, 
Row_Number() OVER(PARTITION BY Name, Email ORDER BY ID) AS RowNo
FROM Customer
) TMP ON TMP.ID = C.ID
WHERE TMP.RowNo > 1;



Deleting Duplicate Records using CTE:

; WITH CTE 
AS 
(
SELECT Id,Name, Email,
ROW_NUMBER() OVER(PARTITION BY Name, Email ORDER BY ID) AS RowNo                                         
FROM Customer
)
DELETE FROM CTE WHERE RowNo > 1;








Deleting Duplicate Records using DT:

DELETE FROM DT 
FROM 
(
SELECT Id, Name, Email,
RANK() OVER(PARTITION BY Name, Email ORDER BY ID) AS RankNo                                         
FROM Customer
) DT
WHERE RankNo > 1;

Please read the complete article for removing duplicate records here. Please watch the different ways to remove Duplicate Records video here.



Q81. How can you update the records of a table from other table values?

For updating the table record from another table, you have to use INNER JOIN. But if you have to update a column with another column of the same table you have to use SELF JOIN.

UPDATE targetTable
SET targetTable.Name = sourceTable.Name + '_Updated'
FROM EmployeeTableSchema targetTable
JOIN Employee sourceTable ON targetTable.Id = sourceTable.Id;

Please read the complete article here for more details.



Q82. How can you delete the records of a table from other table values?

For updating the table record from another table, you have to use INNER JOIN. Please note that you have to use the "FROM" clause two times in the case of DELETE. But it is not true in the case of the previous Update command.

DELETE FROM targetTable
FROM EmployeeTableSchema targetTable
JOIN Employee sourceTable ON targetTable.Id = sourceTable.Id;

Please read the complete article here for more details.



Q83. How can you create a table from another table without using CREATE command?

For this, you have to use the SELECT INTO command with a false condition in the WHERE clause.

SELECT *
INTO EmployeeTableSchema
FROM Employee
WHERE 1 = 0;

Please read the complete article here for more details.








Q84. How can you clone the table with schema and data?

For this, you have to use the SELECT INTO command.  You can also use the WHERE clause to filter the records before inserting them into the new table.

SELECT * 
INTO #tmpEmployeeTableClone 
FROM Employee;

Please read the complete article here for more details.



Q85. How can you insert data from a table into another table without any loop or cursor?

For this, you have to use the INSERT INTO SELECT command. But the target table must exist already.

INSERT INTO EmployeeTableSchema
(Name, Email, MobileNumber, EmployeeRoleId)
SELECT Name, Email, MobileNumber, EmployeeRoleId 
FROM Employee;

Please read the complete article here for more details.



Q86. How can you insert the result of a stored procedure into a table in SQL? 

For this, you have to use the INSERT INTO EXEC command, but the target table must exist already.

INSERT INTO EmployeeTableSchema
(
Id,
Name, 
Email, 
MobileNumber,
EmployeeRoleId
)
EXEC procGetEmployees;

Please read the complete article here for more details.



Q87. How can you insert multiple records using a single INSERT command in SQL?

For this, you have to specify all the rows by separating commas in the VALUES.

INSERT INTO EmployeeTableSchema
(Name, Email, MobileNumber, EmployeeRoleId)
VALUES ('James', 'james@gmail.com', '9845372890', 1),
('Thomas', 'thomas@gmail.com', '9745372890', 2),
('Peter', 'peter@gmail.com', '9645372890', 3),
('Sid', 'sid@gmail.com','9445372890', 4)

Please read the complete article here for more details.



Q88. How can you get the last inserted record id in SQL?

There are different ways to get the last inserted record like @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT, but each has its own pros and cons. 

@@IDENTITY returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
 
SELECT @@IDENTITY;
 


SCOPE_IDENTITY() returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.

SELECT SCOPE_IDENTITY();

IDENT_CURRENT() returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
 
SELECT IDENT_CURRENT(TableName);
 
Apart from this, you can also use the MAX() function as well to get the last inserted value explicitly, if there is no Identity column in the table.
 
SELECT MAX(ID) AS LastID FROM TableName;




Q89. How can you update the gender column of the table to 'M' if 1, 'F' if 2, and 'U' if NULL?

UPDATE Employee
SET Gender =
    ( CASE Gender
                WHEN 1 THEN 'M'
        WHEN 2 THEN 'F'
                ELSE 'U'
        END
    )
To Be Continued Part-11...


Recommended Articles






Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK  for regular updates.

    

You May Also Like:
By at
Labels: ,

No comments:

Post a Comment

Please do not enter any HTML. JavaScript or spam link in the comment box.

Subscribe to: Post Comments (Atom)