The Importance of Learning SQL Basic Functions as a Foundation for Learning Data Science

Fungsi Dasar SQL sebagai Fondasi Belajar Data Science (Dok. DQ Lab)

Tangerang – SQL is the second most widely used programming language for data practitioners; it has many functions that play a role in analyzing data. Thus, DQLab collaborated with the Ministry of Communication and Informatics (Kominfo) to hold a live session entitled “Fundamental SQL Using Function and Group By,” which was held on Thursday (17/03/22). This live session is a part of the Digital Talent Scholarship (DTS) program, which invited speaker Tantut Wahyu, a Data Analyst at RupaRupa.com. In this fourth session, Tantut discussed the use of R with SQL.

Entering the start of the live session, Tantut reminded that there are three data practitioners with the same task, namely processing data, cleaning data, collecting data, using data, analyzing data, and exploring data. The three data practitioners are Data Engineers, Data Analysts, and Machine Learning Engineers. A data practitioner that can do all three jobs is called Data Scientist.

A function is a method used to perform data operations in a database. These operations can be used for numerical calculations, such as sum, count, avg, or non-numeric operations, such as string concatenations and sub-strings. There are seven functions introduced by Tantut, namely scalar functions vs. aggregate functions, mathematical scalar functions (numeric functions), text scalar functions (string functions), aggregate functions (summarizing functions), group by, and case when.

“Operations commonly used by a data practitioner to create data automation or taglines are sum, count, avg,” Tantut said.

The scalar function in SQL is used to return a single value from a given input value. In this case, scalar functions can be performed for mathematical arithmetic operations and by words on the database. At the same time, the aggregate function in SQL is used to perform calculations on a group of values ​​and then return a single value.

Also read Did you know? R Language Is Also Important to Be Mastered by Non-ITs

Continuing on the discussion of mathematical scalar functions. In this function, which are usually used in SQL are: abs(), ceiling(), floor(), round(), sqrt(), mod(), and exp(). 

Here are the explanations of each mathematical scalar functions explained by Tantut:

• Abs(): Used to return the absolute value of the input value.
• Ceiling (): Used to return the largest integer value closest to the input value.
• Floor (): Used to return the largest integer value closest to the input value.
• Round (): Used to return the rounded value of a decimal value.
• Sqrt (): Used to return the rounded value of a decimal value.
• Mod () Used to return the remainder of the value divided by the input value.
• Exp(): Used to return the value exponential of the input value.

In addition to the seven mathematical scalar functions mentioned above, there are also text scalar functions that Data Engineers commonly use, they are: concat(), split_part(), substr(), length(), replace(), trim(), upper() and lower(). These functions also have their own uses: 

• Concat(); Used to combine all arguments/input. 
• Pplit_part(): Used to split string/text based on the specified separator. 
• Substr(): Used to extract the desired character/string. 
• Length(): Used to count the number of characters in a string/text. 
• Replace(): Used to replace characters in a string/text.
• Trim(): Used to delete characters in a string.
• Upper(): Used to convert lowercase letters to uppercase. 
• Lower(): Used to convert uppercase to lowercase.

Still on the discussion of functions, namely aggregate functions. In the aggregate function there are several functions, namely SUM(), COUNT(), AVG(), MIN(), MAX(), FIRST() and LAST(). All of them are also explained by Tantut: 

• SUM(): Used to sum a group of values ​​(rows) in one column. 
• COUNT(): Used to count the number of rows. 
• AVG(): Used to calculate the average value of a column. 
• MIN(): Used to calculate the minimum value of a column.
• MAX(): Used to calculate the maximum value of a column. 
• FIRST(): Used to return the value in the first row of a column. 
• LAST(): Used to return the value in the last row of a column.

“Data practitioners most use the aggregate function,” Tantut said.

After the detailed explanations of the functions, Tantut explained the design of a database and showed the relationship between objects or entities and their attributes in detail through an entity-relationship diagram. He also gave hands-on practice on the materials he explained. 

SQL is one of the programming languages ​​that data practitioners must master. DQLab provides a special SQL learning program for those who want to study how to become a Data Scientist with  DQLab.

*by Agnes Nurlisa | DQLab

Kuliah di Jakarta untuk jurusan program studi Informatika| Sistem Informasi | Teknik Komputer | Teknik Elektro | Teknik Fisika | Akuntansi | Manajemen| Komunikasi Strategis | Jurnalistik | Desain Komunikasi Visual | Film dan Animasi | Arsitektur | D3 Perhotelan | International Program, di Universitas Multimedia Nusantara. www.umn.ac.id