Forem: muriithilydia46-wq

The Query Inside the Query: Understanding Subqueries and CTEs in SQL

muriithilydia46-wq — Tue, 28 Apr 2026 07:02:38 +0000

Have you ever written an SQL query, looked at it five minutes later, and had absolutely no idea what it was doing? You are not alone. Most SQL beginners hit a wall when their queries start growing.

In this article, we are going to look at two powerful SQL tools; subqueries and CTEs. We shall understand what they are, when to use them, and why CTEs might just change the way you write SQL forever.

Part 1: Subqueries

A subquery is simply a SELECT statement written inside another SELECT statement. The inner query runs first, and its results is handed to the outer query.

Let us look at an example from city hospital data;

"Question: Find all patients who paid more than the average appointment fee."

SELECT patient_id, diagnosis, fee
FROM appointments
WHERE fee > (SELECT AVG(fee) FROM appointments);

In this case, the inner query (SELECT AVG(fee) FROM appointments) runs first and returns a number. The outer query then uses that number to filter results.

Commonly used types:

Scalar subquery — returns one single value. Used in WHERE or SELECT.

e.g., What is each student's mark compared to the class average?

SELECT student_id, marks,
(SELECT ROUND(AVG(marks),2) FROM exam_results) AS class_avg
FROM exam_results;

List subquery — returns a column of values. Used with IN or NOT IN. e.g., Which patients have had at least one appointment?

SELECT full_name FROM patients
WHERE patient_id IN (SELECT DISTINCT patient_id FROM appointments);

Table (Derived) — returns a full mini-table. Used with the FROM clause.
e.g., Find departments where the average marks exceed 65
SELECT department, avg_marks
FROM (
SELECT s.department, ROUND(AVG(er.marks), 2) AS avg_marks
FROM exam_results er
JOIN subjects s ON s.subject_id = er.subject_id
GROUP BY s.department
) AS dept_summary
WHERE avg_marks > 65;
Correlated — references the outer query. It runs once per row.
e.g., Find students who scored above their own class average

SELECT student_id, class, marks
FROM exam_results e1
WHERE marks > (
SELECT AVG(marks) FROM exam_results e2
WHERE e2.class = e1.class
);

When to Use a Subquery;

Quick one-off filters using IN, NOT IN, or EXISTS
When the logic is short, i.e., one or two lines
When you only need the result once

Part 2: CTEs —(Common Table Expression)

A CTE lets you write your inner logic once, give it a name, and then use that name in your main query.
Think of it as giving your subquery a label so everyone knows what it means. You define it at the top of your query.

Basic Syntax
WITH cte_name AS (
SELECT ...
FROM ...
WHERE ...
)
SELECT * FROM cte_name;

Types of CTEs

Simple CTE -It defines a named result set used once or multiple times in the main query. It is perfect for cleaning up a single layer of complexity.
e.g., Show only doctors with more than 8 years of experience
WITH experienced_doctors AS (
SELECT doctor_id, full_name, specialisation, years_exp
FROM doctors
WHERE years_exp > 8
)
SELECT * FROM experienced_doctors
ORDER BY years_exp DESC;
Multiple CTEs
Defines several named blocks in one WITH clause, separated by commas. Each one can reference the previous, like a logical pipeline.
e.g., Find doctors who charge above the hospital average fee

WITH doctor_fees AS (
SELECT doctor_id, ROUND(AVG(fee), 2) AS avg_fee
FROM appointments
GROUP BY doctor_id
),
hospital_avg AS (
SELECT ROUND(AVG(fee), 2) AS overall_avg
FROM appointments
)
SELECT d.full_name, df.avg_fee, ha.overall_avg
FROM doctor_fees df
JOIN doctors d ON d.doctor_id = df.doctor_id
JOIN hospital_avg ha ON df.avg_fee > ha.overall_avg
ORDER BY df.avg_fee DESC;

3.Recursive CTE
A recursive CTE references itself. It is the only way to handle hierarchical data like a reporting chain or org chart. This is something a regular subquery simply cannot do.

e.g., Build the full doctor supervision hierarchy
WITH RECURSIVE doctor_hierarchy AS (

-- Base: doctors with no supervisor (top of the chain)
SELECT doctor_id, full_name, supervisor_id, 1 AS level
FROM doctors
WHERE supervisor_id IS NULL

UNION ALL
-- Recursive: find doctors supervised by those already found
SELECT d.doctor_id, d.full_name, d.supervisor_id, dh.level + 1
FROM doctors d
JOIN doctor_hierarchy dh ON d.supervisor_id = dh.doctor_id
)

SELECT * FROM doctor_hierarchy
ORDER BY level, doctor_id;

The base case finds the top-level doctors (Dr. Amina Omondi, Dr. James Abdi). The recursive case keeps looking for who reports to them, until no more levels exist.

Here is a comparison between Sub-queries and CTEs

Conclusion;
Subqueries and CTEs both solve similar problems, but they feel very different to write and to read.

Use a subquery when your filter is short, simple, and only needed once, especially with IN, NOT IN, and EXISTS.
Use a CTE when your logic grows, when you need to reuse a result, or when someone else needs to read your code.

Start with subqueries to understand nesting. Proceed to CTEs as your queries grow. Your code will be cleaner, and you definitely will sleep earlier!

SQL SET Operators: UNION, INTERSECT, and EXCEPT Explained Simply

muriithilydia46-wq — Wed, 22 Apr 2026 16:48:02 +0000

If you are just starting out with SQL, you have probably already learned how to write a SELECT query, filter data with WHERE, and maybe even join two tables together. But what happens when you need to combine results from two completely different queries? That is exactly where SET operators come in.

Think of SET operators as tools that let you stack or compare the results of two SELECT queries. Instead of joining tables side by side like a JOIN does, SET operators combine query results on top of each other, row by row.

There are four SET operators in SQL:
• UNION — combines results and removes duplicates
• UNION ALL — combines results and keeps duplicates
• INTERSECT — returns all the rows that appear in both queries
• EXCEPT — returns results from the first query that do not appear in the second query

NB: For intersect and except to work, both select statements must have the same number of columns and columns must have compatible data types. If you try to combine a text column with a number column, PostgreSQL will throw an error.

Let us look at examples for more understanding;

UNION

Union combines results Without Duplicates.
I had two tables; one with student data and another with patients data. My task was to show a combined list of all unique cities from the students table and the patients table, ordered alphabetically. This is where UNION came in. See syntax below to solve the query;

SELECT city FROM nairobi_academy.students
UNION
SELECT city FROM city_hospital.patients
ORDER BY city ASC;

What UNION does here is take all the cities from students and patients table and combine them into one list, then remove any city that appears more than once. So even if Nairobi appears in both tables, it only shows up once in the final result.

UNION ALL

Union All combines results and Keeps everything, including duplicates.
Sometimes you do not want duplicates removed. Maybe you want to see every single name from multiple tables together, with a label showing where each name came from. That is exactly what UNION ALL does.

SELECT first_name AS name, 'Student' AS source
FROM nairobi_academy.students
UNION ALL
SELECT full_name AS name, 'Patient' AS source
FROM city_hospital.patients;

This query gives me one long list with every student name and every patient name. The second column called source tells us whether each name came from the students table or the patients table.

INTERSECT

Intersect finds the answer to the question, what do these two queries have in common?

In my assignment, I used it to find cities that appear in both the students table and the patients table, meaning cities where both students and patients live.

SELECT city FROM nairobi_academy.students
INTERSECT
SELECT city FROM city_hospital.patients

Only cities that exist in both results are returned. If a city appears in students but not in patients, it is excluded.

EXCEPT

Except helps find what is missing. It returns rows from the first query that do not appear in the second query. This is useful when you want to find missing records.

For example, if I wanted to find cities where students live but no patients are from, I would write;

SELECT city FROM nairobi_academy.students
EXCEPT
SELECT city FROM city_hospital.patients;

This returns only the cities unique to the students table. Any city that also appears in the patients table gets filtered out.

Parting shot!

SET operators took me a while to fully grasp, mostly because I kept mixing up UNION with JOIN. But once I started thinking of them as tools for stacking query results rather than connecting tables, everything clicked. If you are a beginner just getting into SQL, my advice is to practice each operator separately before combining them. Write a simple UNION first, check the result, then move to INTERSECT and EXCEPT. Take it one step at a time and do not be discouraged by the errors, they are part of the learning process. Keep going, it gets better!

Introduction to SQL: DDL, DML, and Querying Data

muriithilydia46-wq — Sun, 12 Apr 2026 12:49:25 +0000

As a beginner database administrator, my first assignment was to build a school database for Nairobi Academy from scratch. At first this seemed impossible, especially since I had repeated cases of errors in my queries the whole week. But I soldiered on, ready to research and debug any cases of errors and finish the project. So here is what I learned about SQL this week and some tips;

"SQL stands for Structured Query Language". We use several languages in SQL, main ones being; DDL (Data Definition Language) & DML (Data Manipulation Language)

DDL defines database structure using the following commands; CREATE, ALTER, and DROP.
DML simply entails changing data inside the tables. The main commands used are INSERT, UPDATE, SELECT and DELETE

Think of it this way; DDL builds the house, DML fills it with furniture.

In my current project, the main commands I used were CREATE, INSERT, UPDATE, and DELETE and I will take you through the 'how' shortly.

I used CREATE to create a schema called nairobi_academy, then CREATE TABLE to build three tables: students, subjects, and exam_results;

I also used ALTER TABLE to modify tables after creation, that is; adding a phone_number column, renaming credits to credit_hours, and dropping phone_number when it was no longer needed. This taught me that SQL is flexible and you can always adjust a table's structure even after it has been created.

Next, I used INSERT command, where I added 10 students, 10 subjects, and 10 exam results into the database -see example below;

I then used UPDATE command and corrected data that had changed. For instance; updating Esther's city from Nakuru to Nairobi, and fixing incorrect marks from 49 to 59.

In addition, I used DELETE command to remove a cancelled exam result from the table.

NB: The most important lesson with UPDATE and DELETE is that, always use a WHERE clause, else every single row in the table gets affected and will be deleted.

Another key lesson has been how to use SQL filtering functions e.g., WHERE, SEARCH, IN, NOT IN, COUNT & CASE WHEN.

The WHERE clause filters rows based on conditions while CASE WHEN works like an if-else statement. It creates new labels based on conditions without changing the original data.

examples;

The biggest challenge this week was managing errors when running scripts multiple times. Some errors I encountered today was "relation already exists" and it kept appearing till it got frustrating. I learned that using IF NOT EXISTS and DROP SCHEMA CASCADE solves this cleanly.

I also learned the hard way that column names matter. For instance; renaming credits to credit_hours after inserting data caused errors because the INSERT still referenced the old name. Order really matters in SQL! Do not give up when creating and querying data in SQL, remain calm, research on how to debug and you've got it!

How to publish a Power BI report and embed it in a website.

muriithilydia46-wq — Sun, 05 Apr 2026 10:26:57 +0000

Power BI is a tool created by Microsoft to turn raw data into interactive insights.

The publishing process entails the below key steps;

Creating a workspace
Publishing power BI report
Creating an embedded code
Embedding the report on a Website

Here is a detailed process of the step by step process flow for a successful publishing.

1.Creating a workspace.
Open a browser and sign in with Microsoft account. Use below link,
https://app.powerbi.com

Select “Workspaces” on the left navigation pane, then click “New workspace”

Fill in the details populated, then click “apply”. This auto-populates your new workspace.

2. Publishing Power BI reports
Having created your own workspace, you can now upload your report using the below steps;

Open your finished report in Power BI desktop
Click “publish” on the Home tab;

Select a destination (the workspace you had created earlier), then click select;

The report is automatically uploaded in your workspace. Check your workspace to verify the uploaded data.

To open the report, click on it and it will automatically open.

3. Create an Embedded Code.
There are three different ways to embed a report;

Publish to web (Public)
Power BI gives you an iframe code you can drop into any website. It makes your report publicly accessible to anyone with the link. It is therefore not suitable for confidential reports. Here is how you go about it;

Open your published report in Power BI service, then under the report menu, click file >Embed report>Publish to web;

*Secure Embed (internal use). *

Viewers need to log in with their Microsoft account to see it. Great for internal company pages like a SharePoint site or Teams tab. It keeps the data protected without much technical setup.

Build it into your app (Developer route).

This one is for when you're building a product for a client for instance, and where they do not have Power BI Licenses. e.g., a SaaS tool. It requires more setup (you'll need to work with Azure and the Power BI API), but it's the most powerful and flexible option.

4.Embedding the report on a Website;

Click File in the top menu
Select Embed report → Publish to web (public)
Click Create embed code
Copy the iframe code that appears to your text editor (Vs code)
Create a folder in your desktop and name it deploying/publishing.
Open the folder in VS code and click select.

under the folder, create a new file, i.e., index.html

Open this website in your browser https://www.w3schools.com/html/ and copy the html code and paste it in your text editor and save.

Go to the folder created in desktop (deploying/publishing) and select the folder, then click index;
Copy the url generated and copy it to your browser;
Go back to text editor (VC code) and delete “this is a paragraph)
History copy (windows + V) on the line you have deleted and it select iframe (the embedded code)
Copy the code above and go back to your browser and refresh.
You will get a sign in prompt, proceed to sign in;
After signing in, your report automatically load;

In conclusion, learning power BI has been an eye opening experience as a beginner data analyst. Going in, I expected it to be mostly about making charts look good, but I quickly realised that the real work happens long before you touch a single visual. Cleaning messy data and building a solid model is where most of your time and energy actually goes. Publishing felt like a milestone and is genuinely satisfying. Learning different ways of embedding reports as well felt more like a professional lesson, to ensure I do not publish publicly confidential information and face data breach suits.

How excel is used in Real-World Data Analysis

muriithilydia46-wq — Thu, 26 Mar 2026 18:48:50 +0000

Microsoft Excel is a spreadsheet software that allows you to collect, organize, analyze, calculate, and visualize data efficiently. It organizes data into a grid of rows and columns, where each intersection is called a cell. Each cell can hold text, numbers, dates, or formulas that perform calculations automatically.

In real world, excel has been widely used in data analysis. For instance, where large data sets are involved, excel becomes instrumental in organizing the data and creating a dashboard for visualization. In essence this makes the data easily consumable for insights by management and aids in decision making.

In my first week of study as a data analyst, I have come to appreciate the capabilities of excel. What has really intrigued me is data clean up, use of pivot tables, charts and creating an interactive excel dashboard.

Did you know you can remove extra spaces from your text using text functions? Just use the following formula;'=TRIM(text)'. Additionally, you can combine two columns using the function "Concatenate". Here is how; =Concatenate(text). You have to specify if you need a separator, such that you put the space in parenthesis e.g. '=CONCATENATE(A2, " ", B2)'

The most insightful lesson has been on using pivot tables to show case relationship analysis, and using the pivot tables as primary data source for creating charts and excel dashboard.

A PivotTable is a tool that automatically summarizes large amounts of data. Instead of writing formulas, you simply drag and drop fields to group and calculate your data.
Example use: You have 5,000 rows of sales data. A PivotTable can instantly show you total sales per region per month instantly.

Drag a field to rows - groups your data e.g., by product category
Drag a field to values -calculates totals, count, average, Mid etc.,
Drag a field to filters -narrows down the criteria of what to see.

NB: Slicers are also used in place of filters. To insert a slicer, click inside any pivot table, Go to insert tab, click "slicer" and then select the fields you want slicers for.

Charts visualize your data so patterns and trends are easier to understand. Excel offers many chart types, most commonly used being; bar chart, line chart, pie chart and column chart.

Lastly, creating an interactive dashboard on excel is the main output for visualization. You start by creating a title for your dashboard and KPIs by inserting a shape. You then copy the pivot tables previously mapped from your data source and insert slicers. Ideally, slicers should be on the left side, while KPIs should be on top. You then right click on the slicers then click "report connections". This was the dashboard is now interactive.

Lastly, you create an excel dashboard using pivot tables as the source data, but first you need to write the title of your dashboard and the KPIs.

The next step is to copy the pivot tables previously mapped into the dashboard, including slicers. You then create connections for the slicers such that any change is reflected in all charts.

Excel has been life changing to me and I am already utilizing some of the skills learnt so far in my day to day work activities.