SQL AND CTEs

SALOME CHEMUTAI — Tue, 21 Apr 2026 03:35:40 +0000

What is a subquery?
Is a query that is written inside another query and allows one to carry out operations that actually depend on the result of another query.
There are different types of subqueries

SCALAR SUBQUERY
MULTI-ROW SUBQUERY
CORRELATED AND NON-CORRELATED SUBQUERY SCALAR SUBQUERY It runs a single value that is one row, one column Its operators mostly are greater than, less than, equal to, and EXISTS Example Find students who scored higher than the class average. SELECT student name, score FROM exam results WHERE score > (SELECT AVG (score) FROM exam results); This should give you the students who scored way above the class average MULTI-ROW SUBQUERY It returns many rows, like one row with multiple columns or one column with multiple rows Example Find the names of students who are enrolled in "CRE," but the names are in the students table, and enrolment data is in the Enrolments table. SELECT student name FROM students WHERE student_id IN ( SELECT student id FROM enrolments WHERE course name = 'CRE' ); This should give the names of students who enrolled in CRE, but their names are in different rows and columns.

CORRELATED AND NON-CORRELATED SUBQUERY
Correlated subquery actually depends on the outer query, and it can never be executed on its own.
Non-correlated subquery does depend on the inner query and can be executed on its own.
Example
Find students who scored higher than the average for their specific subject.
SELECT
student_ name, subject, score
FROM exam_ results AS main
WHERE score > (
SELECT AVG (score)
FROM exam_ results AS sub
WHERE sub. subject = main. Subject
);

WHEN TO USE?

Comparisons -between data in the tables
Filtering data in a row
Replacing temporary tables
For the calculation of bigger sums

What are CTEs
Also called common table expressions
CTEs named results in SQL can be referenced in a single statement for easier simplifications of complex logics, be readable, and reusable
Types of CTEs and their uses
Non-recursive CTEs
Mostly used in SQL
Refers to a set of your script that you can refer to anytime
Uses
For simplifying results, e.g., totals, AVG
Used in listing multiple CTEs
Used in referencing, e.g., reusing codes
Recursive CTEs
Reference itself independently
Executions always continue only until you command them to stop
Uses
For organizations of data in a systematic way
Used to create lists of data or series, such as generating all the working days in a certain month
Used in finding paths in different places

Conclusion
We used CTEs mostly because it allows you to write your code once
When you define your data source at the beginning, it makes the query read like a story.

Comparisons between subqueries and CTEs
Readability- Subquery is nested inside, while CTEs are nested outside
Maintenance – Subquery is a bit challenging to maintain due to the large logic, while CTEs are easier
Reusability – Subqueries cannot be reused, while CTEs are commonly reused within the same query
Use case- Subquery is convenient for simple filtrations, while CTEs are convenient for complex filtrations.

excel

SALOME CHEMUTAI — Tue, 21 Apr 2026 03:28:29 +0000

DAY 1

Intro

Excel

I learnt excel today

How to Publish Power report and embed into Website

SALOME CHEMUTAI — Tue, 07 Apr 2026 01:21:21 +0000

INTRODUCTION
Publishing a Power BI report and embedding it into a website depends entirely on whether your data is public or private and how it is in the data set
Currently, was doing power bi project,this article do explain the steps i applied as from data cleaning,modellings ,use of DAX functions,dashboards and etc
STEP 1. CREATE A WORKSPACE.
Before you publish your work, first you must have a workspace in power bi desktop where you will create your work and keeping tracks of your projects.
STEP 2. PUBLISH FROM POWER BI DESKTOP
When you have save your file, you can now publish it by click publish on right side of power bi desktop, hence select.
STEP 3. GENERATE EMBED CODE
After publishing your data, you can now generate the code that is on website which is the browser when you click the file at the left top.
STEP 4. EMBED ON THE WEBPAGE
You now open a HTML file in text editor ,paste where you want your report to appear at after saving the file .

Forem: SALOME CHEMUTAI

SQL AND CTEs

excel

DAY 1

Intro

Excel

How to Publish Power report and embed into Website