As a data analyst, you will be working with LOTS of data. So the first and most natural thing to learn about is, how to use data. In this module, we will cover all the essential and vital concepts of working with databases, data sources and data repositories.
The key skills you will gain are,
In the very first lesson, learn how to use a database on your computer (with MySQL).
Resources and downloads for this lesson:
Watch the lesson:
You can watch this lesson below or on my YouTube channel here.
Introducing Excel + Power BI on-demand course for data analysts.
If you are looking for a one-stop place to learn all the advanced Excel, Power Query, Power Pivot and Power BI techniques to work as a data analyst, consider my online class today.
Now that you have SQL concepts and database understanding, let’s examine 10 practical, everyday data analysis scenarios and build SQL queries for the same. By the end of this lesson, you will learn:
Here are the 10 example SQL queries and home work problems. This is a blank file. Use it to practice and improve your SQL skills.
#10 Sample queries for SAKILA Database
# 1) All films with PG-13 films with rental rate of 2.99 or lower
# 2) All films that have deleted scenes
# 3) All active customers
# 4) Names of customers who rented a movie on 26th July 2005
# 5) Distinct names of customers who rented a movie on 26th July 2005
# H1) How many distinct last names we have in the data?
# 6) How many rentals we do on each day?
# 7) All Sci-fi films in our catalogue
# 8) Customers and how many movies they rented from us so far?
# 9) Which movies should we discontinue from our catalogue (less than 5 lifetime rentals)
# 10) Which movies are not returned yet?
# H2) How much money and rentals we make for Store 1 by day?
# What are the three top earning days so far?
Here are all the 10 queries completed against SAKILA sample database in MySQL.
# 1) All films with PG-13 RATING with rental rate of 2.99 or lower
SELECT * FROM film f
where f.rental_rate <=2.99
and f.rating = ‘PG-13’;
# 2) All films that have deleted scenes
select f.title, f.special_features, f.release_year
from film f
where f.special_features like ‘%Deleted Scenes%’
and title like ‘c%’;
# 3) All active customers
select * from customer
where active=1;
# 4) Names of customers who rented a movie on 26th July 2005
select r.rental_id, r.rental_date, r.customer_id,
concat(c.first_name,’ ‘, c.last_name) ‘Full name’ from rental r
join customer c on c.customer_id = r.customer_id
where date(r.rental_date) = ‘2005-07-26’;
# 5) Distinct names of customers who rented a movie on 26th July 2005
select distinct r.customer_id,
concat(c.first_name,’ ‘, c.last_name) ‘Full name’ from rental r
join customer c on c.customer_id = r.customer_id
where date(r.rental_date) = ‘2005-07-26’;
# H1) How many distinct last names we have in the data?
# 6) How many rentals we do on each day?
select date(rental_date) d, count(*) from rental
group by date(rental_date);
# 7) All Sci-fi films in our catalogue
SELECT * FROM CATEGORY;
SELECT * FROM film_category WHERE category_id=14;
select fc.film_id, fc.category_id,c.name, f.title, f.release_year from film_category fc
join category c on c.category_id = fc.category_id
join film f on f.film_id = fc.film_id
where c.name=’Sci-Fi’;
# 8) Customers and how many movies they rented from us so far?
select r.customer_id, c.first_name, c.email, count(*) ‘Count’
from rental r
join customer c on c.customer_id = r.customer_id
group by r.customer_id
order by count(*) desc;
select c.customer_id, c.first_name, c.email, count(r.customer_id)
from customer c
left outer join rental r on c.customer_id = r.customer_id
group by c.customer_id;
# 9) Which movies should we discontinue from our catalogue (less than 5 lifetime rentals)
// Refer to the correct query below.
with low_rentals as
(select inventory_id, count(*)
from rental r
group by inventory_id
having count(*)<=1)
select low_rentals.inventory_id, i.film_id, f.title
from low_rentals
join inventory i on i.inventory_id = low_rentals.inventory_id
join film f on f.film_id = i.film_id;
// CORRECT QUERY
with low_rentals as
(select i.film_id, count(*)
from rental r
join inventory i on i.inventory_id = r.inventory_id
group by i.film_id
having count(*)<=5)
select low_rentals.film_id, f.title
from low_rentals
join film f on f.film_id = low_rentals.film_id;
# 10) Which movies are not returned yet?
SELECT rental_date, customer_id, i.film_id, f.title
FROM RENTAL r
join inventory i on i.inventory_id = r.inventory_id
join film f on f.film_id = i.film_id
WHERE r.return_date is null
order by f.title;
# H2) How much money and rentals we make for Store 1 by day?
# What are the three top earning days so far?
# H1) How many distinct last names we have in the data?
select count(distinct last_name) from customer;
# H2) How much money and rentals we make for Store 1 by day?
select date(p.payment_date),sum(p.amount) from payment p
join rental r on r.rental_id = p.rental_id
join inventory i on i.inventory_id = r.inventory_id
where i.store_id = 1
group by date(p.payment_date)
order by date(p.payment_date);
# What are the three top earning days so far?
select date(p.payment_date),sum(p.amount) from payment p
join rental r on r.rental_id = p.rental_id
join inventory i on i.inventory_id = r.inventory_id
where i.store_id = 1
group by date(p.payment_date)
order by sum(p.amount) desc
limit 3
Watch the lesson:
Watch the lesson below or on my Channel here.
In the first two lessons of this free data analyst course, I’ve introduced the essential and necessary concepts of SQL. Please refer to below pages / videos for more on SQL.
While SQL is great for data analysis, it has one serious limitation. It can only work with data in a database / datawarehouse. What if the data you want to analyze is in a PDF or webpage or SharePoint folder? In such cases, SQL is almost useless.
This is where Power Query shines.
Power Query is the de-facto query language of Power BI and Excel. Using Power Query, we can connect to any kind of data and perform operations on it (like filtering data, grouping, joining, cleaning up, adding new columns or setting up automations).
So the last lesson of our data segment shall focus on Power Query and introduces how we can use it to work with business data problems.
Topics you will learn in this lesson:
Please grab the sample files for Power Query segment using the links below.
Learn more about Power Query using these pages and links:
Microsoft Excel is the #1 software used by Data Analysts all over the world. That is why, in our module 2 of this free data analyst course, we are going to learn all about Excel. The skills you will gain in this module are:
Technical concepts covered in this module are:
Software used:
Excel has 100s of features, so learning what is important can be tricky. In this video, let me distil the essential concepts and present them in a digestible format in just under 40 minutes.
This is “Essential” Excel in 40 minutes.
What you will learn:
Watch the lesson below (or on my YouTube channel):
Please download these files to learn the Essential Excel Concepts:
Refer to these pages and videos to learn more about Essential Excel Concepts:
Introducing Advanced Excel + Dashboards on-demand course for data analysts.
If you are looking for a one-stop place to learn all the advanced Excel, Power Query, Power Pivot and dashboard techniques to work as a data analyst, consider my online class today.
There are 200+ functions in Excel. But as a data analyst, you just need to know only a handful of them. In this lesson, we are going to learn the most important functions and formulas in Excel with 10 practical business problems.
Functions you will be learning are:
Watch the lesson below (direct YouTube link):
Individual tutorials on all the important functions:
Compilation articles:
Videos:
Courses:
While Excel formulas are powerful, you also need to plan and think hard before writing them to solve business problems. This is why I love Pivot tables. They are really easy to make and provide the biggest bang for buck. We can use Pivots to analyze data and answer business questions effectively and elegantly in short amount of time.
In this lesson, learn how to:
Watch the lesson below (Excel Pivot Tables Tutorial on YouTube):
Check out below pages and videos to learn more about various aspects of Pivot tables.
In our final lesson of Excel module, we will be building a call centre performance dashboard as show-cased above. In this portfolio project, you will learn:
Watch the video below or by going to the “Excel Portfolio Project – Complete Tutorial” video on my channel.
If you need an end-to-end, comprehensive and real-world Excel course, please consider my Excel School Program.
You will learn how I use Excel to solve business problems, how I write formulas and how I make charts in this online, self-paced course.
Don’t let the lack of Excel skills hold you back at work. Unleash your full potential with my Excel School program today.
Module 3 lessons will be added after 24 September, 2024. Stay tuned.
One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.
© All rights reserved.