SQL advance concepts
Advance concepts mostly involves knowledge of window functions, cleaning the data format, working with different date formats, etc. Before proceeding, you should actually go through different ways of casting data types and other widows functions.
1.Create the cumulative revenue column on a restaurant data where the table contains revenue of each of the restaurants and you are supposed to create another column having running sum
select R1.RestaurantId, R1.Revenue, SUM(R2.Revenue) as Cumulative_Revenue from Restaurant R1 inner join Restaurant R2 on R1.RestaurantId >= R2.RestaurantId group by 1,2 order by 1
2. What is the difference between group by and partition by?
Group by and partition by both are used on the complete dataset and is primarily used to apply functions on a different groups of the table.
On one hand, group by aims at reducing the number of rows of the table, but partition by does not reduce the number of rows.
Group by modifies the query but partition by works with ranking functions like row number(), rank(),dense_rank()
3. What are the different value related window function?
Lead() – The LEAD() window function returns the value for the row after the current row in a partition. If no row exists, null is returned
Lag() – The LAG() window function returns the value for the row before the current row in a partition. If no row exists, null is returned
Last_Value() – The FIRST_VALUE window function returns the value of the specified expression with respect to the first row in the window frame
First_Value() – The LAST_VALUE window function returns the value of the specified expression with respect to the last row in the window frame.
4. Use Lag function in a query to get the Lag column
SELECT Restaurant, Price, lag(Price,1) over (Restaurant) as Lag from Restaurant
5. Use the Lead function to get a lead column
SELECT Restaurant, Price, lead(Price,1) over (Restaurant) as Lead from Restaurant
6. Show the use of First_Value()
|To Be Continued||2||6000||4000|
First_Value(Price) Over(Partition By Id) as First
7. What are the aggregate window function?
We are not going to discuss the definition or syntax of these functions
8. What are the ranking functions?
Ranking functions are important to keep in your mind. The most used ranking functions are:-
9. Which of the following group function ignores NULL value?(Tredence)
All of the above
A. All of the above
10. What is the use of FETCH command?(Flipkart)
The FETCH argument is used to return a set of number of rows. FETCH can’t be used itself, it is used in conjuction with OFFSET.
ORDER BY column_name
FETCH NEXT number_of_rows ROWS ONLY;
11. How to find count of duplicate rows?(Flipkart)
Select rollno, count (rollno) from Student
Group by rollno
Having count (rollno)>1
Order by count (rollno) desc;
12. You already know how to get the maximum and second maximum from a table. You also have a fair bit of idea to fetch the maximum from each department(as in form a table of employees with their department present in another column). Now fetch the second highest salary from each department
Select Dept, Max(Salary) From (Select E1.Dept, E1.Salary From Emp E,Select (Dept,Max(Salary) as salary from Emp group by Dept)E1 Where E.Dept = E1.Dept And (E.Salary < E1.Salary) X Group By Dept
We will add more questions in the upcoming articles.
Keep Learning 🙂
The Data Monk