DEV Community: Mercy Musyoka

🚀 SQL Stored Procedures vs. Python Functions: Two Worlds, Same Idea

Mercy Musyoka — Mon, 08 Sep 2025 12:42:32 +0000

When I first started learning SQL and Python, I thought they had nothing in common. One was all about databases and the other about programming.

But the more I used them, the more I realized: stored procedures in SQL and functions in Python are like cousins living in different houses.

Here’s why 👇
What They Are

Stored Procedure (SQL): A set of SQL statements stored inside the database, ready to be reused.
Python Function: A block of Python code you can call whenever you need it.
Both are about reusability and encapsulation.

Key Similarities
1. Encapsulation

Both let you wrap logic into a single unit.

2. Reusability

Write once, use many times.

3. Parameters & Inputs

Both can accept inputs to handle different situations.

4. Return Values

Both give something back — a dataset in SQL, or any object in Python.

5. Control Flow

Both support conditions, loops, and error handling.

Examples in Action

Here’s how they compare in real life:

SQL Stored Procedure:

CREATE PROCEDURE GetEmployee (@id INT)
AS
BEGIN
    SELECT name, salary 
    FROM employees
    WHERE emp_id = @id;
END;

Python Function:

def get_employee(emp_id):
    employees = {1: ("Alice", 5000), 2: ("Bob", 6000)}
    return employees.get(emp_id, ("Not Found", 0
))

Both:

Take an ID as input
Run some logic
Return employee details

Quick Comparison Table

Feature	Stored Procedure	Python Function
Encapsulation	SQL statements	Python code
Reusability	Yes	Yes
Parameters	Input/Output	Arguments
Return Values	Results/Output	`return`
Control Flow	IF, WHILE, CASE	if, for, try

Where They Shine

Use stored procedures when:

Heavy data processing happens in the database
You want to reduce network trips
Security & consistency are top priorities

Use Python functions when:

Logic belongs in the application layer
You need flexibility and integration with libraries
Data has already been fetched into your program

Final Thoughts

Even though they live in different environments, stored procedures and Python functions share the same DNA: encapsulating, reusing, and organizing logic.

When you combine them, you get the best of both worlds:
Databases handle data-heavy tasks
Applications handle business logic

Subqueries vs. CTEs vs. Stored Procedures in SQL — Explained Simply 🚀

Mercy Musyoka — Mon, 08 Sep 2025 12:07:01 +0000

SQL offers different tools to query and manage data effectively. Three common concepts that often confuse beginners are subqueries, CTEs (Common Table Expressions), and stored procedures. While they may look similar at first, they serve different purposes and have unique strengths.

In this article, we’ll break them down one by one with examples, then summarize their key differences.

1. Subquery (Nested Query)

A subquery is a query written inside another query. It’s commonly used to provide intermediate results for filtering, aggregation, or transformation.

Key Features:

Exists only while the main query runs.
Can be written inside WHERE,FROM, or SELECT clauses.
Great for filtering based on calculated values. Example

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Here, the subquery(SELECT AVG(salary) …) calculates the average salary, and the outer query selects employees who earn above it.

2. CTE (Common Table Expression)

A CTE is a temporary, named result set created with the WITH keyword. Think of it as giving a subquery a name so you can reuse it and make your query more readable.

Key Features:

Exists only during a single query execution.
Improves readability and avoids deeply nested queries.
Can be referenced multiple times in the same query.
Supports recursion (useful for hierarchical data like org charts). Example:

WITH HighEarners AS (
    SELECT name, salary
    FROM employees
    WHERE salary > 5000
)
SELECT * FROM HighEarners;

Here, HighEarners acts like a temporary table that can be reused in the query.

3. Stored Procedure

A stored procedure is a saved, compiled block of SQL code stored permanently in the database. Unlike subqueries and CTEs, it isn’t tied to a single query but can be executed anytime.

Key Features:

Stored permanently in the database.
Can accept parameters (input/output).
Can include complex logic: loops, conditions, error handling.
Useful for reusability, automation, and encapsulating business logic. Example:

CREATE PROCEDURE GetHighEarners (@minSalary INT)
AS
BEGIN
    SELECT name, salary
    FROM employees
    WHERE salary > @minSalary;
END;

Run it with:

`EXEC GetHighEarners @minSalary = 6000;`

Quick Comparison

Feature	Subquery	CTE	Stored Procedure
Scope	Inside a single query	Within one query	Permanent in DB
Reusability	Not reusable	Reusable within the query	Reusable anytime
Storage	Not stored	Not stored	Stored in DB
Complexity	Simple logic	Moderate (supports recursion)	Full programming logic
Use Case	Filtering & aggregation	Readability & modularity	Business logic & automation

Conclusion

Use subqueries for quick, inline calculations or filters.
Use CTEs when you want better readability and the ability to reference results multiple times in a single query.
Use stored procedures when you need reusable, parameterized, and complex database logic.
By understanding when to use each, you’ll write cleaner SQL, improve performance, and build maintainable database solutions.

Data-Driven Insights into Kenya’s Crop Production

Mercy Musyoka — Tue, 26 Aug 2025 11:52:15 +0000

Introduction

Agriculture is one of the most important sectors in Kenya. It not only feeds millions of people but also provides jobs and income for many families. With farming being such a key part of the economy, it’s important to understand how different crops and regions are performing. That’s where data comes in.

To dig deeper, I created a Power BI dashboard that shows trends in crop production, revenue, profit, and land use across several counties. The dashboard, called Kenya Crop Insights, makes it easy to see which crops bring in the most revenue, which counties are leading in production, and how costs and yields change over time. My goal with this project is to show how data visualization can turn numbers into useful insights that can guide farmers, policymakers, and even investors in making better decisions for the agricultural sector.
The above can be shown using the image below of the dashboard I created
PLEASE NOTE THAT THE RESULTS BELOW DO NOT RELATE TO AN ACTUAL DATA SET

Overall Highlights (from the dashboard)

From the dashboard, a few big-picture insights immediately stand out. Kenya’s crop sector generated Ksh 1.19 billion in total revenue, with Ksh 1.10 billion in profit, showing that farming activities across the counties are quite profitable overall. The data also shows a combined total yield of 1.23 million units (depending on whether measured in tons or kilograms) spread across a total land area of 4,930 hectares.

What’s interesting here is the balance between land use and output. Even with less than 5,000 hectares under cultivation, farmers were able to achieve significant returns, pointing to the value of efficient farming practices and crop selection. These figures provide a snapshot of the sector’s strength while also highlighting areas where improvements in land utilization and productivity could unlock even greater results.

Revenue and Production Costs by Month

The data shows that revenue is consistently higher than production costs, meaning farming stays profitable throughout the year. However, there are clear ups and downs. Revenue peaks during harvest months, then dips slightly in between seasons. These shifts reflect the seasonal nature of farming in Kenya, where weather patterns and market demand affect performance.

Revenue by County

Nyeri leads with the highest revenue at Ksh 162M, followed closely by Nairobi and Nakuru. This can be linked to factors such as fertile soil, better farming practices, good infrastructure, and easier access to markets. These advantages give farmers in these counties higher returns compared to others.

Land Area & Yield by County

Nairobi and Kiambu stand out with both large land use and high yields. On the other hand, counties like Mombasa have smaller land areas and lower yields. This shows that productivity isn’t just about land size — good farming practices also play a big role in output.

Farmer-Level & Filters

The dashboard can be filtered by crop type, farmer, county, or month. This makes it easy for farmers, policymakers, and investors to drill down into specific details and make better decisions.

Recommendations

Invest more in counties with high yield efficiency to maximize returns.
Focus on profitable crops like rice, sorghum, and potatoes.
Support underperforming counties by providing better inputs, irrigation, and access to markets.

Conclusion

This analysis shows how data analytics can transform agriculture by making it more efficient and profitable. With tools like Power BI, raw data is turned into clear insights that help farmers, policymakers, and investors make informed decisions. By understanding trends in revenue, yield, and land use, Kenya’s agricultural sector can unlock more opportunities for growth and sustainability.

Excel for Predictive Analysis: Friend or Foe in Data-Driven Decisions?

Mercy Musyoka — Mon, 11 Aug 2025 15:26:03 +0000

Introduction

Love it or hate it, Excel has been running the world’s numbers for decades.
From school projects to billion-dollar business plans, it’s everywhere.

But can it actually keep up with predictive analysis — and should we trust it for serious data-driven business decisions?
Let’s break it down.

🚀 Where Excel Shines in Predictive Analysis

Everyone Knows It
From interns to CEOs, Excel is universal. That means you can share a forecast, and everyone “gets it” without a data science degree.
Built-in Forecasting Tools
Functions like FORECAST.ETS and regression in the Data Analysis ToolPak let you project future trends quickly.
Flexible & Quick to Prototype
Need a “what-if” sales model before lunch? Excel delivers with formulas, pivot tables, and charts — no setup headaches.
Works With Many Data Sources
CSV files, databases, Power BI… Excel pulls them in so you can mix datasets before predicting outcomes.

Below is how an excel looks like.

⚠️ Where Excel Falls Short

Struggles With Big Data
Feed it millions of rows, and it’ll slow down or crash.
Human Error Risk
One misplaced formula and your forecast could go off the rails.
Basic Statistics Only
Fine for regression, weak for advanced predictive models.
Messy Collaboration
Multiple editors often mean broken formulas and version chaos.

💡Excel’s Role in Data-Driven Decisions
Excel may not be a machine learning powerhouse, but it’s the perfect starting point for data-driven thinking:

Model business scenarios fast.
Make insights clear with charts and conditional formatting.
Share forecasts in a format everyone understands.

_Example: A retailer can forecast holiday sales, test stock scenarios, and decide inventory levels — all in one afternoon.

🎯The Verdict
Excel is your quick-thinking friend: great for starting the conversation, not for running the entire predictive analysis show.
Pair it with dedicated analytics tools when your data (or decisions) get bigger and more complex.

💬 What’s your take — is Excel underappreciated or overrated in predictive analytics?

How to Install & Set Up PostgreSQL on Linux (Beginner Friendly)-UBUNTU

Mercy Musyoka — Fri, 01 Aug 2025 13:48:48 +0000

How to Install PostgreSQL and Set It Up on a Linux Server

PostgreSQL is a powerful, open-source relational database system used by developers, startups, and large enterprises worldwide.

In this guide, I will walk you through installing PostgreSQL on a Linux server and doing a basic setup so you can start using it right away.

Prerequisites

Before you begin, make sure you have:

A Linux server (Ubuntu/Debian or CentOS/RHEL)
sudo or root access
An internet connection
Terminal(I used git bash)

🛠 Step 1: Access Your Linux Server

First, connect to your Linux server via SSH:
ssh username@server_ip_address

1.Replace the username with server's user(for example in my case mawia)
2.Replace the server_ip_address with your server's ip address

You can as well choose to specify the port after inputing the above( -p 22) but its not a must

🛠 Step 2: View Files and Directories

Once connected, you can check the contents of your home directory:
This can be done by;
ls command on the terminal you are using
This helps confirm you’re in the right place before proceeding.

🛠 Step 3: Update the System

It’s best to ensure your system is up-to-date before installing software to avoid vulnerabilities and compatibility issues.
If using Ubuntu, you can use the command;
sudo apt update

🛠 Step 4: Install PostgreSQL

The command used in ubuntu is;
sudo apt install postgresql postgresql-contrib -y
After inputing this command you will be asked to choose yes or no please choose yes so as to continue with the process of installation as shown below;

🛠 Step 5: Check whether you have PostgreSQL /start

You use the command;
sudo systemctl start PostgreSQL

For example, from the above the state is active

🛠 Step 6:Check the version of PostgreSQL

You use the command;
psql --version
For example my version was 16.9 from the screenshot I took below.

🛠 Step 7:Now Enable the PostgreSQL

use the command;
sudo systemctl enable postresql
As show below, the PostgreSQL is now active.

🛠 Step 8:Check Status

use the command;
sudo systemctl status postgesql
Expect something the this;

🛠 Step 9: Now its time to interact with the PostgreSQL

Now that PostgreSQL is installed and running, let’s log in and create our first database.

🛠 Switching to PostgreSQL default user

PostgreSQL creates a default Linux user called postgres. Switch to it:
we use the command;
sudo -i -u postresql

i- stands for interface
u- user
postgresql is the default Postresql user

You should see your prompt change to:

`postgres@myvm:~$`

🛠 Access the PostgreSQL Shell

Sinceyou’re the postgres user, open the PostgreSQL interactive terminal using the command;
psql
Example of the result to ecpect is shown below;

🛠 Now list the databases created

Using the command,
\l
The default database is postgres as shown below;

🛠 Create a new database

Inside the psql shell, now create a new database
using the command;
create database nameofdatabase;
Remember to terminate the query
for example in my case I was creating a database named student.
so i used the command;
create database student

🛠 create user

Now create user, with the command;
create user nameofuser with password 'passwordofchoice'
eg in my case;
create user lux with password '1234';
Remember to terminate the query

🛠Grant user access to the database user

using the command;
GRANT ALL PRIVILIGES ON DATABASE nameofdatabase TO username
for example in my case,
GRANT ALL PRIVILEGES ON DATABASE STUDENT TO LUX

🛠 Now check the databases created

This can be done by using the command;
\l
yow will see the default one which is postgres and the one you just created in my case it was student.

WRAPPING UP!

Congratulations!!!!!!!!!!!!!!!

You have successfully installed and set up PostgreSQL on ubuntu, created your first database, and learned how to view all the available databases with the `\l` command.

From here, you can connect PostgreSQL to your favorite tool and applications. For example, you might use a postgreSQL GUI tool like pgDdmin or DBeaver, or connect directly from a programming language like python,Node.js or Java.

Here is the general format for a PostgreSQL connecting string:

postgesql://username:password@host:5432/databasename

from the above replace username ,password host and databasename with your own values.

Exiting PostgreSQL and the Terminal

When you're done:

leave the PostgreSQL shell by typing the command; \q
If you are connected to your ubuntu server via SSH, log out by typing the command; exit --- That's it!!!! You are ready now to start building applications backed by a powerful PostgreSQL database. Whether you're developing locally or deploying to production, this setup will give you a reliable foundation for storing and managing your data.

DEV Community: Mercy Musyoka

🚀 SQL Stored Procedures vs. Python Functions: Two Worlds, Same Idea

SQL Stored Procedure:

Python Function:

Quick Comparison Table

Final Thoughts

Subqueries vs. CTEs vs. Stored Procedures in SQL — Explained Simply 🚀

In this article, we’ll break them down one by one with examples, then summarize their key differences.

1. Subquery (Nested Query)

2. CTE (Common Table Expression)

3. Stored Procedure

EXEC GetHighEarners @minSalary = 6000;

Quick Comparison

Conclusion

Data-Driven Insights into Kenya’s Crop Production

Introduction

Overall Highlights (from the dashboard)

Revenue and Production Costs by Month

Revenue by County

Land Area & Yield by County

Farmer-Level & Filters

Recommendations

Conclusion

Excel for Predictive Analysis: Friend or Foe in Data-Driven Decisions?

Introduction

How to Install & Set Up PostgreSQL on Linux (Beginner Friendly)-UBUNTU

How to Install PostgreSQL and Set It Up on a Linux Server

In this guide, I will walk you through installing PostgreSQL on a Linux server and doing a basic setup so you can start using it right away.

Prerequisites

🛠 Step 1: Access Your Linux Server

🛠 Step 2: View Files and Directories

🛠 Step 3: Update the System

🛠 Step 4: Install PostgreSQL

🛠 Step 5: Check whether you have PostgreSQL /start

For example, from the above the state is active

🛠 Step 6:Check the version of PostgreSQL

🛠 Step 7:Now Enable the PostgreSQL

🛠 Step 8:Check Status

🛠 Step 9: Now its time to interact with the PostgreSQL

🛠 Switching to PostgreSQL default user

postgres@myvm:~$

🛠 Access the PostgreSQL Shell

🛠 Now list the databases created

🛠 Create a new database

🛠 create user

🛠Grant user access to the database user

🛠 Now check the databases created

WRAPPING UP!

You have successfully installed and set up PostgreSQL on ubuntu, created your first database, and learned how to view all the available databases with the \l command.

Exiting PostgreSQL and the Terminal

QUERY ON BUDDDY!!!!!!!!**

`EXEC GetHighEarners @minSalary = 6000;`

`postgres@myvm:~$`

You have successfully installed and set up PostgreSQL on ubuntu, created your first database, and learned how to view all the available databases with the `\l` command.