Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Data Science: One-Stop Solution for Beginners

DZone 's Guide to

SQL Data Science: One-Stop Solution for Beginners

See how SQL data science will help you understand how SQL can be used to store, access, and retrieve data to perform data analyses.

· Database Zone ·
Free Resource

Image title

SQL code for data science for beginners

Since the time data science has been ranked at number 1 for being the most promising job of the era, we’ve all tried joining the race of learning data science. This post on SQL data science will help you understand how SQL can be used to store, access, and retrieve data to perform data analyses.

You may also like: Top 6 Languages for Data Science

Why Is SQL Needed for Data Science?

Did you know that we’re generating more than 2.5 quintillion bytes of data each day? This pace of data generation is the reason behind the popularity of high-end technologies such as data science, artificial intelligence, machine learning, and so on.

Deriving useful insights from data is what is termed as data science. Data science involves extracting, processing, and analyzing tons of data. At present what we need are tools that can be used to store and manage this vast amount of data.

This is where SQL comes in.

SQL can be used to store, access, and extract massive amounts of data in order to carry out the whole data science process more smoothly.

Basics of SQL

SQL provides a set of simple commands to modify data tables, let’s go through some of the basic SQL commands:

  • CREATE DATABASE — creates a new database
  • CREATE TABLE — creates a new table
  • INSERT INTO — inserts new data into a database
  • SELECT — extracts data from a database
  • UPDATE — updates data in a database
  • DELETE — deletes data from a database
  • ALTER DATABASE — modifies a database
  • ALTER TABLE — modifies a table
  • DROP TABLE — deletes a table
  • CREATE INDEX — creates an index to search an element
  • DROP INDEX — deletes an index

To better understand SQL, let’s install MySQL and see how you can play with data.

Installing MySQL

Installing MySQL is a simple task. Here’s a step-by-step guide that will help you install MySQL on your system.

Once you’re done installing MySQL, follow the below section for a simple demo that will show you how can you insert, manipulate and modify data.

SQL for Data Science – MySQL Demo

In this demonstration, we will see how to create databases and process them. This is a beginner level demonstration to get you started with data analysis on SQL.

So let’s get started!

Step 1: Create a SQL Database

A SQL database is a storage warehouse where data can be stored in a structured format. Now let’s create a database by using MySQL:

CREATE DATABASE edureka;
USE edureka;

In the above code, there are two SQL commands:

Note: SQL commands are defined in capital letters and a semi-colon is used to terminate a SQL command.

  1. CREATE DATABASE: This command creates a database called ‘edureka’
  2. USE: This command is used to activate the database. Here we’re activating the ‘edureka’ database.

Step 2: Create a Table With the Required Data Features

Creating a table is as simple as creating a database. You just have to define the variables or the features of the table with their respective data types. Let’s see how this can be done:

CREATE TABLE toys (TID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, Item_name TEXT, Price INTEGER, Quantity INTEGER);

In the above code snippet, the following things occur:

  1. Use the ‘CREATE TABLE’ command to create a table called toys
  2. The toy table contains 4 features, namely, TID (Transaction ID), Item_name, Price, and Quantity
  3. Each variable is defined with its respective data types
  4. The TID variable is declared as a primary key. A primary key basically denotes a variable that can store a unique value

You can further check the details of the defined table by using the following command:

DESCRIBE toys;

Data Description - SQL For Data Science - Edureka

Step 3: Inserting Data Into the Table

Now that we’ve created a table, let’s fill it up with some values. Earlier in this article, I mentioned how you can add data into a table by just using a single command, i.e., INSERT INTO.

Let’s see how this is done:

INSERT INTO toys VALUES (NULL, "Train", 550, 88);
INSERT INTO toys VALUES (NULL, "Hotwheels_car", 350, 80);
INSERT INTO toys VALUES (NULL, "Magic_Pencil", 70, 100);
INSERT INTO toys VALUES (NULL, "Dog_house", 120, 54);
INSERT INTO toys VALUES (NULL, "Skateboard", 700, 42);
INSERT INTO toys VALUES (NULL, "G.I. Joe", 300, 120);

In the above code snippet, we simply inserted 6 observations into our ‘toys’ table by using the INSERT INTO command. For each observation, within the brackets, I’ve specified the value of each variable or feature that was defined while creating the table.

The TID variable is set to NULL since it auto-increments from 1.

Now let’s display all the data present in our table. This can be done by using the below command:

SELECT * FROM toys;

Insert Data - SQL For Data Science - Edureka.html

Step 4: Modify the Data Entries

Let’s say that you decided to increase the price of the G.I. Joe since it is getting you a lot of customers. How would you update the price of the variable in a database?

UPDATE toys SET Price=350 WHERE TID=6;

The UPDATE command allows you to modify any values/variables stored in the table. The SET parameter allows you to select a particular feature and the WHERE parameter is used to identify the variable/ value that you want to change. In the above command, I’ve updated the price of the data entry whose TID is 6 (G.I. Joe).

Now let’s view the updated table:

SELECT * FROM toys;

Update Data - SQL For Data Science - Edureka

You can also modify what you want to be displayed by just referring to the columns you want to view. For example, the below command will display only the name of the toy and its respective price:

SELECT Item_name, Price FROM toys;

Filter Data - SQL For Data Science - Edureka

Step 5: Retrieving Data

So after inserting the data and modifying it, it’s finally time to extract and retrieve the data according to the business requirements. This is where data can be retrieved for further data analysis and data modeling.

Note that this is a simple example to get you started with SQL; however, in real-world scenarios, the data is much more complicated and big in size. Despite this, the SQL commands still remain the same, and that’s what makes SQL so simple and understandable. It can process complex data sets with a set of simple SQL commands.

Now let’s retrieve data with a couple of modifications. Refer to the code below and try to understand what it does without looking at the output:

SELECT * FROM toys LIMIT 2;

You guessed it! It displays the first two observations present in my table.

Limit Data - SQL For Data Science - Edureka

Let’s try something more interesting.

SELECT * FROM toys ORDER BY Price ASC;

Ascending Order Data - Edureka

As shown in the figure, the values are arranged with respect to the ascending order of the price variable. If you want to look for the three most frequently bought items, what would you do?

It’s quite simple really!

SELECT * FROM toys ORDER BY Quantity DESC LIMIT 3;

Order by descending order - Edureka

Let’s try one more.

SELECT * FROM toys WHERE Price > 400 ORDER BY Price ASC;

Price greater than 400 - SQL For Data Science - Edureka

This query extracts the details of the toys whose price is more than 400 and arranges the output in ascending order of the price.

That's all folks. I hope you were able to understand the concepts discussed in this article!

Further Reading

Is SQL Beating NoSQL?

Data Analytics vs. Data Science

Topics:
sql (structured query language) ,data science ,database ,basics of sql ,sql data science ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}