Tools & Practice Data

By the end of this lesson you will know which tools you need for this course, what is inside the practice shop database and the three CSV files, and the small differences between the browser editor and a real MySQL server. Five minutes here will save you confusion in every later lesson.

Think of a cooking class. Before the first recipe, the teacher shows you the kitchen: where the spices are, which burner is hot, which knife is sharp. This lesson is that kitchen tour.

The tools of the analyst path

You can do every runnable example in this course in your browser, with nothing installed. When you want to work on your own data, these are the usual tools:

You do not need all of them on day one. Start in the browser, then install one tool at a time as each section needs it.

Step 1: meet the shop database

The SQL examples use a small online shop that sells computer accessories. It has five tables. A table is a grid of rows and columns, and a key is a column that links tables together.

customers (customer_id, first_name, last_name, city, state, email, joined_on)
    |
    | customer_id
    v
orders (order_id, customer_id, order_date, status)
    |
    | order_id
    v
order_items (item_id, order_id, product_id, quantity, unit_price)
    ^
    | product_id
    |
products (product_id, product_name, category, price, stock)

employees (employee_id, first_name, last_name, department, job_title, salary, manager_id, hire_date)

One customer can place many orders, and one order can contain many items. Each item points to one product. This "one to many" shape is the most common pattern you will see in company databases.

Run this query to count the rows in every table:

SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL SELECT 'employees', COUNT(*) FROM employees;

table_name  | row_count
------------+----------
customers   | 12
products    | 12
orders      | 15
order_items | 22
employees   | 10
(5 rows)

What you should see: five rows. The database is tiny on purpose, so you can check every answer by eye. Real company tables have millions of rows, but the queries are exactly the same.

Step 2: know the browser differences

The Try it editor runs SQLite, a small database engine that works inside your browser. Company servers usually run MySQL. Every runnable query in this course works on both, but the output can look slightly different:

The division difference matters most. When both numbers are whole numbers, SQLite throws away the fraction. Multiply by 1.0 first, or by 100.0 for a percentage, and both databases agree.

SELECT 7 / 2 AS whole_division,
       7 * 1.0 / 2 AS decimal_division,
       ROUND(3 * 100.0 / 12, 1) AS percent_of_total;

whole_division | decimal_division | percent_of_total
---------------+------------------+-----------------
3              | 3.5              | 25
(1 row)

What you should see: 3, 3.5 and 25. In MySQL the first column would show 3.5000, so this course always writes * 1.0 or * 100.0 when dividing counts.

Step 3: meet the CSV files

The Python examples use three CSV files. CSV (comma-separated values) is a plain text file where each line is a row and commas separate the columns. These files are made-up practice data, and they are deliberately a little messy, like real data.

Load all three and look at their size and columns:

import pandas as pd

for name in ["sales.csv", "employees.csv", "students.csv"]:
    df = pd.read_csv(name)
    print(f"{name}: {df.shape[0]} rows, {df.shape[1]} columns")
    print("   ", ", ".join(df.columns))

sales.csv: 1217 rows, 10 columns
    order_id, order_date, city, state, category, product, quantity, unit_price, discount, payment_mode
employees.csv: 302 rows, 13 columns
    emp_id, name, gender, age, department, city, education, experience_years, join_date, salary, rating, work_mode, left
students.csv: 483 rows, 13 columns
    student_id, name, gender, city, school_type, attendance, study_hours, tuition, maths, science, english, hindi, social_science

What you should see: the size and the column names of each file. You can also download the files and the database script from the course page and open them in Excel, MySQL or Power BI on your own computer.

Step 4: peek at the rows

Always look at a few real rows before analysing. Numbers in a summary can hide strange values that are obvious when you see them.

import pandas as pd

emp = pd.read_csv("employees.csv")
print(emp.head(4).to_string(index=False))
print()
print(emp["department"].value_counts())

emp_id        name gender  age  department      city education  experience_years  join_date    salary  rating work_mode left
  E501  Karan Nair      M   29 Engineering Hyderabad    B.Tech                 3 2025-10-14  705000.0       1    Office   No
  E502  Neha Patel      F   25     Support   Chennai     B.Com                 3 2024-12-14  387000.0       3    Remote   No
  E503  Kabir Bose      M   40 Engineering Hyderabad    M.Tech                12 2024-07-03 1791000.0       3    Hybrid   No
  E504 Rahul Patel      M   25     Support Bengaluru    B.Tech                 3 2024-02-13  534000.0       3    Office   No

department
Engineering     97
Sales           60
Support         49
Data Science    38
Finance         29
HR              23
hr               2
support          2
finance          1
engineering      1
Name: count, dtype: int64

What you should see: four employees and a count per department. Look closely at the counts: some department names appear twice with different capital letters, such as HR and hr. That is the kind of mess you will learn to fix in A Data Cleaning Checklist.

Common mistakes

• Dividing two whole numbers in SQL. Use * 1.0 or * 100.0 so the browser and MySQL agree.
• Analysing before looking. Print a few rows first; summaries hide oddities.
• Installing everything at once. Learn in the browser, install tools as you need them.

Key points

• The browser editor runs SQLite for SQL and Python with pandas for the CSV files.
• The shop database has customers, orders, order_items, products and employees, linked by keys.
• Avoid whole-number division in SQL; multiply by 1.0 or 100.0 first.
• sales.csv, employees.csv and students.csv are invented and slightly messy on purpose.
• Always look at real rows before trusting a summary.

SELECT category,
       COUNT(*) AS products,
       ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM products), 1) AS pct
FROM products
GROUP BY category
ORDER BY products DESC, category;