Helpline Email- teamsip@thesmartbridge.

com

Apsche Nascom EL 16 June

Recordings:
[Link]

My SQL Workbench
​ Link: [Link]
​ Link: [Link]

Tableau is Data Visualization and Business Intelligence Tool which allows you to create
interactive dashboards and Stories from various data sources.

Insights are Hidden Information- Trend, Pattern etc

Started 2003, In June 2014 Salesforce Acquired Tableau

●​ Drag and Drop Interface

●​ Supports Numerous Data Sources
●​ Dashboard and Story
●​ Calculation and Formulas
●​ Mapping and Geographical Analysis

Tableau Products:
1.​ Tableau Desktop
2.​ Tableau Server
3.​ Tableau Online
4.​ Tableau Public
5.​ Tableau Prep Builder

Before Feb 2025 Tableau was offering 1 year free trails of Tableau Desktop to students

Tableau Desktop Professional Edition- 14 Days free trails

Tableau Desktop Public Edition - Free

Tableau Desktop Public Edition: [Link]

Database:
They are used to store large volume of data in structured format

Types of Database
●​ Relational Database
●​ Operational Database
●​ Distributed Database
●​ Cloud Database
●​ Enduser Database

MySQL Open Source Relational Database Management System by Oracle Corporation

●​ Opensource
●​ Cross Platform
●​ Security
●​ Community and Support

SQL: Structured Query language

Basic SQL Components

1.​ DDL- Data Definition Language- CREATE, ALTER, DROP

2.​ DML- Data Manipulation language- INSERT, UPDATE, DELETE
3.​ DQL- Data Query Language-SELECT
4.​ DCL- Data Control language- GRANT and REVOKE
5.​ TCL-Transaction control language

Basic SQL Commands

●​ SELECT- Used to retrieve the data from tables in database
●​ INSERT- Used to add new record to a table
●​ UPDATE- Used to modify existing record in table
●​ DELETE-Used to remove the record from the table
●​ CREATE- Used to create new database Objects
●​ ALTER- Used to modify the structure of existing table
●​ DROP-Used to delete the database objects
●​ GRANT- used to grant specific privileges to database users
●​ REVOKE- used to revoke previously granted privileges.
●​ TRUNCATE- Removes all record from table
MYSQL Workbench is an IDE by Oracle
●​ Database Design
●​ SQL Development
●​ Cross platform Compatibility
Mysql Edition
●​ MySQL Community Edition
●​ MySQL Standard Edition
●​ MySQL Enterprise Edition

Primary Key - Its column that uniquely identifies each row in a table
Foreign Key- It references another column in another table

Comments
-- singleline comment
/*multi-line
comment*/

CRUD Operation
C- Create
R- Read
U- Update
D- Delete

create database student;

use student;

Table name-Student
Student_Id, Name, Age, Grade

-- Create table
Create Table Students(
student_Id int Primary key auto_increment,
name varchar(50) Not Null,
age int,
grade varchar(5)
);

-- Insert Data
Insert into Students(name, age, grade) values
('Raju',20,'A'),
('Mohan',21,'B'),
('Suraj',23,'A'),
('Gita',22,'B'),
('Sam',19,'C');

select * from students;

select * from students where grade='A';

-- Update Data
update students set age=21 where name='Raju';

-- Delete Data
Delete from Students where name='Gita';

-- SQL Opeartions
select abs(-5) as Absolute_value;
select round(3.14159) as rounded_value;
select round(3.14159,2) as rounded_value;
-- Ceil()
select Ceil(4.25) as Ceil_value;
-- Floor()
Select floor(4.75) as floor_value;
-- Power()
select power(4,2);
select power(10,3) as cubes;
-- Sqrt()
select sqrt(144);
-- rand()
select rand() as random_number;
select mod(14,3) as remainder;
select greatest(2,5,6,18,20,12);
select least(2,5,6,18,20,12);
select truncate(22.7895,2);

Select Upper('student') as Upper_case;

select lower('STUDENT') as lower_case;
select character_length('India is My country') as total_length;
select length('India is My country') as length_of_string;

-- Concat()
select concat('India ' 'is ' 'in Asia') as mergerd;

-- Trim()
select trim(' Hello ') as Trimmed_String;

-- Replace()
select replace('Hello World','World', 'Universe') as Replaced_string;

select current_date;
select current_time as time;
select current_timestamp as current_date_time;

-- format()
select date_format(Now(),'%d-%m-%y') as formatted_date;

-- DateDiff()
select datediff('2025-01-01','2024-01-01') as date_difference;

Joins:
Inner join-Returns matching values in both Table
Left Join: Returns all records from left table and matching values from right table
Right Join: Returns all records from right table and matching values from left table
Cross Join

Table 1 Cricket_Students - Student id, Student Name

Table 2 Football_students- Student_id and Student_Name

create database joins;

use joins;

create table cricket_students(

student_id int primary key,
student_name varchar(50)
);

create table football_students(

student_id int primary key,
student_name varchar(50)
);
insert into cricket_students (student_id, student_name) values
(1,'Raju'),
(2, 'Suraj'),
(3, 'Mohan'),
(4,'Karan'),
(5,'Virat');

insert into football_students (student_id, student_name) values

(2, 'Suraj'),
(3, 'Mohan'),
(5,'Virat'),
(6,'Alex'),
(7,'Taylor');

select * from cricket_students;

select * from football_students;

-- inner join
select * from cricket_students
inner join football_students
on cricket_students.student_id= football_students.student_id;

-- inner join using alias

select * from cricket_students as c
inner join football_students as f
on c.student_id= f.student_id;

-- Left Join
select * from cricket_students as c
left join football_students as f
on
c.student_id=f.student_id;

-- right join;
select * from cricket_students as c
right join football_students as f
on c.student_id= f.student_id;
Dataset link:
[Link]

Segment- Consumer,Corporate and Home Office

Category-Furniture, Office Supply and Technology
Sub-category-17

Order date, Segment, Category, Sub Category, Sales

select * from superstore;
select * from superstore Limit 3;

select `Order Date`,Segment, Category,`Sub-Category`,Sales

from superstore limit 5;

-- Count()
select count(*) as 'No of Records' from superstore;

-- Sum()
select Sum(Sales) as Total_sales from superstore;
select round(Sum(Sales),2) as Total_sales from superstore;

-- Average()
select Avg(discount) as 'Average Discount' from superstore;
select round(Avg(discount),2) as 'Average Discount' from superstore;
select round(Avg(profit),2) as 'Average profit' from superstore;

-- Min()
select min(sales) as lowest_sales from superstore;
select round(min(profit),2) as lowest_profit from superstore;

-- Max()
Select max(sales) as Max_sales from superstore;
Select round(max(profit),2) as Max_Profit from superstore;

-- Rename a Column
alter table superstore
change column `Customer Name` Customer_name varchar(255);
# Where: Filters the rows based on condition before grouping or aggregation

List of records with only furniture;

Select * from superstore where Category = 'Furniture';

List all columns where category is furniture and region is south

select * from superstore where category='Furniture' and region='south';

List all columns where state is either New York or texas

Select * from superstore where state = 'New York' OR state= 'Texas';

List all columns where country is not equal to Unites States

Select * from superstore where not country = ‘United States';

# Group By- Group the rows that have same values in specified column , used with
aggregate function

No of Customers in table by country

Select country,count(*) as Customers from superstore
group by country;

No of customers by State
select state, count(distinct `Customer ID`) as Customers from superstore
group by State;
select Region, count(distinct `Customer ID`) as Customers from superstore
group by Region;

-List Unique Customer by Segment

select segment, count(distinct `Customer ID`) as Customers from superstore
group by segment;

select Customer_name, round(sum(sales),2) from superstore

group by customer_name;
# Having- filters group based on aggregate condition (used after Group By)

List the states with order more than 1000

select state, count(*) as Total_Orders
from superstore group by state
having Total_Orders>500;

List the states with sales>120000

Select state, round(sum(sales)) as Total_Sales
from superstore group by state
having sum(sales) > 120000;

List the customers with total sales >15000

Select customer_name, sum(sales) as total_sales
from superstore group by customer_name
having sum(sales) > 15000;

List the sub-categories with average profit >$50

Select `Sub-Category`, round(avg(profit),2)
from superstore group by `Sub-Category`
having avg(profit) > 50;

Order By- Sorts the result in ascending or descending order

select * from superstore order by sales;

select * from superstore order by sales desc;
select * from superstore order by `Category`;
select * from superstore order by Region, Customer_name;

select `Sub-category` , round(Sum(sales))

from superstore
group by `Sub-category`
order by sum(sales) desc limit 5;

Revenue= Sale * Quantity- Discount

-- Adding New column

Alter table superstore add Revenue int;

update superstore set Revenue= Sales * Quantity - Discount;

update superstore
set `Order Date`= str_to_date(`Order Date`, '%d-%m-%Y');

alter table superstore

modify `Order Date` Date;

Total Sale and Total Profit for each region in year 2015
Select Region,
round(sum(Sales)) as Total_Sales,
round(sum(profit)) as Total_Profit
from superstore
where year(`Order Date`)=2015
group by region;

-Bottom State
select state , round(Sum(sales))
from superstore
group by state order by sum(sales) limit 5;

-- To delete a column
alter table superstore
drop column Revenue;

Tableau Prep

Operation in tableau prep

1.​ Connections- Take data from multiple sources
2.​ Clean Data
a.​ Data type conversion
b.​ Normalization
c.​ Data Renaming
d.​ Remove unwanted columns
3.​ Transform Data
a.​ Splitting Columns
 b.​ Joining the data
c.​ Unioning of data
d.​ Aggregation
e.​ Pivoting
f.​ Calculate new fields
g.​ Sorting and Filtering
4.​ Handling null values

[Link]

For South- 4 datasets- 2015,2016,2017,2018→ South Region

Central- Clean Central —> Central Region

East -Clean East → East Regin

West- Clean West → West region

Combine All region tables into a single Region Table

Return Reason→ Clean Return

Left Join between Region and Return table

Central:

1. Region Column is Missing

2. Separate Columns for order and Ship wrt Day, month and Year

3. Datatype and None Values

West

1.​ Remove all the duplicate entries

2.​ State names are in abbreviated form
East

For sales prefix of USD

Central:

Calculation Field1

Name: Region

Expression: “Central

Calculation field 2

Name: Order Date

Expression: MAKEDATE([Order Year],[Order Month],[Order Day])

Calculation Field 3

Name: Ship Date

Expression: MAKEDATE([Ship Year],[Ship Month],[Ship Day])

Remove Fields- Order Year, Order Month, Order Day, Ship Year, Ship Month, Ship Day

West

AZ- Arizona

CA- California

CO-Colorado

ID- Idaho

MT-Montana

NV-Nevada
NM-New Mexico

OR-Oregon

UT - UTAH

WA - WASHINGTON

WY - WYOMING

East

Sales>Clean>Remove letters

Change type to Decimal

We have combine Central, South, East and West tables using Union Operation

And in Union Table we have merged the mismatched fields

Import Return Reason Table

Remove Row ID,Order Date,Sub-Category,manufacturer,Product name

Add clean step to return new table

Notes> Clean> Trim Spaces

Notes> Split Values> Automatic Split

Notes Spit2> Group Values> Common Characters

Removed the Notes

Renamed- Approver Name

Renamed- Approver Notes

Join Region and Returns using left join

Click on + to add cleaning step

Remove order id 1

Remove product id 1

Returned

No ( order was not returned)

Yes (Order was returned)

Calculation Field

Name- Returned

Expression:

IF ISNULL([Return Reason])THEN 'No'

Else 'yes' END

Days to Ship

DATEDIFF('day',[Order Date],[Ship Date])

Click on + > select output​

Mention the path and name of file and select format (.hyper, .xlsx, .csv)

Tableau Desktop

Qualitative Data-

●​ Describes or Categorizes Data

●​ You cannot perform calculations like Sum, Average or Mean

​ ​ ​

Quantitative Data-

●​ Numerical Data
●​ Can be used for Calculations
When we connect data to tableau, data fields are automatically assigned a role and a type

Filed data type is type of data- String, Integer or date

Field can be assigned either Dimension role or a Measure role

Dimension is a qualitative attribute

Measure is a quantitative attribute

Tableau autogenerates 1 Dimension( Measure Name) and 4 measure(latitude,

longitude,no of records and Measure values)

Column Shelf- It arranges data Horizontally and creates column to display datapoint

Row shelf- It arranges data Vertically and creates rows to display the data points

Tableau file formats:

[Link]

Superstore Dataset link:

[Link]
p=sharing&ouid=113247709954189786236&rtpof=true&sd=true

Product Name → Sub Category (17)→ Category (3)

Segments- Consumer, Home Office, Corporate

Create Revenue Column using Calculation Field​

[Sales] * [Quantity]-[Discount]

1.​ Show Comparison- Bar graphs, Column Chart,Stacked bar Chart etc
2.​ Show Trend over Time-Line chart, Area Chart, Dual Axis chart
3.​ Show Distributions- Histograms,Box Plots etc
4.​ Show Relationships - scatter plot, Bubble chart, heat map
5.​ Show Part to whole- Pie charts, Donut Chart
By default Measure placed in view are aggregated by SUM

Visualizations:

1.​ Bar Chart: It is a graphical representation of data where each bar represents
different categories or groups of data.

​ Average Sales of different Sub-Categories

​ C-Sub- Categories

​ R-Avg(Sales)

​ Col- Category