Tricky Sql Queries For Interview
Advanced SQL Queries for Interview Preparation
Tricky Sql Queries For Interview
Tricky SQL queries are commonly used in interviews to assess a candidate's problem-solving and SQL skills. These queries often involve complex joins, subqueries, aggregations, and functions to test the depth of the candidate's understanding of SQL concepts. Some examples include finding the second highest salary in a table, identifying duplicate records, handling null values, and performing advanced data manipulations. It's important for candidates to be familiar with common SQL interview questions and practice writing and optimizing complex queries to succeed in these types of assessments.
To Download Our Brochure: https://www.justacademy.co/download-brochure-for-free
Message us for more information: +91 9987184296
1 - Find the second highest salary from an Employee table: You can use the following SQL query to identify the second highest salary from an Employee table
```sql
SELECT MAX(salary) as SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);
```
2) Display the nth highest salary from an Employee table: To retrieve the nth highest salary from an Employee table, you can use the following query
```sql
SELECT DISTINCT salary as NthHighestSalary
FROM Employee e1
WHERE n = (SELECT COUNT(DISTINCT salary)
FROM Employee e2
WHERE e1.salary ≤ e2.salary);
```
3) Check for duplicate records in a table: To identify duplicate records in a table, you can execute the below query
```sql
SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
```
4) Retrieve alternate rows from a table: Use the following query to fetch alternate (odd or even) rows from a table
```sql
SELECT *
FROM (
To Download Our Brochure: Click Here
Message us for more information: +91 9987184296
SELECT *, ROW_NUMBER() OVER(ORDER BY column_name) as rowNum
FROM table_name
) t
WHERE MOD(t.rowNum, 2) = 1;
```
5) Calculate the running total in SQL: To compute the running total of a column in a table, utilize the below query
```sql
SELECT column1, column2,
SUM(column2) OVER (ORDER BY column1) as running_total
FROM table_name;
```
6) Delete duplicate records while keeping one instance: Execute the following query to remove duplicates and retain a single instance
```sql
DELETE FROM table_name
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM table_name
GROUP BY column1, column2);
```
7) Find the top nth salary using the LIMIT keyword: You can find the nth highest salary using the LIMIT keyword as follows
```sql
SELECT salary
FROM Employee
ORDER BY salary DESC
LIMIT n 1, 1;
```
8) Retrieve records with the highest frequency: To extract records with the highest frequency, employ the below query
```sql
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
ORDER BY COUNT(*) DESC
LIMIT 1;
```
9) Implement a self join to compare two rows in a table: Perform a self join to compare two rows in a table with this query
```sql
SELECT e1.column1,
e2.column1
FROM table_name e1,
table_name e2
WHERE e1.common_column = e2.common_column
AND e1.column1 <> e2.column2;
```
Browse our course links : https://www.justacademy.co/all-courses
To Join our FREE DEMO Session: Click Here
Contact Us for more info:
- Message us on Whatsapp: +91 9987184296
- Email id: info@justacademy.co
Full Stack Web Developer Interview Questions
Javascript Interview Questions In Hindi
