Top Handy SQL Features for Data Scientists
Whenever we hear "data," the first thing that comes to mind is SQL! SQL comes with easy and quick to learn features to organize and retrieve data, as well as perform actions on it in order to gain useful insights.
By Saurabh Hooda, Hackr.io
SQL is to data science as nature is to life. Without preserving nature, there is no possibility of life. Without preserving the knowledge of SQL, there would be no data science.
Even though NoSQL databases and programming languages like R and Python do the job extremely well, whenever we hear "data," the first thing that comes to mind is SQL! Python does have excellent libraries that help data scientists – check the top Data Science Python Libraries here – but SQL is still the preferred mode of handling data.
SQL (Structured Query Language) has been around for a long time and is a very comfortable programming language for many developers. SQL comes with easy and quick to learn features to organize and retrieve data, as well as perform actions on it in order to gain useful insights. You can read some of the commonly asked SQL interview questions here to get a basic idea of what you should know about SQL. It is also efficient and close to the data.
If you have no idea about databases and haven’t worked with any you should start with the basics. Start by reading about DBMS (Database Management Systems) normalization, which will help you understand why a structure is important for easy analysis of data.
If you are into web development, you know that data is fetched from the backend (database) and presented to the front-end (UI). In the same way, data is entered by the user into the database. Visit Squareboat to know how we can help you with web development.
That said, let us now focus on the top handy features of SQL that should be on a Data Scientist’s fingertips.
1. Select statement
As a data scientist, you will be selecting (reading) a lot of data from different tables to get patterns, statistics and other important information. The basic select query
could return a lot of records. In the real world, databases have millions of records, and if there are multiple columns in a table you will be overwhelmed with the magnitude of results. You can select only the columns you need.
For example, if you want to know the name and age of all registered students, you can query the database as –
2. Grouping and sorting functions (Ranking and top n analysis)
Most of the time, you would want to work on a subset of data that you are interested in. For example, if you want to know the number of teenagers who use your product, you can do that easily with a where clause.
In the same way, you can group the students based on their age. The below query will get you the number of students (count) from each branch/department.
Let us say you want to know which department has the most/least students. You would simply use the order by clause to do that.
Now, if we only want to know about those departments where at least 10 students use our product, what should we do?
We can use ‘having’ clause, which is similar to ‘where’ clause but works on a group of data.
Getting such limited number of records from a huge data set is called top-n analysis. Some real-life examples could be – top 2 students who scored the highest marks, top 10 Facebook users who spend most time browsing, top 3 products that were sold by a company, top 5 employees of the month, least 3 performers of the month and so on. For example –
The first select statement (inline view) gets all records ordered by total_marks, while the 2nd one gets only the first two (n) top records.
Very easily, we have obtained data to a minute filtration level, without scanning or searching extensively for it. We can do a lot of such filtering to further satisfy more and more conditions.
3. String functions (text mining)
There are many useful string functions in SQL just like we have in programming languages. You can avoid writing lines of code when such features are available in the DBMS itself, because it is faster. Some common string functions are –
1. upper and lower
To convert the entire string to upper or lower case, we can use this function. This can come in handy when we want something to be printed in a respective case. For example, if we want a student's first name to be prints in all caps, we can use upper function –
If we have to replace one or more characters with another, we can use this function. For example, let us say we need to get the list of mobile numbers without dashes in between. The value database is 1-832-234-1098 and we want to replace all the hyphens with spaces.
This will present the value as 1 832 234 1098.
Joins two or more different columns or strings to give result as one. For example, if you want to display full name as first name plus last name, concat can be used.
Just like in a programming language, SQL substring gets certain characters from a string. For example, if the exam table has hall_ticket_no (example 00019812345) wherein we only want the last few characters, we can write the query as –
There are two more variations of substring, left and right, that get the left few or right few characters accordingly. In our above example, we can use the RIGHT function as well.
Gets the number of characters in a string. For example –
This will show only those profiles which have shorter descriptions.
6. ltrim, rtrim
ltrim and rtrim remove the leading and trailing spaces respectively from a string. Sometimes database entries are with a lot of spaces at the beginning or end – it may be that the user entered it, or a program padded the spaces; we don’t know. If you wish to trim those and present the data, use these functions. Example –
To remove leading and trailing spaces, you can use –
4. Date functions
Handling dates is slightly complex but SQL does this with ease.
There are many functions like
- DATEADD – add one year to an existing date
- TO_DATE – converts a string into a date
- DATEDIFF – find the difference between the 2 given dates
- DATEPART – get a particular part of the date; for example, year, month or date
- DAY – get the day of the month for the given date
- CURRENT_TIMESTAMP – get the date and time (timestamp)
All the above are quite useful to analyze various types of data. For example, you can use DATEDIFF to get data from a particular range of time, or use the DAY function to find out on what days most students take leave and so on.
5. Aggregations (Statistical functions)
Aggregate functions allow us to find the sum (SUM), average(AVG), minimum(MIN), maximum(MAX) and count(COUNT) values from a set of data. We use these functions with group by and having clauses. For example, if we want to know what is the average percentage of marks that the students of each department have collectively, we can use the AVG function.
In the same way, to get the number of students in a particular deptt, we can use count.
Often you will want to collate data from multiple tables and get only those columns that are matching some condition or pattern. For getting data from two or more tables, we use SQL joins.
7. Regular expressions
It is very useful to know about regex. For example, if you want to validate a phone number, credit card or any numeric values that match a certain sequence, use regex.
For example, if you want to get phone numbers that start with a number (and not a bracket or any other character), you can use –
8. Loading and copying data into database
If you have loads of data in Excel or .csv format and you want to copy all of it into a DBMS, SQL can do that for you.
You can use COPY FROM to copy data from a file to the database. Same way to copy data from database to file, use COPY INTO command.
9. Data bucketing
As we saw earlier, using group by helps us to get sets of data so that we can find trends and deduce business opportunities by analyzing it. Bucketing is the term used to find those groupings (most of the time with timestamps and numbers) and to generate histograms. This reduces human observation errors. For example, using the truncate function, we can get the nearest round off value for a decimal.
In the same way, date_trunc is used to group dates together. This could be useful in tracking user activities over a period of time. For example, the activities of the students in an online learning portal over a specific date interval, for example, weekdays and weekends.
Read more about Data bucketing here.
Using SQL sequences, we can generate a numeric sequence on demand in ascending or descending order. Sequences can be created using create sequence <sequence_name> by giving necessary details. Sequences are not associated with any tables, hence it is useful to retrieve table values using a sequence directly, if there is one. Calling ‘next value’ function retrieves the next row instead of selecting from the table.
The final word
If you haven’t yet had hands-on experience with SQL, you should try these tutorials. As a data scientist, you may not be using all the powerful features of SQL. However, once you start playing with the data, you would definitely enjoy digging more. The above list should help you get through the features you will be expected to use as a data scientist. Do visit this link to read about all the important data science interview questions and to start building your dream career.
Bio: Saurabh Hooda has worked globally for telecom and finance giants in various capacities. After working for a decade in Infosys and Sapient, he started his first startup, Leno, to solve a hyperlocal book-sharing problem. He is interested in product marketing, and analytics. His latest venture Hackr.io recommends the best Data Science tutorial and online programming courses for every programming language. All the tutorials are submitted and voted by the programming community.
- Is SQL needed to be a data scientist?
- 7 Steps to Mastering SQL for Data Science — 2019 Edition
- Python Data Science for Beginners