## Register Now

It will take less than 1 minute to register for lifetime. Bonus Tip - We don't send OTP to your email id Make Sure to use your own email id for free books and giveaways

## Basic Queries to get you started

I hope you have already read the introduction part where we discussed about the execution flow of all the clauses in SQL.

Today we will discuss around 15 questions which shall give you a good launching pad to directly jump on the complicated problems. You can, but you should not skip this article. Just go through it even if you are the king of SQL because “Why be a king, when you can be the God” (Comment if you know the song :P)

Your time is precious, so is mine. So, let’s start.

P.S. – Try to check the number of questions which you can answer by yourself(including the correct syntax). Check your score and it’s meaning at the end of the article

1. Select the maximum and second maximum salary from the employee table.

```SELECT
(SELECT MAX(Salary) FROM Emp) Max_Salary,
(SELECT MAX(Salary) FROM Emp
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Emp)) as Second_max_salary```

2. Get all employee detail from EmployeeDetail table whose “FirstName” not start with any single character between ‘a-p’

`SELECT * FROM EmployeeDetailWHERE FirstName like ‘[^a-p]%’`

3. Write the query to get the branch and branch wise total(sum) admission fees, display it in ascending order according to admission fees.

```Select Branch,
from Student
group by Branch

4. Get department name and average salary of all the employees (department wise)

```Select Department,
AVG(Salary) as Average_Salary
From MuSigma
group by Department
order by Department```

5. Get department wise number of employees in Mu Sigma

```Select Department,
COUNT(*)
FROM MuSigma
group by Department
order by Department

```

6. Will the code below work?

```Select Name, Age as Actual_Age, Region
From Candidate
Where Actual_Age>35```

The above query will not execute. Where clause should have actual field names

7. 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);```

We need a table to understand Row_Number, Rank, etc.
The below table will be used in the next few queries

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

9. Write the syntax to create a new column using Row Number over the Salary column

`SELECT *, ROW_NUMBER() OVER (Order By Salary) as Row_NumFROM Employee`

Output

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

Output

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

12. 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
From Employee```

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

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

Output

14. 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 NtileFROM Employee`

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

15. Can we use HAVING without GROUP BY?
Have you heard it lately that Having can be used only with group by?
It’s actually true, but can we write a query using Having without group by?

Yes we can, but it won’t get you anything fruitful 😛

`Select * From EmployeeHaving 1=1`

Having is used to filter groups .
Where clause is used to filter rows.

12+ correct answers -> Good enough to move to the next article
8-12 correct answers -> Go through sqlzoo website to chisel your skills
4-8 correct answers -> Go through either tutorialspoint or w3school before proceeding
0-4 -> Bhai kar kya rhe ho jindgi ke saath ? 100-200 leke koi nya course join kr lo 😛

We will discuss some more questions in the next article.

Keep Learning 🙂

The Data Monk

1. 