Hello guys, welcome back to my blog. In this article, I will discuss SQL tutorial for data analytics, tutorial on SQL, structured query language, SQL operations, why we need to learn SQL, etc.
If you have any doubts related to electrical, electronics, and computer science, then ask question. You can also catch me @ Instagram – Chetan Shidling.
Also, read:
- What Is Competitive Programming, Tips To Practice Competitive Coding.
- Top 10 Websites To Learn Coding, How To Learn Coding, Programming.
- Roadmap To Become A Big Data And Analytics Engineer.
SQL Tutorial For Data Analytics
The businesses today are moving from traditional intuition based decision making to data driven decision making. Therefore, the use of SQL is now no longer limited to developers or programmers. Even business managers, analysts, and marketing researchers need to learn this language. With our professional experience of working with SQL, we have carefully designed this article. Keeping in mind the current and upcoming demands of the industry.
This is a comprehensive course that will take you from beginner to the advanced level without wasting your time. Topics that we cover are:
- Basics Of Database
- Data Management
- Fundamental SQL Commands
- Data Filtering And Sorting
- Aggregate And Group By Commands
- Joins
- Advanced Concepts – Subqueries, Views, Indexes
- String Functions
- Mathematical Functions
- Data Time Function
- Data Type Conversion Function
- Performance Tuning
- Pattern Matching
- Bonus Lectures
SQL is simple and it just needs practice, so read this article and also practice queries. Let’s begin this SQL tutorial for data analytics.
Before starting this tutorial, step up PostgreSQL in our PC. Click here to download.
Create Table
Creating a basic table involves naming the table and defining its columns and each column’s data type. When we are creating a table it is important that we design it very carefully. The better-designed table will be taking up only the required space and it will be quick in giving back the response to queries. So to create a table using a creat command, we need to name the table and also give names of all the columns and their data types. Below is the syntax to create a table.
Syntax to create table
CREATE TABLE "table_name"(
"column 1" "data type for column 1" [column 1 constraint(s)],
"column 2" "data type for column 2" [column 2 constraint(s)],
.......
.........
"column n"
[table constraint(s)]);
Types of constraints
Now, we will look at what all constraints are available to be put on the columns. Majorly there are six types of constraints.
- NOT NULL Constraint: Ensures that a columns cannot have NULL value.
- DEFAULT Constraint: Provides a default value for a column when none is specified.
- UNIQUE Constraint: Ensures that all values in a column are different.
- CHECK Constraint: Makes sure that all values in a column satisfy certain criteria.
- Primary Key Constraint: Used to uniquely identify a row in the table.
- Foreign Key Constraint: Used to ensure referential integrity of the data.
Difference between primary key and foreign key
- A primary key is used to uniquely identify each row in a table.
- A primary key can consist of one or more columns on a table.
- When multiple columns are used as a primary key, they are called a composite key.
- A foreign key is column (or columns) that references a column (most often the primary key) of another table.
- The purpose of the foreign key is to ensure referential integrity of the data.
SQL code to create table
create table Customer_table(
cust_id int,
First_name varchar,
Last_name varchar,
age int,
email_id varchar);
Insert
The addition of data in tables is done using the “INSERT INTO” command. The INSERT INTO statement is used to add new records into a database table. The syntax is given below.
Syntax to insert
INSERT INTO "table_name"("column1", "column2",...)
VALUES("Value1","Value2",...)
We write “INSERT INTO”, then the table name within the brackets we will specify the name of the columns and specifying column names is optional, next comes the keyword values, here we will specify the values to be inserted in the record. Now if the column names are not specified, the values will be entered in the order of columns.
Examples:
Single row (without column names specified)
INSERT INTO customer_table
VALUES (1, 'Chetan','cee',32,'bc@xyz.com');
Single row (with column names specified)
INSERT INTO customer_table(cust_id, first_name, age, email_id)
VALUES(2,'Nitin',22,'dwed@gmail.com');
Multiple rows
INSERT INTO customer_table
VALUES(1,'chetan','shidling',22,'chj@gmail.com'),
(1,'Nitin','shidling',42,'chj@gmail.com'),
(1,'Raghu','R',21,'chj@gmail.com'),
(1,'Kiran','E',28,'chj@gmail.com');
Import data from file
Let us now learn how to import data from a file into a table, this can be done using the copy command. The basic syntax to import data from CSV file into a table using COPY statement is as below.
Syntax of copy command
COPY "table_name"("column1","column2",...)
FROM 'C:\files\doc\chetan.csv' DELIMITER','CSV HEADER;
Another option is to use PG Admin
The delimiter is the symbol that is segregating different columns of your data. Then comes the type of file it is CSV we will write CSV. Then if the data has headers we will write a header, the header keyword works only if it a CSV file. So, if it is a text file the header keyword will not work, you will have to remove the header from the text file and then import the data.
Copy data from a text file
copy customer_table from 'C:\Users\copytext.txt' delimiter ',';
Select
Retrieval of data from tables is done using the select command. The SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets.
Syntax for select command
SELECT "column_name1","column_name2","column_name3" FROM "table_name";
SELECT * FROM "table_name";
Select command is used to fetch the data from database and the result table that you get is called result sets.
Examples:
To select one column
SELECT first_name FROM customer_table;
To select multiple columns
SELECT first_name, last_name FROM customer_table;
To select all columns
SELECT * FROM customer_table;
Use distinct keyword to remove duplicate values.
select distinct first_name from customer_table;
select distinct * from customer_table;
Where
Now, we will see how to retrieve data which is satisfying a particular specified condition. Condition is specified using the where clause. The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table.
Syntax
SELECT "column_name"
FROM "table_name"
WHERE "condition";
Examples:
Equals to condition
SELECT first_name FROM customer_table WHERE age = 25;
Less than/Greater than condition
SELECT first_name, age FROM customer_table WHERE age>25;
Matching text condition
SELECT * FROM customer_table WHERE frist_name = "Chetan";
Logical Operators
Let us now look at how to retrieve data which is satisfying multiple conditions. Multiple conditions can be applied using the and, or operators. AND operators is when we want that all of the conditions are satisfied. Whereas or operator will be used when we want to retrieve the data with anyone of the condition is satisfied. The SQL AND & OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called conjunctive operators.
Syntax for AND, OR operator
SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND|OR] "simple condition"}+;
Examples:
AND operation
SELECT first_name, last_name, age
FROM customer_table
WHERE age>20
AND age<30;
OR operation
SELECT first_name, last_name, age
FROM customer_table
WHERE age<20
OR age>30
OR first_name = 'Chetan';
Syntax for NOT operator
NOT condition is used to negate a condition in a SELECT, INSERT, UPDATE, or DELETE statement.
SELECT "column_name"
FROM "table_name"
WHERE NOT "simple condition"
There is one customer with age 25, if you want to find out all the other customers we can just use NOT.
Examples:
SELECT first_name, last_name, age
FROM employee
WHERE NOT age=25;
SELECT first_name, last_name, age
FROM employee
WHERE NOT age=25
AND NOT first_name = 'Chetan';
Update Operations
The SQL update query is used to modify the existing records in a table. Below is the syntax, it starts with update, then we mention the table name, then is the keywords set, after that we mention the column, and its value which is to be set. We can specify multiple columns, and then we an give where condition. We can use WHERE if you want to update particular records.
Syntax
UPDATE "table_name"
SET column_1 = [value1], column_2 = [value2],...
WHERE "condition";
Examples:
Single row (with column names specified)
UPDATE Customer_table
SET Age = 19, Last_name ='Shi'
WHERE Cust_id = 2;
Multiple rows
UPDATE Customer_table
SET email_id = 'chetu@gmail.com'
WHERE First_name = 'Chetan' OR First_name = 'Nitin';
Delete Operations
The DELETE query is used to delete the existing records from a table. The syntax is given below,
Syntax
DELETE FROM "table_name"
WHERE "condition";
Examples:
Single row
DELETE FROM CUSTOMERS
WHERE ID = 6;
Multiple rows
DELETE FROM CUSTOMERS
WHERE age>25;
All rows
DELETE FROM CUSTOMERS;
Alter
The ALTER table statement is used to change the definition or structure of an existing table.
Syntax
ALTER TABLE "table_name"
[Specify Actions];
Following actions can be performed:
- Columns – Add, Delete (drop), Modify, or Rename
- Constraints – Add, Drop
- Index – Add, Drop
Column – Add & Drop
Syntax
ALTER TABLE "table_name"
ADD "column_name" "Data Type";
Example:
alter table customer_table add test varchar(255);
Drop command is used to delete the row or column.
ALTER TABLE "table_name"
DROP "column_name";
Example:
alter table customer_table drop test;
Column – Modify & Rename
The basic syntax of an ALTER TABLE command to modify/rename a column in an existing table is as follows.
Syntax to alter data type
ALTER TABLE "table_name"
ALTER COLUMN "column_name" TYPE "New Data Type";
Example:
alter table customer_table alter column age type varchar(255);
Syntax to rename data type
ALTER TABLE "table_name"
RENAME COLUMN "column 1" TO "column 2";
Example:
alter table customer_table rename column email_id to customer_email;
Constraint – Add or Drop
The basic syntax of an ALTER TABLE command to add/drop a Constraint on a existing table is as follows.
Syntax
ALTER TABLE "table_name" ALTER COLUMN "column_name" SET NOT NULL;
ALTER TABLE "table_name" ALTER COLUMN "column_name" DROP NOT NULL;
ALTER TABLE "table_name" ADD CONSTRAINT "column_name" CHECK ("column_name">=100);
Below is the syntax to make column a primary key.
ALTER TABLE "table_name" ADD PRIMARY KEY("column_name');
ALTER TABLE "child_table" ADD CONSTRAINT "child_column"
FOREIGN KEY("parent column") REFERENCES "parent table";
Still many topics we will cover.
I hope this article may help you all a lot. Thank you for reading.
Also, read:
- 100+ C Programming Projects With Source Code, Coding Projects Ideas
- 1000+ Interview Questions On Java, Java Interview Questions, Freshers
- App Developers, Skills, Job Profiles, Scope, Companies, Salary
- Applications Of Artificial Intelligence (AI) In Renewable Energy
- Applications Of Artificial Intelligence, AI Applications, What Is AI
- Applications Of Data Structures And Algorithms In The Real World
- Array Operations In Data Structure And Algorithms Using C Programming
- Artificial Intelligence Scope, Companies, Salary, Roles, Jobs
About The Author
