Further into SQL(part-2)

4 min readFeb 4, 2022

Welcome back, in this story we’ll get further into the SQL,

If you a new to my story you can refer to my previous story for Further into SQL(Part-1)https://medium.com/analytics-vidhya/further-into-sql-part-1-afcaf61c8c92

Today let's get into String Function, Time Functions, and Indexing.

Why do we need String Function!

String Function is the function that helps to Concatenate, Slice, and Trim the text file in the Database. This in turn helps to analyze the Database in a faster way and more efficient way, Because text/string data are difficult to process/fetch/run the different operations on the String Function.

Let's consider the text file are of two Columns First_name and Last_name we need to Join the two columns since these are Text/String types, we cannot use Add or Arthimtic functions on these columns so we would use Concatenate function.

✏️ How to Join Two Text/String Together

Query:“Select First_name,Last_name,concat(First_name,” “,Last_name)as Full_Name from team2021;”

✏️ How to Change Text to lower Case in the Column

Query: “Select upper(First_name)as Capatial_Letter from team2021;”

✏️ How to Change Text to Lower Case in the Column

Query: “Select lower(First_name)as Capatial_Letter from team2021;”

✏️How to Trim/Remove “Space” From the String in the Column

Query : (A) “select ltrim(“ Left”)from team2021 limit 1;” (B)“select ltrim(“Right “)from team2021 limit 1;” (C)”select trim(“ left-Right “)from team2021 limit 1;”

Note: You Remove Space From the Left side, Right side, and also from both sides using the Above Code

✏️How to Extract text from first, last, or from a particular position

Query :(A) “”(B) “” (C)select substring(First_name,2,3) from team2021 limit 3;

Why do we need Time Function!

Time Function is used to get separate Day/Month/Year/Even time as we need the Default formate of “yyyy-mm-dd hh:mm:ss” but We won’t be needing all the details all the time so we would use, time functions like day, month, year, etc in the next examples.

✏️How to Extract Year, month,day sepreratly from a Date

Query: “select Name,DOB,day(DOB),month(DOB),year(DOB) from team2021;”

✏️How to Get Age from Date of Birth

Query: “select Name,year(now())-year(DOB) as Age from team2021;”

✏️What is now(), curdate(), curtime() in the SQL

Query: “select now(),curdate(),curtime() from team2021 limit 1 ;”

Use Limit Clause or the Function will run as the total no of rows in the Column (since my table had less record it ran till 6 or it'll go on)

✏️How to Change Format of the Given Date

Query: “ select Name,DOB,date_format(DOB,”%d/%m/%y”)as DOB_Format from team2021;”

✏️How to sort the players by Age

Query: “select Name,year(now())-year(DOB) as Age from team2021 order by 2;

Why do we need Indexing

Indexing helps to read the data from that column faster and gets better solutions. Try to index the column that is not changed often like Id, Name, Reg_date, etc