Hey all 👋,
Hope you all doing great and today we gonna discuss few MySQL topics that are often ignored or say it advanced.
To whom this blog is intended for? Absolute beginners? Intermediates ? To say it is intended for someone between beginner to Intermediate level, A Basic SQL knowledge is sufficient to understand.
Lets get started 🚀🚀
These are similar to if-else statements, The CASE statement goes through a list of conditions and returns the result corresponding to first condition that is true. If none of conditions are met it returns the result from else statement.
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;
SELECT (CASE WHEN follower_count >= 100 THEN "Celebrity" ELSE "Poor Regular User" END) as "User Type" FROM user_table /* No Celebrities are harmed in making of this code */
The above statement returns the user type based on his follower count. If it is more than 100 followers then returns "Celebrity" else returns as "Poor Regular User"
This function takes 2 Arguments, if the first argument is NULL, it returns second argument.
Usually used to replace a NULL value with a default value while displaying the data.
IFNULL( column_name, alternate_value )
SELECT IFNULL (amount,0) FROM user_table
If value from amount column is null then 0 is returned in place of null value
This function takes 2 or more arguments and It works similar to IFNULL, but returns the first Non NULL argument among the list of arguments, if every argument is NULL then it returns NULL.
COALESCE(argument_1, argument_2...., argument_n)
SELECT COALESCE(phonenumber_1, phonenumber_2, phone number_3) FROM user_table
If phone number 1 is null then it checks for phone number 2, even if it is null then it returns phone number 3
A stored procedure is a group of SQL statements that has been created and stored in the database.
It accepts input parameters so that only one procedure can be used on the network by multiple clients using different input data.
--For creating procedure DELIMITER && CREATE PROCEDURE procedure_name ([[IN | OUT | INOUT] Parameter_name datatype ]... ) BEGIN sql_statements... END && DELIMITER ; -- The delimiter is used to differentiate between the end of the procedure and the sql statements. For executing procedure CALL procedure_name ( arguments... );
--Creating getvalues procedure which prints out data in users_table when called DELIMITER && CREATE PROCEDURE getvalues () BEGIN SELECT * from users_table; END&& DELIMITER; -- calling procedure CALL getvalues();
Window functions perform calculations on a set of rows that are related together. These related rows are termed as windows.
They Do not merge the rows like GROUP BY, they preserve the existing data.
Mainly there are 3 Categories of window functions.
Aggregate Window Functions
SUM(), MAX(), MIN(), AVG(). COUNT()
Ranking Window Functions
RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
Value Window Functions
LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
window_function ([column1....]) OVER ([ PARTITION BY partition_list ] [ ORDER BY order_list]) --  = contents inside square brackets are optional
This function will assign rank to each row within a partition, if there is a tie between the values then they will be assigned same rank and the next rank will be ( previous rank + no of previous duplicates )
Example rank sequence : 1 2 2 4 5 5 7
First, lets see what's there in the table
SELECT * FROM student
Now lets execute the RANK query
SELECT *, RANK() OVER (ORDER BY marks DESC) as "Rank" from student
You can now see the result is sorted and every person is given a rank value, though the rank is not continuous at row with name vikram
Did you notice discontinuity in rank values when using RANK? Dense Rank does the same thing as RANK but with no discontinuity in rank values.
Considering the same above example, lets execute dense query and see the result
select *, DENSE_RANK() OVER (ORDER BY marks DESC) as "Dense Rank" from student
This function is used to give sequential number for each row within its partition.
Lets have a look at the table data before executing the ROW_NUMBER query
SELECT * FROM student
SELECT *, ROW_NUMBER() OVER(PARTITION BY dept) AS "row number" FROM student;
That's it for the blog, these are my 2 cents about some tricky SQL concepts. For more information on concepts, you can refer to javapoint