DataraFlow Week 6: Learning SQL and Data Analysis Through Challenges, Troubleshooting, and Practice

Introduction

Week 6 of my DataraFlow Cohort 2 journey has been one of the most demanding but valuable learning experiences so far. It challenged me to go beyond simply writing Python code and helped me understand the practical steps involved in accessing, preparing, exploring, and analysing data. This week, I worked with SQL, Python, NumPy, Pandas, database tables, VS Code and Google Colab. I also learned how to move data from the DataraFlow cohort database into a Python environment for further analysis.

However, the journey was not straightforward. I encountered several challenges involving database access, file locations, exporting datasets, transferring files into Google Colab, understanding SQL queries, and ensuring that my code worked as expected. At different points, I had to stop, investigate the problem, try another approach, and verify the results before moving forward.

In this article, I share what I learned, what I worked on, the challenges I encountered, and how I approached solving them.

1. What I Learned This Week

Understanding data access with SQL

One of the major concepts I explored this week was accessing data stored in a relational database. The Week 6 datasets were hosted in the DataraFlow cohort database under the week06 schema. Rather than downloading each dataset from a separate website, I could retrieve it by writing SQL queries.

I learned how to use SQL statements to select data from specific tables and how to inspect database structures before writing more complex queries.

I also became more familiar with SQL concepts such as:

• SELECT for retrieving data.
• WHERE for filtering records.
• GROUP BY for grouping data and calculating summaries.
• Aggregate functions such as AVG(), SUM(), and COUNT().
• UNION ALL for combining records from multiple tables.
• Window functions for calculating running totals and ranking records.
• INFORMATION_SCHEMA.COLUMNS for inspecting database column names and data types.

An important detail I learned was that column names containing spaces, such as [Mean TemperatureC], must be enclosed in square brackets when querying SQL Server tables. This reinforced the importance of understanding the structure of a dataset before attempting to analyse it.

Working with Python, NumPy, and Pandas

The assignment also gave me an opportunity to practise Python-based data analysis. With NumPy, I worked on exercises involving missing values, numerical comparisons, complex numbers, tolerance-based comparisons, and memory usage. With Pandas, I practised creating and manipulating Series, converting data between different structures, performing arithmetic operations, sorting values, and exploring DataFrames.

I also learned more about selecting rows using integer positions. For example, df.iloc[54] returns the 55th row because Python uses zero-based indexing. These exercises reminded me that understanding small details in Python is essential. A simple indexing mistake or an incorrect assumption about a data type can affect the result of an analysis.

2. What I Worked On

Accessing and exporting the Week 6 datasets

The Week 6 database included daily weather datasets for six cities: London, Beijing, Brasília, Cape Town, Delhi, and Moscow. Each city had 365 records for 2014. It also included a WHO population and tuberculosis dataset containing 194 records and three columns.

My first major practical task was retrieving these datasets from the database and making them available for analysis in Google Colab. I used the provided run_query() function to execute SQL queries from Python. I then exported the retrieved tables into CSV files so that I could upload them to Colab. The export process completed successfully, and the dataset dimensions matched the expected values.

Dataset Rows Columns London weather 36523 Beijing weather 36523 Brasília weather 36523 Cape Town weather 36523 Delhi weather 36523 Moscow weather 36523 WHO population and TB data1943 This was an important milestone because it showed me that I could retrieve data from a database, save it in a portable format, and prepare it for use in another environment.

Exploring the WHO population and TB dataset

After uploading the CSV files to Google Colab, I loaded the WHO dataset into a Pandas DataFrame.

The dataset contained the following columns:

• Country
• Population (1000s)
• TB deaths

I checked its shape and column names before proceeding. The dataset loaded with 194 rows and three columns, matching the expected structure. I then explored the data using commands such as df.head(10) and df.iloc[54].

The 55th row returned El Salvador, with a population of 6,340 thousand and 61 reported TB deaths in the dataset. I also displayed the first ten records to understand how the data was organised. These exercises helped me practice basic data inspection and understand why it is important to validate a dataset before carrying out more detailed analysis.

Loading and validating the WHO population and tuberculosis dataset in Google Colab.

Preparing the SQL assessment queries

Another major part of the week’s work involved preparing SQL queries for different analytical tasks.

The assessment covered database metadata, missing values in weather records, customer churn, international milk exports, population and tuberculosis indicators, temperature comparisons across cities, monthly temperature extremes, cumulative export values, and loan approval patterns. I organized the queries into separate variables and worked through the tasks individually. This made it easier to identify which query needed attention instead of trying to debug everything at once.

Get bayo dogo Nyangskeb’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

For the weather comparison task, for example, I used UNION ALL to combine temperature observations from the six city tables. I also prepared a query to calculate average temperatures by city. For the monthly temperature task, I used date conversion and a ranking function to identify the hottest day in each month, with an earlier date taking priority when temperatures tied.

For the milk export task, I prepared a query using aggregation and a window function to calculate monthly exports and a running year-to-date total. These exercises helped me appreciate how SQL can do more than retrieve individual records. It can also combine datasets, summarise information, rank observations, and calculate cumulative metrics.

3. The Challenges I Faced

One thing I can honestly say about Week 6 is that it required more troubleshooting than I initially expected.

Challenge 1: Locating and creating the correct files

At one point, I could not find a file in the folder where I expected it to be. This interrupted my progress because I needed the file to continue working. Instead of repeatedly searching for it, I created the Python script directly from the terminal. This gave me greater certainty about where the file was saved and allowed me to run it from the project directory. The experience taught me to pay attention to working directories, file paths, and the relationship between a script’s location and the files it needs.

Challenge 2: Moving data between the database and Google Colab

Another challenge was that the datasets were stored in the DataraFlow cohort database, while I wanted to perform some of the analysis in Google Colab. I initially had to work out how to export the tables and transfer them into Colab. This involved writing a Python script, saving the results as CSV files, locating the export folder, uploading the files, and checking that they were accessible in the notebook. The process involved several steps, and missing or incorrect file paths could easily prevent the code from working.

Challenge 3: Understanding how to load and validate the DataFrame

After uploading the files, I still needed to load the correct dataset and confirm that the columns matched those expected by the assignment. I used Pandas to read the CSV file and inspect its shape and column names. This helped me confirm that the WHO dataset was loaded correctly before answering the DataFrame questions. I learned not to assume that a dataset is ready simply because a file has uploaded successfully. It is necessary to inspect the data and verify its structure.

Challenge 4: Writing SQL queries with the correct column names

Some SQL tasks involved tables outside the week06 schema, including datasets for customer churn, international trade, population, and loan approval. The difficulty was ensuring that each query used the actual column names and values available in the relevant table. A query can appear logically correct but still fail if a column name is wrong or if the data uses different labels than expected. This made inspecting the database schema an important part of my approach.

Challenge 5: Managing a long assignment without becoming overwhelmed

The assignment covered several topics, and attempting to handle all of them at once would have made the work more confusing. I therefore worked through the tasks in stages: preparing the files, exporting the datasets, uploading them, checking the DataFrame, completing the initial Pandas questions, and preparing the SQL queries. Progress was not always fast, but breaking the work into smaller steps made it easier to understand what I was doing and where a problem occurred.

4. How I Overcame the Challenges

The most useful lesson was learning to approach errors and obstacles systematically rather than giving up when something did not work.

First, I created the missing script directly from the terminal so that I could control its location. I then ran it using the Python interpreter in my Anaconda installation. Second, I exported the database tables to CSV files. I checked the printed dimensions to verify that the exports completed successfully. The weather tables each showed 365 rows and 23 columns, while the WHO table showed 194 rows and three columns.

Third, I uploaded the CSV files into Google Colab and used Pandas to inspect the WHO dataset. Checking the shape, column names, and first few records helped confirm that the correct file was loaded.

Finally, I organised the SQL queries into separate tasks. This approach made the work more manageable and helped me identify where I needed to inspect column names or verify assumptions. Looking back, I would begin by confirming the database connection, project folder, file paths, and table schemas before writing the full set of queries. I would also test each task independently and save verified outputs as I progressed.

These steps would make the process more efficient and reduce the time spent troubleshooting problems that could have been identified earlier.

5. Visualising and Interpreting the Results

Data analysis becomes more meaningful when numerical outputs can be interpreted and communicated clearly. This is why I want to include charts from my actual analysis in this article.

Comparing the average daily mean temperatures of London, Beijing, Brasília, Cape Town, Delhi, and Moscow in 2014.

These visuals should be generated from the verified results of the relevant queries or DataFrame operations. I will add brief explanations of the patterns they show after confirming the actual values.

6. Key Lessons and Advice for Other Learners

My Week 6 experience has taught me that learning data analysis involves much more than memorising syntax.

Here are a few lessons I would share with anyone working through a similar assignment:

1. Understand your data source first. Know whether your data is stored in a database, a CSV file, an Excel workbook, or another format.
2. Check before you analyze. Inspect the number of rows, column names, data types, and sample records before proceeding.
3. Work in small steps. Complete and test one task at a time instead of trying to solve an entire assignment in one attempt.
4. Read errors carefully. Error messages often provide clues about incorrect paths, missing files, unsupported operations, or invalid column names.
5. Keep your work organized. Use clear file names, a consistent folder structure, and separate cells or scripts for different tasks.
6. Verify your results. Code running without an error does not automatically mean that the result is correct. Check whether the output makes sense and meets the task requirements.
7. Be patient with yourself. Some concepts take repeated practice before they become clear. Struggling with a problem is not the same as failing to learn.

Conclusion

Week 6 has been a demanding but worthwhile part of my DataraFlow Cohort 2 journey. I gained practical experience retrieving data from a database, exporting tables to CSV, loading datasets into Google Colab, exploring DataFrames with Pandas, and preparing SQL queries for analytical tasks. The challenges I faced made the learning process slower at times, but they also taught me to be more systematic, patient, and deliberate in my approach to problem-solving.

I am still learning, and I recognise that there is more to practise, particularly in validating SQL outputs and communicating analytical findings through charts. However, I am becoming more comfortable with the process of breaking down a problem, investigating errors, testing solutions, and learning from the results.

My biggest takeaway from this week is simple: progress in data analysis is not only about writing code that works; it is also about understanding the data, solving problems, and learning how to explain the results.

The journey continues, one task and one lesson at a time.