Let's practice some SQL queries with a dataset on Kaggle

All work and no play makes Jack a dull boy… no, wait! That’s not what I was going for; I wanted something along the lines of learning a concept and not practicing it will not be of much use in the long run. If you find something more appropriate for this, please post it in the comments😂.

Jack aside, let’s dive back into SQL. In the last article, we went through the basics of SQL and a few queries you are likely to use as you work with SQL.

In this article, it will be less theory and more code.

Side note I think is important to share, I didn’t just sit and type code and get exemplary results; I got errors that had me going back to my notes (I heavily resisted the urge to listen to the tiny voice in my head questioning whether I really know what I’m doing. She’s getting quieter- proof; you’re reading this). further, I coded a lot with the help of Qwen. I started using it yesterday to create a sample itinerary and then decided to use it today to code. I have only used ChatGPT to code so trying out another LLM with my code and that LLM being Qwen, was a huge success. I loved especially how it explains line by line what your mistake was and what you should do instead.

As I grew a little more confident, I tried coding the solution I thought would be correct based on my knowledge or lack thereof and then working through the error with Qwen instead of directly asking for the solution and pasting it on the Jupyter notebook.

To code, I used the Jupyter notebook on Kaggle.

Note that the notebook supports Python; If you want to write SQL code, you will need to code in the modules provided for SQL. I opted to code in the notebook and with just a few lines, I was able to write my SQL commands in Python.

To do this, you want to run the first cell first thing, to import the Python Panda library. Panda is an open-source Python library that allows data manipulation and analysis and helps when working with relational databases/structured data which in our case, is what we are working with! Note that you only need to run this cell once and don’t need to repeat the code in the rest of the subsequent cells you run.

See below short notes I made on the five areas we will be tackling:

Below I have indicated the assignment question under the key area tackled.

Here is a link to my dataset on Kaggle- there’s lots of data sets for you to practice with :)

Let’s get our hands dirty!

import pandas as pd
from pandasql import sqldf

Basic Selection

List all columns for sales that occurred on a Friday.

1.  df = pd.read_csv('/kaggle/input/coffee-orders/Coffe_sales.csv')
   
    # Now run SQL queries on the DataFrame
    #shows all coffee sales that took place on Friday
    coffee_orders_query="""
        SELECT *
        FROM df
        WHERE Weekday= 'Fri';
    """
    result = sqldf(coffee_orders_query)
    result
   

   Filtering with Conditions

   How many transactions were made using "card" payment in the month of March 2024?

    payment_type="""
    SELECT *
    FROM df
    WHERE cash_type='card'
    AND Month_name='Mar'
    """
    result=sqldf(payment_type)
    result
   

   Aggregation

   What is the total revenue (sum of money) generated in April 2024?

1.  sum_query="""
    SELECT SUM(money) AS total_money
    FROM df
    WHERE Month_name='Apr'
    """
    result=sqldf(sum_query)
    result
   

   Grouping

   Which coffee type (coffee_name) was sold the most in terms of quantity (number of transactions) in May 2024?

sales_query="""
SELECT
    coffee_name,
    SUM(money) AS total_sales
FROM df
WHERE Month_name='May'
GROUP BY coffee_name
ORDER BY total_sales DESC
LIMIT 1
"""
result=sqldf(sales_query)
result

Average Calculation

What is the average amount spent per transaction for "Latte" across the entire dataset?

average_query="""
SELECT AVG(MONEY) AS latte
FROM df
WHERE coffee_name='Latte'
"""

result=sqldf(average_query)
result

Interesting enough, I got the assignment questions from Qwen. Below was the prompt I used to get sample exercises.

I am practising SQL commands with a dataset on Kaggle
Give me five practise questions to work with from the data set attached to help me understand the basics

In conclusion, don’t be scared to use AI to code; better yet use it to optimize your learning and your work in general. Reminds me of this interesting article over the week. Have a read and let me know what you think.

See you soon data warriors!