## SQL

In this section, you will have a lot of SQL questions. So, if you think you are good with SQL, then do try these to become even better.

Promotion bhai đ

112 Questions To Crack Business Analyst Interview Using SQL

Write better SQL queries + SQL interview Questions

For the next 7-8Â questions, we will be referring to the below table

Emp. ID | Name | Salary |

123 | Amit | 50000 |

453 | Sumit | 50000 |

232 | Rakshit | 30000 |

124 | Aman | 40000 |

543 | Rahul | 30000 |

**1. What is ROW_NUMBER() function in SQL?**

â The ROW_NUMBER() ranking returns a unique and sequential number to

each row of the table without repeating or skipping any number. If there are two rows which are partitioned and holds the same value as Amit and Sumit, then the row number will be given randomly.**2. Write the syntax to create a new column using Row Number over the Salary column**

SELECT *, ROW_NUMBER() OVER (Order By Salary) as Row_Num

FROM Employee**Output**

Emp. ID | Name | Salary | Row_Num |

232 | Rakshit | 30000 | 1 |

543 | Rahul | 30000 | 2 |

124 | Aman | 40000 | 3 |

123 | Amit | 50000 | 4 |

453 | Sumit | 50000 | 5 |

**3. What is PARTITION BY clause?**

PARTITION BY clause is used to create a partition of ranking in a table. If you partition by Salary in the above table, then it will provide a ranking based on each unique salary. Example below:-

SELECT *, ROW_NUMBER() OVER (PARTITION BY Salary ORDER BY Salary) as Row_Num

Emp. ID | Name | Salary | Row_Num |

232 | Rakshit | 30000 | 1 |

543 | Rahul | 30000 | 2 |

124 | Aman | 40000 | 1 |

123 | Amit | 50000 | 1 |

453 | Sumit | 50000 | 2 |

**4. What is a RANK() function? How is it different from ROW_NUMBER()?**

â RANK() function gives ranking to a row based on the value on which you want to base your ranking. If there are equal values, then the rank will be repeated and the row following the repeated values will skip as many ranks as there are repeated values row. Confused?? Try out the example below:-

SELECT *, RANK() OVER (ORDER BY Salary) as Row_Num

FROM Employee

Output

Emp. ID | Name | Salary | Row_Num |

232 | Rakshit | 30000 | 1 |

543 | Rahul | 30000 | 1 |

124 | Aman | 40000 | 3 |

123 | Amit | 50000 | 4 |

453 | Sumit | 50000 | 4 |

As you can see, the rank 2 has been skipped because there were two employees with the same Salary and the result is ordered in ascending order by default.**5. How to use PARTITION BY clause in RANK() function?**

â There is no point using PARTITION BY clause with RANK() function. The ranking result will have no meaning, as the rank will be done according to Salary values per each partition, and the data will be partitioned according to the Salary values. And due to the fact that each partition will have rows with the same Salary values, the rows with the same Salary values in the same partition will be ranked with the value equal to 1.

SELECT *, RANK() OVER (PARTITION BY Salary ORDER BY Salary) as Row_Num

OUTPUT

Emp. ID | Name | Salary | Row_Num |

232 | Rakshit | 30000 | 1 |

543 | Rahul | 30000 | 1 |

124 | Aman | 40000 | 1 |

123 | Amit | 50000 | 1 |

453 | Sumit | 50000 | 1 |

**6. What is Dense Ranking?**

â DENSE_RANK() is similar to the RANK() function but it does not skip any rank, so if there are two equal values then both will be termed as 1, the third value will be termed as 3 and not 2.

Syntax:-

SELECT *, DENSE_RANK() OVER (PARTITION BY Salary ORDER BY Salary) as Row_Num

FROM Employee

Output:-

Emp. ID | Name | Salary | Row_Num |

232 | Rakshit | 30000 | 1 |

543 | Rahul | 30000 | 1 |

124 | Aman | 40000 | 3 |

123 | Amit | 50000 | 4 |

453 | Sumit | 50000 | 4 |

432 | Nihar | 60000 | 6 |

**7. What is NTILE() function?**

-NTILE() is similar to percentile NTILE(3) will divide the data in 3 parts.

SELECT *, NTILE() OVER (ORDER BY Salary) as Ntile

FROM Employee

The number of rows should be 6/3 = 2, therefore we need to divide the 2 rows for each percentile

Emp. ID | Name | Salary | Ntile |

232 | Rakshit | 30000 | 1 |

543 | Rahul | 30000 | 1 |

124 | Aman | 40000 | 2 |

123 | Amit | 50000 | 2 |

453 | Sumit | 50000 | 3 |

432 | Nihar | 60000 | 3 |

**8. How to get the second highest salary from a table?**

Select MAX(Salary)Â

from EmployeeÂ

Where Salary NOT IN (SELECT MAX(Salary) from Employee)Â **9. Find the 3rd Maximum salary in the employee table**

-Select distinct sal

from emp e1

where 3 = ((select count(distinct sal) from emp e2 where e1.sal <= e2.sal);**10. Get all employee detail from EmployeeDetail table whose âFirstNameâ not start with any single character between âa-pâ**

â SELECT *Â

FROM EmployeeDetail

WHERE FirstName like â[^a-p]%â**11. How to fetch only even rows from a table?**

-The best way to do it is by adding a row number using ROW_NUMBER() and then pulling the alternate row number using row_num%2 = 0

Suppose, there are 3 columns in a table i.e. student_ID, student_Name, student_Grade. Pull the even rows

SELECT *Â

FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY student_ID) as row_num FROM student) x

WHERE x.row_num%2=0Â **12. How to fetch only odd rows from the same table?**

-Simply apply the x.row_num%2 <> 0 to get the odd rows

SELECT *Â

FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY student_ID) as row_num FROM student) x

WHERE x.row_num%2 <> 0Â **13. How to find the minimum salary usingÂ subquery?**

-SELECT *Â

FROM employeeÂ

WHERE salary = (select MIN(salary) from employee);**14. How to find the second minimum salary?**

â SELECT *

FROM employee

WHERE salary = (SELECT MIN(salary) FROM employee > SELECT MIN(salary) FROM employee)

Similarly, find the third minimum salary

â SELECT *

FROM employee

WHERE salary = (SELECT MIN(salary) FROM employee > SELECT MIN(salary) FROM employee > SELECT MIN(salary) FROM employee)**15. The above query is too lengthy, write a query to get the third minimum salary with some other method.**

â SELECT DISTINCT (salary)Â

FROM emp e1 where 3 = (SELECT COUNT(DISTINCT salary) FROM emp e2 WHERE e1.sal >= e2.sal);Â **16. How to get 3 Min salaries?**

-SELECT DISTINCT salary FROM emp a WHERE 3 >= (SELECT COUNT(DISTINCT salary) FROM emp b WHERE a.salary >= b.salary);**17. Some basic SQL Select questions**

â SELECT 125

125

-SELECT âAnkitâ+â1â

Ankit1

-SELECT âAnkitâ+1

Error

â SELECT â2â+2

4

-SELECT SUM(â1â)

1Â **18.Â **

SELECT CURDATE(); Ă It returns the current date(MySQL)

SELECT NOW(); Ă It returns the current date and time(MySQL)

SELECTÂ getdate(); Ă It returns the current date and time(SQL Server Query)

SELECT SYSDATE FROM DUAL Ă This Oracle query returns the current date and timeÂ **19. Explain Scalar functions.**

Scalar functions are those functions which are used to return a single value based on the input. Following are the commonly used scalar functions in SQL:-

1. UCASE() â Convert to Upper Case

2. LCASE() â Convert to Lower Case

3. MID() â It extracts a substring from a string. SELECT MID(âABCDEFGHâ,3,6) AS ExtractedString

It will extract 6 characters starting from 3^{rd}Â character i.e. C,D,E,F,G,H

4. FORMAT() â It specifies the display format

5. LEN() â Gives the length of a text field

6. ROUND() â Rounds up the decimal field in a numberÂ **20. Write a generic method to fetch the nth highest salary without TOP or Limit**

â

SELECT Salary

FROM Worker W1

WHERE n-1 = (

Â SELECT COUNT( DISTINCT ( W2.Salary ) )

Â FROM Worker W2

Â WHERE W2.Salary >= W1.Salary

Â );Â **21. ** **What is the order of execution in a query?**

The order of query goes like this:-

FROM â Choose and join tables to get the raw data

WHERE â First filtering condition

GROUP BY â Aggregates the base data

HAVING â Apply condition on the base data

SELECT â Return the final data

ORDER BY â Sort the final data

LIMIT â Apply limit to the returned data

**22.**

**What is wrong with the following query?**

SELECT Id, Year(PaymentDate) as PaymentYear

FROM Bill_Table

WHERE PaymentYear > 2018;

SELECT Id, Year(PaymentDate) as PaymentYear

FROM Bill_Table

WHERE PaymentYear > 2018;

Though the variable PaymentYear has already been defined in the first line of the query, but this is not the correct logical process order. The correct query will beÂ

SELECT Id, Year(PaymentDate) as PaymentYear

FROM Bill_Table

WHERE Year(PaymentDate) > 2018;

**23.**

**What is Normalization? How many Normalization forms are there?**

Normalization is used to organize the data in such manner that data redundancy will never occur in the database and avoid insert, update and delete anomalies.

There are 5 forms of Normalization

**First Normal Form (1NF):**Â It removes all duplicate columns from the table. Creates a table for related data and identifies unique column values

**Second Normal Form (2NF):**Â Follows 1NF and creates and places data subsets in an individual table and defines the relationship between tables using a primary key

**Third Normal Form (3NF):Â**Follows 2NF and removes those columns which are not related through the primary key

**Fourth Normal Form (4NF):**Â Follows 3NF and do not define multi-valued dependencies. 4NF also known as BCNF

More SQL questions in the links given below:

1. SQL interview Questions

2. SQL basics to kick off

3. SQL one table, 30 Questions

4. SQL Theoretical Questions

Also, Visit the interview Questions to find more questions on the same line.

Keep Learning đ

The Data Monk