SQL for data analysis

SQL for data analysis | The SQL Tutorial for Data Analysis

Introduction to data Analytics

It is the process deriving useful insight from data in order to solve real world problems

Data has been the buzzword for ages now. Either the data being generated from large-scale enterprises or the data generated from an individual, each and every aspect of data needs to be analyzed to benefit yourself from it. But how do we do it? Well, that’s where the term ‘Data Analytics’ comes in. In this blog on ‘What is Data Analytics?’, you will get an insight of this term with a hands-on.

We are generating more than 2.5 quintillion  bytes of data each year. 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. The data is the key in data science.

Data science involve extracting, processing and analyzing tons of data at present what we need are tool that can be used to store and manage this vast amount of data.

What is the role of Data Analytics?

 

  • Gather Hidden Insights – Hidden insights from data are gathered and then analyzed with respect to business requirements.
  • Generate Reports – Reports are generated from the data and are passed on to the respective teams and individuals to deal with further actions for a high rise in business.
  • Perform Market Analysis – Market Analysis can be performed to understand the strengths and weaknesses of competitors.
  • Improve Business Requirement – Analysis of Data allows improving Business to customer requirements and experience.

Why we need SQL for data analytics ?

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

What is SQL ?

SQL stands for structured query language is a querying language amid to manage relational database.

With SQL you can modify database,add,update, or delete rows of data retrieve subsets of information from database and any more.

Queries and other SQL operation are written as statement example select ,insert ,add,update ,delete,create,alter,insert.

Types of database

  • Relational Database
  • Non-Relational database

What is relational database ?

Relational database a group of well-defined Table from which data can be accessed or it can be edited and updated and no need to alter database table.

RDBMS is data management system where data can be store into different tables (Columns and rows) and relations are established among the different data variable by using primary keys and so on.

SQL is standard API for Relational database.

what is database

A database is a collection of data stored in a format that can easily be accessed.

Real life example of database

real life example of database
DBMS mangement system

SQL programming can be used to perform  multiple action on data such as :

  • Querying
  • Inserting
  • Updating
  • Deleting
  • Extracting and so on.

It is manipulating and analyse data in such a way that you derive something useful from data.

Example of Relational data that use SQL are MySQL and oracle.

Why to use MYSQL ?

  • Very easy to use.
  • Need basic knowledge of SQL
  • With few simple SQL statement.
  • It is very secure RDBMS.
  • It is open source.
  • It is scalable.
  • The default size limit 4 GB and can be increase up to 8 TB
  • MySQL follow client server architecture.
  • MYSQL is compatible on many operating system.(windows,linux,Unix,Mac)
  • There are number of API and libraries for development of MySQL application.
  • You can easily work with python and SQL.
  • It is platform independents.
  • MySQL is very fast RDBMS.

Some Data types under MYSQL

  • Numeric
  • Character Sting
  • Bit-String
  • Boolean
  • Date and time
  • Timestamp and interval

Start My SQL

start sql application

COMMAND IS MYSQL

COMMAND IS SQL

 

The first command is SQL is to create Database:

Syntax:

CREATE DATABASE : name;

USE :name;

CREATE DATABASE : to create database

USE: activate the database:

Note : after end of each command there is a terminator which is semicolon ;

Command are written in capital letter so user can differentiate between command and table columns.

Create database in my sql

Use Database in MySQL

You can use the SQL command use to select a database.

CREATE TABLE IN MYSQL

This Function create a new table

Syntax:

CREATE TABLE name (variable1 datatype 1,variable2 datatype2);

  • CREATE TABLE: this command create a table containing the data variable
  • Each variable must be defined w.r.t data type.

Example if your variable is age than your datatype will be integer;

if your variable is name than your datatype will be character;

Table creation in Mysql

CREATE TABLE personal (
ID INT,
name VARCHAR(50),
birth_date DATE,
Phone VARCHAR (12),
gender VARCHAR (1)
);

after creating table 

  • Press execute button
  • refresh button
  • see the picture below

 

TABLE CREATION COMMAND IN MYSQL

INSERT Multiple Rows IN MYSQL

Syntax;

INSERT INTO table_name (column 1,column 2,…..,)

VALUES

(Value1,Value 2,…),

(Value1,Value 2,…),

(Value1,Value 2,…),;

 

INSERT INTO personal (ID, name, birth_date, Phone, gender)
VALUES (1, “Parul Sharma”, “1991-10-05”, “941177878”, “F”),
(2, “Aman Singh”, “1990-05-09”, “942377828”, “M”),
(3, “Rajeev Kapoor”, “1990-05-16”, “989077878”, “M”),
(4, “Vivek Kapoor”, “1994-11-02”, “991557878” ,”M”);

Inset rows in mysql

Command to select the table data 

SELECT * FROM students.personal;

List of Constraints in MYSQL

The constraint in MySQL is used to specify the rule that allows or restricts what values/data will be stored in the table. They provide a suitable method to ensure data accuracy and integrity inside the table. It also helps to limit the type of data that will be inserted inside the table. If any interruption occurs between the constraint and data action, the action is failed.

Types of MySQL

Constraints
Constraints in MySQL is classified into two types:

Column Level Constraints: These constraints are applied only to the single column that limits the type of particular column data.
Table Level Constraints: These constraints are applied to the entire table that limits the type of data for the whole table.
How to create constraints in MySQL
We can define the constraints during a table created by using the CREATE TABLE statement. MySQL also uses the ALTER TABLE statement to specify the constraints in the case of the existing table schema.

Syntax

The following are the syntax to create a constraints in table:

CREATE TABLE new_table_name (
col_name1 datatype constraint,
col_name2 datatype constraint,
col_name3 datatype constraint,
………
);
Constraints used in MySQL
The following are the most common constraints used in the MySQL:

  • NOT NULL
  • CHECK
  • DEFAULT
  • PRIMARY KEY
  • AUTO_INCREMENT
  • UNIQUE
  • INDEX
  • ENUM
  • FOREIGN KEY

NOT NULL Constraint

This constraint specifies that the column cannot have NULL or empty values. The below statement creates a table with NOT NULL constraint.
CREATE TABLE Student(Id INTEGER, LastName TEXT NOT NULL, FirstName TEXT NOT NULL, City VARCHAR(35));

 

UNIQUE Constraint

UNIQUE Constraint
This constraint ensures that all values inserted into the column will be unique. It means a column cannot stores duplicate values. MySQL allows us to use more than one column with UNIQUE constraint in a table. The below statement creates a table with a UNIQUE constraint:

mysql> CREATE TABLE ShirtBrands(Id INTEGER, BrandName VARCHAR(40) UNIQUE, Size VARCHAR(30));

 

CHECK Constraint


It controls the value in a particular column. It ensures that the inserted value in a column must be satisfied with the given condition. In other words, it determines whether the value associated with the column is valid or not with the given condition.

mysql> CREATE TABLE Persons (
ID int NOT NULL,
Name varchar(45) NOT NULL,
Age int CHECK (Age>=18)
);

DEFAULT Constraint


This constraint is used to set the default value for the particular column where we have not specified any value. It means the column must contain a value, including NULL.

mysql> CREATE TABLE Persons (
ID int NOT NULL,
Name varchar(45) NOT NULL,
Age int,
City varchar(25) DEFAULT ‘New York’
);

PRIMARY KEY Constraint


This constraint is used to identify each record in a table uniquely. If the column contains primary key constraints, then it cannot be null or empty. A table may have duplicate columns, but it can contain only one primary key. It always contains unique value into a column.

REATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
Name varchar(45) NOT NULL,
Age int,
City varchar(25));

 

UTO_INCREMENT Constraint


This constraint automatically generates a unique number whenever we insert a new record into the table. Generally, we use this constraint for the primary key field in a table.

mysql> CREATE TABLE Animals(
id int NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id));

 

ENUM Constraint

 

ENUM Constraint


The ENUM data type in MySQL is a string object. It allows us to limit the value chosen from a list of permitted values in the column specification at the time of table creation. It is short for enumeration, which means that each column may have one of the specified possible values. It uses numeric indexes (1, 2, 3…) to represent string values.

mysql> CREATE TABLE Shirts (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(35),
size ENUM(‘small’, ‘medium’, ‘large’, ‘x-large’)
);

 

INDEX Constraint


This constraint allows us to create and retrieve values from the table very quickly and easily. An index can be created using one or more than one column. It assigns a ROWID for each row in that way they were inserted into the table.

mysql> CREATE TABLE Shirts (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(35),
size ENUM(‘small’, ‘medium’, ‘large’, ‘x-large’)
);

 

Foreign Key Constraint


This constraint is used to link two tables together. It is also known as the referencing key. A foreign key column matches the primary key field of another table. It means a foreign key field in one table refers to the primary key field of another table.

 

CREATE TABLE Persons (
Person_ID int NOT NULL PRIMARY KEY,
Name varchar(45) NOT NULL,
Age int,
City varchar(25)
);

Constraints Example

Syntax 

CREATE TABLE tabe_name (ID INT NOT NULL UNIQUE,

name VARCHAR(40) NOT NULL,

age INT NOT NULL CHECK(age>=18),

gender VARCHAR(1) NOT NULL,

phone VARCHAR(10) UNIQUE,

city VARCHAR(12) DEFAULT “Dehradun”

Insert table data

insert data in table mysql
select table rows mysql

MySQL AND, OR, NOT

The MySQL AND, OR and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

The AND operator displays a record if all the conditions separated by AND are TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax
SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;

Table data

AND OPERATOR IN MYSQL

SELECT * FROM personal WHERE age >= 18 AND age <= 20;

It shows all the rows with age between 18 to 20

OR OPERTOR IN MYSQL

OR OPERATOR IN MYSQL

SELECT * FROM personal WHERE age <= 19 OR gender = “F”;

If any one of critera is true then the answer will be shown.

OR OPERTOR IN MYSQL

MySQL IN & NOT IN OPERATOR

The WHERE in MySQL clause, when used together with the IN keyword only affects the rows whose values matches the list of values provided in the IN

SELECT * FROM personal WHERE age IN(18,21);

Its very similar to OR operator where it will show data of 18 or 21.

IN OPERATOR IN MYSQL

MYSQL NOT IN OPERATOR

The WHERE clause when used together with the NOT IN keyword DOES NOT affects the rows whose values matches the list of values provided in the NOT IN keyword

MySQL BETWEEN AND OPERATOR

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

SELECT * FROM personal WHERE age BETWEEN 18 AND 20;

BETWEEN OPERATOR IN MY SQL

MySQL NOT BETWEEN AND operator

MySQL NOT BETWEEN AND operator checks whether a value is not present between a starting and a closing expression.

SELECT * FROM personal WHERE age NOT BETWEEN 18 AND 20;

 

BETWEEN OPERATOR IN MY SQL

SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

The percent sign (%) represents zero, one, or multiple characters
The underscore sign (_) represents one, single character

SELECT * FROM personal WHERE name LIKE “s%”;

It start with S only.

MySQL ORDER BY COMMAND

  • SELECT * FROM personal ORDER BY name;

This will ordered by name from table.

 

  • SELECT * FROM personal WHERE city = “Dehradun” ORDER BY name DESC;

This will ordered by name in decending order where city is Dehrdun

  • SELECT * FROM personal ORDER BY age;

This will ordered list in table according to age.

MySQL ORDER BY command

SELECT DISTINCT COMMAND IN MYSQL

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, …
FROM table_name;

select distinct command in mysql

MySQL IS NULL & IS NOT NULL

NULL VALUE

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

Syntax.

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL IS WHERE IS NOT NULL

NOT NULL


IS NOT NULL Syntax


SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

 

null command in sql

The SQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

Update command in MYSQL 1

The SQL DELETE Statement

The SQL DELETE Statement
The DELETE statement is used to delete existing records in a table.

DELETE Syntax

DELETE FROM table_name WHERE condition;

Example :

DELETE FROM personal WHERE id = 4;

delete command in my sql
10 steps to start career in data science 5 Data Analytics Projects for Beginners 5 Excel Data Analysis Functions You Need to Know 5 Things in Your Resume from Getting Your First Job in Data Science 6 Ways Data Scientists Are Helping the Agricultural Sector 8 Strategies for Pharmaceutical Companies to Use Analytics for Success Applications of Data Science in the Retail Sector Best Data Analytics training in Dehradun Why to learn Best Data science Training in Dehradun Categories of SQL command to know for Data Analysis