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’
WHERE 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, SUM(Admission_Fee) as Total_admission_fees from Student group by Branch order by SUM(Admission_fee) ASC
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_Num
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
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
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
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 Ntile
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 😛
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