Telegram Group: https://t.
me/+VQgeExQT-oI5NWZl
IIC Portal link: https://iic.apsche.ap.gov.in/
Refr Link:
https://drive.google.com/file/d/1liJ62rTlkDc2KaasHIxawWOzVtJe9tXp/view?usp=sharing
Self Paced Course:
https://skills.yourlearning.ibm.com/activity/PLAN-14F2691E3A32?ngo-id=0302&utm_campaign=aca-sm
artbridge-T30M-APSCHE-event#1
Go to above Link
SignUp with Registered Email
Click on enrol button for the Corse Getting started with Data
Recordings: https://www.youtube.com/playlist?list=PL3BTeoo2lf67xYPdqOpH9n_5mjL25b4nr
Project Development Phases
1. Ideation Phase
a. Brainstorming files
b. Empathy Map
c. Problem Statement
2. Requirement Analysis
a. Customer Journey Map
b. Data Flow Diagram
c. Solution Requirement
d. Technology Stack
3. Project Design Phase
a. Problem Solution Fit
b. Proposed Solution
c. Solution Architecture
4. Project Planing Phase
a. Project Planning Template
5. Functional and Performance Testing
a. Performance Testing
Files to submit
1. Dataset
2. Tableau file
3. Screeshot of Dashboard
4. Screenshot of Story
5. Public links of Dashboard and Story
6. Final Report
7. Video Demonstration
My SQL Workbench
Link: https://dev.mysql.com/downloads/workbench/
Link: https://www.youtube.com/watch?v=Rxp3T5GKlR4
Customer Needs,
● Quality of Product
● Excellent Customer Service
● Value for Money
● Transparency and Honesty
Decision making Process
1. Identify the Problem
2. Gather Information
3. Generate the Options
4. Evaluate the Option
5. Select the Best Option
6. Implement the Decision
Need of Data Visualization
● Simplifies Complexity
● Identifies Patterns
● Supports Decision
● Enhances Communication
Data Analytics- Use of tools, techniques to understand data for better decision making
DA Application
● HealthCare
● Retail
● Finance
● Sports
● Education
Data Analytics Process
1. Define Problem- Set clear objectives and goals
2. Data Collection- Gather relevant data from appropriate sources
3. Data Cleaning- Prepare and ensure data quality
4. Data Preprocessing- Transform and organise data for analysis
5. Data Analysis-Apply techniques to derive insights
6. Interpretation- Make sense of result and draw conclusion
7. Communication- Present insights in clear actionable manner
Types of Analytics
1. Descriptive Analytics- What happened
2. Diagnostic Analytics- Why did it happen
3. Predictive Analytics- What is likely to happen
4. Prescriptive Analytics- What should we do
Supermarket
1hr-20 transaction
10 hr- 200 transaction
30 days-6000 transaction
Small data
Large data
Business Intelligence: Business Intelligence helps to turn raw data into useful information that
business can act on.
Difference between BI tools and Excel
1. Scalability
2. Data Integration
3. Advanced Analytics
4. Visualization and Dashboard
Tableau- It was started in 2003 but was acquired by Salesforce in 2019
1. Data Connectivity
2. Drag and Drop Interface
3. Wide range of visualizations
4. Dashboard and storytellings
5. Sharing and collaboration
Products of Tableau
1. Tableau Desktop
2. Tableau Public
3. Tableau Server
4. Tableau Online
For Students– Tableau for Student till 1st Feb 2025
1 year free Tableau Desktop and Tableau Prep
Tableau Desktop Professional Edition( 14 days free trail)
Tableau Prep ( 14 days free trail)
Tableau Desktop- Public Edition ( free of Cost)
Visit : https://www.tableau.com/academic/students
Click on get Tableau for Free
Fill the form
Click on Download the app
Mysql
DataBase: Databases are used to store large amount of data in structured format.
Types of Databases:
● Relational Databases
● Operational Database
● Distributed Database
● Cloud Database
● End User Database
MySQL: Open Source RDBMS by Oracle Corporation
Open Source
Relational Database
Cross Platform
Security
Performance
Community and Support
SQL- Structured Query Language
MySQL Workbench is IDE
MYSQL Edition
● MySQL Community Edition
● MySQl Standard Edition
● MySQL Enterprise Edition
Basic SQl Components
1. DDL- Data Definition Language- CREATE, ALTER and DROP
2. DML- Data Manipulation Language- INSERT, UPDATE and 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 the database.
● INSERT-Used to add new records to a table
● UPDATE- Used to modify existing record in table
● DELETE- Used to remove record from table
● CREATE- Used to create new database objects
● ALTER- Used to modify the structure of existing database object.
● DROP-Used to delete database objects
● GRANT- Used to grant specific privileges to database user
● REVOKE- Used to revoke previously granted privileges.
Create database studentdb;
use studentdb;
Comments are of two types
1 Single line Comment -- single line comment
2. Multiline Line Comment
/*------------------------
-----------------*/
Primary Key- Uniquely identifying each row in a table
Foreign Key- referencing another column in another table
CRUD Operation
● Create (C)
● Read (R)
● Update (U)
● Delete (D)
Student table
Student_Id, Name, age and 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
('John',20, 'A'),
('Smith',21,'B'),
('Johny',23, 'A'),
('Sam',22,'B'),
('Bob',19,'C');
Read Data
Select * from Students;
Update Data
Update students Set age=21 where name='john';
Delete data
Delete from students where name='Bob';
truncate table students;
alter table students rename column age to years;
SQL Operations
select ABS(-5) as absolute_value;
select round(3.14159) as rounded_value;
select round(3.14159,3) as rounded_value;
Ceil() and floor()
Select Ceil(4.25) as ceil_value;
select floor(4.75) as floor_value;
Power()
select power(4,2);
select power(10,3) as cubes;
Square root()
select sqrt(144);
Exponential()
select exp(1) as exponential_value;
Rand()
select rand() as random_value;
Mod()
select mod(14,3) as remainder;
select greatest(2,5,18,6,12);
select least(2,5,18,6,12);
select truncate(22.89734235,2);
select upper('Indra Prakash') as Upper_case;
select lower('INDRA PRAKASH') 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 merged;
TRIM()
select trim(' Hello ') as Trimmed_String;
Replace()
select replace('Hello World', 'World','Universe') as replaced_string;
Current Date
select current_date as today;
select current_time as time;
select current_timestamp as current_timstamp;
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: Return all matching values in both table
Left Join: Return all records from left table and matching values from right table
Right Join: return all records from right table and matching values from left table
Cross Join:Return all reforms from both table
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');
select * from cricket_students;
Insert into football_students(student_id, student_name) values
(2,'Suraj'),
(3,'Mohan'),
(5,'Virat'),
(6,'Alex'),
(7,'Taylor');
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;
Cross Join-
select *
from cricket_students
cross join football_students;
26/5/2025
Dataset link:
https://drive.google.com/file/d/1i1lghiLngW2qF_vSxQASB9DuJQDyE-q3/view?usp=sharing
Segment- Consumer, Corporate and Home Office
Category- Furniture, Office Supply and Technology
Order Date, Segment, Category, Sub - Category
create database superstore;
use superstore;
select * from superstore;
select * from superstore Limit 5 ;
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(sales),2) as `Average Sales` from superstore;
Min()
select min(sales) as lowest_sales from superstore;
Max()
select max(sales) as Max_sales from superstore;
Rename a Column
alter table superstore
change column `Customer Name` Customer_name varchar(255);
27/5/2025
Where: Filters the rows based on condition before grouping or aggregation
select * from superstore where Category='Furniture';
select * from superstore where Category="furniture" and region="south";
select * from superstore where state="New York" or state="texas";
select * from superstore where not country='United States';
Group By: Group rows that have same values in specified column, often used with aggregate
function.
-- No of Customers in United States
select country,count(*) from superstore
Group By Country;
- No of Order by State
select State, count(`Row ID`) as total_customer from superstore
group by State;
- No of Orders By region
select region,count(*) from superstore group by region;
Select Count( Distinct `Customer ID`) as Unique_customer
from superstore ;
Select Category , Count(Distinct `Customer ID`) as Unique_Customer
from superstore
group by Category;
– Having: filters group based on aggregate condition ( Used after Group By)
List of States with orders more than 500
Select State, Count(*) as Total_Orders
from superstore group by State
having count(*)>1000;
List of States with sales>100000
select state, round(sum(sales)) as total_sales from superstore
group by state
having total_sales > 100000 ;
List of Customer with Total Sales> 15000
select Customer_Name,round(sum(sales)) as Total_sales
from superstore
group by Customer_Name
having Total_sales>15000;
List of Sub-Category with Average Profit more than $50
select `Sub-category`,round(avg(profit)) as Avg_profit
from superstore
group by(`Sub-Category`)
having Avg_profit > 50;
Order By- Sort the result in ascending of 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)) as total_sales
from superstore
group by `Sub-Category`
Order by total_Sales Desc limit 5;
Adding New Columns
Alter table superstore add Revenue int;
update Superstore set Revenue= Sales * Quantity;
Describe Superstore;
Update superstore
set `Order Date`=str_to_date(`Order Date`, '%d-%m-%Y');
alter table superstore
Modify `Order Date` Date;
-- Total Sales 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 Sales
- Select * from superstore order by Sales limit 5;
Top 5 State and their Sales
Select State, Sales from Superstore
order by Sales Desc limit 5;
To delete a column
alter table superstore
drop column Revenue;
Tableau Prep Builder: It was created for Data Preparation
Tableau Prep was introduced in 2018
Rebranded to Tableau Prep builder in 2019
Operations in Tableau Prep
1. Connection: You can take data from multiple sources
2. Clean Data:
a. Data type Conversion
b. Normalization: Standardise formats
c. Data Renaming:
d. Remove Unwanted Columns
3. Transform Data:
a. Splitting Columns- US-2002-113456 -> Country Code, Year, Ser No
b. Joining Data-
c. Unioning Data
d. Aggregation
e. Pivoting
f. Calculating New Fields
g. Sorting and Filtering
4. Handling Null Values
Tableau Prep: https://www.tableau.com/products/prep/download
Dataset Link: https://help.tableau.com/current/prep/en-us/prep_get_started.htm
Order Central
