SQL (Structured Query Language) is one of the most important skills for us, data people. So in this article + video, get the necessary SQL skills you need for Data Analysis work.
Step 0: Install MySQL software
I am using the FREE MySQL Community Edition software to learn & practice SQL at home. You can get it from here.
If you have any other database software available (such as SQL Server or Oracle), you can use them to follow this tutorial.
Step 1: Import Awesome Chocolates Dataset
You need some data to practice SQL. So I prepared a sample dataset for a fictional (but yummy) company called Awesome Chocolates.
Download the .SQL file from here.
After you have the file,
- Open MySQL Workbench, login if necessary
- Click on the “server administration” tab (see illustration, click to expand)
- Click on “Data Import/Restore”
- Select the option “Import from self-contained file”
- Specify the path of the downloaded awesome-chocolates-data.sql file
- Start import
At the end of these steps, your MySQL should have the awesome chocolates database. Congratulations 🎉🥳
You can see this from “Schemas” tab on the workbench
Using SQL Server?
You can also use SQL Server to practice SQL. If you are using SQL Server Management Console, then follow below steps to import the data.
- Download this SQL Server Backup file
- Unzip the file
- Open SSMS & right click on the databases and chose “Restore Database” option. Follow the steps on that screen using below screenshots.
Step 2: Learn SQL for Data Analysis with this video
Everything is ready. Time to learn SQL.
I made an hour long tutorial to explain all the necessary SQL concepts for you. In this video, you will learn:
- How to use SELECT statement to answer business questions
- Working with WHERE clause
- Using AND, OR, NOT and combining them to create complex queries.
- Sorting query results using ORDER BY
- Combining data from two or more tables using JOINS
- Creating reports with GROUP BY
- More than 50 example queries, tips and ideas
Please watch the video below or on my YouTube Channel.
The Queries
Here are some of the example queries covered in the video lesson. Feel free to copy paste them in to SQL console to see how they work.
-- Select everything from sales table
select * from sales;
-- Show just a few columns from sales table
select SaleDate, Amount, Customers from sales;
select Amount, Customers, GeoID from sales;
-- Adding a calculated column with SQL
Select SaleDate, Amount, Boxes, Amount / boxes from sales;
-- Naming a field with AS in SQL
Select SaleDate, Amount, Boxes, Amount / boxes as 'Amount per box' from sales;
-- Using WHERE Clause in SQL
select * from sales
where amount > 10000;
-- Showing sales data where amount is greater than 10,000 by descending order
select * from sales
where amount > 10000
order by amount desc;
-- Showing sales data where geography is g1 by product ID &
-- descending order of amounts
select * from sales
where geoid='g1'
order by PID, Amount desc;
-- Working with dates in SQL
Select * from sales
where amount > 10000 and SaleDate >= '2022-01-01';
-- Using year() function to select all data in a specific year
select SaleDate, Amount from sales
where amount > 10000 and year(SaleDate) = 2022
order by amount desc;
-- BETWEEN condition in SQL with < & > operators
select * from sales
where boxes >0 and boxes <=50;
-- Using the between operator in SQL
select * from sales
where boxes between 0 and 50;
-- Using weekday() function in SQL
select SaleDate, Amount, Boxes, weekday(SaleDate) as 'Day of week'
from sales
where weekday(SaleDate) = 4;
-- Working with People table
select * from people;
-- OR operator in SQL
select * from people
where team = 'Delish' or team = 'Jucies';
-- IN operator in SQL
select * from people
where team in ('Delish','Jucies');
-- LIKE operator in SQL
select * from people
where salesperson like 'B%';
select * from people
where salesperson like '%B%';
select * from sales;
-- Using CASE to create branching logic in SQL
select SaleDate, Amount,
case when amount < 1000 then 'Under 1k'
when amount < 5000 then 'Under 5k'
when amount < 10000 then 'Under 10k'
else '10k or more'
end as 'Amount category'
from sales;
-- GROUP BY in SQL
select team, count(*) from people
group by team
SQL Practice Problems
Once you understand the concepts I’ve demoed in the video, try to solve below homework problems.
If you want to cheat, use the solutions tab to see the answers.
INTERMEDIATE PROBLEMS
👉 You need to combine various concepts covered in the video to solve these
1. Print details of shipments (sales) where amounts are > 2,000 and boxes are <100?
2. How many shipments (sales) each of the sales persons had in the month of January 2022?
3. Which product sells more boxes? Milk Bars or Eclairs?
4. Which product sold more boxes in the first 7 days of February 2022? Milk Bars or Eclairs?
5. Which shipments had under 100 customers & under 100 boxes? Did any of them occur on Wednesday?
HARD PROBLEMS
👉 These require concepts not covered in the video
1. What are the names of salespersons who had at least one shipment (sale) in the first 7 days of January 2022?
2. Which salespersons did not make any shipments in the first 7 days of January 2022?
3. How many times we shipped more than 1,000 boxes in each month?
4. Did we ship at least one box of ‘After Nines’ to ‘New Zealand’ on all the months?
5. India or Australia? Who buys more chocolate boxes on a monthly basis?
INTERMEDIATE PROBLEMS:
— 1. Print details of shipments (sales) where amounts are > 2,000 and boxes are <100?
select * from sales where amount > 2000 and boxes < 100;
— 2. How many shipments (sales) each of the sales persons had in the month of January 2022?
select p.Salesperson, count(*) as ‘Shipment Count’
from sales s
join people p on s.spid = p.spid
where SaleDate between ‘2022-1-1’ and ‘2022-1-31’
group by p.Salesperson;
— 3. Which product sells more boxes? Milk Bars or Eclairs?
select pr.product, sum(boxes) as ‘Total Boxes’
from sales s
join products pr on s.pid = pr.pid
where pr.Product in (‘Milk Bars’, ‘Eclairs’)
group by pr.product;
— 4. Which product sold more boxes in the first 7 days of February 2022? Milk Bars or Eclairs?
select pr.product, sum(boxes) as ‘Total Boxes’
from sales s
join products pr on s.pid = pr.pid
where pr.Product in (‘Milk Bars’, ‘Eclairs’)
and s.saledate between ‘2022-2-1’ and ‘2022-2-7’
group by pr.product;
— 5. Which shipments had under 100 customers & under 100 boxes? Did any of them occur on Wednesday?
select * from sales
where customers < 100 and boxes < 100;
select *,
case when weekday(saledate)=2 then ‘Wednesday Shipment’
else ”
end as ‘W Shipment’
from sales
where customers < 100 and boxes < 100;
HARD PROBLEMS:
— What are the names of salespersons who had at least one shipment (sale) in the first 7 days of January 2022?
select distinct p.Salesperson
from sales s
join people p on p.spid = s.SPID
where s.SaleDate between ‘2022-01-01’ and ‘2022-01-07’;
— Which salespersons did not make any shipments in the first 7 days of January 2022?
select p.salesperson
from people p
where p.spid not in
(select distinct s.spid from sales s where s.SaleDate between ‘2022-01-01’ and ‘2022-01-07’);
— How many times we shipped more than 1,000 boxes in each month?
select year(saledate) ‘Year’, month(saledate) ‘Month’, count(*) ‘Times we shipped 1k boxes’
from sales
where boxes>1000
group by year(saledate), month(saledate)
order by year(saledate), month(saledate);
— Did we ship at least one box of ‘After Nines’ to ‘New Zealand’ on all the months?
set @product_name = ‘After Nines’;
set @country_name = ‘New Zealand’;
select year(saledate) ‘Year’, month(saledate) ‘Month’,
if(sum(boxes)>1, ‘Yes’,’No’) ‘Status’
from sales s
join products pr on pr.PID = s.PID
join geo g on g.GeoID=s.GeoID
where pr.Product = @product_name and g.Geo = @country_name
group by year(saledate), month(saledate)
order by year(saledate), month(saledate);
— India or Australia? Who buys more chocolate boxes on a monthly basis?
select year(saledate) ‘Year’, month(saledate) ‘Month’,
sum(CASE WHEN g.geo=’India’ = 1 THEN boxes ELSE 0 END) ‘India Boxes’,
sum(CASE WHEN g.geo=’Australia’ = 1 THEN boxes ELSE 0 END) ‘Australia Boxes’
from sales s
join geo g on g.GeoID=s.GeoID
group by year(saledate), month(saledate)
order by year(saledate), month(saledate);
Resources to Learn More
SQL is a great skill to have if you work with data. Please use below courses, books, articles & websites to learn more.
SQL BOOKs 📚
I recommend getting these SQL books.
SQL COURSEs 💻
I recommend trying out these courses on SkillShare academy.
SQL WEBSITEs 🌐
Do check out these helpful websites to learn and understand various SQL concepts.
If you use my links to purchase the books or courses, I get a small affiliate commission.
There is no extra cost to you, obviously.
SQL Alternatives
If you want an alternative to SQL, consider learning Power Query.
Here is an article and here is a video to help you with that.
All the best 👍
I wish you all the best with your SQL learning. Do let me know in the comments below if you have enjoyed this article and the video.
17 Responses to “Custom Number Formats – Colors”
You are right, Chandoo. I was playing with the colour numbers last week and some of them don't appear different from each other. Others are totally different from yours.
@Duncan
Each version of Excel, post 2003, renders colors slightly differently
Different language versions may also have different default color palettes
Hello in french
excel 2010
colo1 = couleur1 = black
[couleur1]; [couleur2]; etc..
@Hui, thank you very much again for this great post.
However - under Excel 2007, Hungarian version your solution does not work with color names. I've tried both English and Hungarian names, but drops an error message "not valid formats"
Do you have any idea how to solve this issue?
thanks in advance
@Andras
Without a Hungarian version of Excel 2003 I don't think I can assist
Have you tried using the colour numbers? I couldn't get the names to work (despite using an english version of excel). but it did work with the numbers though. I left out the "u" and was easily able to produce burgundy using [color9]
Here a possible solution: find an English version of Excel, write there the formats using English names, then open the file in the Hungarian version and see the translation.
In Excel 2007 I can't get the colour names to work e.g Sea Green but the numbers do e.g color3 - colour3 does not work so I must bow to the country that has stolen my language (ha ha!)
Hey chandoo, nice Tip!
Wouldn't be easier just apply some conditional formatting for negative numbers and another for positive numbers? Or there's some cases that you can't do that?
Unfortunately the TEXT function doesn't color the cell as number formatting does.
Hi Hui,
Great post Sir, love the new way of formatting with color numbers.
I am using 2007, and it leads me to the last color number 56.
Thanks Hui.
[…] explains how to set up custom number formats with a wide array of […]
Thanks Hui - works a treat!
Thank you, very helpful.
Trying to figure out if it is possible to apply color only to a part of the cell?
E.g. I have a value formatted as Accounting with a currency symbol.
Those I find somewhat distracting though necessary. If I could make them less obtrusive by coloring them gray while the number would stay black, that would be great. Tried tinkering with the format string, but didn't get the desired result. Single color for complete cell value works, but coloring just part of it could not be achieved. Maybe somebody managed that?
Exactly what I was looking for - thank you!
colour in the Australian doesn't work - we have to go American and no problem.
I always thought is was 56 colours notice you have 57. Cool.
thanks
Analir Pisani
Customised Microsoft Office Training Specialist
Sydney - Australia
http://www.azsolutions.com.au
Thank You!