{"id":2211,"date":"2020-01-23T02:42:34","date_gmt":"2020-01-23T02:42:34","guid":{"rendered":"http:\/\/kusuaks7\/?p=1816"},"modified":"2024-01-24T17:30:27","modified_gmt":"2024-01-24T17:30:27","slug":"understanding-sqls-application-in-data-science-a-deep-dive","status":"publish","type":"post","link":"https:\/\/www.experfy.com\/blog\/bigdata-cloud\/understanding-sqls-application-in-data-science-a-deep-dive\/","title":{"rendered":"Understanding SQL’s Application in Data Science [A Deep Dive]"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

To learn about SQL, we need to understand how a DBMS works. DBMS or Database Management System is essentially a software to create and manage databases.<\/h3><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tIt will be useful to know why and how SQL holds such an important stature in the field of data science.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

What is data science?<\/strong><\/h2><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tData science is a field that uses many scientific methods and processes to collect data and get useful insights from them. Doing this creates new opportunities, designs, business cases and even revolutionary innovations.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThe future of AI lies in Data science. Data science involves use of algorithms, software programs and statistics to collate and process huge complex chunks of data. The complexity of data comes from its unstructured nature \u2013 data today can be in any form \u2013 text, image, output from sensors and instruments and what not!\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tAs of today, the benefits of data science can be felt in domains like e-commerce, healthcare, manufacturing, banking, finance and transport.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

The Basics of DBMS and Relational Database<\/strong><\/h2><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tTo learn about SQL, we need to understand how a DBMS works. DBMS or Database Management System is essentially a software to create and manage databases. With DBMS, programmers can create, manage or select data.\n
Database acts as an interface between end users and application programs to access data in an organized and consistent manner. For example, a user logs in to Facebook.<\/blockquote>\nBased on the user\u2019s registered email address and password, API calls are made from the application to the database and user details are retrieved.\n
In a relational DBMS or RDBMS, SQL is the API through which users and applications access data. Data can be accessed in the form of tables and its columns.<\/blockquote>\nThe relationships between the tables is such that there is no need to reorganize the data or the tables. Using SQL statements, we can get information about multiple tables and their relationships.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

What is SQL?<\/strong><\/h2><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tSQL or Structured Query Language is a \u2018programming language\u2019 that manages data in a relational database through \u2018queries\u2019. By using SQL, we can insert, update, delete and select data based on various filters and conditions. SQL also helps run certain scheduled scripts automatically, that need to run regularly.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tWith SQL, Developers and data analysts can easily code and run queries. For example, select * from customer; is a simple query that gives details of the customer table.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nAnalysts can access huge amounts of data, process it without having to copy or store data anywhere else.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tIt is easy to analyze data using SQL queries than using excel sheets or any other methods. There are a lot of aggregation functions in SQL that can work on large datasets and multiple tables.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Understanding the lifecycle of data science<\/strong><\/h2><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tTo understand how SQL is used in data science, we need to know the different phases of data science.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThere are 3 important phases in the lifecycle of Data Science \u2013\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

1. Data conditioning and cleaning<\/strong><\/h2><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tAfter discovery of data from various sources, data has to be prepared and structured. That means, all the redundant and duplicate data should be removed and a neat structure should be formed so that it is easy to model the data. This is called\u00a0DBMS normalization<\/a>\u00a0and all relational databases follow this approach.\n
With SQL, structuring and conditioning of the data can be done easily because SQL has the concept of primary key and constraints, wherein the redundancy and dependency can be removed and proper relationship can be established between different data types.<\/blockquote>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tSQL can also help with data cleaning, which means irrelevant, incorrect or corrupt records can be removed periodically by automatic batch scripts without any manual intervention. You can also use R for data cleaning and transformation, however using SQL in conjunction with R can reduce the amount of code to be written and bring in more flexibility.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

2. Data modelling and mining<\/strong><\/h2>\n<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tModel planning and building totally relies on DBMS (Database Management Systems), particularly Structured query Language or SQL (if you did not know it already!). Big data is a big chunk of unstructured data and only a structured language can save our time trying to establish relationships between the variables of presented data.\n
A simple example of modelling will be a customer who is trying to purchase a postpaid mobile connection with a plan, subscription and a few value-added services.<\/blockquote>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tEach of these can be a table in the relational DBMS and the relationship between them can be established using a common key like the customer_id, phone_number or subscription_id which can be unique. Thus, we are creating entities (tables), attributes (columns), relationships (customer_id linked to subscription_id or phone_number etc\u2026) and integrities (customer_id should be unique, phone_number and subscription_id together can be a composite key and so on).\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
We can also create triggers, for example, whenever the customer changes any value-added services, a trigger enables the service to be activated automatically after performing the necessary eligibility checks.<\/blockquote>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tData mining is a process where huge sets of data is examined for patterns and trends for evaluating data to solve a business purpose. The process becomes easier when large datasets are organized and structured.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nAs a continuation, let us say we want to identify what are the value-added services that most users are interested in. Since every person nowadays has at least one mobile phone and the number of services that can be added are unlimited, this will be a huge data to consolidate, sort and examine.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tSQL makes things easier by giving particular records based on specific user query. For example, if we want to know the users who have activated \u2018ringtone\u2019 services during calls, we can write a query as \u2013\n
select customer_id from value_added_service table where service = \u2018ringtone\u2019 and is_service_enabled = \u2018Y\u2019 and service_end_date > NOW();\n<\/code><\/pre>\nFor complex queries indexing can be done to fetch results faster.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tWe can also use in-built SQL functions to list the data in ascending order based on service_end_date, group by a certain order and so on.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t
SQL basics for Data Analysis<\/strong><\/h2><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tYou might want to argue that excel sheet with huge amount of data can also fetch the results and do some filtering operations as well. It is true, but not comparable to the scale at which SQL stores its data. But how much SQL is enough for you to be a data scientist?\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
There is no definite answer, however we have tried to squeeze in a lot of information that will help you grasp the basic idea. If you already know SQL, you will be familiar with most of it, if you are not, don\u2019t worry, this blog will give you good insights of the basics.<\/blockquote>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tMost of the time, the database and schema will be already present with all the relationships established. As a data scientist, you will need to understand the schema, relationships and how you can query the database to get the best of the results from a business perspective.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tLet us create some data and then work our way to fetch this data using the different techniques we will learn below.\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tBelow are the most important statements and commands that you will use on a daily basis.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\nSelecting rows and columns\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThe basic query that fetches all the data from a table is \u2013\n
select * from table_name;\n<\/code><\/pre>\nLet us get some records from student table.\nDoing\n
select * from student;\n<\/code><\/pre>\nwill fetch us all the rows and columns present in the table.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tDoing\n
select * from student;\n<\/code><\/pre>\nwill fetch us all the rows and columns present in the table.\n\nWhat if we only wanted the first name and the date of birth? Then we should specify the column names in the query.\n
select first_name, date_of_birth from student;\n<\/code><\/pre>\nfirst name\u00a0 date_of_birth<\/strong>\n——————————————-\nMaria \u00a0 \u00a0 \u00a0 17-11-1984\nGopal \u00a0 \u00a0 \u00a0 04-02-1983\nKeshav\u00a0 \u00a0 \u00a0 14-12-1983\nAaron \u00a0 \u00a0 \u00a0 05-03-1984\nNaman \u00a0 \u00a0 \u00a0 06-06-1984\n\nWe can also use some expressions to display the data in a particular way. For example,\n
select first_name + \u2018 \u2018 + last_name as full_name from student;\n<\/code><\/pre>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tYou will get something like \u2013\n\nfull_name<\/strong>\n————\nMaria Thomas\nGopal Chandra\nKeshav Sharma\nAaron V\nNaman K\n\nFiltering rows based on conditions\n\nMost of the times, we need only particular trends and patterns. These patterns can be our filters, based on which we will get only those records from database which are relevant and satisfy our conditions. For example,\n\nIf we want to get students of the teacher Brunda only, we can use a where clause.\n
select first_name, date_of_birth from student where teacher_name = \u2018Brunda\u2019;<\/code><\/pre>\nIt is also easy and efficient to use grouping and sorting functions. To start with, if we want to group the records based on the deptt, we can add a group by clause \u2013\n
select first_name, date_of_birth from student where adm_year = 2001 group by deptt;\n<\/code><\/pre>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t<\/code><\/pre>\n\u2018Having\u2019 is another filter similar to the where clause which we can apply on group functions. For example, we want to have all the records of ECE dept with admission date after 2001, we can write the query as \u2013\n
select first_name, deptt, date_of_birth from student group by deptt having adm_year > 2001;<\/code><\/pre>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tThis will fetch something like \u2013\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tfirst_name \u00a0 deptt date_of_birth<\/strong>\nMaria \u00a0 ECE \u00a0 \u00a0 \u00a0 17-11-1984\nNaman \u00a0 ECE\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 06-06-1984\nKeshav \u00a0 CSE\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 14-12-1983\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tIt can so happen that there may be similar records and we want to get only one of them. For example, the deptt (departments) column. If we want to list all the departments present in the database, we can use the keyword distinct. This will give all the unique values in the column.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tSame way, we can get the count of students based on certain conditions, find sum, average, find records that have a particular date of birth and many more things.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t
Aggregating results using in-built functions<\/strong><\/h2><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tAggregate functions are used with group by, having clause to get a scalar value from a select statement. The most common functions are \u2013 SUM, AVG, COUNT, MAX, MIN. Except count function, all others ignore a NULL value. In the above example, if we want to get count of students in ECE deptt, we can form a query as \u2013\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
select count(*) from student where deptt = \u2018ECE\u2019;<\/code><\/pre>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tSimilarly, if we want to get the average percentage obtained by CSE students of all grades, we can use the AVG function and so on.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t
Joins<\/strong><\/h2><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tA relational database, as we know has multiple small tables that can be mapped to each other. To obtain data from multiple tables in a single result, we join the tables and display the required columns. There are different types of joins \u2013\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t